MySQL高级笔记

一、MySQL逻辑架构

• **连接层：**最上层是一些客户端和连接服务，主要完成一些类似于连接处理、授权认证及相关的安全方案。在该层引入了线程池的概念，同样在该层上可以实现基于SSL的安全连接。
• **服务层：**第二层架构主要完成大多数的核心服务功能。在该层服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化，最后生成相应的执行操作。
• **引擎层：**存储引擎真正负责了MySQL中的数据的存储和提取，服务器通过API与存储引擎进行通信。
• 存储层

二、索引优化分析 （一）概述

索引（Index）：排好序的快速查找的数据结构；

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

• 优点：提高数据检索的效率，降低数据库的排序成本，降低CPU的消耗
• 缺点：降低了表的更新速度，因为除了保存数据，还要保存索引文件每次更新添加了索引列的字段

（二）索引分类

• 单值索引：一个索引只包含单个字段，一个表可以有多个单值索引；
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引：一个索引包含多个字段

基本语法：

# 创建
CREATE [UNIQUE] INDEX index_name ON mytable(column_name(length));
ALTER mytable ADD [UNIQUE] INDEX [index_name] ON (column_name(length));

# 删除
DROp INDEX [index_name] ON mytable;

# 查看
SHOW INDEX FROM mytable;

（三）索引结构

索引是在MySQL的存储引擎层中实现的，MySQL目前提供以下4种索引：

• BTREE 索引（InnoDB引擎只支持BTREE索引）
• HASH 索引
• R-tree 索引（空间索引）
• Full-text 索引（全文索引）

BTREE结构

BTREE又叫多路平衡树，一棵m叉的BTREE特性如下：

（四）性能分析EXPLAIN 1、概念

EXPLAIN可以模拟优化器执行sql查询语句，从而知道MySQL是如何处理sql语句的。

2、作用

• 查看表的读取和加载顺序；
• 数据读取操作的操作类型；
• 哪些索引可以使用；
• 哪些索引被实际引用；
• 表之间的引用；
• 有多少行被优化

3、使用方法

EXPLAIN + sql语句

4、字段解释

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra

（1）id

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

• id相同，执行顺序由上至下；
• 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；
• id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id越大，优先级越高，越先执行。

（2）select_type

• SIMPLE ：简单的select查询，查询中不包含子查询或者UNIOn；
• PRIMARY：查询中若包含任何复杂的子查询，最外层查询被标记为PRIMARY；
• SUBQUERY：在SELECT或WHERe列表中包含了子查询；
• DERIVED：在FROM列表中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，将结果放在临时表中；
• UNIOn：若第二个SELECt出现在UNIOn之后，则会被标记为UNIOn；若UNIOn包含在FROM子句的子查询中，外层SELECt将会被标记为DERIVED；
• UNIOn RESULT：从UNIOn表获取结果的SELECT。

（3）type

• ALL：全盘扫描；
• index：Full Index Scan ，遍历索引树；
• range：只检索指定范围的行，使用一个索引选择行；
• ref：非唯一性索引扫描，返回匹配某个单独值的所有行；
• eq_ref：唯一性扫描索引，对于每个索引键，表中只有一条数据与之匹配，常用于主键或唯一性索引；
• const：表示通过索引一次就能找到数据，const用于比较 PRIMARY KEY 或者 UNIQUE索引 ，比如将主键置于 WHERe 列表中，MySQL就能将该查询转换为一个常量；
• system：表只有一行记录（等于系统表）；
• NULL

从最好到最差依次是：system > const > eq_ref > ref > range > index > ALL

（4）possible_keys

显示可能应用在这张表上的索引，一个或多个

（5）key

实际使用的索引，如果为null，则没有使用索引

若查询中出现了覆盖索引，该索引会出现在key中

CREATE INDEX index_c1_c2 on t1 (c1,c2);

EXPLAIN SELECT c1,c2 FROM t1;
# 要查询的字段刚好就是索引定义的字段
# SELECt的数据列只用从索引中就能获取，不必读取数据行

（6）key_len

显示的值为索引字段的最大可能长度，并非实际长度。

（7）ref

显示查询中与其他表关联的字段，外键关系建立索引。

（8）rows

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

（9）Extra

