关键词：

MySQL高级（一）——索引与查询优化

文章目录

• • MySQL高级（一）——索引与查询优化
• 一、MySQL逻辑架构
• • 常用存储对比(InnoDB与MyISAM)
• 二、杂七杂八的一些知识
• • 2.1 SQL慢的可能原因
  • 2.2 SQL解析过程
  • 2.3 JOIN理论
• 三、索引与B树
• • 3.1 什么是索引**？
  • 3.2 B树与B+树索引***！
  • 3.3 聚集索引和非聚集索引*
• 四、索引优化
• • 4.1 Explain性能分析*
  • 4.2 索引失效与避免**
  • 4.3 一些优化例子
• 五、其他查询优化

---

Java、大数据开发学习要点（持续更新中…）

---

一、MySQL逻辑架构

上图中MySQL最核心也是最出彩的地方是插件式的存储引擎架构将查询处理和其他系统任务和数据存储提取相分离。

常用存储对比(InnoDB与MyISAM)

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
锁	表锁	行锁(适合高并发)
缓存	只缓存索引	缓存索引和数据(缓存命中能提高读取效率)
关注点	管理非事务表，提供高速检索(select多的场景)	用于需要事务的表，提供ACID事务支持特性(insert、update多的场景)
表空间	小	大

二、杂七杂八的一些知识

2.1 SQL慢的可能原因

数据库性能下降，SQL查询慢的可能原因有如下：

1. 执行时间长：
   • 查询语句写的差
   • 索引失效(单值索引、复合索引)
2. 等待时间长：
   • 关联过多的join(不得已的需求)
   • 服务器优化不足(缓冲、线程数等)

2.2 SQL解析过程

服务器端对一条查询SQL的解析过程如下：(注意服务器端先关注FROM哪些表的哪些字段，后面才关注SELECT哪些字段)

2.3 JOIN理论

  注意图与SQL的对应关系：

  如果不支持FULL OUTER JOIN，可以用LEFT JOIN结果 UNION RIGHT JOIN结果，其中UNION合并并去重。
  同理，两个部分的独有可以通过两个子查询UNION到一起实现。
ps: union 和 union all 区别在于，union会合并后去重，而union all则得到非去重的结果

三、索引与B树

3.1 什么是索引**？

  索引是帮助MySQL(实际数据库都会做索引)高效获取数据的 数据结构。再进一步理解(注意两个关键字 排序 和 查找)：

												索引——排好序的快速查找数据结构

  在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。例如下面这样：

  一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

• MySQL索引分类：

1. 单值索引：一个索引对应单个列，一个表中可以有多个单列索引。
2. 复合索引：一个索引对应多个列。
3. 唯一索引：索引列的值必须唯一，允许为NULL。
4. 主键索引：以主键作为索引，不允许为NULL。

• 索引的优势：
  1. 提高数据检索的效率，降低IO成本。
  2. 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
• 索引的劣势：
  1. 索引更新会带来额外的延时。
  2. 索引膨胀后对存储空间要求变大，特别是内存（HBase就做了多级索引不一次性加载到哪内存中）。

扩展一些：HBase数据库索引是LSM树，保证数据在内存(Concurrent跳表)和磁盘上都是顺序存储的，检索时通过根索引节点快速定位到叶子索引节点(叶子索引就与对应Data Block相邻)。

3.2 B树与B+树索引***！

• B树
  
  B树每个节点是一个磁盘块(多个连续的磁盘页)，其中包含着数据和指向下一个磁盘块的多个指针，每个节点m个孩子这取决于磁盘页的大小。

  查找过程如下：首先将根磁盘块加载到内存中，通过二分查找，找到查找值的下一个磁盘指针，将对应的磁盘块加载到内存。如此往复，直到找到对应数据。这样磁盘IO次数小于等于B树高度次，大大减少了查找时磁盘IO次数。(实际是对整个关键字做一个全局的二分查找)

• B+树
  
  B+树除了叶子节点磁盘块存储关键字和数据外，其他节点磁盘块只存储关键字和指向下一个节点的指针(所有数据都保存在叶子节点)。叶子节点保存了所有关键字的数据，并且用链表按照关键字从小到大的顺序链接(双向链表)。

  首先，B+树的巨大出度使得其数的高度很矮，磁盘IO消耗少；其次，InnoDB实现中磁盘块的默认大小为16KB，而操作系统磁盘页一般为4KB，并且磁盘页读取有预读多个连续磁盘页的特性，由此，一次磁盘IO能够直接读取一个磁盘块，也是B+树磁盘IO少的重要原因。

• B+树 相对于 B树 的优势：

