注册    登录
关注
Py学习  »  DATABASE

MySQL之慢 SQL 定位、性能优化实践

IT大咖说 • 10 月前 • 85 次点击  

在面试中被问到MySQL慢查询的概率还是非常高的。

说你没有经历过就是理由吗？显然不是。

一般来说一句SQL语句执行超过5s就能够算是慢SQL，需要进行优化了。

◆ 为何要对慢SQL进行治理

每一个SQL都需要消耗一定的I/O资源，SQL执行的快慢直接决定了资源被占用时间的长短。假设业务要求每秒需要完成100条SQL的执行，而其中10条SQL执行时间过长，从而导致每秒只能完成90条SQL，所有新的SQL将进入排队等待，直接影响业务，然后用户就各种投诉来了。

◆ 治理的优先级

  • master数据库->slave数据库 采用读写分离架构，读在从库slave上执行，写在主库master上执行。但由于从库的数据都是在主库复制过去的，主库如果等待较多的情况，会加大从库的复制延时

  • 执行SQL次数多的优先治理

  • 某张表被高并发集中访问的优先治理


◆ MySQL执行原理

为了更好的优化慢SQL，我们来简单了解下MySQL的执行原理

绿色部分为SQL实际执行部分，主要分为两步：

  • 解析：词法解析->语法解析->逻辑计划->查询优化->物理执行计划，过程中会检查缓存是否可用，如果没有可用缓存则进入下一步mysql_execute_command执行

  • 执行：检查用户、表权限->表加上共享读锁->取数据到query_cache->取消共享读锁


◆ 如何发现慢查询SQL

-- 修改慢查询时间,只能当前会话有效;
set long_query_time=1; ­
-- 启用慢查询 ,加上global，不然会报错的;



    
set global slow_query_log='ON';­­ 
-- 是否开启慢查询;
show variables like "%slow%";­­ 
-- 查询慢查询SQL状况;
show status like "%slow%"; ­­ 
-- 慢查询时间（默认情况下MySQL认位10秒以上才是慢查询）
show variables like "long_query_time"; ­­

除了sql的方式，我们也可以在配置文件（my.ini）中修改，加入配置时必须要在[mysqld]后面加入

-- 开启日志;
slow_query_log = on
-- 记录日志的log文件（注意:window上必须写绝对路径）
slow_query_log_file = D:/mysql5.5.16/data/showslow.log
-- 最长查询的秒数;
long_query_time = 2
-- 表示记录没有使用索引的查询
log­queries­not­using­indexes ­­

特别注意：开启慢查询会带来CPU损耗与日志记录的IO开销，所以建议间断性的打开慢查询日志来观察MySQL运行状态

◆ 慢查询分析示例

假设我们有一条SQL

SELECT * FROM `emp` where ename like '%mQspyv%';

执行时间为1.163s，而我们设置的慢查询时间为1s，这时我们可以打开慢查询日志进行日志分析：

## Time: 150530 15:30:58 ­­ -- 该查询发生在2015­5­30 15:30:58
## User@Host: root[root] @ localhost [127.0.0.1]


    
 ­­ --是谁,在什么主机上发生的查询
## Query_time: 1.134065 Lock_time: 0.000000 Rows_sent: 8 Rows_examined: 4000000 Query_time: --查询总共用了多少时间,Lock_time: 在查询时锁定表的时间,Rows_sent: 返回多少rows数据,Rows_examined: 表扫描了400W行数据才得到的结果;

如果我们的慢SQL很多，人工分析肯定分析不过来，这时候我们就需要借助一些分析工具，MySQL自带了一个慢查询分析工具mysqldumpslow，以下是常见使用示例

mysqldumpslow ­s c ­t 10 /var/run/mysqld/mysqld­slow.log # 取出使用最多的10条慢查询
mysqldumpslow ­s t ­t 3 /var/run/mysqld/mysqld­slow.log # 取出查询时间最慢的3条慢查询
mysqldumpslow ­s t ­t 10 ­g “left join” /database/mysql/slow­log #得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow ­s r ­t 10 ­g 'left join' /var/run/mysqld/mysqldslow.log # 按照扫描行数最多的

◆ SQL语句常见优化

