最通俗易懂的Mysql进阶索引详解

Mysql进阶索引详解

索引概述

什么是索引？
索引是能帮助高效获取数据的数据结构，在获取数据时，索引对应的字段会自动排序，从而能跟高效的得到数据。
索引的优缺点
优点：
1.能高效率的获取数据，降低数据库的io成本
2.自动排序，降低数据排序成本，减少cpu的消耗
缺点：
1.索引创建也是需要空间的
2.会降低更新表的效率，比如insert，update，delete

索引结构

B树（多路平衡查找树）
以一个度数最大为5的B树为例（每个节点最多能4个key，5个指针）：

假设以上图树为例，插入数据24，会插入到第二层中的第二个节点（23，25，26）中，若此时再去插入一个26，该节点6个指针，5个key，不满足条件，此时会中间节点向上分裂，当26插入进去时，25是中间节点，所以25分裂到上一个节点（20，30，62，89）中，上一个节点又有6个指针和5个key，当25插入进去，30为中间节点，向上分裂，就变为30为根节点，附属两个子节点（20，25），（62，89），这是B树的插入过程。

B+树
以一个度为4的B+树为例

B+树的插入过程也是和B树的插入过程大体相似，当插入的节点中超出节点规则，会中间节点向上分裂，此时根节点有中间节点一员，分裂为两个子节点，子节点会形成一个链表（左->右），分裂中间节点依然会存在右子节点中。
B+树所有的叶子节点都是已存在排过序数据的链表构成，而非叶子节点只起到索引，查找数据的作用。
在mysql中对索引进行了优化，将叶子节点的链表改为循环双向链表，提高了区间查询的效率。

为什么innodb选择B+树作为索引结构？
1.相对于二叉树，当顺序插入二叉树时，会造成最糟糕不平衡单向链表的形式，查询效率最低。此时会想到红黑树，红黑树本质上也是二叉树，相对于B+树，它层级较少，查询效率高。
2.相对于B树，B+树的非叶子节点是不存储数据的，而B树无论是叶子节点还是非叶子节点都会保存数据，这样会导致一页（非叶子节点）中存储的键值减少，指针也跟着减少，在相同数据量下，B树只能增加层级，这样就会降低查询效率。

索引分类

主键索引：在创建表的时，指定主键的列就是主键索引列，primary key

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会创建主键索引，

唯一索引：在创建表时，指定列为unique条件的列就为唯一索引，要保证列值的值唯一
CREATE UNIQUE INDEX index_name ON table(column(length))

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须一。

普通索引：指表中的任何列都可以充当索引，CREATE INDEX index_name ON table(column(length))

这是最基本的索引，它没有任何限制。

全文索引

FULLTEXT索引用于全文搜索。MySQL5.6.4之后，InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR， VARCHAR和 TEXT列。

根据索引的存储形式又分为：
聚集索引：聚集索引本质上就是主键索引，如果一个表中没有主键，则需要唯一索引充当聚集索引，B+树的叶子节点保存的是整行数据

将数据存储和索引存放到一块，索引结构的叶子节点保存了行数据

二级索引：二级索引表示B+树的叶子节点存储的是该数据行的主键，和聚集索引的区别就是二级索引需要回表查询，查询的效率比较低。

将数据和索引分开存放，索引结构的叶子节点保存行数据的主键

索引使用

联合索引
联合索引是指对表上的多个列进行索引。
最左前缀法则：假设索引为（a，b，c），若查询的条件中没有a索引的存在，而是其他字段，此时这个联合索引将没有作用。
索引创建：alert table test add INDEX sindex (aaa,bbb,ccc)
范围查询时，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

索引失效
1.索引列运算
尽量不要在索引列上运算，否则将会失效。比如说一个Char类型的索引列在查找条件的时候，进行subString函数的运算时查询，此时该列的索引将会失效，进行全表扫描。

2.字符串不加引号
字符串类型的字段使用时，不加引号，索引将会失效。比如说一个Char类型的索引列（Phone）在查找条件的时，使用phone=10010作为查询条件，也会查出来，此时会索引没用到，失效，因为里面存在类型转换。

3.头部模糊查询
在索引列作为模糊查询条件时，如是头部模糊查询，则索引列失效。比如“%软件”，“%工程%”都会失效.

4.or连接查询
在索引列用or作为查询条件时，如果一边的条件是索引列，一边不是索引列，则索引列的使用将会失效。

5.数据分布影响–否定语句，范围查询
在进行范围查询的时候，若查出的数据MySQL会自行判断，查询出来走索引查询比全文查询还慢，则mysql会不走索引，否定语句也是一样。

sql提示
sql提示是优化数据库查询的一个重要手段，就是加入人为操作来达到优化的目的。比如说一个列即包含的该列的单列索引（效率高），又包含与其他列组合的联合索引，此时数据库查询时，会默认选择联合索引，需要人为操作替换使用单列索引来替换联合索引。sql提示语有use index，ignore index，force index

use index
建议mysql使用索引，看查询出来的效率高不高，mysql会自动判断使不使用建议的索引

ignore index
忽略索引

force index
强制mysql使用索引

覆盖索引
select name from student where name=‘lll’，这就是使用覆盖索引查询，查询出来的列刚好是条件指定的列，此时就不需要回表查询，效率比较高。若explain一条sql语句查看其执行计划，最后一列出现user index condition 就表示需要回表查询，这样的效率比较低。

前缀索引
当字段类型为字符串(varchar, text等),时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘lO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

单列索引和联合索引的选择
MySQL会自行判断哪个索引的使用效率高，不需要回表查询的索引就使用哪个索引。

索引设计原则

表：
对数据量大，查询频繁的表建立索引

字段：
1.针对于常作为查询条件（where)、排序(order by)、分组 (group by）操作的字段建立索引。
2.如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

索引：
1.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
2.要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
3.如果索引列不能存储NULL值，请在创建表时使用NOTNULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。