MySQL索引机制

正确的创建合适的索引是提升数据库查询性能的基础。

一、索引是什么？

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。

二、为什么要使用索引？

1. 索引能极大的减少存储引擎需要扫描的数据量。
2. 索引可以把随机IO变成顺序IO。
3. 索引可以帮助我们在进行分组、排序等操作时，避免使用临时表。

三、索引谁实现的

MySQL结构体系：

Indexes是第三方公司提供的可插拔的插件式存储引擎。

三、为什么选择B+Tree？

Data Structure Visualizations：
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
(1)（相对）平衡二叉树
它的左右两个子树的高度差的绝对值不超过1。

缺点：
a）太深
数据处的高/深度决定着他的IO操作次数， IO操作耗时大。
b）太小
每一个磁盘块（节点/页）保存的数据量太小了。
（2）多路平衡查找树 B-Tree

（3）加强版多路平衡查找树 B+Tree

支节点只保存索引列关键字，不保存数据，只有叶子节点才保存数据。

B+Tree与B-Tree的区别：

1、B+节点关键字搜索采用闭合区间。（MYSQL推崇使用ID作为索引，由于ID是自增的数字类型，只会增大，所以采用向右拓展的一个方式。）
2、B+非叶节点不保存数据相关信息， 只保存关键字和子节点的引用。
3、B+关键字对应的数据保存在叶子节点中。
4、B+叶子节点是顺序排列的， 并且相邻节点具有顺序引用的关系。

为什么选择B+Tree？

B+树是B-树的变种（ PLUS版） 多路绝对平衡查找树， 他拥有B-树的优势。
B+树扫库、 表能力更强。
B+树的磁盘读写能力更强。
B+树的排序能力更强。
B+树的查询效率更加稳定。

四、B+Tree在两大引擎中如何体现

show variables like 'datadir';

可到数据存储位置。

MyIsam：

索引和数据分别存储。表定义存在.frm文件中（每个存储引擎都会有）。表中数据存在.MYD文件中。索引存在.MYI文件中。

多个索引：

每个索引都存有每条数据的地址，一旦有变，维护起来比较耗时。

InnoDB：

数据就存在索引的叶子节点中。
辅助索引：

其他索引存主键，再从主键索引中找数据。

五、补充

联合索引列选择原则

1、经常用的列优先 【 最左匹配原则】
2、选择性（ 离散度） 高的列优先【 离散度高原则】（列的离散性越高，选择性就越好。）
3、宽度小的列优先【 最少空间原则】

覆盖索引

如果查询列可通过索引节点中的关键字直接返回， 则该索引称之为覆盖索引。
覆盖索引可减少数据库IO， 将随机IO变为顺序IO， 可提高查询性能。

比如创建索引：create index idx_name_phoneNum on users(name,phoneNum);
查询语句：select name,phoneNum from user where name=?
可直接从索引树中返回关键字，不会再去查数据内容。

六、总结

（1）索引列的数据长度能少则少。
（2）索引一定不是越多越好， 越全越好， 一定是建合适的。
（3）匹配列前缀可用到索引 like 9999%， like %9999%、 like %9999用不到索引。
like 9999%得看情况，如果索引列离散性高，就能用到索引，离散性低，就用不到索引。
（4）Where 条件中 not in 和 <>操作无法使用索引。
（5）匹配范围值， order by 也可用到索引。
（6）多用指定列查询， 只返回自己想到的数据列， 少用select *。
（7）联合索引中如果不是按照索引最左列开始查找， 无法使用索引。在执行常量等值查询时，改变索引列的顺序并不会更改explain的执行结果，因为mysql底层优化器会进行优化，但是推荐按照索引顺序列编写sql语句。
（8）联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引。(索引列为name,age的话，name=‘zhangsan’ and age>20)
（9）联合索引中如果查询中有某个列的范围查询， 则其右边的所有列都无法使用索引。(索引列为age,name的话，age>20 and name=‘zhangsan’)

优化口诀：
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用。