简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)
집 > 데이터 베이스 > MySQL 튜토리얼 > 본문

MySQL 인덱스 원칙 및 최적화

步履不停
풀어 주다： 2019-06-22 18:17:34
원래의
3023명이 탐색했습니다.

MySQL 인덱스 원칙 및 최적화

머리말

이 글은 메이투안의 큰 상사가 쓴 글입니다. 코드가 내장되어 있습니다. html의 sql문은 java 프레임워크로 작성되어 실행되는 sql문만 이해하면 됩니다.

Background

MySQL은 뛰어난 성능, 저렴한 비용, 풍부한 리소스로 대부분의 인터넷 기업이 선호하는 관계형 데이터베이스가 되었습니다. 성능은 뛰어나지만, 소위 "좋은 말에는 좋은 안장이 따른다"는 말은 개발 엔지니어들에게는 필수 코스가 되었고, "MySQL에 능숙하다", "SQL 문 최적화" 같은 말을 자주 보게 됩니다. 직무 설명, "데이터베이스 원칙 이해" 및 기타 요구 사항에서. 우리는 일반적인 응용 프로그램 시스템에서 읽기-쓰기 비율이 약 10:1이며 삽입 작업과 일반 업데이트 작업이 성능 문제를 거의 일으키지 않으며 문제를 일으킬 가능성이 가장 높은 것은 복잡한 쿼리라는 것을 알고 있습니다. 따라서 쿼리문 최적화가 최우선 과제입니다.

저는 2013년 7월부터 메이투안 핵심업무시스템부서에서 느린 쿼리 최적화 작업을 해왔습니다. 총 10개 이상의 시스템이 있으며 수백 개의 느린 쿼리 사례를 해결하고 축적했습니다. . 비즈니스의 복잡성이 증가함에 따라 직면하는 문제는 온갖 종류의 이상하고 다양하며 믿을 수 없습니다. 이 문서에서는 개발 엔지니어의 관점에서 데이터베이스 인덱싱의 원리와 느린 쿼리를 최적화하는 방법을 설명하는 것을 목표로 합니다. 

<span>select</span>
    <span>count</span>(*) <span>from</span>
   task 
<span>where</span>
   <span>status</span>=<span>2</span> 
   <span>and</span> operator_id=<span>20839</span> 
   <span>and</span> operate_time><span>1371169729</span> 
   <span>and</span> operate_time1371174603 
   <span>and</span> <span>type</span>=<span>2</span>;
로그인 후 복사

시스템 사용자들이 함수가 점점 느려진다고 보고했기 때문에 엔지니어는 위의 SQL을 발견했습니다.

그리고 저를 신나게 발견했습니다. "이 SQL을 최적화해야 합니다. 각 필드에 인덱스를 추가해 주세요."

놀라서 "왜 각 필드에 인덱스를 추가해야 하나요?"라고 물었습니다.

"쿼리된 모든 필드에 인덱스를 추가하는 것이 더 빠릅니다. " 자신감 넘치는 엔지니어.

"이 경우에는 조인트 인덱스 구축이 완전히 가능합니다. 가장 왼쪽 접두사 매칭이므로 마지막에 Operate_time을 넣어야 하고, 기타 관련 쿼리도 들어가야 합니다. , 종합적인 평가가 필요합니다."

"Union index? 왼쪽 접두사 일치?" 엔지니어는 생각하지 않을 수 없었습니다.

대부분의 경우 인덱스가 쿼리 효율성을 향상시킬 수 있다는 것을 알고 있지만 인덱스를 어떻게 구축해야 할까요? 인덱스의 순서는 무엇입니까? 많은 분들이 대략적으로만 알고 계십니다. 사실 이러한 개념을 이해하는 것은 어렵지 않으며, 인덱싱의 원리도 생각보다 훨씬 덜 복잡합니다.

인덱스 목적

인덱스의 목적은 쿼리 효율성을 높이는 것인데, 이는 사전과 비교할 수 있습니다. "mysql"이라는 단어를 검색하려면 확실히 m 문자를 찾은 다음 맨 아래부터 시작해야 합니다. 아래로 내려가서 y 문자를 찾은 다음 나머지 SQL을 찾으세요. 색인이 없으면 원하는 것을 찾기 위해 모든 단어를 살펴봐야 할 수도 있습니다. m으로 시작하는 단어를 찾으려면 어떻게 해야 합니까? 아니면 ze로 시작하는 단어는 어떻습니까? 색인이 없으면 이 문제가 전혀 완료될 수 없다고 생각하시나요?

