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