建设银行网站登录不上去,官网cms,为什么网站显示乱码,短租网站那家做的好目录
一#xff0c;前言二#xff0c;准备 type重点看 三#xff0c;慢查询原因和解决 1#xff0c;sql未加索引2#xff0c;索引失效3#xff0c;limit深分页问题 #xff08;1#xff09;limit深分页为什么会慢#xff08;2#xff09;深分页优化 4#xff0c;in…目录
一前言二准备 type重点看 三慢查询原因和解决 1sql未加索引2索引失效3limit深分页问题 1limit深分页为什么会慢2深分页优化 4in元素过多5 join 或者子查询过多6order by文件排序 1为什么查询效率低2优化order by 7拿不到锁8数据库出现脏页 (1)什么是脏页(2)一条更新语句是如何执行的(3)为什么会出现脏页呢(4)什么时候会刷脏页flush(5)为什么刷脏页会导致SQL变慢呢 9优化
一前言
在日常开发中我们往往会给表加各种索引来提高 MySQL 的检索效率。但我们有时会遇到明明给字段加了索引并没有走索引的Case。 进而导致 MySQL 产生慢查询。 严重场景下甚至出现主从延迟、数据库拖垮的极端事故
二准备
1建立user表并初始化
use usermanager;create table userinfo(uid int primary key auto_increment,username varchar(250) not null,loginname varchar(250) unique not null,password varchar(65) not null,sex varchar(2) default 男,age int default 0,address varchar(250) default ,qq varchar(250) default ,email varchar(250) default ,isadmin bit default 0,state int default 1,createtime datetime default now(),updatetime datetime default now()
) default charsetutf8mb4;insert into userinfo(username,loginname,password,isadmin)values(超级管理员,admin,admin,1);
insert into userinfo(username,loginname,password,isadmin)values(张三,zhangsan,123456,0);
2explain命令的使用 只要我们在 SQL 前加上 explain就可以分析出当前环境下 MySQL 的“查询方式”以及“索引选择”。 首先大致看下每个字段的含义
type重点看
type 列表示了 MySQL 关联的类型它代表了mysql是如何在表里找数据的。 下面按性能从高到低的顺序介绍type类型以下四种类型说明 “性能很好一般无需优化”
system表里就一条数据const一般是针对主键/唯一键的等值查询mysql可以把这类查询优化为一个常量表达式eq_ref一般出现在多表join时针对主键/唯一键的等值查询mysql知道只需要返回一条记录ref多表 join 时针对索引字段的查询 以下几种类型需要 “看具体情况决定是否要优化” fulltext关联使用了全文索引ref_or_null查询走了索引但是除此之外还要判断字段是不是null如果出现这种类型可以考虑这个字段是否有为空的必要index_merge使用了索引合并优化如果高频出现可以考虑是不是索引设计有问题。unique_subqueryin 子句中的子查询如果只访问主键/唯一键可能会出现这种 type并不常见index_subquery同样是 in 里的子查询访问了索引列并不常见range对索引字段的范围扫描一般出现在带有比较的查询语句中一些in和or的查询也会导致这种类型的扫描 以下两种类型需要 “优化 避免出现” index按索引进行全表扫描如果查询不是覆盖索引的可能会产生很大量的随机IOall全表扫描
三慢查询原因和解决
1sql未加索引
explain select * from userinfo where username“张三”; 优化根据业务场景合理的建立相应的索引。
2索引失效 具体索引使用和索引失效
3limit深分页问题
1limit深分页为什么会慢 select id,name,balance from account where create_time ‘2020-09-19’ limit 100000,10; 这个SQL的执行流程
通过普通二级索引树idx_create_time过滤create_time条件找到满足条件的主键id。通过主键id回到id主键索引树找到满足记录的行然后取出需要展示的列回表过程扫描满足条件的100000行然后扔掉前100000行返回 原因 limit深分页导致SQL变慢原因有两个
limit语句会先扫描offsetn行然后再丢弃掉前offset行返回后n行数据。也就是说limit 100000,10就会扫描100010行而limit 0,10只扫描10行。limit 100000,10 扫描更多的行数也意味着回表更多的次数
2深分页优化
标签记录法 就是标记一下上次查询到哪一条了下次再来查的时候从该条开始往下扫描 select id,name,balance FROM account where id 100000 limit 10; 这样的话后面无论翻多少页性能都会不错的因为命中了id索引。但是这种方式有局限性需要一种类似连续自增的字段。 延迟关联法 select acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time 2020-09-19 limit 100000, 10) AS acct2 on acct1.id acct2.id; 优化思路就是先通过idx_create_time二级索引树查询到满足条件的主键ID再与原表通过主键ID内连接这样后面直接走了主键索引了同时也减少了回表。 4in元素过多
如果使用了in即使后面的条件加了索引还是要注意in后面的元素不要过多哈。in元素一般建议不要超过500个如果超过了建议分组每次500一组进行哈。
反例 select user_id,name from user where user_id in (1,2,3...1000000);
如果我们对in的条件不做任何限制的话该查询语句一次性可能会查询出非常多的数据很容易导致接口超时。尤其有时候我们是用的子查询。如下这种子查询
select * from user where user_id in (select author_id from artilce where type 1);5 join 或者子查询过多
一般来说不建议使用子查询可以把子查询改成join来优化。而数据库有个规范约定就是尽量不要有超过3个以上的表连接。
join过多的问题 一方面过多的表连接会大大增加SQL复杂度。另外一方面如果可以使用被驱动表的索引那还好并且使用小表来做驱动表查询效率更佳。如果被驱动表没有可用的索引join是在join_buffer内存做的如果匹配的数据量比较小或者join_buffer设置的比较大速度也不会太慢。但是如果join的数据量比较大时mysql会采用在硬盘上创建临时表的方式进行多张表的关联匹配这种显然效率就极低本来磁盘的 IO 就不快还要关联。 一般情况下如果业务需要的话关联2~3个表是可以接受的但是关联的字段需要加索引哈。如果需要关联更多的表建议从代码层面进行拆分在业务层先查询一张表的数据然后以关联字段作为条件查询关联表形成map然后在业务层进行数据的拼装。
6order by文件排序
1为什么查询效率低 order by的文件排序分为全字段排序和rowid排序。它是拿max_length_for_sort_data和结果行数据长度对比如果结果行数据长度超过max_length_for_sort_data这个值就会走rowid排序相反则走全字段排序。
2优化order by
order by使用文件排序效率会低一点。我们怎么优化呢 因为数据是无序的所以就需要排序。如果数据本身是有序的那就不会再用到文件排序啦。而索引数据本身是有序的我们通过建立索引来优化order by语句。 我们还可以通过调整max_length_for_sort_data、sort_buffer_size等参数优化 7拿不到锁
有时候我们查询一条很简单的SQL但是却等待很长的时间不见结果返回。一般这种时候就是表被锁住了或者要查询的某一行或者几行被锁住了。我们只能慢慢等待锁被释放。
这时候我们可以用show processlist命令看看当前语句处于什么状态
8数据库出现脏页
(1)什么是脏页
当内存数据页跟磁盘数据页内容不一致的时候我们称这个内存页为“脏页”。内存数据写入到磁盘后内存和磁盘上的数据页的内容就一致了称为“干净页”。一般有更新SQL才可能会导致脏页我们回忆一下一条更新语句是如何执行的
(2)一条更新语句是如何执行的
以下的这个更新SQL如何执行的呢 update t set cc1 where id666 对于这条更新SQL执行器会先找引擎取id666这一行。如果这行所在的数据页本来就在内存中的话就直接返回给执行器。如果不在内存就去磁盘读入内存再返回。 执行器拿到引擎给的行数据后给这一行C的值加一得到新的一行数据再调用引擎接口写入这行新数据。 引擎将这行新数据更新到内存中同时将这个更新操作记录到redo log里面但是此时redo log 是处于prepare状态的哈。 执行器生成这个操作的binlog并把binlog写入磁盘。 执行器调用引擎的提交事务接口引擎把刚刚写入的redo log改成提交commit状态更新完成。 InnoDB 在处理更新语句的时候只做了写日志这一个磁盘操作。这个日志叫作redo log重做日志。平时更新SQL执行得很快其实是因为它只是在写内存和redo log日志等到空闲的时候才把redo log日志里的数据同步到磁盘中。
(3)为什么会出现脏页呢
更新SQL只是在写内存和redo log日志等到空闲的时候才把redo log日志里的数据同步到磁盘中。这时内存数据页跟磁盘数据页内容不一致,我们称之为脏页。
(4)什么时候会刷脏页flush
InnoDB存储引擎的redo log大小是固定且是环型写入的如下图图片来源于MySQL 实战 45 讲
那什么时候会刷脏页有几种场景 redo log写满了要刷脏页。这种情况要尽量避免的。因为出现这种情况时整个系统就不能再接受更新啦即所有的更新都必须堵住。 内存不够了需要新的内存页就要淘汰一些数据页这时候会刷脏页 InnoDB 用缓冲池buffer pool管理内存,而当要读入的数据页没有在内存的时候就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉如果要淘汰的是一个干净页就直接释放出来复用但如果是脏页呢就必须将脏页先刷到磁盘变成干净页后才能复用。 MySQL 认为系统空闲的时候也会刷一些脏页MySQL 正常关闭时会把内存的脏页都 flush 到磁盘上
(5)为什么刷脏页会导致SQL变慢呢
redo log写满了要刷脏页这时候会导致系统所有的更新堵住写性能都跌为0了肯定慢呀。一般要杜绝出现这个情况。 一个查询要淘汰的脏页个数太多一样会导致查询的响应时间明显变长
9优化
使用explain查看SQL语句的执行计划如果有告警信息,查看告警信息的show warnings查看SQL语句涉及的表结构和索引信息根据执行计划对SQL语句需要优化的地方进行优化根据需要优化的情况执行表结构的修改,索引的添加 ,SQL语句的改写等操作再次使用explain查看优化后的执行时间和执行计划根据优化效果选择继续优化,还是优化成功
