MySQL优化学习笔记（一）

在写程序的时候总会遇到数据库操作很慢的时候，这个时候一个Mysql优化的思路就能把整个操作盘活，所以，今天来聊聊Mysql优化

一、优化整体思路

先找到慢Sql，然后搞懂它为什么会慢；先改SQL和索引，不行再改变表结构，都弄完了还不行再调整数据库的配置；最后实在扛不住了流量，再考虑架构方案。

（一）开启慢查询日志，找到慢SQl

慢查询日志就是装了计时器，只要某条SQL执行的时间超过了咱们设定的阈值，就会把这条SQL记录到日志文件中（与binlog不是一个东西哦，binlog是二级制日志）。

  -- 慢查询相关配置
show variables like 'slow_query_log'; --开启慢查询日志
show variables like 'slow_query_log_file'; -- 日志文件路径
show variables like 'long_query_time';    -- 慢SQL阈值，单位秒，默认10s
show variables like 'log_queries_not_using_indexes'; -- 记录没走索引的SQL

(二)explain，看懂这条SQL会怎么干活

简单来讲是，不真正执行这条SQL，只把执行方案打印出来供我分析，重点看以下的内容：

1. type：查找方式
   最好到最差：system > const > eq_ref > ref > range > index > ALL
2. rows：预估扫描行数
   MySQL预估的为了拿到结果可能会扫描多少行
3. key：实际用了哪个索引
   null = 没用到任何索引。possible_keys 是理论上能用的索引，但是优化器最后没选
4. Extra：额外提示（坑基本都在这里）

• Using index：覆盖索引（√） ，只查索引里的数据，不用回表找原始数据，相当于直接拿现成结果。
• Using index condition：索引下推，在索引阶段就过滤掉一部分数据，减少回表次数。
• Using filesort（×） ：文件排序。没有合适索引帮你排序，MySQL 查到数据后，自己再额外排序。数据量大，内存放不下，就写到磁盘，巨慢。
• Using temporary（×） ：临时表。group by 的时候没索引，MySQL 临时建一张表存中间结果，开销很大。

（三）优化优先级

1. SQL写法+索引
   索引相当于是书的目录。如果没有这个目录，查内容就要翻遍整本书，很费劲。但是，目录并不是越多越好，每次新增一个目录，每次写书都要额外更新目录也会拖慢写入速度

索引规则:
1.联合索引（多个字段一起建索引）：满足最左前缀。好比目录：先按省份，再按城市。你直接查城市不写省份，这个目录就用不了。
2. 索引字段不要做运算、不要类型乱匹配。
比如where phone = 1380000，phone 字段是字符串，你传数字，MySQL 自动转换，索引直接失效，变成全表扫描。
3. like '%关键词' 前面带百分号，相当于目录只能匹配结尾，索引失效；like '关键词%' 可以走索引。
4. 不要写select *。你只需要姓名和手机号，却把所有字段查出来，浪费网络，还很难触发覆盖索引。
5. 大分页limit 100000,10：意思跳过前面 10 万条，拿后面 10 条。MySQL 老老实实扫描 10 万行然后丢掉，很慢。
优化思路：用主键 id 做书签，where id > 100000 limit 10，直接定位起始位置。
6. join 关联查询：关联的字段一定要建索引，尽量不要连超过 3 张表；小表驱动大表（小表去匹配大表）。
7. in 里面的值不要一次性塞几千个，容易让 MySQL 优化器判断出错。
索引踩坑：

• 区分度很低的字段（性别：0/1）不要单独建索引。目录只有两个分类，翻目录和直接翻全书差别不大。
• 单表索引不要超过 5 个，索引多了新增、修改数据会非常慢。

2. 第二优先级：表结构优化
   SQL 和索引都调完，效果还是不行，再考虑改表设计。

1. 字段类型尽量选小的。年龄用 tinyint（0~255 足够），不要直接上 int。越小的数据，内存、磁盘占用越少。
2. 字段尽量设置 not null。null 会增加额外存储开销，索引处理 null 更麻烦，给默认值 0 或者空字符串。
3. 大文本字段 text、blob(二进制大对象) 单独拆到另一张表。日常查询不要加载这些大字段。
4. 主键尽量用自增 id。自增 id 是顺序写入，就像往本子末尾追加记录；UUID 是随机写入，一会写前面一会写后面，会导致页面频繁分裂，性能差。
5. 不要用数据库外键（两张表之间的约束规则）。外键约束交给代码去控制，数据库维护外键会拖慢写入，并发场景还容易死锁。
6. 字符集统一用 utf8mb4，支持 emoji；mysql 里的 utf8 不支持 emoji。

3. 第三优先级：数据库参数调优（最后才动！！！）

参数相当于 MySQL 的内存、磁盘配置。SQL、表结构没问题，压力还大，才去调。

4. 大并发大数据：架构层面优化
   前面优化都是单机 MySQL 的优化。当单台机器、单张表扛不住海量数据和高并发，就要做架构改造，改动成本最高。

读写分离

一主多从：主库只负责写（新增、修改、删除），多个从库负责读查询。
主库通过 binlog 把数据同步给从库。
好处：查询压力分散到多个从库，分担主库压力。
缺点：数据同步有延迟，从库读到的数据可能不是最新。如果业务需要强一致，查询还是走主库。

分库分表

• 垂直分库：按业务拆库。用户相关放用户库，订单放订单库，业务隔离。
• 垂直分表：一张表字段太多，把大字段单独拆出去。
• 水平分表：订单表数据几千万上亿，单表查询很慢。按 id 哈希或者时间范围拆成 order_01，order_02…… 多张表。
  缺点：复杂度暴涨，跨表查询、分页、ID 生成、分布式事务都要自己处理。

配套方案

1. Redis 缓存：热点数据放 Redis，优先读缓存，减少访问 MySQL。要处理缓存穿透、击穿、雪崩。
2. MQ 消息队列：高峰期大量写入请求，丢进队列异步落库，削峰，防止瞬间流量打崩数据库。