本篇文章带大家深入了解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在主键索引中找到整个行数据,并返回,这个就是回表。

1.png

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…

2.png

如上图下载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'复制代码

3.png

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

2. 最左前缀匹配

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

执行sql:

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

4.png

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

3. 匹配列前缀

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

执行sql:

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

5.png

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

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

6.png

4. 匹配一个范围值

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

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

7.png

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

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

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

8.png

6. 只访问索引的查询

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

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

9.png

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

更多编程相关知识,请访问:编程视频!!

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


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

云服务器推荐