MySQL调优

MYSQL 调优

使用mysql版本5.7

msyql安装：https://www.cnblogs.com/Zs-book1/p/11573203.html

使用数据库信息，参考mysql官网 sakila库

目录

• MYSQL 调优
• 一、 性能监控
  • 1.1 profile
    • 指定profile具体的type
  • 1.2 performance_schema
    • 1.2.1 performance_schema入门
    • 1.2.2 performance_schema库表分类
    • 1.2.3 performance_schema的简单配置与使用
    • 1.2.4 常用配置项参数说明
      • 1.2.4.1 启动配置项
      • 1.2.4.2 系统变量
      • 1.2.4.3 配置表相关
    • 1.2.5 performance_schema常用操作语句
  • 1.3 show process list
• 二、 schema与数据类型优化
  • 2.1 数据类型优化
  • 2.2 合理使用范式和反范式
    • 2.2.1 数据库设计三范式：
    • 2.2.2 反范式：
  • 2.3 主键的选择
  • 2.4 字符集的选择
  • 2.5 存储引擎的选择
  • 2.6 数据冗余
  • 2.7 数据拆分
• 三、 explain执行计划
• 四、索引优化
  • 4.1 索引基础知识
    • 4.1.1 什么是索引
    • 4.1.2 索引的数据结构
      • mysql数据检索过程：
      • 检索算法选择
      • 数据结构的选择
    • 4.1.3 聚簇索引和非聚簇索引
    • 4.1.4 索引的分类
    • 4.1.5 索引技术名词
    • 4.1.6 索引的匹配方式
  • 4.2 hash索引
  • 4.3 索引优化
  • 4.4 索引监控
• 五、查询优化
  • 5.1 查询慢的原因
  • 5.2 优化数据访问
  • 5.3 执行过程优化（todo）
    • 5.3.1 查询缓存
    • 5.3.2 查询处理的优化
    • 5.3.3 排序优化
      • mysql的排序算法：
  • 5.4 特定类型查询优化
    • 5.4.1 优化count()查询
    • 5.4.2 优化关联查询
      • 5.4.2.1 关联查询的原理
    • 5.4.3 优化子查询
    • 5.4.4 优化limit分页
    • 5.5.5 优化union查询
• 六、分区表
  • 6.1 分区表的应用场景
  • 6.2 分区表的限制
  • 6.3 分区表的原理
  • 6.4 分区表的类型
  • 6.5 分区表的使用
  • 6.6 分区表使用时需要注意的问题
• 七、服务器参数设置
  • 7.1 general
  • 7.2 character
  • 7.3 connection
  • 7.4 log
  • 7.5 cache(不建议使用)
  • 7.6 InnoDB
• 八、MySQL的锁
  • MySQL的锁机制
    • • 应用场景
  • MyISAM表锁
  • InnoDB行锁
    • 事物
    • InnoDB行锁：
    • InnoDB行锁实现方式
    • MVCC
      • MVCC实现原理
        • 隐式字段
        • undolog日志分为两种：
        • read view
        • MVCC的好处

一、 性能监控

mysql一条sql语句的执行过程如下：

1. 通过连接器：
   • 客户端与服务器建立通信，是半双工模式，也就是在某一固定时刻，只能由客户端向服务端请求或者服务端向客户端响应，而不能同时进行。
   • 权限验证。用户名密码验证以及表权限验证。
2. 缓存：mysql缓存主要作用是为了提高查询效率，但是在mysql5.6版本中已经默认关闭，推荐使用客户端缓存。详见缓存使用注意
3. 分析器：通过分析器，将sql语句解析称语法树，提取查询字段以及查询条件信息等。
4. 优化器：拿到语法树后，mysql会自动对sql进行优化，然后生成多个执行计划，然后根据各个执行计划的耗时，选择最优的方案，最终交给执行器。优化过程如下：有多个索引可以使用，会自动一个最优的索引使用。组合索引书写时没有按照顺序书写，但是经过优化器后会将条件按照组合索引的顺序排列。
5. 执行器：调用存储引擎api，查询表数据。

1.1 profile

通过profile可以查看mysql在各个阶段的执行耗时，然后可以根据耗时去优化。profile在mysql5.7仍然支持，但是在后续mysql版本升级可能会删除。使用方式如下：

• 此工具默认是关闭的，通过set profiling = 1可以在会话级别(只在当前窗口生效)打开监控。
• 当上面的设置完成后，那么在服务器上执行的所有语句，都会测量其耗费的时间和其他的一些查询执行状态变更数据。可以通过show profile查看最近一次执行的sql的执行耗时。

当执行多条sql时，可以通过show profiles来查看最近执行的多条sql结果。如果想要看其中一条的明细，那么可以通过show profile for query QUERY_ID来查看

指定profile具体的type

profile有以下类型：

• ALL：显示所有信息。显示下面所有类型show profile all for query 1;

  

• BLOCK IO：显示块IO操作次数（块IO操作次数越少越好）。show profile block io for query 1;

  

• CONTEXT SWITCHES：显示上下文切换次数。show profile context switches for query 1;

  

• CPU：显示用户cpu的时间，系统cpu的时间。

• IPC：显示发送和接收的消息数量。

• MEMORY：还未实现，计划实现。

• PAGE FAULTS：显示也错误数量。

• SOURCE：显示源码中的函数名称与位置

• SWAPS：显示swaps的次数。

上面类型中，主要关心的类型一般为CPU,BLOCK IO

1.2 performance_schema

performance schema用于监控MySQLserver 再一个较低级别的运行过程中的资源消耗，资源等待等情况。具有如下特点：

• 提供一种在数据库运行时，实时检查数据库内部执行情况的方法。performance_schema库中的表使用performance_schema存储引擎，该数据库主要关注数据库运行过程中的性能相关数据。与information_schema不同，information_schema主要关注server运行过程中的元数据信息。
• performance_schema通过监视server的事件来实现监视server内部运行情况，"事件"就是server内部活动中所做的任何事情以及对应的时间消耗，利用这些信息来判断server中的相关资源消耗在了哪儿。一般来说，事件可以是函数调用，操作系统的等待，sql语句执行的阶段或者整个sql语句与sql语句集合。事件的采集可以方便的提供server中的相关存储引擎对磁盘文件，表IO，表锁等资源的同步调用信息
• performance_schema中的事件与写入二进制日志中的事件（描述数据修改的事件）和事件计划调度程序的事件不同。performance_schema中的事件记录的是server执行某些活动对某些资源的消耗、耗时、这些活动执行次数等情况。
• performance_schema中的事件只记录在本地server的performance_schema中，其下的这些表的数据发生变化时不会被写入binlog日志中，也不会通过复制机制被复制到其他的server中。(在主备或者主从模式下，该库下的数据不会被同步，只做本地保留)
• 收集的事件数据存储在performance_schema数据库的表中。这些表可以使用select语句查询，也可以使用sql语句更新表中的记录（动态修改配置表中的配置，更改配置会立即生效，会影戏那个数据收集）。
• performance_schema表中的数据不会持久化到磁盘，而是保存在内存中，一旦服务器重启，表中的数据会全部丢失(包括修改的配置表数据)。

1.2.1 performance_schema入门

在mysql 5.7版本中，性能模式是默认开启的，如果想要显示关闭的话需要修改配置文件，不能直接进行修改，会报错。修改此变量需要在mysql配置文件my.cnf中进行修改。

[mysqld]
performance_schema=OFF

使用performance_schema:

-- 切换performance_schema
use performance_schema;
-- 查看库下所有表
show tables;
-- 查看表接口
show create table setup_consumers;

可以看到表存储引擎为PERFORMANCE_SCHEMA。

performance_schema库主要有两个概念：

• instruments：生产者，用来采集mysql中各种各样的操作产生的事件信息，对应配置表中的配置项我们可以成为监控采集配置项
• consumers： 消费者，对应的消费者表用来存储来自instruments采集的数据，对应配置表中的配置项称之为消费存储配置项。

1.2.2 performance_schema库表分类

performance_schema库下的表按照监听维度可以分为以下类别：

-- 事件语句记录表
show tables like '%statements%';
-- 等待事件记录表
show tables like '%wait%';
-- 阶段事件记录表
show tables like '%stage%';
-- 事物事件记录表
show tables like '%transaction%';
-- 监控文件系统层调用的表
show tables like '%file%';
-- 监控内存使用的表
show tables like '%memory%';
-- 动态对performance_schema进行配置的配置表
show tables like '%setup%'

事件相关分类下包含如下表：

mysql> show tables like '%wait%';
+-----------------------------------------------+
| Tables_in_performance_schema (%wait%)         |
+-----------------------------------------------+
| events_waits_current                          |
| events_waits_history                          |
| events_waits_history_long                     |
| events_waits_summary_by_account_by_event_name |
| events_waits_summary_by_host_by_event_name    |
| events_waits_summary_by_instance              |
| events_waits_summary_by_thread_by_event_name  |
| events_waits_summary_by_user_by_event_name    |
| events_waits_summary_global_by_event_name     |
| table_io_waits_summary_by_index_usage         |
| table_io_waits_summary_by_table               |
| table_lock_waits_summary_by_table             |
+-----------------------------------------------+
12 rows in set (0.00 sec)

• *_current：当前时间表
• *_history： 历史事件表
• *_history_long： 长语句历史事件表
• *_summary_*：聚合摘要表。聚合摘要表还可以根据account账号、host主机、user用户、program程序、thread线程、global全局在进行细分

1.2.3 performance_schema的简单配置与使用

数据库刚刚初始化并启动时，并非所有的intruments和consumers都启动了，所以默认不会收集所有的事件。可以通过setup_instruments和setup_consumers表查询配置是否打开。select * from setup_instruments;

如果需要检测的事件没有打开，可以通过如下语句打开配置：

update setup_instruments set enabled = 'YES', timed = 'YES' where name like '%wait%';
update setup_consumers set enabled = 'YES' where name like '%wait%';

打开生产者配置时也要打开消费者配置。

通过current表可以看到当前server正在做什么，该表中每个线程只包含一条数据，显示每个线程的最新监视事件。

• thread_id: 线程id
• enent_id：事件id
• event_name：事件名
• timer_start：事件开始事件
• timer_end： 事件结束时间
• timer_wait：事件总耗时

current表中每个线程只包含一条记录，一旦线程完成工作，该表中不会记录线程事件信息。
history表中记录每个线程执行完成的事件信息。但是每个线程只会记录10条，再多就会被覆盖。
history_long表中记录所有线程事件信息，但是总记录数时10000，超过就会被覆盖。
summary表提供所有事件汇总信息。
instance表记录哪儿些类型的对象会被检测，这些对象在被server使用时，会在该表中生成一条事件记录。

1.2.4 常用配置项参数说明

1.2.4.1 启动配置项

• performance_schema_consumer_events_statements_current=TRUE：是否在mysql启动时开启events_statements_current表记录功能。其他表开启配置方式于此类似，如：

  performance_schema_consumer_events_statements_history=TRUE
  performance_schema_consumer_events_stages_history_long=TRUE
  

• performance_schema_instrument=[name]: 是否在服务启动时就启用某些收集器。

  由于收集器较多，因此支持key-value的方式配置，如下：

  performance-schema-instrument= 'instrument_name=value'
  

  还可以通过通配符开启多个配置：

  performance-schema-instrument='wait/synch/cond/%=COUNTED'
  

