MySQL的查询性能优化——《深究MySQL》

更新中。。。。。。。

#写在前面

mysql查询缓慢的原因有哪些?
1.查询语句是否请求了不必要的多余数据
2.
总结以上原因之后,优化数据库性能,就需从以下几个方面着手:
1.

说明:下面的知识为零散的记录,后期需要整理

一、Mysql优化


1.优化insert和update

1.1 批量插入:将多条要插入的数据合并成一条

  • 合并后日志量(MySQL的binlog和innodb的事务让日志)减少了,降低日志刷盘的数据量和频率,从而提高效率;
  • 通过合并SQL语句,减少网络传输的IO;
  • 通过合并SQL语句,减少SQL语句解析的次数;

注意事项:数据库sql长度是有限制,sql长度过大会溢出报错。可通过max_allowed_packet配置修改,默认是1M

1.2 事务插入:在开始插入前开启事务,插入完的提交事务

  • 进行一个INSERT操作时,MySQL内部会建立一个事务,在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗;

注意事项:事务日志大小也有限制,过大会刷磁盘,导致性能下降。可通过innodb_log_buffer_size配置修改

1.3 主键顺序插入:前面插入数据和主键和后插入数据的主键是有序的

  • 如果有序,新插入的记录直接定位在索引的最后面,索引的定位效率会很高,也且对索引调整也较小。

  • 如果无序,新插入的记录可能要定位到索引中间,索引定位效率下降,而这时还需要B+tree进行分裂合并等处理,会消耗比较多计算资源,数据量较大时会有频繁的磁盘操作。

参考:https://www.2cto.com/database/201707/654686.html

2. in 和 exsits的区别和效率

参考:https://www.cnblogs.com/emilyyoucan/p/7833769.html
总结如下:
select a.* from A a where exists(select 1 from B b where a.id=b.id)
exists()适合B表比A表数据大的情况

select * from A where id in(select id from B)
in()适合B表比A表数据小的情况

2.not in 和not exists

如果查询语句使用了not in ,那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。

3. 要建索引的MySQL字段尽量设置为NOT NULL

含有空值的列很难进行查询优化,而且表索引是不会存储NULL值的,所以如果索引的字段可以为NULL,索引的效率会下降很多。
因为它们使得索引、索引的统计信息以及比较运算更加复杂。
你应该用0、一个特殊的值或者一个空串代替空值。

4.负向查询不能使用索引

反例:select name from user where id not in (1,3,5);
正例:select name from user where id in (2,4,6);

5. 前导模糊查询不能使用索引

反例:select name from user where name like '%zhang ’
正例:select name from user where name like ‘zhang%’

6. 不对索引字段进行计算(不然索引会失效)

反例:select name from user where FROM_UNIXTIME(create_time) < CURDATE();
正例:select name from user where create_time < FROM_UNIXTIME(CURDATE());

7.保证参数的数据类型与库一致(不然会作强制转换)

库里 phone字段为varchar
反例:select name from user where phone=18810503732;
正例:select name from user where telno=‘18810503732’

8. 避免复合索引失效

说明:user表的username 和phone 字段创建了复合索引,且username在前面。
能利用到索引的SQL:
select username from user where username=‘zhangsan’ and phone =‘18810503732’

select username from user where phone =‘18810503732’ and username=‘zhangsan’

select username from user where username=‘zhangsan’

不能利用到索引的SQL:

select username from user where phone =‘18810503732’

这个类似于电话本,你先以用户名字作了目录,再按电话号作的目录。如果你先查电话的目录的话,肯定是不行的。你只能先安用户名字去搜索。

非同步方法会不会受同步方法的影响?

SQL语句中or的使用方法注意事项

二、优化特定类型的查询


1. 优化count查询

1.尽量使用count(*)。当然也可以尝试索引覆盖,此时最好是主键。

count(columnName) 虽然也可以求出行数,但是它有一个致命的问题,那就是不统计为NULL的列。所以如果用列名代替*求行数的方式可能会出现统计错误。

2.优化关联查询

1.确保在ON或USING的关联顺序中第二个表的关联列有索引

例如,当A表和B表的关联顺序是A、B,即SQL语句为select * from A left join B on A.cid = B.cid 时,需要保证在B表的cid列表创建索引,而A表的cid列有无索引对性能没有影响。

2. 尽量让group by 和order by 的表达式中只涉及到一个表中的列

分组的字段在一个表,排序的字段又在另外一个表中,这样就很难利用到索引。

3.优化子查询

1. 尽量使用关联查询代替子查询

子查询会创建临时表,而临时表又没有任何索引

2. 子查询优化的基本思路,就是消除嵌套层次,把子查询与父查询放在同一个层次去执行。
实现步骤:

