索引与外键
普通索引:
这是最基本的索引,它没有任何索引,只是为了加快查询速度。
唯一索引
它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
主键索引
它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引
要求:
一张表只允许一个主键
主键要求自增
组合索引
多个字段建立索引。
全文索引
fulltext
MyISAM引擎支持。
外键
注意:
1、引擎必须一样。
2、字段类型必须一样
3、长度必须一样
4、存储范围也必须一样
5、约束字段必须在被引用的字段中存在。
索引的不足之处
上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:
◆虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。
索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。
使用索引的注意事项
使用索引时,有以下一些技巧和注意事项:
◆索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
◆使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
◆索引列排序
MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
◆like语句操作
一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
◆不要在列上进行运算
1.select * from users where YEAR(adddate)<2007;
将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成
1.select * from users where adddate<‘2007-01-01’;
◆不使用NOT IN和<>操作
深入了解mysql索引与优化
索引目的
索引的目的在于提高查询效率,可以类比字典,如果要查“mysql”这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql。如果没有索引,那么你可能需要把所有单词看一遍才能找到你想要的,如果我想找到m开头的单词呢?或者L开头的单词呢?是不是觉得如果没有索引,这个事情根本无法完成?
索引原理
除了词典,生活中随处可见索引的例子,如火车站的车次表、图书的目录等。它们的原理都是一样的,通过不断的缩小想要获得数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是我们总是通过同一种查找方式来锁定数据。
数据库也是一样,但显然要复杂许多,因为不仅面临着等值查询,还有范围查询(>、<、between、in)
、模糊查询(like)
、并集查询(or)
等等。数据库应该选择怎么样的方式来应对所有的问题呢?我们回想字典的例子,能不能把数据分成段,然后分段查询呢?最简单的如果1000条数据,1到100分成第一段,101到200分成第二段,201到300分成第三段……这样查第250条数据,只要找第三段就可以了,一下子去除了90%的无效数据。但如果是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毫秒的时间,显然是个灾难。
考虑到磁盘IO是非常高昂的操作,计算机操作系统做了一些优化,当一次IO时,不光把当前磁盘地址的数据,而是把相邻的数据也都读取到内存缓冲区内,因为局部预读性原理告诉我们,当计算机访问一个地址的数据的时候,与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关,一般为4k或8k,也就是我们读取一页内的数据时候,实际上才发生了一次IO,这个理论对于索引的数据结构设计非常有帮助。
MySQL索引原理及慢查询优化
MySQL凭借着出色的性能、低廉的成本、丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色,但所谓“好马配好鞍”,如何能够更好的使用它,已经成为开发工程师的必修课,我们经常会从职位描述上看到诸如“精通MySQL”、“SQL语句优化”、“了解数据库原理”
等要求。我们知道一般的应用系统,读写比例在10:1左右
,而且插入操作和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,所以查询语句的优化显然是重中之重
。
多数情况下,我们知道索引能够提高查询效率,但应该如何建立索引?索引的顺序如何?许多人却只知道大概。其实理解这些概念并不难,而且索引的原理远没有想象的那么复杂。
MySQL索引原理
索引的数据结构
前面讲了生活中索引的例子,索引的基本原理,数据库的复杂性,又讲了操作系统的相关知识,目的就是让大家了解,任何一种数据结构都不是凭空产生的,一定会有它的背景和使用场景,我们现在总结一下,我们需要这种数据结构能够做些什么,其实很简单,那就是:每次查找数据时把磁盘IO次数控制在一个很小的数量级,最好是常数数量级。
那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢?就这样,b+树
应运而生。
慢查询优化
关于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命令相信大家并不陌生,具体用法和字段含义可以参考官网HYPERLINK
"http://dev.mysql.com/doc/refman/5.5/en/explain-output.html"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的执行计划和索引原则有非常清楚的认识,请看下面的语句
MySQL
1select
2 distinct cert.emp_id
3from
4 cm_log cl
5inner join
6 (
7 select
8 emp.id as emp_id,
9 emp_cert.id as cert_id
10 from
11 employee emp
12 left join
13 emp_certificate emp_cert
14 on emp.id = emp_cert.emp_id
15 where
16 emp.is_deleted=0
17 ) cert
18 on (
19 cl.ref_table='Employee'
20 and cl.ref_oid= cert.emp_id
21 )
22 or (
23 cl.ref_table='EmpCertificate'
24 and cl.ref_oid= cert.cert_id
25 )
26where
27 cl.last_upd_date >='2013-11-07 15:03:00'
28 and cl.last_upd_date<='2013-11-08 16:00:00';
29
30 53 rows in set (1.87 sec)
先运行一下,53条记录 1.87秒,又没有用聚合语句,比较慢
explain
1+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
2| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
3+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
4| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where; Using temporary |
5| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 63727 | Using where; Using join buffer |
6| 2 | DERIVED | emp | ALL | NULL | NULL | NULL | NULL | 13317 | Using where |
7| 2 | DERIVED | emp_cert | ref | emp_certificate_empid | emp_certificate_empid | 4 | meituanorg.emp.id | 1 | Using index |
8+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
9
简述一下执行计划,首先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性能。
优化过的语句如下
MySQL
1select
2 emp.id
3from
4 cm_log cl
5inner join
6 employee emp
7 on cl.ref_table = 'Employee'
8 and cl.ref_oid = emp.id
9where
10 cl.last_upd_date >='2013-11-07 15:03:00'
11 and cl.last_upd_date<='2013-11-08 16:00:00'
12 and emp.is_deleted = 0
13union
14select
15 emp.id
16from
17 cm_log cl
18inner join
19 emp_certificate ec
20 on cl.ref_table = 'EmpCertificate'
21 and cl.ref_oid = ec.id
22inner join
23 employee emp
24 on emp.id = ec.emp_id
25where
26 cl.last_upd_date >='2013-11-07 15:03:00'
27 and cl.last_upd_date<='2013-11-08 16:00:00'
28 and emp.is_deleted = 0
不需要了解业务场景,只需要改造的语句和改造之前的语句保持结果一致
现有索引可以满足,不需要建索引
用改造后的语句实验一下,只需要10ms 降低了近200倍!
1+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
2| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
3+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
4| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where |
5| 1 | PRIMARY | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | Using where |
6| 2 | UNION | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where |
7| 2 | UNION | ec | eq_ref | PRIMARY,emp_certificate_empid | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | |
8| 2 | UNION | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.ec.emp_id | 1 | Using where |
9| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
10+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
11
明确应用场景
举这个例子的目的在于颠覆我们对列的区分度的认知,一般上我们认为区分度越高的列,越容易锁定更少的记录,但在一些特殊的情况下,这种理论是有局限性的
MySQL
1select
2 *
3from
4 stage_poi sp
5where
6 sp.accurate_result=1
7 and (
8 sp.sync_status=0
9 or sp.sync_status=2
10 or sp.sync_status=4
11 );
12 951 rows in set (6.22 sec)
先看看运行多长时间,951条数据6.22秒,真的很慢
先explain,rows达到了361万,type = ALL表明是全表扫描
1+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
2| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
3+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
4| 1 | SIMPLE | sp | ALL | NULL | NULL | NULL | NULL | 3613155 | Using where |
5+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
所有字段都应用查询返回记录数,因为是单表查询 0已经做过了951条
让explain的rows 尽量逼近951
看一下accurate_result = 1的记录数
1select count(*),accurate_result from stage_poi group by accurate_result;
2+----------+-----------------+
3| count(*) | accurate_result |
4+----------+-----------------+
5| 1023 | -1 |
6| 2114655 | 0 |
7| 972815 | 1 |
8+----------+-----------------+
我们看到accurate_result这个字段的区分度非常低,整个表只有-1,0,1三个值,加上索引也无法锁定特别少量的数据
再看一下sync_status字段的情况
1select count(*),sync_status from stage_poi group by sync_status;
2+----------+-------------+
3| count(*) | sync_status |
4+----------+-------------+
5| 3080 | 0 |
6| 3085413 | 3 |
7+----------+-------------+
同样的区分度也很低,根据理论,也不适合建立索引
问题分析到这,好像得出了这个表无法优化的结论,两个列的区分度都很低,即便加上索引也只能适应这种情况,很难做普遍性的优化,比如当sync_status 0、3分布的很平均,那么锁定记录也是百万级别的
找业务方去沟通,看看使用场景。业务方是这么来使用这个SQL语句的,每隔五分钟会扫描符合条件的数据,处理完成后把sync_status这个字段变成1,五分钟符合条件的记录数并不会太多,1000个左右。了解了业务方的使用场景后,优化这个SQL就变得简单了,因为业务方保证了数据的不平衡,如果加上索引可以过滤掉绝大部分不需要的数据
根据建立索引规则,使用如下语句建立索引
1alter table stage_poi add index idx_acc_status(accurate_result,sync_status);
2952 rows in set (0.20 sec)
观察预期结果,发现只需要200ms,快了30多倍。
我们再来回顾一下分析问题的过程,单表查询相对来说比较好优化,大部分时候只需要把where条件里面的字段依照规则加上索引就好,如果只是这种“无脑”优化的话,显然一些区分度非常低的列,不应该加索引的列也会被加上索引,这样会对插入、更新性能造成严重的影响,同时也有可能影响其它的查询语句。所以我们第4步调差SQL的使用场景非常关键,我们只有知道这个业务场景,才能更好地辅助我们更好的分析和优化查询语句。
无法优化的语句
MySQL
1select
2 c.id,
3 c.name,
4 c.position,
5 c.sex,
6 c.phone,
7 c.office_phone,
8 c.feature_info,
9 c.birthday,
10 c.creator_id,
11 c.is_keyperson,
12 c.giveup_reason,
13 c.status,
14 c.data_source,
15 from_unixtime(c.created_time) as created_time,
16 from_unixtime(c.last_modified) as last_modified,
17 c.last_modified_user_id
18from
19 contact c
20inner join
21 contact_branch cb
22 on c.id = cb.contact_id
23inner join
24 branch_user bu
25 on cb.branch_id = bu.branch_id
26 and bu.status in (
27 1,
28 2)
29 inner join
30 org_emp_info oei
31 on oei.data_id = bu.user_id
32 and oei.node_left >= 2875
33 and oei.node_right <= 10802
34 and oei.org_category = - 1
35 order by
36 c.created_time desc limit 0 ,10;
37 10 rows in set (13.06 sec)
还是几个步骤
先看语句运行多长时间,10条记录用了13秒,已经不可忍受
explain
1+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+
2| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
3+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+
4| 1 | SIMPLE | oei | ref | idx_category_left_right,idx_data_id | idx_category_left_right | 5 | const | 8849 | Using where; Using temporary; Using filesort |
5| 1 | SIMPLE | bu | ref | PRIMARY,idx_userid_status | idx_userid_status | 4 | meituancrm.oei.data_id | 76 | Using where; Using index |
6| 1 | SIMPLE | cb | ref | idx_branch_id,idx_contact_branch_id | idx_branch_id | 4 | meituancrm.bu.branch_id | 1 | |
7| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 108 | meituancrm.cb.contact_id | 1 | |
8+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+
9
从执行计划上看,mysql先查org_emp_info表扫描8849记录,再用索引idx_userid_status关联branch_user表,再用索引idx_branch_id关联contact_branch表,最后主键关联contact表。
rows返回的都非常少,看不到有什么异常情况。我们在看一下语句,发现后面有order by + limit组合,会不会是排序量太大搞的?于是我们简化SQL,去掉后面的order by 和 limit,看看到底用了多少记录来排序
MySQL
1select
2 count(*)
3from
4 contact c
5inner join
6 contact_branch cb
7 on c.id = cb.contact_id
8inner join
9 branch_user bu
10 on cb.branch_id = bu.branch_id
11 and bu.status in (
12 1,
13 2)
14 inner join
15 org_emp_info oei
16 on oei.data_id = bu.user_id
17 and oei.node_left >= 2875
18 and oei.node_right <= 10802
19 and oei.org_category = - 1
20+----------+
21| count(*) |
22+----------+
23| 778878 |
24+----------+
251 row in set (5.19 sec)
发现排序之前居然锁定了778878条记录,如果针对70万的结果集排序,将是灾难性的,怪不得这么慢,那我们能不能换个思路,先根据contact的created_time排序,再来join会不会比较快呢?
于是改造成下面的语句,也可以用straight_join来优化
MySQL
1select
2c.id,
3c.name,
4c.position,
5c.sex,
6c.phone,
7c.office_phone,
8c.feature_info,
9c.birthday,
10c.creator_id,
11c.is_keyperson,
12c.giveup_reason,
13c.status,
14c.data_source,
15from_unixtime(c.created_time) as created_time,
16from_unixtime(c.last_modified) as last_modified,
17c.last_modified_user_id
18from
19contact c
20where
21exists (
22select
231
24from
25contact_branch cb
26inner join
27branch_user bu
28on cb.branch_id = bu.branch_id
29and bu.status in (
301,
312)
32inner join
33org_emp_info oei
34on oei.data_id = bu.user_id
35and oei.node_left >= 2875
36and oei.node_right <= 10802
37and oei.org_category = - 1
38where
39c.id = cb.contact_id
40)
41order by
42c.created_time desc limit 0 ,10;
4310 rows in set (0.00 sec)
验证一下效果 预计在1ms内,提升了13000多倍!
本以为至此大工告成,但我们在前面的分析中漏了一个细节,先排序再join和先join再排序理论上开销是一样的,为何提升这么多是因为有一个limit!大致执行过程是:mysql先按索引排序得到前10条记录,然后再去join过滤,当发现不够10条的时候,再次去10条,再次join,这显然在内层join过滤的数据非常多的时候,将是灾难的,极端情况,内层一条数据都找不到,mysql还傻乎乎的每次取10条,几乎遍历了这个数据表!
用不同参数的SQL试验下
MySQL
1select
2 sql_no_cache c.id,
3 c.name,
4 c.position,
5 c.sex,
6 c.phone,
7 c.office_phone,
8 c.feature_info,
9 c.birthday,
10 c.creator_id,
11 c.is_keyperson,
12 c.giveup_reason,
13 c.status,
14 c.data_source,
15 from_unixtime(c.created_time) as created_time,
16 from_unixtime(c.last_modified) as last_modified,
17 c.last_modified_user_id
18from
19 contact c
20where
21 exists (
22 select
23 1
24 from
25 contact_branch cb
26 inner join
27 branch_user bu
28 on cb.branch_id = bu.branch_id
29 and bu.status in (
30 1,
31 2)
32 inner join
33 org_emp_info oei
34 on oei.data_id = bu.user_id
35 and oei.node_left >= 2875
36 and oei.node_right <= 2875
37 and oei.org_category = - 1
38 where
39 c.id = cb.contact_id
40 )
41 order by
42 c.created_time desc limit 0 ,
43 10;
44Empty set (2 min 18.99 sec)
2 min 18.99 sec!比之前的情况还糟糕很多。由于mysql的nested loop机制,遇到这种情况,基本是无法优化的。这条语句最终也只能交给应用系统去优化自己的逻辑了。
通过这个例子我们可以看到,并不是所有语句都能优化,而往往我们优化时,由于SQL用例回归时落掉一些极端情况,会造成比原来还严重的后果。所以,第一:不要指望所有语句都能通过SQL优化,第二:不要过于自信,只针对具体case来优化,而忽略了更复杂的情况。
慢查询的案例就分析到这儿,以上只是一些比较典型的案例。我们在优化过程中遇到过超过1000行,涉及到16个表join的“垃圾SQL”,也遇到过线上线下数据库差异导致应用直接被慢查询拖死,也遇到过varchar等值比较没有写单引号,还遇到过笛卡尔积查询直接把从库搞死。再多的案例其实也只是一些经验的积累,如果我们熟悉查询优化器、索引的内部原理,那么分析这些案例就变得特别简单了。
写在后面的话
本文以一个慢查询案例引入了MySQL索引原理、优化慢查询的一些方法论;并针对遇到的典型案例做了详细的分析。其实做了这么长时间的语句优化后才发现,任何数据库层面的优化都抵不上应用系统的优化
,同样是MySQL,可以用来支撑Google/FaceBook/Taobao应用,但可能连你的个人网站都撑不住。套用最近比较流行的话:查询容易,优化不易,且写且珍惜!