《土豆干货》Mysql索引底层原理

**

土豆原创，不说废话，把mysql索引的一部分理解拉出来给大家分享。欢迎补充指正。

**

一、MYSQL的存储引擎：MyISAM和InnoDB
1、InnoDB
这是MySQL 5.5或更高版本的默认存储引擎。它提供了事务安全(ACID兼容)表，支持外键引用完整性约束。它支持提交、回滚和紧急恢复功能来保护数据。它还支持行级锁定。当在多用户环境中使用时，它的“一致非锁定读取”提高了性能。它将数据存储在集群索引中，从而减少了基于主键的查询的I/O。
2、MyISAM
该存储引擎管理非事务性表，提供高速存储和检索，支持全文搜索。
注意：存储引擎不是针对于数据库的，是针对于表的。在MySQL5.5以上的版本中，同样可以建立以MyISAM为存储引擎的表。大多数情况下，我们使用的表都是InnoDB存储引擎，所以以下均针对于以InnoDB为存储引擎的表。
二、索引
1、Unique 唯一索引
唯一索引，不允许具有索引值相同的行，从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值，并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。该索引除提高查询速度这一任务外，往往被用来作为数据唯一性的约束。注意，该索引在插入和更新时，会先查询该表中师傅存在重复值，也就是说，会对表先加S锁，后加X锁。如果其他操作刚好在S锁后，加了X锁，就会造成死锁。这种情况的死锁Innodb引擎会选择代价较小的操作回滚。建议：当无唯一性约束要求时，使用普通索引。
2、Normal 普通索引
普通索引的唯一任务为：提高查询速度。·大部分情况下，普通索引是可以满足要求的。
3、主键索引
必须为主键字段创建一个索引，这个索引就是所谓的“主索引”。主索引与唯一索引的唯一区别是：前者在定义时使用的关键字是PRIMARY而不是UNIQUE。（Innodb表中在没有默认主键的情况下会生成一个6byte空间的自动增长主键。当在表里设置了主键之后，_rowid就是对应主键，该主键只对mysql内部可见。即哪怕表中无主键，同时对一张表进行插入，也会出现AUTO_INC锁）。
三、索引类型
索引类型分为BTREE以及HASH（创建索引时的索引方式）
1、btree
btree结构的索引在底层数据结构上的选择为B+树。https://www.jianshu.com/p/71700a464e97这里有B+树的一些性质。这种数据结构就导致了作为索引的字段最好不要出现以下情况：
1）重复。索引key重复会降低查询性能
2）key值插入顺序不规则。例如，已经存在的key为1,3,8.此时如果在插入4，很有可能会造成索引B+树的结构变化，需要更长的时间去维护索引树
3）key值过长。正常情况下，索引B+树深度最大为3。根节点存于Mysql为其划分的内存中。当key值过长，根节点超出了内存大小时，B+树深度就会+1，如此循环。B+树深度每+1，查询时就会增加一次I/O操作，性能大幅度下降。
4）表中数据过多同上，三层B+树一般可以存储2100万数据多一点。如果超出了这个数据量，索引树深度也会增加。这也是单表数据量达到两千万被建议分表的原因。
2、hash
hash结构的索引工作原因为hash算法。将key进行hash运算，所得到的值，根据hash表存储到相应的磁盘模块中。当获取时，同样根据hash运算得到位置，一次I/O即可取出数据。针对于单key的数据查询，这种索引类型是比btree更快的。但是其缺点为，范围查询性能极低。当且仅当该字段只会被用于=查询时，建议使用该类型索引。实际上这种场景少之又少，所以hash结构的索引比较少见。
四、执行计划
每一条SQL执行前，建议使用explain观察其执行计划。explain执行计划可以观察SQL执行检索类型、可能用到的索引类型。实际用到的索引类型、索引key长度、检索的索引行数、其他排序方式。其中有三处尤为需要注意。
1、type为all。该字段含义为该SQL所执行的查询方式。ALL代表着全表查询。如果不需要全部的表数据，全表查询一定要避免。
2、key为null。该字段含义为该SQL使用的索引。为null代表着不使用索引。这种情况，type基本上都是ALL，也就是全表查询。如果该查询语句不是快照读的话，会锁全表。而且性能极差。必要的时可以使用force index(索引名称)强制指定索引。
3、extra出现filesort。该字段出现filesort，一般原因为SQL语句中出现了order by。filesort代表着使用了内存或磁盘排序，这种排序方式很慢而且耗费机器性能。建议避免这种情况。order by排序字段最好为索引字段（索引方式要为btree）。根据b+树结构可知，索引树的位置本身就是有顺序的，排序其实就是按顺序输出就可以。