MySQL索引

【快速回顾】
MySQL索引在内存中是哈希表的结构，存储引擎中是树结构。
                                     ↓
树分为二叉(排序/查找)树和多叉(排序/查找)树。(排序树和查找树是同个概念都是最基本的树结构)
                                     ↓
为了不使树的深度过大退化成链表，所以树需要会自行翻转折叠，于是有了平衡树的概念。
                                     ↓
二叉平衡树有红黑树、AVL树，多叉平衡树有B-树、B+树。

Innodb由于数据和索引是以聚集的形式存储的，所以才有回表和覆盖索引的概念；MyISAM则没有这种说法。（聚集索引和非聚集索引、回表和覆盖索引）

常见的数据结构：

• Hash表(数组+链表的结构，有hash冲突的问题且无法进行范围查找)
• 二叉树(二叉排序树递增插入的时候会退化成链表)
• 红黑树(非严格的二叉平衡树，树的层数会相对多一些，但是插入性能相对会提升，HashMap的底层从1.8开始引入树化的概念，其中的树结构便是用红黑树实现)
  红黑树详解，红黑树与AVL树区别 - 朱子威 - 博客园
• AVL树(严格的二叉平衡树，左右两个子树高度差不能大于1，这会把树的层数打薄但是插入性能会降低)
• B-树(多叉平衡树，所以大大降低了树的深度，但由于叶子节点不存储指针所以区间访问的时候会回旋查找，非叶子节点也存储data所以三层结构只能存储几千条索引数据)
  
• B+树（多叉平衡树，三层结构即可存储2000万条的索引数据）
  

聚集索引与非聚集索引：

 
                            表名.frm、表名.MYI、表名.MYD

 
                                      表名.frm、表名.ibd

MySQL的体系结构：

        连接池、SQL接口、解析器、优化器、执行引擎、缓存、文件系统

 

索引的目的是减少IO次数，使大数据量情景下的查询效率更高。

【回表】：当使用其他索引的时候，叶子节点并不包含整行的数据值，需要去主键索引中进行数据查找的过程叫做回表。回表即表示，回到数据所在的那颗B+树进行查询。eg： select * from table where name = zhangsan;

【覆盖索引】：当普通的B+树包含要查询的所有字段时，可以不用回表，此时称之为覆盖索引。eg: select id,name from table where name = zhangsan;

【最左匹配原则】：适用于组合索引的情况（name,age创建组合索引）。类似于三级联动，必须先匹配到第一个列，然后匹配到第二个列，然后匹配到第三个列。

select * from table where name = ? and age = ?                        （会走索引）

select * from table where age = ?                                               （不会走索引）

select * from table where name = ?                                            （会走索引）

select * from table where age = ? and name = ?                        （优化器会帮我们优化条件顺序，然后走索引）

【索引下推】：mysql5.7版本以后才有的一个特征，即原来在server层（进程）做的数据过滤操作下推到存储引擎（磁盘文件）中来完成。执行mysql -> show variables like '%op%';，通过index_condition_pushdown = on来设置，默认是开启的。

select * from table where name = ? and age = ? 

没有索引下推之前：先根据name的值去存储引擎中把数据取回来，然后在server层再做age的条件过滤。

有索引下推以后：根据name和age两个列的值去存储引擎中直接做数据筛选，不需要在server层做条件过滤了，显然效率会更高。

*【谓词下推】：select t1.name, t2.name from t1 join t2 on t1.id = t2.id; 有两种执行方式，一是把所有的字段先做表关联，然后再从关联好的表中选择需要的4个字段；另一种是先把两张表需要的4个字段取出，然后再做表关联。这里说的谓词下推显然是优化后的第二种方式。另外，谓词下推对于where条件的优化，会先进行条件过滤然后再做表过滤。

---

Q：索引的数据结构？

A：在InnoDB和MyISAM存储引擎中是使用B+树索引，在内存中是使用的哈希结构（哈希索引有不可避免的哈希碰撞问题，而且无法针对范围查找）的索引。

Q：索引的分类？

A：按DBA使用的角度分，主键索引（唯一且非空）、唯一索引、普通索引、全文索引（es、solar）、组合索引；按照数据结构分，B+树索引、哈希索引；按照数据和索引存储方式分，聚集索引和非聚集索引；对于InnoDB还存在着回表和索引覆盖。

Q：B-树和B+树的区别？

• B-树和B+数都是多叉平衡树，通过横向扩展解决了二叉树在大数据量下层数过高（层级过多会使磁盘IO次数过多），查询效率慢的问题。
• B+树是B-数的优化，由于非叶子节点不存储实际的数据Value值，可节省更多的页空间，非叶子节点的层级可容纳更多的索引Key。同是三层结构下，B+树可存储千万级别的数据，而B-树只能存储几千条数据。所以相同数据量的情况下，B+树会比B-树更矮，深度更浅，查询更快。
• 对于叶子节点的结构，B+树所有的Key和Vaule都会完整地保存在一个双向指针的链表中，并且是按递增顺序存储，对于给定区间范围的查询可以快速响应。而B-数在进行区间访问的时候会回旋查找，效率不及B+树。

