椒江建设网站,专做h5的公司网站,网站建设用什么软件做,青岛市南区城市建设局网站MySQL凭借着出色的性能、低廉的成本、丰富的资源#xff0c;已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色#xff0c;但所谓“好马配好鞍”#xff0c;如何能够更好的使用它#xff0c;已经成为开发工程师的必修课#xff0c;我们经常会从职位描述上看到诸… 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的时间约等于54.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、P3P1表示小于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由磁盘加载到内存发生第二次IO29在26和30之间锁定磁盘块3的P2指针通过指针加载磁盘块8到内存发生第三次IO同时内存中做二分查找找到29结束查询总计三次IO。真实的情况是3层的b树可以表示上百万的数据如果上百万的数据查找只需要三次IO性能提高将是巨大的如果没有索引每个数据项都要发生一次IO那么总共需要百万次的IO显然成本非常非常高。 7.b树性质 1.通过上面的分析我们知道IO次数取决于b数的高度h假设当前数据表的数据为N每个磁盘块的数据项的数量是m则有h㏒m1N当数据量N一定的情况下m越大h越小而m 磁盘块的大小 / 数据项的大小磁盘块的大小也就是一个数据页的大小是固定的如果数据项占的空间越小数据项的数量越多树的高度越低。这就是为什么每个数据项即索引字段要尽量的小比如int占4字节要比bigint8字节少一半。这也是为什么b树要求把真实的数据放到叶子节点而不是内层节点一旦放到内层节点磁盘块的数据项会大幅度下降导致树增高。当数据项等于1时将会退化成线性表。 2.当b树的数据项是复合的数据结构比如name,age,sex的时候b数是按照从左到右的顺序来建立搜索树的比如当张三,20,F这样的数据来检索的时候b树会优先比较name来确定下一步的所搜方向如果name相同再依次比较age和sex最后得到检索的数据但当20,F这样的没有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.尽量选择区分度高的列作为索引 区分度的公式是countdistinct col/count*表示字段不重复的比例比例越大我们扫描的记录数越少唯一键的区分度是1而一些状态、性别字段可能在大数据面前区分度就是0那可能有人会问这个比例有什么经验值吗使用场景不同这个值也很难确定一般需要join的字段我们都要求是0.1以上即平均1条扫描10条记录 4.索引列不能参与计算保持列“干净” 比如from_unixtimecreate_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的执行计划和索引原则有非常清楚的认识请看下面的语句
selectdistinct cert.emp_id
fromcm_log cl
inner joinselectemp.id as emp_id,emp_cert.id as cert_id fromemployee emp left joinemp_certificate emp_cert on emp.id emp_cert.emp_id whereemp.is_deleted0 cert on cl.ref_tableEmployee and cl.ref_oid cert.emp_id or cl.ref_tableEmpCertificate and cl.ref_oid cert.cert_id
wherecl.last_upd_date 2013-11-07 15:03:00 and cl.last_upd_date2013-11-08 16:00:00;0.先运行一下53条记录 1.87秒又没有用聚合语句比较慢
53 rows in set 1.87 sec1.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性能。 优化过的语句如下
selectemp.id
fromcm_log cl
inner joinemployee emp on cl.ref_table Employee and cl.ref_oid emp.id
wherecl.last_upd_date 2013-11-07 15:03:00 and cl.last_upd_date2013-11-08 16:00:00 and emp.is_deleted 0
union
selectemp.id
fromcm_log cl
inner joinemp_certificate ec on cl.ref_table EmpCertificate and cl.ref_oid ec.id
inner joinemployee emp on emp.id ec.emp_id
wherecl.last_upd_date 2013-11-07 15:03:00 and cl.last_upd_date2013-11-08 16:00:00 and emp.is_deleted 04.不需要了解业务场景只需要改造的语句和改造之前的语句保持结果一致 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,2 明确应用场景 举这个例子的目的在于颠覆我们对列的区分度的认知一般上我们认为区分度越高的列越容易锁定更少的记录但在一些特殊的情况下这种理论是有局限性的
select*
fromstage_poi sp
wheresp.accurate_result1 and sp.sync_status0 or sp.sync_status2 or sp.sync_status4;0.先看看运行多长时间,951条数据6.22秒真的很慢
951 rows in set 6.22 sec1.先explainrows达到了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 |
----------------------------------------------------------------------------------------2.所有字段都应用查询返回记录数因为是单表查询 0已经做过了951条 3.让explain的rows 尽量逼近951 看一下accurate_result 1的记录数
select count*,accurate_result from 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 |
-----------------------同样的区分度也很低根据理论也不适合建立索引 问题分析到这好像得出了这个表无法优化的结论两个列的区分度都很低即便加上索引也只能适应这种情况很难做普遍性的优化比如当sync_status 0、3分布的很平均那么锁定记录也是百万级别的 4.找业务方去沟通看看使用场景。业务方是这么来使用这个SQL语句的每隔五分钟会扫描符合条件的数据处理完成后把sync_status这个字段变成1,五分钟符合条件的记录数并不会太多1000个左右。了解了业务方的使用场景后优化这个SQL就变得简单了因为业务方保证了数据的不平衡如果加上索引可以过滤掉绝大部分不需要的数据 5.根据建立索引规则使用如下语句建立索引
alter table stage_poi add index idx_acc_statusaccurate_result,sync_status;6.观察预期结果,发现只需要200ms快了30多倍。
952 rows in set 0.20 sec我们再来回顾一下分析问题的过程单表查询相对来说比较好优化大部分时候只需要把where条件里面的字段依照规则加上索引就好如果只是这种“无脑”优化的话显然一些区分度非常低的列不应该加索引的列也会被加上索引这样会对插入、更新性能造成严重的影响同时也有可能影响其它的查询语句。 所以我们第4步调差SQL的使用场景非常关键我们只有知道这个业务场景才能更好地辅助我们更好的分析和优化查询语句。 慢查询的案例就分析到这儿以上只是一些比较典型的案例。 我们在优化过程中遇到过超过1000行涉及到16个表join的“垃圾SQL”也遇到过线上线下数据库差异导致应用直接被慢查询拖死也遇到过varchar等值比较没有写单引号还遇到过笛卡尔积查询直接把从库搞死。再多的案例其实也只是一些经验的积累如果我们熟悉查询优化器、索引的内部原理那么分析这些案例就变得特别简单了。 你可能也喜欢: 阿里P8架构师谈Web前端、应用服务器、数据库SQL等性能优化总结 最全MySQL面试60题和答案阿里P8架构师谈MySQL行锁、表锁、悲观锁、乐观锁的特点与应用阿里P8架构师谈MySQL有哪些存储引擎各自的优缺点应用场景阿里P8架构师谈数据库、JVM、缓存、SQL等性能调优方法和原则 阿里P8架构师谈MySQL慢查询优化、索引优化、以及表等优化总结
