MySQL高级(归纳四)之sql性能调优分析与详解

最新推荐文章于 2022-04-11 17:08:06 发布

suqinyi

最新推荐文章于 2022-04-11 17:08:06 发布

阅读量344

点赞数 3

分类专栏：数据库文章标签： mysql优化 mysql高级篇四 mysql性能调优

本文链接：https://blog.csdn.net/qq_44413835/article/details/117868293

版权

数据库专栏收录该内容

11 篇文章 1 订阅

订阅专栏

本文详细讲解了MySQL高级查询优化技巧，包括索引的使用与失效、单表与多表查询分析、子查询优化、排序与GROUP BY优化、分页与去重优化，以及LIKE和索引策略。通过实例演示和最佳实践，提升SQL查询性能和效率。

摘要由CSDN通过智能技术生成

MySQL高级篇四 sql性能调优

一、开篇

1.1 、索引俩大功能

① 查询 ② 排序

1.2 、索引失效总结：

全值匹配性能最高（查询字段和索引字段对应）
最佳左前缀法则（如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。）
不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select
mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
is not null 也无法使用索引,但是is null是可以使用索引的
like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作
字符串不加单引号索引失效
少用or,用它来连接时会索引失效

二、单表查询分析与优化

2.1、准备工作 => 建表

# 表
CREATE TABLE
IF
	NOT EXISTS `article` (
		`id` INT ( 10 ) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
		`author_id` INT ( 10 ) UNSIGNED NOT NULL,
		`category_id` INT ( 10 ) UNSIGNED NOT NULL,
		`views` INT ( 10 ) UNSIGNED NOT NULL,
		`comments` INT ( 10 ) UNSIGNED NOT NULL,
		`title` VARBINARY ( 255 ) NOT NULL,
	`content` TEXT NOT NULL 
	);

# 数据
INSERT INTO `article` 
	( `author_id`, `category_id`, `views`, `comments`, `title`, `content` )
VALUES
	( 1, 1, 1, 1, '1', '1' ),
	( 2, 2, 2, 2, '2', '2' ),
	( 1, 1, 3, 3, '3', '3' );

2.2、案例分析

案例要求：
查询 category_id 为1 且 comments 大于 1 的情况下,views 最多的 article_id。

第一次 EXPLAIN分析
在这里插入图片描述

结论：很显然,type 是 ALL,即最坏的情况。
Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。
Using filesort是内部自排序

第一次优化：

01 创建索引：

# 3个字段组合的复合索引
create index idx_article_ccv on article(category_id,comments,views);

# 查看索引
show index from article;

02 复合索引的顺序为 category_id 、comments、views
在这里插入图片描述
03 第2次EXPLAIN分析

结论：
type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。

但是我们已经建立了索引,为啥没用呢?
01、这是因为按照 BTree 索引的工作原理,
02、先排序 category_id,
03、如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。

当 comments 字段在联合索引里处于中间位置时,
因 comments > 1 条件是一个范围值(所谓 range),既范围就索引查询失效，不能查询只能排序
MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。

第二次优化：

01 索引重建

# 删除第一次建立的索引
DROP INDEX idx_article_ccv ON article;

# 新建索引（俩个列的复合索引）
create index idx_article_cv on article(category_id,views);

02 EXPLAIN分析
在这里插入图片描述

结论：可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。

三、多表查询分析与优化

3.1、双表案例

3.1.1、创建表

# 表一
CREATE TABLE
IF
	NOT EXISTS `class` (
		`id` INT ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT, 
		`card` INT ( 10 ) UNSIGNED NOT NULL, 
		PRIMARY KEY ( `id` ) 
	);
# 表二
CREATE TABLE
IF
	NOT EXISTS `book` ( 
		`bookid` INT ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT, 
		`card` INT ( 10 ) UNSIGNED NOT NULL,
		PRIMARY KEY ( `bookid` ) 
	);

# 数据
# 表一数据
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
 
# 表二数据
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

3.1.2 、左连接explain分析

01、没有索引的情况 ⇒ 效率为ALL（垃圾）
在这里插入图片描述
02、添加索引优化

ALTER TABLE `book` ADD INDEX Y ( `card`);

03、explain分析
在这里插入图片描述

结论:
	可以看到第二行的 type 变为了 ref,rows 也变成了优化比较明显。
	这是由左连接特性决定的。
	LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,
	所以右边是我们的关键点,一定需要建立索引。

04、测试索引加另一表

