首頁  >  文章  >  資料庫  >  歸納整理MySQL索引最佳化知識點

歸納整理MySQL索引最佳化知識點

WBOY
WBOY轉載
2022-06-09 15:55:131988瀏覽

這篇文章為大家帶來了關於mysql的相關知識,其中主要介紹了關於索引的相關問題,包括了索引優化的原則的相關內容,下面一起來看一下,希望對大家有幫助。

歸納整理MySQL索引最佳化知識點

推薦學習:mysql影片教學

前言:相信大家都知道索引可以大大提高MySQL的檢索速度,但真正在平常工作中寫SQL,真的會考慮到這條SQL如何能夠用上索引提升執行效率?這篇部落格詳細的介紹了索引優化的20個原則,只要在工作中能夠隨時應用到,相信你寫出的SQL能夠命中索引,效率槓槓的。

一、索引的分類

索引可以大幅提升MySQL的檢索速度。索引就像書中的目錄一樣為了讓我們更快的尋找到自己想要的數據,以下是MySQL常用的索引簡介。

 1.1、普通索引、主鍵索引與唯一索引

(1) 普通索引

這是最基本的索引,它沒有任何限制。

如何使用?

//方式1
ALTER TABLE table_name ADD INDEX index_name ( column )

例如:ALTER TABLE users ADD INDEX index_users( id )

//方式2
CREATE INDEX index_name ON table_name (column_name)

例如:CREATE INDEX index_users ON users (id)

(2) 唯一索引

#與普通索引類似,不同的是:索引列的值必須唯一,但允許有空值。如果是組合索引,則列值的組合必須一。

如何使用?

//方式1
ALTER TABLE table_name ADD UNIQUE [indexName] (column)

例如:ALTER TABLE users ADD UNIQUE index_users( id )

//方式2
CREATE UNIQUE INDEX index_name ON table_name (column_name)

例如:CREATE UNIQUE INDEX index_users ON users(id)

(3) 主鍵索引

它是一種特殊的唯一索引,不允許有空值。一般是在建表的時候指定了主鍵,就會自動建立主鍵索引, CREATE INDEX不能用來建立主鍵索引,使用 ALTER TABLE來取代。

如何使用?

//方式1
ALTER TABLE table_name ADD PRIMARY KEY ( column )

例如:ALTER TABLE users ADD PRIMARY KEY ( id )

方式2:建立表格的時候指定主鍵

 1.2、聚集索引和非聚集索引

(1)聚集索引

聚集索引,又叫叢集索引,所有的資料都存在聚集索引上,葉子節點直接對應數據,從中間級的索引頁的索引行直接對應資料頁。 InnoDB儲存引擎中的主鍵預設會建立一個聚集索引,每張表只能建立一個聚集索引。記錄的索引順序與物理順序相同,更適合between and和order by操作。

InnoDB聚集索引的葉子節點儲存行記錄,因此, InnoDB必須要有,且只有一個聚集索引:

(1)如果表定義了PK,則PK就是聚集索引;

(2)如果表沒有定義PK,則第一個not NULL unique列是聚集索引;

(3)否則,InnoDB會建立一個隱藏的row-id作為聚集索引

畫外音:所以PK查詢非常快,直接定位行記錄。

打個比方,一個表就像是我們以前用的新華字典,聚集索引就像是拼音目錄,而每個字存放的頁碼就是我們的資料物理位址,我們如果要查詢一個「哇」字,我們只需要查詢「哇」字對應在新華字典拼音目錄對應的頁碼,就可以查詢到對應的「哇」字所在的位置,而拼音目錄對應的A-Z的字順序,和新華字典實際儲存的字的順序A-Z也是一樣的,如果我們中文新出了一個字,拼音開頭第一個是B,那麼他插入的時候也要按照拼音目錄順序插入到A字的後面。

(2)非聚集索引

非聚集索引,又叫非叢集索引、輔助索引,所有的資料和索引的目錄是分開存的,葉子節點不存放具體的整行資料(葉子結點不直接指向資料頁),而是儲存的這一行的主鍵的值。

