mysql查询优化
目录
一.用explain去分析sql
二.单表查询优化
1.无索引
技巧一:避免select * 查询全部列
技巧二:sql的全盘扫描是从第一行数据一直扫描到最后一行.因此在提前知道查询的条数时加个limit n.
2.有索引
技巧一:给经常被当做搜索条件的列加索引
技巧二:用like的话尽量把%放后面,如’9%’ ,可以尽可能的发挥索引的作用
技巧三:有了索引后,避免索引失效
三.多表查询的优化
1.left join 外连接查询优化
情况一:A1表letf jion B1表
情况二:B1表letf jion A1表
总结:
- join 内连接查询优化
情况一:A1表letf jion B1表,on后面没有跟索引
情况二:A1表letf jion B1表,on后面跟索引
总结:
- 多表查询优化总结:
一.用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 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
- 对于连续的数值，能用 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 后面的字段加上索引.
-—————————————————————————————————————————————————————————————
个人小总结,更多的优化方法请自行百度.
你的名字
Myth丶恋晨
绝地灬酷狼
港控/mmm°
淩亂°似流年
亦凉
还没有评论，来说两句吧...