MySQL优化

LZ最近重构的时候接触到了sql优化，总结了几点

一.单表优化

如果单表数据在未来一段时间内会不断上涨，那么就要考虑拆分了，拆分是下下策，因为会增加部署，逻辑，运维的难度，所以在设计阶段尽量规避。

二.字段

• 尽量使用TINYINT、SMALLINT、MEDIUM_INT 作为整数类型而非 INT，如果非负则加上 UNSIGNED。
• VARCHAR 的长度只分配真正需要的空间。可以估算大概长度，一个中文按4~5长度计算。
• 使用枚举或整数代替字符串类型。
• 尽量使用 TIMESTAMP 而非 DATETIME。
• 单表不要有太多字段，建议在 20 以内。
• 避免使用 NULL 字段，很难查询优化且占用额外索引空间。
• 用整型来存 IP。

索引

关于索引，可以单拿出来写一篇文章的，这里说几个重点

• 索引并不是越多越好，要根据查询有针对性的创建，考虑在 WHERE 和 ORDER BY 命令上涉及的列建立索引，可根据 EXPLAIN 来查看是否用了索引还是全表扫描。
• 应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
• 字符字段只建前缀索引
• 字符字段最好不要做主键。
• 不用外键，由程序保证约束。
• 尽量不用 UNIQUE，由程序保证约束。
• 使用多列索引时注意顺序和查询条件保持一致，同时删除不必要的单列索引。

查询语句

• 可通过开启慢查询日志来找出较慢的 SQL。
• 不做列运算，如：SELECT id WHERE xxx + 1 = 10，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
• SQL 语句尽可能简单：一条 SQL只能在一个 CPU 运算;大语句拆小语句，减少锁时间;一条大 SQL 可以堵死整个库。
• 不用SELECT *。
• OR 改写成 IN：OR 的效率是 n 级别，IN 的效率是 log(n) 级别，IN 的个数建议控制在 200 以内。如：a =1 or a=2 or a =3 尽量写成 a in(1,2,3)
• 不用函数和触发器，在应用程序实现。
• 避免 %xxx 式查询
• 少用 JOIN
• 尽量避免在 WHERE 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
• 对于连续数值，使用 BETWEEN 不用 IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5。
• 列表数据不要拿全表，要使用 LIMIT 来分页，每页数量也不要太大。