mysql 高并发 亿级数据量 sql优化 索引优化 慢sql优化

一、索引优化

SQL性能优化 神器 mysql explain

搞懂MySQL索引优化 explain

MySQL索引背后的数据结构及算法原理

神奇的 SQL 之别样的写法 → 行行比较

那些年我们一起优化的SQL

慢sql治理经典案例分享

5大步骤+10个案例，SQL优化

基于业务场景的MySQL千万级大表优化

二、sql优化1

MySQL 大表优化方案

聊聊数据库优化

优化 MySQL： 3 个简单的小调整

MySQL 性能优化：8 种常见 SQL 错误用法！

SQL优化

52条SQL语句性能优化策略

1. mysql嵌套子查询效率比较低 可以将其优化成连接查询

2. 连接表时，可以先用where条件对表进行过滤，然后做表连接（虽然mysql会对连表语句做优化）

3. 建立合适的索引，必要时建立多列联合索引

4. 学会分析sql执行计划，mysql会对sql进行优化，所以分析执行计划很重要

5. 列类型尽量定义成数值类型，且长度尽可能短，如主键和外键，类型字段等等

6. 建立单列索引

7. 根据需要建立多列联合索引

   • 当单个列过滤之后还有很多数据，那么索引的效率将会比较低，即列的区分度较低
   • 如果在多个列上建立索引，那么多个列的区分度就大多了，将会有显著的效率提高。

8. 根据业务场景建立覆盖索引只查询业务需要的字段，如果这些字段被索引覆盖，将极大的提高查询效率

9. 多表连接的字段上需要建立索引，这样可以极大提高表连接的效率

10. where条件字段上需要建立索引

11. 排序字段上需要建立索引

12. 分组字段上需要建立索引

13. Where条件上不要使用运算函数，以免索引失效

14. 比较运算符能用 “=”就不用“<>”
    “=”增加了索引的使用几率。

15. 明知只有一条查询结果，那请使用 “LIMIT 1”
    “LIMIT 1”可以避免全表扫描，找到对应结果就不会再继续扫描了。

16. 为列选择合适的数据类型
    能用TINYINT就不用SMALLINT，能用SMALLINT就不用INT，道理你懂的，磁盘和内存消耗越小越好嘛。

17. 将大的DELETE，UPDATE or INSERT 查询变成多个小查询
    能写一个几十行、几百行的SQL语句是不是显得逼格很高？然而，为了达到更好的性能以及更好的数据控制，你可以将他们变成多个小查询。

18. 使用UNION ALL 代替 UNION，如果结果集允许重复的话
    因为 UNION ALL 不去重，效率高于 UNION。

19. 为获得相同结果集的多次执行，请保持SQL语句前后一致
    这样做的目的是为了充分利用查询缓冲。
    比如根据地域和产品id查询产品价格，第一次使用了：
    那么第二次同样的查询，请保持以上语句的一致性，比如不要将where语句里面的id和region位置调换顺序。

20. 尽量避免使用 “SELECT *”
    如果不查询表中所有的列，尽量避免使用 SELECT *，因为它会进行全表扫描，不能有效利用索引，增大了数据库服务器的负担，以及它与应用程序客户端之间的网络IO开销。

21. WHERE 子句里面的列尽量被索引
    只是“尽量”哦，并不是说所有的列。因地制宜，根据实际情况进行调整，因为有时索引太多也会降低性能。

22. JOIN 子句里面的列尽量被索引
    同样只是“尽量”哦，并不是说所有的列。

23. ORDER BY 的列尽量被索引
    ORDER BY的列如果被索引，性能也会更好。

24. 使用 LIMIT 实现分页逻辑
    不仅提高了性能，同时减少了不必要的数据库和应用间的网络传输。

25. 使用 EXPLAIN 关键字去查看执行计划
    EXPLAIN 可以检查索引使用情况以及扫描的行。

26. 负向条件查询不能使用索引。
    select * from order where status!=0 and stauts!=1
    not in/not exists都不是好习惯。
    可以优化为in查询：
    select * from order where status in(2,3)

27. 前导模糊查询不能使用索引。
    select * from order where desc like ‘%XX’
    而非前导模糊查询则可以：
    select * from order where desc like ‘XX%’

28. 数据区分度不大的字段不宜使用索引。
    select * from user where sex=1
    原因：性别只有男，女，每次过滤掉的数据很少，不宜使用索引。

29. 在属性上进行计算不能命中索引。
    select * from order where YEAR(date) < = ‘2020’
    即使date上建立了索引，也会全表扫描，可优化为值计算：
    select * from order where date < = CURDATE()
    或者：
    select * from order where date < = ‘2020-01-01’

30. 如果业务大部分是单条查询，使用Hash索引性能更好，例如用户中心。
    select * from user where uid=?
    select * from user where login_name=?
    原因：
    B-Tree索引的时间复杂度是O(log(n))；
    Hash索引的时间复杂度是O(1)。

31. 允许为null的列，查询有潜在大坑。
    单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集。
    select * from user where name != ‘shenjian’
    如果name允许为null，索引不存储null值，结果集中不会包含这些记录。
    所以，请使用not null约束以及默认值。