색인원리

색인의 예는 사전 외에도 기차역의 기차 시간표, 도서 카탈로그 등 생활 곳곳에서 볼 수 있습니다. 얻고자 하는 데이터의 범위를 지속적으로 좁힘으로써 원하는 최종 결과를 필터링할 수 있으며, 동시에 무작위 이벤트를 순차적 이벤트로 전환할 수 있습니다. 즉, 항상 동일한 검색을 사용합니다. 데이터를 잠그는 방법.

데이터베이스도 마찬가지지만, 동등한 쿼리뿐만 아니라 범위 쿼리(>,

디스크 IO 및 사전 읽기

앞서 디스크에 액세스하는 것에 대해 언급했으므로 여기서는 디스크 IO 및 사전 읽기에 대해 간략하게 소개합니다. 디스크 읽기 데이터는 기계적 움직임에 의존하며 매번. 데이터를 읽는 데 소요되는 시간은 탐색 시간, 회전 지연, 전송 시간의 세 부분으로 나눌 수 있습니다. 탐색 시간은 자기 팔이 지정된 트랙으로 이동하는 데 필요한 시간을 의미하며 일반적으로 5ms 미만입니다. 회전 지연은 우리가 자주 듣는 말입니다. 예를 들어 디스크의 속도는 7200rpm이며 이는 분당 7200번 회전할 수 있음을 의미하며 회전 지연은 1/120/2입니다. = 4.17ms; 전송 시간은 디스크에서 읽는 것을 의미합니다. 또는 디스크에 데이터를 쓰는 시간은 일반적으로 10분의 1초로 처음 두 번에 비해 무시할 수 있습니다. 그러면 디스크에 액세스하는 시간, 즉 디스크 IO에 소요되는 시간은 대략 5+4.17 = 9ms와 같습니다. 꽤 그럴듯하게 들리지만 500-MIPS 머신은 초당 5억 개의 명령을 실행할 수 있다는 것을 알아야 합니다. 즉, 하나의 IO를 실행하는 데 걸리는 시간에 400,000개의 명령을 실행할 수 있기 때문입니다. 데이터베이스에는 매번 수십만, 수백만 또는 수천만 개의 데이터가 포함되는 경우가 많습니다. 9밀리초가 걸리면 이는 분명히 재앙입니다. 다음 그림은 참고용으로 컴퓨터 하드웨어 대기 시간을 비교한 차트입니다.

MySQL 인덱스 원칙 및 최적화

various-system-software-hardware-latency

#🎜 🎜 #디스크 IO가 매우 비용이 많이 드는 작업이라는 점을 고려하여 컴퓨터 운영 체제는 IO가 수행될 때 현재 디스크 주소의 데이터뿐만 아니라 인접한 데이터도 메모리 버퍼로 읽어옵니다. 로컬 미리 읽기의 원리는 컴퓨터가 특정 주소의 데이터에 액세스할 때 인접한 데이터에도 빠르게 액세스한다는 것을 의미합니다. IO가 매번 읽는 데이터를 페이지라고 합니다. 페이지의 데이터 크기는 운영 체제에 따라 달라지며 일반적으로 4k 또는 8k입니다. 즉, 페이지의 데이터를 읽을 때 실제로는 IO가 하나만 발생한다는 이론은 인덱스의 데이터 구조 설계에 매우 유용합니다. .

인덱스의 데이터 구조

생활 속 인덱스의 예, 인덱스의 기본 원리, 데이터베이스의 복잡성, 운영체제 관련 지식에 대해 이야기를 나누는 것이 목적입니다. 모든 데이터 구조는 허공에서 생성되지 않는다는 점을 이해하십시오. 배경 및 사용 시나리오가 있어야 합니다. 이제 이 데이터 구조가 수행해야 하는 작업을 요약해 보겠습니다. 즉, 검색할 때마다입니다. 데이터의 경우 디스크 IO 수를 작은 크기로, 바람직하게는 일정한 크기로 제어합니다. 그런 다음 고도로 제어 가능한 다중 경로 검색 트리가 요구 사항을 충족할 수 있는지 생각해 보겠습니다. 이런 식으로 b+ 트리가 탄생했습니다.

b+tree에 대한 자세한 설명

MySQL 인덱스 원칙 및 최적화

b+tree

위와 같이 b+ 트리의 정의는 B+ 트리를 참조하세요. 다음은 몇 가지 핵심 사항입니다. 연한 파란색 블록을 디스크 블록이라고 합니다(어두운 부분으로 표시). 파란색) 및 포인터((노란색으로 표시). 예를 들어 디스크 블록 1에는 포인터 P1, P2 및 P3을 포함하여 데이터 항목 17과 35가 포함되어 있습니다. P1은 17보다 작은 디스크 블록을 나타내고, P2는 17과 17 사이의 디스크 블록을 나타냅니다. 35이고 P3은 35개보다 큰 디스크를 나타냅니다. 실제 데이터는 리프 노드, 즉 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99에 존재합니다. Non-leaf 노드는 실제 데이터를 저장하지 않고 검색 방향을 안내하는 데이터 항목만 저장합니다. 예를 들어 17과 35는 실제로 데이터 테이블에 존재하지 않습니다.

B+트리 검색 프로세스

그림과 같이 데이터 항목 29를 찾으려면 디스크 블록 1이 먼저 디스크에서 메모리로 로드됩니다. IO가 발생하면 메모리에서 이진 검색을 사용하여 17과 35 사이의 29를 결정하고 디스크 블록 1의 P2 포인터를 잠급니다. 메모리 시간은 매우 짧기 때문에 무시할 수 있습니다(디스크의 IO에 비해). 디스크 블록 1의 P2 포인터를 디스크에 전달합니다. 주소는 디스크에서 메모리로 디스크 블록 3을 로드합니다. 두 번째 IO는 26과 30 사이에서 발생합니다. 디스크 블록 3의 P2 포인터는 잠겨 있습니다. 세 번째 IO가 발생합니다. 동시에 메모리에서 이진 검색을 수행하여 29개를 찾아 총 3개의 IO를 찾습니다. 실제 상황은 3계층 b+ 트리가 수백만 개의 데이터를 나타낼 수 있다는 것입니다. 수백만 개의 데이터 검색에 3개의 IO만 필요한 경우 인덱스가 없으면 각 데이터 항목에 대해 하나의 IO가 발생합니다. 그러면 총 수백만 개의 IO가 필요하며 이는 분명히 매우 비쌉니다.

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)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

