MySQL性能优化

MySQL性能优化
最近看了一篇MySQL优化的ppt，把相关知识点总结一下。

让数据库多做它擅长的事情：
尽量不在数据库做运算
复杂运算移到程序端CPU
尽可能简单应用MySQL
举例：md5(xx字段)、ORDER BY RAND()

合理分表不超载
User id
Date
Area
...
一年内的单表数据：纯int不超过1000W，含char不超过500W，单库不超过300-400个表

表字段少而精
IO高效、全表遍历、表修复快
提高并发、alter table快
单表不超过50个纯int字段
道标不超过20个char（10）字段
单表字段数上限控制在20-50个

效率优先、提升性能
适当时牺牲范式、加入冗余

拒绝3B
大SQL-BIG SQL
大事务-BIG Transaction
大批量-BIG Batch

数字型VS字符串型索引
更高效
查询更快
占用空间更小
举例：用无符号INT存储IP，而非char(15)

优先使用ENUM或SET
ENUM占用1字节，转为数值运算
举例：'sex' enum('F','M') COMMENT '性别'

避免使用NULL字段
很难进行查询优化
NULL列加索引，需要额外空间
含NULL符合索引无效

少用并拆分TEXT/BLOB
text类型处理性能原低于varchar
   强制生成硬盘临时表
   浪费更多空间
若必须使用则拆分到单独的表

谨慎合理添加索引
改善查询、减慢更新，索引不是越多越好
能不加的索引尽量不加，，最好不超过字段数的20%
重复率高的字段不要加索引
举例：不要给“性别”列创建索引

不在索引列进行数学运算或函数运算
无法使用索引
导致权标扫描

自增列或全局ID做INNODB主键
忌用字符串做主键
推荐用独立与业务的AUTO_INCREMENT列或全局ID生成器做主键代理

尽量不用外键，由程序保证约束

拒绝大SQL，拆解成多条简单SQL
一条SQL只能在一个CPU运算
可能一条大SQL就能把数据库堵死
简单SQL缓存命中率更高
用上多CPU
减少锁表时间，特别是MyISAM

尽可能少用存储过程
尽可能少用触发器
减少使用MySQL函数对结果进行处理，由客户端程序负责

尽量不用SELECT *，只取需要的数据列
为使用覆盖索引提高可能性

同一字段，将OR改写为IN()
OR效率：O(n)
IN效率：O(Log n)
当n很大时，OR会慢很多
注意控制IN的个数，建议n小于200。

将OR改写为UNION
减少对不同字段进行"OR"查询
合并索引往往很“弱智”
如果有足够信心：set global optimizer_switch='index_merge=off';
举例：SELECT * FROM user WHERE phone='010-88886666' OR mobile='13800138000';
改写为：
SELECT * FROM user WHERE phone='010-88886666'
UNION
SELECT * FROM user WHERE mobile='13800138000';

避免负向查询：NOT、!=、<>、NOT EXISTS、NOT IN、NOT LIKE等
避免%前缀模糊查询：使用不了索引、导致全表扫描
举例：SELECT * FROM POST WHERE city LIKE '北京%';         YES
          SELECT * FROM POST WHERE city LIKE '%北京%';      NO！！！

尽量少用COUNT(*)，COUNT(*)的资源开销快，用COUNT(主键）。

用UNION ALL 而非 UNION
若无需对结果进行去重，则用UNION ALL，UNION有去重开销

高并发DB不建议进行两个表以上的JOIN

GROUP BY 去除排序
GROUP的功能：分组与自动排序
无需排序：ORDER BY NULL
特定排序：GROUP BY xxx DESC/ASC