mysql优化秘籍

https://www.cnblogs.com/ace-ty/p/11458491.html

https://cloud.tencent.com/developer/article/1421876

https://cloud.tencent.com/developer/article/1424272

https://cloud.tencent.com/developer/article/1484421

https://cloud.tencent.com/developer/article/1481467

https://mp.weixin.qq.com/s/ZBgv-s-_ojWMiTZYTVx-iQ

https://mp.weixin.qq.com/s/Gid96Ivb0I3yGAiieyKRdg

https://segmentfault.com/a/1190000020844635?utm_source=tag-newest

https://www.cnblogs.com/yycc/p/7518240.html  千万级数据表优化

 https://mp.weixin.qq.com/s/Y8QSV3sgJX9rnrrbw0LnJQ

2.2 由 SQL 编写导致的慢 SQL 优化

针对SQL编写导致的慢 SQL，优化起来还是相对比较方便的。正如上一节提到的正确的使用索引能加快查询速度，那么我们在编写 SQL 时就需要注意与索引相关的规则：

• 字段类型转换导致不用索引，如字符串类型的不用引号，数字类型的用引号等，这有可能会用不到索引导致全表扫描；
• mysql 不支持函数转换，所以字段前面不能加函数，否则这将用不到索引；
• 不要在字段前面加减运算；
• 字符串比较长的可以考虑索引一部份减少索引文件大小，提高写入效率；
• like % 在前面用不到索引；
• 根据联合索引的第二个及以后的字段单独查询用不到索引；
• 不要使用 select *；
• 排序请尽量使用升序 ;
• or 的查询尽量用 union 代替 （Innodb）；
• 复合索引高选择性的字段排在前面；
• order by / group by 字段包括在索引当中减少排序，效率会更高。

索引规范：

• 索引建议命名规则：idx_col1_col2[_colN]、uniq_col1_col2[_colN]（如果字段过长建议采用缩写）。

• 索引中的字段数建议不超过 5 个。

• 单张表的索引个数控制在 5 个以内。

• InnoDB 表一般都建议有主键列，尤其在高可用集群方案中是作为必须项的。

• 建立复合索引时，优先将选择性高的字段放在前面。

• UPDATE、DELETE 语句需要根据 WHERE 条件添加索引。

• 不建议使用 % 前缀模糊查询，例如 LIKE “%weibo”，无法用到索引，会导致全表扫描。

• 合理利用覆盖索引，例如：SELECT email，uid FROM user_email WHERE uid=xx，如果 uid 不是主键，可以创建覆盖索引 idx_uid_email（uid，email）来提高查询效率。

• 避免在索引字段上使用函数，否则会导致查询时索引失效。

• 确认索引是否需要变更时要联系 DBA。

应用规范：

• 避免使用存储过程、触发器、自定义函数等，容易将业务逻辑和DB耦合在一起，后期做分布式方案时会成为瓶颈。

• 考虑使用 UNION ALL，减少使用 UNION，因为 UNION ALL 不去重，而少了排序操作，速度相对比 UNION 要快，如果没有去重的需求，优先使用 UNION ALL。

• 考虑使用 limit N，少用 limit M，N，特别是大表或 M 比较大的时候。

• 减少或避免排序，如：group by 语句中如果不需要排序，可以增加 order by null。

• 统计表中记录数时使用 COUNT(*)，而不是 COUNT(primary_key) 和 COUNT(1)。

  InnoDB 表避免使用 COUNT(*) 操作，计数统计实时要求较强可以使用 Memcache 或者 Redis，非实时统计可以使用单独统计表，定时更新。

• 做字段变更操作（modify column/change column）的时候必须加上原有的注释属性，否则修改后，注释会丢失。

• 使用 prepared statement 可以提高性能并且避免 SQL 注入。

• SQL 语句中 IN 包含的值不应过多。

• UPDATE、DELETE 语句一定要有明确的 WHERE 条件。

• WHERE 条件中的字段值需要符合该字段的数据类型，避免 MySQL 进行隐式类型转化。

• SELECT、INSERT 语句必须显式的指明字段名称，禁止使用 SELECT * 或是 INSERT INTO table_name values()。

• INSERT 语句使用 batch 提交（INSERT INTO table_name VALUES(),(),()……），values 的个数不应过多。

三、分析诊断工具

在日常开发工作中，我们可以做一些工作达到预防慢 SQL 问题，比如在上线前预先用诊断工具对 SQL 进行分析。常用的工具有：

• mysqldumpslow
• mysql profile
• mysql explain