1.2.4.2 系统变量

可以通过show variables like '%**%'查询到的变量。关于performance_schema比较重要的变量有：

• performance_schema=ON：开启mysql事件收集功能，启用performance_schema库。
• performance_schema_digests_size=10000：控制events_statements_summary_by_digest表中的最大行数。如果产生的语句摘要信息超过此最大值，便无法继续存入该表，此时performance_schema会增加状态变量
• performance_schema_events_statements_history_long_size=10000：控制events_statements_history_long表中能够存放的总记录数，超过这个限制后，最早记录将被覆盖。
• performance_schema_events_statements_history_size=10：控制events_statemetns_history表中的单个线程的最大行数，超过这个限制，每个线程最早记录将被覆盖。
• performance_schema_max_digest_length=1024：用于控制标准化形式的sql语句文本存入performance_schema时的限制长度，该变量与max_digest_length变量相关。
• performance_schema_max_sql_text_length=1024：控制存入events_statements_current,event_statements_history,event_statements_history_long语句事件表中的sql_text列的最大长度子结束，超出限制的部分将被丢弃，不会记录，一般情况下不会调整该变量。

1.2.4.3 配置表相关

• performance_timers： timers表中记录了server中有哪儿写可用的事件计时器。
• setup_timers：记录当前使用的事件计时器信息
• setup_consumer：事件消费者相关配置
• setup_instruments： 事件生产者相关配置
• setup_actors：匹配任何用户和主机，因此对于所有前台线程，默认情况下启用监视和历史事件收集功能。
• setup_objects：控制performance_schema是否监视特定对象，默认情况下，最大行数100行。
• threads：对每个server线程生成一行包含线程相关的信息。

1.2.5 performance_schema常用操作语句

• 哪儿类sql执行最多：

  select DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC;
  

• 哪儿类sql平均响应时间最长

  SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
  

• 哪儿类sql排序记录数量最多

  SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
  

• 哪儿类sql扫描记录数最多

  SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
  

• 哪儿类sql使用临时表最多

  SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
  

• 哪儿类sql返回结果集最多

  SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
  

• 哪儿个物理表IO最多

  SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC
  

• 哪儿个表逻辑IO最多

  SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC
  

• 哪儿个表索引访问最多

  SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC
  

• 哪儿个索引从来没用过

  SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;
  

• 哪儿个等待事件消耗时间最多

  SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC
  

• 剖析某条sql的执行情况，包括statement信息，stege信息，wait信息。

  • 查询sql事件id

    SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%select%';
    

  • 查看每个阶段的时间消耗

    SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;
    

  • 查看每个阶段的锁等待情况

    SELECT  event_id, event_name, source, timer_wait, object_name, index_name, operation, nesting_event_id FROM events_waits_history_long WHERE nesting_event_id = 1553;
    

1.3 show process list

使用show processlist查看连接的线程个数，来观察是否有大量线程处于不正常的状态或者其他不正常的特征。

• Id：表示sessionId
• User：表示操作的用户
• Host：表示操作的主机
• db：表示操作的数据库
• Command：当前命令状态
  • Sleep：线程正在等待客户端发送新的请求
  • Query：线程正在执行查询
  • Locked：线程在等待表锁
  • Analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划
  • Copying to tmp table：线程正在执行查询，并将结果集都复制到临时表中
  • Sorting result：线程正在对结果集进行排序
  • Sending data：线程可能在多个状态之间传递数据，或者在生成结果集，或者想客户端返回数据
• Time：上面命令的执行时间
• State：表示命令执行的状态
• Info：执行的sql语句。

二、 schema与数据类型优化

2.1 数据类型优化

1. 更小的通常更好。

   尽量选择可以正确存储数据的最小的数据类型。更小的数据类型意味着更快，他们占用更少的磁盘，内存以及cpu缓存，并且需要的cpu时钟周期更少。但是要确保没有低估需要存储值的范围。如果无法确认哪儿个数据类型。就选择你认为不会超过范围的最小类型。

2. 简单就好。

   简单的数据类型通常需要更少的cpu时钟周期。例如：

   • 整型比字符串操作代价更低，因为字符集校验和校对规则是字符比较 比 整型比较更复杂。
   • 使用mysql自建类型存储时间和日期而不是字符串
   • 用整型存储ip地址。

3. 尽量避免null

   如果查询中包含可为null的列，对mysql来说更难优化。因为可为null的列使得索引、索引统计和值比较都更为复杂。将null的列改为not null带来的性能提升比较小，因此没必要将库中的所有类型都进行修改，但是应尽量避免设计为null类型（如果可以，最好使用默认值替换null）。

4. 数据类型的选择。

   • **整型 **：可以使用的整形类型有tinyint, smallint, mediumint, int, bigint分别使用8，16，24，32，64位存储空间。尽量使用满足需求的最小的数据类型

   • **子符和字符串选择 **：

     • char类型长度固定，最大长度255个字符。会自动删除尾部的空格。每条数据占用等长的空间（例如char(5)无论存储的值是长度是否到5，最终存储的空间都会占5个字符），检索效率、写效率比varchar高（空间换时间），适用于身份证，手机号等定长字段。

     • varchar长度可变，最大空间65535个字节，适合长度不固定的。

     • text不设置长度，不知道长度时可以使用text。

     根据越小越快原则，查询速度char>varchar>text

   • **blob和text类型 **：两者都是为了存储很大的数据而设计的字符串类型，分别适用二进制和字符存储。

   • **datetime和timestamp **：

     • datetime: 占用8个字节。与时区无关，数据库底层时区配置对datetime无效。可保存到毫秒。可保存时间范围大。
     • timestamp：占4个字节。采用整型存储。依赖数据库设置的时区。可保存到秒。时间范围：1970-01-01～2038-01-19。自动更新timestamp列的值。
     • date：占用三个字节。可保存范围大，可以表示1000-01-01～9999-12-31之间的值。

     不要使用字符串来保存日期格式，占用空间大，而且损失日期类型函数便捷性。

     使用datetime和date保存时，可以使用日期时间函数进行计算

   • 使用枚举替代字符串类型：有时可以使用枚举替代常用的字符串类型。mysql存储枚举类型会非常紧凑，会根据列表值的数据压缩到一个或两个字节中，mysql内部会根据每个值在列表中的位置保存为整数，并且在表的.frm文件中保存数字-字符串映射关系的查找表。

     create table enum_test(fruit enum('apple','pear','orange') not null);
     insert into enum_test values ('apple'),('pear'),('orange');
     

     

     

   • 特殊类型数据：存储ip地址时，一般都会使用varchar来存储，但是ip地址的本质是无符号整数，可以使用整数类型。通过inet_aton()和inet_ntoa()函数来进行转换。

     

2.2 合理使用范式和反范式

2.2.1 数据库设计三范式：

1. 每一列属性都是不可再分割的属性，确保每一列原子性。两列属性相近或相似时，尽量合并属性一样的列，保证不产生数据冗余。
2. 每一行的数据只能与其中一列相关，如果出现重复就要把数据拆分出来。
3. 数据不能存在传递关系，每个属性都跟主键又直接关系而不是间接关系。

优点：

• 范式化的更新通常比反范式更快
• 范式化较好的数据，很少或没有重复数据
• 范式化数据较小，可以存放在内存中，操作比较快。

缺点： 通常都需要关联表

2.2.2 反范式：

就是在某些情况下，为了提高效率，违反三范式的原则的设计。允许数据冗余。

优点：

• 所有数据都在一张表中，可以避免关联
• 可以设计有效的索引

缺点：表内数据冗余较多，删除数据时候会造成表有些有用的信息丢失

在企业中很少做到严格的范式或反范式，一般都是混合使用。

2.3 主键的选择

1. 代理主键： 与业务无关的无意义的数字序列，比如自动增长的id。
2. 自然主键： 事物属性中的自然唯一标识，就是充当主键的字段本身具有一定的含义。例如学生学号，员工工号，是构成记录的一部分。

推荐使用代理主键，因为他们不与业务耦合，更容易维护。而且通过的主键策略，能够减少需要编写的代码，提高效率。

2.4 字符集的选择

1. 纯拉丁字符能表示的内容，没必要选择latin1之外的其他字符编码，因为这会节省大量的存储空间。
2. 如果确定不需要存放多种语言，没必要非得使用utf8或者unicode的其他编码，这会造成大量的空间浪费。
3. msyql的数据类型可以精确到字段，所以当需要对大型数据库存放多字节数据时，可以通过对不同表的不同字段指定不同的数据类型来减少数据存储量，进而降低IO操作次数并提高命中率。

2.5 存储引擎的选择

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。可以通过执行show engines;来查看mysql支持的存储引擎。

• ARCHIVE： Archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就开始支持索引了。Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当做仓库使用。

  **场景 **：由于高压缩和快速插入的特点Archive非常适合作为日志表的存储引擎，但是前提是不经常对该表进行查询操作。

• BLACKHOLE(黑洞引擎)：该存储引擎支持事务，而且支持mvcc的行级锁，写入这种引擎表中的任何数据都会消失，主要用于做日志记录或同步归档的中继存储，这个存储引擎除非有特别目的，否则不适合使用。

  **场景 **：如果配置一主多从的话，多个从服务器会在主服务器上分别开启自己相对应的线程，执行binlogdump命令而且多个此类进程并不是共享的。为了避免因多个从服务器同时请求同样的事件而导致主机资源耗尽，可以单独建立一个伪的从服务器或者叫分发服务器。

• MRG_MYISAM: 此引擎是一组MyISAM表的组合，这些MyISAM表结构必须完全相同，尽管其使用不如其它引擎突出，但是在某些情况下非常有用。说白了，Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。

  场景：对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关。例如：可以用12个相同的表来存储服务器日志数据，每个表用对应各个月份的名字来命名。当有必要基于所有12个日志表的数据来生成报表，这意味着需要编写并更新多表查询，以反映这些表中的信息。与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询，之后再删除Merge表，而不影响原来的数据，删除Merge表只是删除Merge表的定义，对内部的表没有任何影响。

• FEDERATED: 该存储引擎可以不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。这种存储引擎非常适合数据库分布式应用。

• MyISAM：MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物和外键。MyISAM主要特性有：

  1. 大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持

  2. 当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成

  3. 每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16

  4. 最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上

  5. BLOB和TEXT列可以被索引，支持FULLTEXT类型的索引，而InnoDB不支持这种类型的索引

  6. NULL被允许在索引的列中，这个值占每个键的0~1个字节

  7. 所有数字键值以高字节优先被存储以允许一个更高的索引压缩

  8. 每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快

  9. 可以把数据文件和索引文件放在不同目录

  10. 每个字符列可以有不同的字符集

  11. 有VARCHAR的表可以固定或动态记录长度

  12. VARCHAR和CHAR列可以多达64KB

  存储格式：

  1. 静态表（默认）：字段都是非变长的（每个记录都是固定长度的）。存储非常迅速、容易缓存，出现故障容易恢复；占用空间通常比动态表多。

  2. 动态表：占用的空间相对较少，但是频繁的更新删除记录会产生碎片，需要定期执行optimize table或myisamchk -r命令来改善性能，而且出现故障的时候恢复比较困难。

  3. 压缩表：使用myisampack工具创建，占用非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。

  静态表的数据在存储的时候会按照列的宽度定义补足空格，在返回数据给应用之前去掉这些空格。如果需要保存的内容后面本来就有空格，在返回结果的时候也会被去掉。（其实是数据类型char的行为，动态表中若有这个数据类型也同样会有这个问题）

  使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）。

  场景：如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。

