MySQL索引分析

---

一、索引是什么？

索引是数据库管理中的一个排序的数据结构，用于协助快速实现查找、更新表数据

1. 没有索引的情况下，则每一次查询都需要从头开始匹配数据，直到找到匹配的数据为止（全表扫描）
2. 有索引的情况下，则会先去索引的数据结构（磁盘地址+索引字段的值）里面匹配创建索引字段的值，匹配之后再通过磁盘地址去查找对应的值

二、INNODB存储引擎数据结构

使用BinarySearchTree（二叉搜索树）不符合需求

1. 左子树<父节点<右子树
2. 极端情况下可能会蜕变成线性链表，比如插入顺序1，2，3，4，5，6，7，达不到提高检索的能力（左子树和右子树的深度差太大）

使用AVL（平衡二叉树）

1. 左右子树的深度差绝对值不能超过1，降低了左右子树的深度差过大，避免蜕变成线性链表
2. 一张表索引占用的内存空间会随着表数据的增加而不断扩大，所以不可能把索引的信息进行内存存储，只能进行磁盘存储，数据库默认的读取的逻辑单位为16k，每一个索引节点存储着（键值+磁盘地址+左右子节点的引用），那么一次性IO只能获取一个索引树上的一个节点的相关信息，远远小于16k，相对应的IO操作也会更多，所以需要每一次IO操作获取多个节点的信息，以降低IO的次数

使用多路平衡查找树（Balanced Tree B树）

1. 一个磁盘块装多个子树，降低了树的深度，极大的提高纵向查询的效率
   每一个磁盘的默认大小为16k,假设索引键值大小为8b,指针为6b,数据大小为1024b,那么每一个磁盘块能包含的索引数据数约等于16 * 1024 /（1024+6+8）
   使用B+Tree
2. 提高每一个磁盘块的利用率，进一步降低树的深度，不会在根节点和内节点进行存储数据，只会在叶子节点才会存储数据，那么每一个非叶子节点的磁盘块装的索引数约等于16 * 1024 / （6+8），相比于b树，极大的的降低了深度
3. 叶子节点增加了指向相邻叶子节点的指针，形成了一个有序链表的结构，支持范围查询

三、MyISAM和INNODB的比较

物理存储不一样

1. MyISAM的数据库结构，索引，数据分开三个文件存放
2. INNODB的数据库结构为一个文件，另外索引和数据存在同一个文件

叶子节点数据不一样

1. MyISAM的叶子节点无论是主键索引还是辅助索引状态下，都是不存放数据的，而是通过磁盘地址去查找对应的数据
2. INNODB的叶子节点在主键索引的情况下存储数据，而在辅助索引的情况下叶子节点存储索引和主键值，通过主键值再去查找主键索引B+树的叶子节点数据

事务

1. MyISAM不支持事务
2. INNODB支持事务

锁

1. MyISAM支持表锁
2. INNODB支持行锁

四、索引创建原则

列的离散度

1. count(distinct(column_name)):count(*)，离散度高证明越有区分度，走索引的概率越高

联合索引的最左匹配原则

1. alter trable table_name and index ‘index_name’(cloumn_name,…)
2. 联合索引的创建规则是根据索引创建字段的顺序进行有规律生成的，不能跳过匹配，对于查询语句条件中的字段前后顺序是可以经过优化器优化的，查询如果要走联合索引，理论上不能跳过第一个字段（但是满足一定的条件也会走索引）

覆盖索引

1. 回表：辅助索引无法查询出除主键值和索引值外的其它字段值，需要通过回表查询主键索引B+树来获取其它字段的值（会带来性能的消耗）
2. 覆盖索引，explain->extra字段包含using index，即使查询条件不满足最左匹配原则也会走索引

五、创建索引规则

1. 在用于where判断order排序和join的（on）字段上创建索引。
2. 索引的个数不要过多。
3. 区分度低的字段，例如性别，不要建索引。
4. 频繁更新的值，不要作为主键或者索引。
5. 符合索引把散列性高（区分度高）的值放在前面。
6. 创建复合索引，而不是修改单列索引。
7. 过长的字段，怎么建立索引？创建一个有区分度的字段保存过长字段的无序部分
8. 为什么不建议用无序的值（例如身份证、UUID ）作为索引？因为是无序的，不知道最终生成索引的位置在哪里，可能会导致b+树的分裂重构，带来性能上的开销，所以建议使用自增主键

六、索引失效

1. 索引列上使用函数、表达式，不是一个具体的值
2. 字符串类型的数字没有加引号，出现隐私转换
3. like条件前面使用%
4. 负向查询可能会导致索引失效？（<>， !=，not in），最终由mysql的优化器算法有关（成本计算模型）