Mysql-索引的创建规则和使用场景以及组合索引的重点讲解！（入木三分，简洁明了）

前言：之前的项目中一直用Oracle数据库，所以对Oracle的细节理解方面要远胜于MySQL，这家公司用的MySQL，这几天在做项目优化，看到之前的表中有的没建索引，有的乱建索引（建了没用到），所以我又重新梳理了一遍，正好对MySQL索引研究了研究，特写下此文，供后来者参考。

这里我不会讲到索引的概念还有内部的详细原理，我会根据场景讲解什么时候要建索引，怎么建索引，怎么用索引，索引在什么查询语句中会失效等问题。

正文：

   1.    建立索引的目的是什么？就是为了快快快！

   2. 为什么使用索引查询会变快？广义来讲：就是用空间来换取时间，索引就是内存块。

MySql索引类型：

普通索引INDEX：加速查找（需自己创建）

唯一索引：
    -主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复, 在创建表的时候自动生成的）
    -唯一索引UNIQUE:加速查找+约束（不能重复，需自己创建）

联合索引（组合索引）：
    -PRIMARY KEY(id,name):联合主键索引
    -UNIQUE(id,name):联合唯一索引
    -INDEX(id,name):联合普通索引

#我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引

------------------------------------------------------------------------------------------------

问题一：什么表都需要建立索引吗?

创建表的目的是什么？是实现业务！举个例子：如果一张表中只是存储了一个公司内部各位董事的个人信息，这家公司就算再大，董事也不会太多，所以没必要创建索引，因为数据量太少的话，及时创建了索引，Mysql也会进行全表扫描（数据库很智能，它会选择它认为最快的执行计划）；

如果一张表是订单业务表，那就必须要创建索引。

问题二：创建什么样的索引最合适？

还是归结于业务。比如一个电商内部管理系统，创建的订单表该怎么加索引？开发人员至少要想到两点：

1.系统使用者的查询习惯，比如使用者的查询习惯是城市city,习惯查询某个城市的订单信息，这时候 city这个字段可以作为单索引。

比如使用者习惯查询 某个城市在某个时间单的订单信息，那么最好的方式是创建 组合索引：idx_city_time(city,time);

有些人会说分别创建两个单独的索引（idx_city 和  idx_time）不行吗？

答：针对上述场景是不行的，因为在MySQL执行的时候，一张表只会有一个索引生效

解析：

两个单字段索引：

select * from order where city='BJ' and time<'2020-12-12'；两个单独索引的话，idx_city会生效，idx_time不会生效。

我讲一下这个SQL的where后面条件大概查询过程：

（1）MySQL where条件是从左到右执行的，先执行city='BJ'，city这个字段有索引，所以BJ这个值是直接从索引中查询，而不是中表中搜索的（我们前面说了，索引是单独的内存区间），比如符合这个条件的有5条数据，索引中就会有5条，然后再根据这5条索引中的数据找到表中的5条数据，而不是直接去表中全表搜索符合条件的数据。

假如：select city from order where city='BJ'； 因为city这个字段的数据在索引中，直接获取到了，就无须通过索引再到表中获取。

（2）通过idx_city找到的表中 5条数据，然后再从这些数据中全表查询符合time<'2020-12-12' 条件的数据。即使time有索引也不会走。

一个组合索引：

如果是一个组合索引idx_city_time(city,time)，同样的SQL，select * from order where city='BJ' and time<'2020-12-12'；

where后面的条件 city 和time，都会先走索引，由于搜索结果是 * ，所以再通过索引找到表中对应的数据。

 

这里我重点讲一下组合索引！！！

最左前缀匹配原则

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，示例：
对列col1、列col2和列col3建一个联合索引

?

1	KEY test_col1_col2_col3 on test(col1,col2,col3);

联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。

?

1	SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4”

上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。

注意

索引的字段可以是任意顺序的，如：

?

1 2	SELECT * FROM test WHERE col1=“1” AND clo2=“2” SELECT * FROM test WHERE col2=“2” AND clo1=“1”

这两个查询语句都会用到索引(col1,col2)，mysql创建联合索引的规则是首先会对联合合索引的最左边的，也就是第一个字段col1的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个字段col2进行排序。其实就相当于实现了类似 order by col1 col2这样一种排序规则。

有人会疑惑第二个查询语句不符合最左前缀匹配：首先可以肯定是两个查询语句都保函索引(col1,col2)中的col1、col2两个字段，只是顺序不一样，查询条件一样，最后所查询的结果肯定是一样的。既然结果是一样的，到底以何种顺序的查询方式最好呢？此时我们可以借助mysql查询优化器explain，explain会纠正sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。

为什么要使用联合索引

减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！

 

我下一章会讲一下MySQL 查询语句的执行顺序，希望小伙伴们留言！

 

江湖险恶，我从来都不轻易留下我的名字~