MySQL高级

MySQL逻辑架构

mysql分层思想

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

1. 连接层：最上层是一些客户端和连接服务，包含本地socket通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全连接。服务器也回为安全接入的每个客户端验证它所具有的操作权限。
2. 服务层：第二层架构主要完成大多数的核心服务功能（如SQL接口），并完成缓存的查询，SQL的分析和优化以及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现（如过程、函数等）。在该层服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化（如确定查询表的顺序、是否利用索引等），最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

Management Serveices & Utilities	系统管理和控制工具
SQL Interface	SQL接口。接收用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
Parser	解析器。SQL命令传递到解析器的时候，会被解析器验证和解析
Optimizer	查询优化器。SQL语句在查询之前会使用查询优化器对查询进行优化，比如有where条件时，优化器会决定先投影还是先过滤
Cache 和 Buffer	查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存、记录缓存、key缓存以及权限缓存等

3. 引擎层：存储引擎层，存储引擎真正的负责MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。
4. 存储层：数据存储层，主要是将数据存数在运行于裸设备的文件系统上，兵完成于存储引擎的交互。

SQL大致的查询流程

1. MySQL客户端通过协议于MySQL服务器建立连接，发送查询语句，先检查缓存，如果命中则直接返回结果，否则进行语句解析，也就是说在解析查询之前，服务器会先访问查询缓存（query cache，存储select语句以及相应的查询结果集）。如果某个查询结构已经位于缓存中，服务器就不会再对查询进行解析、优化以及执行。它仅仅将缓存中的而结果返回给用户，这将大大提高系统的性能。
2. 语法解析器和预处理：首先MySQL通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询。预处理器则根据一些规则进一步检查解析树是否合法。
3. 查询优化器当解析树被认为是合法的后，由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
4. MySQL默认使用BTree（B树）索引，并且一个大致方向是：无论怎么折腾sql，至少目前来说，MySQL最多只用到表中的一个索引。

MyISAM引擎和InnoDB引擎对比

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整张表，不合适高并发的操作	行锁，操作时只锁某一行，不对其他行有影响，适合高并发的操作
缓存	只支持缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

索引优化分析

1. 慢SQL

性能下降、SQL慢、执行时间长、等待时间长的原因分析

1. 查询语句写得烂
2. 索引失效
3. 关联查询太多（设计缺陷或不得已的需求）
4. 服务器调优以及各个参数设置（缓存、线程数等）

2. join查询

2.1 SQL执行顺序

手写SQL顺序

MySQL实际执行SQL顺序

1. MySQL执行顺序：随着MySQL版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗，动态调整执行顺序。
2. MySQL常见查询顺序：

总结
MySQL执行SQL会先从from关键字执行

2.2 join连接查询

join查询图

-- 两表公共部分数据
select * from A inner join B on A.id = B.id;

-- 左表独有数据 + 两表共有数据
select * from A left join B on A.id = B.id;

-- 右表独有数据 + 两表共有数据
select * from A right join B on A.id = B.id;

-- 左表独有数据
select * from A left join B on A.id = B.id where B.id is null;

-- 右表独有数据
select * from A right join B on A.id = B.id where A.id is null;

-- MySQL不支持full join, 可用union代替, union用于合并结果集并自动去重, union all允许有重复值
-- 两表所有数据
-- select * from A full outer join B on A.id = B.id;
select * from A left join B on A.id = B.id
union
select * from A right join B on A.id = B.id;

-- 两表独有数据
-- select * from A full outer join B on A.id = B.id where A.id is null or B.id is null;
select * from A left join B on A.id = B.id where B.id is null
union
select * from A right join B on A.id = B.id where A.id is null;

3. 所有简介

3.1 索引是什么

索引是什么

1. MySQL官方定义：索引（index）是帮助MySQL高效获取数据的数据结构。索引的本质：索引是数据结构。
2. 简单理解：排好序的能快速查找的数据结构，即索引 = 排序 + 查找
3. 一般来说索引本身占用内存空间也很大，不可能全部存储在内存中，因此所有往往以文件形式存储在硬盘上
4. 常说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉树）结构组织的索引
5. 聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。除B+树这种类型索引外，还有哈希索引等。

