领先的免费Web技术教程，涵盖HTML到ASP.NET

网站首页 > 知识剖析 正文

MySQL之SQL优化

nixiaole 2024-11-22 18:45:27 知识剖析 13 ℃

我们日常开发中常常会遇到数据库查询比较慢，大量的慢查询日志堆积使开发人员痛不欲生，上有DBA劈头盖脸的吐槽你SQL写的垃圾导致数据库负载居高不下，下有产品经理鄙视你代码垃圾系统响应太慢，只有你夹在中间欲哭无泪，而面试中也常常会问到你对于SQL性能优化的知识，下面我们就来说一下开发中必须要掌握的一些SQL优化技能。

如果你对数据库的数据库引擎、数据库索引原理不是很了解那你可以查看我之前的两篇文章：

MySQL必备知识——存储引擎、彻底搞明白MySQL的索引原理


首先把基础原理搞懂才能谈优化，而生产中我们怎么确定哪些是慢查询SQL的呢？那就要用到我们的慢查询日志了，配置方法如下：

修改配置文件my.cnf，在[mysqld]下的下方加入

[mysqld]

slow_query_log = ON

log_queries_not_using_indexes = ON;

slow_query_log_file = /usr/local/mysql/data/slow.log

long_query_time = 1 #时间自己根据业务情况定义

这样我们就能获取到我们系统中的慢查询SQL了，获取到这些慢查询SQL之后呢，我们可以使用Explain 这个命令对我们的语句进行分析，如果不懂这个命令的使用，可以查看我的另一篇文章：MySQL——SQL性能分析优化利器之Explain

安利了半天，下面我们就进入正题，下面是我们平时开发中在写SQL时一定要牢牢掌握的一些建议，只只有将这些建议牢记于心才能真正写出高性能的SQL。

  1. 首先就是要对查询进行优化，任何查询都应该避免全表扫描，只要出现查询首先要想到的就是对where及order by中的字段增加合适的索引。
  2. 在 where 条件中使用合适的操作符避免索引失效，MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN...AND，IN；像!=或<>这类操作符会导致索引失效，所以尽量避免使用。
  3. 在 where 条件子句中对字段进行 null 值判断将导致引擎放弃使用索引而进行全表扫描，所以索引列尽量设置为非null字段并设置默认值。
  4. 在where条件子句中应该尽量避免使用 or 来连接查询条件，否则将导致引擎放弃索引进而进行全表扫描，可以使用union进行关联获取结果。
  5. 在where条件中使用like时一定要注意 %key% 会导致索引失效 进而进行权标扫描。
  6. 对于 in 和 not in 也要慎用，有时会导致全表扫描，对于连续的数值，能用 between 可能会好于用in。
  7. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。例如：select id from student where num/2=100
  8. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，这将导致引擎放弃使用索引而进行全表扫描 。例如：select id from student where substring(name,1,4)='wang'
  9. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。（最左原则）
  10. 在很多时候用 exists 代替 in 是一个不错的选择，相对的not exists 也可以代替 not in。
  11. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，所以尽量选择区别度比较高的字段建立索引，像status、sex这种区别度不是很高的字段就不太适合建立索引了。
  12. 物极必反在建立索引这照样适用，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，索引索引并不是越多越好，如何创建索引要视具体情况而定。一个表的索引数最好不要超过6个。
  13. 尽可能的避免更新聚簇索引数据列（如果可以一定不要），因为 聚簇索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新聚簇索引数据列，那么需要考虑是否应将该索引建为 聚簇索引。
  14. 尽量使用数值型字段建立索引（索引的实现原理），若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
  15. 选择存储空间较小的字段建立索引，因为在较小的字段内搜索效率更高。
  16. select * from 尽量不用，值返回所需字段或者是索引覆盖字段。
  17. 尽量避免向客户端返回大量数据信息，因为大量的数据信息会增加查询的消耗成本，若数据量过大，应该考虑相应需求是否合理。
  18. 当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个查询的列上。这样一来，就可以减少解析的时间并减少那些由查询列歧义引起的语法错误。
  19. 避免频繁的创建和删除临时表，以减少系统表资源的消耗。
  20. 使用“临时表”暂存中间结果，简化SQL语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在tempdb中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。
  21. 常见的简化规则如下：不要有超过5个以上的表连接（JOIN），考虑使用临时表或表变量存放中间结果。少用子查询，视图嵌套不要过深，一般视图嵌套不要超过2个为宜。
  22. 当需要写入或者更新一批数据时，可以采用批量插入或者批量更新，切勿一条一条的进行插入或更新。
  23. 提升group by的效率，可以先将过滤条件提前，将不需要的数据先过滤再进行分组。
  24. 最好不要使用触发器，触发一个触发器，执行一个触发器事件本身就是一个耗费资源的过程;如果能够使用约束实现的，尽量不要使用触发器。
  25. 为每一张表设置一个自增的主键。（可参考索引原理）
  26. 当你只需要检索出一条数据时，请加上limit 1 。
  27. 数据库设计阶段，尽可能选择合适的字段类型来作为字段类型，原则：更小通常更好，简单就好，所有字段都得有默认值，尽量避免null。

索引失效的几种情况

  1. 最左原则，最佳左前缀法则：查询从索引的最左前列开始并且不能跳过中间的列，如果索引为多字段联合索引，使用索引时应遵从最左原则来进行使用。
  2. 不要在索引列上做任何操作（计算，函数，（自动或手动）类型转换），会导致索引失效而导致全表扫描。
  3. 存储引擎不能使用索引中范围条件右边的列，范围之后全失效。例如：student表中有一个联合索引 idx_name_age_class('name','age','class')，如果执行SQL:select id from student where name='wang' and age>20 and class=10; 虽然会使用联合索引idx_name_age_class 但是只会使用'name'和'age'两个字段。
  4. 如果可以尽量使用覆盖索引（查询列与索引列相同），虽然查询其他列不会造成索引失效，但是如果使用覆盖索引，将大大提高查询性能，如果使用的索引为非聚簇索引还会减少回表的消耗。
  5. mysql在使用不等于（!=或<>）的时候无法使用索引会导致全表扫描。
  6. is null和is not null也会导致索引失效，所以创建字段时尽量将所有字段都设置为非空并且设置默认值。
  7. like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描。
  8. 字符串查询如果不加引号引起，将会导致该字段索引失效，因为引擎会进行类型转换从而导致索引失效。
  9. 尽量不用or作为条件连接，用它来连接时会导致索引失效。
  10. where 子句中的“=”左边进行函数、算术运算或其他表达式运算，这将导致引擎放弃使用索引而进行全表扫描 。

流传已久的优化口诀

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

Like百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用；

VAR引号不可丢，SQL高级也不难！

Tags：

猜你喜欢

最近发表
标签列表