只要简单了解过MySQL内部优化机制，就很容易写出高性能的SQL

不使用子查询

SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name='hechunyang');

在MySQL5.5版本中，内部执行计划器是先查外表再匹配内表，如果外表数据量很大，查询速度会非常慢

再MySQL5.6中，有对内查询做了优化，优化后SQL如下

SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;

但也仅针对select语句有效，update、delete子查询无效，所以生成环境不建议使用子查询

避免函数索引

SELECT * FROM t WHERE YEAR(d) >= 2016;

即使d字段有索引，也会全盘扫描，应该优化为：

SELECT * FROM t WHERE d >= '2016-01-01';

使用IN替换OR

SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;

非聚簇索引走了3次，使用IN之后只走一次：

SELECT * FROM t WHERE LOC_IN IN (10,20,30);

LIKE双百分号无法使用到索引

SELECT * FROM t WHERE name LIKE '%de%';

应优化为右模糊

SELECT * FROM t WHERE name LIKE 'de%';

增加LIMIT M,N 限制读取的条数

避免数据类型不一致

SELECT * FROM t WHERE id = '19';

应优化为

SELECT * FROM t WHERE id = 19;

分组统计时可以禁止排序

SELECT goods_id,count(*) FROM t GROUP BY goods_id;

默认情况下MySQL会对所有GROUP BY co1，col2 …的字段进行排序，我们可以对其使用

ORDER BY NULL

禁止排序，避免排序消耗资源

SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;

去除不必要的ORDER BY语句

◆ Mysql 慢查询优化实践

◆ 目标:

提高mysql运行效率,增加并发,提高响应速度

◆ 方案:

通过阿里云给的慢查询日志excel,对耗时长,开销大的sql语句进行优化,提升访问速度服务器运行效率

◆ 实践:

分析 阿里云给的数据库单日报表有以下字段

  • Create Time DBName

  • MySQL Total Execution Counts

  • MySQL Total Execution Times

  • Max Execution Time

  • Max Lock Time

  • Parse Total Row Counts

  • Parse Max Row Count

  • Return Total Row Counts

  • Return Max Row Count

  • SQL Text

  • hard ware- latencies

分别是

  • 创建时间

  • 数据库名

  • mysql总执行数目

  • muysql总执行耗时

  • 最大执行耗时

  • 最大锁耗时

  • 解析总行数统计

  • 解析最大行数

  • 返回总计行数

  • 返回最大行数

  • sql语句

  • 硬件延迟

根据阿里云提供的慢查询记录,本次采用的优化策略如下:

查询次数超过100次/日的高频需求,按照最大查询/总查询用时最大,依次优化取得的优化收益最高.

◆ 第一条语句:

执行次数: 1114 最大耗时: 7 解析最大行数: 348325 返回最大行数 4 #执行次数: 1114 最大耗时: 7 解析最大行数: 348325 返回最大行数 4

csharp复制代码select id from appname_m_members where yiku_id = :1

可以看出,这个简单的sql不应该有这么大的解析行数,甚至最高要七秒钟.
初步判断没有在yiku_id这个字段加索引的可能性最大.现在我们需要寻求各种办法来验证下我们的猜测

◆ 分析

explain select id from appname_m_members where yiku_id = 1;


可以看到的确是没有给yiku_id增加索引.

索引的特点
对于查询操作能迅速缩小查询范围,减少row的数量,指数级提高查询速度点
对于写操作,因为需要维护索引的变更,有一定开销.如果遇到大量并发写入,会有负面影响.
在这个表用来记录我们微信用户和应用id的关系,所以读的操作较之写操作更多,所以能够增加索引.

markdown复制代码#增加索引

ALTER TABLE `appname_m_members` 
ADD INDEX `yiku_id` (`yiku_id`) ;

尝试增加索引之后,再次分析语句的执行

◆ 结果:

匹配范围 rows 从32w 降低到1

可以看到type从all的全表扫描变成ref的单个行的索引访问,rows从全表32w降为1,说明添加索引对这条语句产生了巨大效果.

◆ 第二条语句:

执行次数: 482 最大耗时: 15 解析最大行数: 764383 返回最大行数: 482

