Mysql--索引优化及最左原则剖析 -----好理解版

索引概念和作用

索引是一种使记录有序化的技术,它可以指定按某列/某几列预先排序,从而大大提高查询速度(类似于汉语词典中按照拼音或者笔画查找)。

索引的主要作用是加快数据查找速度,提高数据库的性能。

MySQL 索引类型

从物理存储角度上,索引可以分为聚集索引和非聚集索引。

1. 聚集索引(Clustered Index)

聚集索引决定数据在磁盘上的物理排序,一个表只能有一个聚集索引。

2. 非聚集索引(Non-clustered Index)

非聚集索引并不决定数据在磁盘上的物理排序,索引上只包含被建立索引的数据,以及一个行定位符 row-locator,这个行定位符,可以理解为一个聚集索引物理排序的指针,通过这个指针,可以找到行数据。

从逻辑角度,索引可以分为以下几种。

1.普通索引:最基本的索引,它没有任何限制。

2唯一索引:与普通索引类似,不同的就是索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。

3.主键索引:它是一种特殊的唯一索引,用于唯一标识数据表中的某一条记录,不允许有空值,一般用     primary key 来约束。主键和聚集索引的关系详见“问题详解”中的第4题。

4.联合索引(又叫复合索引):多个字段上建立的索引,能够加速复合查询条件的检索。

5.全文索引:老版本     MySQL 自带的全文索引只能用于数据库引擎为 MyISAM 的数据表,新版本 MySQL 5.6 的 InnoDB 支持全文索引。默认 MySQL 不支持中文全文检索,可以通过扩展 MySQL,添加中文全文检索或为中文内容表提供一个对应的英文索引表的方式来支持中文。

MySQL索引优化规则

可以通过以下规则对 MySQL 索引进行优化。

1.前导模糊查询不能使用索引。

例如下面 SQL 语句不能使用索引。

select * from doc where title like '%XX'

而非前导模糊查询则可以使用索引,如下面的 SQL 语句。

select * from doc where title like 'XX%'

页面搜索严禁左模糊或者全模糊,如果需要可以用搜索引擎来解决。

2.union、in、or 都能够命中索引,建议使用 in。

(1)union:能够命中索引。

示例代码如下:

 
  1. select * from doc where status=1

  2. union all

  3. select * from doc where status=2

直接告诉 MySQL 怎么做,MySQL 耗费的 CPU 最少,但是一般不这么写 SQL。

(2)in:能够命中索引

示例代码如下:

select * from doc where status in (1, 2)

查询优化耗费的 CPU 比 union all 多,但可以忽略不计,一般情况下建议使用 in

(3)or:新版的 MySQL 能够命中索引。

示例代码如下:

select * from doc where status = 1 or status = 2

查询优化耗费的 CPU 比 in 多,不建议频繁用 or。

3.负向条件查询不能使用索引,可以优化为 in 查询。

负向条件有:!=、<>、not in、not exists、not like 等。

例如下面代码:

select * from doc where status != 1 and status != 2

可以优化为 in 查询:

select * from doc where status in (0,3,4)

4.联合索引最左前缀原则(又叫最左侧查询)

如果在(a,b,c)三个字段上建立联合索引,那么它能够加快 a | (a,b) | (a,b,c) 三组查询速度。

例如登录业务需求,代码如下。

select uid, login_time from user where login_name=? andpasswd=?

可以建立(login_name, passwd)的联合索引。

因为业务上几乎没有 passwd 的单条件查询需求,而有很多 login_name 的单条件查询需求,所以可以建立(login_name, passwd)的联合索引,而不是(passwd, login_name)。

(1)建联合索引的时候,区分度最高的字段在最左边。

如果建立了(a,b)联合索引,就不必再单独建立 a 索引。同理,如果建立了(a,b,c)联合索引,就不必再单独建立 a、(a,b) 索引。

(2)存在非等号和等号混合判断条件时,在建索引时,请把等号条件的列前置。