記錄的索引順序與物理順序無關。每張表可以有多個非聚集索引,需要更多磁碟和內存,多個索引會影響insert和update的速度。

畫外音:非聚集索引需要回表查詢,先定位主鍵值,再定位行記錄,因為要掃描兩遍索引樹,它的效能較掃一遍索引樹更低。

其實依照定義,除了聚集索引以外的索引都是非聚集索引,只是人們想細分一下非聚集索引,分成普通索引,唯一索引,全文索引。如果非要把非聚集索引類比成現實生活中的東西,那麼非聚集索引就像新華字典的偏旁字典,他結構順序與實際存放順序不一定一致。

 1.3、联合索引最左匹配原则

联合索引又叫复合索引,对表上的多个字段同时建立的索引(有顺序,ABC,ACB是完全不同的两种联合索引。)以联合索引(a,b,c)为例,建立这样的索引相当于建立了索引a、ab、abc三个索引。

遵循最左前缀原则(必须带着最左边的列做条件才能命中索引),且从出现范围开始索引失效;

当遇到范围查询(>、<、between、like)就会停止匹配。也就是:

#这样a,b可以用到(a,b,c),c不可以
select * from t where a=1 and b>1 and c =1;</p>
<p>这条语句只有 a,b 会用到索引,c 都不能用到索引。</p>
<pre class="brush:php;toolbar:false">create index mix_ind on 表名 (id,name,email);

select * from 表名 where id = 123;  # 命中索引

select * from 表名 where id = 123 and name = 'pamela';  # 命中索引

select * from 表名 where id > 123 and name = 'pamela';  # id命中,name不命中索引,因为出现范围

select * from 表名 where id = 123 and email = 'pamela@123.com';  # 命中索引

select * from 表名 where email = 'pamela@123.com';  # 不命中索引,因为条件中没有id

select * from 表名 where name='pamela' and email = 'pamela@123.com';  # 不命中
A:select * from student where age = 16 and name = '小张'

B:select * from student where name = '小张' and sex = '男'

C:select * from student where name = '小张' and sex = '男' and age = 18

D:select * from student where age > 20 and name = '小张'

E:select * from student where age != 15 and name = '小张'

 A遵从最左匹配原则,age是在最左边,所以A走索引;

 B直接从name开始,没有遵从最左匹配原则,所以不走索引;

 C虽然从name开始,但是有索引最左边的age,mysql内部会自动转成where age = '18' and name = '小张'  and sex = '男' 这种,所以还是遵从最左匹配原则;

 D这个是因为age>20是范围,范围字段会结束索引对范围后面索引字段的使用,所以只有走了age这个索引;

 E这个虽然遵循最左匹配原则,但是不走索引,因为!= 不走索引;

question1:如何给下列sql语句加上联合索引?

select * from test where a = 1 and b = 1 and c = 1;

answer:

咱们一看,直接加索引(a,b,c)就可以了,其实不然,也不能说这个答案不对,只能说这个答案不够完整。因为mysql在执行的时候会经历优化器过程,所以会把sql语句自动优化成符合索引的顺序,所以索引(a,b,c) (a,c,b) 或者是(c,b,a)都是可以的,那我们究竟如何确定索引呢?这个就得根据实际情况来了,比如a字段是表示性别的,只有0,1和2三个值来表示 未知,男,女三种性别,那么把a字段放在联合索引的最后会比较合适,反正哪个字段的内容重复率越高,就把哪个字段往联合索引的后面放。

question2:如何给下列sql语句加上索引?

SELECT * FROM table WHERE a > 1 and b = 2;

answer:

如果咱们建立索引(a,b),那么a>1是可以走到索引的,但是b=2就没法走到索引了。但是如果咱们建立索引(b,a),那么sql语句会被自动优化成 where b=2 and a> 1,这样a和b都能走到索引,所以建立索引(b,a)比较合适

 1.4、索引覆盖和回表