关于MySQL索引原理是比较枯燥的东西，大家只需要有一个感性的认识，并不需要理解得非常透彻和深入。我们回头来看看一开始我们说的慢查询，了解完索引原理之后，大家是不是有什么想法呢？先总结一下索引的几大基本原则：

建索引的几大原则

  1. 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、 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)的索引，那么只需要修改原来的索引即可。

回到开始的慢查询

根据最左匹配原则，最开始的sql语句的索引应该是status、operator_id、type、operate_time的联合索引；其中status、operator_id、type的顺序可以颠倒，所以我才会说，把这个表的所有相关查询都找到，会综合分析； 比如还有如下查询：

<span>select</span> * <span>from</span> task <span>where</span> <span>status</span> = <span>0</span> <span>and</span> <span>type</span> = <span>12</span> <span>limit</span> <span>10</span>;
로그인 후 복사
<span>select</span> <span>count</span>(*) <span>from</span> task <span>where</span> <span>status</span> = <span>0</span> ;
로그인 후 복사

那么索引建立成(status,type,operator_id,operate_time)就是非常正确的，因为可以覆盖到所有情况。这个就是利用了索引的最左匹配的原则

查询优化神器 – explain命令

关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。

慢查询优化基本步骤

  1. 先运行看看是否真的很慢，注意设置SQL_NO_CACHE
  2. where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
  3. explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
  4. order by limit 形式的sql语句让排序的表优先查
  5. 了解业务方使用场景
  6. 加索引时参照建索引的几大原则
  7. 观察结果，不符合预期继续从0分析

几个慢查询案例

下面几个例子详细解释了如何分析和优化慢查询。

复杂语句写法

很多情况下，我们写SQL只是为了实现功能，这只是第一步，不同的语句书写方式对于效率往往有本质的差别，这要求我们对mysql的执行计划和索引原则有非常清楚的认识，请看下面的语句：

<span>select</span>
   <span>distinct</span> cert.emp_id 
<span>from</span>
   cm_log cl 