如  where a>? and b=?,那么即使 a 的区分度更高,也必须把 b 放在索引的最前列。

(3)最左侧查询需求,并不是指 SQL 语句的 where 顺序要和联合索引一致。

下面的 SQL 语句也可以命中 (login_name, passwd) 这个联合索引。

select uid, login_time from user where passwd=? andlogin_name=?

但还是建议 where 后的顺序和联合索引一致,养成好习惯。

5.范围列可以用到索引(联合索引必须是最左前缀)。

范围条件有:<、<=、>、>=、between等。

范围列可以用到索引(联合索引必须是最左前缀),但是范围列后面的列无法用到索引,索引最多用于一个范围列,如果查询条件中有两个范围列则无法全用到索引。

假如有联合索引 (empno、title、fromdate),那么下面的 SQL 中 emp_no 可以用到索引,而 title 和 from_date 则使用不到索引。

select * from employees.titles where emp_no < 10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31'

6.把计算放到业务层而不是数据库层。

在字段上进行计算不能命中索引。

例如下面的 SQL 语句。

select * from doc where YEAR(create_time) <= '2016'

即使 date 上建立了索引,也会全表扫描,可优化为值计算,如下:

select * from doc where create_time <= '2016-01-01'

把计算放到业务层,这样做不仅可以节省数据库的 CPU,还可以起到查询缓存优化效果。

比如下面的 SQL 语句:

select * from order where date < = CURDATE()

可以优化为:

select * from order where date < = '2018-01-2412:00:00'

优化后的 SQL 释放了数据库的 CPU 多次调用,传入的 SQL 相同,才可以利用查询缓存。

7.强制类型转换会全表扫描

如果 phone 字段是 varchar 类型,则下面的 SQL 不能命中索引。

 select * from user where phone=13800001234

可以优化为:

select * from user where phone='13800001234'

8.更新十分频繁、数据区分度不高的字段上不宜建立索引。

更新会变更 B+ 树,更新频繁的字段建立索引会大大降低数据库性能。

“性别”这种区分度不大的属性,建立索引是没有什么意义的,不能有效过滤数据,性能与全表扫描类似。

一般区分度在80%以上的时候就可以建立索引,区分度可以使用 count(distinct(列名))/count(*) 来计算。

9.利用覆盖索引来进行查询操作,避免回表。

被查询的列,数据能从索引中取得,而不用通过行定位符 row-locator 再到 row 上获取,即“被查询列要被所建的索引覆盖”,这能够加速查询速度。

例如登录业务需求,代码如下。

Select uid, login_time from user where login_name=? and passwd=?

可以建立(login_name, passwd, login_time)的联合索引,由于 login_time 已经建立在索引中了,被查询的 uid 和 login_time 就不用去 row 上获取数据了,从而加速查询。

10.如果有 order by、group by 的场景,请注意利用索引的有序性。

Order by 最后的字段是组合索引的一部分,并且放在索引组合顺序的最后,避免出现file_sort 的情况,影响查询性能。

例如对于语句 where a=? and b=? order by c,可以建立联合索引(a,b,c)。

如果索引中有范围查找,那么索引有序性无法利用,如 WHERE  a>10 ORDER BY b;,索引(a,b)无法排序。

11.使用短索引(又叫前缀索引)来优化索引。

前缀索引,就是用列的前缀代替整个列作为索引 key,当前缀长度合适时,可以做到既使得前缀索引的区分度接近全列索引,同时因为索引 key 变短而减少了索引文件的大小和维护开销,可以使用 count(distinct left(列名, 索引长度))/count(*) 来计算前缀索引的区分度。

前缀索引兼顾索引大小和查询速度,但是其缺点是不能用于 ORDER BY 和 GROUP BY 操作,也不能用于覆盖索引(Covering Index,即当索引本身包含查询所需全部数据时,不再访问数据文件本身),很多时候没必要对全字段建立索引,根据实际文本区分度决定索引长度即可。

例如对于下面的 SQL 语句:

