MySQL优化讲解

最新推荐文章于 2023-08-15 15:59:21 发布

研发部小伙伴

最新推荐文章于 2023-08-15 15:59:21 发布

阅读量241

点赞数

本文链接：https://blog.csdn.net/kcsm_csdn/article/details/102495018

版权

感谢贡献及整理人：小川，感谢组织及发起人：何大大，郑大大

1 表设计

1.1 最大程度遵守三范式

1NF:字段不可分;
2NF:有主键，非主键字段依赖主键;
3NF:非主键字段不能相互依赖;

解释:
1NF:原子性字段不可再分,否则就不是关系数据库;
2NF:唯一性一个表只说明一个事物;
3NF:每列都与主键有直接关系，不存在传递依赖;

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

第一范式（1NF）:即表的列的具有原子性,不可再分解，即列的信息，不能分解, 只要数据库是关系型数据库(mysql/oracle/db2/informix/sysbase/sql server)，就自动的满足1NF。数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。如果实体中的某个属性有多个值时，必须拆分为不同的属性。通俗理解即一个字段只存储一项信息。

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要我们设计一个主键来实现(这里的主键不包含业务逻辑)。即满足第一范式前提，当存在多个主键的时候，才会发生不符合第二范式的情况。比如有两个主键，不能存在这样的属性，它只依赖于其中一个主键，这就是不符合第二范式。通俗理解是任意一个字段都只依赖表中的同一个字段。（涉及到表的拆分）

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主键字段。就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放(能尽量外键join就用外键join)。很多时候，我们为了满足第三范式往往会把一张表分成多张表。即满足第二范式前提，如果某一属性依赖于其他非主键属性，而其他非主键属性又依赖于主键，那么这个属性就是间接依赖于主键，这被称作传递依赖于主属性。通俗解释就是一张表最多只存两层同类型信息。

1.2 字段设计

字段选择的一般原则是保小不保大，能占用字节少的字段就不用大字段；
字段的命名要有意义;
不用使用无法加索引的类型作为关键字段，比如text；
表中组合主键的字段个数越少越好，没有冗余的数据库未必是最好的数据库，适当的时候需降低范式标准；
根据应用场合选择表的存储引擎；
varchar字段的长度要按照实际可能的最大长度来设计，但是不能够粗暴的设计为255，varchar会按照设计的长度来申请内存

1.3 Myisam与innodb的区别

详见：Myisam与innodb的区别介绍篇

2 索引

2.1 创建索引的好处

帮助用户提高查询速度；
利用索引的唯一性来控制记录的唯一性；
可以加速表与表之间的连接；
降低查询中分组和排序的时间；

2.2 创建索引的坏处

存储索引占用磁盘空间；
执行数据修改操作(INSERT、UPDATE、DELETE)产生索引维护；

2.3建立索引原则

2.3.1 总体原则

在了解表的具体应用场景基础上建立索引；
为所有主键和外键列建立索引；
对出现在WHERE子句、JOIN子句、ORDER BY或GROUP BY子句中的列考虑建立索引；
对需要确保唯一性的列考虑建立索引；
对于WHERE子句中用AND连接并频繁使用的列使用组合索引，最频繁的列放在最左边，查询顺序与索引顺序一致，从左至右；
数据更新频繁的列不宜建立索引；
数据量较小的表也不宜建立索引;

2.3.2 最左前缀原则

mysql建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：

如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引；

如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引；

b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道第一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。
比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。（这种情况无法用到联合索引）

关于最左前缀的使用，有下面两条说明：

最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

2.3.3 索引失效情况

如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
对于多列索引，不是使用的第一部分，则不会使用索引（即不符合最左前缀原则）
like查询是以%开头
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
如果mysql估计使用全表扫描要比使用索引快,则不使用索引

2.4 正确使用索引

去除查询条件左端的任何标量函数
去除查询条件左端的任何数学运算
确保宿主变量定义与列数据类型匹配
查询条件中避免使用in
尽可能用UNION ALL取代UNION
查询条件中使用like时避免宿主变量以‘%’开头
避免使用“or” ，采用其它方式重写
避免使用<>，用其它方式改写
使用组合索引时，注意“最左前缀”这个基本原则