javascript复制代码#执行次数: 482 最大耗时: 15 解析最大行数: 764383 返回最大行数: 482
select fullname as username , linkphone as userphone , 
`userimage` , `nickname` , `hospitalname` , `partmentname` , 
`doctortitle` , `iscertification` , `fullname` 
from `users` 
where `useruuid` = '597_f66e1cb79341cedf6f24aaf01fde8611' limit 1;

◆ 分析:

◆ 对其增加索引:

markdown复制代码#增加索引
ALTER TABLE `users` 
ADD INDEX `useruuid` (`useruuid`);


直接将扫描范围(rows)从72w降到了1,提升明显

◆ 结果:

匹配范围 rows 从72w 降低到1

◆ 第三条语句:

执行次数: 820 最大耗时: 10 解析最大行数: 167214 返回最大行数 1

csharp复制代码#执行次数: 820 最大耗时: 10 解析最大行数: 167214  返回最大行数 1
select count ( postingid ) as postnum from mediposting 
where isaudit != :1
and isgoodcase = :2
and postsection = :3

◆ 分析:

改变sql语句的顺序,按照最左原则修改如下

csharp复制代码select count(postingid) as postnum from mediposting 
where postsection = 1
and isgoodcase = 1
and isaudit != 1

◆ 结果:

主要使用的是 postsection 作为索引来统计总数,这部分无需优化.

shell复制代码##### 第四条语句:
执行次数: 482 最大耗时: 15 解析最大行数: 764383 返回最大行数: 482
##执行次数: 410 最大耗时: 10 解析最大行数:348325   返回最大行数 1
........

◆ 结果: 语句过长(2017个字符),嵌套了逻辑,暂不优化

◆ 第五条语句:

执行次数: 659 最大耗时: 6 解析最大行数:215115 返回最大行数 659




    
perl复制代码## 执行次数: 659 最大耗时: 6 解析最大行数:215115   返回最大行数 659
select `medigooddoc`.`docid` , `medigooddoc`.`docname` ,
 `medigooddoc`.`doctitle` , `medigooddoc`.`docimgurl` ,
  `medigooddoc`.`docdep` , `medigooddoc`.`dochospital` ,
   ( initalscore+effectevaladd ) as `effectval` 
   from `medigooddoc` 
   where ( ( initalscore+effectevaladd ) > 80 ) 
   order by rand ( ) limit 1 ;

◆ 分析:

rand()函数放在order by后面会被执行多次,优化方式: 求出随机id后,取得对应记录

javascript复制代码select `medigooddoc`.`docid` , `medigooddoc`.`docname` ,
 `medigooddoc`.`doctitle` , `medigooddoc`.`docimgurl` ,
 `medigooddoc`.`docdep` , `medigooddoc`.`dochospital` ,
 ( initalscore+effectevaladd ) as `effectval` 
 from `medigooddoc` 
 where (initalscore+effectevaladd)  > 80
 and docid > ( 
     RAND() * (
         (SELECT MAX(docid) FROM `medigooddoc`) 
         -
         (SELECT MIN(docid) FROM `medigooddoc`)
    )
    +
    (SELECT MIN(docid) FROM `medigooddoc`) 
 )
 order by `docid` limit 1;

优化前语句:

可以看到扫描范围很大(rows) 120 770行.

可以看到

  • 查询范围最小(rows) 1

  • 获取最大值/最小值是直接从mysql查询优化器返回数据(extra).mysql文档中有以下解释:

The query contained only aggregate functions (MIN(), MAX()) that were all resolved using an index, or COUNT(*) for MyISAM, and no GROUP BY clause. The optimizer determined that only one row should be returned.

测试执行效率:

  • 执行10次 a: 2 941 ms b: 168 ms

  • 执行50次 a: 14 441 ms b: 828 ms

  • 执行100次a: 29 126 ms b: 1 645 ms

可以看到每百次运行时间已经从30s缩短到不到2秒,大大提高查询mysql响应速度. 但是还有个问题,总共100 000的id,原来的语句查询出的结果比较平衡,有过万也有几千,但是用这个语句后,总是出现小于一万的id,结果在我们预期之外.

修正概率偏差

◆ 方案1:

增加一次对数据库消耗不大的表查询

