MySQL调优(七)-索引优化实战细节

本文从索引优化的实际优化细节和实战案例了解如果利用索引进行优化，话不多说，直入主题：

一、实际优化细节

1）索引列查询时尽量使用准确值

2）尽量使用主键查询、因为主键索引不会触发回表操作、主键索引为聚集索引索引中存储索引行数据。
3）使用前缀索引
截取某列的前几个字节，作为索引，前提是截取的这个部分数不能是重复的，如果是重复的和全列扫描没区别；
基数：某列去重之后的数量，值越小效率越高
select count(distinct city) from citydemo;

4)使用索引扫描来排序，order by非索引列性能比较低。

5）union all or in 都可以使用索引，推荐使用in 但in的数量值是有限的1000个？
能用union all 则不用 union ,union 会自动去重，类似distinct

6)范围列可以用到索引：范围条件是<,<=,>,>=,bettween; 注意范围列可以用到索引，但范围列后面的列不能用到索引，索引最多用于一个范围列

7）强制类型转换会全表扫描：phone 为字符串类型，转数值类型会全表扫描

8）频繁更新/数据区分度不高的列不适合建立索引：
频繁更新会变更B+数，降低数据库性能；
区分度不高的列比如性别（男，女），不能有效过滤数据；
区分度计算规则：count(distinct(列))/count(*)来计算，一般区分度在80%以上可以建立索引。
9）创建索引的列要求不为null，可能会得到不符合预期的结果
10）当需要进行表关联连接的时候，最好不超过3个表，因为需要join的字段，类型必须一致。
将需要关联的列创建为索引列，减少join匹配的数据量，不需要全表扫描；
小表join大表，减少循环次数；或者将小表数据放内存中，然后去从大表获取数据。
思考：大表join 大表，如何解决慢的问题：分区表，切成小表
思考join的实现原理（三种方式）：
普通join：一行一行进行匹配，效率低

基于索引的join：减少匹配过程，匹配到索引才进行回表操作：

基于缓存的join:
可通过join_buffer_size 设置join缓冲区内存大小

11）能使用limit尽量使用limit【如果明确知道只有一条数据返回，limit 1 能够提高效率（减少一次判断，获取即返回）】
12）单表索引控制在5个内（索引越多，占用的存储空间越大，磁盘IO越大）
13）组合索引单索引字段控制在5个内（有最左匹配原则，乱发存储空）
14）创建索引的时候应该避免以下错误：1，索引越多越好，2，过早优化，在不了解系统的情况下进行优化【需要结合实际场景优化，比如通过执行计划从type为all 优化到 const】

二、索引监控

show status like ‘Handler_read%’;
如果Handler_read_key 和 Handler_read_rnd_next使用得比较多，说明索引是有效的，如果Handler_read_*都是0 则表示索引没起多大作用：

三、索引优化分析案例

3.1 据准备：

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `itdragon_order_list`;
CREATE TABLE `itdragon_order_list` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id，默认自增长',
  `transaction_id` varchar(150) DEFAULT NULL COMMENT '交易号',
  `gross` double DEFAULT NULL COMMENT '毛收入(RMB)',
  `net` double DEFAULT NULL COMMENT '净收入(RMB)',
  `stock_id` int(11) DEFAULT NULL COMMENT '发货仓库',
  `order_status` int(11) DEFAULT NULL COMMENT '订单状态',
  `descript` varchar(255) DEFAULT NULL COMMENT '客服备注',
  `finance_descript` varchar(255) DEFAULT NULL COMMENT '财务备注',
  `create_type` varchar(100) DEFAULT NULL COMMENT '创建类型',
  `order_level` int(11) DEFAULT NULL COMMENT '订单级别',
  `input_user` varchar(20) DEFAULT NULL COMMENT '录入人',
  `input_date` varchar(20) DEFAULT NULL COMMENT '录入时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8;

INSERT INTO itdragon_order_list VALUES ('10000', '81X97310V32236260E', '6.6', '6.13', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-28 17:01:49');
INSERT INTO itdragon_order_list VALUES ('10001', '61525478BB371361Q', '18.88', '18.79', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-18 17:01:50');
INSERT INTO itdragon_order_list VALUES ('10002', '5RT64180WE555861V', '20.18', '20.17', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-09-08 17:01:49');

3.2 第一个案例：

--通过查看执行计划发现type=all,需要进行全表扫描
select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";

--优化一、为transaction_id创建唯一索引
 create unique index idx_order_transaID on itdragon_order_list (transaction_id);
 
--当创建索引之后，唯一索引对应的type是const，通过索引一次就可以找到结果，普通索引对应的type是ref，表示非唯一性索引赛秒，找到值还要进行扫描，直到将索引文件扫描完为止，显而易见，const的性能要高于ref
 explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E"; 
 
 --优化二、使用覆盖索引，查询的结果变成 transaction_id,当extra出现using index,表示使用了覆盖索引
 explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";

3.3 第二个案例：

--创建复合索引
create index idx_order_levelDate on itdragon_order_list (order_level,input_date);

--创建索引之后发现跟没有创建索引一样，都是全表扫描，都是文件排序
explain select * from itdragon_order_list order by order_level,input_date;

--可以使用force index强制指定索引
explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;

--其实给订单排序意义不大，给订单级别添加索引意义也不大，因此可以先确定order_level的值，然后再给input_date排序
explain select * from itdragon_order_list where order_level=3 order by input_date;