登陆

极彩娱乐下载地址-阿里P8架构师谈:MySQL数据库的索引原理、与慢SQL优化的5大原则

admin 2019-10-31 257人围观 ,发现0个评论

MySQL凭借着超卓的功用、低价的本钱、丰厚的资源,现已成为绝大多数互联网公司的首选联系型数据库。尽管功用超卓,但所谓“好马配好鞍”,怎么能够更好的运用它,现已成为开发工程师的必修课,咱们常常会从职位描绘上看到比方“通晓MySQL”、“SQL句子优化”、“了解数据库原理”等要求。

咱们知道一般的运用体系,读写份额在10:1左右,而且刺进操作和一般的更新操作很少呈现功用问题,遇到最多的,也是最简略出问题的,仍是一些杂乱的查询操作,所以查询句子的优化明显是重中之重。

本文旨在以开发工程师的视点来解说数据库索引的原理和怎么优化慢查询。

MySQL索引原理

1.索引意图

索引的意图在于进步查询功率,能够类比字典,假如要查“mysql”这个单词,咱们必定需求定位到m字母,然后从下往下找到y字母,再找到剩余的sql。假如没有索引,那么你或许需求把一切单词看一遍才干找到你想要的,假如我想找到m最初的单词呢?或许ze最初的单词呢?是不是觉得假如没有索引,这个工作底子无法完结?

2.索引原理

除了词典,日子中随处可见索引的比方,如火车站的车次表、图书的目录等。它们的原理都是相同的,经过不断的缩小想要取得数据的规模来筛选出终究想要的成果,一起把随机的事情变成次序的事情,也便是咱们总是经过同一种查找方法来确认数据。

数据库也是相同,但明显要杂乱许多,由于不只面临着等值查询,还有规模查询(>、<、between、in)、含糊查询(like)、并集查询(or)等等。数据库应该挑选怎么样的方法来应对一切的问题呢?咱们回想字典的比方,能不能把数据分红段,然后分段查询呢?最简略的假如1000条数据,1到100分红榜首段,101到200分红第二段,201到300分红第三段……这样查第250条数据,只需找第三段就能够了,一会儿去除了90%的无效数据。但假如是1千万的记载呢,分红几段比较好?稍有算法根底的同学会想到查找树,其均匀杂乱度是lgN,具有不错的查询功用。但这儿咱们疏忽了一个要害的问题,杂乱度模型是依据每次相同的操作本钱来考虑的,数据库完成比较杂乱,数据保存在磁盘上,而为了进步功用,每次又能够把部分数据读入内存来核算,由于咱们知道拜访磁盘的本钱大概是拜访内存的十万倍左右,所以简略的查找树难以满意杂乱的运用场景。

3.磁盘IO与预读

前面提到了拜访磁盘,那么这儿先简略介绍一下磁盘IO和预读,磁盘读取数据靠的是机械运动,每次读取数据花费的时刻能够分为寻道时刻、旋转推迟、传输时刻三个部分,寻道时刻指的是磁臂移动到指定磁道所需求的时刻,干流磁盘一般在5ms以下;旋转推迟便是咱们常常传闻的磁盘转速,比方一个磁盘7200转,标明每分钟能转7200次,也便是说1秒钟能转120次,旋转推迟便是1/120/2 = 4.17ms;传输时刻指的是从磁盘读出或将数据写入磁盘的时刻,一般在零点几毫秒,相关于前两个时刻能够疏忽不计。那么拜访一次磁盘的时刻,即一次磁盘IO的时刻约等于5+4.17 = 9ms左右,听起来还挺不错的,但要知道一台500 -MIPS的机器每秒能够履行5亿条指令,由于指令依托的是电的性质,换句话说履行一次IO的时刻能够履行40万条指令,数据库动辄十万百万甚至千万级数据,每次9毫秒的时刻,明显是个灾祸。下图是核算机硬件推迟的比照图,供咱们参阅:

考虑到磁盘IO是十分昂扬的操作,核算机操作体系做了一些优化,当一次IO时,不但把当时磁盘地址的数据,而是把相邻的数据也都读取到内存缓冲区内,由于部分预读性原理告知咱们,当核算机拜访一个地址的数据的时分,与其相邻的数据也会很快被拜访到。每一次IO读取的数据咱们称之为一页(page)。具体一页有多大数据跟操作体系有关,一般为4k或8k,也便是咱们读取一页内的数据时分,实际上才发作了一次IO,这个理论关于索引的数据结构设计十分有协助。

4.索引的数据结构

