在MySQL-5.7.23下验证一些sql优化建议的正确性

最新推荐文章于 2022-07-06 09:16:36 发布

石头成说

最新推荐文章于 2022-07-06 09:16:36 发布

阅读量410

点赞数

分类专栏： mysql 文章标签： mysql查询语句优化

本文链接：https://blog.csdn.net/qq_27127145/article/details/86487025

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

从网上看到一些sql查询优化的一些建议，由于使用的数据库版本不同等原因，一些建议可能描述不准确。毕竟网上的一些内容还是需要自己去验证一下的。因此作者对常见的一些sql优化建议进行了验证。

验证表：

其中id字段是主键，在mysql中默认创建主键索引。会根据具体验证情况，在其他字段上创建索引。

1、应尽量避免在 where 子句中使用!=、<>、in、not in、between and 等范围条件查询，否则将导致存储引擎API放弃使用索引而进行全表扫描

不一定。索引：age

select *：

SELECT * from user WHERE age = 13;使用

SELECT * from user WHERE age < 13;使用

SELECT * from user WHERE age != 13;不使用

SELECT * from user WHERE age <> 13; 不使用

SELECT * from user WHERE age > 13; 不使用

SELECT * from user WHERE age in (11,22,13);不使用

SELECT * from user WHERE age not in (11,22,13);不使用

SELECT * from user WHERE age between 1 and 33;不使用

查询所有列时，范围条件对索引的使用有影响。但是有一个列外，<符号可以使用索引。

select 建立索引的字段：

SELECT age from user WHERE age = 13; 使用索引

SELECT age from user WHERE age <> 13; 使用索引

SELECT age from user WHERE age != 13;使用索引

SELECT age from user WHERE age < 13;使用索引

SELECT age from user WHERE age > 13;使用索引

SELECT age from user WHERE age in (11,22,13)或者not in(11,22,13)或者 between 1 and 33; 均使用索引

SELECT id,age from user WHERE age in (11,22,13)或者not in(11,22,13)或者 between 1 and 33; 均使用索引

由于查询的列上都有索引(id为默认的主键索引)，所以范围条件对索引无影响。

2、前置%查询会导致全表扫描

不一定。索引：name

select *：

SELECT * from user WHERE name like '%c%'; 不使用索引

SELECT * from user WHERE name like 'c%';使用索引

select 建立索引的字段：

SELECT name from user WHERE name like '%c%'; 使用索引

3、尽量避免在 where 子句中使用 or 来连接条件，如果or的字段上有一个字段没有建立索引，将导致引擎放弃使用索引而进行全表扫描，如：

正确。

SELECT id from user WHERE age = 13 or phone = '13167001221';

若在age和phone两个字段其中一个字段上没有索引，则不会使用索引。必须都有索引才使用索引。

可以这样查询：

SELECT id from user WHERE age = 13

union all

SELECT id from user WHERE phone = '13167001221';

这样有索引字段的使用索引，没有的不使用索引(age字段有索引，phone没有索引)。

注意：即使or两边的字段都建立索引，虽然最后会使用到索引，但通常这也是一个不好的操作。

在《高性能MySQL》一书中，有这样的描述：

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

正确。

使用表达式，索引：age

SELECT * from user WHERE age = 3; 使用索引

SELECT * from user WHERE age/2 = 3;不使用索引

select *： 索引：name

SELECT * from user WHERE substring(name,1,3) = 'abc'; 查询name以abc开头不使用索引

改为： SELECT * from user WHERE name like 'abc%'; 使用索引

select 建立索引的字段：

SELECT name from user WHERE substring(name,1,3) = 'abc'; 使用索引

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

(未验证)

select id from t where num=@num

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

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

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

不正确。与null无关。

select * from user where age is null;

select * from user where age = 0;

若age是索引列，则两条语句都会使用索引。

若age不是索引列，则两条语句都不会使用索引。

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

EXISTS:一种通俗的可以理解为：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS 子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果。

EXISTS语法并没有说哪个字段落在了子查寻的结果中，而是说exists后面的语句执行的结果是不是有记录，只要有记录，则主查询语句就成立。它代表‘存在’，用来引领嵌套查询的子查询，它不返回任何数据，只产生逻辑真值‘true’与逻辑假值‘false’。由EXISTS引出的子查询，其目标列表达式通常都用*（用null也可以），因为带有EXISTS的子查询只返回真值或假值，给出列名没有实际意义。

select * from user a where age in(select age from user_b b);

select * from user a where EXISTS(select 1 from user_b b WHERE b.age = a.age);

在数据量比较小的情况下，以上两个查询用时差不多，在0s左右。

但是在100万条记录的表t_user中，exists非常耗时：

分析：如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in，反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。

在第二条语句中，由于是exists，那么以外层表为驱动表，需要先查询出t_user表中大量的数据，再进行exists筛选，非常耗时。远远多于使用in时先进行子查询再进行多个等值查询所用的时间。

使用内连接查询代替exists反而是一个好的选择。

select * from t_user a, t_user_b b where a.password = b.password; 耗时0s。

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

由于在password字段上建立了B-Tree索引，在只查询password字段时，查询覆盖了索引，直接获取password这个二级索引上的主键id，再根据主键id查询，所以速度很快。

实际上，select * 与 select 大多数字段，两者差别几乎可忽略(在4000w行数据上验证)。所以查询所有字段（或者大多数字段）的时候，大可select *来操作。如果某些不需要的字段数据量特别大，还是写清楚字段比较好，因为这样可以减少网络传输。

9、在使用索引字段作为条件时，如果该索引是多列索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。（最左匹配原则）。

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

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

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

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

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

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

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

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

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