SELEC * FROM employees WHERE first_name='Eric'AND last_name='Anido';

我们可以建立索引:(firstname, lastname)。

12.建立索引的列,不允许为 null。

单列索引不存 null 值,复合索引不存全为 null 的值,如果列允许为 null,可能会得到“不符合预期”的结果集,所以,请使用 not null 约束以及默认值。

13.利用延迟关联或者子查询优化超多分页场景。

MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后返回放弃前 offset 行,返回 N 行,那当 offset 特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行 SQL 改写。

示例如下,先快速定位需要获取的 id 段,然后再关联:

selecta.* from 表1 a,(select id from 表1 where 条件 limit100000,20 ) b where a.id=b.id

14.业务上具有唯一特性的字段,即使是多个字段的组合,也必须建成唯一索引。

不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但提高查找速度是明显的。另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必然有脏数据产生。

15.超过三个表最好不要 join。

需要 join 的字段,数据类型必须一致,多表关联查询时,保证被关联的字段需要有索引。

16.如果明确知道只有一条结果返回,limit 1 能够提高效率。

比如如下 SQL 语句:

select * from user where login_name=?

可以优化为:

select * from user where login_name=? limit 1

自己明确知道只有一条结果,但数据库并不知道,明确告诉它,让它主动停止游标移动。

17.SQL 性能优化 explain 中的 type:至少要达到 range 级别,要求是 ref 级别,如果可以是 consts 最好。

consts:单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。

ref:使用普通的索引(Normal Index)。

range:对索引进行范围检索。

当 type=index 时,索引物理文件全扫,速度非常慢。

18.单表索引建议控制在5个以内。

19.单索引字段数不允许超过5个。

字段超过5个时,实际已经起不到有效过滤数据的作用了。

20.创建索引时避免以下错误观念

(1)索引越多越好,认为一个查询就需要建一个索引。

(2)宁缺勿滥,认为索引会消耗空间、严重拖慢更新和新增速度。

(3)抵制惟一索引,认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

(4)过早优化,在不了解系统的情况下就开始优化。

问题详解

这部分,我将列出平时会遇到的一些问题,并给予解答。

 

1. 请问如下三条 SQL 该如何建立索引

 
  1. where a=1 and b=1

  2. where b=1

  3. where b=1 order by time desc

MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引吗?

回答:             

第一问:建议建立两个索引,即 idxab(a,b) 和 idxbtime(b,time)。

第二问:MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引,对于上面的第一条 SQL,如果建立索引为 idxba(b,a) 也是可以用到索引的,不过建议 where 后的字段顺序和联合索引保持一致,养成好习惯。

2.假如有联合索引(empno、title、fromdate),下面的 SQL 是否可以用到索引,如果可以的话,会使用几个列?

 
  1. select * fromemployees.titles

  2. where emp_no between '10001' and'10010'

  3. and title='Senior Engineer'

  4. and from_date between '1986-01-01'and '1986-12-31'

回答:可以使用索引,可以用到索引全部三个列,这个 SQL 看起来是用了两个范围查询,但作用于 empno 上的“between”实际上相当于“in”,也就是说 empno 实际是多值精确匹配,在 MySQL 中要谨慎地区分多值匹配和范围匹配,否则会对 MySQL 的行为产生困惑。

3.既然索引可以加快查询速度,那么是不是只要是查询语句需要,就建上索引?

回答:不是,因为索引虽然加快了查询速度,但索引也是有代价的。索引文件本身要消耗存储空间,同时索引会加重插入、删除和修改记录时的负担。另外,MySQL 在运行时也要消耗资源维护索引,因此索引并不是越多越好。一般两种情况下不建议建索引。第一种情况是表记录比较少,例如一两千条甚至只有几百条记录的表,没必要建索引,另一种是数据的区分度比较低,可以使用 count(distinct(列名))/count(*) 来计算区分度。

4.主键和聚集索引的关系?

