MySQL 语句的性能优化

1 mysql架构介绍

1.1 逻辑架构

1.2 MySQL存储引擎

查看支持的存储引擎命令

show engines;

查看使用的存储引擎命令

show variables like ‘%storage_engine%’;

2. Join查询与索引介绍

2.1 Join查询

关联查询太多join会导致性能降低，高并发情况下尽量减少使用

手写模式join的sql语法：

机器读取顺序：

机读顺序图：

2.1.1 Join 集合关系示意图

2.1.2 Join 查询 SQL语句实例

原数据如下：

inner jion

left jion

right jion

full outer join

union

2.2 索引介绍

2.2.1 索引命名规则

创建索引的一般命名规则

2.2.2 查看表中索引

show index from 表名

2.2.3 索引的数据结构(B+树)

Optimizer 语句优化器

2.2.4 索引的选择性

索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高，可以选择该字段设置索引。

2.2.5 性能瓶颈

2.2.6 性能优化流程

3 explain查看SQL执行计划分析

3.1 id - 表的读取顺序

3.2 select_type - 查询类型

3.3 table - 查询哪张表

显示执行这一步操作的是哪张表；

3.4 type - 数据读取操作的操作类型（重要）

system 表中只有一条记录

const 通过索引一次就找到了

eq_ref 唯一索引扫描，每个索引建表中只有一条记录与之匹配（常见主键、唯一索引）

ref 非唯一索引，返回匹配某个单独值的所有行

range检索给定范围，使用一个索引来选择航，key列显示使用哪个索引（一般是where语句中出现出现了 between、>、

index遍历索引树（虽然all与index都读全表，但index是从整个索引数遍历，而all是从硬盘中读取）

all遍历全表

3.5 possible_keys - 哪些索引可以使用

3.6 key - 哪些索引被实际使用（重要）

3.7 key_len - 索引中使用的字节数

3.8 ref - 表之间的引用

3.9 rows - 每张表有多少行被优化器查询（重要）

3.10 Extra- 额外信息 （重要）

3.10.1 Using filesort - 未能使用索引排序（排序效率低，尽快优化）

3.10.2 Using temporary - 排序使用临时表（需要优化）

3.10.3 Using index - 覆盖索引，获取的字段数量和顺序与索引一致（非常好）

3.10.4 impossible where - WHERE子句的值总是false,不能获取任何元素

4 多表jion关联查询索引优化

4.1 单表

4.2 两表 - 左连接右表加索引，右连接左表加索引

4.3 三表 - 左连接右表加索引，右连接左表加索引

5 索引优化

建表

5.1 全值匹配是最好

5.2 最佳左前缀法则（第一索引不能丢、中间索引不能断）

索引查询的顺序不会导致索引失效，因为mysql优化器会进行优化，但是建议按索引顺序，减少多余优化器工作时间

5.3 索引列上不操作（计算、函数、手动or隐式类型转换）

不在索引列上做任何操作（计算、函数、（手动or隐式）类型转换），会导致索引失效转为全表扫描

5.4 范围之后全失效

存储引擎不能使用索引中范围条件右边的列

5.5 覆盖索引尽量用，select * 要杜绝

尽量使用覆盖索引（只查询设置了索引的列，或复合索引的某些字段），减少 select *

5.6 索引列使用 !=、<> 会失效

索引列上使用 !=、 时索引失效，导致全表扫描

5.7 索引列使用 is null ,is not null 会失效

索引列使用 is null ,is not null 时索引失效，会导致全表扫描

5.8 like “%” 加最左边会失效

like以通配符开头（’%abc’）索引会失效，导致全表扫描

使用覆盖索引,解决 like以通配符开头（’%abc’）索引会失效问题

5.9 字符串必须加引号

字符串 不加单引号 索引会失效 - 本质是因为第三条：隐式类型转换

5.10 OR 连接索引会失效

5.11 小测试：

6 其他优化

6.1 小表驱动大表

6.2 优化子查询（JOIN代替子查询）

执行子查询时需要为内层查询语句建立一张临时表，查询完毕要清除临时表，效率不高，

JOIN连接查询不需要建立临时表，查询效率要更高。

6.3 order by 优化

表结构

建立复合索引

6.4 group by 优化

where高于having,能在where中限定的条件不要去having限定

7 慢查询日志

7.1 慢查询日志是什么？

7.2 如何开启慢查询日志？

本次开启慢查询日志，重启数据库后会失效（建议）

7.3 什么样的SQL语句会记录到慢查询日志？

7.4 设置慢查询日志阈值时间

Mysql配置文件中修改如下：

7.5 测试慢查询日志功能

7.6 慢日志分析工具

常用分析命令：

------------END-----------