32. 复合索引最左前缀，并不是指SQL语句的where顺序要和复合索引一致。
    用户中心建立了(login_name, passwd)的复合索引
    select * from user where login_name=? and passwd=?
    select * from user where passwd=? and login_name=?
    都能够命中索引。
    select * from user where login_name=?
    也能命中索引，满足复合索引最左前缀。
    select * from user where passwd=?
    不能命中索引，不满足复合索引最左前缀。

33. 如果明确知道只有一条结果返回，limit 1能够提高效率。
    select * from user where login_name=?
    可以优化为：
    select * from user where login_name=? limit 1
    原因：
    你知道只有一条结果，但数据库并不知道，明确告诉它，让它主动停止游标移动。

34. 把计算放到业务层而不是数据库层，除了节省数据的CPU，还有意想不到的查询缓存优化效果。
    select * from order where date < = CURDATE()
    这不是一个好的SQL实践，应该优化为：
    $curDate = date(‘Y-m-d’);
    $res = mysql_query(
    ‘select * from order where date < = $curDate’
    );
    原因：释放了数据库的CPU。多次调用，传入的SQL相同，才可以利用查询缓存。

35. 强制类型转换会全表扫描
    select * from user where phone=13800001234

36. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

37. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0

38. 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

39. 应尽量避免在 where 子句中使用or 来连接条件
    注意一般认为，or两边的列如果都能走索引，那么这条sql语句是会利用到索引的。必须所有的or条件都必须是独立索引。
    否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10
    or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20

40. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100*2

41. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)=‘abc’ ，name以abc开头的id
    应改为:
    select id from t where name like ‘abc%’

42. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

43. 不要写一些没有意义的查询，如需要生成一个空表结构：select col1,col2 into #t from t where 1=0
    这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
    create table #t(…)

44. 很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)
    用下面的语句替换：
    select num from a where exists(select 1 from b where num=a.num)

45. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

46. 索引并不是越多越好，索引固然可 以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

47. 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

48. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

49. 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

50. 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

51. 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

52. 避免频繁创建和删除临时表，以减少系统表资源的消耗。

53. 临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

54. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

55. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

56. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

57. 尽量避免大事务操作，提高系统并发能力。

58. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

59. 任何对列的操作都将导致表扫描，它包括数据库教程函数、计算表达式等等，查询时要尽可能将操作移 至等号右边。

60. 只要能满足你的需求，应尽可能使用更小的数据类型：例如使用 MEDIUMINT 代替 INT

61. 尽量少用 VARCHAR、TEXT、BLOB 类型

62. 合理用运分表与分区表提高数据存放和提取速度。

63. 查询条件放到子查询中，子查询只查主键ID，然后使用子查询中确定的主键关联查询其他的属性字段；原理：减少回表操作；
    – 优化前SQL
    SELECT 各种字段
    FROM table_name
    WHERE 各种条件
    LIMIT 0,10;
    – 优化后SQL
    SELECT 各种字段
    FROM table_name main_tale
    RIGHT JOIN
    (
    SELECT 子查询只查主键
    FROM table_name
    WHERE 各种条件
    LIMIT 0,10;
    ) temp_table ON temp_table.主键 = main_table.主键

64. 查是否存在某条数据 不用count
    SELECT 1 FROM table WHERE a = 1 AND b = 2 LIMIT 1

65. 不要有超过5个以上的表连接
    连表越多，编译的时间和开销也就越大。
    把连接表拆开成较小的几个执行，可读性更高。
    如果一定需要连接很多表才能得到数据，那么意味着糟糕的设计了。

三、sql优化2

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10

union all

select id from t where num=20

5.in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

6.下面的查询也将导致全表扫描：

select id from t where name like ‘%abc%’

若要提高效率，可以考虑全文检索。

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)=‘abc’–name以abc开头的id

select id from t where datediff(day,createdate,‘2005-11-30’)=0–‘2005-11-30’生成的id

应改为:

select id from t where name like ‘abc%’

select id from t where createdate>=‘2005-11-30’ and createdate<‘2005-12-1’

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(…)

13.很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21.避免频繁创建和删除临时表，以减少系统表资源的消耗。

22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29.尽量避免大事务操作，提高系统并发能力。

30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

31.在WHERE子句里不可SARG的搜索条件如"IS NULL", “<>”, “!=”, “!>”, “!<”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE"和"LIKE ‘%500’”，通常（但不总是）会阻止查询优化器使用索引执行搜索。另外在列上使用包括函数的表达式、两边都使用相同列的表达式、或和一个列（不是常量）比较的表达式，都是不可SARG的。ARGE来源于"Search Argument"（搜索参数）的首字母拼成的"SARG"，它是指WHERE子句里，列和常量的比较。如果WHERE子句是sargable（可SARG的），这意味着它能利用索引加速查询的完成。如果WHERE子句不是可SARG的，这意味着WHERE子句不能利用索引（或至少部分不能利用），执行的是全表或索引扫描，这会引起查询的性能下降。