前面讲了日子中索引的比方,索引的根本原理,数据库的杂乱性,又讲了操作体系的相关常识,意图便是让咱们了解,任何一种数据结构都不是随便发作的,必定会有它的布景和运用场景,咱们现在总结一下,咱们需求这种数据结构能玄君七章秘经够做些什么,其实很简略,那便是:每次查找数据时把磁盘IO次数控制在一个很小的数量级,最好是常数数量级。那么咱们就想到假如一个高度可控的多路查找树是否能满意需求呢?就这样,b+树应运而生。

5.详解b+树

如上图,是一颗b+树,关于b+树的界说能够拜见B+树,这儿只说一些要点,浅蓝色的块咱们称之为一个磁盘块,能够看到每个磁盘块包括几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包括数据项17和35,包括指针P1、P2、P3,P1标明小于17的磁盘块,P2标明在17和35之间的磁盘块,P3标明大于35的磁盘块。实在的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储实在的数据,只存储指引查找方向的数据项,如17、35并不实在存在于数据表中。

6.b+树的查找进程

如图所示,假如要查找数据项29,那么首要会把磁盘块1由磁盘加载到内存,此刻发作一次IO,在内存顶用二分查找确认29在17和35之间,确认磁盘块1的P2指针,内存时刻由于十分短(比较磁盘的IO)能够疏忽不计,经过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发作第2次IO,29在26和30之间,确认磁盘块3的P2指针,经过指针加载磁盘块8到内存,发作第三次IO,一起内存中做二分查找找到29,完毕查询,总计三次IO。实在的状况是,3层的b+树能够标明上百万的数据,假如上百万的数据查找只需求三次IO,功用进步将是巨大的,假如没有索引,每个数据项都要发作一次IO,那么一共需求百万次的IO,明显本钱十分十分高。

7.b+树性质

1.经过上面的剖析,咱们知道IO次数取决于b+数的高度h,假定当时数据表的数据为N,每个磁盘块的数据项的数量是m,则有h=㏒(m+1)N,当数据量N必定的状况下,m越大,h越小;而m = 磁盘块的巨细 / 数据项的巨细,磁盘块的巨细也便是一个数据页的巨细,是固定的,假如数据项占的空间越小,数据项的数量越多,树的高度越低。这便是为什么每个数据项,即索引字段要尽量的小,比方int占4字节,要比bigint8字节少一半。这也是为什么b+树要求把实在的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度下降,导致树增高。当数据项等于1时将会退化成线性表。

2.当b+树的数据项是复合的数据结构,比方(name,age,sex)的时分,b+数是按照从左到右的次序来树立查找树的,比方当(张三,20,F)这样的数据来检索的时分,b+树会优先比较name来确认下一步的所搜方向,假如name相同再依次比较age和sex,最终得到检索的数据;但当(20,F)这样极彩娱乐下载地址-阿里P8架构师谈:MySQL数据库的索引原理、与慢SQL优化的5大原则的没有name的数据来的时分,b+树就不知道下一步该查哪个节点,由于树立查找树的时分name便是榜首个比较因子,必需求先依据name来查找才干知道下一步去哪里查询。比方当(张三,F)这样的数据来检索时,b+树能够用name来指定查找方向,但下一个字段age的缺失,所以只能把姓名等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是十分重要的性质,即索引的最左匹配特性。

慢查询优化

关于MySQL索引原理是比较单调的东西,咱们只需求有一个理性的知道,并不需求了解得十分透彻和深化。咱们回头来看看一开端咱们说的慢查询,了解完索引原理之后,咱们是不是有什么主意呢?先总结一下索引的几大根本准则

建索引的几大准则

1.最左前缀匹配准则

十分重要的准则,mysql会一向向右匹配直到遇到规模查询(>、<、between、like)就中止匹配,比方a = 1 and b = 2 and c > 3 and d = 4 假如树立(a,b,c,d)次序的索引,d是用不到索引的,假如树立(a,b,d,c)的索引则都能够用到,a,b,d的次序能够恣意调整。

2.=和in能够乱序

比方a = 1 and b = 2 and c = 3 树立(a,b,c)索引能够恣意次序,mysql的查询优化器会帮你优化成索引能够辨认的方法

3.尽量挑选区分度高的列作为索引

区分度的公式是count(distinct col)/count(*),标明字段不重复的份额,份额越大咱们扫描的记载数越少,仅有键的区分度是1,而一些状况、性别字段或许在大数据面前区分度便是0,那或许有人会问,这个份额有什么经历值吗?运用场景不同,这个值也很难确认,一般需求join的字段咱们都要求是0.1以上,即均匀1条扫描10条记载

4.索引列不能参加核算,坚持列“洁净”

