mysql中in查询效率低的替代方法_简单谈谈如何提高sql效率

从最开始学习sql到至今，sql语句也是写了很多，但是一直都在专注于实现功能，很少关注效率。工作中常常听到吐槽，说有的sql语句真是触目惊心，明明可以简简单单提升多倍效率，但是偏偏选择一个耗时间的sql。因此，在这里简单聊一聊如何提高sql的效率，与大家共同进步哦～

由于不同数据库支持的sql语句还是会有一些差异，我下文涉及到的是基于mysql来进行的，但是本文思想还是所有适用sql的平台通用的。

一、explain语句

explain关键字可以模拟MySQL优化器执行SQL语句，可以很好的分析SQL语句或表结构的性能瓶颈。

explain的用法：explain+sql语句，例如：explain select * from securityprice order by `index`

结果如下：

结果包含12个字段：

1. id //select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

2. select_type //查询类型

3. table //正在访问哪个表

4. partitions //匹配的分区

5. type //访问的类型

6. possible_keys //显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到

7. key //实际使用到的索引，如果为NULL，则没有使用索引

8. key_len //表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

9. ref //显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值

10. rows //根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数

11. filtered //查询的表行占表的百分比

12. Extra //包含不适合在其它列中显示但十分重要的额外信息

具体explain字段解释可以参考如下文章：

https://segmentfault.com/a/1190000021458117?utm_source=tag-newest

https://blog.csdn.net/why15732625998/article/details/80388236

二、explain中type类型和提高sql效率建议：

在explain结果字段中，包含“type”表示访问的类型，其中常见type的访问效率如下：

NULL>system>const>eq_ref>ref>range>index>ALL

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行。

index: Full index Scan，Index与All区别：index只遍历索引树，通常比All快。因为索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。

range:只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引。一般就是在你的where语句中出现between、<>、in等的查询。

ref: 非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的行。

eq_ref: 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system。

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引。

假设用如下方式建立表：

CREATE TABLE `securityprice` (
  `index` datetime NOT NULL,
  `open` double DEFAULT '0',
  `close` double DEFAULT '0',
  `high` double DEFAULT '0',
  `low` double DEFAULT '0',
  `volume` double DEFAULT '0',
  `money` double DEFAULT '0',
  `stockname` varchar(20) NOT NULL,
  PRIMARY KEY (`stockname`,`index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
           

结合如上type访问效率，可以得出sql语句中提高效率的一些技巧如下：

1、应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

1）如果where中的内容是primary key，那么type 为const。

explain select  `open`,`close` from securityprice where `index`='2020-05-18' and stockname='000004.XSHE';
           

2）但是如果where中不是索引，那么type为all。

explain select  `open`,`close` from securityprice where `index`='2019-05-17';
           

3）为表加上索引：

ALTER TABLE `securityprice` ADD INDEX index_index ( `index` ) ;
           

可以看到此刻type为ref

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

1）如果运用了<>操作符

explain select  `open`,`close` from securityprice where `index`<>'2020-05-18'
           

3、应尽量避免在 where 子句中使用 or 来连接条件

explain select  `open`,`close` from securityprice where `index`='2020-05-18' or `index`='2020-05-17' 
           

如果修改成union语句，那么两个select查询均可以使用索引，类型为ref。

explain select  `open`,`close` from securityprice where `index`='2020-05-18' union  select  `open`,`close` from securityprice where `index`='2020-05-18'
           

此外，取代or的方法还有in、between and、exist。

如果是连续变量，可用between and。

一般情况下，exist效率好于in ，in效率好于or。

5、定义1个复合索引，索引的顺序是固定的；但查询sql语句中where后的语句优化器会进行自动优化。

也就是说：

select * from securityprice where `index`='2020-05-18' and stockname='000004.XSHE' and `open`=20 中where语句后的内容顺序是无影响的，优化器会进行优化。

但是如果建立复合索引，如下索引1、2、3是完全不同的索引：

索引1:a1，a2，a3
索引2:a3，a1，a2
索引3:a2，a3，a1
           

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

7、避免使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。这一点非常简单，但真的慎重写select * 。

8、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。

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

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