MySQL（三）高性能优化实战

索引优化

一、索引基本

1.概念及删除：

1.1 索引是存储引擎快速找到记录的一种数据结构。
1.2 索引不能修改，只能删除重建。删除方法：

DROP INDEX warehouse_id ON job_summary;

2.索引类型及创建：

SHOW INDEX FROM job_summary;

2.1 主键索引 PRIMARY KEY： 特殊的唯一索引，通常在建表同时建立（如id），不允许重复和空值。

2.2 唯一索引 UNIQUE： 唯一索引（如果是组合唯一索引则列组合的值必须唯一）所在列的值必须唯一，可以为空值。创建方法：

ALTER TABLE job_summary ADD UNIQUE (`warehouse_id`);

2.3 组合索引 INDEX： 一个索引包含多个列，常用于避免回表查询。创建方法：

ALTER TABLE job_summary ADD INDEX warehouseid_summarizerid(`warehouse_id`,`summarizer_id`);

2.4 普通索引 INDEX： 最基本也是用的最多的索引，没有任何限制。创建方法：

ALTER TABLE job_summary ADD INDEX create_time(`create_time`);

2.5 全文索引 FULLTEXT： 全文检索，搜索引擎的重要技术。创建方法：

ALTER TABLE job_summary ADD FULLTEXT job_content(`job_content`);

二、索引优化

1. 查看索引性能

1.1 通过EXPLAIN语句

创建的表及key如下：

CREATE TABLE `invoice_company` (
  `id` char(32) NOT NULL,
  `warehouseid` char(32) DEFAULT NULL,
  `name` varchar(200) DEFAULT NULL,
  `type` char(1) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `invoice_company_key1` (`warehouseid`,`type`) USING BTREE,
  KEY `invoice_company_key2` (`warehouseid`) USING BTREE,
  KEY `invoice_company_key3` (`warehouseid`,`type`,`name`) USING BTREE,
  KEY `invoice_company_key4` (`type`) USING BTREE,
  KEY `invoice_company_key5` (`name`) USING BTREE,
  KEY `invoice_company_key6` (`create_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

其中的key如下：

//  例1语句如下：
EXPLAIN SELECT * FROM invoice_company WHERE type='A';

//  例2语句如下：
EXPLAIN SELECT * FROM invoice_company WHERE name LIKE  '产品销售有限公司%';

//例3语句如下：
EXPLAIN SELECT * FROM invoice_company WHERE name LIKE  '%产品销售有限公司%';

//例3语句如下：
EXPLAIN SELECT * FROM invoice_company WHERE type='A' AND name LIKE  '产品销售有限公司%';

例1结果如下：

例2结果如下：

例3结果如下：

例4结果如下：

其中：
key： 表示该查询所用到的索引，若为空则代表未用到任何索引。

filtered： 返回记录比例数，或者说跟据索引能查到记录占总记录的比例。比例越高，走索引的命中率越高。一般来说，大于30%会走索引，否则全表扫描。

Extra： Using where表示不走索引查询 – 即需要通过回表查询结果，而Using Index则表示走索引查询 - 即直接通过索引就能查询到主要数据。

1.2 通过SHOW STATUS语句

SHOW STATUS LIKE 'Handler_read%';

其中：
Handler_read_key： 值很高说明索引当前正在使用的数量多
Handler_read_rnd_next： 数据文件中读取下一行的请求数，如果正在进行大量的全表扫描，
值会比较高，值越高说明索引的利用效果越差。

2.索引优化

2.1 建立的索引及其使用要比全表扫描快
否则将全表扫描。( 具体看上面的filtered值是否在30%内)

2.2 根据最左原则使用索引
避免like 前缀查询（上面例2、3）、组合索引避免缺少索引左列导致索引失效(上面例4)

2.3 尽量用in
因为union要多查一步更耗cpu，而or的条件列中有索引，其后面列如果没有索引涉及到的索引都会失效，另外像负向条件查询：!=、<>、not in、not exists、not like都不会使用索引

2.4 范围条件查询可以命中索引
范围条件查询：<、<=、>、>=、between等
不过：
2.4.1 范围列可以用到索引（联合索引必须是最左前缀），但是范围列后面的列无法用到索引；

2.4.2 范围查询和等值查询同时存在，优先匹配等值查询列的索引；

2.5 条件查询列如果有计算、函数不会用到索引
例如：

EXPLAIN SELECT id,warehouseid, name  FROM invoice_company WHERE DATE_FORMAT(create_time,"%Y-%m-%d") >= '2021-09-30'

结果：

3.explain结果中type字段

3.1 定义
找到所需数据使用的扫描方式

3.2 方式（从前到后扫描速度由快到慢）

• system： 系统表，少量数据，一般不需要进行磁盘IO

EXPLAIN SELECT * FROM mysql.time_zone_name

• const： 常量连接
• eq_ref： 主键索引或者非空唯一索引等值扫描
• ref： 非主键非唯一索引等值扫描
• range： 范围扫描
• index： 索引树扫描
• all： 全表扫描

其他

一、分析不走索引的其他原因

未走索引可以再用 force index（）强制执行看是否走
1.表或索引字段的字符集格式不同
2.索引字段基数太小
但如果又需要用到，则可以用上述强制执行索引方法。

上一篇跳转—MySQL（二）高性能优化规范)

---

随心所往，看见未来。Follow your heart，see light！

欢迎点赞、关注、留言，一起学习、交流！