python复制代码# php
$round = select max(docid) as max,min(docid) as min from medigooddoc;
$rand = rand($round['min'],$round['max']);
perl复制代码# sql
select `medigooddoc`.`docid` , `medigooddoc`.`docname` ,
 `medigooddoc`.`doctitle` , `medigooddoc`.`docimgurl` ,
 `medigooddoc`.`docdep` , `medigooddoc`.`dochospital` ,
 ( initalscore+effectevaladd ) as `effectval` 
 from `medigooddoc` 
 where (initalscore+effectevaladd)  > 80
 and docid > $rand
 order by `docid` limit 1;

这样的问题是:会多产生一个sql交互,数据库

◆ 方案2:

使用内连接 join 优化

perl复制代码#可用一
select `docid` ,`docname`,
 `doctitle` , `docimgurl` ,
 `docdep` , `dochospital` ,
 ( initalscore+effectevaladd ) as `effectval` 
 from `medigooddoc` as t1 
 join (
     select rand() * (select max(docid) from `medigooddoc`) 
     as rand
 ) as t2 
 where (t1.initalscore+t1.effectevaladd)  > 80
 and `t1`.`docid` >= t2.rand
 order by `docid` limit 1;

但是这样有一个问题:并不是完全平均落到每条记录上,因为记录并不是连续的

修正概率 rand * 数量范围,这样概率平均到整张表存在的记录中.

javascript复制代码select `docid` ,`docname`,
 `doctitle` , `docimgurl` ,
 `docdep` , `dochospital` ,
 ( initalscore+effectevaladd ) as `effectval` 
 from `medigooddoc` as t1 
 join (
     select rand() * 
     (
         (select max(docid) from `medigooddoc`)
         -
         (select min(docid) from `medigooddoc`)
     )
     +
     (select min(docid) from `medigooddoc`)  
     as rand
 ) as t2 
 where (t1.initalscore+t1.effectevaladd)  > 80



    
 and `t1`.`docid` >= t2.rand
 order by `docid` limit 1;

综合来说,因为方案1 产生了更多的数据库交互,因为我们的数据库是另一台服务器,网络连接开销是比较大的,额外的查询也会在高并发的时刻对数据库产生更大压力.

而方案2采用内连接的方式,仅需要一次数据库交互就能完成,最大最小值也是直接由mysql查询器返回,减少了种种数据库性能开销.故采用为最佳方案..

◆ 结果:

使用mysql保存的表结构信息替代了order rand()的低效率查询.

◆ 深入理解:

◆ 第六条语句:

执行次数: 729 最大耗时: 4秒 解析最大行数:130898 返回最大行数 2

go复制代码select `medigooddoc`.`docid` , `medigooddoc`.`yikuid` 
from `medigooddoc` 
where ( yikuid = 597725 or yikuid = -597725 );

◆ 分析:

◆ 优化方案:

字段yikuid加索引

go复制代码ALTER TABLE `medigooddoc`
ADD INDEX `YiKuID` (`YiKuID`);

再次执行explain分析

◆ 结果:

匹配范围 rows 从8.3w 降低到1

◆ 第七条语句

执行次数: 474 最大耗时: 5秒 解析最大行数:261797 返回最大行数 1

select `medigooddoc`.`docid` , `medigooddoc`.`docname` ,
 `medigooddoc`.`doctitle` , `medigooddoc`.`docimgurl` 
 from `medigooddoc` order by rand ( ) limit 1;

◆ 分析

◆ 方案

将获取一条随机记录 由order by rand() limit 1 改为 内连接方式

select `docid`, `docname`,
  `doctitle` , `docimgurl` 
 from `medigooddoc` as  t1 
 inner join 
 (
     select rand()  *
     (
         (select MAX(docid) from `medigooddoc`)
         -
         (select MIN(docid) from `medigooddoc`)
     )
     +
     (select MIN(docid) from `medigooddoc`)
     as rand
 ) as t2 
 on t1.docid >= t2.rand
 order by docid limit


    
 1;

再次执行explain分析

◆ 结果

用mysql存储的表信息替代了效率低下的order by rand()

◆ 第八条语句

执行次数: 136 最大耗时: 7秒 解析最大行数:301880 返回最大行数 1

