mysql 优化
由于此处我并不是一个专职的 DBA，更多倾向于业务。技术开发，对于 DBA 常常会负责的硬件调参等不太了解，也不会花时间在这些方面，主要讲和记录一些常用的东西
关于版本的选择
- 官方版本（Oracle Mysql）
- Percona 分支 与官方基本兼容，在这个基础上再优化 20%左右的性能
- MariaDB 分支，与官方版本冲突较大，酌情考虑
参数调参
- 若选择上面两个分支版本，可以开启 Thread Pool 特性，保证高并发时的性能
- default-storage-engine=InnoDB MyISAM 引擎已经可以淘汰了
- innodb_buffer_pool_size 大小，若为单实例则可以考虑物理内存的 50%~70%
- innodb_flush_log_at_trx_commit & sync_binlog =1 保证数据不丢失
- innodb_flush_log_at_trx_commit 事务日志从 buffer 中写到 redo log 中
- =1 代表每次提交事务时，都刷入磁盘，默认参数
- sync_binlog 将二进制日志文件刷到磁盘上
- 每向二进制日志文件写入 1 条 SQL or 1 个事务后，将二进制文件刷到磁盘
- innodb_flush_log_at_trx_commit 事务日志从 buffer 中写到 redo log 中
- innodb_file_per_table 独立表空间
innodb_data_file_path = ibdata1:1G:autoextend缺省情况下，在数据目录中创建一个名为 ibdata1 的单个系统表空间数据文件。系统表空间数据文件的大小和数量由 InnoDB_DATA_FILE_PATH 启动选项定义，可以从 10MB 调大- 设置 innodb_log_file_size=256M，设置 innodb_log_files_in_group=2
- innodb_log_file_size 日志文件大小，太大会导致回放日志时缓慢，太小会导致 innodb 频繁切换文件，导致数据库的检查点（CheckPoint），导致缓存脏页的小批量刷新
- innodb_log_files_in_group 控制日志文件数，对于 mysql 事务而言日志文件是循环覆写的。
- key_buffer_size 设置 32M，建议关闭 query Cache
- key_buffer_size 指定索引缓冲区大小，决定索引处理速度，尤其是索引读的速度。哪怕不适用 MyISAM 表，内部的临时磁盘也是用 MyISAM 表的
- query cache 除非系统的数据库更新极少，不然这块 QC 提高的性能远不如关闭来的高。因为这块需要频繁更新相关的 cache 内容
关于数据库规范
- 都设置一个无业务用途的自增列做主键
避免使用系统自生成的主键 id，无序且消耗大，设置一个新的主键，也便于后期做拓展，唯一标识等
- 长度应适度的小，此外搭配上 NOT NULL 约束，提高性能
- 尽量不要用 TEXT/BLOB 类型，若需要尽量拆到拓展表去
对于 select、insert 语句的效率都会有较大的影响
- 只读取你要的列
输出应指定化，一是开发规范要求，二是避免回表，或者查询到 TEXT/BLOB 类型的字段影响性能
- 建 50%左右长度的前缀索引足以满足大部分的查询需求了
- 子查询建议换为 join 查询
mysql 在优化时也是这么处理的
- 关联 join 查询时，应使关联字段尽量类型一致，且都有索引
方便小表驱动大表，且是通过索引列进行 join，而不是通过块查询的进行关联
- 多表 join 查询且有排序时，排序字段需要是驱动表的，不然排序列无法使用到索引
- 多用复合索引，少用多个独立索引，对于基数（Cardinality）太小的，比如低于 255 的列就不需要创建索引了
- 类似分页功能 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 |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+