SQL语句优化的相关资料介绍-sql语句模型结构优化

SQL语句优化的一些必知指南

更新时间：2019年5月09日 09:09:16 作者：时光cs过去

本文主要向您介绍SQL语句优化的相关信息。 文章通过示例代码对其进行了非常详细的介绍。 对于大家学习或者使用SQL有一定的参考学习价值。 有需要的朋友可以一起来学习。

前言

如何加快查询速度，优化查询效率？ 主要原则是尽量避免全表扫描，并考虑在where和order by涉及到的列上创建索引。

建立索引并不是建立尽可能多的索引。 原理是：

第一：表的索引越多越好，没有具体数字。 根据以往的经验，一张表最多不能有超过6个索引，因为索引越多，对sum运算的性能影响就越大。 涉及索引的创建和重建。

第二：建立指标的方法是：

SQL语句优化指南

1. SQL语句模型结构优化指导

A。 ORDER BY + LIMIT组合的索引优化

如果 SQL 语句的形式为：[],[],...。 FROM [TABLE] ORDER BY [sort] LIMIT [],[LIMIT];

优化这条SQL语句比较简单，只需在[sort]字段创建索引即可。

b. WHERE + ORDER BY + LIMIT组合的索引优化

如果 SQL 语句的形式为：[],[],...。 FROM [TABLE] WHERE [] = [VALUE] ORDER BY [sort] LIMIT [],[LIMIT];

对于这条语句，如果仍然使用第一个例子中的索引创建方法，虽然可以使用索引，但是效率不高。 更有效的方法是创建联合索引（,sort）

C。 WHERE+ORDER BY 多个字段+LIMIT

如果 SQL 语句的形式为： * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;

对于此语句，您可以添加如下索引：(x, y, uid)。 但实际上效果更好的是(uid,x,y)。 这是由 MySQL 处理排序的方式造成的。

2.复合索引（(x,y,uid)索引形式的索引）

首先看这样一条语句： * from users where area ='' andage=22;

如果我们在area和age上分别创建索引，由于mysql查询一次只能使用一个索引，虽然这相对于没有索引的全表扫描来说效率提升了不少，但是如果我们在area和age上创建两列，复合索引将带来更高的效率。

使用索引字段作为条件时，如果索引是复合索引，则必须使用索引中的第一个字段作为条件，以保证系统使用该索引，否则该索引将不会使用，应该使用尽可能使字段顺序与索引顺序一致。

比如我们创建这样一个索引(area,age,)，实际上就相当于创建了(area,age,)、(area,age)、(area)三个索引，这就是所谓的最佳左前缀特征。

3. Like语句优化

　　　　SELECT id FROM A WHERE name like '%abc%'

由于abc前面使用了“%”，所以查询必须是全表查询。 除非必要，不要在关键字前添加 %。 优化如下

　　　　SELECT id FROM A WHERE name like 'abc%'

4.使用where子句！ = 或运算符优化

用在where子句中！ = 或运算符，会放弃索引，进行全表查询。

例如，SQL：id FROM A WHERE ID != 5 优化为：id FROM A WHERE ID>5 OR ID

5、where子句中使用IS NULL或IS NOT NULL的优化

如果where子句中使用IS NULL或IS NOT NULL，索引将被放弃，并执行全表查询。

例如SQL：id FROM A WHERE num IS NULL经过优化，对num设置默认值0，保证表中num不存在空值，则SQL为：id FROM A WHERE num=0

6.using或inwhere子句的优化

很多时候，使用 union all 或 nuin （必要时）来代替“or”会得到更好的结果。 如果在where子句中使用or，索引将被丢弃。

例如，SQL： id FROM A WHERE num =10 或 num = 20 优化为： id FROM A WHERE num = 10 union all id FROM A WHERE num=20

7、where子句中使用IN或NOT IN的优化

in和not in也应该谨慎使用，否则也会导致全表扫描。

选项 1：更换

例如，SQL：id FROM A WHERE num in (1,2,3) 优化为：id FROM A WHERE num 1 and 3

选项2：用exist替换in

例如，SQL： id FROM A WHERE num in ( num from b ) 优化为： num FROM A WHERE num ( 1 from B where B.num = A.num)

选项 3：左连接替换 in

例如，SQL： id FROM A WHERE num in( num from B) 优化为： id FROM A LEFT JOIN B ON A.num = B.num

8、where子句中字段表达式操作的优化

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

例如，SQL：id FROM A WHERE num/2 = 100 优化为：id FROM A WHERE num = 100*2

例如，SQL： id FROM A WHERE (name,1,3) = 'abc' 优化为： id FROM A WHERE LIKE 'abc%'

例如，SQL： id FROM A WHERE (day,,'2016-11-30')=0 优化为： id FROM A WHERE >='2016-11-30' and = (( MAX(id) FROM A )-( 来自 A 的 MIN(id))) * RAND() + ( 来自 A 的 MIN(id)) LIMIT 10

15.排序索引问题

Mysql查询只使用索引，所以如果where子句中已经使用了索引，order by中的列将不会使用索引。 因此，当数据库默认排序能够满足要求时，不要使用排序操作；