<span>inner</span> <span>join</span>
   (
      <span>select</span>
         emp.id <span>as</span> emp_id,
         emp_cert.id <span>as</span> cert_id 
      <span>from</span>
         employee emp 
      <span>left</span> <span>join</span>
         emp_certificate emp_cert 
            <span>on</span> emp.id = emp_cert.emp_id 
      <span>where</span>
         emp.is_deleted=<span>0</span>
   ) cert 
      <span>on</span> (
         cl.ref_table=<span>'Employee'</span> 
         <span>and</span> cl.ref_oid= cert.emp_id
      ) 
      <span>or</span> (
         cl.ref_table=<span>'EmpCertificate'</span> 
         <span>and</span> cl.ref_oid= cert.cert_id
      ) 
<span>where</span>
   cl.last_upd_date >=<span>'2013-11-07 15:03:00'</span> 
   <span>and</span> cl.last_upd_date'2013-11-08 16:00:00';
로그인 후 복사
  1. 先运行一下，53条记录 1.87秒，又没有用聚合语句，比较慢
53 rows in <span>set</span> (<span>1.87</span> sec)
로그인 후 복사
  1. explain
+<span>----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+</span>
| id | select_type | table      | type  | possible_keys                   | key                   | key_len | ref               | rows  | Extra                          |
+<span>----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+</span>
|  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     | <derived2> | 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                    |
+<span>----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+</span></derived2>
로그인 후 복사

简述一下执行计划，首先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性能。

优化过的语句如下：

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

  1. 不需要了解业务场景，只需要改造的语句和改造之前的语句保持结果一致

  2. 现有索引可以满足，不需要建索引

  3. 用改造后的语句实验一下，只需要10ms 降低了近200倍！

    +<span>----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+</span>
| id | select_type  | table      | type   | possible_keys                   | key               | key_len | ref                   | rows | Extra       |
+<span>----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+</span>
|  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 | <union1> | ALL    | NULL                            | NULL              | NULL    | NULL                  | NULL |             |
+<span>----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+</span>
53 rows in <span>set</span> (<span>0.01</span> sec)</union1>
    로그인 후 복사
       

明确应用场景

举这个例子的目的在于颠覆我们对列的区分度的认知，一般上我们认为区分度越高的列，越容易锁定更少的记录，但在一些特殊的情况下，这种理论是有局限性的。

<span>select</span>
   * 
<span>from</span>
   stage_poi sp 
<span>where</span>
   sp.accurate_result=<span>1</span> 
   <span>and</span> (
      sp.sync_status=<span>0</span> 
      <span>or</span> sp.sync_status=<span>2</span> 
      <span>or</span> sp.sync_status=<span>4</span>
   );
로그인 후 복사

  1. 先看看运行多长时间,951条数据6.22秒，真的很慢。

    951 rows in <span>set</span> (<span>6.22</span> sec)
    로그인 후 복사
       

  2. 先explain，rows达到了361万，type = ALL表明是全表扫描。

    +<span>----+-------------+-------+------+---------------+------+---------+------+---------+-------------+</span>
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+<span>----+-------------+-------+------+---------------+------+---------+------+---------+-------------+</span>
|  1 | SIMPLE      | sp    | ALL  | NULL          | NULL | NULL    | NULL | 3613155 | Using where |
+<span>----+-------------+-------+------+---------------+------+---------+------+---------+-------------+</span>
    로그인 후 복사
       

  3. 所有字段都应用查询返回记录数，因为是单表查询 0已经做过了951条。

  4. 让explain的rows 尽量逼近951。

看一下accurate_result = 1的记录数：

<span>select</span> <span>count</span>(*),accurate_result <span>from</span> stage_poi  <span>group</span> <span>by</span> accurate_result;
+<span>----------+-----------------+</span>
| count(*) | accurate_result |
+<span>----------+-----------------+</span>
|     1023 |              -1 |
|  2114655 |               0 |
|   972815 |               1 |
+<span>----------+-----------------+</span>
로그인 후 복사

我们看到accurate_result这个字段的区分度非常低，整个表只有-1,0,1三个值，加上索引也无法锁定特别少量的数据。

再看一下sync_status字段的情况：