3.2 索引优劣势

索引的优势

1. 提高数据检索的效率，降低数据库IO成本。类似图书馆的书目索引。
2. 利用索引列对数据进行排序，降低数据排序成本，降低CPU的消耗。

索引的劣势

1. 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占用空间的
2. 虽然索引大大提高了查询速度，同时会降低更新表的速度，如对表进行insert，update和delete操作。因为更新表时，MySQL不仅要保存数据，还要重新保存索引列字段（索引文件每次更新都要重新添加索引列字段，因为更新操作可能会改变键值）
3. 索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句

3.3 MySQL索引分类

1. 普通索引（单列索引）：最基本的索引，没有任何限制，即一个索引只包含单个列，一个表可以有多个单列索引。建议一张表索引不要超过5个，优先考虑复合索引
2. 唯一索引：该索引列的值必须唯一，但允许有空值。如果是复合索引，则列值的组合必须唯一
3. 主键索引：一个特殊的唯一索引，一张表只能有一个主键索引，不允许有空值。一般在建表的时候同时创建主键索引
4. 复合索引（组合索引）：指在多个字段上创建索引，只有在查询条件中使用了复合索引的第一个字段时，复合索引才会被使用。使用复合索引时遵循最左前缀集合
5. 全文索引（fulltext）：主要用来查找文本中的关键字，而不是直接于索引中的值相比较。fulltext索引跟其他索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配

3.4 MySQL索引语法

索引相关SQL语句

1. 创建索引：
   - 如果是char和varchar类型，length可以小于字段实际长度
   - 如果是blob和text类型，必须指定length

-- indexName: 索引名称，tableName：表名称，columnName：列名称
create [unique] index indexName on tableName(columnName(length));
alter tableName add [unique] index [indexName] on (columnName(length));

2. 删除索引

drop index [indexName] on tableName;

3. 查看索引

show index from tableName
-- \G表示将查询结果纵向输出，原结果是横向输出
show index from tableName\G

alter命令，4中方式添加索引

-- 添加主键索引
alter table tableName add primary key(columnName);

-- 添加唯一索引
alter table tableName add unique indexName(columnName);

-- 添加普通索引
alter table tableName add index indexName(columnName);

-- 添加全文索引
alter table tableName add fulltext indexName(columnName);

3.5 何时需要建索引

适合建立索引情况

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段
3. 查询中与其他表关联的字段
4. 查询中排序的字段
5. 查询中统计或分组字段
6. 高并发下倾向复合索引

不适合建立索引情况

1. 频繁更新的字段
2. where条件里用不到的字段
3. 数据重复且分布平均的字段
4. 表记录太少
5. 经常增删改的表

4. 性能分析

4.1 性能优化概述

MySQL Query Optimizer 的作用

1. MySQL中有专门负责优化select语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划（MySQL认为最优的数据检索方式，但不见得是DBA认为最优的，这部分最耗费时间）
2. 当客户端向MySQL 请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer 首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query中的Hint信息（如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint 或Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

MySQL 常见瓶颈

1. CPU瓶颈：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取的时候
2. IO瓶颈：磁盘IO瓶颈发生在装入数据远大于内存容量时
3. 服务器硬件的性能瓶颈：可通过top、free、iostat和vmstat来查看系统的性能状态

4.2 Explain概述

是什么

Explain是查看执行计划

1. 使用explain关键字可以模拟优化器执行sql语句，从而知道mysql是如何处理sql语句的。分析查询语句或结构的性能瓶颈
2. 官网地址

能干嘛

分析sql语句的信息

1. 表的读取顺序，id
2. 数据读取操作的操作类型，select_type
3. 哪些索引可以被使用，possible_keys
4. 哪些索引实际被使用，keys
5. 表之间的引用，ref
6. 每张表有多少行被优化器查询，rows

怎么玩

Explain + SQL语句

mysql> explain select * from tbl_emp;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | tbl_emp | ALL  | NULL          | NULL | NULL    | NULL |    8 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

4.3 Explain详解

