当前位置:   article > 正文

mysql——B+树索引_mysql的b+句

mysql的b+句

mysql——B+树索引

何谓索引

索引是一种高效的数据结构,可以帮助数据库快速搜索数据,给某个字段建立索引,就会生成一棵以该字段排序的B+树,如果你不懂B+树,可以想一想为什么排序后二分查找会这么快。索引也不可以过度使用,否则会消耗很多磁盘空间,并且DML操作的时候维护索引也会很消耗性能。

索引类型

聚簇索引

聚簇索引又叫聚集索引或主键索引
聚簇索引有以下两个特点:

  • 特点1:
    按主键值的大小进行记录和页的排序,数据页(叶子节点)之间是按照主键值从小到大排序的一个双向链表
  • 特点2:
    B+树的叶子节点存储的是完整的用户记录,就是指这个记录中存储了所有列的值(包括隐藏列)

InnoDB存储引擎会自动的为我们主键创建聚簇索引,因此只有在搜索添加是主键才能发挥作用

非聚簇索引

别名非聚集索引,二级索引,复制索引,辅助索引。

非聚簇索引按照指定的索引列排序生成B+树,并且叶子节点只存储了索引列的值和主键值,这肯定是为了节省磁盘空间,这使得我们非聚集索引查询时候只能查到主键值,如果你需要记录中除了索引列的其他列的数据,只能通过查到的主键再走一遍聚集索引,这叫做回表

非聚簇索引又有 唯一索引,联合索引,前缀索引

唯一索引
该列的值必须唯一,NULL除外,我mysql5.5测试NULL值可以有很多个
联合索引
即给多个字段建立索引,这会牵扯出最左前缀原则,后面再说
前缀索引
当我们想给某个字段建立索引时,发现该字段里面的值都比较长,可以采用前缀索引来节省空间,来达到空间和效率以及数据重复率的平衡,后面举例具体说。

创建索引

创建语句

#创建索引
CREATE [UNIQUE] INDEX index_name ON table_name(field_list);#多个字段用逗号隔开

#删除索引
DROP INDEX [index_name] ON table_name;

#查看索引
SHOW INDEX FROM table_name;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

创建前缀联合索引例子:

在这里插入图片描述
商品表(图中部分数据),id是主键,下面我想给shop_id和goods_name建立联合索引,但是由于goods_name值太长了,我想使用前缀索引来缩减索引要使用的磁盘空间,但是我不知道goods_name要取前面多少个字符比较合适。

可以采取这条sql看看shop_id+goods_name组合的重复率
SELECT count(DISTINCT(concat(shop_id, left(goods_name,8))))/count(*) AS Selectivity FROM takeout_goods_pic

经过测试截取goods_name前8个字符比较合适
create index idx_id_name on takeout_goods_pic(shop_id,goods_name(8))

创建原则

原则1: 主键外键自动创建索引

原则2: 频繁搜索,排序,分组的字段适合创建索引

原则3: 按照业务需求建立联合索引,索引过长使用前缀索引

原则4: Cardinality/表记录数越接近1越适合建立索引,见上例

索引覆盖

利用辅助索引查询时要经过两个步骤:
第一步利用辅助索引查询到叶节点,通过叶节点拿到主键。
第二步通过主键索引回表查询数据
如果我们只通过第一步就可以拿到所需数据,就是索引覆盖。例如有联合索引(a,b,c)
select a,b,c from table where a=1

最左前缀原则

例如有联合索引(a,b,c)

可以利用到索引的例子

select a,b,c from table where a=1 and b=1 and c>1 
select a,b,c from table where a=1 and c>1 
select a,b,c from table where a=1 and b>1 
  • 1
  • 2
  • 3

不可以利用到索引的例子

select a,b,c from table where b=1 and c>1 
select a,b,c from table where c>1 
select a,b,c from table where b>1 
为什么用不到索引:因为联合索引(a,b,c)要根据abc的顺序来判断大小,如果a相等,则判断b,如果b再相等,判断c
  • 1
  • 2
  • 3
  • 4

一些特殊例子,部分利用索引

只有a>1能用到联合索引,应该是先a>1利用索引查出记录,然后再筛出b>1&&c>1的记录
select a,b,c from table where a>1 and b>1 and c>1 
下面两个类似
select a,b,c from table where a>1 and c>1 
select a,b,c from table where a>1 and b>1 
  • 1
  • 2
  • 3
  • 4
  • 5

like查找也是类似最左前缀原则
select * from table where name like ‘%xx%’ 我们都知道这用不到索引,因为没办法比较大小,只有左边是精确值才可以使用索引

order by的使用

利用索引
select * from table order by a,b,c 
select * from table where a = 1 order by b,c 
不走索引
select * from table order by b,c
select * from table where a > 1 order by b,c 
混用ASC DESC不走索引
select * from table order by a asc,b desc,c 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

group by 类似,不再赘述

索引失效

什么是索引失效?
explain关键字可以看到possible_keys和key
possible_keys是可能使用到的索引,key是查询优化器真正使用的索引
当possible_keys不为空,而key为空时这就是索引失效,查询优化器认为这条查询走索引还不如全表扫描效率来的高

而造成索引失效的主要原因是查询语句满足条件的记录过多,mysql技术内幕的作者说是超过全表记录的20%就会失效。
思考为什么记录过多就会放弃索引?
这应该是因为辅助索引查出了非常多的主键值,由于这些主键值是离散非连续的,根据主键值去回表需要离散读取主键记录,这可能会比全表扫描的顺序读更耗时,离散读取的效率远低于顺序读取,因为离散读取可能要切换扇区,盘块之类。

导致索引失效的情况
in, not in,between and , <,>,甚至=
都可能导致索引失效,具体还要explain查看一下,optimizer_trace也有帮助
例如:explain select * from takeout_goods_pic where shop_id > ‘0’ 就索引失效了
在这里插入图片描述
字段本是varchar,查找的时候用int
例如:select * from takeout_goods_pic where shop_id = 7551069

使用or查询
explain select * from takeout_goods_pic where shop_id = ‘7691005’ or goods_name =‘八爪鱼 500g’
在这里插入图片描述
or查询右边没有办法使用到联合索引,只能全表扫描

不使用索引

where里面对索引字段使用函数操作
explain select * from takeout_goods_pic where left(shop_id,1) = ‘7’
在这里插入图片描述

where里对索引字段执行表达式操作

参考资料

sirm-innodb索引

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/菜鸟追梦旅行/article/detail/616734
推荐阅读
相关标签
  

闽ICP备14008679号