<span>select</span> <span>count</span>(*),sync_status <span>from</span> stage_poi  <span>group</span> <span>by</span> sync_status;
+<span>----------+-------------+</span>
| count(*) | sync_status |
+<span>----------+-------------+</span>
|     3080 |           0 |
|  3085413 |           3 |
+<span>----------+-------------+</span>
로그인 후 복사

同样的区分度也很低，根据理论，也不适合建立索引。

问题分析到这，好像得出了这个表无法优化的结论，两个列的区分度都很低，即便加上索引也只能适应这种情况，很难做普遍性的优化，比如当sync_status 0、3分布的很平均，那么锁定记录也是百万级别的。

  1. 找业务方去沟通，看看使用场景。业务方是这么来使用这个SQL语句的，每隔五分钟会扫描符合条件的数据，处理完成后把sync_status这个字段变成1,五分钟符合条件的记录数并不会太多，1000个左右。了解了业务方的使用场景后，优化这个SQL就变得简单了，因为业务方保证了数据的不平衡，如果加上索引可以过滤掉绝大部分不需要的数据。

  2. 根据建立索引规则，使用如下语句建立索引

    <span>alter</span> <span>table</span> stage_poi <span>add</span> <span>index</span> idx_acc_status(accurate_result,sync_status);
    로그인 후 복사
       

  3. 观察预期结果,发现只需要200ms，快了30多倍。

    952 rows in <span>set</span> (<span>0.20</span> sec)
    로그인 후 복사
       

我们再来回顾一下分析问题的过程，单表查询相对来说比较好优化，大部分时候只需要把where条件里面的字段依照规则加上索引就好，如果只是这种“无脑”优化的话，显然一些区分度非常低的列，不应该加索引的列也会被加上索引，这样会对插入、更新性能造成严重的影响，同时也有可能影响其它的查询语句。所以我们第4步调差SQL的使用场景非常关键，我们只有知道这个业务场景，才能更好地辅助我们更好的分析和优化查询语句。

无法优化的语句

<span>select</span>
   c.id,
   c.name,
   c.position,
   c.sex,
   c.phone,
   c.office_phone,
   c.feature_info,
   c.birthday,
   c.creator_id,
   c.is_keyperson,
   c.giveup_reason,
   c.status,
   c.data_source,
   from_unixtime(c.created_time) <span>as</span> created_time,
   from_unixtime(c.last_modified) <span>as</span> last_modified,
   c.last_modified_user_id  
<span>from</span>
   contact c  
<span>inner</span> <span>join</span>
   contact_branch cb 
      <span>on</span>  c.id = cb.contact_id  
<span>inner</span> <span>join</span>
   branch_user bu 
      <span>on</span>  cb.branch_id = bu.branch_id 
      <span>and</span> bu.status <span>in</span> (
         <span>1</span>,
      <span>2</span>)  
   <span>inner</span> <span>join</span>
      org_emp_info oei 
         <span>on</span>  oei.data_id = bu.user_id 
         <span>and</span> oei.node_left >= <span>2875</span> 
         <span>and&llt;/span> oei.node_right 10802</span> 
         <span>and</span> oei.org_category = - <span>1</span>  
   <span>order</span> <span>by</span>
      c.created_time <span>desc</span>  <span>limit</span> <span>0</span> ,
      <span>10</span>;
로그인 후 복사

还是几个步骤。

  1. 先看语句运行多长时间，10条记录用了13秒，已经不可忍受。        
    10 rows in <span>set</span> (<span>13.06</span> sec)
    로그인 후 복사
       
  2. explain        
    +<span>----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+</span>
| id | select_type | table | type   | possible_keys                       | key                     | key_len | ref                      | rows | Extra                                        |
+<span>----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+</span>
|  1 | SIMPLE      | oei   | ref    | idx_category_left_right,idx_data_id | idx_category_left_right | 5       | const                    | 8849 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | bu    | ref    | PRIMARY,idx_userid_status           | idx_userid_status       | 4       | meituancrm.oei.data_id   |   76 | Using where; Using index                     |
|  1 | SIMPLE      | cb    | ref    | idx_branch_id,idx_contact_branch_id | idx_branch_id           | 4       | meituancrm.bu.branch_id  |    1 |                                              |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY                             | PRIMARY                 | 108     | meituancrm.cb.contact_id |    1 |                                              |
+<span>----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+</span>
    로그인 후 복사
       

从执行计划上看，mysql先查org_emp_info表扫描8849记录，再用索引idx_userid_status关联branch_user表，再用索引idx_branch_id关联contact_branch表，最后主键关联contact表。

