Mysql-慢查询优化

十三Tech
285 阅读3分钟

建立索引的几个原则

  • 最左前缀匹配原则
  • =和in可以乱序
  • 尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)
  • 索引列不能参与计算，保持列“干净”
  • 尽量扩展索引，不要新建索引

慢查询优化基本步骤

  • 先运行看看是否真的很慢，注意设置SQL_NO_CACHE
  • where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
  • explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
  • order by limit 形式的sql语句让排序的表优先查
  • 加索引时参照建索引的几大原则
  • 观察结果，不符合预期继续从0分析

进阶-基于代价的推荐

代价模型.jpeg

基于代价的索引选择

使用 SQL select * from sync_test1 where name like 'Bobby%' and dt > '2021-07-06'为例

table

Create Table: CREATE TABLE `sync_test1` ( 
    `id` int(11) NOT NULL AUTO_INCREMENT, 
    `cid` int(11) NOT NULL, 
    `phone` int(11) NOT NULL, 
    `name` varchar(10) NOT NULL, 
    `address` varchar(255) DEFAULT NULL, 
    `dt` datetime DEFAULT NULL, 
    PRIMARY KEY (`id`), 
    KEY `IX_name` (`name`), 
    KEY `IX_dt` (`dt`), 
    KEY `IX_dt_name` (`dt`,`name`), 
    KEY `IX_name_dt` (`name`,`dt`) 
) ENGINE=InnoDB

通过执行explain看出MySQL最终选择了IX_name索引

image.png

根据代价模型来推演一下代价的计算过程：

  1. 走全表扫描的代价：io_cost + cpu_cost = （数据页个数 * io_block_read_cost）+ (数据行数 * row_evaluate_cost + 1.1) = （data_length / block_size + 1）+ (rows * 0.2 + 1.1)
  2. 走二级索引IX_name的代价：io_cost + cpu_cost = (预估范围行数 * io_block_read_cost + 1) + (数据行数 * row_evaluate_cost + 0.01)
  3. 走二级索引IX_dt的代价：io_cost + cpu_cost = (预估范围行数 * io_block_read_cost + 1) + (数据行数 * row_evaluate_cost + 0.01)
  4. 走二级索引IX_dt_name的代价: io_cost + cpu_cost = (预估范围行数 * io_block_read_cost + 1) + (数据行数 * row_evaluate_cost + 0.01)
  5. 走二级索引IX_name_dt的代价：io_cost + cpu_cost = (预估范围行数 * io_block_read_cost + 1) + (数据行数 * row_evaluate_cost + 0.01)

基于代价的索引推荐思路

想借助MySQL优化器给慢查询计算出最佳索引，那么需要真实地在业务表上添加所有候选索引，对于线上业务来说，直接添加索引的时间空间成本太高，是不可接受的

MySQL优化器选最佳索引用到的数据是索引元数据和统计数据，所以我们想是否可以通过给它提供候选索引的这些数据，而非真实添加索引的这种方式来实现

基于代价的索引推荐思路.jpeg

MySQL本身就支持自定义存储引擎，所以索引推荐思路是构建一个支持虚假索引的存储引擎，在它上面建立包含候选索引的空表，再采集样本数据，计算出统计数据提供给优化器，让优化器选出最优索引，整个调用关系如下图所示：

image.png

写在最后

第一：不要指望所有语句都能通过SQL优化

第二：不要过于自信，只针对具体case来优化，而忽略了更复杂的情况

avatar
文章
阅读
粉丝