创建索引需要注意SQL语句的写法

最新推荐文章于 2023-06-15 10:00:00 发布

原创最新推荐文章于 2023-06-15 10:00:00 发布 · 502 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#索引

1、避免在where子句中使用 is null 或 not null

2、避免在where子句中使用！=或<>操作符

3、避免在where子句中使用or

4、少用 in 或not in

5、like通配符的使用不要“%11%” 要“11%”

          例如LIKE“%name”或者LIKE“%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

         那么如何解决这个问题呢，答案：使用全文索引。

          在我们查询中经常会用到select id,fnum,fdst from dynamic_201606 where user_name like '%zhangsan%'; 。这样的语句，普通索引是无法满足查询需求的。庆幸的是在MySQL中，有全文索引来帮助我们。

           创建全文索引的SQL语法是：

           ALTER TABLE `dynamic_201606` ADD FULLTEXT INDEX `idx_user_name` (`user_name`);

         使用全文索引的SQL语句是：

        select id,fnum,fdst from dynamic_201606 where match(user_name) against('zhangsan' in boolean mode);

6、避免在where子句中表达式操作 id/2=100 要用id=200

7、避免在where子句中进行函数式操作

8、在子句中使用exists代替in是一个好选择

9、避免在where子句中对字段进行null值判断

       对于null的判断会导致引擎放弃使用索引而进行全表扫描。

10、避免隐式类型转换

    where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型。

11、分段查询

在一些用户选择页面中，可能一些用户选择的时间范围过大，造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。

12、使用合理的分页方式以提高分页的效率

     select id,name from product limit 866613, 20

      使用上述SQL语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。

       优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。SQL可以采用如下的写法：

     select id,name from product where id> 866612 limit 20

13、区分in和exists、not in和not exists

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句？

原文链接：https://blog.youkuaiyun.com/zs742946530/article/details/82353045