关于sql的一点心得

积累了一定的后端开发经验，谈一下写sql的一点心得

---

 

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  来查看 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

 

剩下的后面继续补充