Q：聚集索引和非聚集索引的区别？

A：聚集索引和非聚集索引都是针对B+树而言的。区别在于Innodb将数据和索引存储在一个文件中，主键索引的B+树的叶子节点即存储着所有列的数据，普通索引的B+树的叶子节点存储的则是主键（为什么普通索引结构的叶子节点存储的是主键值？因为这样可以节省存储空间然后也能提高维护数据一致性的效率）；而MyISAM使用的是非聚集索引，将数据和索引分别存储在.MYD和.MYI的文件中，主索引和普通索引的B+树的叶子节点存储的都是表的相应行数据的地址，索引定位到叶子节点后需要寻址到另一个文件才能读取到行数据，MyISAM不存在回表。
       聚集索引在主键索引的情景下因为少做一次磁盘IO，所以效率更高；而在非主键索引的情景下，可能会存在回表，所以这个时候的效率和非聚集索引差别不大。

Q：说说索引覆盖和回表？

A：索引覆盖和回表是一个相对立的概念，且都是针对Innodb存储引擎的非主键索引说的。当使用非主键索引时，叶子节点并不包含需要select出来的字段信息时，需通过回到主键索引所在的树结构进行查找的这么一个过程叫做回表。索引覆盖即不需要做回表，就可以得到要查询的字段信息。

Q：为什么Innodb的非主键索引结构叶子节点存储的是主键值？

A：保持一致性和节省磁盘空间。对数据库表在做DML操作时，如果叶子节点存储的是所有列的数据，则需要增大维护的开销才能保持数据的一致性，如果存储的是主键，则无需进行修改。节省存储空间很好理解，数据和主键索引通过B+树的结构组织在一起，如果非主键索引叶子节点也存储完整的数据，那么相当于数据拷贝了多份，海量数据的情况下必定会占据大量的磁盘空间。

Q：为什么Innodb表必须有主键，且推荐使用整型而不是UUID？

A：因为Innodb引擎的表数据存储是通过和主键索引的B+树存储在一起的，但是如果没有设立主键的话，MySQL优化器也会识别选择一个唯一列或者创建能唯一区分的隐藏列来组织B+树存储数据。
     所以DBA最好是自己定义主键，推荐使用整型而不是UUID是因为在检索的时候，整型的比对会比字符型的比对效率更高；在新增数据时，由于B+树的叶子节点是通过指针的链接有序排列的，整型主键可设置按序生成(自增)的方式，将索引和数据通过Append追加的方式插入到数据结构中，而UUID由于是随机生成的，在插入到叶子节点的时候需要进行排序，会导致上层级的页分裂，需要不断维护B+树的结构，开销大且不稳定。

Q：为什么主键一般选择自动递增的？

A：涉及到索引的维护过程。如果主键是随机插入的话，由于B+树的特性，叶子节点需要保持顺序性，所以元素的插入会影响整个树的结构，可能会频繁地造成页分裂，影响性能。而如果主键本身是有序的话，那么新增的元素相当于是Append模式追加到叶子节点链表的末尾，会更稳定，从而保证性能。

Q：索引的优化？

• 索引不会包含有NULL值的列，所以数据库设计的时候不要让字段的默认值为NULL，注意是NULL的那一行无效。
• 创建索引的时候尽量选择数字型字段，因为数字型的比对效率高于字符型，只需比较一次即可。
• 使用短索引。例如有一个CHAR(255)的列，我们创建索引 CREATE INDEX index_name ON table_name(column(length))。
• MySQL的一次查询只会使用一个索引，如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。
• 避免在字段上进行运算或者函数操作。将select * from users where YEAR(create_date)<2007 改成 select * from users where create_date < '2007-01-01'；select id from t where num/2 = 100应改为 select id from t where num = 100*2。
• like语句操作。like "%aaa%"不会使用索引，而like "aaa%"可以使用索引。
• MySQL一个表的索引数最好不要超过6个，一般用于where子句中，只会对以下操作符才会走索引： <,<=,=,>,>=,between,in,not exists,exists以及某些时候的like（不以通配符%或_开头的情形）。eg:为了尽可能地走索引查询，我们可以使用id<5 union all id>5来替换id!=5，使用id not exist (5,10,15)来替换id not in (5,10,15)。
• 避免使用or逻辑，否则将导致引擎放弃使用索引而进行全表扫描。比如，使用select id from t where num=10 union all select id from t where num=20来代替selcet id from t where num=10 or num=20的查询。
• 慎用in逻辑，在嵌套子查询的情况下，外层查询会全表扫描，不走索引。如，使用select id from t1, (select id from t1 where id >10) t2 where t1.id = t2.id来代替select id from t1 where id in (select id from t2 where id >10)，此时可以明显提高效率。也可以使用exists代替in，比如使用select num from a where exists (select 1 from b where num = a.num)来代替select num from a where num in (select num from b)。

想要学习理财的大学生亦或是参加了工作想要入门股市提升自己财富的朋友，可以关注这个公众号。