比方from_unixtime(create_time) = ’2014-05-29’就不能运用到索引,原因很简略,b+树中存的都是数据表中的字段值,但进行检索时,需求把一切元素都运用函数才干比较,明显本钱太大。所以句子应该写成create_time = unix_timestamp(’2014-05-29’);

5.尽量的扩展索引,不要新建索引。

比方表中现已有a的索引,现在要加(a,b)的索引,那么只需求修正本来的索引即可

查询优化神器 – explain指令

关于explain指令信任咱们并不生疏,具体用法和字段意义能够参阅官网explain-output,这儿需求着重rows是中心目标,绝大部分rows小的句子履行必定很快(有破例,下面会讲到)。所以优化句子根本上都是在优化rows。

慢查询优化根本进程

0.先运转看看是否真的很慢,留意设置SQL_NO_CACHE

1.where条件单表查,确认最小回来记载表。这句话的意思是把查询句子的where都运用到表中回来的记载数最小的表开端查起,单表每个字段别离查询,看哪个字段的区分度最高

2.explain检查履行计划,是否与1预期共同(从确认记载较少的表开端查询)

3.order by limit 方法的sql句子让排序的表优先查

4.了解事务方运用场景

5.加索引时参照建索引的几大准则

6.调查成果,不契合预期持续从0剖析

慢查询事例

下面几个比方具体解说了怎么剖析和优化慢查询

杂乱句子写法

许多状况下,咱们写SQL仅仅为了完成功用,这仅仅榜首步,不同的句子书写方法关于功率往往有实质的不同,这要求咱们对mysql的履行计划和索引准则有十分清楚的知道,请看下面的句子

select
distinct cert.emp_id
from
cm_log cl
inner join
(
select
emp.id as emp_id,
emp_cert.id as cert_id
from
employee emp
left join
emp_certificate emp_cert
on emp.id = emp_cert.emp_id
where
emp.is_deleted=0
) cert
on (
cl.ref_table='Employee'
and cl.ref_oid= cert.emp_id
)
or (
cl.ref_table='EmpCertificate'
and cl.ref_oid= cert.cert_id
)
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00';

0.先运转一下,53条记载 1.87秒,又没有用聚合句子,比较慢

53 rows in set (1.87 sec)

1.explain

+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where; Using temporary |
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 63727 | Using where; Using join buffer |
| 2 | DERIVED | emp | ALL | NULL | NULL | NULL | NULL | 13317 | Using where |
| 2 | DERIVED | emp_cert | ref | emp_certificate_empid | emp_certificate_empid | 4 | meituanorg.emp.id | 1 | Using index |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+

简述一下履行计划,首要mysql依据idx_last_upd_date索引扫描cm_log表取得379条记载;然后查表扫描了63727条记载,分为两部分,derived标明结构表,也便是不存在的表,能够简略了解成是一个句子构成的成果集,后边的数字标明句子的ID。derived2标明的是ID = 2的查询结构了虚拟表,而且回来了63727条记载。咱们再来看看ID = 2的句子终究做了写什么回来了这么很多的数据,首要全表扫描employee表13317条记载,然后依据索引emp_certificate_empid相关emp_certificate表,rows = 1标明,每个相关都只确认了一条记载,功率比较高。取得后,再和cm_log的379条记载依据规矩相关。从履行进程上能够看出回来了太多的数据,回来的数据绝大部分cm_log都用不到,由于cm_log只确认了379条记载。

怎么优化呢?能够看到咱们在运转完后仍是要和cm_log做join,那么咱们能不能之前和cm_log做join呢?仔细剖析句子不难发现,其根本思想是假如cm_log的ref_table是EmpCertificate就相关emp_certificate表,假如ref_table是Employee就相关employee表,咱们完全能够拆成两部分,并用union连接起来,留意这儿用union,而不必union all是由于原句子有“distinct”来得到仅有的记载,而union刚好具有了这种功用。假如原句子中没有distinct不需求去重,咱们就能够直接运用union all了,由于运用union需求去重的动作,会影响SQL功用。

优化过的句子如下

select
emp.id
from
cm_log cl
inner join
employee emp
on cl.ref_table = 'Employee'
and cl.ref_oid = emp.id
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00'
and emp.is_deleted = 0
union
select
emp.id
from
cm_log cl
inner join
emp_certificate ec
on cl.ref_table = 'EmpCertificate'
and cl.ref_oid = ec.id
inner join
employee emp
on emp.id = ec.emp_id
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00'
and emp.is_deleted = 0

4.不需求了解事务场景,只需求改造的句子和改造之前的句子坚持成果共同

