SQL优化探索 1

知识分享官

于 2024-09-03 15:04:02 发布

阅读量379

点赞数 11

文章标签： sql adb 数据库

本文链接：https://blog.csdn.net/2301_78976656/article/details/141862149

版权

关于一些书本上的说法，我尝试在实践中体验一下

索引列的类型尽量小

这个原则的意思是：由于B+树上每个页固定16KB，当索引列的类型占用的空间比较小时，一个页里能容纳的记录就多，无论是覆盖查询还是回表查询对页的随机访问就少，所以查询会更快

而且这个原则对主键也适用，主要是因为在二级索引里，主键也在叶子节点中。

测试环境：
- MySQL 5.7.6
- 一张example_table1表，三千条数据，在主键-id和email上有索引；主键和email都是bigint类型。
- 一张example_table2表，三千条数据，在主键-id和email上有索引；主键和email都是mediumint类型。
测试内容：
- 看看两张表索引文件的大小
- 通过email上的索引，回表查询十分之一的记录
- 全表扫描
测试过程：
1. 通过mysql命令show table status like 'table_name'查看索引大小
  - table1：
  - table2：虽说BIGINT是8字节，MEDIUMINT是三字节，但是构造成B+树后比例有所变化，可能是除了实际数据外MySQL的其他数据拉平了比例，比如说每页都有些page header，file header，file tail之类的...
2. 通过执行SELECT * FROM example_table2 WHERE email < xxx;观察执行时间
由于我的数据量并不大，简单查询时间都是0.00 sec，所以我用存储过程来循环查询100遍，代码如下
SQL

代码解读

复制代码
DELIMITER $$ CREATE PROCEDURE RepeatQuery511() BEGIN DECLARE count INT DEFAULT 0; -- 循环执行查询 100 次 WHILE count < 100 DO -- 执行查询 SELECT * FROM example_table5 WHERE email < 20000; -- 增加计数器 SET count = count + 1; END WHILE; END$$ DELIMITER ;
执行：CALL RepeatQuery511();

测试结果：
- table1：
- table2：
  
  结论：走二级索引时，优化了大概10%；
1. 通过执行SELECT * FROM example_table5看看全表扫描是否受影响
- table1：
- table2：
  
  按理来说在聚簇索引上，主键的大小和整个行记录相比占比很小，所以在全局扫描时应该影响不大，但是还有4%的提升，有可能是我的行记录中其他属性较小，主键为BIGINT时就会占蛮大比例的。
  
  整理了一份好像面试笔记包括了：Java面试、Spring、JVM、MyBatis、Redis、MySQL、并发编程、微服务、Linux、Springboot、SpringCloud、MQ、Kafka 面试专题
  
  需要全套面试笔记【点击此处即可】免费获取
关于force index的使用

在个人测试中发现，很多时候我觉得走某个索引好，但MySQL的优化器并不认为，此时可以通过force index 或者 use index测试具体索引的性能

但是话又说回来，MySQL的预测通常都是准确的，和人的思考有出入时大概率二者效率差不多；

MySQL可能认为回表的代价更大，而人会认为全表扫描的成本更高

对此进行了测试：

测试环境：

三千行数据，有一个二级索引在project_id上

测试步骤：

通过在where中调整查询project_id的范围，观察下什么时候走索引，什么时候全表扫描

测试内容：

explain select * from bdi_event_info where project_id > 182;
explain select * from bdi_event_info where project_id > 180;

其中，大于182的大约有560条数据

结论：当通过索引需要回表的行数在所有行的16%左右时，MySQL会放弃二级索引，选择全表扫描。

接下来，在没有走索引的情况下，通过force index强行走索引，测试结果相近；

【特殊情况】：当查询的数据量较多时，使用force index将查询时间从0.35s 优化到0.21s，优化了近50%；情况如下：

当执行的SQL中含有order by，且行数大于400行时，在explain的Extra栏出现using filesort，此时查询时间为0.35s

但当加入force index后，由于按照索引的顺序天生是有序的，避免了经由磁盘的排序，所以避免了文件排序，时间优化到0.21s

那么问题来力，MySQL为什么会统计不准呢？我们想想MySQL的统计过程 —— 根据SQL罗列出可能用到的索引，针对每种方式进行估计，估计的过程是根据在sable_stats和index_stats两张表中存储的表的统计信息和索引的统计信息，结合MySQL设计者确定的成本参数，估算出一种查询方式的大致成本；从潜在索引中选择大致成本最低的，就作为MySQL的执行计划；

所以可能有两方面的问题：

成本参数设置不合理

通过调整成本参数可以设置成优先少用某个资源，比如说运行MySQL的服务器上还有更重要的进程在执行CPU密集型任务，那么可以提高MySQL对CPU使用的成本参数，接下来MySQL就会在选择执行计划时尝试避开需要检测大量记录的方案

成本参数有两类，一类是在server层面的，比如说创建临时表的成本，两条记录排序时比较的成本，检测一条记录是否符合条件的成本（都是CPU型）...另一类是engine层面的，比如从磁盘读取一页的成本(IO型)...
统计信息不准确

MySQL默认会自动更新表的统计信息，当对表的更改超过总行数的10%时，触发异步更新；

我在网上也找到有说统计信息不准确需要手动更新的，看来10%的区别对MySQL成本估计会有蛮大影响

针对统计信息不准确的解决思路我大致是：定时触发更新脚本，脚本中避免使用analyze table同步计算，而是手动查出来统计信息后，用flush table会快很多。

为长字符串列前缀建立索引

这个原则表明，当我们要在某个较长的字符串上创建索引时，由于字符串本身太大，会导致16KB的页存不下多少行数据，于是导致页数较多，查询时更有可能涉及更多的页，导致IO更加频繁；所以这个原则认为可以只为字符串的前n个字符设置索引，当查询时定位到二级索引的前n个字符后会对应多个满足前n个字符是目标字符的行，回表遍历各行，找出满足条件的来。

简而言之，这种思路通过增加回表检验的次数，优化查找二级索引的时间
让索引以列名的形式在搜索条件中单独出现

对于这种sqlselect * from table where key1 * 2 < 4;，MySQL不会尝试简化 key1 * 2< 4，而是直接认为这个搜索条件不能形成合适的扫描区间
插入记录时按照主键递增的次序插入

在中间插入的话造成裂页的概率会比在末尾插入大得多。

使用索引来优化连接
当我们使用join将两个表连接查询时，简单来说过程是这样的：
- 前提：筛选条件有三种，分别是1；对驱动表的筛选条件，2；对被驱动表的筛选条件，3；对比驱动表和被驱动表的筛选条件
- 首先：根据对驱动表的筛选条件尝试通过索引找出每一行来
- 其次：对每一行而言，都可以明确条件3，然后使用条件3和条件2来全表扫描被驱动表
优化的点就在于，由于被驱动表可能会被访问多次（即使使用join buffer也可能被访问多次），最好也能在其上建立索引便于访问，所以我们可以根据条件2和3，从中选择一个成本小的访问方式来优化连接。

使用where触发MySQL空值拒绝

当mysql处理两张表外连接时，其实无法像内连接一样通过改变连接顺序来优化查询速度；是因为左连接和右连接分别选择保留左边或右边无法匹配on条件的数据；

但是当我们用where将被驱动表中，默认补充null的属性用“where attr != null”过滤掉，那么此时的内连接和外连接就没有区别了；MySQL就又可以优化连接顺序了；