id：select查询的序列号，包含一组数字，表示查询中执行select操作或子句的顺序

id取值的三种情况：

1. id相同，执行顺序由上至下
   

2. id不同，如果是在查询，id的序号会递增，id值越大优先级越高，越先被执行
   

3. id存在相同和不同，id相同的为一组，从上往下顺序执行。在所有组中，id值越大，优先级越高，越先执行。

select_type：查询的类型，主要用于区别普通查询、联合查询和字查询等复杂查询

1. SIMPLE：简单的 select 查询，查询中不包含字查询或者 union
2. PRIMARY：若查询为复杂的嵌套查询，最外层查询则被标记为 PRIMARY
3. SUBQUERY：在 select 或者 where 列表中包含的字查询标记为 SUBQUERY
4. DERIVED：在from列表中包含的字查询被标记为 DERIVED（衍生），MySQL会递归执行这些字查询，把结果放入临时表中
5. UNION：若第二个select出现在 union 之后，则被标记为 UNION。若 union 包含在 from 子句的字查询中，外层 select 将被标记为 DERIVED
6. UNION RESULT：从 UNION 表获取结果的 select

UNION 和 UNION RESULT 举例

explain
    -> select * from tbl_emp e left join tbl_dept d on e.deptId = d.id
    -> union
    -> select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;
+----+--------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------------+
| id | select_type  | table      | type | possible_keys | key        | key_len | ref       | rows | Extra                                              |
+----+--------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------------+
|  1 | PRIMARY      | e          | ALL  | NULL          | NULL       | NULL    | NULL      |    8 | NULL                                               |
|  1 | PRIMARY      | d          | ALL  | PRIMARY       | NULL       | NULL    | NULL      |    5 | Using where; Using join buffer (Block Nested Loop) |
|  2 | UNION        | d          | ALL  | NULL          | NULL       | NULL    | NULL      |    5 | NULL                                               |
|  2 | UNION        | e          | ref  | fk_dept_Id    | fk_dept_Id | 5       | db01.d.id |    1 | NULL                                               |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL       | NULL    | NULL      | NULL | Using temporary                                    |
+----+--------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------------+
5 rows in set (0.00 sec)

table：表示这行数据是关于哪张表的

type：表示查询使用了何种访问类型

1. type表示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fultext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

2. 一般来说，得保证查询至少达到 range 级别，最好能达到 ref

---

查询类型最好到最差排序（简略版）：system > const > eq_ref > ref > range > index > all

1. system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计

2. const：表示通过索引依次就找到了，const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于 where 列表中，MySQL 就能将查询转换为一个常量
   

3. eq_ref：唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
   

4. ref：非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
   

5. range：只检索给定范围的行，使用一个索引来选择行。若在where语句中出现between、<、>、in等关键字，这种查询的范围扫描比全表扫描要好，因为它只需要开始索引的某一点，而结束于另一点，不用扫描全部索引
   

6. index：Full Index Scan，index与all区别为index类型只遍历索引树。这通常比all快，因为索引文件通常比数据文件小。虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘数据库文件中读取的
   

7. all：Full Table Scan，通过遍历全表找到匹配的行（全表扫描）
   

8. 一般来说，得保证查询至少到达range级别，最好达到ref级别

possible_keys

1. 表示这张表可能使用到的索引，一个或多个
2. 若查询涉及的字段上存在索引，则该索引将被列出，但不一定被实际使用

key

1. 实际使用的索引，如果为null，表示没有使用索引
2. 如查询中使用了覆盖索引，则该索引仅出现在key列表中
   

key_len

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

ref

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

rows

1. 根据表统计信息及索引选用情况，大致估算出找到对应记录所需要读取的行数

extra：包含不适合在其他列中显示但十分重要的额外信息

1. Using filesort（文件排序）：
   - MySQL中无法利用索引完成排序操作称为“文件排序”
   - 说明MySQL对数据使用了一个外部索引排序，而不是按照表内索引进行读取
   - 出现次情况的sql非常影响效率（九死一生），需尽快优化
   

   • 上图第一个示例中，只是用了col1和col3，原有索引排派上用场，所以进行了外部文件排序
   • 上图第二个示例中，使用了col1、col2和col3，原有索引派上用场，无需进行文件排序