1. B+树空间利用率更高，相应的磁盘IO次数更少
   首先索引也是较大的，持久化在盘中，一次性读入内存是不明智的，由此存储在各个磁盘块上的索引是通过多次磁盘IO读入的。这是由于B+树非叶子节点存储的只是关键字和指向下一个节点的指针，比B树少存储了关键字对应的数据。因此，相同的磁盘块内，B+树能存储更多的关键字，一次读入内存的关键字更多。由此B+树高度会更小，对应磁盘IO会更少。
2. B+树范围查询(顺序遍历)性能好
   这是因为B+树叶子节点是一个顺序链表，范围查找根据范围起始头节点后就能通过链表顺序查询。
3. B+树查询稳定
   因为B+树从根节点到叶子节点的路径长度是固定的，查询到效率相当稳定(搜索都是在叶子节点结束)；而B树的搜索不稳定，会在非叶子节点结束。

3.3 聚集索引和非聚集索引*

  聚集索引和非聚集索引本质上是数据的物理存储方式。在InnoDB引擎中，主键是必须设置的，于是可以依赖主键生成聚集索引(非NULL的值唯一列)。
  聚集索引在B+树中存储，数据有序存储在叶子节点；而非聚集索引建立的是基于B+树的二级索引结构，叶子节点存储的是key+主键值。
  由此可知，非聚集索引的查找，需要先查找二级索引，再根据主键值到聚集索引所在的树再次查找，这么做磁盘IO较大，因此InnoDB也会用哈希索引来映射聚集索引和非聚集索引的对应关系。

四、索引优化

4.1 Explain性能分析*

  使用EXPLAIN关键字可以模拟优化器执行SQL查询语句(EXPLAIN + SQL语句)，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

  分析结果中有几个重要的字段对优化提供了方向：

• id：不同的id表示一趟独立的查询，id越大优先级越高（子查询id>外层查询id）。我们优化要做到不同id尽可能少，来减少独立查询的次数。

• type：访问类型，常见结果值从好到坏依次有system>const>eq_ref>ref>range>index>ALL

  • system：一行记录(系统表)，平常不会出现
  • const：常量，表示通过一次聚集索引查找就能找到数据，用于比较聚集索引(将主键放入WHERE条件中，比如WHERE id = 1，只匹配一行记录)。
  • eq_ref：聚集索引扫描，另一个表中只有一条记录与之匹配，常见于主键或唯一索引扫描(如WHERE t1.id = t2.id，只需要进行聚集索引的等值匹配)。
  • ref：非聚集索引扫描，返回另一个表中匹配的所有行(比如复合索引按照其中索引部分查找，需要非聚集索引的的等值匹配)。
  • range：只检索给定范围的行，用一个索引来选择行(一般就是WHERE中的BETWEEN、<、>、in)。
  • index：全索引扫描，要遍历索引树。(SELECT id FROM t1，也就是索引覆盖)

  一般来说保证查询至少达到range级别(索引范围查询)，最好能达到ref（等值查询）。

• possible_keys 、 key 、 key_len：理论可能用到的索引 、 查询实际使用的索引(索引失效的判别)、实际使用的索引总长度(在相同查询结果的前提下越小越好)。

• rows：每张表有多少行被优化器查询过(不断优化索引建立让rows越小越好)。

• Extra：给一些额外的信息。

  • Using FileSort：赶紧优化！查询、排序、分组都和索引相关，说明排序时索引失效，原因是排序未将索引条件使用完全，导致MySQL自己临时做了排序。
  • Using temporary：九四一生的情况！MySQL在查询结果排序的时候建立了临时表，常见于ORDER BY和GROUP BY。
  • Using index：好事！查询使用了覆盖索引，只需要读取索引就能够满足查询，根本不需要读取叶子节点的data了。(查询列被所建索引覆盖)

  这里告诉我们做索引也不能乱做，尽量保证复合索引的列在ORDER BY和GROUP BY中都被用到或者符合最左前缀原则，否则索引失效，面对排序和组合MySQL开始自己排序和建临时表来折腾，性能急剧下降。

4.2 索引失效与避免**

1. 建立复合索引优化查询时，要遵循最左前缀法则(查询从索引的最左前列开始并且不跳过索引中的列)。【从哪没遵循最左就从哪开始失效】

2. 索引列中不要做计算与类型转换，会导致转为全表扫描。

3. 复合索引中某个列的范围查询(BETWEEN、>、<)，右侧的索引失效【范围及前面的还有效】。

4. 不要用SELECT * ，尽量覆盖索引查询。

