MySql教程

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

本文主要是介绍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查询的字段是索引字段,索引不会失效

这篇关于MySQL的语句和索引的优化方法的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!