5.现有索引能够满意,不需求建索引

6.用改造后的句子试验一下,只需求10ms 降低了近200倍!

+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where |
| 1 | PRIMARY | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | Using where |
| 2 | UNION | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where |
| 2 | UNION | ec | eq_ref | PRIMARY,emp_certificate_empid | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | |
| 2 | UNION | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.ec.emp_id | 1 | Using where |
| NULL | UNION RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
53 rows in set (0.01 sec)

清晰运用场景

举这个比方的意图在于推翻咱们对列的区分度的认知,一般上咱们以为区分度越高的列,越简略确认更少的记载,但在一些特别的状况下,这种理论是有局限性的

select
*
from
stage_poi sp
where
sp.accurate_result=1
and (
sp.sync_status=0
or sp.sync_status=2
or sp.sync_status=4
);

0.先看看运转多长时刻,951条数据6.22秒,真的很慢

951 rows in set (6.22 sec)

1.先explain,rows达到了361万,type = ALL标明是全表扫描

+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| 1 | SIMPLE | sp | ALL | NULL | NULL | NULL | NULL | 3613155 | Using where极彩娱乐下载地址-阿里P8架构师谈:MySQL数据库的索引原理、与慢SQL优化的5大原则 |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+

2.一切字段都运用查询回来记载数,由于是单表查询 0现已做过了951条

3.让explain的rows 尽量迫临951

看一下accurate_result = 1的记载数

select count(*),accurate_result f极彩娱乐下载地址-阿里P8架构师谈:MySQL数据库的索引原理、与慢SQL优化的5大原则rom stage_poi group by accurate_result;
+----------+-----------------+
| count(*) | accurate_result |
+----------+-----------------+
| 1023 | -1 |
| 2114655 | 0 |
| 972815 | 1 |
+----------+-----------------+

咱们看到accurate_result这个字段的区分度十分低,整个表只要-1,0,1三个值,加上索引也无法确认特别少数的数据

再看一下sync_status字段的状况

select count(*),sync_status from stage_poi group by sync_status;
+----------+-------------+
| count(*) | sync_status |
+----------+-------------+
| 3080 | 0 |
| 3085413 | 3 |
+----------+-------------+

相同的区分度也很低,依据理论,也不适合树立索引

问极彩娱乐下载地址-阿里P8架构师谈:MySQL数据库的索引原理、与慢SQL优化的5大原则题剖析到这,如同得出了这个表无法优化的定论,两个列的区分度都很低,即使加上索引也只能习惯这种状况,很难做普遍性的优化,比方当sync_status 0、3散布的很均匀,那么确认记载也是百万等级的

4.找事务方去交流,看看运用场景。事务方是这么来运用这个SQL句子的,每隔五分钟会扫描契合条件的数据,处理完结后把sync_status这个字段变成1,五分钟契合条件的记载数并不会太多,1000个左右。了解了事务方的运用场景后,优化这个SQL就变得简略了,由于事务方确保了数据的不平衡,假如加上索引能够过滤掉绝大部分不需求的数据

5.依据树立索引规矩,运用如下句子树立索引

alter table stage_poi add index idx_acc_status(accurate_result,sync_status);

6.调查预期成果,发现只需求200ms,快了30多倍。

952 rows in set (0.20 sec)

咱们再来回忆一下剖析问题的进程,单表查询相对来说比较好优化,大部分时分只需求把where条件里边的字段按照规矩加上索引就好,假如仅仅这种“无脑”优化的话,明显一些区分度十分低的列,不该该加索引的列也会被加上索引,这样会对刺进、更新功用形成严峻的影响,一起也有或许影响其它的查询句子。

所以咱们第4步调差SQL的运用场景十分要害,咱们只要知道这个事务场景,才干更好地辅佐咱们更好的剖析和优化查询句子。

慢查询的事例就剖析到这儿,以上仅仅一些比较典型的事例。

咱们在优化进程中遇到过超越1000行,涉及到16个表join的“废物SQL”,也遇到过线上线下数据库差异导致运用直接被慢查询拖死,也遇到过varchar等值比较没有写单引号,还遇到过笛卡尔积查询直接把从库搞死。再多的事例其实也仅仅一些经历的堆集,假如咱们了解查询优化器、索引的内部原理,那么剖析这些事例就变得特别简略了。


本文标题:阿里P8架构师谈:MySQL数据库的索引原理、与慢SQL优化的5大准则

转载请保存页面地址:https://youzhixueyuan.com/index-principle-and-slow-query-optimization-of-mysql.html

请关注微信公众号
微信二维码
不容错过
Powered By Z-BlogPHP