MySQL总结

1. 数据库引擎

MySQL默认有多款数据库引擎,默认使用的是InnoDB.介绍一下InnoDB和MyISAM.

1.1 InnoDB

  • 支持事务安全

    事务四大特征(ACID)
    原子性(A)：要么都成功,要么都失败

    一致性(C)：保证没有数据更新会执行到一半而导致和其他数据不统一

    隔离性(I)：事务A和事务B之间具有隔离性

    持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)

  • 为处理巨大数据量而设计

    它的CPU效率肯能是任何其他基于磁盘的关系数据库引擎所不能匹敌的

  • 将表和索引在一个逻辑表空间中

  • 支持外键

  • 被用在众多需要高性能的大型数据库站点上

1.2 MyISAM

拥有较高的插入、查询速度、但不支持事务

  • 在支持大文件的文件系统和操作系统上被支持
  • 表最大索引64,每个索引最大的列数16
  • 最大的键长度是1000B
  • BLOB和TEXT可以被索引
  • null值被允许在索引的列中
  • 可以把数文件和索引文件放在不同目录
  • 每个字符列可以有不同的字符集
  • 有VARCHAR的表可以固定或动态记录长度
  • VACHAR和CHAR列可以多达64KB

1.3 存储引擎的选择

大多数情况下选择默认的InnoDB即可

2. 索引

对数据库表中一列或多列的值进行排序的一种结构,

使用索引可提高数据库中特定数据的查询速度

2.1 含义和特点

  • 类型

    • BTREE

      INNODB和MyISAM引擎只支持BTREE

    • HASH

      MEMORY/HEAP引擎只支持HASH

  • 优点

    • 唯一索引可保证数据库表中每一行数据的唯一性.
    • 大大加快查询速度
    • 可以加速表和表之间的连接
    • 可显著减少分组和排序的时间.

  • 缺点

    • 创建和维护索引要消耗时间
    • 占磁盘空间
    • 对表中数据CUD时,索引也要动态的维护,耗时

2.2 分类

  • 普通索引和唯一索引

    普通索引: 可插入重复值和空值

    唯一索引: 列值必须唯一,但可有空值

    组合索引: 列值的组合必须唯一

    主键索引: 特殊的唯一索引,不允许空值

  • 单列索引和组合索引

    单列索引: 一个索引只包含单个列,一个表可以有多个单列索引.

    组合索引: 多个字段组合创建的索引.在查询中使用了这些字段的左边字段时,索引才会被引用

  • 全文索引

    全文索引:

    • FULLTEXT,列值支持全文查找,可存入空值和重复值,

    • 可以创建在char、varchar或text类型的列上

  • 空间索引

    空间索引:

    • 对空间数据类型字段建立的所以呢
    • 四种空间类型: GEPMETRY POINT LINESTRING POLYGON
    • 使用SPQTIAL关键字扩展创建正规索引的语法来创建空间so因
    • 列必须为NOT NULL,只能在MyISAM的表中创建

2.3 设计原则

  • 索引并非越多越好,占空间、影响CUD性能
  • 不对经常更新的表进行过多的索引,并使索引的列尽可能少,但对经常查询的字段创建索引.
  • 数据量小不要使用索引
  • 在条件表达式中不同值多的列上建立索引.
  • 唯一的值使用唯一索引
  • 频繁排序或分组的列上建立索引.

3. 性能优化

通过SQL语句查询、数据结构和硬件三个方面进行优化.

3.1 查询

show status like 'value' 查询数据库性能参数.

value 可以替换为:

  • Connections 连接次数

  • Uptime 上线时间

  • Slow_queries 慢查询次数

  • Com_select 查询的次数

  • Com_insert 插入操作的次数

  • Com_update 更新操作的次数

  • Com_delete 删除操作的次数

  • 分析查询语句

    explain/describe/desc select select_options

  • 使用索引提高查询速度

  • 使用索引查询

    • 使用LIKE关键字的查询语句(%在第一个位置索引不会起作用)

    • 使用多列索引的查询语句(假如一个索引由a、b、c三个字段组成,必须使用a字段,查询时才能使索引生效,最多包含16个字段)

    • 使用OR关键字的查询语句(查询条件只有or,且or前后的两个条件中的列都是索引时,查询中才使用索引.否则查询将不使用索引)

  • 优化子查询

    子查询虽然可以使查询语句很灵活,但是执行效率不高.

    可以使用JOIN查询代替子查询,连接查询不需要建立临时表,速度比子查询快.

