mysql优化参考手册--真

mysql 优化

由于此处我并不是一个专职的 DBA，更多倾向于业务。技术开发，对于 DBA 常常会负责的硬件调参等不太了解，也不会花时间在这些方面，主要讲和记录一些常用的东西

关于版本的选择

1. 官方版本（Oracle Mysql）
   1. Percona 分支 与官方基本兼容，在这个基础上再优化 20%左右的性能
   2. MariaDB 分支，与官方版本冲突较大，酌情考虑

参数调参

1. 若选择上面两个分支版本，可以开启 Thread Pool 特性，保证高并发时的性能
2. default-storage-engine=InnoDB MyISAM 引擎已经可以淘汰了
3. innodb_buffer_pool_size 大小，若为单实例则可以考虑物理内存的 50%~70%
4. innodb_flush_log_at_trx_commit & sync_binlog =1 保证数据不丢失
   1. innodb_flush_log_at_trx_commit 事务日志从 buffer 中写到 redo log 中
      1. =1 代表每次提交事务时，都刷入磁盘，默认参数
   2. sync_binlog 将二进制日志文件刷到磁盘上
      1. 每向二进制日志文件写入 1 条 SQL or 1 个事务后，将二进制文件刷到磁盘
5. innodb_file_per_table 独立表空间
6. innodb_data_file_path = ibdata1:1G:autoextend 缺省情况下，在数据目录中创建一个名为 ibdata1 的单个系统表空间数据文件。系统表空间数据文件的大小和数量由 InnoDB_DATA_FILE_PATH 启动选项定义，可以从 10MB 调大
7. 设置 innodb_log_file_size=256M，设置 innodb_log_files_in_group=2
   1. innodb_log_file_size 日志文件大小，太大会导致回放日志时缓慢，太小会导致 innodb 频繁切换文件，导致数据库的检查点（CheckPoint），导致缓存脏页的小批量刷新
   2. innodb_log_files_in_group 控制日志文件数，对于 mysql 事务而言日志文件是循环覆写的。
8. key_buffer_size 设置 32M，建议关闭 query Cache
   1. key_buffer_size 指定索引缓冲区大小，决定索引处理速度，尤其是索引读的速度。哪怕不适用 MyISAM 表，内部的临时磁盘也是用 MyISAM 表的
   2. query cache 除非系统的数据库更新极少，不然这块 QC 提高的性能远不如关闭来的高。因为这块需要频繁更新相关的 cache 内容

关于数据库规范

1. 都设置一个无业务用途的自增列做主键

   避免使用系统自生成的主键 id，无序且消耗大，设置一个新的主键，也便于后期做拓展，唯一标识等

2. 长度应适度的小，此外搭配上 NOT NULL 约束，提高性能
3. 尽量不要用 TEXT/BLOB 类型，若需要尽量拆到拓展表去

   对于 select、insert 语句的效率都会有较大的影响

4. 只读取你要的列

   输出应指定化，一是开发规范要求，二是避免回表，或者查询到 TEXT/BLOB 类型的字段影响性能

5. 建 50%左右长度的前缀索引足以满足大部分的查询需求了
6. 子查询建议换为 join 查询

   mysql 在优化时也是这么处理的

7. 关联 join 查询时，应使关联字段尽量类型一致，且都有索引

   方便小表驱动大表，且是通过索引列进行 join，而不是通过块查询的进行关联

8. 多表 join 查询且有排序时，排序字段需要是驱动表的，不然排序列无法使用到索引
9. 多用复合索引，少用多个独立索引，对于基数（Cardinality）太小的，比如低于 255 的列就不需要创建索引了
10. 类似分页功能 sql，建议先通过主键关联，再返回结果集

SQL

SQL优化

关于子查询

根据官方文档的描述，join比等价的子查询语句性能会更好或者相等，因为优化器会对这些查询语句进行性能优化。所以我们应尽量使用join，子查询相比的优势在于可读性

优化为join案例

explain select * from test_student where a in (select a from test_student2) li
                             mit 100;            
+----+--------------+---------------+------------+--------+---------------+---------+---------+---------------+--------+----------+--------+
| id | select_type  | table         | partitions | type   | possible_keys | key     | key_len | ref           | rows   | filtered | Extra  |
+----+--------------+---------------+------------+--------+---------------+---------+---------+---------------+--------+----------+--------+
| 1  | SIMPLE       | <subquery2>   | <null>     | ALL    | <null>        | <null>  | <null>  | <null>        | <null> | 100.0    | <null> |
| 1  | SIMPLE       | test_student  | <null>     | eq_ref | PRIMARY       | PRIMARY | 4       | <subquery2>.a | 1      | 100.0    | <null> |
| 2  | MATERIALIZED | test_student2 | <null>     | ALL    | <null>        | <null>  | <null>  | <null>        | 9650   | 100.0    | <null> |
+----+--------------+---------------+------------+--------+---------------+---------+---------+---------------+--------+----------+--------+


-- 可以优化为join的形式做处理
explain select * from test_student L inner join test_student2 R on L.a=R.a lim
                             it 100;         
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+--------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra  |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+--------+
| 1  | SIMPLE      | R     | <null>     | ALL    | <null>        | <null>  | <null>  | <null>     | 9650 | 100.0    | <null> |
| 1  | SIMPLE      | L     | <null>     | eq_ref | PRIMARY       | PRIMARY | 4       | jarvis.R.a | 1    | 100.0    | <null> |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+--------+

-- 从执行计划上我们可以看出，join的步骤更少，且MySQL可以选择小表驱动大表，比如说test_student2做驱动表操作，会比子查询的性能更好。当然MySQL对于子查询的语句也会有相应的优化内容
-- 比如还是刚刚的那条子查询语句，通过show warnings可以看到MySQL对其的优化内容
show warnings;

+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                            |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `jarvis`.`test_student`.`a` AS `a`,`jarvis`.`test_student`.`b` AS `b`,`jarvis`.`test_student`.`c` AS `c` from `jarvis`.`test_student` semi join (`jarvis`.`test_student2`) where (`jarvis`.`test_student`.`a` = `<subquery2>`.`a`) limit 100 |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

mysql优化参考手册--真

/archives/7b0e77ed/

作者

tyrantqiao

发布于

2021-04-05

更新于

2023-07-09

许可协议

CC BY-NC-SA 4.0

赏

蟹蟹大佬的打赏，大家一起进步

支付宝

微信