5. != 或 <> 、is null 或 is not null、like语句以通配符开头，如%abc均导致索引失效(看实际需求，实在要用该用还是用，但可以避免的尽量避免)

   问：那例如"%字符串%"的查询怎么避免索引失效？
   答：先分析问题，%开头没索引就是全表扫描了。解决方法就是为查询列建立查询的覆盖索引，就能优化全表扫描到index级别。(注意虽然索引都遍历了，但是没有实际去读B+树的data节点就完成了查询，比ALL级别快)。

6. 复合索引的查询条件是OR连接的，则索引失效(在最左前缀原则下OR后的失效)。要是单值索引查询用OR连接，则两个索引都失效。(改成两个SELECT进行UNION)
   
   上面1用到了a,b列索引，而余下的都是只用到了a列的索引

索引失效的具体分析

4.3 一些优化例子

1. SELECT id,author_id FROM article WHERE category_id = 1 AND comments>1 ORDER BY views DESC LIMIT 1;

   这么一个查询，第一次对其做一个category_id,comments,views的复合索引。发现type=range，索引也被用上了，但是Extra中出现了Using filesort.这是由于符合索引在comments范围查询后失效了，MySQL对views重新排序。优化方法是将范围查询的comments从索引中剔除。

2. 两表JOIN的索引优化SELECT * FROM class LEFT JOIN book ON class.card = book.card;

   不建索引两表都是全表扫描。由于两个表的映射字段非一一对应(也就是LEFT JOIN右边可能为空)，不清楚索引建立在哪个表上更好，那么就都试试看。结果表明，索引加在book.card上比索引加在class.card上优化不少(rows少)。这是由于左连接的特性决定的，左表一定全有，右表如何确定才是优化的方向，所以索引应当设置在右表（当然右连接同理）。

五、其他查询优化

• 小表驱动大表

• ORDER BY排序优化(尽量按照索引顺序排序，避免出现filesort的情况)：

  MySQL支持两种排序方式：FileSort和Index，Index通过扫描索引完成效率高，FileSort效率低。使用Index排序的前提是满足索引最左前缀原则。（要是左前缀是查询常量后面的也能排序）

  MySQL单路排序的问题：迫不得已的情况下需要filesort，单路查询将数据字段都放入缓存中进行排序，但会出现缓存放不下全部数据的情况，那么就分批次排序后归并。反而产生了大量的磁盘I/O。所以这里的优化点是，调节排序缓存的配置。
  

• GROUP BY分组优化(实际是先排序后分组，也遵循索引最左前缀原则)：

  有一点能在WHERE中搞定的不要去用HAVING(WHERE先过滤再对数据进行排序分组处理的数据量更小)

mysql高级-day01(代码片段)

Mysql高级-day01MySQL高级课程简介序号Day01Day02Day03Day041Linux系统安装MySQL体系结构应用优化MySQL常用工具2索引存储引擎查询缓存优化MySQL日志3视图优化SQL步骤内存管理及优化MySQL主从复制4存储过程和函数索引使用MySQL锁问题综合案例5... 查看详情

mysql高级(代码片段)

文章目录存储结构索引索引概述索引的优劣势索引的结构BTree结构B+Tree结构MySQL中的B+Tree结构==为什么MySql索引使用B+树====hash比B+查找时间更短，为什么索引不用hash？==索引分类（只... 查看详情

mysql索引使用与优化(代码片段)

前言索引对有一定开发经验的同学来说并不陌生，合理使用索引，能大大提升sql查询的性能，可以这么讲，随着业务数据量的不断增长，优化系统的响应速度，很大程度上可以说就是集中在索引的优化上&#x... 查看详情

mysql索引优化与查询优化（重点：索引失效的11种情况）(代码片段)

...爱干饭的猿，本文重点介绍MySQL的内连接、外连接、索引失效的11种情况、关联查询优化、子查询优化、排序优化、GROUPBY优化、优化分页查询、覆盖索引、索引条件下推和其它查询优化策略的一些问题。后续会继续分享MySQL和... 查看详情

理解mysql索引与优化(代码片段)

理解MySql索引与优化1、索引是什么？1.1mysql数据存储在什么地方？1.2查询数据比较慢，一般情况下卡在哪里？1.3去磁盘读取数据，用多少读多少？1.4索引存储在哪里？1.5索引在存储的时候需要什么信息&#... 查看详情

mysql查询优化(代码片段)

...法2.4.1原则2.4.2方法3.原理剖析3.1B+Treeindex3.2InnoDBTable3.3索引检索过程4.MySQL的行为4.1MySQLSQL执行过程4.2MySQLSQL执行顺序4.3MySQL优化器与执行计划4.3.1查询优化器4.3.2查询优化器工作过程4.3.3查看和干预执行计划4.3.4processlist5.常规优化... 查看详情

