PHP 三年模拟五年面试之一网打尽系列（4）----- MySQL 高级

Mysql 逻辑架构简介

和其它数据库相比，Mysql 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上，插件式的存储引擎架构及那个查询处理和其他的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择适合的存储引擎。

1. 连接层： 最上层是一些客户端和连接服务，包含本地 sock 通信和大多数基于客户端/服务端工具的实现的类似于 tcp/ip 的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基本的 SSL 的安全链接。服务器也会为安全接入的每个客户端验证他所具有的操作权限。
2. 服务层：第二层架构主要完成核心服务功能，如 SQL 结构，并完成缓存的查询，SQL 的分析和优化及布冯内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创捷相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作，如果是 select 语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
3. 引擎层：存储引擎层，存储引擎真正的负责了 Mysql 中数据的存储和提取，服务器通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
4. 数据存储层：主要是将数据存储在运行与裸设备的文件系统之上，并完成与存储引擎的交互。

myisam和innodb 对比

索引是什么 -—— 索引是一种数据结构

Mysql 官方对索引的定义为：索引 是帮助 Mysql 高效获取数据的数据结构。（排好序的快速查找数据结构）

索引的优势与劣势

优势

• 提高数据检索的效率，降低数据库的 IO 成本
• 对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗

  劣势

• 实际上索引也是一张表，该表保存了主键与索引字段，并只想实体表的记录，索引索引列也是要占用空间的
• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 insert、update 和 delete。
• 因为更新表时，Mysql 不仅要保存数据，还有保持一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
• 索引只是提高效率的一个因素，如果你的 Mysql 有大数据量的表，就需要花时间研究建立最优秀的索引，或优秀查询。

索引结构与检索原理

哪些情况适合建索引，哪些情况不适合？

需要

• 主键自动简历唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中其他表关联的字段，外键关系建立索引
• 查询中排序、统计或者分组字段

不需要

• 表记录太少
• 经常增删改的表
• 数据包含许多重复的内容

explain 使用简介

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

explain 之 id 介绍

• select 查询的序列号，包含一组数字，表锁查询中执行 select 子句或操作表的顺序
• id 相同，执行顺序由上至下
• id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，有先被执行
• id 相同不同，同时存在

explain 之 select_type 介绍

查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

• simple：简单的 select 查询，查询中不包含子查询或者 union
• primary：查询中若包含任何复杂的子部分，最外层查询则被标记为 primary
• subquery：在 select 或 where 列表中包含了子查询
• derived：在 from 列表中包含的子查询被标记为 derived ， Mysql 会递归执行这些子查询，把结果放在临时表中
• union：若第二个 select 出现在 union 之后，则被标记为 union；若 union 包含在 from 子句的子查询中，外层 select 将被标记为derived

explain 之 type 介绍

显示查询使用了何种类型，由好至坏的排序是：system > const > eq_ref > ref > range > index >all。 一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

• system：表只有一行记录，这是 const 类型的特例，平时不会出现，整个也可以忽略不计
• const： 表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因此之匹配一行数据，索引很快。如将主键置于 where 列表中，Mysql 就能将该查询转换为一个常量。
• eq_ref： 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见与主键或唯一索引扫描
• ref： 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，他返回索引匹配某个单独值的行，但是他可能会找出多个复合条件的行，所以他是属于查找和扫描的混合体。
• range：只检索给定范围的行，使用一个索引来选择行。key 列显示了哪个索引，一般就是你的 where 语句中出现了 between、<、>、in 等的查询，这种范围扫描索引扫描比全表扫描要好，因为他只需要开始与索引的某个点，结束与另一个。
• index：full index scan index 与 all 区别为 index 类型只遍历索引树，这通常比 all 快。
• all： full table scan，将遍历全表以找到匹配的行。

explain 之 possible_keys 和 key 介绍

possible_key 理论上可能用到的索引。key 实际使用的索引，如果为 null，则没有使用索引

explain 之 key_len 介绍

表示索引中使用的字节数，可通过该列计算查询中使用索引的长度。在不损失精确性的情况下，长度越短越好。key_len 显示的值为索引字段的最大可能长度，非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出来的。

explain 之 ref 介绍

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

explain 之 rows 介绍

根据表统计信息及所有的选用情况，大致估算出找到所需的记录所需要读取的行数。

explain 之 extra 介绍

包含不适合在其他列中显示但十分重要的额外信息，下面列举出几个比较常见的词。

• using filesort（九死一生）：说明 Mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。Mysql 无法利用索引完成的排序操作称为：文件排序。
• using temorary（十死无生）：使用了临时表保存中间结果，Mysql 在对查询结果排序时使用了临时表。常见于排序 order by 和分组查询 group by。
• using index（还不错）：表示相应的 select 操作中使用了覆盖索引，避免访问了表的数据行，效率不错；如果同时出现了 using where，表明索引被用来执行索引键值的查找；否则，表面索引用来读取数据而非执行查找动作。

索引覆盖的理解

• 就是 select 的数据列只用从索引中就能够取得，不必读取数据行，Mysql 可以利用索引返回 select 列表中的字段，而不必更加索引再次读取数据文件，换句话说 查询列要被所建的索引覆盖。
• 索引是高效找到行的一个办法，但是一般数据库也能使用索引找到一个列的数据，因此他不必读取整个行。毕竟索引叶子节点存储了他们索引的数据；当能通过读取所有就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做 索引覆盖。

索引失效的原因

• 是否符合最佳左前缀法则
• 是否在索引列做任何操作（计算、函数、自动手动类型转换）
• 存储引擎不能使用索引范围条件右边的列
• Mysql 在使用（!= <>)的时候无法使用索引会导致全表扫描
• is null, is not null 也无法使用索引
• like 以通配符开头（‘%abc...’) Mysql 索引失效会变成全表扫描的操作（只查询索引中的字段，"%ab%" 不失效）
• 字符串不加单引号索引失效（还可能使行锁变为表锁）
• 少用 or，用它来连接会索引失效

本作品采用《CC 协议》，转载必须注明作者和本文链接