MySQL索引八大法则之上篇

数据库 MySQL
索引优化的目的主要是让索引不失效,走正确的索引,今天主要分享的是最近整理的索引八大法则上篇,看完的话面试考索引应该没问题了。

索引优化的目的主要是让索引不失效,走正确的索引,今天主要分享的是最近整理的索引八大法则上篇,看完的话面试考索引应该没问题了~

下面主要以实验来帮助大家理解~

MySQL索引

一、最佳左前缀法则

1. 定义

在创建了多列索引的情况下,查询从索引的最左前列开始且不能跳过索引中的列。

最佳左前缀法则就是说如果创建了多个索引,在使用索引时要按照创建索引的顺序来使用,不能缺少或跳过,当然如果只使用最左边的索引列,也就是第一个索引是可以的。

2. 环境准备

  1. DROP TABLE IF EXISTS `tb_emp`; 
  2. CREATE TABLE `tb_emp` ( 
  3.   `id` int(11) NOT NULL AUTO_INCREMENT, 
  4.   `name` varchar(20) NOT NULL, 
  5.   `age` int(11) NOT NULL, 
  6.   gender varchar(10) NOT NULL, 
  7.   email varchar(20), 
  8.   PRIMARY KEY (`id`) 
  9. ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  10. INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Tom', '22','male','1@qq.com'); 
  11. INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Mary', '21','female','2@qq.com'); 
  12. INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Jack', '27','male','3@qq.com'); 
  13. INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Rose', '23','female','4@qq.com'); 

3. 创建组合索引

  1. create index idx_all on tb_emp(name,age,gender); 
  2. show index from tb_emp; 

mysql索引八大法则之上篇

 

这里用火车头代表name,车厢代表age,车尾代表gender。

4. 只有火车头

mysql索引八大法则之上篇

说明:

  • 索引的创建顺序为name,age,gender;
  • 直接使用name(火车头)作为条件,可以看到type=ref,key_len=82,ref=const,效果还行。

5. 只有车厢

mysql索引八大法则之上篇

说明:没使用火车头(name),直接用车厢,导致走全表扫描(type=ALL)

6. 火车头加车厢、火车头加车尾

mysql索引八大法则之上篇

mysql索引八大法则之上篇

说明:

火车头加车厢、火车头加车尾,虽然都是type=ref,但是观察key_len和ref两项,并对比只有火车头中的结果,可得出在使用火车头(name)和车尾(gender)时,只使用了部分索引也就是火车头(name)的索引。

通俗理解:火车头单独跑没问题,火车头与直接相连的车厢一起跑也没问题,但是火车头与车尾,如果中间没有车厢,只能火车头自己跑。

7. 火车头加车厢加车尾

mysql索引八大法则之上篇

说明:火车头加车厢加车尾,三者串联,就变成了奔跑的小火车。type=ref,key_len=128,ref=const,const,const。

二、索引列不做计算

在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效从而转向全表扫描。

1. 函数计算

mysql索引八大法则之上篇

说明:这里使用了函数计算,type=ALL,导致索引失效。

2. 隐式类型转换

mysql索引八大法则之上篇

说明:这里'123'是字符串,而123是数字,发生了隐式类型转换,导致全表扫描(type=ALL)

三、范围右边索引列全失效

存储引擎不能使用索引中范围右边的列,也就是说范围右边的索引列会失效。

mysql索引八大法则之上篇

对以上4个SQL进行分析:

  • 条件单独使用name时,type=ref,key_len=82,ref=const。
  • 条件加上age时(使用常量等值),type=ref,key_len=86,ref=const,const。
  • 当全值匹配时,type=ref,key_len=128,ref=const,const,const。说明索引全部用上,从key_len与ref可以看出。
  • 当使用范围时(age>27),type=range,key_len=86,ref=Null,可以看到只使用了部分索引,但gender索引没用上。

结论:范围右边的索引列失效。

四、尽量使用覆盖索引

1. 覆盖索引定义

如果一个索引包含(或覆盖)所有需要查询的字段的值,称为‘覆盖索引’。即只需扫描索引而无须回表。

只扫描索引而无需回表的优点:

  • 索引条目通常远小于数据行大小,只需要读取索引,则mysql会极大地减少数据访问量。
  • 因为索引是按照列值顺序存储的,所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
  • 一些存储引擎如myisam在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系统调用
  • innodb的聚簇索引,覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询)

覆盖索引必须要存储索引列的值,而哈希索引、空间索引和全文索引不存储索引列的值,所以mysql只能用B-tree索引做覆盖索引。

当发起一个索引覆盖查询时,在explain的extra列可以看到using index的信息

2. 对比是否使用覆盖索引好处

尽量使用覆盖索引(查询列和索引列尽量一致,通俗说就是对A、B列创建了索引,然后查询中也使用A、B列),减少select *的使用。

  1. mysql> explain select * from tb_emp where name='Jack' and age=27 and gender='male'
  2. mysql> explain select name,age,gender from tb_emp where name='Jack' and age=27 and gender='male'

mysql索引八大法则之上篇

说明:对比两个sql,第一个使用select *,第二个使用覆盖索引(查询列与条件列对应),可看到Extra从Null变成了Using index,提高检索效率。

 

责任编辑:赵宁宁 来源: 今日头条
相关推荐

2014-12-12 15:47:56

张小龙微信公众平台

2016-02-15 10:57:39

SaaSSaaS运营SaaS服务

2015-10-12 09:03:53

程序员法则

2011-01-18 13:41:40

运维法则

2014-12-08 09:26:52

HTTP

2012-06-12 09:32:02

LinuxWinMac

2009-08-13 18:33:25

2013-12-25 10:09:42

2015-08-31 09:46:59

公有云云成本

2009-08-12 22:14:12

2009-06-22 14:07:46

JSF优势

2011-08-17 13:55:25

VoIPPBX

2022-01-05 09:26:56

IT灾难IT故障

2010-11-12 10:21:49

面试

2010-07-06 10:53:41

RationalJazz测试管理

2024-04-30 08:50:17

2009-11-04 14:30:22

2010-11-29 11:02:50

职场

2023-12-27 11:45:09

2010-11-22 10:44:13

点赞
收藏

51CTO技术栈公众号