# 删除旧索引
DROP INDEX Y ON book;

# 建立新索引
ALTER TABLE class ADD INDEX Y (card);

05、explain分析
在这里插入图片描述

第一次建立的索引好，ref和rows比对，因为ref效率高于index
左连接索引加右表

3.1.2 、右连接简单分析

在这里插入图片描述
01、第一种分析

02、删除索引，新建索引

03、第二种分析

3.2、三表案例

3.2.1、创建表

# 表三，在双表的基础在增加一张
CREATE TABLE
IF
	NOT EXISTS `phone` ( 
		`phoneid` INT ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT,
		`card` INT ( 0 ) UNSIGNED NOT NULL,
		PRIMARY KEY ( `phoneid` ) 
	) ENGINE = INNODB;

# 数据
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));

3.2.2、删除3张表的索引，保证干净

# 查看class表索引
show index from class;

# 删除class表的索引Y
drop index Y on class;

# 其他俩张表也是这样操作，有索引就删除

3.2.3、建索引，分析并优化

01 创建索引

ALTER TABLE `phone` ADD INDEX z(`card`);
ALTER TABLE  `book`  ADD INDEX Y(`card`)

02 explain 分析
在这里插入图片描述
后2行的type都是ref且总rows 优化很好,效果不错。
因此索引最好设置在需要经常查询的字段。

3.3、 join语句的优化总结

尽可能减少Join语句中的NestedLoop的循环总次数;即“永远用小结果集驱动大的结果集”。
优先优化NestedLoop的内层循环;
保证Join语句中被驱动表上Join条件字段已经被索引;
当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer设置;

为什么是小表驱动大表

例如 俩个循环 ：一个循环3次 ，一个循环100次 ，嵌套循环
那么是用循环3次去循环100次的循环的？
还是是用循环100次去循环3次的循环的？

可想而知 ⇒ 当然是3次去循环100次的效率高

所以：要用小表去驱动大表

3.3、建议总结

保证被驱动表的join字段已经被索引
left join 时，选择小表作为驱动表，大表作为被驱动表。
inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
子查询尽量不要放在被驱动表，有可能使用不到索引。

四、子查询优化

4.1 、用in 还是 exists？

在这里插入图片描述

EXISTS 说明
	SELECT ... FROM table WHERE EXISTS (查询语句)
	该语法可以理解为：将主查询的数据，放到子查询中做条件验证，
		根据验证结果（TRUE或FALSE)来决定主查询的数据结果是否得以保留。
 
注意
	1 EXSTS (subquey)只返回TRUE或FALSE，因此子查询中的SELECT * 也可以是SELECT 1或 select  'X'，官方说法是实际执行时会忽略SELECT清单，因此没有区别
	
	2 EXSTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
	
	3 EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN-来替代，何种最优需要具体问题具体分析

4.2 、采用案例分析

先说明：用 exists 是看数据是否存在，存在返回一条记录，exists 是作为一个查询判断用，所以 select 后返回什么不重要。

4.2.1 、无索引大表驱动小表效率

# in 
select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);

# exists  
select sql_no_cache sum(sal) from emp where exists (select 1 from dept where emp.deptno=dept.deptno);

# inner  join
select sql_no_cache sum(sal) from emp inner  join dept on  emp.deptno=dept.deptno;

在这里插入图片描述

4.2.2 、无索引小表驱动大表效率

select sql_no_cache sum(e.sal) from (select * from emp where id<10000) e  where  exists (select 1 from  emp where e.deptno=emp.deptno);
select sql_no_cache sum(e.sal) from (select * from emp where id<10000) e inner join (select distinct deptno from  emp) m on m.deptno=e.deptno;
select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);

在这里插入图片描述

4.2.3 、有索引大表驱动小表效率

4.2.3.1 、先explain分析，key是索引

在这里插入图片描述

4.2.3.2 、效率对比

#in
select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);

# 用 exists 是否存在，存在返回一条记录，exists 是作为一个查询判断用，所以 select 后返回什么不重要。
select sql_no_cache sum(sal) from emp where exists (select 1 from dept where emp.deptno=dept.deptno);  

# inner  join
select sql_no_cache sum(sal) from emp inner  join dept on  emp.deptno=dept.deptno;

在这里插入图片描述

4.2.4 、有索引小表驱动大表

# exists 
select sql_no_cache sum(e.sal) from (select * from emp where id<10000) e  where  exists (select 1 from  emp where e.deptno=emp.deptno);

