MySQL性能优化技巧分享

时间：2021-05-23

MySQL性能优化

在互联网公司MySQL的使用非常广泛，大家经常会有MySQL性能优化方面的需求。整理了一些在MySQL优化方面的实用技巧。

Schema与数据类型优化

  • 整数通常是标识列最好的选择，因为它们很快并且可以使用AUTO_INCREMENT
  • 完全“随机”的字符串（如：MD5()、SHA1()或者UUID()等产生的字符串）会任意分布在很大的空间内，会导致INSERT以及一些SELECT语句变的很慢
  • 如果希望查询执行得快速且并发性好，单个查询最好不要做太多的关联查询（互联网公司非常忌讳关联查询），利用程序来完成关联操作
  • 如果需要对一张比较大的表做表结构变更（ALTER TABLE操作增加一列），建议先拷贝一张与原表结构一样的表，再将数据复制进去，最后通过重命名将新表的表名称修改为原表的表名称。因为在变更表结构的时候很有可能会锁住整个表，并且可能会有长时间的不可用
  • 避免多表关联的时候可以适当考虑一些反范式的建表方案，增加一些冗余字段

    • InnoDB索引优化

  • 如果不是按照索引的最左列开始查找，则无法使用索引
  • 所有的非聚簇索引都需要先通过索引定位到对应的主键，然后在到聚簇索引查找数据，所以在定义主键索引的时候一定要谨慎
  • 只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或者正序）都一样时，MySQL才能够使用索引来对结果做排序。有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。
  • 在使用like来匹配字符串类型的字段的值时，尽可能的使用前缀匹配like ‘XX%'，避免使用 like ‘%XX'
  • 哈希索引是基于哈希表实现的，只有精确匹配索引所有列的查询才有效，也不遵循索引的最左匹配原则
  • 当服务器需要对多个索引做联合操作时（通常有多个OR条件），建议修改成UNION的方式，这样方便命中索引
  • 对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列
  • 尽可能多的使用覆盖索引（如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为覆盖索引），通过EXPLAIN的Extra列可以看到“Using index”信息
  • 当ID为主键时，创建索引(A)，相当于创建了(A)和(A, ID)两个索引
  • 表中的索引越多对SELECT、UPDATE和DELETE操作速度变慢，同时占用的内存也会比较多
  • InnoDB在二级索引上使用共享锁，但是访问主键索引需要排他锁
  • 尽可能的使用WHERE IN和WHERE BETWEEN AND的方式来进行范围查询
  • LIMIT的偏移量越大性能越慢
  • 编写查询语句时应该避免单行查找、尽可能的使用数据原生顺序从而避免额外的排序操作，并尽可能使用索引覆盖查询

    • 查询性能优化

    1.对于低效的查询，通常从两个方面来分析：

  • 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候可能是访问了太多的列
  • 确认MySQL服务器层是否在分析大量超过需要的数据行

    • 2.一般MySQL能够使用以下三种方式应用WHERE条件，从好到坏依次为：

    • 在索引中使用WHERE条件俩过滤不匹配的记录
    • 使用索引覆盖扫描来返回记录
    • 从数据表中返回数据，然后过滤不满足条件的记录

    3.MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。在一个通用服务器上，也能够运行每秒超过10万的查询，一个千兆网卡也能轻松满足每秒超过2000次的查询，MySQL内部每秒能够扫描内存中上百万行数据

    4.在删除大量数据时，建议每次删除一小批量数据后，暂停一会儿再做下一次的删除

    5.无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序

    6.COUNT()函数有两种不同的作用：它可以统计某个列值的数量，也可以统计行数。最简单的就是通过COUNT(*)来统计行数

    7.关联查询的时候要确保关联的字段上有索引

    8.在数据量很大并且历史数据需要定期删除的情况下，可以考虑使用分区表

    9.如果定了的索引列和分区列不匹配，会导致查询无法进行分区过滤

    10.外键约束尽可能避免，通常通过程序来实现，心中要有外键

    11.触发器、存储过程、自定义函数等最好不要使用

    12.尽可能的利用查询缓存，如果在写查询语句的时候有一些不确定的数据（NOW()或者CURRENT_DATE()等）时，则不会被缓存

    13.用多个小表代替一个大表对查询缓存有好处

    14.批量写入时只需要做一次缓存失效，所以相比单条写入（每写入一次，缓存就失效）效率更好，对于写密集型的应用，直接禁用查询缓存

    15.如果缓存的空间太大，在过期操作的时候可能会导致服务器僵死

    以上是个人在工作中的经验总结，如果有描述错误的地方希望大家可以帮忙指出，一起交流学习！

    到此这篇关于MySQL性能优化技巧分享的文章就介绍到这了,更多相关MySQL性能优化内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

    声明：本页内容来源网络，仅供用户参考；我单位不保证亦不表示资料全面及准确无误，也不保证亦不表示这些资料为最新信息，如因任何原因，本网内容或者用户因倚赖本网内容造成任何损失或损害，我单位将不会负任何法律责任。如涉及版权问题，请提交至online#300.cn邮箱联系删除。

    相关文章