把子查询的FROM子句中的表对象,与上层的父FROM子句中的表对象做JOIN

再把子查询的WHERE子句中的条件表达式,与上层的父WHERE子句中的条件表达式用“AND”操作符连接

4.优化group by

1. 不关心顺序时,尽量用order by NULL 避免group by的默认排序(从而避免了文件排序)

group by子句如果没有指定排序列,则会自动根据分组列进行默认升序排序,从而导致了文件排序。

有个一般人不会用的方式,那就是直接在 group by 字段A 后面加上ASC或DESC关键字,使得分组结果按分组列(字段A)进行升序或降序排序。
2. group by要求返回的所有字段,要么出现在聚合函数(avg、sum、count、max、min等)中,要么出现Group By后面作为分组依据,不然高版本的mysql会报错。这是因为分组后,没有通过聚合函数聚合的多行数据不知道怎么存放在一行了,高版本时可通过配置文件设置忽略,从而只选择一行来存。
3. 注意:group by是先排序再分组,且是分组后再聚合。

4.1优化group by with rollup

可通过explain查看执行计划,决定是否加with rollup

with rollup表示需要做超级聚合,即可统计分组结果中每组数据分组前的某些值,如我们需要统计各部门员工工资的平均值
select depId, avg(money) from user group by depId with rollup;

需要注意的是,用了with rollup后不能再使用order by关键字,但可以直接在分组字段后加asc或desc

参考:https://blog.csdn.net/id19870510/article/details/6254358

5.优化limit

1.尽量利用索引覆盖做“延迟关联"(延迟关联也可以优化关联查询的limit子句)

在做分页查询时,如果遇到像limit 2000,20这样偏移量大的查询时,mysql会实际查询2020条数据,然后抛弃前面2000条数据,只返回最后20条。这样做的代价非常高。

limit 2000,20 这样的分而之所以会慢,是因为offset。offset会导致mysql扫描大量不需要的行然后抛弃掉。所以平时我们看到 limit 1 这种写法是与offset无关的,也就是说这种写法不会很慢。顺便提一下,limit 2000,20 是等价于 limit 20 offset 2000的

6.优化union查询

1.如果不需要对查询结果去重的话,尽量使用union all 而不是union。

union后没跟all 时,会自动给临时表加上distinct做唯一性检查,从而降低性能。

2.巧用 limit

(SELECT * from sys_user where id < 2000 order by office_id ) UNION (SELECT * from sys_user WHERE id >2000 order by office_id ) order by office_id desc limit 20;

两个括号里的子查询都会将各自的数据全部放在一个临时表中,然后从临时表中取出20条数据,这时可以将每个子查询都用上limit 20

(SELECT * from sys_user where id < 2000 order by office_id limit 20) UNION (SELECT * from sys_user WHERE id >2000 order by office_id limit 20 ) order by office_id desc limit 20;

这样一来,临时表中就只有40条数据了。需要注意的是,从临时表取数据的顺序是不确定的,所以要想按自定义的顺序取,必须在全局加上order by ,如上面SQL上的红色字。

7.优化 in 子查询

1.将in子查询用innor join改写成关联查询

IN的子查询在实际执行的时候会被mysql自动改写成较缓慢的查询(mysql有时候也会自以为是的,哈哈),如下查询

select * from film where film_id IN(select film_id from film_actor where actor_id = 1)

会被改成写:

select * from film where film_id exists (select film_id from film_actor where actor_id = 1 and film_actor.film_id = film.film_id )

从改写后的语句可以看到,子查询里面涉及到外部表 film 的film_id字段了,所以mysql会先对外部表film进行全表扫描,通过explain执行计划也可以看到。

优化后的写法为:

select * from film inner join film_actor ON film.film_id = film_actor.film_id WHERE film_actor.actor_id = 1;

8.优化 MAX()、MIN()

1. 通过order by 和limit来优化

如下,我们需要获取最大值和最小值(前提是id值有索引)

优化前:
最小值:select min(id) from actor;
最大值:select max(id) from actor;

优化后:
最小值:select id from actor order by id asc limit 1;
最大值:select id from actor order by id desc limit 1;

从explain可以看出,虽然优化前和优化后都用到了索引,但是min和max函数进行了大量的扫描,而优化后只扫描了一行。

9.优化OR查询

用UNION替换OR (仅适用于索引列)

对索引列使用OR将造成全表扫描。注意,该规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低。

在下面的例子中, LOC_ID 和REGION上都建有索引:
高效: select log_id , log_desc , region from log where log_id = 10 UNION
select log_id , log_desc , region from log where region= “update”

低效: select log_id , log_desc , region from log where log_id = 10 OR region = “update”

如果你坚持要用OR, 那就建议你将返回记录最少的索引列写在最前面。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值