# inner join
select sql_no_cache sum(e.sal) from (select * from emp where id<10000) e inner join (select distinct deptno from  emp) m on m.deptno=e.deptno;

# in
select sql_no_cache sum(sal) from emp where deptno in (select deptno from dept);

在这里插入图片描述

4.2.5 、有索引，大驱小、小驱大的性能比较

有上面的案例可知：

有索引小驱动大表性能优于大表驱动小表

在数据量少的情况下，是看不出太大的差距，MySQL官网说能抗5~8百万。
在数据量达到这个等级，性能就会出现明显的下降和差距，就需要我们进行性能调优

五、order by关键字优化

ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序
尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

5.1、排序分析与优化

5.1.1、排序方式

MySQL支持二种方式的排序:

FileSort 内部自排序
Index 索引排序

排序效率说明:

Index效率高.
FileSort方式效率较低,它指MySQL扫描索引本身完成排序。

ORDER BY满足两情况，会使用Index方式排序:

ORDER BY 语句使用索引最左前列
使用Where子句与Order BY子句条件列组合满足索引最左前列
失效：where子句中如果出现索引的范围查询(即explain中出现range)会导致order by 索引失效。

5.1.2、建索引的最佳左前缀法则

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
在这里插入图片描述
说明：

第二种中，where a = const and b > const order by b , c 不会出现 using filesort 。 b , c 两个衔接上了
但是：where a = const and b > const order by c 将会出现 using filesort 。因为 b 用了范围索引，断了。
而上一个 order by 后的 b 用到了索引，所以能衔接上 c

5.1.3、准备数据和创建索引

# 建表
CREATE TABLE tblA(
  id int primary key not null auto_increment,
  age INT,
  birth TIMESTAMP NOT NULL,
);
 
# 插入数据
INSERT INTO tblA(age,birth) VALUES(22,NOW());
INSERT INTO tblA(age,birth) VALUES(23,NOW());
INSERT INTO tblA(age,birth) VALUES(24,NOW());
 
# 创建复合索引（age和birth组成一个复合索引）
CREATE INDEX idx_A_ageBirth ON tblA(age,birth);

Case1：
在这里插入图片描述
Case2：

5.1.4、双路排序和单路排序介绍和问题与调优

5.1.4.1、双路排序

MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

5.1.4.2、单路排序

取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。
从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。
并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。

5.1.4.3、结论及引申出的问题

多路排序需要借助磁盘来进行排序。所以取数据，排好了取数据。两次 io操作。比较慢
单路排序，将排好的数据存在内存中，省去了一次 io 操作，所以比较快，但是需要内存空间足够。
由于单路是后出的，总体而言好过双路
但是用单路有问题

单路问题说明：

在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出
所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。

本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

5.1.4.4、优化策略(3点)

增大sort_buffer_size参数的设置 => 用于单路排序的内存大小
增大max_length_for_sort_data参数的设置 =>单次排序字段大小。(单次排序请求)
去掉select 后面不需要的字段 =>select 后的多了，排序的时候也会带着一起，很占内存，所以去掉没有用的

5.1.4.5、提高Order By的速度解释

提高Order By的速度
 
第一点: Order by时select * 是一个大忌只Query需要的字段， 这点非常重要。
   在这里的影响是：
   		01.当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。
        02.两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提sort_buffer_size。
 
第二点: 尝试提高 sort_buffer_size
	不管用哪种算法，提高这个参数都会提高效率
	当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
 
第三点: 尝试提高 max_length_for_sort_data
	提高这个参数， 会增加用改进算法的概率。
	但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率.

五、GROUP BY关键字优化

01、分组和排序其实规则是很几乎一样的，因为分组前，不得先排序
02、group by 基本上是需要进行排序，会有临时表的产生

group by实质是先排序后进行分组，遵照索引建的最佳左前缀
当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
where高于having，能写在where限定的条件就不要去having限定了。

最佳左前缀原则：

索引建立是 a_b_c 顺序就是 1是a ，2是b，3是c
就像去3层楼的3把梯子，乱了的话，MySQL底层会帮我们调优，顺序整回来（消耗一部分性能），但是断了话，比如2楼没梯子，3楼的梯子就没用了。
使用范围的话（>,<,beteewn...）这些的话，将无法使用索引排序，但是能用于查找

什么是覆盖索引（index）：