rows返回的都非常少，看不到有什么异常情况。我们在看一下语句，发现后面有order by + limit组合，会不会是排序量太大搞的？于是我们简化SQL，去掉后面的order by 和 limit，看看到底用了多少记录来排序。

<span>select</span>
  <span>count</span>(*)
<span>from</span>
   contact c  
<span>inner</span> <span>join</span>
   contact_branch cb 
      <span>on</span>  c.id = cb.contact_id  
<span>inner</span> <span>join</span>
   branch_user bu 
      <span>on</span>  cb.branch_id = bu.branch_id 
      <span>and</span> bu.status <span>in</span> (
         <span>1</span>,
      <span>2</span>)  
   <span>inner</span> <span>join</span>
      org_emp_info oei 
         <span>on</span>  oei.data_id = bu.user_id 
         <span>and</span> oei.node_left >= <span>2875</span> 
         <span>and</span> oei.node_right 10802 
         <span>and</span> oei.org_category = - <span>1</span>  
+<span>----------+</span>
| <span>count</span>(*) |
+<span>----------+</span>
|   <span>778878</span> |
+<span>----------+</span>
<span>1</span> <span>row</span> <span>in</span> <span>set</span> (<span>5.19</span> sec)
로그인 후 복사

发现排序之前居然锁定了778878条记录，如果针对70万的结果集排序，将是灾难性的，怪不得这么慢，那我们能不能换个思路，先根据contact的created_time排序，再来join会不会比较快呢？

于是改造成下面的语句，也可以用straight_join来优化：