go复制代码select `searchrecords`.`searchid` , `searchrecords`.`searchnum` 
from `searchrecords` 
where ( searchtype = 0 ) and ( userid = 14 ) 
and ( searchmsg = '碳酸钙D3' );

◆ 方案

索引的目的是为了缩小查询范围,通过文字内容的前三个字区分,通过userid进行区分,可以得到范围更精确的语句执行

sql复制代码ALTER TABLE searchrecords ADD INDEX searchmsg (searchmsg(5));
ALTER TABLE searchrecords ADD INDEX userid (userid);

通过文本前5个字建立索引来区分范围后,范围缩小到28个记录

再通过用户ID建立索引,进一步缩小范围,仅需要查找1条记录

分析索引对写入的影响 表主要用来记录用户搜索的高频词,主要的写操作时更新统计字段,这两个新增索引的字段并不会频繁更新,故索引开销不大.

◆ 结果

匹配范围从 29w 缩小到 1

◆ 第九条语句

less复制代码select `projects`.`id` , `projects`.`guid` , 
`projects`.`getittime` , `projects`.`keywords` ,
 `projects`.`barcode` as `num` , `projects`.`goodcasedep` ,
  `projects`.`bingshi` , `pictures`.* 
  from `projects` 
  inner join `pictures` on projects.guid = pictures.projectid 
  and pictures.filetype = :1 
  where ( islock != :2 ) and ( isgoodcase = :3 ) 
  and ( ( goodcasedep like :4 or goodcasedep like :5 
  or goodcasedep like :6 or goodcasedep like :7 
  or goodcasedep like :8 or goodcasedep like :9 
  or goodcasedep like :10 or goodcasedep like :11 
  or goodcasedep like :12 or goodcasedep like :13 
  or goodcasedep like :14 or goodcasedep like :15 
  or goodcasedep like :16 or goodcasedep like :17 
  or goodcasedep like :18 or goodcasedep like :19 
  or goodcasedep like


    
 :20 or goodcasedep like :21 
  or goodcasedep like :22 or goodcasedep like :23 
  or goodcasedep like :24 or goodcasedep like :25 
  or goodcasedep like :26 or goodcasedep like :27 
  or goodcasedep like :28 or goodcasedep like :29 
  or goodcasedep like :30 or goodcasedep like :31 
  or goodcasedep like :32 or goodcasedep like :33 
  or goodcasedep like :34 or goodcasedep like :35 
  or goodcasedep like :36 or goodcasedep like :37 
  or goodcasedep like :38 or goodcasedep like :39 
  or goodcasedep like :40 or goodcasedep like :41 ) ) 
  order by rand ( ) limit :42

◆ 结果:

暂不修改:超过字节限制

◆ 第十条语句

执行次数: 145 最大耗时: 2秒 解析最大行数:130898 返回最大行数 1




    
go复制代码select `medigooddoc`.`isfollow` , `medigooddoc`.`isconsult` ,
 `medigooddoc`.`isphone` , `medigooddoc`.`isprivate` 
 from `medigooddoc` where ( yikuid =  694 );

◆ 分析:

◆ 方案

增加索引

sql复制代码ALTER TABLE `medigooddoc` ADD INDEX YiKuID(`YiKuID`);

再次执行explain分析

◆ 结果

匹配范围从12w缩小到1

◆ 第十一条

执行次数: 148 最大耗时: 3秒 解析最大行数:74616 返回最大行数 30

go复制代码select `magazinearticle`.`articleid` , 
`magazinearticle`.`articletitle` , 
`magazinearticle`.`article_publishtime` ,
 `magazinearticle`.`articlepicpath` ,
  `magazinearticle`.`articleurl` ,
   `magazinearticle`.`articlenum` ,
    `magazinearticle`.`perid` ,
     `magazinearticle`.`article_originallink` ,
      `magazinearticle`.`islink` from `magazinearticle` 



    
      where ( logicdel = 0 ) and ( perid != 60 ) 
      order by `article_publishtime` desc limit 1,30;

◆ 分析:

◆ 方案:

由于是读多写少的文章表,增加索引适用这类场景,提高查询响应速度.