• PERFORMANCE_SCHEMA: 此引擎是mysql性能监控库performance_schema使用的引擎。可以通过show create table performance_schema库下表名;查看到performance_schema库下的表都是使用的此存储引擎。用户不能创建使用此存储引擎的表。

• InnoDB: InnoDB是事务型数据库的首选引擎，InnoDB是目前MYSQL的默认事务型引擎，是目前最重要、使用最广泛的存储引擎。支持事务安全表（ACID），支持行锁定和外键。InnoDB主要特性有:

  1. InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合。
  2. InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的
  3. InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上
  4. InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键
  5. InnoDB被用在众多需要高性能的大型数据库站点上

  InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。

  场景：由于其支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

• MEMORY: 使用此存储引擎的表数据都存放在内存中。默认使用hash索引，比B+树快，速度快。但是也因为数据存放在内存中，因此不稳定。随着现在内存数据库redis，mongodb的流行，此引擎使用场景越来越少。

  场景：查询用临时表

• CSV: 使用该引擎的MySQL数据库表会在MySQL安装目录data文件夹中的和该表所在数据库名相同的目录中生成一个.CSV文件（所以，它可以将CSV类型的文件当做表进行处理），这种文件是一种普通文本文件，每个数据行占用一个文本行。该种类型的存储引擎不支持索引，即使用该种类型的表没有主键列；另外也不允许表中的字段为null。csv的编码转换需要格外注意。

  场景：这种引擎支持从数据库中拷入/拷出CSV文件。如果从电子表格软件输出一个CSV文件，将其存放在MySQL服务器的数据目录中，服务器就能够马上读取相关的CSV文件。同样，如果写数据库到一个CSV表，外部程序也可以立刻读取它。在实现某种类型的日志记录时，CSV表作为一种数据交换格式，特别有用。

以上内容引用自Mysql存储引擎

上面的九种存储引擎，目前常用的就是MyISAM和InnoDB，其他的了解就好。

2.6 数据冗余

适当的数据冗余可以提高查询效率，其实也是反范式的一种体现。主要用在被频繁引用且只能通过join两张(或者更多大表)的方式才能获得的小字段。由于每次要查询的只是小字段，join大表查到的记录比较大，会造成大量不必要的io，通过空间换时间的方式进行优化，但是冗余的同时需要保证数据一致性不被破坏，确保更新时冗余的字段也会被更新。

2.7 数据拆分

当我们的表中存在类似text或者很大的varchar类型的大字段时，如果我们大部分访问这张表的时候都不需要这个字段，我们就应该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少了物理IO次数，也能大大提高内存中的缓存命中率。

三、 explain执行计划

要想对sql进行调优，就需要知道sql的执行过程。可以使用explain来查看sql的执行计划，通过分析执行计划来对sql进行相应优化，提高sql执行速度。

使用方式explain 待执行sql。例如

主要有以下几个字段：

• id: 执行序列号

  可以分为以下三种情况：

  1. id号相同，执行顺序从上到下

     

  2. id号不同，id越大优先级越高，越先被执行

     

  3. 当同时存在id相同和不同的时，按照id进行分组，id越大的优先级越高，相同id的按照顺序从上到下执行。

     explain select *
     from customer cust
              join address a on cust.address_id = a.address_id
     where a.city_id = (select city_id from city where city = 'Stockport');
     

• select_type: 主要用来分便查询的类型。

  select_type	meaning
  SIMPLE	简单的查询，不包含子查询和UNION
  PRIMARY	主查询，查询中包含任何复杂自查询，最外层被标记为Primary
  SUBQUERY	子查询，在select或者where列表中包含子查询
  DEPENDENT SUBQUERY	subquery的子查询要受到外部表查询的影响
  UNION	包含union查询，当为union all时
  DEPENDENT UNION	跟union类似，此处的depentent表示union或union all联合而成的结果会受外部表影响
  UNION RESULT	从union表获取结果的select
  DERIVED	from子句中出现的子查询，也叫做派生类
  UNCACHEABLE SUBQUERY	表示使用子查询的结果不能被缓存
  UNCACHEABLE UNION	表示union查询的结果不能被缓存

  # simple
  explain select * from city;
  # primary(一般与子查询subQuery同时出现)
  explain select * from address a where a.city_id = (select city.city_id from city where city_id = 50);
  # dependent subQuery
  explain select * from address where address_id in (select distinct address_id from customer);
  # union
  explain select * from city where city_id = 20 union all select * from city where city_id = 30;
  # union result
  explain select * from city where city_id = 50 union select * from city where city_id < 30;
  # dependent union
  explain select * from city where city_id in (select city_id from city where city_id = 50 union select city_id from city where city_id < 30);
  -- DERIVED及后面的未演示出来
  

• table: 对应正在访问哪儿张表或表别名，可能是临时表或union结果集

  1. 如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名
  2. 表名是derivedN的形式，表示使用了id为N的查询产生的衍生表
  3. 当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id

• partitions: 表分区，当使用表分区时才能看到。

• type: type表示访问类型，以哪儿种方式访问数据。不同的访问类型效率也不同，效率从好到坏依次是

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

  • system: 表只有一行记录（等于系统表），这是const类型的特例，平时不会出现

  • constant: 表中最多有一个匹配行(当使用主键或唯一索引匹配时)

    explain select *  from city where city_id =1;
    

  • eq_ref: 使用唯一索引进行数据查找，对于每个来自前面表的记录，从该表中读取唯一一行

    explain select * from staff join store s on staff.staff_id = s.manager_staff_id
    
    

  • ref: 使用非唯一性索引进行查找

    explain select * from address where city_id = 1;
    

  • fulltext: 全文索引，只有在MyISAM引擎下才能用。

  • ref_or_null: 对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式

    create index idx_release_year on film(release_year);
    explain select * from film where release_year = year(2006) or release_year is null;
    

  • index_merge: 查询一张表用到了多个索引时，mysql会将索引合并到一起

    explain select * from rental where customer_id = 10 or inventory_id=367;
    
    

  • unique_subquery: 与index_subquery相似，使用唯一索引关联子查询

    --未模拟出来
    

  • index_subquery: 利用索引来关联子查询，不再扫描全表

    --未模拟出来
    

  • range: 表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描。

    适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or ,IN

    explain select * from address where city_id = 10 or city_id between 20 and 30;
    

  • index: 全索引扫描。 也是全”表“扫描，但是是在索引树中扫描，通常比ALL快，因为索引文件通常比数据文件小，index扫描是通过二叉树的方式扫描，而all是扫描物理表。

    	explain select city_id from address;
    	explain select address_id from address order by city_id;
    

  • all: 全表扫描，效率最低。一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。

    explain select * from city;
    

• possible_keys: 可能会使用到的索引

• key: 实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

• key_len: 表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。

  ken_len计算方式：以表结构列name varchar(20) null,字符集utf8为例(utf8-mb4占4个字节)，

  ken_len = 20(类型长度)*3(utf8字符集占用3字节)+1(null占用一个字节)+2(varchar可变类型有2个字节表示变长字段)=63

• ref: 显示索引的哪儿一列被使用了。如果可能的话，是一个常数。

• rows: mysql认为查询时会返回的行数，如果没有用上索引时会全表扫描。rows越小说明检索的数据量越小，越好。

• filtered: 给出了一个百分比的值，这个百分比值和rows列的值一起使用，可以估计出那些将要和执行计划中的前一个表（前一个表就是指id列的值比当前表的id小的表）进行连接的行的数目。

• Extra: 显示一些额外字段，这些字段的部分值具有优化的参考意义。

  • using where: 表示查询使用了where语句处理结果。
  • using index: 表示使用了覆盖索引。这个值重点强调了只需要使用索引就可以满足查询表的要求，不需要直接访问表数据。如果同时出现using where 表示索引被用来执行索引键值的查找，如果没有，表示索引被用来读取数据，而不是真的查找。
  • using join buffer: 这个值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进性能
  • using filesort: 这是 order by 语句的结果。这可能是一个CPU密集型的过程。using filesort表示出现了文件内排序，表示很不好的现象，必须要优化，特别是大表，可以通过选择合适的索引来改进性能，用索引来为查询结果排序。
  • using temporary: mysql需要创建一张临时表来保存中间结果。 也就是说，需要先把数据放到临时表中，然后从临时表中获取需要的数据。出现这种临时表，也是必须需要优化的地方，特别是数据量大的情况。两个常见的原因是在来自不同表的列上使用了distinct，或者使用了不同的 order by 和 group by 列。

四、索引优化

4.1 索引基础知识

4.1.1 什么是索引

小时候应该都用过新华字典，字典中有很多的字，当我们想要查其中的一个字的时候，我们有两种可能的办法：

1. 一页一页的翻字典，知道找到想要查的字
2. 字典前面有目录，根据拼音或者偏旁找到这个字在哪儿一页，然后在到该页查找数据。

上面两种方式很明显。使用目录的方式是效率最高的。对应的mysql中，使用全表扫描就像是上面第一种方式。而使用索引就相当于给表数据加了目录。mysql可以通过show index from 表名查看表中建立的索引。

4.1.2 索引的数据结构

以机械硬盘来说，先了解几个概念：

• 扇区：磁盘存储的最小单位，扇区大小一般为512字节

• 磁盘块：文件系统与磁盘交互的最小单位，一个磁盘块由连续几个(2^n)扇区组成，块大小一般为4KB。

  磁盘读取数据过程：

  磁盘读取数据依靠的是机械运动，每次读取数据都要话费的时间可以分为寻道时间、旋转延迟、传输时间三个部分。

  寻道时间：指的是磁臂移动到指定磁道需要的时间，主流磁盘一般在5ms以下。

  旋转延迟：就是常听的磁盘转速。比如一个磁盘转速为7200转，表示每分钟转7200次，每秒能转120次，旋转延迟就是1/120/2=4.17ms；

  传输时间：从磁盘读出或者将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。

  那么访问一次磁盘的时间，即一次磁盘io的时间约等于5+4=9ms左右。听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。

• 页： mysql中和磁盘交互的最小单位，页是mysql内部定义的一种数据结构，默认为16KB，相当于4个磁盘块，也就是说mysql每次从磁盘中读取一次的数据是16KB，要么不读，读就是16KB，此值可以修改。

mysql数据检索过程：

假如我们对数据存储方式不做任何优化，直接将数据库表中的数据存储在磁盘中，假设某个表只有一个字段，为int型，int占用4个字节，每个磁盘块可以存储1000条记录，100万的数据记录只需要1000个磁盘块。如果我们需要从这100万记录中检索需要的数据，需要读取1000个磁盘块的数据（需要1000次IO），每次IO需要9ms，那么1000次就需要9s，100万条数据随便一个查询就是9秒，这种情况显然是无法接受的。

因此我们迫切需要解决的问题是：