使用聚集索引(主键或第一个唯一索引)就不会回表,非聚集索引就会回表。当select的数据列被所建索引覆盖时不需要回表,可以直接取得数据。

覆盖索引是select的数据列只用从索引中就能够取得,不必读取数据行,换句话说查询列要被所建的索引覆盖。覆盖索引在查询过程中不需要回表。只需要在一棵索引树上就能获取SQL所需的所有列数据,无需回表速度更快。

覆盖索引其核心就是只从辅助索引要数据。那么, 普通索引(单字段)和联合索引,以及唯一索引都能实现覆盖索引的作用。explain的输出结果Extra字段为Using index时,能够触发索引覆盖。

create index ind_id on 表名(id);  # 对id字段创建了索引

select id from 表名  where id > 100;  # 覆盖索引:在查找一条数据的时候,命中索引,不需要再回表 
select max(id) from 表名  where id > 100;  # 覆盖索引:在查找一条数据的时候,命中索引,不需要再回表 
select count(id) from 表名  where id > 100;  # 覆盖索引:在查找一条数据的时候,命中索引,不需要再回表 
select name from 表名  where id > 100;  # 相对慢

(1) 如何实现索引覆盖?

常见的方法是:将被查询的字段,建立到联合索引里去。

select id,name from user where name='shenjian';

 能够命中name索引,索引叶子节点存储了主键id,通过name的索引树即可获取id和name,无需回表,符合索引覆盖,效率较高。

Extra:Using index。

(2)哪些场景可以利用索引覆盖来优化SQL? 

场景1:全表count查询优化

原表为:user(PK id, name, sex);不能利用索引覆盖

select count(name) from user;

添加索引,就能够利用索引覆盖提效

alter table user add key(name);

场景2:列查询回表优化

这个例子不再赘述,将单列索引(name)升级为联合索引(name, sex),即可避免回表。

场景3:分页查询

将单列索引(name)升级为联合索引(name, sex),也可以避免回表。

1.5、前缀索引

前缀索引说白了就是对文本的前几个字符(具体是几个字符在建立索引时指定)建立索引,这样建立起来的索引更小,所以查询更快。

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

MySQL 前缀索引能有效减小索引文件的大小,提高索引的速度。但是前缀索引也有它的坏处:MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把它们用作覆盖索引(Covering Index)。

 1.6、索引合并

深入理解 index merge 是使用索引进行优化的重要基础之一。理解了 index merge 技术,我们才知道应该如何在表上建立索引。

为什么会有index merge?

我们的 where 中可能有多个条件(或者join)涉及到多个字段,它们之间进行 AND 或者 OR,那么此时就有可能会使用到 index merge 技术。index merge 技术如果简单的说,其实就是:对多个索引分别进行条件扫描,然后将它们各自的结果进行合并(intersect/union)

MySQL5.0之前,一个表一次只能使用一个索引,无法同时使用多个索引分别进行条件扫描。但是从5.1开始,引入了 index merge 优化技术,对同一个表可以使用多个索引分别进行条件扫描。

索引合并是指分别创建的两个索引,在某一次查询中临时合并成一条索引。

# 索引合并
create index ind_id on 表名(id);
create index ind_email on 表名(email);

select * from 表名 where id=100 or email = 'pamela@123.com'  # 索引合并,临时把两个索引ind_id和ind_email合并成一个索引

 1.7、索引下推

(1)索引下推简介

索引条件下推(Index Condition Pushdown),简称ICP。MySQL5.6新添加,用于优化数据的查询。 通过索引下推对于非主键索引进行优化,可有效减少回表次数,从而提高效率。

