sql优化个人总结

墨蓝 2022-03-01 12:26 278阅读 0赞

[https://blog.csdn.net/persistencegoing/article/details/84376427][https_blog.csdn.net_persistencegoing_article_details_84376427]

\--导致查询缓慢的原因  
1、数据量过大

2、表设计不合理

3、sql语句写得不好

4、没有合理使用索引

\-- 针对SQL语句的优化

1、查询语句中不要使用 \*

2、尽量减少子查询，使用关联查询（left join,right join,inner  join）替代

3、减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代

4、or 的查询尽量用 union或者union all 代替  
(在确认没有重复数据或者不用剔除重复数据时，union all会更好)

5、合理的增加冗余的字段（减少表的联接查询）

6、增加中间表进行优化（这个主要是在统计报表的场景，  
后台开定时任务将数据先统计好，尽量不要在查询的时候去统计）

7、建表的时候能使用数字类型的字段就使用数字类型（type,status...），数字类型的字段作为条件查询比字符串的快

8、那些可以过滤掉最大数量记录的条件必须写在WHERE子句的最末尾

\-- 索引优化

如果针对sql语句已经没啥可以优化的，那我们就要考虑加索引了。

\--说索引前先说说explain查看sql的执行计划

1 id  
 SELECT识别符。这是select查询序列号。这个不重要

2 select\_type   
表示查询中每个select子句的类型（简单OR复杂）  
有以下几种值：  
 1 simple   
 查询中不包含查询或者UNION（联合查询）  
 2 PRIMARY  
 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY  
 3 UNION  
 表示连接查询的第2个或后面的查询语句。  
 4 DEPENDENT UNION  
 UNION 中的第二个或者后面的select语句，取决于外面的查询  
 5 UNION RESULT  
 连接查询的结果  
 6 SUBQUERY  
 子查询中的第一个select语句  
 7 DEPENDENT SUBQUERY  
 子查询中的第一个select语句，取决于外面的查询  
 8 DERIVED  
 select（from子句的子查询）

3 table 表示查询的表

4 type   
表示表的连接类型  
以下的连接类型的顺序是从最佳类型到最差类型  
 1 syste  
 表仅有一行，这是const类型的特例，平时不会出现  
 2 const  
 数据表最多只有一个匹配行，因为只匹配一行数据，所以很快，常用于PRIMARY KEY  
 或者UNIQUE查询，可理解为是最优化的。  
 3 eq\_ref  
 mysql手册是这样说的： 对于每个来自前面的表的行组合，从该表中读取一行。  
 这可能是最好的联接类型，除了const类型。他用在一个索引的所有部分被联接使用并且并且索引是UNIQUE或PRIMARY KEY    eq\_ref可以用于使用=比较带索引的列。

4 ref   
 查询条件索引既不是UNIQUE 也不是PRIMARY KEY 的情况，ref可用于=或<或>操作符的带索引的列。

5 ref\_or\_null   
 该联接类型如同ref，但是添加了Mysql可以专门搜索包含null值的行，在解决子查询中经常使用该联接类型的优化。

以上这五种情况都是很理想的索引使用情况。

6 index  
 该连接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。

7 ALL  
 对于每个来自先前的表的行组合，进行完整的表扫描。

5 possible\_key  
 指出Mysql能使用哪个索引在该表中找到行。  
 如果该列为NULL 说明没有使用索引，可以对该列创建索引来提高性能  
6 Key  
 显示mysql实际决定使用的索引，如果没有选择索引，键是null  
 可以强制使用索引或者忽略索引：  
 强制使用索引：USE index（列名）  
 忽略使用索引：IGNORE INDEX(列名)  
7 key\_len  
 显示mysql决定使用的键长度。如果键是NULL则长度为NULL。  
 注意：key\_len 是确定了mysql将实际使用的索引长度

8 ref   
 显示使用哪个列或常数与key一起从表中选择行  
9 rows  
 显示mysql认为它执行查询时必须检查的行数  
10 extra  
 关于MYSQL如何解析查询的额外信息。Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢  
 说明：extra列返回的描述的意义  
 Distinct ：一旦mysql找到了与行相联合匹配的行，就不再搜索了。  
 Not exists ：mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。  
 Range checked for each Record（index map:\#） ：没有找到理想的索引，因此对从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。  
 Using filesort ：看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。  
 Using index ：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。  
 Using temporary ：看到这个的时候，查询需要优化了。这里，mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。  
 Where used ：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题。

使用explain查看sql执行计划后，我们主要先看下type属性，表示连接的类型，如果是ALL这种那就需要优化了，

再看下possible\_key属性，表示可以使用的索引，如果没有则为null，key属性表示mysql实际决定使用的索引，如果没有选择索引，键是null，

rows 表示mysql认为它执行查询时必须检查的行数，行数越多效率越低。

\--索引类型

主键索引，唯一索引，组合索引，普通索引

\--什么是索引

数据库索引是数据库管理系统中的一个排序的数据结构，以协助快速查询，更新数据库表中数据，索引的实现通常使用B树(B-tree)以及其变种B+tree（一些高效率的算法）

\--使用索引时有些不生效的情况

1、使用like关键字模糊查询时，% 放在前面索引不起作用，只有“%”不在第一个位置，索引才会生效（like '%文'--索引不起作用）

2、使用联合索引时，只有查询条件中使用了这些字段中的第一个字段，索引才会生效

3、使用OR关键字的查询，查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则索引不生效。  
 4、尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

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