3、主要参数优化

3.1 目的

通过根据服务器目前状况，修改Mysql的系统参数，达到合理利用服务器现有资源，最大合理的提高MySQL性能。

3.2 参数配置

[mysqld]

#back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效，查看当前系统的TCP/IP连接的侦听队列的大小命令：cat /proc/sys/net/ipv4/tcp_max_syn_backlog目前系统为1024。对于Linux系统推荐设置为小于512的整数。

back_log=500

#最大连接数

#max_connections是指MySql的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySql会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小。

max_connections = 1000

#用户能链接的最大数量

#针对某一个账号的所有客户端并行连接到MYSQL服务的最大并行连接数。简单说是指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。

max_user_connections=600

#MySQL客户端的数据库连接闲置最大时间值

#说得比较通俗一点就是当你的MySQL连接闲置超过一定时间后将会被强行关闭。MySQL默认的wait-timeout 值为8个小时，可以通过命令show variables like 'wait_timeout'查看结果值;

#设置这个值是非常有意义的，比如你的网站有大量的MySQL链接请求（每个MySQL连接都是要内存资源开销的），由于你的程序的原因有大量的连接请求空闲啥事也不干，白白占用内存资源，或者导致MySQL超过最大连接数从来无法新建连接导致“Too many connections”的错误。在设置之前你可以查看一下你的MYSQL的状态（可用show processlist)，如果经常发现MYSQL中有大量的Sleep进程，则需要修改wait-timeout值了。

wait_timeout=1800

#Mysql关闭交互连接前的等待时间

#interactive_timeout是服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。

#wait_timeout是服务器关闭非交互连接之前等待活动的秒数。在线程启动时，根据全局wait_timeout值或全局 interactive_timeout值初始化会话wait_timeout值，取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义).

#这两个参数必须配合使用。否则单独设置wait_timeout无效

interactive_timeout = 1800

#模式

#NO_ENGINE_SUBSTITUTION 如果需要的存储引擎被禁用或未编译，那么抛出错误

#STRICT_TRANS_TABLES 在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做任何限制

#NO_ZERO_IN_DATE 在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。

#NO_ZERO_DATE 在严格模式，不要将 '0000-00-00'做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告

#ERROR_FOR_DIVISION_BY_ZERO 在严格模式，在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产生错误(否则为警告)。如果未给出该模式，被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被零除警告，但操作结果为NULL

#NO_AUTO_CREATE_USER 防止GRANT自动创建新用户，除非还指定了密码。

sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER

#key_buffer_size是用于索引块的缓冲区大小，一般4G左右的服务器可设置为256M

# key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，对MyISAM(MySQL表存储的一种类型，可以百度等查看详情)表性能影响最大的一个参数。如果你使它太大，系统将开始换页并且真的变慢了。严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。对于内存在4GB左右的服务器该参数可设置为256M或384M.

key_buffer_size=512M

#innodb_thread_concurrency应设为CPU核数的2倍

#同一时刻能够进入innodb层次并发执行的线程数(注意是并发不是并行)，如果超过CPU核数，某些线程可能处于就绪态而没有获得CPU时间轮片，如果SERVER层的线程大于这个值，对不起多余的，线程将会被放到一个叫做wait queue的队列中，而不能进入INNODB层次，进不到innodb层当然也就不能干活了，谈不上获得CPU。既然是一个队列那么它必然满足先进入先出的原则。

innodb_thread_concurrency=16

#innodb缓冲池大小，建议为系统内存1/2-3/4大小

# innodb_buffer_pool_size主要针对InnoDB表性能影响最大的一个参数。功能与Key_buffer_size一样。InnoDB占用的内存，除innodb_buffer_pool_size用于存储页面缓存数据外，另外正常情况下还有大约8%的开销，主要用在每个缓存页帧的描述、adaptive hash等数据结构，如果不是安全关闭，启动时还要恢复的话，还要另开大约12%的内存用于恢复，两者相加就有差不多21%的开销。假设：12G的innodb_buffer_pool_size，最多的时候InnoDB就可能占用到14.5G的内存。若系统只有16G，而且只运行MySQL，且MySQL只用InnoDB，那么为MySQL开12G，是最大限度地利用内存了。

