mysql查询优化

查询优化对于字段定义的要求

1 选择合理的字段类型：在选择字段类型时，应该遵循这样的原则：

• 能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。
• 尽可能使用小的类型，比如：用bit存布尔值，用tinyint存枚举值等。
• 长度固定的字符串字段，用char类型，长度可变的字符串字段，用varchar类型。
• 金额字段用decimal，避免精度丢失问题。

2字段定义尽可能地使用NOT NULL。

 

查询优化注意事项

1. 避免使用select *，选择必需的字段
2. 当只要一行数据时使用limit 1
3. 为搜索、排序字段建立索引，索引不要包含选择性过低字段，选择性高的字段前置或者单独建立索引， 尽量使用覆盖索引，避免最左匹配截断、索引选择性过低、asc和desc混用导致索引失效，满足业务需求的情况下，尽量降低主键的长度
4. 避免在where子句中对字段进行null值判断：对于null的判断会导致搜索引擎放弃使用索引而进行全表扫描。
5. 慎用!=或<>操作符，这些操作符会导致搜索引擎放弃使用索引而进行全表扫描。
6. 注意范围查询语句：对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效。
7. 不建议使用%前缀模糊查询:例如like “%name”或者like “%name%”，这种查询会导致索引失效而进行全表扫描，但是可以使用like “name%”。可以使用全文索引来代替%前缀模糊查询
8. 在where子句中对索引字段进行表达式或者函数操作会导致索引失效
9. where条件中or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。可以使用union all或者是union的方式来代替or会得到更好的效果。
10. in包含的值不应过多,对于连续的数值，可以用between and代替；不连续的数值可以使用连接union all来替换。
11. 尽量用union all代替union：union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算。当然，使用union all的前提条件是两个结果集没有重复数据。
12. 区分in和exists、not in和not exists：select * from 表A where id in (select id from 表B) 相当于select * from 表A where exists(select * from 表B where 表B.id=表A.id)，exists以外层表为驱动表，先被访问，in先执行子查询。所以in适合于外表大而内表小的情况；exists适合于外表小而内表大的情况。not in和not exists之间推荐使用not exists
13. 注意inner join自动找出数据少的表作为驱动表，left join左表驱动右表。使用时要用小表去驱动大表
14. 分段查询：一些由于用户可选择时间范围过大造成查询缓慢的页面。可以通过程序分段进行查询，将结果合并处理。
15. 避免在查询中存在数据类型隐式转换和编码转换
16. 不使用order by rand()

 

count函数

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是NULL，累计值就加 1，否则不加，最后返回累计值。

count(主 键) InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为null)

count(字 段) 没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

count(数 字) InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。

count(*) InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)。

 

插入数据

主键顺序插入，性能要高于乱序插入。

如果一次性需要插入大批量数据(比如: 几百万的记录)，使用insert语句插入性能较低，此时可以使用mysql数据库提供的load指令进行插入。

 

深分页

mysql在执行limit n,m时，工作原理就是先读取前面n条记录，然后抛弃前n条，读后面m条想要的，所以n越大，偏移量越大，性能就越差。优化的方式就是避免深分页带来的额外回表。

1避免深分页：可以改成id过滤，每次都只查询大于上次查询的数据id。这样每次只查询100条，回表也只需要回表100条

 select * from my_table where id>上次查询的数据id值 limit 100

2 索引覆盖+子查询优化 避免大量回表 ：

select * from my_table t1,(select id from my_table where col_c=1 limit 1000,100) t2 where t1.id=t2.id

 

索引下推

索引下推(Index Condition Pushdown，简称ICP)，是MySQL5.6版本的新特性，它能减少回表查询次数，提高查询效率。

服务层负责SQL语法解析、生成执行计划等，并调用存储引擎层去执行数据的存储和检索。

索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。

在MySQL5.6之前，通过使用非主键索引进行查询的时候，存储引擎通过索引查询数据，然后将结果返回给MySQL Server层，在server层判断是否符合条件。

在MySQL5.6及以上版本，可以使用索引下推的特性。当存在索引的列做为判断条件时，MySQL server将这一部分判断条件传递给存储引擎，然后存储引擎会筛选出符合MySQL server传递条件的索引项，即在存储引擎层根据索引条件过滤掉不符合条件的索引项，然后回表查询得到结果，将结果返回给MySQL server。

可以看到，有了索引下推的优化，在满足一定的条件下，存储引擎层会在回表查询之前对数据进行过滤，可以减少存储引擎回表查询的次数。

使用explain查看，当Extra列的值为Using index condition，则表示使用了索引下推。

对于innodb引擎，索引下推只能用在二级索引，也就是非主键索引。主键索引的叶子节点上保留的是完整的数据，无需回表。子查询不能使用索引下推。存储过程不能使用索引下推

 

 

反向扫描索引

Backward index scan，反向扫描索引，MySQL中默认索引的叶子节点是从小到大排序的，若要从大到小查询排序就是反向扫描，就会出现 Backward index scan。 在MySQL8版本中，支持降序索引，我们可以创建降序索引。

 

本文内容参考 https://blog.csdn.net/m0_53022813/article/details/124514337