深入了解MySQL中的索引（用处、分类、匹配方式）

本篇文章带大家深入了解MySQL中的索引，介绍一下索引的优点、用处、分类、技术名词以及匹配方式，希望对大家有所帮助！

对于高级开发，我们经常要编写一些复杂的sql，那么防止写出低效sql，我们有必要了解一些索引的基础知识。通过这些基础知识我们可以写出更高效的sql。【相关推荐：mysql视频教程】

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这个非主键索引找到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 = 18;
select * from user where name= "纪先生";

建立name+age和age索引，或者建立age+name和name索引,看着两个都可以。

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

注意！注意！注意！：

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

如果索引是name+age的组合索引，sql是select * from user where age = 18 and name="纪先生"很多人认为这种是不能走索引，实际上可以的。mysql的优化器会优化调整顺序的，调整成 name=”纪先生” and age = 18

4. 索引下推

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

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

05 索引匹配方式

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

官网地址：dev.mysql.com/doc/index-o…

如上图下载zip, 其中包含了sakila-schema.sql和sakila-data.sql,分别是sakila的库，表和数据的创建脚本。

mysql> source /Users/ajisun/Downloads/sakila-db/sakila-schema.sql;
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:

mysql> explain select * from staff where first_name='Mike' and last_name='Hillyer';

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

3. 匹配列前缀

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

执行sql:

mysql> explain select * from staff where first_name like 'Mi%';

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

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

4. 匹配一个范围值

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

mysql> explain select * from staff where first_name > 'Mike';

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

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

mysql> explain select * from staff where first_name = 'Mike' and last_name like 'Hill%';

6. 只访问索引的查询

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

mysql> explain select first_name,last_name,username from staff where first_name='Mike' and last_name='Hillyer';

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

更多编程相关知识，请访问：编程视频！！

以上就是深入了解MySQL中的索引（用处、分类、匹配方式）的详细内容，更多请关注亿码酷站其它相关文章！

<!––>深入了解MySQL中的索引（用处、分类、匹配方式）
—–文章转载自PHP中文网如有侵权请联系ymkuzhan@126.com删除

27410288普通用户

收藏 海报 链接