1. 需要一种存储结构：当从磁盘中检索数据的时候，能够减少磁盘IO的次数，最好能够降低到一个稳定的常量值。
2. 需要一种检索算法：当从磁盘中读取磁盘块的数据之后，这些块中可能包含多条记录，这些记录被加载到内存中，那么需要一种算法能够快速从内存中多条记录中检索出目标数据。

检索算法选择

• 循环遍历：时间复杂度为O(n)，效率不稳定。
• 二分查找法：时间复杂度为O(logN)，效率较稳定。数据是有序的

数据结构的选择

• 数组：大数据量下读取数据耗费IO次数太多，插入数据会涉及数组内部数据移动，耗时太久，放弃考虑。

• 链表：大数据量写数据快，但是检索数据需要从第一个节点开始遍历，查询太慢了（放弃考虑）

• 二叉树：二叉树同时拥有数组和链表的优点，但是某些情况下又会变成链表。

  

  这种情况下，二叉树其实与链表相同。（放弃，考虑平衡二叉树）

• 平衡二叉树（AVL树）：平衡二叉树解决了二叉树的瘸腿问题。但是平衡二叉树也有缺点。平衡二叉树的查找效率是非常高的，但是因为是严格平衡二叉树（严格要求最左子树和最右子树深度之差不会超过2）,因此每当有写操作时，都会重新调整树结构，当写操作比较频繁时，整个树的维护成本过高，当数据量大时写入效率也会非常低。因此又进化出了红黑树。

• 红黑树：红黑树是一种特殊的平衡二叉树。它要求最左子树和最右子树深度之差不会超过最左子树的2倍。AVL树是2，而平衡二叉树是二倍。因此就解决了AVL树写成本太高的问题。但是红黑树和AVL树一般都是基于内存的，而mysql数据是要存放到磁盘里的。而且在数据量非常大的情况下，会造成树的深度非常深，就会造成磁盘io过于频繁，io次数多了就会影响速度。

  当二叉树的深度过深时，mysql的数据是存储在磁盘中的。那么当需要查询数据时，就需要先查根节点数据(一次IO)，判断是左子树还是右子树，然后读节点数据（一次IO），再判断左子树还是右子树，读节点数据（一次IO）。因此树非常深时，每个节点都要进行一次IO。

  

  上图可以看出，才三十多个子节点，而树的深度最深已经达到7层。而mysql需要保证百万甚至千万数据量的快速操作。因此进化出了B-树。

• B-树：B-树就是B树，Mysql是基于磁盘进行存储的，读取数据都是基于磁盘进行数据查询的。而基于磁盘的数据读取是需要磁盘的机械臂进行移动来实现数据定位的。机械臂移动属于物理运动，物理运动是一定比电子运动慢的。因此可以使用B树来进行优化。

  B树拥有如下特点：

  • 所有键值分布在整棵树中。
  • 搜索有可能在非叶子结点结束，在关键字全集内做一次查找,性能逼近二分查找
  • 每个节点最多拥有m个子树
  • 根节点至少有两个子树
  • 分支节点至少拥有ceil(m/2)个子树(除根节点和叶子结点外都是分支节点)
  • 所有叶子结点都在同一层，每个节点最多有m-1个key，并且以升序排序。

  B树结构如下图：

  

  如果此时查找关键字28，那么查找过程如下：

  1. 根据根节点找到磁盘块1，读入内存（磁盘IO第1次）
  2. 比较关键字28在区间[16,34]之间，找到磁盘块1的指针p2，根据p2找到磁盘块p3，读入内存（磁盘IO第2次）
  3. 比较关键字28在区间[25,31]之间，找到磁盘块3的指针p2，根据p2找到磁盘块p8，读入内存（磁盘IO第3次）
  4. 在磁盘块8中的关键字列表找到关键字28。

  使用B树可以有效减少树的深度，减少IO次数，提高查询效率。但是B树也有缺点：

  1. 每个节点都存储有key，同时又包含data，而每个页的存储空间是有限的，如果data比较大的话，就会导致每个节点存储的key数量变少。
  2. 当存储的数据量很大时，会导致树的深度较深，增大查询时IO次数，进而影响查询性能。

• B+树：为了解决上面B树的问题，于是有了对B+树。B+树就是在B树的基础上进行优化产生的。B+树的特点如下：

  • B+树每个节点可以包含更多的节点，这么的原因有两个，一方面降低了树的高度，另一方面将数据范围变为多个区间，区间越多，数据查询效率越快。
  • 非叶子结点存储key，叶子节点存储key和data。
  • 叶子结点两两指针相互连接（符合磁盘的预读特性），顺序查询性能更高。

  B+树结构如下图：

  

索引系统的数据结构选择有如下两种：

• B+树(默认选择)：由于mysql与磁盘交互的最小单位为页，mysql使用B+树做存储引擎时，每一个节点都是一页，一页16KB。由于B+树除叶子结点每个节点只包含关键字和指针，关键字占用8字节，指针占用6字节，那么一个节点可以保存的关键字为：1170个，那么只需要三层B+树，mysql就可以存储千万级的数据。
• hash表：见HASH索引

4.1.3 聚簇索引和非聚簇索引

常用的存储引擎为MyISAM和InnoDB，两种存储引擎支持的索引类型也是不相同的。MyISAM是非聚簇索引，而InnoDB是聚簇索引。

• 聚簇索引：数据行和相邻的键值紧凑的存储在一起。
• 非聚簇索引：数据文件跟索引文件分开存放

聚簇索引优缺点分析：

优点：

1. 可以把相关数据保存在一起
2. 数据访问更快，因为索引和数据保存在同一个树中。
3. 使用覆盖索引扫描的查询可以直接使用页结点中的主键值。

缺点：

1. 聚簇数据最大限度的提高了IO密集型应用的性能，如果数据全部在内存，那么聚簇索引就没有什么优势
2. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的插入方式。
3. 更新聚簇索引列的代价很高。因为会强制将每个被更新的行移动到新的位置
4. 基于聚簇索引的表在插入新行或者主键被更新导致需要移动行时，可能面临页分裂的问题。
5. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。

页分裂： 在使用B+树进行存储时，因为使用树是为了使用二分查找法来快速检索数据，因此就需要保证key是有序的，磁盘页内有序，同时相邻磁盘页间也是有序的， 如果key是无序的，那么就会出现数据的挪动来保证key的有序行，就是页分裂。

4.1.4 索引的分类

• 主键索引：通过表的主键建立索引，特殊的唯一索引，但是不能为空。当创建表指定主键时就会创建主键索引。
• 唯一索引：索引列值不能重复，但是可以为空。可以通过create unique intdex 索引名 on 表名（列名）
• 普通索引：基本索引类型，没有限制，只是为了加快查询数据速度。create index 索引名 on 表名（列名）
• 全文索引：只能在MyISAM引擎下才能用。只能在char,varcar,text类型字段上使用全文索引。主要针对大文本字符串，通过大文本里某几个关键字就可能查到该记录。只是可能，而且使用此索引时，会占用大量的物理空间并降低记录修改性，因此不建议使用。
• 组合索引：上面的索引类型只能为单个列添加索引，而组合索引可以为多个列的组合添加索引。create index 索引名 on 表名（列名1，列名2..)。使用组合索引时，需要注意最左匹配原则，避免索引失效或建立重复索引。

4.1.5 索引技术名词

• 回表： mysql在使用InnoDB引擎时，使用聚簇索引，聚簇索引数据和索引存放在一起，主键就是关键字key，因此使用主键查询速度最快。当同时在这个表上建立其他字段的索引时，也就是普通索引。普通索引维护仍是一个B+树。

  • 主键索引：也就是聚簇索引的B+树，非叶子结点存储主键值，叶子结点存储数据。
  • 普通索引：非叶子结点存储索引列关键字，叶子结点存储主键索引关键字。

  因此当使用非主键索引查询数据时，首先需要到普通索引树中查询索引列对应的主键，然后再到主键索引树中根据主键查询数据。这个过程就叫做回表。

• 索引覆盖：索引覆盖就是指可以直接在普通索引树中查询到需要的所有列，无需进行回表，速度更快。

  

  如上图，查询计划中Extra出现Using index说明发生了索引覆盖。

• 最左匹配： 最左匹配主要是指在使用组合索引时，从最左边为起点，任何连续的索引都能匹配上，同时遇到范围查询就会停止匹配。

  create table staffs
  (
      id      int primary key auto_increment,
      name    varchar(24) not null default '' comment '姓名',
      age     int         not null default 0 comment '年龄',
      pos     varchar(20) not null default '' comment '职位',
      addtime timestamp   not null default current_timestamp comment '入职时间'
  ) charset utf8 comment '员工记录表';
  alter table staffs add index idxnap(name, age, pos);
  

  如上图建立组合索引name，age，pos。

  

  当查询条件为name，age，pos时，会触发索引。同时无需保证条件书写顺序，因为mysql会自动对条件进行优化，如下：

  

  但是当条件没有三个时，就会根绝最左匹配原则来进行索引的选择。当前索引列顺序为name，age，pos。那么当查询条件为name、name & age、 name & age & pos时都会使用索引。

  

  当只有name和pos为条件时，只有name会走索引。

  

  但是如果没有name条件，那么就不会使用索引。

  

  如果出现了范围查询，那么从范围索引列开始后面字段不会使用索引，

  

  最左匹配的原理：

  因为索引的底层数据结构是一颗B+树，联合索引仍是B+树，但是键值并不止一个，而是多个，当使用（a，b）建立联合索引时，生成的B+树如下：

  

  可以看到a的值是有顺序的1 1 2 2 3 3 ，而b的值是无序的，因此使用b=2这种查询是查不到数据的。因为联合索引是按照最左边的来排序的。

  同时还可以看出当a的值相等时，b的值又是有序的了，但是这种有序是相对的，所以最左匹配遇上范围查询就会停止。

• 索引下推：MySQL5.6引入索引下推，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数。

  假设我们现在有一个需求是查询表中名字第一个字是张，年龄为10岁的所有记录。那么，查询语句是这么写的：

  select * from tuser where name like '张%' and age=10;
  

  根据上面最左匹配原则，那么mysql在执行时只会查询所有的name为张开头的记录，然后拿到id后，回表查询数据进行年龄的判断。

  如下图：

  

  但是msyql引入索引下推后，在索引层就对数据进行了过滤，过滤后再进行回表，这样就减少了回表的次数。如下图：

  

4.1.6 索引的匹配方式

索引的匹配方式有如下几种：

• 全值匹配：就是和索引中的所有列进行匹配。select * from staffs where name = '张三' and age = 20 and pos = '上海'
• 匹配最左前缀：只匹配前面的几列。select * from staffs where name = '张三' and age = 20;
• 匹配列前缀：可以匹配某一列的值的开头部分。select * from staffs where name like '张%';
• 匹配范围值：可以查找某一个范围的数据。select * from staffs where name > 'Mary'
• 精确匹配某一列并范围匹配另一列：查询第一列的全部及第二列的部分。select * from staffs where name = '章三' and age>20;
• 只访问索引的查询。本质就是索引覆盖。

4.2 hash索引

