MySQL的常用优化方案

概述

我们知道， MySQL DB 的数据是存放在磁盘上的，其读写速度无法和存放在内存中的 redis 相比，当 MySQL 记录数过大时，增删改查时系统的吞吐量会非常的大，数据增删改查的性能会急剧下降。为了改善以上情况，我们需要对 MySQL 进行相关优化，可以参考以下步骤来优化：

表结构的优化

字段设计

• 尽量使用 TINYINT  、  SMALLINT  、  MEDIUM_INT  作为整数类型而非  INT  ，如果非负则加上 UNSIGNED
• VARCHAR  的长度只分配真正需要的空间
• 使用枚举 enum 代替字符串类型
• 尽量使用 TIMESTAMP  而非  DATETIME  ，
• 单表不要有太多字段，建议在20以内
• 避免使用 NULL 字段，很难查询优化且占用额外索引空间
• 用整型来存 IP（ INET_ATON(str)，INET_NTOA(number) )
• 适当的字段荣誉，避免过多的 join 查询

索引

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

SQL 语句的优化

索引失效的情况

• 当我们使用左或者左右模糊匹配的时候，也就是  like %xx  或者  like %xx% 这两种方式都会造成索引失效；
• 当我们在查询条件中对索引列使用函数，就会导致索引失效。
• 当我们在查询条件中对索引列进行表达式计算，也是无法走索引的。
• MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。
• 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。
• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

从以上索引失效的场景分析，我们可对相关查询的 SQL 语句进行以下优化：

查询SQL

• 可通过开启慢查询日志来找出较慢的SQL
• 不做列运算： SELECT id WHERE age + 1 = 10  ，任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
• sql语句尽可能简单：一条sql只能在一个cpu运算；大语句拆小语句，减少锁时间；一条大sql可以堵死整个库
• 不用 SELECT *
• OR  改写成  IN  ： OR  的效率是n级别，  IN  的效率是log(n)级别，in的个数建议控制在200以内
• 不用函数和触发器，在应用程序实现
• 避免 %xxx  式查询
• 少用 JOIN
• 使用同类型进行比较，比如用 '123'  和  '12