• **using filesort：**MySQL会对数据使用一个外部的索引排序；
• **using temporary：**使用临时表保存中间数据；
• **using index：**表示相应的SELECT操作中使用了覆盖索引；如果同时出现using where，表明索引用来读取数据而非执行查找动作；
• using where：使用了where过滤；
• using join buffer：使用了连接缓存；
• impossible where：where子句的值总是false，不能用来获取任何元组；
• select table optimized away：在没有group by子句的情况下，基于索引优化min/max操作或者对于MyISAM存储引擎优化count操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化；
• distinct：优化distinct操作，在找到第一匹配的元组后即停止寻找同等值的操作。

（五）索引优化 避免索引失效

• 全值匹配；
• 最佳左前缀法则：如果索引了多列，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列；

CREATE INDEX idx_t1_nameAgeEmail ON t1 (name,age,email);

# 此时只会用到name索引
SELECT * FROM t1 WHERe name = 'tom' AND email = 'tom@qq.com';
# 从name开始，经过age，到email结束
SELECt * FROM t1 WHERe name = 'tom' AND age = 10 AND email = 'tom@qq.com';

• 不在索引列上做任何的操作（计算、函数、手动或自动的类型转换）；
• 存储引擎不能使用索引中范围条件右边的列；

# name和age都用到了，但是email没有用到
SELECt * FROM t1 WHERe name = 'tom' AND age > 10 AND email = 'tom@qq.com';

• 尽量使用覆盖索引，减少SELECt *；
• is null和is not null也会导致索引失效；
• like以通配符开头会导致索引失效；

# 以通配符开头会使索引失效
SELECT * FROM t1 WHERe name LIKE '%a';
SELECt * FROM t1 WHERe name LIKE '%a%';

# 尽量把通配符写在之后
SELECt * FROM t1 WHERe name LIKE 'a%';

如何解决索引失效的问题？：使用覆盖索引

• 字符串不加单引号会导致索引失效，相当于上文中的“不在索引列上做任何的操作，包括类型转换”；

• 少用OR。

三、查询截取分析 （一）查询优化 1、小表驱动大表

# 当B表的数据集小于A表的数据集时，用IN优于EXISTS
SELECt * FROM A WHERe id IN (SELECt id FROM B)；

# 当A表的数据集小于B表的数据集时，用EXISTS优于IN
# 将主查询的数据，放到子查询中做条件验证，根据返回的boolean值决定是否保留数据结果
SELECt * FROM A WHERe EXISTS (SELECt 1 FROM B WHERe B.id = A.id)；

2、ORDER BY优化

• 尽量使用index方式排序，避免使用filesort方式排序；
• 尽可能在索引列上完成排序，遵照索引的最佳左前缀原则；
• 如果不在索引列上，filesort会有两种算法：双路排序和单路排序。
  • 双路排序：两次扫描磁盘，最终得到数据；
  • 单路排序：按照order by在buffer对它们进行排序，因为保存在内存中了，所以会使用更多的空间。
    • 可以增大sort_buffer_size，max_length_for_sort_data参数的设置。

3、GROUP BY优化

• group by实质是先排序后进行分组，遵照索引的最佳左前缀原则；
• 能在where中限定的条件就不要写在having中；
• 当无法使用索引列时，可以增大sort_buffer_size，max_length_for_sort_data参数的设置。

（二）慢查询日志

慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time的sql，会被记录到慢查询日志中。

默认情况下，MySQL没有开启慢查询日志，如果不是调优需要，一般不建议开启。

# 查看是否开启
SHOW VARIABLES LIKE '%slow_query_log%';
# 开启日志
# 只对当前数据库生效，重启数据库之后就会失效
SET GLOBAL slow_query_log = 1;

# 查看相关参数
# 默认情况下，long_query_time为10秒，只有>10秒才会被记录到日志中
SHOW VARIABLES LIKE '%long_query_time%';

# 设置相关参数
SET GLOBAL long_query_time = 3;

# 查看日志中有几条慢查询记录
SHOW GLOBAL STATUS LIKE '%SLow_queries%';

（三）Show Profile

Show Profile是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优测量。

默认情况下，参数处于关闭状态，并保存最近15次的运行结果。

# 默认是关闭状态
SHOW VARIABLES LIKE 'profiling';
# 开启
SET profiling = on;

# 运行sql
SELECt * FROM t1;

# 查看结果
SHOW profiles;

# 诊断sql,取query_id为1的数据
SHOW profile cpu,block io FOR QUERY 1;

• ALL：显示所有开销信息；
• BLOCK IO：显示块IO相关开销；
• ConTEXT SWITCHES：上下文切换开销；
• CPU：显示cpu相关开销；
• IPC：显示发送和接收相关开销；
• MEMORY：显示内存相关开销；
• PAGE FAULTS：显示页面错误相关开销；
• SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息；
• SWAPS：显示交换次数相关开销。