基于hash表的实现，只有精确匹配索引所有列的查询才有效。在mysql中只有memory的存储引擎是显式的支持哈希索引。哈希索引自身只需存储对应的hash值，所以索引的结构非常紧凑，这让哈希索引查找的速度非常快。

hash索引创建: create index hash_idx_address using hash on address(address)

hash索引的限制：

• hash索引只包含hash值和行指针，无法使用索引覆盖
• hash索引数据并不是按照索引值顺序存储的，因此无法进行排序
• hash索引不支持部分列匹配查找，hash索引是使用索引列的全部内容来计算hash值
• hash索引支持等值比较查询，不支持任何范围查询
• 访问hash索引的数据非常快，除非有很多hash冲突，当出现hash冲突时，存储引擎需要遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行，hash冲突比较多时，维护代价会很高

4.3 索引优化

1. 当使用索引列进行查询时尽量不要使用表达式，把计算放到业务层而不是数据库层。例：使用select * from table where id = 4而不是select * from table where id+1=5；

2. 尽量使用主键查询，而不是其他索引。因为主键查询不会触发回表查询。

3. 使用前缀索引。长文本尽量不加索引，如果一定要加索引，尽量使用前缀索引。前缀索引可以使索引变得更小更快，但是无法拿来做order by，group by

   前缀索引创建方法create index idx_description on 表名(列名(前缀长度))

   前缀长度计算方法：

   1. 首先计算要加索引的长文本列，列区分度是多少:

      select count(distinct description)/count(*) from film_text

   2. 然后不断变化左前缀长度，知道区分度最接近原区分度，那么就是最合适的：

      select count(distinct left(description,50))/count(*) from film_text;

4. 使用索引扫描来排序

5. union all，in，or都能够使用索引，但是推荐使用in

6. 范围列可以用到索引。范围条件是 < <= > >= between and可以用到索引，但是范围列后面的列无法用到索引，索引只能作用一个范围列。

7. 强制类型转换会全表扫描。create index idx_phone on address(phone);

   

8. 更新十分频繁，数据区分度不高的字段上不宜建立索引。

   • 更新数据会变更索引B+树，更新频繁的字段建立索引会大大降低数据库性能。
   • 区分度不大的数据，建立索引是没有意义的，不能有效的过滤数据
   • 一般区分度在80%以上时可以建立索引，区分度可以使用count(distinct 列名)/count(*)来计算

9. 创建索引的列，不允许为null，可能会得到不符合预期的结果

10. 当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致。

11. 能使用limit的时候尽量使用limit

12. 单表索引建议控制在5个以内

13. 单索引字段数不允许超过5个（组合索引）

14. 创建索引的时候应该避免一下错误概念：索引越多越好；过早优化，在不了解系统的情况下进行优化。

4.4 索引监控

使用show status like 'Handler_read%';可以查看索引的使用情况。

• Handler_read_first: 读取索引第一个条目的次数
• Handler_read_key：通过索引获取数据的次数
• Handler_read_last：读取索引最后一个条目的次数
• Handler_read_next：通过索引读取下一条数据的次数
• Handler_read_prev：通过索引读取上一条数据的次数
• Handler_read_rnd：从固定位置读取数据的次数
• Handler_read_rnd_next：从数据结点读取下一条数据的次数

五、查询优化

5.1 查询慢的原因

查询慢的原因可能会有以下几个方面：

1. 网络：网速慢导致查询慢
2. IO：查询数据访问磁盘IO次数过多
3. CPU：cpu被其他线程占用过多，或查询sql中计算逻辑较多
4. 上下文切换：
5. 系统调用：产生系统调用过多，系统调用会产生中断，导致上下文切换等。
6. 生成统计信息：一般都会涉及全表扫描
7. 锁等待时间：数据行被锁。

5.2 优化数据访问

查询性能低下的主要原因是访问的数据量太多，某些查询不可避免的需要筛选大量的数据，我们可以通过减少访问数据量的方式进行优化：

1. 确认应用程序是否在检索大量超过需要的数据
2. 确认mysql服务器是否在分析大量超过需要的数据行

不需要的数据具体包括：

1. 查询不需要的记录
2. 多表关联返回全部列
3. 总是取出全部列。(前面三条其实一句话就是不要使用select *， 需要哪儿些字段就查询哪儿些字段，避免图方便)
4. 重复查询相同的数据。（有时需要不断的执行相同的查询，而且每次返回相同的数据，因此基于这样的应用场景，可以将这部分数据缓存起来，可以提高查询效率）

5.3 执行过程优化（todo）

5.3.1 查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中查询缓存中的数据，如果查询恰好命中了查询缓存，那么会在返回结果之前会检查用户权限，如果权限没有问题，那么mysql会跳过所有的阶段，就直接从缓存中拿到结果并返回给客户端。想见7.5cache

5.3.2 查询处理的优化

在最开始性能监控中讲了mysql的执行过程，在查询缓存完过后，如果没有命中缓存，那么mysql会经过一下几个步骤：解析sql、预处理、优化sql执行计划。这几个步骤出现任何的错误都可能会终止查询。

在执行过程中，经过缓存后，会首先进入语法分析器，将sql解析成语法树，这一步耗时较少，如果语法树没问题，那么就会进入优化器生成执行计划(耗时较久)。

一条sql语句可以生成非常多的执行计划，最后得到的结果集都一样，但是不同的执行计划带来的效率是不同的，优化器的最主要目的就是选择最有效的执行计划。mysql使用的是基于成本的优化器，在优化的时候会尝试预测一个查询使用一个查询计划时的成本，并选择其中成本最低的一个。

优化器的优化可以从以下方面考虑：

• 重新定义关联表的顺序，详见关联查询原理
• 将外连接转化为内连接，内连接的效率要高于外连接。
• 覆盖索引扫描。当索引中的列包含所有查询中需要使用的列时，可以使用覆盖索引。
• 等值传播。如果两个列的值通过等式关联，那么mysql能够把其中一个列的where条件传递到另一个上。例如：select film.film_id from film inner join film_actor using(film_id ) where film.film*id > 500;这里不仅film会使用此条件，film_actor也会使用此条件，上面的sql其实与下面相同:select film.film_id from film inner join film_actor using(film_id ) where film.film_id > 500 and film_actor.film_id >500。

5.3.3 排序优化

无论如何排序都是一个成本很高的操作，所以从性能的角度出发，应该尽可能避免排序或者尽可能避免对大量数据进行排序。
推荐使用利用索引进行排序，但是当不能使用索引的时候，mysql就需要自己进行排序，如果数据量小则再内存中进行，如果数据量大就需要使用磁盘，mysql中称之为filesort。
如果需要排序的数据量小于排序缓冲区show variables like '%sort_buffer_size%';,mysql使用内存进行快速排序操作，如果内存不够排序，那么mysql就会先将树分块，对每个独立的块使用快速排序进行排序，并将各个块的排序结果存放再磁盘上，然后将各个排好序的块进行合并，最后返回排序结果。

mysql的排序算法：

• 两次传输排序：第一次数据读取是将需要排序的字段读取出来，然后进行排序，第二次是将排好序的结果按照需要去读取数据行。这种方式效率比较低，原因是第二次读取数据的时候因为已经排好序，需要去读取所有记录而此时更多的是随机IO，读取数据成本会比较高
  两次传输的优势，在排序的时候存储尽可能少的数据，让排序缓冲区可以尽可能多的容纳行数来进行排序操作
• 单词传输排序：先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。此方式只需要一次顺序IO读取所有的数据，而无须任何的随机IO，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据。

当需要排序的列的总大小超过max_length_for_sort_data定义的字节，mysql会选择双次排序，反之使用单次排序，当然，用户可以设置此参数的值来选择排序的方。

5.4 特定类型查询优化

5.4.1 优化count()查询

• myisam的count()函数在没有where条件时才会比较快。

• 使用近似值。有的场景不需要精确的值，可以参考使用近似值来代替，比如可以使用explain来获取近似值。

• 复杂情况下，count需要扫描大量的行才能获取精确的数据，在实际操作时可以考虑使用索引覆盖扫描，或者增加汇总表，或者增加外部缓存。

• 分页count优化。一般在web应用中使用mybatis+pagehelper分页查询数据。pagehelper执行分页查询时，会生成一个methodName_COUNT的方法来统计总数，执行sql为将原sql的查询字段改为count(0)，

  即查询sql为select name,age,...from table where name = ？ and age = ？，会自动生成统计sqlselect count(0) from table where name = ? and age = ?。当sql比较复杂时，比如关联多张表都是left join，那么可以重写_COUNT方法，只count一张表。

5.4.2 优化关联查询

• 确保on或者using子句中的列上有索引，在创建索引的时候就要考虑好关联的顺序。
• 确保任何group by和order by中的表达式只涉及一个表中的列，这样mysql才有可能使用索引来优化。

5.4.2.1 关联查询的原理

了解关联查询的原理前先了解两个概念：

• 驱动表：关联查询的左表，通常扫描次数为1
• 被驱动表：关联查询的右表，根据算法不同，扫描次数不同。

扫描算法有以下几种：

• Simple nested-loop join(SNLJ)(基本不会使用)

  

  R表为驱动表，S为匹配表(被驱动表)。R表的每一条记录都会与匹配表的所有记录做匹配，此方式对数据库开销比较大。

  比较次数为M(驱动表行数)*N(匹配表行数)

• index nested-loop join(INLJ)

  

  当匹配表S有索引时，会使用此方式。因为索引查询的成本是固定的，mysql优化器会倾向于使用记录少的表作为驱动表。

  驱动表R的每一行会通过S表的索引进行访问，当索引匹配时才会进行S表的回表查询。当S表的关联键是主键时，性能会非常高。如果不是主键，则会先进行索引匹配，然后通过索引查到的主键进行回表查询。性能上索引比主键要慢。

  比较次数为M*index(N)

• block nested-loop join(BNLJ)

  

  通常情况下，mysql会先判断被驱动表是否有索引，如果有索引就会走INLJ，如果没有索引就会走BNLJ(MySQL优化器会自动选择两种方式中的一种，不会选择SNLJ，因为它实在是太慢)。

  可以看到上图中间有个join buffer,是将驱动表的所有join相关的列都先缓存到join buffer中，然后批量与匹配表进行匹配，将第一种多次比较合并为一次，降低了非驱动表的访问频率，默认情况下join _buffer_size=256K。

  在查找是mysql会讲所有的需要的列缓存到join buffer中，包括select的列。

  在一个有N个join关联的查询中执行时会分配N-1个join_buffer。

注意:

在使用关联查询时，如果是外连接(left join/right join),那么书写时左边的就是驱动表，右边的就是被驱动表。如果是内连接(inner join)那么优化器会自动选择加索引的表作为被驱动表。

5.4.3 优化子查询

子查询尽可能使用关联查询来代替

5.4.4 优化limit分页

优化此类查询最简单的办法就是尽可能使用覆盖索引，而不是查询所有列。

查看两条sql扫描的rows，可以发现下面扫描数据量更少。

5.5.5 优化union查询

mysql总是通过创建并填充临时表的方式来执行union查询，因此很多优化策略在union查询中都没法很好的使用。经常需要手工的将where、limit、order by等子句下推到各个子查询中，以便优化器可以充分利用这些条件进行优化。因此在执行此类sql时，除非缺失需要服务器消除重复的行，否则一定要使用union all， 因为没有all关键字，msyql会在查询的时候给临时表加上distinct关键字，这个操作的代价很高。

