Mysql的表优化和性能排查 [入门篇]

1. 表和字段的规划

1. 字段长度的规划要准确, 可以varchar(100) 就不要varchar(200), 过多浪费将会导致-----表联查时占用更多内存
2. 字段尽量设置为NOT NULL, 因为值为NULL会占更多字节空间
3. 尽量将定长字段放在一张表, 不定长字段放在另一张表, 用主键关联起来
4. 尽量选用定长字段, 这样在运算和排序时速度更快
5. 索引要命中关键点, 而且建索引时越少字段越好
6. 时间和日期请用无符号的时间戳(int) , 避免用(data)

2. 前置分析

1. 查询已连接的全部会话, 判断是否连接数占满

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.PROCESSLIST

2. 查询业务耗时记录按从大到小排序 [前提是已设置 log_output=table 默认记录在slow_log表]

SELECT * FROM slow_log ORDER BY query_time DESC;

3. 检查某个语句的内存开销 [进入mysql命令行模式]

# 先列出运行中的语句
show profiles;

# 然后根据Query_ID查询内存开销,假设要看Query_ID=5的那个语句
show profile Memory for query 5;

3. 重点分析和优化

1. 查询慢?
   检查where条件的索引命中率
   缩小搜索的范围, 尽量确定目标列而少用SELECT *
2. 单表数据量太大?
   通过分表来降低索引的层数
   考虑做读写分离
3. 分页查询慢?
   检查limit是否有偏移, 正常来说是: 上一次查到第5万,这一次应从第50001 ~ X
4. 加入了子查询导致变慢?
   尽量将子查询替换为关联查询, 但关联也不宜过多[参考第7点]
5. 复杂查询慢?
   尽量分拆为单个查询, 单个查询的缓存利用率更大,并且减少了锁的竞争
6. 删除大量数据很慢?
   按主键分批次删除 (主键本身有索引, 而且分批次可以重复利用有限的缓存)

DELETE FROM xxx WHERE id < max(id) LIMIT 50000;

7. 关联查询慢?
   拆分关联为多个单查询, 然后将各个结果放在应用里做关联