#另外InnoDB和 MyISAM 存储引擎不同， MyISAM 的 key_buffer_size 只能缓存索引键，而 innodb_buffer_pool_size 却可以缓存数据块和索引键。适当的增加这个参数的大小，可以有效的减少 InnoDB 类型的表的磁盘 I/O 。

#当我们操作一个 InnoDB 表的时候，返回的所有数据或者去数据过程中用到的任何一个索引块，都会在这个内存区域中走一遭。

innodb_buffer_pool_size = 2G

#InnoDB存储引擎的事务日志所使用的缓冲区

# innodb_log_buffer_size 这是InnoDB存储引擎的事务日志所使用的缓冲区。类似于Binlog Buffer，InnoDB在写事务日志的时候，为了提高性能，也是先将信息写入Innofb Log Buffer中，当满足innodb_flush_log_trx_commit参数所设置的相应条件(或者日志缓冲区写满)之后，才会将日志写到文件 (或者同步到磁盘)中。可以通过innodb_log_buffer_size 参数设置其可以使用的最大内存空间。

innodb_log_buffer_size=20M

#日志组中每个日志文件的字节大小

innodb_log_file_size = 1G

#innodb_flush_log_at_trx_commit

#0，不管有没有提交，每秒钟都写到binlog日志里

#1，每次提交事务，都会把log buffer的内容写到磁盘里去，对日志文件做到磁盘刷新，安全最好

#2，每次提交事务，都写到操作系统缓存，由OS刷新到磁盘，性能最好

innodb_flush_log_at_trx_commit = 2

#InnDB事务等待行锁的时间长度

innodb_lock_wait_timeout = 120

#缓存MySQL中的ResultSet

# query_cache_size: 主要用来缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。当我们打开了Query Cache功能，MySQL在接受到一条select语句的请求后，如果该语句满足Query Cache的要求(未显式说明不允许使用Query Cache，或者已经显式申明需要使用Query Cache)，MySQL会直接根据预先设定好的HASH算法将接受到的select语句以字符串方式进行hash，然后到Query Cache中直接查找是否已经缓存。也就是说，如果已经在缓存中，该select请求就会直接将数据返回，从而省略了后面所有的步骤(如SQL语句的解析，优化器优化以及向存储引擎请求数据等)，极大的提高性能。根据MySQL用户手册，使用查询缓冲最多可以达到238%的效率。

当然，Query Cache也有一个致命的缺陷，那就是当某个表的数据有任何任何变化，都会导致所有引用了该表的select语句在Query Cache中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache可能会得不偿失

query_cache_size=64M

#读入缓冲区大小

# read_buffer_size 是MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能

read_buffer_size=4M

#排序使用的缓冲大小

# sort_buffer_size是MySql执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小

sort_buffer_size=4M

#随机读缓冲区大小

# read_rnd_buffer_size 是MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

read_rnd_buffer_size=8M

# tmp_table_size是MySql的heap （堆积）表缓冲大小。所有联合在一个DML指令内完成，并且大多数联合甚至可以不用临时表即可以完成。大多数临时表是基于内存的(HEAP)表。具有大的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。如果某个内部heap（堆积）表大小超过tmp_table_size，MySQL可以根据需要自动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的大小。也就是说，如果调高该值，MySql同时将增加heap表的大小，可达到提高联接查询速度的效果。

tmp_table_size=16M

4、Explain走查SQL调优

4.1 Explain简介

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

在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL（如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）

explain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

explain 有两个变种：

1）explain extended：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

2）explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

explain列描述

id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

列值说明

SELECT识别符。这是SELECT的查询序列号，是SQL执行的顺序的标识，SQL从大到小的执行

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

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

3. id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type

表示查询中每个select子句的类型

(1) SIMPLE(简单SELECT，不使用UNION或子查询等)

(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)

(3) UNION(UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)

