MySQL-索引优化/查询优化

UndefinedException

已于 2022-07-04 16:11:34 修改

阅读量879

点赞数 2

分类专栏： MySQL 文章标签：数据库 mysql

于 2022-07-04 16:09:21 首次发布

本文链接：https://blog.csdn.net/weixin_62427168/article/details/125592958

版权

MySQL 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

一、索引失效的情况

1.1 最佳左前缀法则

1.2 计算、函数、类型转换(自动或手动)导致索引失效

1.3 范围条件右边的列索引失效

1.4 不等号/is not null 导致索引失效

1.5 like以通配符%开头导致索引失效

1.6 OR 前后存在非索引的列，索引失效

九、其他查询优化（EXISTS和IN/COUNT(*)与COUNT(字段)/LIMIT 1/SELECT(*)/COMMIT）

9.1 EXISTS 和 IN 的区分

9.2 COUNT（*）/COUNT（1）与COUNT（字段）

一、索引失效的情况

1.1 最佳左前缀法则

对于多列索引，过滤条件想使用索引就必须按照索引建立时的顺序依次满足，一旦跳过某个字段，索引后面的字段都无法使用。

假如索引中定义的是id，name，age，那查询时条件必须严格按照 id，name，age 的顺序。

1.2 计算、函数、类型转换(自动或手动)导致索引失效

CREATE INDEX idx_name ON student(NAME); # 给name字段创建索引

SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%'; #索引正常使用
SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc'; #索引失效-函数
SELECT SQL_NO_CACHE * FROM student WHERE name = 123; #索引失效-类型转换

对于添加了索引的字段，最好不要包括在函数内，或有其他计算、转换行为。

1.3 范围条件右边的列索引失效

create index idx_age_name_classid on student(age,name,classid); #创建索引

SELECT * FROM student WHERE student.age=30 
AND student.name > 'a%' 
AND student.classId=10 
; #索引失效-age name字段正常使用索引，classid字段失效

由于name字段是范围条件，所以它后面的字段都失效了。我们开发时要把条件字段放在最后。

1.4 不等号/is not null 导致索引失效

SELECT * FROM student WHERE student.age != 30 
SELECT * FROM student WHERE student.age is not null

1.5 like以通配符%开头导致索引失效

SELECT * FROM student WHERE student.name like '%wz%' #索引失效

1.6 OR 前后存在非索引的列，索引失效

SELECT * FROM student WHERE age = 10 OR classid = 100;
#假设age字段有索引，classid字段没索引，那这个查询索引就失效了

1.7 数据库和各表的字符集不统一

统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行转换会造成索引失效。

1.8 举例

假设index（a,b,c）

二、JOIN查询的优化

2.1 外连接

SELECT * FROM `type` LEFT JOIN book ON type.card = book.card;

在这里，type是驱动表，book是被驱动表。至少被驱动表要建立索引

ALTER TABLE book ADD INDEX Y (card);    #右表一定要创建索引
ALTER TABLE `type` ADD INDEX X (card);    #左边也推荐创建索引

2.2 内连接

SELECT * FROM type INNER JOIN book ON type.card=book.card;

对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表。

如果只有一个字段有索引，那么这个字段所在表将会作为被驱动表。

如果两个表连接条件都有索引，优化器会选择小表作为驱动表，以”小表驱动大表“

2.3 小结

保证被驱动表的JOIN字段已经创建了索引

需要JOIN 的字段，数据类型保持绝对一致。

LEFT JOIN 时，选择小表作为驱动表，大表作为被驱动表 。减少外层循环的次数。

INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。

能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)

不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。

三、子查询优化

子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。

原因：

1. 执行子查询时，MySQL需要为内层查询语句的查询结果 建立一个临时表 ，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。

2. 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引 ，所以查询性能会受到一定的影响。

3.对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

4.尽量不要使用NOT IN 或者 NOT EXISTS

在MySQL中，可以使用JOIN查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。

四、ORDER BY优化

优化建议：

1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描 ，在 ORDER BY 子句避免使用 FileSort 排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。

2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。

3. 无法使用 Index 时，需要对 FileSort 方式进行调优。

4.Order By后不加limit，索引可能会失效。

5.若两个索引同时存在，mysql会自动选择最优的方案。当范围条件和group by/order by字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，需要排序的数据并不多时，优先把索引放在范围字段上。

INDEX a_b_c(a,b,c)

order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC

如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c

不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

filesort算法：双路排序和单路排序

双路排序（慢）：

先读取order by列，对他们排序，然后根据排好的顺序去取其他的列。要进行两次磁盘扫描。随机IO。

单路排序（快）：

从磁盘读取要查询的所有列，然后排序，最后输出排好的列表。只进行一次读磁盘操作，顺序IO，但占用更多内存。

如何优化？

1.提高sort_buffer_size

2.提高max_length_for_sort_data

3.order by 时最好不用select *，要select 具体字段。

五、GROUP BY

-group by 使用索引的原则几乎跟order by一致，group by 即使没有过滤条件用到索引，也可以直接使用索引。

-group by 先排序再分组，遵照最佳左前缀法则

-当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置 where效率高于having，能写在where限定的条件就不要写在having中了

-减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。

-包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

六、LIMIT

SELECT * FROM student LIMIT 200000,10; #先排序前2000010条记录，然后返回最后10条

优化思路一：在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10) a
WHERE t.id = a.id;

优化思路二：该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询。

SELECT * FROM student WHERE id > 2000000 LIMIT 10;

七、优先考虑覆盖索引

覆盖索引：

覆盖索引的特点是不用回表二次查询。我们知道非聚簇复合索引的节点中包含了创建索引的这几个列的记录，比如a，b，c。如果我们利用其中的某些字段来查找其他字段（都包含在 a、b、c内），那就不需要通过主键去聚簇索引再找完整的记录了，省去了回表的过程。