2. Using temporary（创建临时表）
   - 使用了临时表保存中间结果，MySQL在对查询结果排序时使用了临时表。常见于排序order by 和分组查询 group by
   - 出现此情况的sql及其不好（十死无生），需立即优化
   

   • 上图第一个示例中只是用了col1，原有索引派不上用场，创建了临时表进行分组
   • 上图第二个示例中使用了col1、col2，原有索引派上用场，无需创建临时表

3. 
   （覆盖索引）
   - 表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错
   - 如果同时出现Using where，表模索引被用来执行索引键值的查找
   - 如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作
   
   覆盖索引（Covering Index），也称为索引覆盖

   • 理解：就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列被索引覆盖
   • 注意：如果要使用覆盖所有，一定要注意select列表中只取出需要的列，不可以再用select *，因为将所有字段一起做索引会导致索引文件过大，查询性能下降

4. Using where
   - 表示使用了where过滤

5. Using join buffer
   - 表示使用了连接缓存

6. impossible where
   - where子句的值总是false，不能用来获取任何元组
   

7. select table optimized away
   - 在没有group by子句的情况下，基于索引优化min / max操作或者对于MyISAM存储引擎优化count(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

8. distinct
   - 优化distinct，在找到第一匹配的元组后即停止找同样值的工作

回顾练习

1. 第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name …】
2. 第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id, name from t1 where other_column= ’ '】
3. 第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】
4. 第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行【select name, id from t2】
5. 第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1, 4>表示用第一个和第四个select的结果进行union操作。【两个结果进行uinion操作】

5. 索引失效

5.1 索引失效准则

索引失效判断准则

1. 全值匹配我最爱
2. 最佳左前缀法则：如果索引为多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始且不跳过索引中的列
3. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
4. 存储引擎不能使用索引中范围条件右边的列
5. 尽量使用覆盖索引（之访问索引的查询（索引列和查询列一致）），减少select *
6. mysql 在使用不等于（!= 或者 <>）时无法使用索引而导致全表扫描
7. is null，is not null 也无法使用索引（早期版本不使用索引，后续版本可以）
8. like 以通配符开头（’%XXX…’）mysql索引会失效
9. 字符串不加单引号索引会失效
10. 使用 or 关键字 连接时索引会失效

5.2 索引失效总结

一般性建议

1. 对于单键索引，尽量选择针对当前query过滤性更好的索引
2. 在选择组合索引时，当前query中过滤性最好的字段位置越靠左越好
3. 在选择组合索引时，尽量选择能包含where子句中更多的字段
4. 尽可能通过分析统计信息和调整query的写法来达到选择何使索引的目的

索引优化总结

1. 全值匹配我最爱，最左前缀要遵守
2. 带头大哥不能死，中间兄弟不能断
3. 索引列上少计算，范围之后全失效
4. like百分写最右，覆盖索引不写*
5. 不等空值还有or，索引影响要注意
6. var引号不能丢，sql优化有诀窍
7. like后面以常量开头，比如 like ‘kk%’ 和 like ‘k%kk%’，可以理解为常量
   

查询截取分析

1. 查询优化

1.1 MySQL优化原则

mysql的调优大纲

1. 慢查询的开启并捕获
2. explain + 慢sql分析
3. show profile 查询sql在mysql服务器中的执行细节和生命周期
4. sql数据库服务器的参数调优

永远用小表驱动大表，类似嵌套循环 Nested Loop

1. exists 语法
   • select … from table where exists (subquery)
   • 该语法可以理解为：将查询的数据放入字查询中做条件验证，根据验证结果（true 或 false）来决定著查询的数据结果是否得以保留
2. exists（subquery）只返回 true 或 false，因此字查询中的 select * 也可以是 select 1 或其他，官方说法是实际执行时会忽略select清单，因此没有区别
3. exists 字查询的实际执行过程可能经过优化，而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题
4. exists 字查询往往也可以用条件表达式、其他字查询或者hoin代替，何种最优需要具体问题具体分析