MySQL数据库优化步骤

目录

一、可以从哪些方面进行数据库调优？

二、优化步骤

1、查看系统性能参数

2、比较页的开销

3、定位执行慢的SQL语句：查询慢日志

4、查看SQL执行成本：Show Profile

5、分析查询语句：EXPLAIN

 三、索引优化与查询优化

1、索引失效的情况

2、关联查询优化

3、JOIN语句的底层原理

4、子查询优化

5、排序优化

6、GROUP BY分组优化、LIMIT分页优化

7、覆盖索引

8、索引条件下推（ICP）

9、其他查询优化策略

四、数据库其他调优策略

1、调优的目标

2、如何定位调优问题

3、调优的维度和步骤

4、优化MySQL服务器

5、优化数据库结构

6、大表优化

7、其他调优操作

---

一、可以从哪些方面进行数据库调优？

1. 索引失效、没有充分利用到索引一-索引建立
2. 关联查询太多JOIN (设计缺陷或不得已的需求)--SQL优化
3. 服务器调优及各个参数设置(缓冲、线程数等)--调整my.cnf
4. 数据过多——分库分表

        虽然 SQL 查询优化的技术有很多，但是大方向上完全可以分成 物理查询优化和 逻辑查询优化两大块。

1. 物理查询优化是通过 索引和 表连接方式 等技术来进行优化，这里重点需要学握索引的使用。
2. 逻辑查询优化就是通过 SOL 等价变换 提升查询效率，直白一点就是说，换一种查询写法执行效率可能更高。

---

二、优化步骤

1、查看系统性能参数

• Connections: 连接MySQL服务器的次数。
• Uptime: MySQL服务器的上线时间。
• slow_queries: 慢查询的次数
• Innodb_rows_read: Select查询返回的行数
• Innodb_rows_inserted: 执行INSERT操作插入的行数
• Innodb_rows_updated: 执行UPDATE操作更新的行数
• Innodb_rows_deleted: 执行DELETE操作删除的行数
• Com_select: 查询操作的次数。
• Com_insert: 插入操作的次数。对于批量插入的INSERT 操作，只累加一次
• Com_update: 更新操作的次数
• Com_delete: 删除操作的次数。

SHOW STATUS LIKE '参数';

2、比较页的开销

• last_query_cost：用到的页数量。（用来比较页的开销）

3、定位执行慢的SQL语句：查询慢日志

• long_query_time：SQL语句运行时间超过该参数的值时，就称之为慢查询。

3.1 打开慢查询日志（默认是关闭的）

# 临时修改
# 打开慢查询日志
SET slow_query_log = ON;

# 修改慢查询门槛阈值
SET GLOBAL long_query_time=秒数;
SET long_query_time=秒数;


# 永久修改
修改配置my.cnf配置文件，在[mysqld]下修改参数，然后重启服务器。

3.2 分析慢查询语句

# 查看已有多少条慢查询语句
SHOW variables LIKE 'slow_queries';

# 使用mysqldumpslow来查看慢查询语句。
mysqldumpslow -s -a t /var/lib/mysql/table-slow.log

4、查看SQL执行成本：Show Profile

# 打开show profile功能
SET profiling = 'ON';

# 查看最近执行的查询语句
SHOW profiles;

# 查看某一条查询语句
SHOW profile for query 1;

5、分析查询语句：EXPLAIN

1 基本语法

EXPLAIN SELECT * FROM table;

DISCRIBE SELECT * FROM table;

2 EXPLAIN语句输出的列作用

列名	描述
id	本条select语句对应的专属ID
select_type	查询类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际上使用的索引的长度
ref	当索引列等值查询时，与索引列进行等值匹配的对象信息
rows	语句的需要读取的记录的条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
extra	一些额外信息

3、EXPLAIN的四种输出格式

1. 传统格式
2. JSON格式
3. TREE格式
4. Worldbench可视化输出

EXPLAIN FORMAT='格式' SELECT * FROM table1;

id详解

1. id如果相同，可以认为是一组，从上往下顺序执行
2. 在所有组中，id越大，优先级就越高，越先执行
3. id的每个号码，都表示一趟独立的查询，查询趟数越少越好。

select_type详解

类型	描述
SIMPLE
PRIMARY
UNION
UNION RESULT
SUBQUERY
DEPENDENT SUBQUERY
DEPENDENT UNION
MATERIALIZED
UNCACHEABLE SUBQUERY
UNCACHEABLE UNION

