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.如果缓存的空间太大,在过期操作的时候可能会导致服务器僵死
以上是个人在工作中的经验总结,如果有描述错误的地方希望大家可以帮忙指出,一起交流学习!
免责声明:本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除!
更新日志
- 小骆驼-《草原狼2(蓝光CD)》[原抓WAV+CUE]
- 群星《欢迎来到我身边 电影原声专辑》[320K/MP3][105.02MB]
- 群星《欢迎来到我身边 电影原声专辑》[FLAC/分轨][480.9MB]
- 雷婷《梦里蓝天HQⅡ》 2023头版限量编号低速原抓[WAV+CUE][463M]
- 群星《2024好听新歌42》AI调整音效【WAV分轨】
- 王思雨-《思念陪着鸿雁飞》WAV
- 王思雨《喜马拉雅HQ》头版限量编号[WAV+CUE]
- 李健《无时无刻》[WAV+CUE][590M]
- 陈奕迅《酝酿》[WAV分轨][502M]
- 卓依婷《化蝶》2CD[WAV+CUE][1.1G]
- 群星《吉他王(黑胶CD)》[WAV+CUE]
- 齐秦《穿乐(穿越)》[WAV+CUE]
- 发烧珍品《数位CD音响测试-动向效果(九)》【WAV+CUE】
- 邝美云《邝美云精装歌集》[DSF][1.6G]
- 吕方《爱一回伤一回》[WAV+CUE][454M]