回答:在 MySQL 中,InnoDB 引擎表是(聚集)索引组织表(Clustered IndexOrganize Table),它会先按照主键进行聚集,如果没有定义主键,InnoDB 会试着使用唯一的非空索引来代替,如果没有这种索引,InnoDB 就会定义隐藏的主键然后在上面进行聚集。由此可见,在 InnoDB 表中,主键必然是聚集索引,而聚集索引则未必是主键。MyISAM 引擎表是堆组织表(Heap Organize Table),它没有聚集索引的概念。

5.一个6亿的表 a,一个3亿的表 b,通过外键 tid 关联,如何最快的查询出满足条件的第50000到第50200中的这200条数据记录?

回答:方法一:如果 a 表 tid 是自增长,并且是连续的,b表的id为索引。SQL语句如下。

select * from a,b where a.tid = b.id and a.tid>500000 limit200;

方法二:如果 a 表的 tid 不是连续的,那么就需要使用覆盖索引,tid 要么是主键,要么是辅助索引,b 表 id 也需要有索引。SQL语句如下。

select * from b, (select tid from a limit 50000,200) awhere b.id = a.tid;

6.假如建立联合索引(a,b,c),下列语句是否可以使用索引,如果可以,使用了那几列?(考察联合索引最左前缀原则)

 
  1. where a= 3

  2. 答:是,使用了 a 列。

  3.  
  4. where a= 3 and b = 5

  5. 答:是,使用了 a,b 列。

  6.  
  7. where a = 3 and c = 4 and b = 5

  8. 答:是,使用了 a,b,c 列。

  9.  
  10. where b= 3

  11. 答:否。

  12.  
  13. where a= 3 and c = 4

  14. 答:是,使用了 a 列。

  15.  
  16. where a = 3 and b > 10 andc = 7

  17. 答:是,使用了 a,b 列。

  18.  
  19. where a = 3 and b like 'xx%' andc = 7

  20. 答:是,使用了 a,b 列。