尽量不要对多列进行排序。 如果有必要，最好为这些列创建复合索引。

16.尝试用union all替换union

union 和 union all 的主要区别在于，前者需要合并两个（或多个）结果集，然后执行唯一的过滤操作，这会涉及排序，增加大量 CPU 操作，增加资源消耗和延迟。 因此，当我们可以确认不可能出现重复的结果集或者我们不关心重复的结果集时，请尝试使用 union all 而不是 union。

17.避免类型转换

这里所说的“类型转换”是指当where子句中字段的类型与传入参数的类型不一致时发生的类型转换。通过转换函数人为的转换直接导致mysql无法使用指数。 如果需要转换，则需要对传入的参数进行转换。

比如utime是一个类型，传入的参数是“2016-07-23”。 比较大小时，通常是date(utime)>“2016-07-23”，可以优化为utime>“2016-07-23 00:00:00”

18. 尽可能使用较小的字段

MySQL从磁盘读取数据后，将其存储在内存中，然后使用CPU周期和磁盘I/O来读取它。 这意味着数据类型越小，占用的空间就越小，从磁盘读取或打包到内存中的效率都更好，但不要太执着于减少数据类型。 如果以后应用发生变化，就没有空间了。

修改表将需要重构，这可能会间接导致代码更改。 这是一个令人头疼的问题，所以需要找到一个平衡点。

19.内连接与左连接、右连接、子查询

第一：内连接 内连接也叫等价连接，left/就是外连接。

SELECT A.id,A.name,B.id,B.name FROM A LEFT JOIN B ON A.id =B.id;
SELECT A.id,A.name,B.id,B.name FROM A RIGHT JOIN ON B A.id= B.id;
SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;

从很多方面已经证实，内连接的性能更快，因为内连接是等值连接，返回的行数可能比较少。 但我们必须记住，某些语句隐式使用等效连接，例如：

SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;

建议：如果可以使用内联连接，请尝试使用内联连接。

第二：子查询的性能比外连接慢。 尝试使用外连接来代替子查询。

Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);

A表数据为十万级表，B表数据为百万级表。 在本机上执行大约需要2秒。 我们可以看到该子查询是一个相关子查询( )； mysql首先对A表执行全表查询，然后根据uuid逐条执行子查询。 如果外表是一个大表，我们可以想象查询性能会比这个更差。

一个简单的优化就是使用替换子查询的方法，查询语句改为：

Select* from A inner join B ON A.uuid=B.uuid using(uuid) where b.uuid>=3000; 这个语句执行测试不到一秒；

第三：使用JOIN时，应该用小结果来驱动结果（左连接，左表结果尽量小，如果有条件就先在左处理，右连接也是如此并反向），同时尽量涉及多个表的联合查询，拆分多个查询（多表查询效率低下，容易出现表锁和阻塞）。 喜欢：

Select * from A left join B A.id=B.ref_id where A.id>10;可以优化为：select * from (select * from A wehre id >10) T1 left join B on T1.id=B.ref_id;

20. 存在替换

SELECT * from A WHERE idin (SELECT id from B)
SELECT * from A WHERE id EXISTS(SELECT 1 from A.id= B.id)

in就是在内存中遍历比较

exit需要查询数据库，所以当B中数据量比较大时，效率比in要好。

in()只执行一次，缓存B表中的所有id字段，然后检查A表的id是否等于B表的id，如果id相等，则将A表的记录添加到结果中设置直到遍历完成。 表A中的所有记录。

In运行的流程原理如下：

　　　　List resultSet={};
　　　　Array A=(select * from A);
　　　　Array B=(select id from B);
　　　　for(int i=0;i

可见in()在B表数据较大时不适合使用，因为它会遍历一次B表的所有数据。
比如A表有10000条记录，B表有1条记录，可能会被遍历多达10000*次，效率非常低。
再比如：A表有10000条记录，B表有100条记录。 那么最多可以遍历10000*100次。 遍历次数大大减少，效率大大提高。
结论：in()适用于B表数据小于A表数据的情况。
exit()会被执行A.()次，执行过程代码如下
　　　　List resultSet={};
　　　　Array A=(select * from A);
　　　　for(int i=0;i

当B表的数据比A表的数据大时，适合使用()，因为它没有那么多的遍历操作，只需要执行另一个查询即可。
例如：A表有10000条记录，B表有1条记录，则()会执行10000次，判断A表中的id与B表中的id是否相等。
例如：A表有10000条记录，B表有1条记录，那么()仍然执行10000次，因为它只执行了A.次。 可见，表B中的数据越多，()越适合发挥其效果。
再比如：A表有10000条记录，B表有100条记录，那么()仍然执行10000次。 最好用in()遍历10000*100次，因为in()是在内存中遍历比较，而())需要查询数据库，
我们都知道查询数据库消耗的性能更高，内存也更快。
结论：（）适合B表数据量比A表数据大的情况
当A表和B表数据大小相同时，in和效率几乎相同，可以选择使用其中之一。
总结
以上就是本文的全部内容。 希望本文的内容对大家的学习或者工作有一定的参考价值。 感谢您对脚本之家的支持。