3.2 数据库结构

  • 将字段很多的表分解成多个表

    将使用频率很低的表拆解出来.

  • 增加中间表

    对于经常需要联合查询的表,可以建立中间表提高查询效率.

    给经常查询的字段建立一个中间表,并将原来联合查询的表的数据插入到中间表中.以后就可以使用中间表来查询了

  • 增加冗余字段

    合理地加入冗余字段可以提高查询速度.

    通过在冗余字段上加入自己需要联合查询才能查出来的值,这样避免联合查询提高效率

  • 优化插入记录的速度

    • 禁用索引(MYSQL会根据表的索引对插入的记录建立索引.建立索引有额外开销)
    • 禁用唯一性检查(理由和上面差不多)
    • 使用批量插入(一起插入比一条一条 insert语句要快)
    • 使用LOAD DATA IN FILE批量导入(比insert快)

  • 分析表、检查表和优化表

    • 分析表

      分析表: 分析关键字的分布.

      ANALYZE TABLE  tbl_name;

      分析表的过程会加一个只读锁,分析期间只能读,不能update&insert

    • 检查表

      检查表是否存在错误.

      除了表还可以检查视图,也加只读锁

      CHECK TABLE tbl_name;

    • 优化表

      主要是消除删除或者更新造成的空间浪费.

      通过OPTIMIZE TABLE可以消除删除和更新造成的文件素片.也会加锁

      只能优化表中的VARCHAR、BLOB或TEXT类型的字段.

3.3 MySQL服务器

  • 优化服务器硬件

    • 配置较大的内存

      增加系统的缓冲区容量,使数据在内存停留的时间更长,以减少磁盘I/O

    • 配置高速磁盘系统

    • 合理分配磁盘I/O

      把磁盘I/O分散在多个设备上,减少资源竞争.提高并行操作

    • 配置更多处理器

      多处理器可同时执行多个线程

  • 优化MySQL参数

  • 修改back_log参数值:由默认的50修改为500.（每个连接256kb,占用：125M）
    back_log=500

 back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。将会报：unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时.

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

修改系统内核参数，）http://www.51testing.com/html/64/n-810764.html

查看mysql 当前系统默认back_log值，命令：

show variables like 'back_log'; 查看当前数量
  • 修改wait_timeout参数值，由默认的8小时，修改为30分钟。(本次不用)
    wait_timeout=1800（单位为妙）
我对wait-timeout这个参数的理解：MySQL客户端的数据库连接闲置最大时间值。

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

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

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

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

这两个参数必须配合使用。否则单独设置wait_timeout无效
  • 修改max_connections参数值，由默认的151，修改为3000（750M）。
    max_connections=3000
max_connections是指MySql的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySql会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小。

MySQL服务器允许的最大连接数16384；

查看系统当前最大连接数：

show variables like 'max_connections';
  • 修改max_user_connections值，由默认的0，修改为800
    max_user_connections=800
max_user_connections是指每个数据库用户的最大连接

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

目前默认值为：0不受限制。

这儿顺便介绍下Max_used_connections:它是指从这次mysql服务启动到现在，同一时刻并行连接数的最大值。它不是指当前的连接情况，而是一个比较值。如果在过去某一个时刻，MYSQL服务同时有1000个请求连接过来，而之后再也没有出现这么大的并发请求时，则Max_used_connections=1000.请注意与show variables 里的max_user_connections的区别。默认为0表示无限大。

查看max_user_connections值

show variables like 'max_user_connections';
  • 修改thread_concurrency值，由目前默认的8，修改为64
    thread_concurrency=64
thread_concurrency的值的正确与否, 对mysql的性能影响很大, 在多个cpu(或多核)的情况下，错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核), 出现同一时刻只能一个cpu(或核)在工作的情况。

thread_concurrency应设为CPU核数的2倍. 比如有一个双核的CPU, 那thread_concurrency  的应该为4; 2个双核的cpu, thread_concurrency的值应为8.

比如：根据上面介绍我们目前系统的配置，可知道为4个CPU,每个CPU为8核，按照上面的计算规则，这儿应为:4*8*2=64

查看系统当前thread_concurrency默认配置命令：

 show variables like 'thread_concurrency';
  • 添加skip-name-resolve，默认被注释掉，没有该参数。
    skip-name-resolve
skip-name-resolve：禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！
  • skip-networking，默认被注释掉。没有该参数。（本次无用）
    skip-networking建议被注释掉，不要开启
开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项！否则将无法正常连接！
  • default-storage-engine(设置MySQL的默认存储引擎)
    default-storage-engine= InnoDB(设置InnoDB类型，另外还可以设置MyISAM类型)
设置创建数据库及表默认存储类型

show table status like ‘tablename’显示表的当前存储状态值

查看MySQL有哪些存储状态及默认存储状态

 show engines;

创建表并指定存储类型

CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB;

修改表存储类型：

  Alter table tableName engine =engineName

 

备注：设置完后把以下几个开启：

