searchusermenu
  • 发布文章
  • 消息中心
点赞
收藏
评论
分享
原创

sql使用简单心得

2023-02-24 03:13:27
15
0

1.select * from  

* 一定要要慎用,最好是只返回你需要的列,否则效率会很低


2.游标要慎用

之前在实际项目中,因为内存不足的原因,使用游标从数据库中读数据,

游标实际上就是分批放入内存,实际上减小了内存的占用,但效率会变低


3.索引建立在需要查询较为频繁的列

要建立在经常做查询的列上


4.防止索引失效是很关键的

  (1) 最左前缀法则:比如create index 了三个索引,进行查询匹配时,要按顺序走索引

       ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column1`,  `column2`,  `column3`  )

        查询时符合最左前缀:

            select id from tablename where column1 = “spx”;      

            select id from tablename where column1 = “spx” and column2= "spx"

            select id from tablename where column1 = “spx” and column2 ="spx" and column3="spx"

        违背最左前缀,索引全部失效:

            select id from tablename where column2 = “spx”  and column3="spx"

        符合最左前缀,只有左列索引生效(跳跃了某一索引列):

            select id from tablename where column1 = “spx”  and column3="spx"

   (2) 索引列进行运算操作,索引将失效

   (3)使用or 时如果or之前是索引列,or之后不是那么索引失效,所以限制条件字段没有索引就少用or;

     解决方案:可以使用union 或者union all 效果更好,但是尽量要使用 union all ,

        因为前者要加结果集合并后再进行过滤操作,增大cpu运算,但是union all 前提是俩个结果集没有重复数据

        column1 --索引列   column 2 -- 不是索引列

        索引失效:select id from tablename where conlumn1 ="spx"  or conlumn2="spx";

        解决: select id from tablename   where conlumn1=“spx”

           union all 

           select id from tablename  where conlumn2="spx";

   (4)模糊查询时,以%开头,索引失效,尽量都写成尾部模糊匹配

   (5) is NULL,is not NULL 有时索引失效

   (6) 索引列数据类型不匹配,比如 column1 的类型是varchar

      eg: select id from  tablename where  column1 = 1;   没有加引号,会自动转类型导致索引失效 


5.复合索引大多数情况下效率高于单列索引

因为多条件联合查询时,mysql优化器会评估哪个条件的索引的效率高,会去选择最佳的索引

6.EXPLAIN的使用

要经常使用 EXPLAIN  来查看 sql 的执行计划,这算一个很好的习惯,比较sql的性能,查看该语句是否使用了索引等等,真的很重要!!


7. in 和 exists , not in 和 not exists 小结

  select *from tablename1 where id in (select id from tablename2)

  上面的sql等于下面这个

  select *from tablename1 where exists(select id tablename2 where tablename2.id = tablename1.id)

  根据驱动的顺序可知,in先执行子查询,in适合于外表大而内表小的情况,exists先对外表做loop循环,所以适合于外表小而内表大的情况

  not in 内外表都进行全表扫描,而 not exists 得子查询依然能用到表上的索引,所以一定要使用 not exists

0条评论
0 / 1000
嘎嘎嘎嘎
15文章数
0粉丝数
嘎嘎嘎嘎
15 文章 | 0 粉丝
原创

sql使用简单心得

2023-02-24 03:13:27
15
0

1.select * from  

* 一定要要慎用,最好是只返回你需要的列,否则效率会很低


2.游标要慎用

之前在实际项目中,因为内存不足的原因,使用游标从数据库中读数据,

游标实际上就是分批放入内存,实际上减小了内存的占用,但效率会变低


3.索引建立在需要查询较为频繁的列

要建立在经常做查询的列上


4.防止索引失效是很关键的

  (1) 最左前缀法则:比如create index 了三个索引,进行查询匹配时,要按顺序走索引

       ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column1`,  `column2`,  `column3`  )

        查询时符合最左前缀:

            select id from tablename where column1 = “spx”;      

            select id from tablename where column1 = “spx” and column2= "spx"

            select id from tablename where column1 = “spx” and column2 ="spx" and column3="spx"

        违背最左前缀,索引全部失效:

            select id from tablename where column2 = “spx”  and column3="spx"

        符合最左前缀,只有左列索引生效(跳跃了某一索引列):

            select id from tablename where column1 = “spx”  and column3="spx"

   (2) 索引列进行运算操作,索引将失效

   (3)使用or 时如果or之前是索引列,or之后不是那么索引失效,所以限制条件字段没有索引就少用or;

     解决方案:可以使用union 或者union all 效果更好,但是尽量要使用 union all ,

        因为前者要加结果集合并后再进行过滤操作,增大cpu运算,但是union all 前提是俩个结果集没有重复数据

        column1 --索引列   column 2 -- 不是索引列

        索引失效:select id from tablename where conlumn1 ="spx"  or conlumn2="spx";

        解决: select id from tablename   where conlumn1=“spx”

           union all 

           select id from tablename  where conlumn2="spx";

   (4)模糊查询时,以%开头,索引失效,尽量都写成尾部模糊匹配

   (5) is NULL,is not NULL 有时索引失效

   (6) 索引列数据类型不匹配,比如 column1 的类型是varchar

      eg: select id from  tablename where  column1 = 1;   没有加引号,会自动转类型导致索引失效 


5.复合索引大多数情况下效率高于单列索引

因为多条件联合查询时,mysql优化器会评估哪个条件的索引的效率高,会去选择最佳的索引

6.EXPLAIN的使用

要经常使用 EXPLAIN  来查看 sql 的执行计划,这算一个很好的习惯,比较sql的性能,查看该语句是否使用了索引等等,真的很重要!!


7. in 和 exists , not in 和 not exists 小结

  select *from tablename1 where id in (select id from tablename2)

  上面的sql等于下面这个

  select *from tablename1 where exists(select id tablename2 where tablename2.id = tablename1.id)

  根据驱动的顺序可知,in先执行子查询,in适合于外表大而内表小的情况,exists先对外表做loop循环,所以适合于外表小而内表大的情况

  not in 内外表都进行全表扫描,而 not exists 得子查询依然能用到表上的索引,所以一定要使用 not exists

文章来自个人专栏
java开发
2 文章 | 1 订阅
0条评论
0 / 1000
请输入你的评论
1
0