(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

table

显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称。

type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

Key

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）

不损失精确性的情况下，长度越短越好

ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by

Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

No tables used：Query语句中使用from dual 或不含任何from子句

4.2 走查前提

排列清晰明了的SQL代码；
SQL所涉及表结构的详细定义、所建立索引情况等；
此SQL解析的EXPLAIN信息；
此SQL执行后的相关信息，包括获取的行数、执行时间等等

4.3 分析SQL执行结果

检查所有表空间扫描
检查发生的所有排序，是否需要排序?
检查发生的所有排序，是否有其它办法编写查询来消除排序？
SQL语句中选择的列是否都需要，不需要的移除掉
检查各个查询中的所有列。其中有没有可为null的列？确保编写相应的SQL来处理null值
对于AVG、MIN、MAX或SUM语句，确保编写相应的SQL来处理null值
内联接和外联接的使用是否正确
检查所有的Union SQL语句。是否需要写为Union，或者SQL语句是否可以写为Union All
检查所有的Order By语句。从业务上考量是否需要这些Order By语句么？有没有一个索引支持排序？如果与一个索引匹配，Order By可能非常高效
检查所有Distinct语句。是否可能出现重复？如果有重复，考虑改写该语句以避免排序
检查所有谓词，确保所有数学计算都在操作符的另一边完成，而不是应用在列本身
检查所有谓词，确保左端无任何标量函数
检查所有的“非”逻辑，按正面的方式来改写此非逻辑，这样会更高效
检查所有的“<>”,对其进行改写，这样会更高效
检查所有OR语句,对其进行改写，这样更高效
检查所有的LIKE语句，确保其宿主变量不以“%”开头
检查所有的组合索引，确保符合“最左前缀”原则
检查所有的“In”语句，对其进行改写，这样会更高效
确保宿主变量定义与列数据类型匹配

5、MySQL事务隔离简介

5.1 SQL标准中的事务四种隔离级别

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读
串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

5.2 未提交读（Read uncommitted）

数据库一般都不会用，而且任何操作都不会加锁

由于MySQL的InnoDB默认是使用的RR级别，所以我们先要将该session开启成RU级别

SET session transaction isolation level read uncommitted;

5.3 已提交读（Read committed）

在RC级别中，数据的读取都是不加锁的，但是数据的写入、修改和删除是需要加锁的。

SET session transaction isolation level read committed;

SET SESSION binlog_format = 'ROW';（或者是MIXED）

为了防止并发过程中的修改冲突，事务A中MySQL给id=1的数据行加锁，并一直不commit（释放锁），那么事务B也就一直拿不到该行锁，wait直到超时。

5.4 可重复读（Repeatable read）

这是MySQL中InnoDB默认的隔离级别。我们姑且分“读”和“写”两个模块来讲解读

读就是可重读，可重读这个概念是一事务的多个实例在并发读取数据时，会看到同样的数据行。

RC模式下的展现（不可重读）

事务B修改id=2的数据提交之后，事务A同样的查询，后一次和前一次的结果不一样，这就是不可重读（重新读取产生的结果不一样）。这就很可能带来一些问题。

在RR级别中MySQL的表现

我们注意到，事务A先做了一次读取，事务B中间修改了id=1的数据，并commit之后，事务A第二次读到的数据和第一次完全相同。所以说它是可重读的。

5.5 不可重复读和幻读的区别

不可重复读重点在于update和delete，而幻读的重点在于insert。

如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。

5.6 可串行化（Serializable ）

读加共享锁，写加排他锁，读写互斥。使用的悲观锁的理论，实现简单，数据更加安全，但是并发能力非常差。如果业务并发的特别少或者没有并发，同时又要求数据及时可靠的话，可以使用这种模式。

研发部小伙伴

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL优化讲解

感谢贡献及整理人：小川，感谢组织及发起人：何大大，郑大大1 表设计1.1 最大程度遵守三范式1NF:字段不可分; 2NF:有主键，非主键字段依赖主键; 3NF:非主键字段不能相互依赖;解释:1NF:原子性字段不可再分,否则就不是关系数据库;2NF:唯一性一个表只说明一个事物;3NF:每列都与主键有直接关系，不存在传递依赖;目前关系数据库有六种范式：第一范...
复制链接

扫一扫