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索引