好处：

1. 避免Innodb表进行索引的二次查询（回表）

2. 可以把随机IO变成顺序IO加快查询效率

弊端： 索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

八、索引下推

索引下推(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

8.1 不使用IPC

存储层：只将满足index key条件的索引记录对应的整行记录取出，返回给server层

服务层：对返回的数据，使用后面的where条件过滤，直至返回最后一行。

8.2 使用IPC

存储层：首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤。将满足index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃，不回表、也不会返回server层。

服务层：对返回的数据，使用table filter条件做最后的过滤。

使用ICP前，存储层多返回了需要被index filter过滤掉的整行记录

使用ICP后，直接就去掉了不满足index filter条件的记录，省去了他们回表和传递到server层的成本。

8.3 使用条件

① 只能用于二级索引(secondary index)

②explain显示的执行计划中type值（join 类型）为 range 、 ref 、 eq_ref 或者 ref_or_null 。 ③ 并非全部where条件都可以用ICP筛选，如果where条件字段不在索引列中，还是要读取整表的记录到server端做where过滤。

④ ICP可以用于MyISAM和InnnoDB存储引擎

⑤ MySQL 5.6版本的不支持分区表的ICP功能，5.7版本的开始支持。

⑥ 当SQL使用覆盖索引时，不支持ICP优化方法。

#例如 people表中有如下字段：id，zipcode，firstname，lastname，address
# id为主键，zipcode，firstname，lastname 有一个联合索引

select * from people
where zipcode='0001' 
and lastname like '%wz%'
and address like '%北京%';

#如果没有开启IPC，在我们直接把zipcode='0001'的所有记录找出来，然后再进行下一步的过滤操作

#如果开启了IPC，虽然lastname并不是按照索引列的顺序的，但它包含在索引列，我们就把满足
#zipcode='0001'同时满足lastname like '%wz%'的记录找出来，再进行最后的操作。省去了一部分回表操作

九、其他查询优化（EXISTS和IN/COUNT()与COUNT(字段)/LIMIT 1/SELECT()/COMMIT）

9.1 EXISTS 和 IN 的区分

对于表A与表B：

如果A表大，B表小，就用 IN；

SELECT * FROM A WHERE c IN(SELECT c FROm B)#不相关子查询
#先把内循环B的东西查完给外循环A用，所以B越小越好

如果B表大，A表小，就用 EXISTS；

SELECT * FROM A WHERE EXISTS(SELECT c FROm B WHERE B.c=A.c)#相关子查询
#把外循环A的东西一条条送进内循环B中查询，所以A越小越好

9.2 COUNT（*）/COUNT（1）与COUNT（字段）

COUNT（*）与 COUNT（1）本质上没有区别。

如果用的是MyIsam引擎，那么统计行数的时间复杂度为O（1），因为每张表都有一个记录行数的meta信息。

如果用的是InnoDB引擎，则需要全表扫描，时间复杂度为O（n）。

如果要统计具体字段行数，尽量用二级索引，因为聚簇索引（主键索引）包含的信息多。

对于COUNT（*）与 COUNT（1），系统会自动选择占用空间更小的二级索引来统计。

**9.3 SELECT（*）**

开发中不建议使用select（*），因为SQL解析需要用数据字典将*转为所有列名，耗费资源

9.4 LIMIT 1

针对的是扫描全表的SQL语句。如果确定结果集只需要一条记录，可以加LIMIT1，这样找到一条结果时就不会再继续扫描了。

如果数据表已经对字段加了唯一索引，就可以通过索引查询，不用加LIMIT 1.

9.5 COMMIT

推荐经常使用COMMIT，因为COMMIT会释放资源：

1.回滚段上用于恢复数据的信息

2.被程序语句获得的锁

3.redo/undo log buffer中的空间

4.管理上面3种资源的内部花费。

9.6 数据库主键怎么设计？

最好不用 自增主键。因为有以下缺点：

1.可靠性不高

2.安全性不高

3. 性能差

4. 交互多

5. 局部唯一性

推荐的主键设计：

非核心业务 ：对应表的主键自增ID，如告警、日志、监控等信息。

核心业务 ：主键设计至少应该是全局唯一且是单调递增。全局唯一保证在各系统之间都是唯一的，单调递增是希望插入时不影响数据库性能。推荐使用UUID：

MySQL数据库的UUID组成如下所示：

UUID = 时间+UUID版本（16字节）- 时钟序列（4字节） - MAC地址（12字节）

UUID是无序的，因为时间低位放在最前面，而这部分的数据是一直在变化的。

若将时间高低位互换，则时间就是单调递增的了，UUID也就变得单调递增了。

MySQL 8.0可以更换时间低位 和 时间高位的存储方式，这样UUID就是有序的UUID了。 MySQL 8.0还解决了UUID存在的空间占用的问题，除去了UUID字符串中无意义的"-"字符串，并且将字符串用二进制类型保存，这样存储空间降低为了16字节。原来是36字节。

SET @uuid = UUID();
SELECT @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);

UndefinedException

关注

2
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
MySQL-索引优化/查询优化

对于多列索引，过滤条件想使用索引就必须按照索引建立时的顺序依次满足，一旦跳过某个字段，索引后面的字段都无法使用。假如索引中定义的是id，name，age，那查询时条件必须严格按照 id，name，age 的顺序。对于添加了索引的字段，最好不要包括在函数内，或有其他计算、转换行为。由于name字段是范围条件，所以它后面的字段都失效了。我们开发时要把条件字段放在最后。1.5 like以通配符%开头导致索引失效1.6 OR 前后存在非索引的列，索引失效
复制链接

扫一扫