六、分区表

对于用户而言，分区表是一个独立的逻辑表，但是底层是由多个物理子表组成。分区表对于用户而言是一个完全封装底层实现的黑盒子，对用户而言是透明的，从文件系统中可以看到多个使用#分隔命名的表文件。

mysql在创建表时使用partition by子句定义每个分区存放的数据，在执行查询的时候，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询就无须扫描所有分区。
分区的主要目的是将数据安好一个较粗的力度分在不同的表中，这样可以将相关的数据存放在一起。

6.1 分区表的应用场景

• 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。
• 分区表的数据更容易维护。批量删除大量数据可以使用清除整个分区的方式；对一个独立分区进行优化检查修复等操作。
• 分区表的数据可以分布在不同的物理设备上，从而高效的利用多个硬件设备。
• 可以使用分区表来避免某些特殊的瓶颈。InnoDB的单个索引的互斥访问；ext3文件系统的Inode竞争。
• 可以备份和恢复独立的分区

6.2 分区表的限制

• 一个表最多只能有1024个分区，在5.7版本的时候可以支持8196个分区
• 在早期的mysql中，分区表达式必须是整数或者是返回整数的表达式，在mysql5.5中，某些场景可以直接使用列来进行分区。
• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都要包含进来。
• 分区表无法使用外键约束

6.3 分区表的原理

分区表由多个相关的底层表实现，这个底层表也是由句柄对象标识，我们可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引知识在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

分区表的操作按照以下的操作逻辑进行：

• select查询：当查询一个分区表的时候，分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据
• insert操作：当写入一条记录的时候，分区层先打开并锁住所有的底层表，然后确定哪个分区接受这条记录，再将记录写入对应底层表
• delete操作：当删除一条记录时，分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作
• update操作：当更新一条记录时，分区层先打开并锁住所有的底层表，mysql先确定需要更新的记录再哪个分区，然后取出数据并更新，再判断更新后的数据应该再哪个分区，最后对底层表进行写入操作，并对源数据所在的底层表进行删除操作

有些操作时支持过滤的，例如，当删除一条记录时，MySQL需要先找到这条记录，如果where条件恰好和分区表达式匹配，就可以将所有不包含这条记录的分区都过滤掉，这对update同样有效。如果是insert操作，则本身就是只命中一个分区，其他分区都会被过滤掉。mysql先确定这条记录属于哪个分区，再将记录写入对应得曾分区表，无须对任何其他分区进行操作。虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级锁，例如innodb，则会在分区层释放对应表锁。

6.4 分区表的类型

• 范围分区： 根据列值在给定范围内进行分配分区。

  CREATE TABLE employees
  (
      id        INT  NOT NULL,
      fname     VARCHAR(30),
      lname     VARCHAR(30),
      hired     DATE NOT NULL DEFAULT '1970-01-01',
      separated DATE NOT NULL DEFAULT '9999-12-31',
      job_code  INT  NOT NULL,
      store_id  INT  NOT NULL
  ) partition by range (store_id)(
      partition p0 values less than (6),
      partition p1 values less than (11),
      partition p2 values less than (16),
      partition p3 values less than (21)
      );
  

  在建表语句中可以看到，store_id 的值在1-5的在p0分区，6-10的在p1分区...,但是数据超过20时，mysql就不知道放哪儿个分区了。可以使用less than maxvalue来避免这种情况：

  CREATE TABLE employees
  (
      id        INT  NOT NULL,
      fname     VARCHAR(30),
      lname     VARCHAR(30),
      hired     DATE NOT NULL DEFAULT '1970-01-01',
      separated DATE NOT NULL DEFAULT '9999-12-31',
      job_code  INT  NOT NULL,
      store_id  INT  NOT NULL
  ) partition by range (store_id)(
      partition p0 values less than (6),
      partition p1 values less than (11),
      partition p2 values less than (16),
      partition p3 values less than maxvalue 
      );
  

• 列表分区：类似于按range分区，区别在于list分区是基于列值匹配一个离散值接中的某个值来进行选择。

  CREATE TABLE employees
  (
      id        INT  NOT NULL,
      fname     VARCHAR(30),
      lname     VARCHAR(30),
      hired     DATE NOT NULL DEFAULT '1970-01-01',
      separated DATE NOT NULL DEFAULT '9999-12-31',
      jobcode   INT,
      store_id  INT
  ) PARTITION BY LIST (store_id) (
      PARTITION pNorth VALUES IN (3,5,6,9,17),
      PARTITION pEast VALUES IN (1,2,10,11,19,20),
      PARTITION pWest VALUES IN (4,12,13,14,18),
      PARTITION pCentral VALUES IN (7,8,15,16)
      );
  

• 列分区：mysql从5.5开始支持column分区，可以认为是range和list的升级版，在5.5之后可以使用column分区替代range和list，但是column只支持普通列，不支持表达式。

  -- range
  CREATE TABLE employees
  (
      id        INT  NOT NULL,
      fname     VARCHAR(30),
      lname     VARCHAR(30),
      hired     DATE NOT NULL DEFAULT '1970-01-01',
      separated DATE NOT NULL DEFAULT '9999-12-31',
      jobcode   INT,
      store_id  INT
  ) PARTITION BY range columns (store_id)(
      PARTITION p0 VALUES LESS THAN (6) ENGINE = InnoDB,
      PARTITION p1 VALUES LESS THAN (11) ENGINE = InnoDB
      );
  -- list
  CREATE TABLE employees
  (
      id        INT  NOT NULL,
      fname     VARCHAR(30),
      lname     VARCHAR(30),
      hired     DATE NOT NULL DEFAULT '1970-01-01',
      separated DATE NOT NULL DEFAULT '9999-12-31',
      jobcode   INT,
      store_id  INT
  ) PARTITION BY list columns (store_id)(
      PARTITION p0 VALUES in (1,2,3,4,5),
      PARTITION p1 VALUES in (6,7,8,9,10)
      )
  

• hash分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含myql中有效的、产生非负整数值的任何表达式。

  CREATE TABLE employees
  (
      id        INT  NOT NULL,
      fname     VARCHAR(30),
      lname     VARCHAR(30),
      hired     DATE NOT NULL DEFAULT '1970-01-01',
      separated DATE NOT NULL DEFAULT '9999-12-31',
      jobcode   INT,
      storeid   INT
  ) PARTITION BY LINEAR HASH (YEAR(hired)) PARTITIONS 4;
  

• key分区：类似于hash分区，区别在于key分区只支持一列或者多列，且mysql服务器提供自身的hash函数，必须有一列或者多列包含整数值。

  CREATE TABLE employees
  (
      id        INT  NOT NULL,
      fname     VARCHAR(30),
      lname     VARCHAR(30),
      hired     DATE NOT NULL DEFAULT '1970-01-01',
      separated DATE NOT NULL DEFAULT '9999-12-31',
      jobcode   INT,
      store_id  INT
  ) PARTITION BY key(store_id) PARTITIONS 4;
  

• 子分区：在分区的基础上再进行分区后存储。

  CREATE TABLE employees
  (
      id        INT  NOT NULL,
      fname     VARCHAR(30),
      lname     VARCHAR(30),
      hired     DATE NOT NULL DEFAULT '1970-01-01',
      separated DATE NOT NULL DEFAULT '9999-12-31',
      jobcode   INT,
      store_id  INT
  ) PARTITION BY range (store_id) subpartition by hash ( year(hired) ) subpartitions 2(
      partition p0 values less than (6),
      partition p1 values less than (11),
      partition p2 values less than (16),
      partition p3 values less than (21)
      );
  

表分区文件可以在mysql的data表文件中看到：

分区表数据文件名。

6.5 分区表的使用

• 全量扫描数据，不要任何索引

  使用简单的分区方式存放表，不要任何索引，根据分区规则大致定位需要的数据为止，通过使用where条件将需要的数据限制在少数分区中，这种策略适用于以正常的方式访问大量数据

• 索引数据并分离热点数据

  如果数据有明显的热点，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中，这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效的使用缓存

6.6 分区表使用时需要注意的问题

• null值会使分区过滤无效
• 分区列和索引列不匹配，会导致查询无法进行分区过滤
• 选择分区的成本可能很高
• 打开并锁住所有地层表的成本可能很高
• 维护分区的成本可能很高

七、服务器参数设置

7.1 general

• datadir=/var/lib/mysql：数据文件存放的目录
• socket=/var/lib/mysql/mysql.sock：mysql.sock表示server和client在同一台服务器，使用localhost进行连接，就会使用socket进行连接
• pid_file=/var/lib/mysql/mysql.pid：pid文件存储地址
• port=3306：端口号
• default_storage_engine=InnoDB：默认的存储引擎
• skip-grant-tables: 当忘记密码时，可以在mysql配置文件中配置改参数，跳过权限表验证，不需要密码就可以登陆mysql

7.2 character

• character_set_client：客户端数据的字符集
• character_set_connection：mysql处理客户端发来的信息时，会把这些数据转换成连接的字符集格式
• character_set_results：mysql发送给客户端的结果集所用的字符集
• character_set_database：数据库默认的字符集
• character_set_server：mysql服务端的默认字符集

7.3 connection

• max_connections: mysql最大连接数，如果数据库的并发连接请求较大，应调高该值
• max_user_connections：限制每个用户的连接数
• back_log：mysql能够暂时存储的连接数量，当mysql的连接数超过max_connections时，就会将新的连接请求放在堆栈中等待一个连接的释放，如果back_log也满了，那么就不再接受新的连接请求。
• wait_timeout：mysql在关闭一个非交互的连接之前需要等待的时间
• interactive_timeout：关闭一个交互连接之前需要等待的秒数

7.4 log

• log_error：指定错误文件名称，用户记录当mysql启动和停止时，以及服务器在运行过程中发生任何严重错误是的相关信息

• log_bin：指定二进制日志文件名称，用于记录对数据造成更改的所有语句

• binlog_do_db：指定将更新记录到二进制日志的数据库，其他所有没有显式指定的数据库更新将忽略，不记录在任何日志中。

• binlog_ignore_db：指定不将更新记录到二进制日志的数据库

• sync_binlog：指定多少次写日志后同步磁盘。

  • 0：默认值，事物提交后将二进制文件从缓冲写入磁盘，但是不进行刷新操作。此时只是写入了操作系统缓冲中，依赖操作系统默认的刷新机制，如果此时系统宕机则会丢失部分数据。
  • 1：每次事物提交后就会将二进制文件写入磁盘并立即执行刷新操作，相当于同步写入磁盘
  • N：每写N次操作系统缓冲就执行一次刷新操作。

  设置为1时，数据最安全，但是也最慢，会影响性能。设置N越大性能越高，同时数据丢失的风险也越高。

• general_log：是否开启查询日志记录

• general_log_file：指定查询日志文件名，用于记录所有的查询语句

• slow_query_log：是否开启慢查询日志记录

• slow_query_log_file：指定慢查询日志文件名，用于记录耗时比较长的查询语句

• long_query_time：设置慢查询的时间，超过这个时间的查询语句才会记录日志

• log_slow_admin_statements：是否将管理日志写进慢查询日志