如果没有索引下推优化(或称ICP优化),当进行索引查询时,首先根据索引来查找记录,然后再根据where条件来过滤记录;在支持ICP优化后,MySQL会在取出索引的同时,判断是否可以进行where条件过滤再进行索引查询,也就是说提前执行where的部分过滤操作,在某些场景下,可以大大减少回表次数,从而提升整体性能。

  • 当你不使用ICP,通过使用非主键索引(普通索引or二级索引)进行查询,存储引擎通过索引检索数据,然后返回给MySQL服务器,服务器再判断是否符合条件。

  • 使用ICP,当存在索引的列做为判断条件时,MySQL服务器将这一部分判断条件传递给存储引擎,然后存储引擎通过判断索引是否符合MySQL服务器传递的条件,只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

(2)适用场景

  • 当需要整表扫描,e.g.:range,ref,eq_ref....

  • 适用InnoDB引擎和MyISAM引擎查询(5.6版本不适用分区查询,5.7版本可以用于分区表查询)。

  • InnoDB引擎仅仅适用二级索引。(原因InnoDB聚簇索引将整行数据读到InnoDB缓冲区)。

  • 子查询条件不能下推。触发条件不能下推,调用存储过程条件不能下推。


二、索引优化规则

查询的条件字段尽量用索引字段

2.0、and/or条件相连

and条件相连,有一列有索引就会命中索引,加快查询速度;or条件相连,所有列都有索引才能命中索引,加快查询速度;

create index mix_ind on 表名 (id);

select * from 表名 where id = 123 and name = 'pamela';  # 有一列有索引,速度快

select * from 表名 where id = 123 or name = 'pamela';  # 不是所有列都有索引,速度慢

2.1、like语句的前导模糊查询不能使用索引

select * from doc where title like '%XX';   --不能使用索引

select * from doc where title like 'XX%';   --非前导模糊查询,可以使用索引

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

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

union能够命中索引,并且MySQL 耗费的 CPU 最少。

select * from doc where status=1

union all

select * from doc where status=2;

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

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

or 新版的 MySQL(MySQL5.0后) 索引合并能够命中索引,查询优化耗费的 CPU 比 in多,不建议频繁用or。

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

补充:有些地方说在where条件中使用or,索引会失效,造成全表扫描,这是个误区:

  • 要求where子句使用的所有字段,都必须建立索引;

  • 如果数据量太少,mysql制定执行计划时发现全表扫描比索引查找更快,所以会不使用索引;

  • 确保mysql版本5.0以上,且查询优化器开启了index_merge_union=on, 也就是变量optimizer_switch里存在index_merge_union且为on。

2.3、负向条件查询不能使用索引

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

例如下面SQL语句:

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

可以优化为 in 查询:

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

2.4、联合索引最左前缀原则

如果在(a,b,c)三个字段上建立联合索引,那么他会自动建立 a| (a,b) | (a,b,c)组索引。联合索引遵循最左前缀原则(必须带着最左边的列做条件才能命中索引),且从出现范围开始索引失效;

create index mix_ind on 表名 (id,name,email);

select * from 表名 where id = 123;  # 命中索引

select * from 表名 where id > 123;  # 不命中索引,因为出现范围

select * from 表名 where id = 123 and name = 'pamela';  # 命中索引

select * from 表名 where id > 123 and name = 'pamela';  # 不命中索引,因为出现范围

select * from 表名 where id = 123 and email = 'pamela@123.com';  # 命中索引

select * from 表名 where email = 'pamela@123.com';  # 不命中索引,因为条件中没有id

select * from 表名 where name='pamela' and email = 'pamela@123.com';  # 不命中索引,因为条件中没有id

登录业务需求,SQL语句如下:

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

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

2.5、不能使用索引中范围条件右边的列(范围列可以用到索引),范围列之后列的索引全失效。

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

索引最多用于一个范围列,如果查询条件中有两个范围列则无法全用到索引。

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

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

2.6、不要在索引列上面做任何操作(计算、函数),否则会导致索引失效而转向全表扫描。

例如下面的 SQL 语句,即使 date 上建立了索引,也会全表扫描:

select * from doc where YEAR(create_time) <= &#39;2016&#39;;

可优化为值计算,如下:

select * from doc where create_time <= &#39;2016-01-01&#39;;