type详解

1. 结果值从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index merge > unique_subquery > index_subquery > range > index > ALL
2. 其中比较重要的几个提取出来 (见上图中的蓝色) 。SQL 性能化的目标: 至少要达到 range 级别，要求是ref级别，最好是 consts级别。(阿里巴巴开发手册要求)

key_len详解

1. 主要针对联合索引
2. 长度越长越好。

Extra详解

        略

---

 三、索引优化与查询优化

1、索引失效的情况

1. 带有运算
2. 使用函数
3. LIKE使用%XXX左模糊查询，因为mysql是最左原则，使用XXX%右模糊查询是可以使用索引的，但是左模糊违背了最左原则所以不行
4. 使用范围运算，not in，in > ,<都不行
5. 查询的字段不是索引的最左字段，同样是因为最左原则
6. 字段类型不匹配，常见的隐式数据类型转换，mobile=1356不会走索引，会转换为字符串可以查询但是，mobile='1356'会走索引
7. or条件左边的是索引字段，右边的不是。也不会走索引，因为or是一个并集
    

一般性建议：

1. 对于单列索引，金陵选择针对当前query过滤性更好的索引。
2. 在选择联合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
3. 在选择联合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引。
4. 在选择联合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。
5. 总之，书写SQL语句时，尽量避免造成索引失效的情况。

2、关联查询优化

1. 使用JOIN时，优先在被驱动表中添加索引。
2. 对于内连接来说，查询优化器可以决定谁是驱动表，谁是被驱动表。（一般是小表驱动大表）。
3. 能够直接用多表关联尽量直接关联，不使用子查询（减少查询的趟数）
4. 不建议使用子查询，而是将子查询SQL拆开结合程序多次查询，或使用JOIN来代替子查询。 

3、JOIN语句的底层原理

1. 使用小表驱动大表（本质是减少外层循环的数据数量）

   -- 推荐写法
   select tb1.b tb2.* from tb1 straight_join tb2 on (tb1.b=tb2.b) 
   where tb2.id <= 100;

2. 为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）
3. 增大 join buffer size 的大小（一次缓存的数据越多，那么内层包含的扫描次数就越少）
4. 减少驱动表不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）
5. 使用 Hash Join

4、子查询优化

1. 尽量使用JOIN代替子查询

5、排序优化

两种排序方式，分别是 FileSort 和 Index排序

1. Index排序中，索引可以保证数据的有序性，不需要在进行排序，效率高，占用资源少。
2. FileSort排序则一般在内存中进行，占用CPU比较多，如果待排序结果较大，甚至会IO到磁盘中进行排序，效率较低。

优化建议

1. SQL中，可以在where子句和order by子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY子句避免使用FileSort排序。
2. 尽量使用Index完成Order BY排序。如果WHERE和ORDER BY后面是相同的列就是用单列索引，如果不是就是用联合索引。
3. 无法使用Index时，需要对FileSort方式进行调优。
   1. 提高 sort_buffer_size
   2. 提高max_length_for_sort_data
   3. 使用Order BY时不要select *
4. 避免索引失效的情况，例如升序降序掺杂、丢失最左边的索引、丢失中间的索引、使用非索引排序、使用IN()等范围查询。

6、GROUP BY分组优化、LIMIT分页优化

GROUP BY分组优化

1. group by 使用索引的原则几乎跟order by一致，group by 即使没有过滤条用到索引，也可以直接使用索引。·group by 先排序再分组，遵照索引建的最佳左前缀法则
2. 当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size 参数的设置
3. where效率高于having，能写在where限定的条件就不要写在having中了
4. 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
5. 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

LIMIT分页优化

# 优化之前，不推荐
SELECT * FROM tb1 LIMIT 2000000, 10;

# 优化一，在索引上完成分页，然后根据主键回表
SELECT * FROM tb1 t1, 
(SELECT id FROM tb1 ORDER BY id LIMIT 2000000, 10) t2
WHERE t1.id = t2.id;

# 优化二，如果主键是自增的，那么可以直接使用WHERE定位到具体位置
SELECT * FROM tb1 WHERE id > 2000000 LIMIT 10;    

7、覆盖索引

定义：在索引中已经包含查询所需要的信息时，就不需要回表了。

好处：

1. 避免InnoDB表进行索引的二次查询（回表）
2. 可以把随机IO编程顺序IO加快查询效率

8、索引条件下推（ICP）

解释：在使用非聚簇索引时，查询语句在回表之前，筛选多次，减少回表的数据量。

