MySQL联合索引与最左匹配原则

以下是我在学习mysql时所记得笔记，如有错误请指正，谢谢，不喜勿喷。

索引是什么？

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

索引的分类有哪些？

• 单列索引
• 全文索引
• 联合索引
• 覆盖索引

为什么我们要用索引（索引的作用）？

• 索引能减少存储引擎需要扫描的数据量。
• 减少数据读取产生的IO，可以吧随机IO转换成顺序IO。
• 可以帮助我们在进行分组、排序等操作时产生的临时表。

mysql索引的底层是通过什么实现的？

• B+Tree

为什么不选择平衡二叉树？

• 太深，因为在存储时对于结构和数据量很复杂的表中AVL树的高度会变得异常庞大，我们都知道数的高度决定IO操作的次数。
• 太小，每一个磁盘块（节点/页）1页=4k，所以所保存的数据量太小。

为什么不选择多路平衡查找树（B树）？

• 虽然B树提高了IO效率，但是并没有解决元素遍历是的效率低的问题。

为什么不选择Hash索引？

  虽然Hash查询的时间复杂度只有O(1)，但是Hash所产生的键值每次都是唯一的，而且是一一对应的，但是在进行范围查询时，
hash索引就没法用了，原因是原本的键值是连续的，但是经过Hash运算后有可能变为不连续的了，所以没法进行范围查询，而且不能避免全表扫描。
还有一个原因是Hash也没法利用索引进行排序，而且也没法进行模糊查询（like关键字）其实本质也是范围查询。
而且哈希索引也不支持多列联查的最左匹配原则（我会在下文介绍最左匹配原则）。

为什么选择B+Tree？

• B+Tree 是根据B-Tree实现的得一种多路平衡搜索树，拥有B-Tree的优势
• B+Tree的扫表、扫库能力更强
• B+Tree的磁盘读写能力更强
• B+Tree的排序能力更强
• B+Tree的查询效率很强（个人见解）

B树和B+树的区别？

• B+节点关键字搜索采用了闭合区间。
• B+非叶子节点不保存数据，只保存关键字和子节点的引用。
• B+关键字对应的数据保存在叶子节点中
• B+叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系

B树和B+树的稀疏索引和密集索引的区别？

• 密集索引文件中每一个搜索码值都对应一个索引值。
• 稀疏索引文件只为索引码的某些值建立索引项。

Innodb和Myisam的比较？

结论

• Innodb中的索引是聚簇的，而Myisam是非聚簇的。
• Innodb只有一个IDB文件，里边存储了索引+数据，而Myisam有两个文件分别是MYI、MYD分别存储索引信息+数据。
• Innodb安全性更好。
• Innodb是行级锁定、Myisam是表级锁定而且不支持外键。
• Innodb支持事务、Myisam不支持事务。
• Myisam支持全文索引。

什么是最左匹配原则？

• 在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
• 对索引中关键字进行计算比对，一定是从左往右依次进行，且不可跳过。

最左匹配原则简单演示。

建立一个user表

CREATE TABLE t_user(
t_id INT(2) NOT NULL AUTO_INCREMENT,
t_name VARCHAR(20) DEFAULT NULL,
t_idcard VARCHAR(20) DEFAULT NULL,
t_age INT(1) DEFAULT NULL,
t_phone VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(t_id),
KEY name_idcard(t_name,t_idcard),
UNIQUE KEY user_phone(t_phone)
)ENGINE = INNODB DEFAULT CHARSET = utf8

在这里我设置了一个联合索引name_idcard，一个唯一索引user_phone，一个主键索引t_id。

在这里我向表中插入了2条数据：

1. 首先我们先来体会一下什么是联合索引。

首先执行以下语句

     EXPLAIN SELECT * FROM t_user WHERE `t_name` = '张三'  AND  `t_idcard` = '123'

我们看到了查询结果，用到了我们所建的联合索引。

2. 那么我们来交换一下"t_name"和 “t_idcard” 的顺序看一下运行结果
执行语句如下：

	EXPLAIN SELECT * FROM t_user WHERE `t_idcard` = '123' AND `t_name` = '张三'   

然而查询结果并没有变

这是为什么呢？这遵循最左匹配原则么?

    答案是肯定的，其中的原因是因为Mysql查询优化器起到了作用，mysql查询优化器会判断纠正这条sql语句该以什么
样的顺序执行效率最高，最后才生成真正的执行计划。可想而知我们用到联合索引查询时效率是最高的，所以mysql查询
优化器会自动以联合索引进行查询。

3. 如果我们只用其中一个字段进行查询能否会用到索引？
   于是我们首先执行以下语句

      EXPLAIN SELECT * FROM t_user WHERE `t_name` = '张三'   

结果还是没有变还是用到了联合索引。

我们再执行下边的语句

	EXPLAIN SELECT * FROM t_user WHERE `t_idcard` = '123'

神奇的一幕发生了并没有用到联合索引。最左匹配原则生效了

4. 接下来我们猜想一下我们只用联合中的索引一个字段，和一个不相干的字段进行查询结果又是啥样呢？
于是我们执行以下sql

		EXPLAIN SELECT * FROM t_user WHERE `t_name` = '张三'  AND `t_age` = 20 或者
		EXPLAIN SELECT * FROM t_user WHERE `t_name` = '张三'  AND `t_age` > 10

结果也是用到了联合索引。

我们再执行一下“t_idcard”和“t_age”查询的sql

		EXPLAIN SELECT * FROM t_user WHERE `t_idcard` = '123' AND `t_age` = 20 或者
		EXPLAIN SELECT * FROM t_user WHERE `t_idcard` = '123' AND `t_age` > 10

结果我们应该也猜到了，并没有用到索引。

结论

• 最左匹配原则是一个非常重要的原则，在Innodb数据库引擎中，mysql会一直向右匹配知道遇到查询范围（>、<、between、like）就停止匹配， 比如 a = 3 and b = 4 and c>5 and d = 6 如果建立（a，b，c，d）顺序的索引，d是用不到索引的，如果建立了（a,b,d,c）的索引则都可以用到索引， a,b,d的顺序可以任意调整。
• =和in可以乱序，以上我们证明过了，原因是优化器会帮你优化成索引可以识别的形式。
• where条件中not in和 <> 操作无法用到索引。
• 匹配范围值，order by ， group by 也可以用到索引 。
  注：在这我就不一一验证了。

扩展

建立联合索引时的注意事项

1. 优先给经常使用的列建立索引，需考虑到【最左匹配原则】。
2. 选择离散度高的列建立索引。
3. 宽度小的列优先选择。
   注：以上条件的优先级为：1>2>3。