mysql高级第八篇：关联查询子查询和排序相关优化(代码片段)

MySQL高级第八篇：关联查询、子查询和排序相关优化一、关联查询的优化情况1：左外连接(和右外连接相似)情况2：内连接JOIN语句原理二、子查询优化三、排序相关优化问题：WHERE条件上加了索引，为什么还要... 查看详情

mysql高级学习笔记(代码片段)

文章目录第1章MySQL的架构介绍1.1MySQL简介1.2在Linux上安装MySQL1.3MySQL配置文件1.3MySQL逻辑架构介绍1.4MySQL存储引擎1.4.1查看命令1.4.2MyISAM和InnoDB第2章索引优化分析2.1概述2.1.1性能下降原因2.1.2常用的join查询2.1.2.1SQL执行顺序2.1.2.2join图2.... 查看详情

mysql高级--优化——创建索引原则使用场景和索引失效的情况(代码片段)

文章目录1、创建索引的原则1.1、最左前缀匹配原则1.2、=和in可以乱序1.3、选择`区分度`高的列作为索引1.4、索引列不能参与计算，保持列“干净”1.5、尽量的扩展索引，不要新建索引2、索引的使用场景2.1、匹配... 查看详情

mysql高级笔记-02(代码片段)

文章目录MySQL高级-笔记-02六.应用优化6-1使用连接池6-2减少对MySQL的访问6-3负载均衡七.Mysql中查询缓存优化7-1概述7-2操作流程7-3查询缓存配置7-4开启查询缓存7-5查询缓存SELECT选项7-6查询缓存失效的情况八.Mysql内存管理及优化8-1内存... 查看详情

mysql深入理解mysql索引优化器原理（mysql专栏启动）(代码片段)

...编/计算机底层原理/源码，就职于大型金融公司后端高级工程师，擅长交易领域的高安全/可用/并发/性能的架构设计与演进、系统优化与稳定性建设。 📫热衷分享，喜欢原创~关注我会给你带来一些不一样的认知... 查看详情

mysql性能优化(代码片段)

...据表分离2.1.4.数据库范畴的定义2.1.5.外键关系的建立2.1.6.索引的设计2.1.7.命名规范的定义2.2索引设计原则2.2.1存储引擎选择2.2.2索引列选择2.2.3索引的维护2.2.4索引使用的场景2.3SQL语句执行原理2.4数据库系统的调优策略三、查询优... 查看详情

mysql知识体系总结（sql优化篇）(代码片段)

...n返回列简介1、type常用关键字2、Extra常用关键字二、触发索引代码实例1、建表语句+联合索引2、使用主键查询3、使用联合索引查询4、联合索引，但与索引顺序不一致5、联合索引，但其中一个条件是>6、联合索引ÿ... 查看详情

mysql优化器之indexmergemulti-rangereadmrr与batchedkeyaccess使用案例详解(代码片段)

MySQL优化器之IndexmergeMulti-RangeRead MRR与BatchedKeyAccess介绍索引合并Indexmerge覆盖索引是在索引里即有查询时需要的字段，而在一个表上的查询往往条件会有多个组合且较为复杂，所以很难全部由覆盖索引包含到。那么此时我... 查看详情

mysql模糊查询like优化(代码片段)

1、使用全文索引MySQL之全文索引：https://blog.csdn.net/mrzhouxiaofei/article/details/79940958--直接查询400msSELECT*fromitemwheretitlelike'%小米%';--创建全文索引，然后使用全文索引35mscreatefulltextindexitem_title_f 查看详情

mysql索引优化与分析（重要）(代码片段)

建表SQLCREATETABLEstaffs(idINTPRIMARYKEYAUTO_INCREMENT,NAMEVARCHAR(24)NULLDEFAULT‘‘COMMENT‘姓名‘,ageINTNOTNULLDEFAULT0COMMENT‘年龄‘,posVARCHAR(20)NOTNULLDEFAULT‘‘COMMENT‘职位‘,add_timeTIMESTAMPNOTNULLDEFAULTC 查看详情

mysql索引与优化(代码片段)

内容包括：概念和基本使用索引的优缺点及使用场景索引底层结构(B,B+树及优缺点对比)高效使用索引聚簇索引与非聚簇索引概念索引存储在内存中，为服务器存储引擎为了快速找到记录的一种数据结构。基本操作为数... 查看详情

数据库索引原理及优化(代码片段)

一、摘要本文以MySQL数据库为研究对象，讨论与数据库索引相关的一些话题。特别需要说明的是，MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，哈希索引，全... 查看详情