sql复制代码ALTER TABLE `magazinearticle` ADD INDEX 
article_publishtime(`article_publishtime`);

再次执行explain分析

◆ 结果:

匹配范围 rows 从2w缩小到59

◆ 深入理解:

explain type的不同种类

类型

含义

类型

含义

system

表只有一行

const

表最多只有一行匹配，通用用于主键或者唯一索引比较时

eq_ref

每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引

ref

如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键

fulltext

全文搜索

ref_or_null

与ref类似，但包括NULL

index_merge

表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。
这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话)

unique_subquery

在in子查询中，就是value in (select...)把形如“select unique_key_column”的子查询替换。
PS：所以不一定in子句中使用子查询就是低效的！

index_subquery

同上，但把形如”select non_unique_key_column“的子查询替换

range

常数值的范围

index

a.当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index)
b.以索引顺序从索引中查找数据行的全表扫描（无 Using Index）；
c.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思；
d.如单独出现，则是用读索引来代替读行，但不用于查找

all

全表扫描

◆ 第十二条

执行次数: 135 最大耗时: 3秒 解析最大行数:78395 返回最大行数 0

sql复制代码select distinct userid from weekhosnominate 
where userid = 351211 and datatype = 4

◆ 分析

◆ 方案

sql复制代码ALTER TABLE `weekhosnominate`


    
 ADD INDEX UserID(`UserID`);

再次执行explain分析

◆ 结果

匹配范围 rows 从1w缩小到288

◆ 第十三条

执行次数: 110 最大耗时: 2秒 解析最大行数:87693 返回最大行数 1

ini复制代码select `inspectioninfo`.`itemmsg` from `inspectioninfo` 
where ( itemid in ( 30 ,31 ) and itemtype = 0
and inspectionid = 109 ) limit 1 ;

◆ 分析

◆ 方案:

增加索引

sql复制代码ALTER TABLE `inspectioninfo` ADD INDEX 
InspectionID(`InspectionID`);

再次执行explain分析

◆ 结果

匹配范围 rows 从 5w 缩小到 13

◆ 第十四条语句

执行次数: 103 最大耗时: 2秒 解析最大行数:78395 返回最大行数 0

select `weekhosnominate`.`id` from `weekhosnominate` 
where ( userid = 351211 );

◆ 分析:

◆ 方案:

通过给字段 userid 建立索引来区分,缩小范围

sql复制代码ALTER TABLE `weekhosnominate` ADD INDEX UserID(UserID) ;

再次执行explain分析可以发现, 通过索引 userid 将范围由全表扫描的近万到索引指向的数十条记录.

◆ 结果:

匹配范围 rows 从 9k 缩小到 288

◆ 深入理解:

◆ mysql索引原理

索引目的
索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

索引原理
除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、

但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

磁盘IO与预读
前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。

那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。下图是计算机硬件延迟的对比图，供大家参考：

硬件处理延

◆ 总结

总的来说，我们知道曼查询的SQL后，优化方案可以做如下尝试：

  • SQL语句优化，尽量精简，去除非必要语句

  • 索引优化，让所有SQL都能够走索引

  • 如果是表的瓶颈问题，则分表，单表数据量维持在2000W（理论上）以内

  • 如果是单库瓶颈问题，则分库，读写分离

  • 如果是物理机器性能问题，则分多个数据库节点


来源：https://www.toutiao.com/article/7234018475412193829/?log_from=fe9bc67bd470a_1688518700843

“IT大咖说”欢迎广大技术人员投稿，投稿邮箱：aliang@itdks.com





来都来了，走啥走，留个言呗~




 IT大咖说  |  关于版权 

由“IT大咖说（ID：itdakashuo）”原创的文章，转载时请注明作者、出处及微信公众号。投稿、约稿、转载请加微信：ITDKS10（备注：投稿），茉莉小姐姐会及时与您联系！

感谢您对IT大咖说的热心支持！


相关推荐


推荐文章

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/157164
Reply
 
85 次点击  
登录后回复
关于   移动版   ·   三行代码   ·   今天看啥   ·   Code   ·   link之家   ·   卧龙搜索   ·   藏经阁   ·   小百科
Py学习 - 专注于Python技术发展的社区(原Django社区)
沪ICP备11025650号