select c.id, c.name, c.position, c.sex, c.phone, c.office_phone, c.feature_info, c.birthday, c.creator_id, c.is_keyperson, c.giveup_reason, c.status, c.data_source, from_unixtime(c.created_time) as created_time, from_unixtime(c.last_modified) as last_modified, c.last_modified_user_id
from contact c
where exists ( select 1 from contact_branch cb
inner join branch_user bu
on cb.branch_id = bu.branch_id
and bu.status in ( 1, 2)
inner join org_emp_info oei
on oei.data_id = bu.user_id
and oei.node_left >= 2875
and oei.node_right <p>验证一下效果 预计在</p><pre class="brush:php;toolbar:false"><span>1</span>ms内，提升了<span>13000</span>多倍！
sql
<span>10</span> rows <span>in</span> <span>set</span> (<span>0.00</span> sec)
로그인 후 복사

本以为至此大工告成，但我们在前面的分析中漏了一个细节，先排序再join和先join再排序理论上开销是一样的，为何提升这么多是因为有一个limit！大致执行过程是：mysql先按索引排序得到前10条记录，然后再去join过滤，当发现不够10条的时候，再次去10条，再次join，这显然在内层join过滤的数据非常多的时候，将是灾难的，极端情况，内层一条数据都找不到，mysql还傻乎乎的每次取10条，几乎遍历了这个数据表！

用不同参数的SQL试验下：

<span>select</span>
   sql_no_cache   c.id,
   c.name,
   c.position,
   c.sex,
   c.phone,
   c.office_phone,
   c.feature_info,
   c.birthday,
   c.creator_id,
   c.is_keyperson,
   c.giveup_reason,
   c.status,
   c.data_source,
   from_unixtime(c.created_time) <span>as</span> created_time,
   from_unixtime(c.last_modified) <span>as</span> last_modified,
   c.last_modified_user_id    
<span>from</span>
   contact c   
<span>where</span>
   <span>exists</span> (
      <span>select</span>
         <span>1</span>        
      <span>from</span>
         contact_branch cb         
      <span>inner</span> <span>join</span>
         branch_user bu                     
            <span>on</span>  cb.branch_id = bu.branch_id                     
            <span>and</span> bu.status <span>in</span> (
               <span>1</span>,
            <span>2</span>)                
         <span>inner</span> <span>join</span>
            org_emp_info oei                           
               <span>on</span>  oei.data_id = bu.user_id                           
               <span>and</span> oei.node_left >= <span>2875</span>                           
               <span>and</span> oei.node_right 2875                           
               <span>and</span> oei.org_category = - <span>1</span>                
         <span>where</span>
            c.id = cb.contact_id           
      )        
   <span>order</span> <span>by</span>
      c.created_time <span>desc</span>  <span>limit</span> <span>0</span> ,
      <span>10</span>;
Empty <span>set</span> (<span>2</span> <span>min</span> <span>18.99</span> sec)
로그인 후 복사

2 min 18.99 sec！比之前的情况还糟糕很多。由于mysql的nested loop机制，遇到这种情况，基本是无法优化的。这条语句最终也只能交给应用系统去优化自己的逻辑了。 通过这个例子我们可以看到，并不是所有语句都能优化，而往往我们优化时，由于SQL用例回归时落掉一些极端情况，会造成比原来还严重的后果。所以，第一：不要指望所有语句都能通过SQL优化，第二：不要过于自信，只针对具体case来优化，而忽略了更复杂的情况。

慢查询的案例就分析到这儿，以上只是一些比较典型的案例。我们在优化过程中遇到过超过1000行，涉及到16个表join的“垃圾SQL”，也遇到过线上线下数据库差异导致应用直接被慢查询拖死，也遇到过varchar等值比较没有写单引号，还遇到过笛卡尔积查询直接把从库搞死。再多的案例其实也只是一些经验的积累，如果我们熟悉查询优化器、索引的内部原理，那么分析这些案例就变得特别简单了。

本文以一个慢查询案例引入了MySQL索引原理、优化慢查询的一些方法论;并针对遇到的典型案例做了详细的分析。其实做了这么长时间的语句优化后才发现，任何数据库层面的优化都抵不上应用系统的优化，同样是MySQL，可以用来支撑Google/FaceBook/Taobao应用，但可能连你的个人网站都撑不住。套用最近比较流行的话：“查询容易，优化不易，且写且珍惜！”

参考文献：
1.《高性能MySQL》
2.《数据结构与算法分析》

더 많은 MySQL 관련 기술 기사를 보려면 MySQL 튜토리얼 컬럼을 방문하여 알아보세요!

위 내용은 MySQL 인덱스 원칙 및 최적화의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

관련 라벨：
mysql 优化
원천：php.cn
이전 기사：MySQL 데이터베이스는 이모티콘 표현과 특수 기호를 저장합니다. 다음 기사：mysql 저장 프로시저에서 동적 SQL 문 사용
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
저자별 최신 기사
최신 이슈
결과 집합에서 가장 낮은 값만 표시하는 방법(MYSQL) 나는 다음과 같은 진술을 가지고 있습니다 : selectDATE(recieved_on)asDay,round(count(*)/24)AS'average'frommes...
에서 2024-04-06 21:44:19
0
1
603
MySQL SQL 쿼리를 사용하여 다른 테이블의 필드 합계를 계산합니다. 다음과 같은 스키마가 있습니다. "user_id" 및 "username" 속성이 있는 사용자 테이블과 "custome...
에서 2024-04-06 19:39:29
0
1
441
MySQL: "<여기 변수 또는 하위 쿼리>로...에서 텍스트 선택" 다음 데이터가 포함된 다음 테이블이 있습니다. id 텍스트 언어 1 독일어 텍스트 독일어 2 영어 텍스트 영어 내가 원하는 것은 다음 형식의 결과를 얻는 것입니다...
에서 2024-04-06 19:25:54
0
2
529
MySQL에서 그룹화하고 계산하는 방법은 무엇입니까? 친구에게 보낸 삭제되지 않은 메시지의 총 개수를 추출하는 쿼리를 작성하려고 합니다. 다음은 테이블 구조의 스크린샷입니다. 제가 달성하려는 출력은 다음과 같습니다....
에서 2024-04-06 18:30:17
0
1
353
MySQL은 여러 테이블에서 데이터를 가져옵니다. 다음 열이 포함된 eg_design 테이블이 있습니다. 다음 열이 포함된 eg_domains 테이블과 다음 열이 포함된 eg_fonts 테이블이 있습니다. 이제 ...
에서 2024-04-06 18:42:44
0
2
479
관련 주제
더>
인기 추천
인기 튜토리얼
더>
관련 튜토리얼
인기 추천
최신 강좌
최신 다운로드
더>
웹 효과
웹사이트 소스 코드
웹사이트 자료
프론트엔드 템플릿
회사 소개 부인 성명 Sitemap
PHP 중국어 웹사이트：공공복지 온라인 PHP 교육，PHP 학습자의 빠른 성장을 도와주세요！