# Uncomment the following if you are using InnoDB tables

innodb_data_home_dir = /var/lib/mysql

#innodb_data_file_path = ibdata1:1024M;ibdata2:10M:autoextend（要注释掉，否则会创建一个新的把原来的替换的。）

innodb_log_group_home_dir = /var/lib/mysql

# You can set .._buffer_pool_size up to 50 - 80 %

# of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 1000M

innodb_additional_mem_pool_size = 20M

# Set .._log_file_size to 25 % of buffer pool size

innodb_log_file_size = 500M

innodb_log_buffer_size = 20M

innodb_flush_log_at_trx_commit = 0

innodb_lock_wait_timeout = 50

设置完后一定记得把MySQL安装目录地址（我们目前是默认安装所以地址/var/lib/mysql/）下的ib_logfile0和ib_logfile1删除掉。否则重启MySQL起动失败。
  • 全局缓存
启动MySQL时就要分配并且总是存在的全局缓存。目前有：key_buffer_size(默认值：402653184,即384M)、innodb_buffer_pool_size(默认值：134217728即：128M)、innodb_additional_mem_pool_size（默认值：8388608即：8M）、innodb_log_buffer_size(默认值：8388608即：8M)、query_cache_size(默认值：33554432即：32M)等五个。总共：560M.

这些变量值都可以通过命令如：show variables like '变量名';查看到。

  • key_buffer_size,本系统目前为384M,可修改为400M

    key_buffer_size=400M

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

怎么才能知道key_buffer_size的设置是否合理呢，一般可以检查状态值Key_read_requests和Key_reads   ，比例key_reads / key_read_requests应该尽可能的低，比如1:100，1:1000 ，1:10000。其值可以用以下命令查得：show status like 'key_read%';

比如查看系统当前key_read和key_read_request值为：

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| Key_read_requests | 28535 |

| Key_reads         | 269   |

+-------------------+-------+

可知道有28535个请求，有269个请求在内存中没有找到直接从硬盘读取索引.

未命中缓存的概率为：0.94%=269/28535*100%.  一般未命中概率在0.1之下比较好。目前已远远大于0.1，证明效果不好。若命中率在0.01以下，则建议适当的修改key_buffer_size值。

MyISAM、InnoDB、MyISAM Merge引擎、InnoDB、memory(heap)、archive
  • innodb_buffer_pool_size(默认128M)

innodb_buffer_pool_size=1024M(1G)

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_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率，并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。值可以用以下命令查得：show status like 'Innodb_buffer_pool_read%';

比如查看当前系统中系统中

| Innodb_buffer_pool_read_requests      | 1283826 |

| Innodb_buffer_pool_reads              | 519     |

+---------------------------------------+---------+

其命中率99.959%=（1283826-519）/1283826*100%  命中率越高越好。
  • innodb_buffer_pool_size(默认128M)

innodb_buffer_pool_size=1024M(1G)

 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_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率，并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。值可以用以下命令查得：show status like 'Innodb_buffer_pool_read%';

比如查看当前系统中系统中

| Innodb_buffer_pool_read_requests      | 1283826 |

| Innodb_buffer_pool_reads              | 519     |

+---------------------------------------+---------+

其命中率99.959%=（1283826-519）/1283826*100%  命中率越高越好。

  • innodb_additional_mem_pool_size(默认8M)

    innodb_additional_mem_pool_size=20M

   innodb_additional_mem_pool_size 设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，所以当我们一个MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。

这个参数大小是否足够还是比较容易知道的，因为当过小的时候，MySQL会记录Warning信息到数据库的error log中，这时候你就知道该调整这个参数大小了。

查看当前系统mysql的error日志  cat  /var/lib/mysql/机器名.error 发现有很多waring警告。所以要调大为20M.

根据MySQL手册，对于2G内存的机器，推荐值是20M。

    32G内存的 100M
  • innodb_log_buffer_size(默认8M)

innodb_log_buffer_size=20M

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

   InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。 因此，如果有大的事务处理，设置大的日志缓冲可以减少磁盘I/O。 在 my.cnf中以数字格式设置。

默认是8MB，系的如频繁的系统可适当增大至4MB～8MB。当然如上面介绍所说，这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB

注：innodb_flush_log_trx_commit参数对InnoDB Log的写入性能有非常关键的影响,默认值为1。该参数可以设置为0，1，2，解释如下：

0：log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作;

1：在每次事务提交的时候将log buffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步;

2：事务提交会触发log buffer到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘同步操作。

实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。根据MySQL手册，在存在丢失最近部分事务的危险的前提下，可以把该值设为0。

5. 集群

MySQL官网推荐两种集群方式一种是InnoDB集群还有一种是NDB集群

5.1 InnoDB和NDB集群工作方式