比如下面的 SQL 语句:

select * from order where date < = CURDATE();

可以优化为:

select * from order where date < = &#39;2018-01-2412:00:00&#39;;

2.7、强制类型转换会全表扫描

字符串类型不加单引号会导致索引失效,因为mysql会自己做类型转换,相当于在索引列上进行了操作。

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

select * from user where phone=13800001234

可以优化为:

select * from user where phone=&#39;13800001234&#39;;

2.8、更新十分频繁、数据区分度不高的列不宜建立索引

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

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

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

2.9、利用覆盖索引来进行查询操作,避免回表,减少select * 的使用

覆盖索引:查询的列和所建立的索引的列个数相同,字段相同。

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

例如登录业务需求,SQL语句如下。

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

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

2.10、索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时,尽量使用not null 约束以及默认值。

2.11、is null, is not null无法使用索引

2.12、如果有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)无法排序。

2.13、使用短索引(前缀索引)

对列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果该列在前10个或20个字符内,可以做到既使得前缀索引的区分度接近全列索引,那么就不要对整个列进行索引。因为短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作,减少索引文件的维护开销。可以使用count(distinct leftIndex(列名, 索引长度))/count(*) 来计算前缀索引的区分度。

但缺点是不能用于 ORDER BY 和 GROUP BY 操作,也不能用于覆盖索引。

不过很多时候没必要对全字段建立索引,根据实际文本区分度决定索引长度即可。

2.14、利用延迟关联或者子查询优化超多分页场景

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

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

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

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

比如如下 SQL 语句:

select * from user where login_name=?;

可以优化为:

select * from user where login_name=? limit 1

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

2.16、超過三個表最好不要 join

需要 join 的字段,資料類型必須一致,多表關聯查詢時,保證被關聯的字段需要有索引。

例如:left join是由左邊決定的,左邊的資料一定都有,所以右邊是我們的關鍵點,建立索引要建右邊的。當然如果索引在左邊,可以用right join。

2.17、單表索引建議控制在5個以內

2.18、SQL 效能最佳化explain 中的type:至少要達到range 級別,要求是ref 級別,如果可以是consts 最好

consts:單表中最多只有一個匹配行(主鍵或唯一索引),在最佳化階段即可讀取到資料。

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

range:對索引進行範圍檢索。

當 type=index 時,索引實體檔案全掃,速度非常慢。

2.19、業務上具有唯一特性的字段,即使是多個字段的組合,也必須建成唯一索引

不要以為唯一索引影響了insert 速度,這個速度損耗可以忽略,但提高查找速度是明顯的。另外,即使在應用層做了非常完善的校驗控制,只要沒有唯一索引,根據墨菲定律,必然有髒數據產生。

2.20、建立索引時避免以下錯誤觀念

#索引越多越好,認為需要一個查詢就建立一個索引。寧缺勿濫,因為索引會消耗空間、嚴重拖慢更新和新增速度。

抵制惟一索引,認為業務的惟一性一律需要在應用層透過「先查後插」方式解決。

過早優化,在不了解系統的情況下就開始優化。


三、使用索引和不使用索引的情況

3.1、使用索引

  • 主鍵自動建立唯一索引。 

  • 經常作為查詢條件在WHERE或ORDER BY 語句中出現的欄位要建立索引。 

  • 查詢中與其他資料表關聯的字段,外鍵關係建立索引。 

  • 常用於聚合函數的欄位要建立索引,如min(),max()等的聚合函數。

3.2、不使用索引

  • #經常增刪改的欄位不要建立索引。 

  • 有大量重複的欄位不建立索引。 

  • 表格記錄太少不要建立索引,因為資料較少,可能查詢全部資料花費的時間比遍歷索引的時間還要短,索引就可能不會產生最佳化效果 。

推薦學習:mysql影片教學

以上是歸納整理MySQL索引最佳化知識點的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:csdn.net。如有侵權,請聯絡admin@php.cn刪除