结果要注意的要点：

1. converting HEAP to MyISAM查询结果太大，内存不够使用硬盘；
2. Creating tmp table创建临时表；
3. Copying to tmp table on disk把内存中临时表复制到硬盘中；
4. locked

四、MySQL锁机制 （一）概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，数据也是一种供许多用户共享的资源。锁冲突也是影响数据库并发访问性能的一个重要因素。

（二）锁的分类

从对数据操作的类型（读/写）分：

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响；
• 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的粒度分：

• 表锁
• 行锁

（三）表锁（偏读）

特点：

• 偏向MyISAM存储引擎，开销小，加锁快；
• 无死锁；
• 锁定力度大，发生锁冲突概率最高，并发度最低。

# 查看表上加过的锁
# in_use = 0 表示没有上锁
SHOW OPEN TABLES;

# 给t1上一把读锁，给t2上一把写锁
LOCK TABLE t1 READ,t2 WRITE;

# 释放表锁
UNLOCK TABLES;

读锁：

Session_1	Session_2
添加READ锁	连接终端
可以查询该表记录	可以查询该表记录
不可以查询其他没有锁定的表	可以查询其他没有锁定的表
插入或者更新锁定的表都会报错	插入或者更新会一直等待锁的释放
释放锁	获得锁，插入或者更新操作完成

写锁：

Session_1	Session_2
添加WRITE锁	连接终端
不可以查询其他没有锁定的表	可以查询其他没有锁定的表
可以查询+搜索+更新表记录	查询+搜索+更新表都会阻塞，等待锁被释放
释放锁	获得锁，操作完成

简而言之，就是读锁会阻塞写，而写锁会阻塞读和写。

# 表锁分析
SHOW STATUS LIKE 'table%';

table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数；

table_locks_waited：出现表级锁定争用而发生等待的次数。

MyISAM的读写锁是写优先，这也是MyISAM不适合做写为主表的引擎。因为写锁之后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永久堵塞。

（四）行锁（偏写）

特点：

• 偏向InnoDB存储引擎，开销大，加锁慢；
• 会出现死锁；
• 锁定力度小，发生锁冲突的概率最低，并发度最高。

InnoDB与MyISAM最大的不同有两点：

1. 支持事务（TRANSACTION）
2. 采用了行级锁

# 查看当前数据库的事务隔离级别
SHOW VARIABLES LIKE 'tx_isolation';
# 数据库默认是可重复读（Repeatable Read），避免了脏读和不可重复读，但会存在幻读

# 表锁分析
SHOW STATUS LIKE 'innodb_row_lock%';

**脏读：**session_2读到session_1修改还未提交的数据

# session_1
# 关闭自动提交
SET AUTOCOMMIT = 0;

# 将'jerry'修改为'tom'
UPDATe t1 SET name = 'tom' WHERe id = 3;

# session_2
# 关闭自动提交
SET AUTOCOMMIT = 0;

# 结果还是'jerry'
SELECT * FROM t1 WHERe id = 3;

session_1	session_2
更新但未提交	更新操作会一直阻塞
提交	阻塞消除，更新操作执行
	提交

如果session_1和session_2操作的不是同一条数据，那么session之间不会发生堵塞。

无索引行锁升级为表锁：

# 索引失效
UPDATE t1 SET name = 'tom' WHERe id = '3';

此时就算session_1和session_2操作的不是同一条数据，在session_1提交之前，session_2还是会进入阻塞状态。

间隙锁：

当我们用范围条件而不是相等条件检索数据时，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙（GAP）。InnoDB会对这个间隙加锁，就是所谓的间隙锁（Next-Key锁）。

# 此时t1中有id=1，3，4，5，6的数据，没有id=2的数据
# session_1
UPDATE t1 SET name = 'tom' WHERe id > 1 AND id < 6;

# session_2
INSERT INTO t1 VALUES (2,'allen');

此时session_2会进入阻塞状态，只有等session_1提交之后，session_2才会执行操作。

五、主从复制

MySQL的复制过程：

1. master将改变记录放到二进制日志中（binary log），这些记录过程叫做二进制日志事件（binary log events）；
2. slave将master的binary log events拷贝到他的中继日志（relay log）；
3. slave重做中继日志中的事件，将改变应用到自己的数据库中。

MySQL的复制是异步的且串行化的。

复制的基本原则：

• 每个slave只能有一个master；
• 每个slave只能有一个且唯一一个服务器ID；
• 每个master可以有多个slave。