你需要知道的一些索引基础知识

“ 对于高级开发，我们经常要编写一些复杂的sql，那么防止写出低效sql，我们有必要了解一些索引的基础知识。通过这些基础知识我们可以写出更高效的sql。”

01 索引的优点

• 大大减少服务器需要扫描的数据量，也就是IO量
• 帮助服务器避免排序和临时表（尽量避免文件排序，而是使用索引排序）
• 将随机IO变成顺序IO

02 索引的用处

• 快速查找匹配where子句中的行
• 如果可以在多个索引中选择，mysql通常会使用找到最少行的索引
• 如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行
• 当有表连接的时候，从其他表检索行数据
• 查找特定索引列的min和max的值
• 如果排序或者分组时可用索引的最左前缀完成的，则对表进行排序和分组
• 在某些情况下，可以优化查询以检索数据值而无需查找数据行

03 索引的分类

数据库默认建立的索引是给唯一键建立的

• 主键索引(唯一且非空)

• 唯一索引(唯一可为空)

• 普通索引(普通字段的索引)

• 全文索引(一般是varchar,char,text类型建立的，但很少用)

• 组合索引(多个字的建立的索引) 

04 索引的技术名词

1. 回表

name字段是普通索引，从name列的B+树找到主键，再从主键的B+树找到最终的数据，这就是回表。(主键索引的叶子节点保存的是列的所有数据，但是普通所有的叶子结点保存的是对应的主键ID）

如图：一个use表中name建立的索引结构sql是select * from use where name="sun"首先会通过name这个非主键索引找到索引key=sun对应的主键Id=2,然后通过id=2在主键索引中找到整个行数据，

并返回，这个就是回表。

 

2. 覆盖索引

在非主键索引上可以查询到所需要的字段，不需要回表再次查询就叫覆盖索引。

如上图name索引，sql是 select id,name from user where name =1 ，id的值在第一步非主键索引就已经有了，就不需要根据ID到主键索引中查询行数据了。

3. 最左匹配

组合索引中 先匹配左边，再继续向后匹配；比如user表中有name+age组成的联合索引，select * from user where name=“纪先生” and age = 18 就符合最左匹配，可以用的索引。而select * from user where age = 18就不符合，用不到这个索引。

扩展；

如果是下面两个sql怎么建索引select * from user where name="纪先生" and age = 18 select * from user where age = 18由于最左匹配原则：只需要建立一个组合索引age+name即可

如果是下面三个sql又该怎么建索引呢

select * from user where name="纪先生" and age = 18select * from user where age = 18select * from user where name= "纪先生"建立name+age和age两个索引，或者建立age+name和name两个索引

看着这两种索引方式都可以，3个sql 也都能使用到索引，其实name+age和age更好，因为索引也是需要持久化存储的，占用磁盘空间，读取的时候也是占用内存的，name+age和age+name这两个占用是一样的，但是name和age单独比较，肯定age占用空间更少，name更长（索引越大，IO次数可能更多）

注意！注意！注意！：

在看很多文章的时候，经常看到一些对于最左匹配错误的举例：

如果索引是name+age的组合索引，sql是select * from user where age = 18 and name="纪先生"很多人认为这种是不能走索引。实际上可以的，mysql没有这么死板，他的优化器组件就是做这个工作的，会优化sql结构调整顺序的。

4. 索引下推

组合索引中尽量利用索引信息，来尽可能的减少回表的次数

案例：还是 name+age的组合索引如果没有索引下推的查询是 在组合索引中通过name查询所有匹配的数据，然后回表根据ID查询对于的数据行，之后在筛选出符合age条件的数据。索引下推就是组合索引中通过name查询匹配再根据age找到符合的数据ID，然后回表根据ID查询对应行数据，明显会减少数据的条数

05 索引匹配方式

mysql官网准备了一些学习测试的数据库，可以直接下载通过source导入到我们自己的数据库

官网地址：https://dev.mysql.com/doc/index-other.html

 

如上图下载zip, 其中包含了sakila-schema.sql和sakila-data.sql，如下命令导入到自己的数据库中

mysql> source /Users/ajisun/Downloads/sakila-db/sakila-schema.sql;

mysql> use sakila;

mysql> source /Users/ajisun/Downloads/sakila-db/sakila-data.sql;

需要通过explain来查看索引的执行情况，执行计划以前有文章详细讲过，具体参考执行计划explain

1. 全值匹配

指和某个索引中的所有列进行匹配，例如使用数据库sakila中的staff表

新建一个三个字段的联合索引：

mysql> alter table staff add index index_n1(first_name,last_name,username);

执行sql:

mysql> explain select * from staff where first_name="Mike" and last_name="Hillyer" and username="Mike"

 

其中的ref是三个const， 用到三个字段，能全匹配一条数据

2. 最左前缀匹配

只匹配组合索引中前面几个字段

执行sql:

explain select * from staff where first_name="Mike" and last_name="Hillyer";

 

ref只出现2个const,比上面全值匹配少一个，就只匹配了前面两个字段

3. 匹配列前缀

可以匹配某一列的的开头部分，像like属性

执行sql:

explain select * from staff where first_name like "Mi%";

 

type=range ,是个范围查询，可以匹配一个字段的一部分，而不需要全值匹配

所以如果有模糊匹配的字段在建立索引的时候不要放在索引的最前面，否则有索引也不能使用，如下

 

4. 匹配一个范围值

可以查找某一个范围的数据

explain select * from staff where first_name > "Mike";

 

5. 精确匹配某一列并范围匹配另一列

可以查询第一列的全部和另一列的部分

explain select * from staff where first_name = "Mike" and last_name like "Hill%";

6. 只访问索引的查询

查询的时候只需要访问索引，不需要访问数据行，其实就是索引覆盖

explain select first_name,last_name,username from staff where first_name="Mike" and last_name="Hillyer";

 

extra=Using index 说明是使用了索引覆盖，不需要再次回表查询。

其实一张表中有索引并不总是最好的。总的来说，只有当索引帮助存储引擎快速提高查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对应很小的表，大部分情况下没有索引，全表扫描更高效；对应中大型表，索引是非常有效的；但是对于超大的表，索引的建立和使用代价也就非常高，一般需要单独处理特大型的表，例如分区，分库，分表等。

MySQL高级相关更多内容，如事务，锁，MVCC，读写分离，分库分表等还在持续更新中，欢迎关注催更。

我是阿纪，用输出倒逼输入而持续学习，持续分享技术系列文章，以及全网值得收藏好文，欢迎关注公众号，做一个持续成长的技术人。