就是你查询的字段和你的索引匹配
例如： 索引是a_b_c  ，查询的是select  a , b, c, from user  这就是复合索引和字段对应
当然abc索引，你只查a或者b查一部分也是覆盖索引的类型。
但是如何查abcd 四个字段，d不是索引，那么就不是覆盖索引，效率就没那么高
覆盖索引：即查询又排序，性能很好

六、分页查询的优化 limit

案例分析：

01 无索引：
在这里插入图片描述

02 deptno这个字段加上索引 ⇒ 结果（没卵用）
在这里插入图片描述
优化:

先利用覆盖索引把要取的数据行的主键取到，然后再用这个主键列与数据表做关联：(查询的数据量小了后)

在这里插入图片描述

效果比较：

没优化前：
在这里插入图片描述
优化后：

结论：
①、order by 后的字段（XXX）有索引
②、sql 中有 limit 时，当 select id 或 XXX字段索引包含字段时，显示 using index
③、sql 中有 limit 时，当 select 后的字段含有 bouder by 字段索引不包含的字段时，将显示 using filesort

六、去重优化

1、尽量不要使用 distinct
2、优化：关键字去重(union)

准备数据：

t_mall_sku 表
  id  shp_id      kcdz                
------  ------ --------------------
     3       1    北京市昌平区  
     4       1    北京市昌平区  
     5       5    北京市昌平区  
     6       3       重庆              
     8       8     天津

例子：


# 将产生重复数据，
select kcdz form t_mall_sku where id in( 3,4,5,6,8 )  

# 使用 distinct 关键字去重消耗性能
select distinct kcdz form t_mall_sku where id in( 3,4,5,6,8 )   

# 优化：  能够利用到索引(kcdz字段建立索引)
select  kcdz form t_mall_sku where id in( 3,4,5,6,8 )  group by kcdz

七、like优化

问题：解决like '%字符串%'时索引不被使用的方法？？

01 准备数据

CREATE TABLE `tbl_user` (
	 `id` INT(11) NOT NULL AUTO_INCREMENT,
	 `NAME` VARCHAR(20) DEFAULT NULL,
 	 `age` INT(11) DEFAULT NULL,
	 `email` VARCHAR(20) DEFAULT NULL,
 	PRIMARY KEY (`id`)
	) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


INSERT INTO tbl_user(NAME,age,email) VALUES('1aa1',21,'b@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('2aa2',222,'a@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('3aa3',265,'c@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('4aa4',21,'d@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('aa',121,'e@163.com');

没索引测试：
在这里插入图片描述

创建索引测试：（索引->避免全表扫描）

创建索引

CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);

1、全部吻合
在这里插入图片描述
2 id是主键

3、一个沾边

4、查询参数沾边索引和查询查询个数超出（搅屎棍）

八、优化总结

8.1、索引失效

全值匹配性能最高（查询字段和索引字段对应）
最佳左前缀法则（如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。）
不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select
mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
is not null 也无法使用索引,但是is null是可以使用索引的
like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作
字符串不加单引号索引失效
少用or,用它来连接时会索引失效

案例剖析：假设index(a,b,c)

Where语句	索引是否被使用
where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a，b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N
where a = 3 and c = 5	使用到a，但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b， c不能用在范围之后，b后断了
where a = 3 and b like ‘kk%’ and c = 4	Y,使用到a,b,c
where a = 3 and b like ‘%kk’ and c = 4	Y,只用到a
where a = 3 and b like ‘%kk%’ and c = 4	Y,只用到a
where a = 3 and b like ‘k%kk%’ and c = 4	Y,使用到a,b,c

8.2、建议

对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。(避免索引过滤性好的索引失效)
在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

上一篇：MySQL高级(归纳三)之常见瓶颈和性能分析工具（Explain）

下一篇：MySQL高级(归纳五)之查询截取分析(Show Profile、日志、存储过程)

suqinyi

关注

3
点赞
踩
3

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySQL高级(归纳四)之sql性能调优分析与详解

MySQL高级篇四 sql性能调优文章目录MySQL高级篇四 sql性能调优一、索引俩大功能二、单表查询分析与优化2.1、准备工作 => 建表2.2、案例分析三、多表查询分析与优化3.1、双表案例3.1.1、创建表3.1.2 、左连接explain分析3.1.2 、右连接简单分析3.2、三表案例3.2.1、创建表3.2.2、删除3张表的索引，保证干净3.2.3、建索引，分析并优化3.3、 join语句的优化总结3.3、建议总结四、子查询优化4.1 、用in 还是 exists？4.2 、采用案例
复制链接

扫一扫