MySQL的三个日志文件

• bin log：binlog日志记录数据库执行时写入性操作信息(不包括查询)，以二进制形式保存在磁盘中，属于mysql的逻辑日志（记录sql语句），任何存储引擎都会产生binlog日志。主要应用在mysql高可用数据同步以及mysqlbinlog的数据恢复上。

  binlog日志默认是没有的，只有启用后才会有。

  

  在my.cnf文件中添加配置启用binlog日志：

  [mysqld]
  log-bin=mysql-bin
  server-id=1
  

  

  默认的文件位置为：$datadir/mysql-bin,可以通过手写log-bin文件的位置来自定义位置。

  

  找到文件位置后，可以插入一条数据，然后查看bin-log日志：

  

  使用mysqlbinlog命令查看bin-log文件:

  mysqlbinlog --base64-output=DECODE-ROWS -v -d 数据库名 binlog文件

  

• redo log：redo log是innodb存储引擎特有的日志，主要是用来操作事物保证数据一致性的。默认文件名为ib_logfile0~ib_logfileN。

  

  事物的四大特性之一是持久性，就是要求事物提交成功，那么对数据库做的修改就要永久的保存下来，不能再回到原来的状态，那么mysql是如何保证一致性的？

  最简单的做法是在每次事物提交时，将事物设计的数据页全部刷写到磁盘中，但是这样会有严重的性能问题主要体现在：

  1. 因为Innodb是以页为单位进行磁盘交互的，而一个事物可能只修改了一个数据页里的几个字节，这时将完整的数据页刷写到磁盘，浪费资源
  2. 一个事物可能涉及多个数据页，多个数据页物理上并不联系，使用随机IO写入性能太差

  因此mysql设计了redo log，就是记录事物对数据页做了哪儿些修改，这样就解决了上面的性能问题：

  • 具备crash-safe能力，提供断电重启时解决事物丢失数据问题
  • 提高性能，先写入redo log记录更新，等到有空闲线程/内存不足/redolog满了时刷脏（将数据页修改刷写到磁盘），写redo log是顺序IO，刷脏是随机IO。

• undo log：redo log保证了数据的一致性，undo log保证了数据的原子性，要么全部成功，要么全部失败。undo log日志是回滚日志，当执行一条insert语句时，就会在undo log中写入一条delete日志，这样当事物失败时，就可以根据undo log回滚到事物之前的数据状态。

7.5 cache(不建议使用)

• key_buffer_size：索引缓冲区的大小(只对MyISAM表起作用)

• query_cache：缓存相关属性, mysql5.7可用，后续会移除。

  • query_cache_size: 查询缓存占用空间大小，设置为0时，关闭query_cache

    set query_cache_size = 102400000
    

    监控缓存性能：show status like '%Qcache%'

    mysql> show status like '%Qcache%';
    +-------------------------+---------+
    | Variable_name           | Value   |
    +-------------------------+---------+
    | Qcache_free_blocks      | 1       |
    | Qcache_free_memory      | 1031832 |
    | Qcache_hits             | 0       |
    | Qcache_inserts          | 0       |
    | Qcache_lowmem_prunes    | 0       |
    | Qcache_not_cached       | 1       |
    | Qcache_queries_in_cache | 0       |
    | Qcache_total_blocks     | 1       |
    +-------------------------+---------+
    8 rows in set (0.01 sec)
    

    • Qcache_free_blocks: 缓存中相邻内存快的个数，如果值比较大，那么查询缓存中碎片比较多
    • Qcache_free_memory: 查询缓存中剩余的内存大小
    • Qcache_hits: 表示有多少命中此缓存
    • Qcache_inserts: 表示有多少次未命中而插入
    • Qcache_lowmen_prunes: 多少条query因为内存不足而被移除cache
    • Qcache_not_cached: 不适合进行缓存的查询的数量，通常是由于这些查询不是select语句
    • Qcache_queries_in_cache: 当前cache中缓存的query数量
    • Qcache_total_blocks: 当前cache中block的数量。

    可以使用FLUSH QUERY CACHE语句来清理查询缓存碎片以提高内存使用性能。RESET QUERY_CACHE从查询缓存中移出所有查询。

  • query_cache_limit：超出此大小的查询将不被缓存

  • query_cache_min_res_unit：缓存快最小大小

  • query_cache_type：缓存类型，决定缓存什么样的查询。

    • 0: 禁用
    • 1:缓存所有结果，除非sql语句中使用sql_no_cache禁用查询缓存
    • 2:表示只缓存select语句中通过sql_cache指定需要缓存的查询

    select sql_cache * from address where city_id = 1
    

  使用注意

  如果数据表被更改，那么和这个表相关的全部缓存都会被删除。表数据被更改包括:INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE、DROP DATABASE等。如果数据表的访问比较频繁，那么说明该数据表中很多数据都会被缓存起来，但是每一次该表的更新，无论是否影响到cache数据，都会将该表的全部缓存数据移除。如果该表更新较频繁，那么query_cache将会成为系统的负担，严重影响性能。

• sort_buffer_size：每个需要排序的线程分派该大小的缓冲区

• max_allowed_packet=32M：限制server接受的数据包大小

• join_buffer_size=2M：表示关联缓存的大小

• thread_cache_size: 服务器线程缓存，这个值表示可以重新利用保存在缓存中线程数量。当断开链接时，那么客户端的线程将被放到缓存中以响应下一个客户端而不是销毁，如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，这个请求重新创建。

  

  • Threads_cached：代表此时线程缓存中有多少空闲线程
  • Threads_connected：代表当前已经建立连接的数量
  • Threads_created：代表最近一次服务启动，以创建的线程的数量，如果该值比较大，那么服务器会一直再创建线程。
  • Threads_running：代表当前激活的线程数

7.6 InnoDB

• innodb_buffer_poll_size: 使用指定大小的内存来缓冲数据和索引，最大可以设置为物理内存的额80%
• innodb_flush_log_at_trx_commit：控制innodb将logbuffer中的数据写入日志文件并刷新磁盘的时间点。取值如下：
  • 0：提交事物时，不立即把redo log buffer里的数据刷入磁盘文件。而是依靠innodb主线程每秒执行一次刷新到磁盘，此时是刷入到操作系统缓存，并调用操作系统fsync刷写到磁盘。
  • 1：提交事物时，就必须把redo log从内存刷写到磁盘中，并调用操作系统fsync刷写到磁盘。
  • 2：提交事物时，把redo日志写入磁盘文件对应的os cache缓存中，但是不调用fsync刷写磁盘，而是每秒做一次IO刷写磁盘操作。
• innodb_thread_concurrency：设置innodb线程的并发数，默认为0表示不限制。如果要设置建议跟服务器cpu核数一致（超线程2倍）
• innodb_log_buffer_size=1M：InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。 因此，如果有大的事务处理，设置大的日志缓冲可以减少磁盘I/O。 在 my.cnf 中以数字格式设置。
• innodb_log_file_size=1M：日志组中的每个日志文件的大小(单位 MB)。如果 n 是日志组中日志文件的数目，那么理想的数值为 1M 至下面设置的缓冲池(buffer pool)大小的 1/n。较大的值，可以减少刷新缓冲池的次数，从而减少磁盘 I/O。但是大的日志文件意味着在崩溃时需要更长的时间来恢复数据。 在 my.cnf 中以数字格式设置。
• innodb_log_files_in_group=2：日志组中的日志文件数目。InnoDB 以环型方式(circular fashion)写入文件。数值 3 被推荐使用。在 my.cnf 中以数字格式设置。
• read_buffer_size=131072：mysql读入缓冲区大小。对表进行顺序扫描的请求将被分配到一个读入缓冲区。
• read_rnd_buffer_size=262144: mysql随机读的缓冲区大小
• innodb_file_per_table=YES: 开启为每张表分配一个新的文件

八、MySQL的锁

MySQL的锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CP U、RAM、IO等）的争用外，数据也是一种供多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这几个角度来说，锁对数据库而言显得更加重要也更加复杂。

相对其他数据库而言，mysql的锁机制比较简单。其最显著的特点是不同的存储引擎支持不同的锁机制。比如MyISAM和MEMORY的存储引擎采用的是表级锁，而InnoDB采用的是行级锁。

• 表级锁：开销小，加锁快；不会出现死锁；锁粒度大，发生冲突概率最高，并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁粒度小，发生冲突概率最低，并发读最高。

表级锁和行级锁各自有自己的特点，很难说哪儿种锁更好，只能就具体的应用场景来选择哪儿种锁更适合。

应用场景

仅从锁的角度看的话：

• 表级锁：适合以查询为主，只有少量按索引条件更新数据的应用，如web应用；
• 行级锁：更适合于有大量按照索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事物处理系统。

MyISAM表锁

MyISAM的表级锁有两种模式：表共享读锁和表独占写锁。

对MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但是会阻塞对同一表的写请求；
对MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；
MyISAM表的读操作和写操作以及写操作之间是串行的。

创建MyISAM表：

