Mysql性能优化

从何入手

性能优化可以从以下几方面入手，性能优化收益占比由高到低：

• 业务需求和架构优化：55%  （商业需求合理变通，系统架构最优化）
• Query语句的优化：30%  （逻辑实现精简规范化）
• 数据库自身的优化：15%  （硬件设施、服务器配置合理最优化）

优先考虑

• 业务设计层面：我们在商业需求设计时应该避免那些画蛇添足的不必要的需求设计，避免系统资源浪费，另外必要的时候，可以通过降低某些统计数据的精确度，或者利用异步请求的方式，来保证页面的响应速度和良好的用户体验。
• 架构设计层面：在系统架构体系方面做文章，来实现系统性能的可扩展性，常见的设计模式有：读写分离、水平切分（分表）、垂直切分（分库）。

索引的使用

• 索引的个数限制：单表索引个数尽量控制在5个以内，复合索引的字段个数不要超过5个。
• 适宜索引的情况：索引通常建立在where、order by、group by、distinct从句和join关联列的字段上。
• 不宜索引的情况：数据类别少、频繁更新、text、bit类型、UUID\MD5\HASH字符串的字段，不适合建立索引。
• 最左原则：对于联合索引，字段长度小、使用频繁、区分读（列中不同值的数量/列的总行数）高的列，适宜在最左侧。
• 如何避免索引失效：

1. 字段值中的NULL值会导致索引失效。
2. 模糊匹配百分号前置，会导致索引失效，譬如LIKE “％aa”。
3. 负向查询会导致索引失效，譬如<>、!= 、not in 、not exist。
4. 内置函数或表达式会导致索引无效，譬如where score-10 > 60、where abs(x) > 10。
5. 强制转换数据类型会导致索引无效，譬如where userid=34987576。
6. 复合索引中的第一个字段没有出现在条件语句中，会导致索引无效。
7. JOIN的字段的数据类型不一致，会导致索引无效。

SQL调优

• 控制检索量

1. 避免使用select *，select count(*)。
2. 避免limit 偏移量（offset）过大，减少检索量。

• 如何连接效率更高

1. 关联的表个数不宜多，过多可能会出现内存溢出，影响数据库性能
2. 用INNER JOIN替换LEFT JOIN（INNER JOIN比LEFT JOIN效率高）
3. 用UNION ALL替换UNION（UNION 会去重和排序，效率低）

• 避免临时表

1. 避免子查询：通常子查询结果集会被存储在临时表里中而无法使用索引，可以用JOIN替换子查询。
2. 使用索引，如果不能使用索引，数据库会创建临时表用于排序。
3. 垂直切分，将长字段，如TEXT、BLOG，拆分到单独的表存储。
4. 避免去重，能不去重就别用DISTINCT；能用UNION ALL，就别用UNION。

• 其它方面

1. 不用OR：or大多数情况下很少能利用上索引，in能更有效地利用索引。
2. IN个数：IN包含的值不宜过多，建议少于500个。
3. 预编译：预编译可提高性能，而且可以防止SQL注入。
4. 冗余：冗余数据可以让查询执行更快，避免表关联。
5. 缓存表：利用缓存表存储查询结果，提升查询效率。
6. 拆大变小：将复杂的sql拆分成小的sql并行执行，效率更高。

Schema设计

• 存储引擎，没有特殊情况，尽量选择InnoDB引擎，支持事务，行锁。
• 单表大小，控制单表数据量在500万以内，另外不要在库中存储图片。
• 冷热分离，将不常使用的数据从主表中分离出去，保证主表的访问速度。
• 字段冗余，适当冗余字段，减少表连接。
• 数据类型的选择：

1. 尽量选择短小的数据类型，避免使用TEXT、BLOG类型，可分离到扩展表中去。
2. 尽量避免ENUM枚举类型，排序执行效率低，禁止使用数值作为枚举值。
3. 尽量用DATATIME和TIMESTAMP存储日期，不要用字符串型来存储日期，会占用更多的空间。
4. 存储金额请使用Decimal精准浮点型，不会损失精度，而float、double是非精准浮点型。