使用条件：

1. 如果表访问的类型为 range、 ref、 eq_ref和ref_or_null 可以使用ICP
2. ICP可以用于 InnoDB 和MyISAM表，包括分区表InnoDB 和 MyISAM 表
3. 对于InnoDB 表，ICP 仅用于二级索引。ICP 的目标是减少全行读取次数，从而减少I/O 操作当SQL使用覆盖索引时，不支持ICP。因为这种情况下使用ICP 不会减少I/O。4.
4. 相关子查询的条件不能使用ICP

9、其他查询优化策略

1. EXISTS和IN的区分

   # 当B表小时，使用IN
   SELECT * FROM A WHERE cc IN (SELECT cc FROM B);
   
   # 当A表小时，使用EXISTS
   SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc = A.cc);

2. COUNT(*) 和 COUNT，MYISAM和InnoDB有区别

---

四、数据库其他调优策略

1、调优的目标

1. 尽可能 节省系统资源，以便系统可以提供更大负荷的服务。(量更大)
2. 合理的结构设计和参数调整，以提高用户操作 响应的速度。(响应速度更快)
3. 减少系统的瓶颈，提高MySQL数据库整体的性能。

2、如何定位调优问题

1. 用户反馈（主要）
2. 日志分析（主要
3. 服务器资源使用监控
4. 数据库内部状况监控

3、调优的维度和步骤

1. 首先选择一个合适的数据库。
2. 优化表设计
   1. 表结构尽量遵循三范式原则
   2. 如果查询比较多，尤其是多个表联查的时候，可以采用反范式来提高查询的效率。
   3. 数据类型的选择。
3. 优化逻辑查询
4. 优化物理查询
5. 使用redis或者memcached作为缓存
6. 库级优化
   1. 读写分离
   2. 数据分片

4、优化MySQL服务器

优化服务器硬件

1. 配置较大的内存，减少磁盘IO次数，或者增加缓冲区容量。
2. 配置告诉磁盘系统
3. 合理分配磁盘IO
4. 配置多处理器

优化MySQL参数

1. innodb_buffer_pool_size：表和索引的最大缓存
2. key_buffer_size：所以缓冲区大小
3. table_cache：同时打开的表的个数
4. query_cache_size：查询缓冲区的大小。
5. query_cache_type：牵扯到是否使用查询缓存区
6. sort_buffer_size：需要进行排序的线程分配的缓冲区的大小
7. join_buffer_size = 8M：联合查询操作所能使用的缓冲区大小
8. read_bufer_size：每个线程连续扫描时为扫描的每个表分配的缓冲区的大小
9. innodb_flush_log_at_trx_commit：何时将缓冲区的数据写入日志文件
10. innodb_log_buffer_size：事务日志所使用的缓冲区
11. max_connections：允许连接到MySQL的最大数量
12. back_log：控制监听TCP端口时设置的积压请求栈大小
13. thread_cache_size：线程池缓存线程数量的大小
14. wait_timeout：一个请求的最大连接时间
15. interactive_timeout：表示服务器在关闭连接前等待行动的秒数

5、优化数据库结构

1. 拆分表：冷热数据分离
2. 增加中间表
3. 增加冗余字段
4. 优化数据类型 
   1. 整数类型的优化
   2. 在文本类型和整数类型之间选择，优先考虑整数类型
   3. 避免使用TEXT，BLOB数据类型
   4. 避免使用ENUM，因为ORDER BY效率低
   5. 使用时间戳来储存时间
   6. 使用DECIMAL定点数来代替浮点数
5. 优化插入记录的速度
   1. 提前禁用索引
   2. 提前禁用唯一性检查
   3. 使用批量插入
   4. 尽量使用LOAD DATA INFLE代替INSERT
   5. 提前禁用外键检查
   6. 提前禁止自动提交
6. 使用非空约束
7. 分析表、检查表、优化表

   # 分析表，立即更新表索引的区分度
   ANALYZE TABLE tb1;
   
   # 检查表
   CHECK TABLE 
   
   # 优化表，但只优化字节数多的类型
   OPTIMIZE TABLE

8.  以上方式都是有利有弊的，需要权衡利弊谨慎优化。

6、大表优化

1. 限定查询的范围
2. 读写分离
3. 垂直分库、垂直分表
4. 水平拆分

7、其他调优操作

1. 服务器语句超时处理
2. 创建全局通用表空间
3. 隐藏索引

---