我们来学mysql -- 查询成本之索引选择(原理篇)

题记

• 在真正动身前往景区前，通过地图找出了所有可行的路线
• 同样道理，在真正执行一条查询语句前，MYSQL会找出所有可执行的方案
• 所有可行的线路摆在了面前，通过红绿灯数，收费总金额，拥堵情况确定最终线路
• 所有可执行方案摆在InnoDB面前，有无索引可使用，使用哪个索引，找出成本最低方案

整活

• 回顾ucoding表

CREATE TABLE `test`.`Untitled`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `k1` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `k2` int(0) NOT NULL,
  `k3` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `k_part1` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `k_part2` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `k_part3` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `mark` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_key1`(`k1`) USING BTREE,
  UNIQUE INDEX `uk_key2`(`k2`) USING BTREE,
  INDEX `idx_key3`(`k3`) USING BTREE,
  INDEX `idx_key_part`(`k_part1`, `k_part2`, `k_part3`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 20001 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

• 执行语句

SELECT
	* 
FROM
	ucoding 
WHERE
	k1 IN ( 'B1c34', 'Baa36', 'C49e3' ) 
	AND k2 > 10 
	AND k2 < 1000 AND k3 < k2 
	AND k_part1 LIKE '%part1_7%' 
	AND mark = 'mark_65'

• 分析可能用到的索引
  • 能形成扫描区间，就是可能使用的索引
  • k1 IN ( 'B1c34', 'Baa36', 'C49e3' ) 搜索条件可以用二级索引idx_key1
  • k2 < 1000 AND k3 < k2搜索条件可以用二级索引uk_key2
  • k3 < k2搜索条件的索引列，没有与常数进行比较，产生不了扫描区间 ，用不上uk_key2 或 idx_key3
  • k_part1 LIKE '%part1_7%'，虽满足联合索引使用的最左原则，但是like 操作符用**%通配符**开头的字符串，不能产生扫描区间，用不上idx_key_part
  • mark = 'mark_65',压根没有在该列上建立索引
• 因此可能用到的索引 (possible keys) 是 idx_key1 和 uk_key2
  • 想到了什么吗，是的，就是explain 输出的 possible_keys 列值
    

成本

• 通过上面分析 知道了possible_keys，但是用哪个呢？
• 公式
  • 查询成本 = I/O成本+CPU成本
  • I/O成本 = 页数 * 1.0 + 1.1
  • CPU成本 = 记录数 * 0.2 + 1.0
• 页数和记录数
  • 关注show TABLE STATUS like 'ucoding'输出的Rows和data_length
    
  • Rows :表示记录数，对于InnoDB是个估计值
  • Data_length：表示表占用存储空间的字节数，对于InnoDB
    Data_length = 聚簇索引的页数 * 每个页面大小
  • 对于ucoding如果全表扫描
    • CPU成本 = （20085 * 0.2 + 1.0 ）
      0.2指访问一条记录的所需成本常数，1.0表示微调
    • I/O成本 = （2637824 / 16 /1024 ) * 1.0 + 1.1
      （2637824 / 16 /1024 ) 表示页数，1.0 指加载一个页面的成本常数，1.1 表示微调
    • 全表扫描成本为4018+ 162.1= 4,180.1

分析

• mysql查询器计算出使用idx_key1和uk_key2各自成本后，选择其一，生成执行计划
• 这里，不挨个做分析，让我们从explain的视角，通过force index人为控制执行计划，比较哪个索引执行计划成本小
  • force index(idx_key1)
    
  • force index(uk_key2)
    
• 通过比较使用idx_key1成本最小，实际上mysql采用的索引就是idx_key1