mysql查询优化

目录

一.用explain去分析sql

二.单表查询优化

1.无索引 

技巧一:避免select * 查询全部列

技巧二:sql的全盘扫描是从第一行数据一直扫描到最后一行.因此在提前知道查询的条数时加个limit n.

2.有索引

技巧一:给经常被当做搜索条件的列加索引

技巧二:用like的话尽量把%放后面,如'9%' ,可以尽可能的发挥索引的作用

 技巧三:有了索引后,避免索引失效

三.多表查询的优化

1.left join 外连接查询优化

 情况一:A1表letf jion B1表

情况二:B1表letf jion A1表

总结:

2. join 内连接查询优化

情况一:A1表letf jion B1表,on后面没有跟索引

情况二:A1表letf jion B1表,on后面跟索引

总结:

3. 多表查询优化总结:

---

一.用explain去分析sql

详见我的mysql专栏---explain文章.

二.单表查询优化

 现在建一张表,student,表中有五百万数据.

1.无索引 

技巧一:避免select * 查询全部列

情况1:select * from student ;查询所有列大概需要6秒

 情况2:select classid from student ;只查一列大概需要大概需要3.6秒

技巧二:sql的全盘扫描是从第一行数据一直扫描到最后一行.因此在提前知道查询的条数时加个limit n.

情况一:查询第一条数据,大概需要2.4秒

 情况二:查询最后一条数据,大概需要2.4秒

 查询第一条和查询最后一条两者时间一样,因为两者都是全盘扫描了

 情况三:查询第一条数据,加个Limit 1,大概需要0.012s,避免了全盘扫描 

2.有索引

技巧一:给经常被当做搜索条件的列加索引

 情况一:查询第一条数据,没索引大概需要3.2秒

情况二:给classid列加个Normal普通索引或Unique 普通索引 ,大概需要1.5秒

(因为普通索引和普通索引都是建一样的索引数,它俩查询效率一样高,实验后证实,的确一样高,所以下面就只列了Normal普通索引的例子)

 

 

技巧二:用like的话尽量把%放后面,如'9%' ,可以尽可能的发挥索引的作用

 技巧三:有了索引后,避免索引失效

1.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num is null

2.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

3. 对于连续的数值，能用 between 就不要用 in 了,如：select id from t where num in(1,2,3)改为select id from t where num between 1 and 3

4.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为:select id from t where num=100*2

5.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)=’abc’ ，name以abc开头的id应改为：select id from t where name like‘abc%’

6.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

三.多表查询的优化

1.left join 外连接查询优化

有两表:A1表和B1表,都是500万数据

 情况一:A1表letf jion B1表

SELECT * FROM `A1` a left JOIN B1 b on a.sid = b.classid
//耗时无限非常多秒

执行过程就是,A1表取出一条数据,然后B1表扫描全部行的B.classid字段找到等于a.sid的.

但是B.classid字段没有索引,所以只能走全盘磁盘扫描.

然后A1表再取下一行数据,再循环上个步骤.      因此就是A1表的500万次*B1表的500万次

情况二:B1表letf jion A1表

SELECT * FROM B1 b left JOIN `A1` a on b.classid = a.sid
//耗时13秒

执行过程就是,B1表取出一条数据,然后A1表扫描全部行的A.sid字段找到等于B.classid的.

因为A.sid字段是主键索引,所以走索引.

然后B1表再取下一行数据,再循环上个步骤.      因此就是B1表的500万次*A1表的500万次,但是A1表的500万走索引,所以就非常快.

总结:

A left join B联查的时候, B表作为子表, 要尽量把on 后面的作为子表的B表的字段设为索引,这样查询就很快.

2. join 内连接查询优化

有两张表,a1表,500万数据,    c1表,500万数据

事先说明一:,有百度说两个表join 的执行过程是:

要先进行笛卡尔积交叉相乘得到临时表,再on

那么问题来了,两个表join不就是先生成一个500万*500万数据的临时表吗,一行数据大约20B,那么也要需要5万G的内存才能存下这个临时表.  

其实,sql是有优化器简化的.  我们可以简单的认为join的逻辑是先生成临时表,这样便于我们对查询的过程和结果在我们大脑的理解,  但是,sql的优化器是很复杂的,我们很难彻底弄懂这个过程,反正知道sql的优化器是不会让一个500万*500万数据的临时表真的出现就行了.

事先说明二:

因为join on 是不分主子表,没有基表的, 所以a1表和c1表谁写在前面都一样.

情况一:A1表letf jion B1表,on后面没有跟索引

select * FROM a1 join c1 on c1.classC ='1班'
//耗时无限

 on 后面的C.classC没有索引,查询非常慢

情况二:A1表letf jion B1表,on后面跟索引

select * FROM a1 join c1 on c1.idC ='1'
//耗时8.5秒

  on 后面的C.idC是主键索引,查询非常快

总结:

A  join B联查的时候,  要把on 后面的字段设为索引,这样查询起来就快的多.

3. 多表查询优化总结:

其实三表或更多表的联查和两张表联查的原理是一模一样的,这里我就不再举例三表或更多表的联查的例子了.

多表联查的优化就一条,就是在单表查询的优化条件的基础之上,给on 后面的字段加上索引.

---------------------------------------------------------------------------------------------------------------------------

个人小总结,更多的优化方法请自行百度.