CREATE TABLE `mylock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO `mylock` (`id`, `NAME`) VALUES ('2', 'b');

• MyISAM读共享锁：创建两个sql客户端窗口session1和session2；

  

  操作顺序按照行号从上到下，两个session都可以查到数据

• MyISAM读锁阻塞写操作：

  

  按照行号执行顺序如下：

  1. session1执行lock tables mylock read;成功
  2. session2执行insert into mylock values(3, 'c');阻塞中，等待session1释放表锁。
  3. session1执行select * from mylock;成功
  4. session1执行 unlock tables;成功
  5. session2执行插入语句结束阻塞，执行成功。

  | session1 | session2 |
  | :----------------------------------------------------------: | :----------------------------------------------------------: |
  | 获得表的read锁定
  lock table mylock read; | |
  | 当前session可以查询该表记录：
  select * from mylock; | 当前session可以查询该表记录：
  select * from mylock; |
  | 当前session不能查询没有锁定的表
  select * from person
  Table 'person' was not locked with LOCK TABLES | 当前session可以查询或者更新未锁定的表
  select * from mylock
  insert into person values(1,'zhangsan'); |
  | 当前session插入或者更新表会提示错误
  insert into mylock values(6,'f')
  Table 'mylock' was locked with a READ lock and can't be updated
  update mylock set name='aa' where id = 1;
  Table 'mylock' was locked with a READ lock and can't be updated | 当前session插入数据会等待获得锁
  insert into mylock values(6,'f'); |
  | 释放锁
  unlock tables; | 获得锁，更新成功 |

• MyISAM写锁阻塞读操作：

  可以看到session2获取到写锁后，第二行session1执行查询语句，左边在转圈，被阻塞了，在等待session2的锁释放。

  在session2的unlock tables;执行的瞬间，锁被释放，session1立即执行读操作成功。

  | session1 | session2 |
  | :----------------------------------------------------------: | :-------------------------------------------------------: |
  | 获取表的write锁定
  lock table mylock write; | |
  | 当前session对表的查询，插入，更新操作都可以执行
  select * from mylock;
  insert into mylock values(5,'e'); | 当前session对表的查询会被阻塞
  select * from mylock； |
  | 释放锁：
  unlock tables； | 当前session能够立刻执行，并返回对应结果 |

上面是为了显示的控制加锁的情况下，读写操作的效果，采用手动加锁释放锁的方式。在实际使用中，MyISAM在执行查询语句之前，会自动给涉及到的所有表加读锁；在执行更新操作之前，会自动给所有涉及的表加写锁，不会像上面一样手动使用显示命令加锁。

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁争夺：

• table_locks_immediate：表示立即释放表锁数
• table_locks_waited：表示需要等待的表锁数

如果table_locks_waited的值比较高，那么说明存在这严重的锁争用情况。如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎。

InnoDB行锁

MyISAM由于表锁的机制，其实在并发情况下，是类似串行执行的，效率太低。相对来说InnoDB使用并发事物可以大大提高资源利用率，提高数据库系统的事物吞吐量。

事物

在聊Innodb行锁前，首先需要了解mysql的事物。事物具有ACID四大特性：

• A：Actomicity原子性。事物是一个原子操作单位，对数据的修改要么全都执行，要么都不执行。
• C：consistent一致性。事物开始前和完成后，数据必须保持一致状态。
• I：Isolation隔离性。数据库提供隔离机制，保证事物在不受外部并发操作影响的独立环境执行。
• D：Durable持久性。事物一旦提交，那么对数据的修改就是永久的，即使出现系统故障也能够保持。

事物在并发情况下，会带来如下问题：

• 脏读：事物A正在修改一条记录，此时事物B也来读取同一条数据，如果不加控制，事物B就会读到事物A未提交的数据，此时事物B再对数据进行修改。就会产生未提交数据的依赖关系，这种情况叫做脏读。
• 不可重复读：一个事物在读取的某些数据已经发生了改变，或者某些记录已经删除了，这种现象叫做不可重复读。（说白了就是一个事物内执行相同查询条件时，无论查询多少次数据，查询到的结果应该是不变的，如果发生了变化，那就说明发生了不可重复读）。
• 幻读：一个事物按相同的查询条件重新读取以前检索过的数据，却发现其他事物插入了满足其查询条件的新数据，这种情况叫做幻读。

为了解决上面的问题，所以有了事物的隔离级别的概念，隔离级别可以分为如下几类：

准备测试表：

create table psn(id int primary key,name varchar(10)) engine=innodb;
insert into psn values(1,'zhangsan');
-- 关闭自动提交事物
set autocommit = 0;

• read_uncommited：未提交读。允许事物读取其他事物未提交的数据。

  session1(左窗口，下同)设置隔离级别未未提交读，session2(右窗口，下同)使用默认隔离级别。session2插入一条记录但是未提交。可以看到session1读到了session2未提交的数据。此时就发生了脏读。

  如果此时是session2删除了id为1的数据，那么session1也读不到id为1的数据了，此时就发生了不可重复读。

  如果此时session2提交了数据，那么session1当然可以读到session2的数据，此时发生了幻读

• read_commited：已提交读。允许当前事物读取其他事物已提交的数据。

  

  session1读到了session2以提交的数据，发生了不可重复读。

  

  但是此时，session1已经读不到session2未提交的数据了，因此解决了脏读的问题

  但是仍然存在幻读的问题，幻读将在下面演示。

• repeatable_read：可重复读（默认的隔离级别）。不允许当前事物读取其他事物的数据。

  

  可以看到解决了不可重复读的问题。

  但是此时如果因为sessino2已经插入数据成功了，session1此时查询表是空的，如果此时session1插入一条id为1的数据，那么此时就会报错。现象就是明明我查询没有id为1的数据，但是我插入时却告诉我id为1的数据已经存在了，就好像出现了幻觉一样。

  

• serializable：序列化。所有事物串行化执行。

  可以看到session1事物先开启，此时如果session2事物要加一条记录的话，会阻塞，一致转圈，要等session1的事物提交，session2的插入才会执行。

  session1执行commit的瞬间，session2的插入操作也执行成功了。

  序列化解决了上面所有的问题，但是效率是最低的，因为事物间并行变成了串行执行。

通过上面的演示最终可以得到如下表格：

	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed		√	√
repeatable read			√
serializable

数据库的事物隔离级别越严格，并发副作用就越小，但付出的代价就越大，因为事物隔离本质上就是使事物在一定程度串行化，需要根据具体的业务需求来决定使用哪儿种隔离级别。

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况。Innodb_row_lock_waits和Innodb_row_lock_time_avg的值比较高，则说明锁争用比较严重

InnoDB行锁：

• 共享锁（S）：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
• 排他锁（X）：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

InnoDB引擎默认的修改语句：insert/update/delete会自动给涉及到的数据加排他锁，select默认不会加任何锁。如果加排他锁可以使用select ... for update语句，加共享锁可以使用select ... lock in share mode语句。所以加排他锁的数据在其他事物中不能修改数据，也不能通过for update或lock in share mode锁的方式查询数据，但是可以直接通过select ... from ...查询数据，因为普通查询没有锁机制。

InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的(在mysql中是这样，oracle中是通过在数据块中对应的数据行来加锁实现的)。这意味着：只有通过索引条件检索数据，InnoDB才能使用行锁，否则InnoDB将使用表锁。

InnoDB是通过索引加锁，所以如果是检索相同索引键下的不同数据行，也会起冲突

在使用InnoDB时要尽量避免死锁：

• 尽量使用较低的隔离级别； 精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会；
• 选择合理的事务大小，小事务发生锁冲突的几率也更小；
• 给记录集显式加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁；
• 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会；
• 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响； 不要申请超过实际需要的锁级别；除非必须，查询时不要显示加锁；
• 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

MVCC

MVCC(Multi Version Concurrency Control)多版本并发控制器。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现读数据库并发访问，在编程语言中实现事物内存。

在学习MVCC前先了解一下当前读和快照读：

• 当前读：在InnoDB行锁中学习了共享锁和排他锁，都是属于当前读。因为这两种方式读取的记录都是记录的最新版本，读取时还要保证其他并发事物不能修改当前记录，会对读取到的记录进行加锁。

  select ..for update; 
  select .. lock in shard mode;
  update..;
  delete...;
  insert ...;
  

  上面五种写法都是当前读。

• 快照读：像不加锁的select...操作就是快照读。即不加锁的非阻塞读，不涉及其他锁之间的冲突。快照读的前提是并行，隔离级别不是串行，串行级别下快照读就会退化成当前读。之所以出现快照读的情况是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC。可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销。而因为是基于多版本，因此快照读可能读到的数据并不一定是最新的数据，可能是历史数据。

MVCC就是为了实现读-写冲突不加锁。这个读就是指快照读，而不是当前读，当前读是一种加锁操作，是悲观锁的实现方式。

MVCC多版本并发指的是：维持一个数据的多个版本，使得读写操作没有冲突。而这仅仅是一个理念。

MySQL为了实现MVCC这个理念而提供了快照读的功能。MySQL的MVCC并不是真正的MVCC，只是借用MVCC实现了读的非阻塞。主要运用在RR（可重复读），RC（已提交读）隔离级别。

MVCC实现原理

MVCC的实现主要是依靠隐式字段、undolog、read view去完成的。

隐式字段

在InnoDB中，每一行都有如下隐式字段：

• DB_TRX_ID：6字节，表示最近修改该行数据的事物ID。

• DB_ROLL_PTR：7字节，表示指向该行回滚段的指针。该行上所有的旧版本，在undo log中，通过链表的形式组织，而改值，指向undo log中该行的历史记录链表。

• DB_ROW_ID：6字节，隐藏主键列。

  关于row_id:

  如果创建表时指定了主键并且是数值类型，那么这个主键就是row_id

  如果创建表时没有指定主键，但是有一个数值类型并且不为空的唯一键，那么这个唯一键就是row_id，并且row_id会被当主键用，使用这个键来建聚簇索引。

  上面两种方式都可以使用select _rowid from table_name来显示的查询。

  如果上面两种情况都不存在，那么mysql底层会生成一个6字节的自增的隐藏列来作为row_id，并通过rowid来建立聚簇索引。通过此方式生成的rowid无法显示的查询。看不到，但是存在，而且6个字节如果用完的话，数据会被覆盖。

  无论哪儿种方式，rowid都是一直存在的

例如表：

create table mvcc_test(
    name varchar(20),
    age int
)engine = InnoDB

实际存储时的行结构为：MVCC就是通过控制DATA_TRX_ID和DATA_ROLL_PTR列实现的。

undolog日志分为两种：

• insert undo log：代表事物在insert新记录时产生的undo log，只在事物回滚时需要，事物提交后可以被立即丢弃。
• update undo log：事物在update或delete时产生的undo log，不仅在事物回滚时需要，在快照读时也需要。所以不能随便删除，只有在快照读或事物回滚不涉及该日志时，对应的日志才会被purge线程统一清除。

可以看到对MVCC有帮助的实际上是update undo log,undo log实际上就是存在rollback segment中旧记录链，执行流程如下：

1. 比如现在事物插入并提交mvcc_test表新记录：insert into mvcc_test values('zhangsan', 18);那么实际这行记录结构为：

2. 又来了个事物ID为1对数据name做出了修改，udpate mvcc_test set name='lisi' where name='zhangsan';。

   在事物B对数据做修改时，数据库会对该行数据加排他锁。然后把该行数据拷贝到undolog中，作为旧记录。然后修改该行name为lisi，并且修改隐藏字段事物id为当前事物id。回滚指针指向拷贝到undolog的副本记录。事物提交，锁释放，那么数据结构如下：
   

3. 此时又有一个事物2对数据age做了修改：update mvcc_test set age=20 where name='lisi';。那么就会重复上面过程，更新后的数据结构如下：

   

read view

read view就是事物在执行快照读时生成的读视图。在事物执行快照读的那一刻，会生成当前事物的一个快照，记录并维护当前活跃事物(未提交事物)的ID。每个事物开启时都会分配一个id，id是递增的，id越大表示事物越新。

readview中主要有四个比较重要的内容：

• m_ids：表示生成readview时，当前系统中活跃的事物id列表。
• min_trx_id：表示生成readview时，当前系统中活跃的事物列表中的最小事物id
• max_trx_id：表示生成readview时，系统应该分配的下一个事物id。
• creator_trx_id：表示生成该readview的事物id

举例说明：

比如说现在有id为1，2，3三个活跃事物，那么m_ids就是1，2，3，min_trx_id就是1，max_trx_id就是4。如果此时id为3的事物提交了，那么m_ids就是1，2，min_trx_id还是1，max_trx_id还是4，此时又打开了一个新的事物分配id为4，那么m_ids就是1,2,4，min_trx_id是1，max_trx_id是5。

通过这几个id，就可以在访问readview时判断记录是否可见：

1. 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
2. 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
3. 如果被访问版本的trx_id属性值大于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
4. 如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

MVCC的好处

数据库的并发场景有一下三种：

• 读-读：不存在任何问题，也不需要任何并发控制
• 读-写：有线程安全问题，可能会造成事物隔离性问题：脏读、幻读、不可重复读
• 写-写：有线程安全问题，可能造成数据丢失

通过mvcc就可以并发读写操作时，读不会阻塞写操作，写操作也不会阻塞读操作，提高了数据库并发读写的性能。同时还可以解决脏读、不可重复读、幻读等数据隔离性问题。但是不能解决更新丢失问题。