MySQL的语句和索引的优化方法

一，sql语句优化

1、尽量避免使用子查询
SELECT * from t1 where cate_id in (select id from t2 where pid=21)
此条子查询的内部执行顺序是：先从外层查询t1表中取出一行记录，此行记录的相关列的值传给内层查询。然后执行内层t2表的查询，得到内层查询操作的值。最后外层查询t1表根据子查询t2表返回的结果得到满足条件的行。这样外层表一条记录的查询完成，要得到外层表所有满足条件的记录，需要不断重复上述过程，知道外层表最后一套记录查询结束。如果外层表的数据较多，查询会非常慢

将此条查询改成join查询：SELECT * from t1 as j LEFT JOIN t2 as c on j.cate_id=c.id where c.pid=21
join查询的原理是：先从驱动表t1中进行单表查询得到结果集r1，然后拿r1结果集中的每条数据挨个作为条件去和被驱动表t2中进行单表查询得到的结果集r2进行匹配,最终得到匹配结果r12，当有很多个表连接查询的时候就是不断重复上面的步骤，
2、避免函数索引
mysql不支持函数索引，即使此条字段是建立了索引，如果使用函数，则索引无效，所以应该在实际查询中避免函函数索引，例如非必要不使用rand()随机查询
status是表table的一个索引，下面两条语句中，第一条索引有效，第二条无效；
SELECT * from table where status = 1
SELECT * from table where ABS(status) = 1
使用explain查询索引是否生效如下图，可以看到第二张图中，索引没有生效


3、模糊查询效率低下
使用百分号、下划线查询等模糊查询查询非索引列或*无法使用到索引。如果查询的列是索引，则可以使用索引
SELECT status from table where status like ‘%1%’;
SELECT title from table where status like ‘%1%’;
如上语句，status是表索引，两条语句分别查询status字段和title字段，使用explain检查索引使用情况
，可以看到第一条数据，实际使用到了索引status，第二条语句没有使用到任何索引

4、查询适当的字段和适当的记录数
读取表字段，如果非必要情况，不要使用select * 查询。特别是数据表字段比较多的情况，增加了使用覆盖索引的可能性，这样会被一些无用的信息拖慢查询的速度，而且从业务上来说，对查询的数据进行处理的时候，太多无用的信息也会对开发者起到干扰的副作用。读取适当的记录，使用LIMIT M,N，而不要读多余的记录。直接使用limit读取的话，对于数据量庞大的表来说，效率也不高。例如分页查询，数据量越大，limit语句中的偏移量越大，效率就越低。可以使用where条件限制读取记录的起点。如下，可将第一条语句优化为第第二条
SELECT id,title from table limit 123456, 20；
SELECT id,title from table where id> 123456 limit 20；
5、批量INSERT插入
多条语句的插入操作，批量执行插入操作比逐条插入的效率高。因为批量插入值需要解析一次sql语句，而逐条插入需要多次解析。需要插入的数据越多，效率的差距越大

二，索引的优化：

1，不在索引列上做任何操作
上述已经提到，如果在索引列使用函数，会导致索引失效而进行全表扫描。如果使用极端和类型转换，同样会导致索引失效。
2，存储引擎不能使用索引中范围条件右边的列。
3，mysql在使用不等于等不确定的操作时，会使索引失效而导致全表扫描。另外null操作也会使索引失效，无论是is null还是is not null都会使索引失效
4，like 以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作。特殊情况：like查询的字段是索引字段，索引不会失效