+----------+------------+---------------------------------------+| Query_ID | Duration | Query |
+----------+------------+---------------------------------------+| 1 | 0.00024700 | select * from mytable where id=100 |
| 2 | 0.27912900 | select * from mytable where id+1=101 |
+----------+------------+---------------------------------------+
另外很多同学在拉取全表数据时,喜欢用select xx from xx limit 5000,1000这种形式批量拉取,其实这个SQL每次都是全表扫描,建议添加1个自增id做索引,将SQL改为select xx from xx where id>5000 and id<6000;
+----------+------------+-----------------------------------------------------+| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------+| 1 | 0.00415400 | select * from mytable where id>=90000 and id<=91000 |
| 2 | 0.10078100 | select * from mytable limit 90000,1000 |
+----------+------------+-----------------------------------------------------+
合理用好索引,应该可解决大部分SQL问题。当然索引也非越多越好,过多的索引会影响写操作性能
只select出需要的字段,避免select
+----------+------------+-----------------------------------------------------+| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------+| 1 | 0.02948800 | select count(1) from ( select id from mytable ) a |
| 2 | 1.34369100 | select count(1) from ( select * from mytable ) a |
+----------+------------+-----------------------------------------------------+
尽量早做过滤,使Join或者Union等后续操作的数据量尽量小