mysql查询优化

你的名字 2024-03-17 19:55 86阅读 0赞

**目录**

一.用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,表中有五百万数据.

![fbb6c83c07414bccbe4a60e5870218cb.png][]

![38fc69892aa948dc813827e963ce7712.png][]

#### 1.无索引 ####

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

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

![0bed190573074205a6a3db131b47e1e1.png][]

情况2:select classid from student ;只查一列大概需要大概需要3.6秒![3bdac292ce7e429588dac99520bc1a73.png][]

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

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

![b5c1c6e31a76418f8459ebedadedd895.png][]

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

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

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

![041095a941574b5e807e14fd4773bf05.png][]

#### 2.有索引 ####

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

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

![b5c1c6e31a76418f8459ebedadedd895.png][]

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

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

![2e81c9d02a024b67b8a69ce8bf780fd1.png][]

![4a1607b7c8844855859aa1de58a78d76.png][]

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

![d2c398a93f2d4b899c23e8c416267b07.png][]

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

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万数据

![eaa31296fc324cab98c257e5bd30ca55.png][]![a30eccba0fe7499f82463a8ded914bf1.png][]

##### 情况一:A1表letf jion B1表 #####

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

![972f907f374b4aadb99b56727a546271.png][]

执行过程就是,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秒

![d4f8e0218cec47039c8d5e754d0a7f16.png][]

执行过程就是,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万数据

![ed7665b0840f4471a11eece0943af595.png][]![c617a8c6367740e090f46ee3820bac17.png][]

事先说明一:,有百度说两个表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班'
    //耗时无限

![24d0e7c794054e7199a9266ceb7e8e0a.png][]

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

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

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

![0f778e8d7295497180ba0b28296db5f6.png][]

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

##### 总结: #####

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

#### 3. 多表查询优化总结: ####

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

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

\---------------------------------------------------------------------------------------------------------------------------

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

[fbb6c83c07414bccbe4a60e5870218cb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/abb37da6a5b64b76be7651a7a7dd6797.png
[38fc69892aa948dc813827e963ce7712.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/92be7df94866463fbdff659eb3113313.png
[0bed190573074205a6a3db131b47e1e1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/030f8aaaedc24286873e2fd3e5e84df3.png
[3bdac292ce7e429588dac99520bc1a73.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/1c1c5aaac9ed4277aef9b1dcea51247f.png
[b5c1c6e31a76418f8459ebedadedd895.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/605d05aed9774472991e2a272458ab06.png
[041095a941574b5e807e14fd4773bf05.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/280223de71944ea0bd3b93d436bdd08a.png
[2e81c9d02a024b67b8a69ce8bf780fd1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/48ebbf9d78df437c9b2126c426a9f1af.png
[4a1607b7c8844855859aa1de58a78d76.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/42918575fe4e470194371e7555cb4de0.png
[d2c398a93f2d4b899c23e8c416267b07.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/65e6dd69b8e4466aafe465dac6988cb8.png
[eaa31296fc324cab98c257e5bd30ca55.png]: https://img-blog.csdnimg.cn/eaa31296fc324cab98c257e5bd30ca55.png
[a30eccba0fe7499f82463a8ded914bf1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/f79e16331cbe48bfb9399937ea64a8c7.png
[972f907f374b4aadb99b56727a546271.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/1e87f0c9c80f4d7fbdec3bcb7cd45b65.png
[d4f8e0218cec47039c8d5e754d0a7f16.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/eb31e900e3704da99d689415659a54d6.png
[ed7665b0840f4471a11eece0943af595.png]: https://img-blog.csdnimg.cn/ed7665b0840f4471a11eece0943af595.png
[c617a8c6367740e090f46ee3820bac17.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/b405c099eee14ba6a597f70fab09fc62.png
[24d0e7c794054e7199a9266ceb7e8e0a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/643e468fb12246c6a8ba309b0bbdb0e7.png
[0f778e8d7295497180ba0b28296db5f6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/94d30a0459b54ef79abe4a9811243578.png