7.文章表的表结构如下:

 
  1. CREATE TABLEIF NOT EXISTS `article` (

  2. `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  3. `author_id` int(10) unsigned NOT NULL,

  4. `category_id` int(10) unsigned NOT NULL,

  5. `views` int(10) unsigned NOT NULL,

  6. `comments` int(10) unsigned NOT NULL,

  7. `title` varbinary(255) NOT NULL,

  8. `content` text NOT NULL,

  9. PRIMARY KEY (`id`)

  10. );

下面语句应该如何建立索引?

 
  1. Select author_id, title, content from `article` where category_id= 1 and comments > 1

  2. order by views desc limit 1;

回答:

没有联合索引时,explain显示,如下图所示:

创建 idxcategoryidcommentsviews(category_id,comments, views) 联合索引时,explain显示,如下图所示:

创建 idxcategoryidviews(categoryid,views) 联合索引,explain 显示,如下图所示:

由此可见,可以创建 idxcategoryidviews(categoryid,views) 联合索引。

结语

对于大数据量的业务,应该时刻考虑到性能,本文只是从使用层面提供了一些优化的思路,由于业务和数据的复杂性,需要具体问题具体分析,对于数据量比较大的业务,最好自己使用 explain 具体分析一下,同时,知其然知其所以然,有时间大家可以看看索引的底层实现原理。

 

首先是问题:

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_cid_INX` (`name`,`cid`),
  KEY `name_INX` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

随便建了一个student表做测试。

create INDEX name_cid_INX ON student(name,cid)
;
create INDEX name_INX ON student(name);

建了两个索引,故意这样建的。
执行1:

EXPLAIN SELECT * FROM student WHERE    name='小红';

依据mysql索引最左匹配原则,两个索引都匹配上了,这个没有问题。。
执行2:

EXPLAIN SELECT * FROM student WHERE   cid=1;

EXPLAIN SELECT * FROM student WHERE   cid=1 AND name='小红';

为什么还能匹配索引。

 

解答:

这是你的表结构,有三个字段,分别是id,name,cid 

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_cid_INX` (`name`,`cid`),
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

索引方面:id是主键,(name,cid)是一个多列索引。
-----------------------------------------------------------------------------
下面是你有疑问的两个查询:

EXPLAIN SELECT * FROM student WHERE   cid=1;

EXPLAIN SELECT * FROM student WHERE   cid=1 AND name='小红';

 

 

你的疑问是:sql查询用到索引的条件是必须要遵守最左前缀原则,为什么上面两个查询还能用到索引?
---------------------------------------------------------------------------------------------------------------------------

讲上面问题之前,我先补充一些知识,因为我觉得你对索引理解是狭隘的:
上述你的两个查询的explain结果中显示用到索引的情况类型是不一样的。,可观察explain结果中的type字段。你的查询中分别是:
1. type: index 
2. type: ref

解释:
index:这种类型表示是mysql会对整个该索引进行扫描。要想用到这种类型的索引,对这个索引并无特别要求,只要是索引,或者某个复合索引的一部分,mysql都可能会采用index类型的方式扫描。但是呢,缺点是效率不高,mysql会从索引中的第一个数据一个个的查找到最后一个数据,直到找到符合判断条件的某个索引。

所以:对于你的第一条语句:

EXPLAIN SELECT * FROM student WHERE   cid=1;

判断条件是cid=1,而cid是(name,cid)复合索引的一部分,没有问题,可以进行index类型的索引扫描方式。explain显示结果使用到了索引,是index类型的方式。

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

ref:这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引,而不是会对索引中每一个数据都进行一 一的扫描判断,也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找,索引却是有要求的,要实现这种能快速查找的算法,索引就要满足特定的数据结构。简单说,也就是索引字段的数据必须是有序的,才能实现这种类型的查找,才能利用到索引。

 

有些了解的人可能会问,索引不都是一个有序排列的数据结构么。不过答案说的还不够完善,那只是针对单个索引,而复合索引的情况有些同学可能就不太了解了。

下面就说下复合索引:
以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的:

mysql创建复合索引的规则是首先会对复合索引的最左边的,也就是第一个name字段的数据进行排序,在第一个字段的排序基础上,然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。

 

所以:第一个name字段是绝对有序的,而第二字段就是无序的了。所以通常情况下,直接使用第二个cid字段进行条件判断是用不到索引的,当然,可能会出现上面的使用index类型的索引。这就是所谓的mysql为什么要强调最左前缀原则的原因。

那么什么时候才能用到呢?
当然是cid字段的索引数据也是有序的情况下才能使用咯,什么时候才是有序的呢?观察可知,当然是在name字段是等值匹配的情况下,cid才是有序的。发现没有,观察两个name名字为 c 的cid字段是不是有序的呢。从上往下分别是4 5。
这也就是mysql索引规则中要求复合索引要想使用第二个索引,必须先使用第一个索引的原因。(而且第一个索引必须是等值匹配)。
---------------------------------------------------------------------------------------------------------------------------
所以对于你的这条sql查询:

EXPLAIN SELECT * FROM student WHERE   cid=1 AND name='小红';

没有错,而且复合索引中的两个索引字段都能很好的利用到了!因为语句中最左面的name字段进行了等值匹配,所以cid是有序的,也可以利用到索引了。

你可能会问:我建的索引是(name,cid)。而我查询的语句是cid=1 AND name='小红'; 我是先查询cid,再查询name的,不是先从最左面查的呀?

好吧,我再解释一下这个问题:首先可以肯定的是把条件判断反过来变成这样 name='小红' and cid=1; 最后所查询的结果是一样的。
那么问题产生了?既然结果是一样的,到底以何种顺序的查询方式最好呢?

所以,而此时那就是我们的mysql查询优化器该登场了,mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高,最后才生成真正的执行计划。所以,当然是我们能尽量的利用到索引时的查询顺序效率最高咯,所以mysql查询优化器会最终以这种顺序进行查询执行。

 

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页