5.2 InnoDB和NDB集群的选择

NDB Cluster具有一系列独特属性，非常适合为需要高可用性，快速故障转移，高吞吐量和低延迟的应用程序提供服务。由于其分布式架构和多节点实现，NDB Cluster还具有特定约束，可能会使某些工作负载无法正常运行

6. InnoDB锁

不同的数据库引擎使用锁的形式大相径庭,主要介绍InnoDB的锁

6.1 乐观锁

用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

举例
1、数据库表设计
三个字段，分别是id,value、version

select id,value,version from TABLE where id=#{id}

复制代码2、每次更新表中的value字段时，为了防止发生冲突，需要这样操作

update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version};

6.2 悲观锁

与乐观锁相对应的就是悲观锁了。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。

  • 共享锁

    共享锁又称读锁 read lock，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
    如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获得共享锁的事务只能读数据，不能修改数据.

  • 排它锁

    排他锁 exclusive lock（也叫writer lock）又称写锁。

    若事务 1 对数据对象A加上X锁，事务 1 可以读A也可以修改A，其他事务不能再对A加任何锁，直到事物 1 释放A上的锁。这保证了其他事务在事物 1 释放A上的锁之前不能再读取和修改A。排它锁会阻塞所有的排它锁和共享锁

6.3 行锁

行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

6.4 表锁

innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.

6.5 死锁

死锁（Deadlock） 
所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。

  • 产生死锁的条件

    • 互斥条件

      一个资源每次只能被一个进程使用。

    • 请求与保持条件

      一个进程因请求资源而阻塞时，对已获得的资源保持不放。

    • 不剥夺条件

      进程已获得的资源，在末使用完之前，不能强行剥夺。

    • 循环等待条件

      若干进程之间形成一种头尾相接的循环等待资源关系。

  • 降低死锁的方式

    • 按同一顺序访问对象。
    • 避免事务中的用户交互。
    • 保持事务简短并在一个批处理中。
    • 使用低隔离级别。
    • 使用绑定连接。

  • 解决死锁的方法

    • 方法1

      1.查询是否锁表

      show OPEN TABLES where In_use > 0;

      2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）

      show processlist

      3.杀死进程id（就是上面命令的id列）

      kill id

    • 方法2

      1：查看当前的事务

      SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

      2：查看当前锁定的事务

      SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

      3：查看当前等锁的事务

      SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

      杀死进程

      kill 进程ID

7. 事务

一个支持事务（Transaction）的数据库，必须要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易方的要求

7.1 并发问题

  • 脏读

    （针对未提交数据）如果一个事务中对数据进行了更新，但事务还没有提交，另一个事务可以“看到”该事务没有提交的更新结果，这样造成的问题就是，如果第一个事务回滚，那么，第二个事务在此之前所“看到”的数据就是一笔脏数据。

  • 不可重复读

    （针对其他提交前后，读取数据本身的对比）不可重复读取是指同一个事务在整个事务过程中对同一笔数据进行读取，每次读取结果都不同。如果事务1在事务2的更新操作之前读取一次数据，在事务2的更新操作之后再读取同一笔数据一次，两次结果是不同的，所以，Read Uncommitted也无法避免不可重复读取的问题。

  • 幻读

    (针对其他提交前后，读取数据条数的对比） 幻读是指同样一笔查询在整个事务过程中多次执行后，查询所得的结果集是不一样的。幻读针对的是多笔记录。在Read Uncommitted隔离级别下， 不管事务2的插入操作是否提交，事务1在插入操作之前和之后执行相同的查询，取得的结果集是不同的，所以，Read Uncommitted同样无法避免幻读的问题。

7.2 事务四大特征(ACID)

  • 原子性(A)

    整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

  • 一致性(C)

    以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

    • 保护性
    • 不变性

  • 隔离性/串行化(I)

    隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

    • read uncommitted
    - 事物A和事物B，事物A未提交的数据，事物B可以读取到
- 这里读取到的数据叫做“脏数据”
- 这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别

    • read committed

      • 事物A和事物B，事物A提交的数据，事物B才能读取到
      • 这种隔离级别高于读未提交
      • 换句话说，对方事物提交之后的数据，我当前事物才能读取到
      • 这种级别可以避免“脏数据”
      • 这种隔离级别会导致“不可重复读取”
      • Oracle默认隔离级别

    • repeatable read

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

    • serializable

      • 事务A和事务B，事务A在操作数据库时，事务B只能排队等待
      • 这种隔离级别很少使用，吞吐量太低，用户体验差
      • 这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发

  • 持久性(D)

    在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

mysql参数调优参考: 参数配置

posted @ 2019-06-03 11:16  lbr617  阅读(357)  评论(0)  编辑  收藏  举报