Jamzy Wang

life is a struggle,be willing to do,be happy to bear~~~

Hadoop常见问题解决方案总结

2015-05-09 20:32

原创声明:本作品采用知识共享署名-非商业性使用 3.0 版本许可协议进行许可,欢迎转载,演绎,但是必须保留本文的署名(包含链接),且不得用于商业目的。

作业执行性能

(1) reduce执行很慢

reduce执行很慢的一个原因是数据数据分桶不均导致个别reduce节点的数据量远大于其他节点。解决方案是检查 分桶是否均匀,如不均匀考虑使用自定义的Partitioner(使用 IntHashPartitioner / MapIntPartioner:可以根据int前缀/后缀决定分到哪个桶),如果均匀考虑单个reduce的数据里是否过大,如果单个reduce的数据量过大则应该考虑增加reduce的数量。

(2) 数据倾斜(Data Skew)问题

由于数据本身的分布特点导致reduce阶段分桶不均,个别(1,2个)reduce严重拖慢整个任务的效率。以WordCount为例可以如下方式解决:

增加一轮任务,以随机数作为partition key

1
2
Map<doc> -> <rndkey, word1, count1>, <rndkey, word2, count2>
Reduce<rndkey, wordi, counti> -> <word, count>

第二轮再按word聚合

1
2
Map:直接输出
Reduce<wordi, counti> -> <word, count>

(3) map执行很慢

输入数据文件大小不均衡,个别的输入文件较其他输入文件要大很多。一种解决方案是切分个别大文件。

(4) 绝大多数的map任务都完成了,只有少数几个map老是进度很慢,完不成

Hadoop会把任务分配到许多个节点,其中一些慢的节点会限制整体程序的执行速度。这时Hadoop会引入“推测执行”过程:因为作业中大多数任务都已经完成,hadoop平台会在几个空闲节点上调度执行剩余任务复制,当任务完成时会向JobTracker通告。任何一个首先完成的复制任务将成为权威复制,如果其他复制任务还在推测执行中,hadoop会告诉TaskTracker去终止这些任务并丢弃其输出,然后reducer会从首先完成的mapper那里获取输入数据。推测执行为默认启用,设置 JobConf的mapred.map.tasks.speculative.executionmapred.reduce.tasks.speculative.execution 为false可以禁止推测执行。

(5) 小文件 、大文件

数据块的默认大小是64M,如果一个文件的大小小于64M,那么它也要占据一个数据块的大小。一个block在NameNode中对应一条记录(一般一条记录占用150字节),如果是大量的小文件,会消耗大量内存,所以要使用archive的方式来实现归并小文件。

默认情况下,每个map最多处理一个HDFS文件,所以用MapReduce处理大量的小文件时,就会产生过多的map task,线程管理开销将会增加作业时间处理大量小文件的速度远远小于处理同等大小的大文件的速度。因此Hadoop建议存储大文件。CombineFileInputFormat可以将若干个Split打包成一个,目的是避免过多的Map任务(因为Split的数目决定了Map的数目,大量的Mapper Task创建销毁开销将是巨大的)

(6)作业的某个任务阻塞了,长时间占用资源不释放,如何处理?

这种场景通常是由于软件Bug、数据特殊性等原因导致的,会让程序阻塞,任务运行停滞不前。这种事情经常发生,由于任务长时间占用着资源但不使用(如果不采取一定的手段,可能永远不会被使用,造成“资源泄露”),会导致资源利用率下降,对系统不利,那么,Hadoop MapReduce遇到这种情况如何处理呢?

在TaskTracker上,每个任务会定期向TaskTracker汇报新的进度(如果进度不变则不汇报),并由TaskTracker进一步汇报给JobTracker。当某个任务被阻塞时,它的进度将停滞不前,此时任务不会向TaskTracker汇报进度,这样,一定达到超时时间上限,TaskTracker会将该任务杀掉,并将任务状态(KILLED)汇报给JobTracker,进而触发JobTracker重新调度该任务。

在实际应用场景中,有些正常的作业,其任务可能长时间没有读入或者输出,比如读取数据库的Map Task或者需要连接其他外部系统的Task,对于这类应用,在编写Mapper或Reducer时,应当启动一个额外的线程通过Reporter组件定期向TaskTracker汇报心跳。

(7)任务JVM重用

mapred.job.reuse.jvm.num.tasks 默认为1,即每个Task都启动一个JVM来运行任务,当值为-1时,表示JVM可以无限制重用。当值为-1时,TaskTracker先判断当前当前节点是否有slot剩余,如果没有slot槽位才会判断当前分配的slot槽位中的JVM是否已经将当前task任务运行完,如果task已经运行完,才会复用当前JVM(同一Job的JVM才会复用)

注意:当一个Job的Task(尤其Task耗时很小)数目很大,由于频繁的JVM停启会造成很大开销,进行JVM复用会使同一个Job的一些静态数据得到共享,从而是集群性能得到很大提升。但是JVM重用会导致在同一个JVM中的碎片增加,导致JVM性能变差。

作业故障处理

在生产环境中,用户代码存在软件错误、进程崩溃、机器故障等都会导致失败。Hadoop判断的失败有不同级别类型,针对不同级别的失败有不同的处理对策,这就是MapReduce的容错机制。

(1) map或者reduce任务失败

任务失败分为3种情况:Task失败、子进程JVM退出、超时检测被关闭。

1.任务失败。最常见的是Map或Reduce任务的失败,即写的本身MR代码导致失败。发生Map或Reduce失败的时候,子任务JVM进程会在退出之前向上一级TaskTracker发送错误报告。错误报告最后会记录在用户的错误日志里面,TaskTracker会将此次task attempt标记为failed,释放一个任务槽slot用来运行另一个任务。

2.子进程JVM突然退出。可能由于JVM的bug导致,从而导致MapReduce用户代码执行失败。在这种情况下,TaskTracker会监控到进程以便退出,并将此次尝试标记为“failed”失败。

3.关闭了超时连接(把超时timeout设置成0)。所以长时间运行的任务永不会被标记failed。在这种情况下,被挂起的任务永远不会释放其所占用的任务槽slot,并随时间推移会降低整个集群的性能。

当任务执行失败后会重试,超过重试次数(默认为4),整个Job会失败。Hadoop 提供配置参数 mapred.max.ap.failures.percent 解决这个问题。如果一个 Job 有 200 个 MapTask ,参数设置为5,则单个 Job 最多允许 10 个 MapTask 失败(200×5%=10),其可以在配置文件 mapred-site.xml 里修改。

(2) TaskTracker失败

正常情况下,TaskTracker 会通过心跳向 JobTracker 通信,如果 TaskTracker 发生故障则心跳会停止,JobTracker 会将 TaskTracker 从等待任务调度的池中移除。TaskTracker失败分为两种情况:

1.map 阶段的情况。如果属于未完成的作业,reduce 阶段无法获取本地 Map 输出的文件结果,任务都需要重新调度和执行,只要是Map阶段失败必然是重新执行这个任务。

2.reduce 阶段的情况。自然是执行未完成的 reduce 任务。因为 reduce 只要执行完了就会把输出写到 HDFS 上。

(3) JobTracker失败

jobtracker和hdfs的namenode一样也存在单点故障,JobTracker失败会导致作业最终失败。hadoop框架里包含zookeeper框架,zookeeper可以结合jobtracker,用几台机器同时部署jobtracker,保证一台出故障,有一台马上能补充上,不过这种方式也没法恢复正在跑的mapreduce任务。

(4) map结果存放磁盘损坏

如果节点没有挂,只是存放map任务结果的磁盘损坏了,则分两种情况:如果map任务的结果已经传输给了所有reduce,那么所有reduce任务可以正常执行下去,整个job会正常执行。如果尚有部分reduce任务没有获得map任务,那么当reduce任务读取那个已经运行完成的map任务(但结果已经损坏)时,会尝试读取若干次,如果尝试次数超过了某个上限值,则会通过RPC告诉所在的TaskTracker该map任务的结果已经损坏,而TaskTracker则进一步通过RPC告诉JobTracker,JobTracker收到该消息后,会重新调度该map任务进而重新计算生成结果。

目前Hadoop实现中,reduce任务尝试读取map任务结果的时间间隔是指数形式递增的,计算公式是 10000*1.3^noFailedFetches,其中noFailedFetches取值范围为 MAX{10, numMaps/30},也就是说,如果map task数目是300,则需要尝试10次才会发现map结果结果已经损坏,尝试时间间隔分别是10s,13s,21s,28s,37s,48s,62s,81s和106s,需要非常长的时间才能发现。

常用技巧

(1) 多路输出实现

一般情况下map/reduce的输出是一个存储在HDFS上文件的形式,如果希望一个map/reduce任务希望产出多份输出数据怎么办?一种解决方案如下:

hadoop命令增加一个参数:

1
-outputformat org.apache.hadoop.mapred.lib.SuffixMultipleTextOutputFormat

reduce阶段每行的 value 末尾增加’#A’, ‘#B’, … 不同的字母会被输出到对应的 part 文件:part-00000-A, part-00000-B 最多支持26 路输出 A~Z 如果输出数据只有key没有value,那么#A 前面需要补一个 ’\t’

(2) map/reduce程序如何做本地测试

在调试mapper,reducer时,可使用如下命令在本地调试:cat filename | mapper.sh | sort –k1,3 | reducer.sh,其中mapper.sh和reducer.sh可以是任何可执行文件。

易错点

(1) map任务前的数据分割不保证相同的key在一起。因此在reduce阶段需要先做merge(边merge边sort)。

(2) map任务的输出中相同的key会被copy到同一个reducer,但是一个reducer并不只放一个key,因此在对同一个key下的数据进行操作时,仍需判断当前行的key是否与上一行的key相同。

Ref

MySQL中的索引详解

2015-04-27 23:24

原创声明:本作品采用知识共享署名-非商业性使用 3.0 版本许可协议进行许可,欢迎转载,演绎,但是必须保留本文的署名(包含链接),且不得用于商业目的。

数据库性能优化中最常推荐的方法是什么?

最容易优化SQL查询速度的方法是什么(在不改变表结构的前提下)?

最容易被忽视的数据库开发和维护技巧是什么?

索引

数据库的磁盘读写

在讲解索引之前,先来看一下数据是如何存储在数据库中的。数据库中的数据是存放在磁盘中的,磁盘的读取相对于内存的读取效率是非常非常低的,Latency numbers every programmer should know一文给出了各种常见读写操作的时间对比:

L1 cache reference …………………………………0.5 ns

Branch mispredict ………………………………….5 ns

L2 cache reference …………………………………7 ns

Mutex lock/unlock …………………………………25 ns

Main memory reference ………………………..100 ns

Compress 1K bytes with Zippy ………………3,000 ns = 3 µs

Send 2K bytes over 1 Gbps network ………20,000 ns = 20 µs

SSD random read ………………………………………150,000 ns = 150 µs

Read 1 MB sequentially from memory ..250,000 ns = 250 µs

Round trip within same datacenter …………..500,000 ns = 0.5 ms

Read 1 MB sequentially from SSD* …………..1,000,000 ns = 1 ms

Disk seek ………………………………………………………..10,000,000 ns = 10 ms

Read 1 MB sequentially from disk ………….20,000,000 ns = 20 ms

Send packet CA->Netherlands->CA …………..150,000,000 ns = 150 ms

从上面的数据中我们可以看到在内存中顺序读取1M的数据需要250us,而在磁盘中顺序读取1MB的数据需要20ms,对比一下二者的读取速度,可以发现内存读写要比磁盘读写快近千倍。总之需要记住一点:

1
磁盘IO性能非常低,这会严重影响数据库系统的性能。

磁盘的这个特性不单单对数据库会产生影响,在其他程序中,当代码需要读取一个很大的文件,比如10G大小的文件,也会因为磁盘IO的低效耗费很长时间。

磁盘空间被划分为许多大小相同的块(Block)或者页(Page),Block和Block之间是以链表的形式串联在一起的,一个Block连着另一个Block。而数据库中的数据是以行(Row)为单位一行一行的存放在磁盘块中,一个磁盘块中存放几行或者几十行的数据(具体一个块能存放的数据的行数取决于表中每行数据的大小),如图所示:

磁盘读写的另一个特点是:

1
在访问数据时,一次从磁盘中读出或者写入至少一个完整的Block

也就是说不能从磁盘中单独读取一行数据,只能一下子读取几行或者几十行数据。

数据库的基本操作有INSERT、UPDATE、DELETE、SELECT4种,那么这4种操作是如何实现的呢?

  • SELECT

    1) 定位数据

    2) 读出数据所在的块,对数据加工

    3) 返回数据给用户

  • UPDATE、DELETE

    1) 定位数据

    2) 读出数据所在的块,修改数据

    3) 写回磁盘

  • INSERT

    1) 定位数据要插入的页(如果数据需要排序)

    2) 读出要插入的数据页,插入数据.

    3) 写回磁盘

那么数据库是如何定位数据的呢? 通过表扫描(Table Scan),所谓表扫描指的是:

1
从磁盘中依次读出所有的数据块,一行一行的进行数据匹配,直到找到所需要的行。

这种扫描方法也叫作全表扫描(full table scan),可以看出这种表扫描定位数据的时间复杂度是O(n)(类似在链表中查找特定的值),如果一张表的所有数据占用了10000个块,那么即使只查询一行数据,在最坏情况下需要读取10000个块,平均每次定位数据需要读取5000个块。

上文中我们已经强调过,磁盘的读写性能是非常低的,全表扫描需要大量的磁盘IO操作(这些IO操作中极大部分是无效的,因为不是我们需要找的数据),这极大的影响了数据定位的性能。而数据定位又是所有数据操作必须的,因此数据定位操作的效率会直接影响所有的数据操作的效率。自然地,我们需要寻找一种方法来减少磁盘IO。减少磁盘IO的方法有:

  • 1)减少数据占用的磁盘空间 压缩算法、优化数据存储结构

  • 2)减少访问数据的总量 读出或写入的数据中,有一部分是数据操作所必须的,这部分称作有效数据。剩余的部分则不是数据操作必须的数据,称为无效数据。例如,查询id是‘000183’的记录。那么这条记录是有效记录,其他记录则是无效记录。我们要努力减少无效数据的访问。

索引

在数据库中,索引的含义与日常意义上的“索引”一词并无多大区别(想想查字典的过程),它用于提高数据库表数据访问速度,它有如下特点:

  • 索引是对数据库表中一列或多列的值进行排序的一种结构。
  • 索引是表数据的目录结构。
  • 索引提供指向存储在表的指定列中的数据值的指针,然后根据指定的排序顺序对这些指针排序。
  • 索引可以避免全表扫描。多数查询可以仅扫描少量索引页及数据页,而不是遍历所有数据页。

下面将具体阐述索引的作用。

我们发现在多数情况下,定位操作并不需要匹配整行数据, 而是只需要匹配某一列或某几列的值,这些用来确定一条数据的列,统称为键(Key),如:

1
2
3
4
5
匹配1列:
select user_name, user_email, user_age, user_gender from user_info where user_id = "01103300";

匹配2列:
select user_email, user_gender from user_info where user_name = "myname" AND user_age = 15;

那么根据减少无效数据访问的原则,我们可以将键的值存放到独立的块中,并且为每一个键值添加一个指针, 指向原来的数据块,这种键值组织方式叫作“Dense Index”,如下图所示:

这种Dense Index就是最初的索引实现方式。当有了这种索引之后,数据的定位操作不再需要进行全表扫描而只需要进行索引扫描(Index Scan),依次读出所有的索引块,进行键值的匹配。当找到匹配的键值后,根据该行的指针直接读取对应的数据块,进行操作。

假设一个块中能存储100行数据,10,000,000行的数据需要100,000个块的存储空间。假设键值列(+指针)占用一行数据1/10的空间。那么大约需要10,000个块来存储Dense索引。因此我们用大约1/10的额外存储空间换来了大约全表扫描10倍的定位效率。

在实际的应用中,这样的定位效率仍然不能满足需求,于是,许多索引优化方法被提出。

我们来确定一下索引优化需要实现什么功能:

1
对于给定的某个表中的某列值,如何组织这些值使得对于特定值的查找能够最大限度的减少磁盘访问。

比如,我们要对一个表中的id(整型)建立索引,我们需要解决的问题就是“给你一个整数序列,如何组织这个整数序列使得查找某个整数的性能达到最优”。对于这种数据的查找问题,往往通过排序二分查找算法来提高性能。假设有如下数据:

1
2
3
4
5
6
7
表结构:
id    name        age       email         gender
1   Brighton   10  abc@gmail.com    male
2   Downtown   15  def@gmail.com    female
3   Mianus     29  dfd@gmail.com    female
4   Redwood    54  odk@gmail.com    male
...

每一行数据对应的磁盘块号如下:

1
2
3
4
5
6
(id, block id)
1, 894454
2, 453654
3, 342344
4, 435435
...

上述(id,block id)代表的就是索引数据。现在需要查找id = 100000的数据所在的行该怎么做呢? 一种做法自然是遍历这个索引数据,如果id有序,我们可以用二分查找来加快这个查找过程。如果id无序或者我们用name这个字段做索引呢?可以先对索引排序再用二分查找算法查找。

上面的表述中我们默认索引数据按照数组的方式组织,那么是否还有其他更好的数据组织方式呢?当然有,比如b-tree,比如hash表。后文中我们将具体介绍b-tree, hash表实现的索引的区别。

下面再用一个具体的例子来对比一下不用索引和使用索引的数据的读取效率对比。假设有如下的数据库表结构:

1
2
3
4
5
Field name       Data type      Size on disk
id (Primary key) Unsigned INT   4 bytes
firstName        Char(50)       50 bytes
lastName         Char(50)       50 bytes
emailAddress     Char(100)      100 bytes

假设现在表中有 r = 5,000,000 条记录,每条记录的长度是 R = 204 bytes, 这些记录存储在MyISAM存储引擎中(默认会为Primary Key建立索引),磁盘块的默认大小是 size B = 1,024 bytes。那么,一个磁盘块中可以存 bfr = (B/R) = 1024/204 = 5 条记录,存所有记录需要 N = (r/bfr) = 5000000/5 = 1,000,000 块。那么通过全表扫描的方式定位数据平均需要读取 N/2 = 500,000 个磁盘块。

现在有两个查询操作,一个通过id查询,一个通过frirstName查询,我们来对比一下二者的性能差异。

  • 基于id查询:

由于id已排序,所以可以用二分查找来降低查找时间,定位数据平均只需要读取 log2 1000000 = 19.93 = 20 磁盘块。

  • 基于firstName查询:

由于firstName 没有经过排序,所以不能用二分查找定位数据,同时在表中firstName也不是唯一的,因此定位数据需要进行全表扫描,需要读取N = 1,000,000 个块。

从上述对比可以看出索引的巨大优化效果。

索引类型

常见的索引类型根据其实现包括hash index、btree index、full text index等, 索引类型的选择主要取决于应用的不同需求:

1
2
3
4
hash index: 主要用于满足精确匹配
btree index: 主要用于满足范围查询、精确匹配
fulltext index: 主要用于全文关键字查询
spatial (R-Tree) index: 用于空间索引

MySQL中不同的存储引擎支持不同的索引类型:

1
2
3
heap 引擎支持 hash index
myisaminnodb 引擎支持 btree index
myisaminnodb-ft 支持 fulltext index

hash index

hash 索引是一种非常高效的索引类型,一般实现为 hashtable, 属于内存型索引,典型的 key/value 类型, 在 mysql中只有memory storage engine支持 hash indexe。

hash索引的特点:

1
2
3
1. 只支持通过column值精确匹配(=, IN(), and <=>),不支持范围查询(> ,<),不支持部分匹配(like)
2. 不支持 order by
3. 不支持 index statistics( MySQL 查询计划没有帮助)

b-tree

B树是一种适合磁盘等慢速设备的索引结构,能够以较少磁盘读取次数查找数据(至于B树为什么能够减少磁盘访问推荐阅读此文:从B树、B+树、B*树谈到R 树),Oracle、Mysql均使用B树实现索引。

为何不是二叉树存储索引?

1)树的层次太深。由于二叉树出度为2,假如Key的数量N很大的话,会造成树的层次过多。这样造成的直接结果是若要查找的Key在比较靠近叶子节点的深度时,会造成大量磁盘IO。(为了存取设计的简易,通常将每一个节点放进单独的磁盘块中

2)通常就算是用二叉树作为索引的存储结构,也并非用的二叉树,而是二叉平衡树或者红黑树,因为二叉树是可能不平衡的,若要保持平衡,在每一次插入、更新或者删除节点的时候就要对树做相应的旋转操作,以保持起平衡。这本身也是很耗费计算资源的操作

能用 B+ 树查询的操作(前提是查询条件所在列建了索引)

1
2
3
4
1) 精确查找匹配某个值的记录(Match the full value),如查找last nameJack的记录
2)查找以某个值为前缀的记录(Match a column prefix),如查找以 J开头的last name
3)查找某个范围内的记录(Match a range of values),比如查找 last name Allen  barrymore
4)上述1)、2)、3)的任意组合,只能是建了索引的相同列。

B树的索引分为两种,非聚集索引和聚集索引(cluster index),非聚集索引与聚集索引相比:

1
2
3
4
1. 叶子结点并非数据结点
2. 叶子结点为每一真正的数据行存储一个“键-指针”对
3. 叶子结点中还存储了一个指针偏移量,根据页指针及指针偏移量可以定位到具体的数据行。
4. 类似的,在除叶结点外的其它索引结点,存储的也是类似的内容,只不过它是指向下一级的索引页的。

非聚集索引

非聚集索引是MySQL的MyISAM索引实现方式。MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。

  • 非聚集索引的查询操作

此处输入图片的描述

  • 非聚集索引与插入操作

如果一张表包含一个非聚集索引但没有聚集索引,则新的数据将被插入到最末一个数据页中,然后非聚集索引将被更新。如果也包含聚集索引,该聚集索引将被用于查找新行将要处于什么位置,随后,聚集索引、以及非聚集索引将被更新。

  • 非聚集索引与删除操作

如果在删除命令的Where子句中包含的列上,建有非聚集索引,那么该非聚集索引将被用于查找数据行的位置,数据删除之后,位于索引叶子上的对应记录也将被删除。如果该表上有其它非聚集索引,则它们叶子结点上的相应数据也要删除。 如果删除的数据是该数所页中的唯一一条,则该页也被回收,同时需要更新各个索引树上的指针。

注意:由于没有自动的合并功能,如果应用程序中有频繁的随机删除操作,最后可能导致表包含多个数据页,但每个页中只有少量数据。

聚集索引

聚集索引是MySQL的InnoDB索引实现方式。和MyISAM索引实现相比,InnoDB的数据文件本身就是索引文件。在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。每个叶子节点包括 primary key(或者 row_id), transactionID, rollback pointer 和行数据, 非叶子节点只包括被索引列的索引信息。

innodb 中的 btree 实际上是 b+ tree。为何是b+tree而不是b-tree?

1
2
3
1.b+tree出度相比b-tree更大,因为其非叶节点不需要存储Key相对应的物理地址指针
2.叶节点包含了所有的Key,查询比较均衡
3.每个叶节点都保存了指向下一个叶节点的指针,对于范围查询存取更方便

聚集索引主要优点:

1
2
相关的数据临近存放, 利于磁盘存取
row 的读取更快,因为 row  index 一起存放

聚集索引主要缺点:

1
2
3
4
如果访问模式与存储顺序无关,则 cluster index 无用
按主键顺序插入和读取最快, 但是如果按主键随机插入(特别是字符串) 则读写效率降低
更新 cluster index 列的代价较大, 会将整个 row 重新写到新的位置上,并且所有 secondary index 也要更新
如果 cluster index 建立在内容较长的字符串字段上, 会导致所有的 secondary index 都较大
  • 聚集索引的查询操作

此处输入图片的描述

  • 索引更新

最简单的情况下,插入操作根据索引找到对应的数据页,然后通过挪动已有的记录为新数据腾出空间,最后插入数据。如果数据页已满,则需要拆分数据页(页拆分是一种耗费资源的操作,一般数据库系统中会有相应的机制要尽量减少页拆分的次数,通常是通过为每页预留空间来实现): 1. 在该使用的数据段(extent)上分配新的数据页,如果数据段已满,则需要分配新段。 2. 调整索引指针,这需要将相应的索引页读入内存并加锁。 3. 大约有一半的数据行被归入新的数据页中。 4. 如果表还有非聚集索引,则需要更新这些索引指向新的数据页。

联合索引

在 MySQL 中有一个限制,一个表在一次查询中最多使用一个索引。但是我们对一张表的很多字段可能都需要使用索引,这个时候怎么办呢? 正是联合索引来解决这个问题。

联合索引可以用于的查询(下面的所有例子都基于联合索引 ABC(按照 A、B、C 顺序建立的联合索引)):

  • 1)联合索引 ABC 依然符合最左前缀的原则

即只有 ABC、AB、A 三种情况可以使用到索 引, 也不能跳 index,即 AC 同时查询只能使用到联合索引的 A 部分。

  • 2) 第一个列的精确匹配、第二列的范围查询

例如: select d from table where A = “x” and B >= “y” and C = “z”

只有 A = “x”和 B >= “y”能使用到索引, C = “z”不能使用索引, 因为如果联合索引 ABC 中的某一个字段使用了范围查询,则后面的字段不能再使用索引。

联合索引,经常要使用范围查询的, 将该范围查询索引字段尽量放在后面,因为联合索引中某个索引使用范围查询后, 其后的索引将不再有效。

Spatial (R-Tree) index

MyISAM支持R-Tree索引,不需要最左前缀匹配。主要用户地理位置相关查询。有个GEOMETRY数据类型采用R-tree索引。

全文索引

全文检索是一种基于关键字的查询,支持全文内的检索,而 btree、hash 只能支持精确匹配和 leftmost 查询, 另外, 全文检索不像 btree、hash 那样要求准确的结果。

MyISAM 是少有的支持全文检索的引擎, 其特点如下:

  • 全文检索语法支持有限
  • 百万量级

相比于MyISAM, Sphinx 是一款非常优秀的全文检索系统,其特点如下:

  • 功能和性能都很强大的全文检索系统
  • 十亿级别数据规模
  • 非实时检索,需要主动拉数据
  • 相关性不易扩展

索引的负面影响

虽然索引可以提高查询速度,但是它们也会导致数据库系统更新数据的性能下降,因为大部分数据更新需要同时更新索引,一般会减小1/3 ~1/2。因此只有在频繁查询,并且更新操作较少的数据上建立索引。可以通过profile,日志和实验分析获取。

MySQL中如何建索引

MySQL会自动给申明为PRIMARY KEYKEYUNIQUEINDEX的列建立索引。建立索引语法如下:

1
2
3
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
USING [BTREE | HASH | RTREE]
ON table_name (column_name [(length)] [ASC | DESC],...)

删除索引语法如下:

1
DROP INDEX index_name ON table_name

我们尝试在products表上对name列建立索引:

此处输入图片的描述

然后我们用 show index from products; 命令查看products表上已经建立的索引,如:

此处输入图片的描述

从上图中我们可以看到,products表中建了2个索引,一个是针对productID的索引,这事MySQL自动创建的,另一个就是我们刚才对name建立的索引。

这里需要注意几点:

1
2
3
unique索引指的是索引上的值都是唯一的。
fulltext索引只能在MyISAM引擎上创建的表中才能建立,且要求列的属性是`CHAR`、`VARCHAR`、`TEXT`。
spatial索引只能在MyISAM引擎上创建的表中才能建立。

同时using 字段中的值的选择取决于表所使用的引擎类型:

此处输入图片的描述

MySQL中如何正确使用索引

索引设计优化索引设计规范:

1
2
3
4
5
6
7
8
索引不是越多越好。虽然能改善查询,但是也能减慢插入和更新的速度
索引的创建需要以SQL为基础,一般需要提供所有对表的相关SQL来均衡索引的创建
长字符类的字段适当情况添加前缀索引,减小索引文件大小,提高检索效率。比如idx_name(name(10))
对于InnoDB引擎的表,最好使用自增ID或者数值类字段做主键
索引中的字段数建议不超过5
合理创建联合索引(避免冗余),使用前缀索引,(a,b,c) 相当于 (a) (a,b) (a,b,c)
模糊匹配like %xxx”,不会使用索引
不在索引列进行计算,否则不会使用索引

Ref

How does database indexing work?

How to Design Indexes, Really

由浅入深理解索引的实现

MySQL Query Optimization

SQL Indexing For Dummies

How MySQL Uses Indexes

How b-tree database indexes work and how to tell if they are efficient (100’ level)

MySQL索引背后的数据结构及算法原理

MySQL中的事务与锁

2015-04-23 03:29

原创声明:本作品采用知识共享署名-非商业性使用 3.0 版本许可协议进行许可,欢迎转载,演绎,但是必须保留本文的署名(包含链接),且不得用于商业目的。

事务

数据库事务(简称:事务)是数据库管理系统执行过程中的一个逻辑单位,由一个有限的数据库操作序列构成。一个数据库事务通常包含了一个序列的对数据库的读/写操作。它的存在包含有以下两个目的:

1
2
1. 为数据库操作序列提供了一个从失败中恢复到正常状态的方法,同时提供了数据库即使在异常状态下仍能保持一致性的方法。
2. 当多个应用程序在并发访问数据库时,可以在这些应用程序之间提供一个隔离方法,以防止彼此的操作互相干扰。

当事务被提交给了DBMS(数据库管理系统),则DBMS(数据库管理系统)需要确保:

1
2
该事务中的所有操作都成功完成且其结果被永久保存在数据库中,如果事务中有的操作没有成功完成,则事务中的所有操作都需要被回滚,
回到事务执行前的状态;同时,该事务对数据库或者其他事务的执行无影响,所有的事务都好像在独立的运行。

Basically any time you have a unit of work that is either sensitive to outside changes or needs the ability to rollback every change, if an error occurs or some other reason.

ACID

并非任意的对数据库的操作序列都是数据库事务。数据库事务拥有以下四个特性: 原子性(Atomiocity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability),习惯上被称之为ACID特性。

  • 原子性(Atomicity)

一个事务中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。

  • 一致性(Consistency)

事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。这表示写入的数据必须完全符合所有的预设规则,这包含数据的精确度、关联性以及后续数据库可以自发性地完成预定的工作,如两用户转款前后的金额总和要一样。

  • 隔离性(Isolation)

当两个或者多个事务并发访问(此处访问指查询和修改的操作)数据库的同一数据时所表现出的相互关系。通常来说,一个事务的执行不能被其他事务干扰,即一个事务内部的操作及使用的数据对其他事务是隔离的,并发执行的各个事务之间互相不干扰。但事务之间的真实隔离性取决于事务的隔离模式。

SQL标准中定义了4中隔离级别(或称为隔离模式),低级别的隔离可以执行更高的并发,系统的开销也更低。这4种级别包括读未提交(read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(serializable)。

此处输入图片的描述

给出每种隔离级别的实际代码例子

  • READ UNCOMMITTED(未提交读)

该隔离级别下,事务中的修改,即使没有提交,对其他事务也都是可见的。因此,对于其他业务,可能会产生“脏读”,从而引起很多问题。同时从性能层面考虑,READ UNCOMMITED 和其他隔离级别也差不多,因此实际场景中一般很少使用。

脏读: 读取到部分修改的、事务未提交的数据, 即SELECT会读取其他事务修改而还没有提交的数据。比如:事务T1更新了一行记录的内容,但是并没有提交所做的修改;事务T2读取更新后的行,然后T1执行回滚操作,取消了刚才所做的修改。现在T2所读取的行就无效了。

  • READ COMMITTED(提交读)

大多数据库的默认隔离级别如Oracle,但MySQL不是。本隔离级别下,满足隔离的基本定义:事务在提交前所做的修改对其他业务不可见。该级别下,两次执行同样的查询,可能会得到不一样的结果,产生不可重复读的效果。

不可重复读: SELECT的时候无法重复读,即同一个事务中两次执行同样的查询语句,若在第一次与第二次查询之间时间段,其他事务又刚好修改了其查询的数据且提交了,则两次读到的数据不一致。比如:事务T1读取一行记录,紧接着事务T2修改了T1刚才读取的那一行记录。然后T1又再次读取这行记录,发现与刚才读取的结果不同。这就称为“不可重复”读,因为T1原来读取的那行记录已经发生了变化。

  • REPEATABLE READ(可重复读)

MySQL默认隔离级别,该隔离级别解决了不可重复读问题——SELECT的时候可以重复读,即同一个事务中两次执行同样的查询语句,得到的数据始终都是一致的,但还是存在幻读。

可重复读: 在同一个事务内的查询都与事务开始时刻一致的,InnoDB默认级别。

幻读: 事务T1读取一条指定的WHERE子句所返回的结果集。然后事务T2新插入一行记录,这行记录恰好可以满足T1所使用的查询条件中的WHERE 子句的条件。然后T1又使用相同的查询再次对表进行检索,但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”,因为对T1来说这一行就像突然出现的一样。InnoDB 通过多版本并发控制(MVCC)解决幻读问题。

  • SERIALIZE(可串行化)

强制事务串行执行。该隔离级别下,会对读取的每一行数据上都加上锁,因而对锁机制的管理比较耗系统资源,数据库一般都不会用这个隔离级别。与可重复读的唯一区别是,默认把普通的SELECT语句改成SELECT …. LOCK IN SHARE MODE。即为查询语句涉及到的数据加上共享琐,阻塞其他事务修改真实数据。

在MySQL中,可以通过 select @@tx_isolation;命令查看当前的事务隔离级别,如:

此处输入图片的描述

也可以通过执行命令set session transaction isolation level read committed;修改事务隔离级别,如:

此处输入图片的描述

此处输入图片的描述

需要注意的是上述方式修改的事务隔离级别仅对当前session有效。如果要对所有新建的连接设置隔离级别,可以用set global transaction isolation level read committed; 它将决定新建连接的初始隔离级,但不会改变已有连接的隔离级。可以通过select @@global.tx_isolation;命令查看global transaction isolation level:

此处输入图片的描述

如果想要全局修改事务隔离级别,可以在my.cnf 配置文件中修改,只需在最后加上”

1
2
3
#可选参数有:READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
[mysqld]
transaction-isolation = READ-UNCOMMITTED
  • 持久性(Durability)

已被提交的事务对数据库的修改应该永久保存在数据库中,接下来其他的其他操作或故障不应该对其执行结果有任何影响,即提交的事务一定保证写入磁盘。

事务的实现

1. 如何保证原子性(A)?

数据库中与原子性相关的操作有rollback和commit。commit用于正常提交一个事务,rollback用于将事务中之前的操作回滚。第三种情况是数据库出现异常时,如断电,事务执行一半而退出。 事务的整个执行过程说明如下: (1) 每个事务开始时,系统会为该事务分配一个时间戳(唯一标识该事务)、回滚段和undo段。 (2) 事务中的每条SQL在执行修改操作前都会写undo日志,然后再将更新的内容写入undo段。 (3) 执行commit时,系统将修改的数据写入实际内存,并将修改信息写入回滚段。 (4) 执行rollback时,系统将undo段内容失效。 (5) 当系统在执行事务过程中出现异常退出后,系统再次启动,会从undo日志中恢复。

2. 如何保证一致性(C)?

事务一致性的保证和原子性和隔离性都有关系,即系统保证事务一致性的前提是保证事务的原子性和隔离性。上文中的“脏读”、“不可重复读”、“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。

3. 如何保证隔离性(I)?

目前数据库实现的事务隔离方式分两种:

1
2
3
1、基于悲观并发控制思想: 在读取数据前,对其加锁,阻止其他事务读数据进行修改。
2、基于乐观并发控制思想:不加任何锁,通过一定的机制生成一个数据请求时间点的一致性数据快照(snapshot),
并用这个快照来提供一定级别(语句级或事务级)的一致性读取。
  • 悲观并发控制

悲观并发控制,正如其名,它指的是对数据被外界(包括本系统当前的其他事务,以及来自外部系统的事务处理)修改持保守态度,因此,在整个数据处理过程中(当前事务中),将数据处于锁定状态,即读取数据时给加锁,其它事务无法修改这些数据,修改删除数据时也要加锁,其它事务无法读取这些数据。那只有当这个事务把锁释放,其他事务才能够执行与该锁冲突的操作。

悲观锁的实现,往往依靠数据库提供的锁机制(Lock-Based Concurrency Control)(也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据)。

悲观并发控制主要用于数据争用激烈的环境,以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。然而,数据库又是个高并发的应用,同一时间会有大量的并发访问,如果加锁过度,会极大的降低并发处理能力,于是就有了乐观并发控制(又名“乐观锁”,Optimistic Concurrency Control,缩写“OCC”)。

  • 乐观并发控制

在关系数据库管理系统里,乐观并发控制(又名“乐观锁”,Optimistic Concurrency Control,缩写“OCC”)是一种并发控制的方法。相对悲观锁而言,乐观锁机制采取了更加宽松的加锁机制。它假设多用户并发的事务在处理时不会彼此互相影响,各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前,每个事务会先检查在该事务读取数据后,有没有其他事务又修改了该数据。如果其他事务有更新的话,正在提交的事务会进行回滚。

乐观并发控制多数用于数据争用不大、冲突较少的环境中,这种环境中,偶尔回滚事务的成本会低于读取数据时锁定数据的成本,因此可以获得比其他并发控制方法更高的吞吐量。

乐观锁,大多是基于MVCC (Multi-Version Concurrency Control),即多版本控制协议实现。MVCC最大的好处是读不加锁,读写不冲突。不加任何锁,通过一定的机制生成一个数据请求时间点的一致性数据快照(snapshot),并用这个快照来提供一定级别(语句级或事务级)的一致性读取。从用户角度来看,好像是数据库可以提供同一数据的多个版本,因此这种技术又叫做多版本并发控制(Mutil Version Concurrency Control,简称MVCC或MCC),也称为多版本数据库。 在读多写少的OLTP应用中,读写不冲突是非常重要的,极大的增加了系统的并发性能,这也是为什么现阶段,几乎所有的RDBMS,都支持了MVCC。要说明的是,MVCC的实现没有固定的规范,每个数据库都会有不同的实现方式,这里讨论的是InnoDB的MVCC。

4. 如何保证持久性(D)?

持久性的保证需要日志的支持,数据库写日志的原则是执行写数据前要先写日志。

针对事务一些推荐的做法

  • 开启新事务前先rollback一下
  • 每次做完update后校验affected_rows是否是期望的
  • 考虑重连逻辑
  • 尽量避免大事务
  • 加锁资源使用要有一定的顺序, 避免死锁
  • mysql的事务尽量小,使用完,立即commit或rollback.不要起一个过大的事务
  • 避免尝试去锁一个不存在的记录,for update语句where条件请使用主键
  • 避免过多的for update集合
  • mysql单表记录保持在1000W以下,以获得较好的性能
  • 需要修改mysql 锁等待时间,避免for update等待时间超长,造成系统阻塞。innodb_lock_wait_timeout 参数

锁就是事务T在对某个数据对象例如表、记录等操作之前,先向系统发出请求,对其加锁。加锁后事务T就对该数据对象有了一定的控制。当多个客户端同时访问和修改相同的数据时,锁机制可以保证数据的一致性。

InnoDB的锁结构如下:

1
2
3
4
5
6
7
8
9
10
11
12
/* lock_struct结构用于描述InnoDB的锁,其中一条记录由多个结点组成 */
struct lock_struct{
  trx_t*    trx;    /* 拥有该锁的事务 */
  UT_LIST_NODE_T(lock_t)  trx_locks;  /* 该事务的所有锁 */
  ulint   type_mode;  /*锁类型 */
  hash_node_t hash;   /*被锁记录的结点链表 */
  dict_index_t* index;    /*被锁记录的索引 */
  union {
    lock_table_t  tab_lock;/*表锁 */
    lock_rec_t  rec_lock;/* 行锁 */
  } un_member;
};

InnoDB中的每个行信息由多个结点组成,每个结点对应该行的物理存储的页号和偏移量。行级锁的数据结构如下:

1
2
3
4
5
6
/* lock_rec_struct结构用于描述InnoDB的行锁,对应于物理页和偏移量 */
struct lock_rec_struct{
  ulint space;    /* 偏移量 id */
  ulint page_no;  /* 页号 */
  ulint n_bits;   /* 锁bitmap的位数 */
};

锁的粒度

锁的粒度又称为锁的级别,实际上是可控制的资源的范围级别。常见的有表级锁、行级锁和页级锁,三种方式各有利弊。行级锁的锁粒度最小,表级锁的锁粒度最大,页级锁趋于中间。

锁粒度在锁机制存在的情况下,提高共享资源并发性的方法是让锁定对象更准确。尽量只锁定需要修改的数据部分。理想的情况下,精确锁定会修改的数据片段。另一方面,加锁也需要消耗资源。锁的各种操作,包括获得锁、检查锁是否已解除、释放锁等,都会增加系统开销。如果锁的操作比较频繁,系统会花大量的时间来管理锁,如不是执行数据存储,则兄台那个的性能也会受到影响。

  • 表级锁(MyISAM,MEMORY)

表级的锁是锁定整张表的资源。表级锁逻辑简单,可以较容易的避免死锁。但表级锁的并发性较差,因为它锁住的是整个表,对于大量并发读写的应用不太适用。

如果加的是S锁,则其他事务只能再对此表加S锁,而不能加X锁。如果加的是X锁,则其他事务不能加任何锁。 可以理解为如果有事务在读表A中的数据,则其他事务此时只能读表A,不能写表A。如果有事务在插入或者更新表A中的数据,则其他事务此时不能读表A,也不能写表A。

表级锁的优势:

1
2
3
4
 适用于表的大部分语句用于读取的情况。
 对严格的key进行读取和更新,可以更新或删除可以用单一的读取的key来提取的一行;
 适用于SELECT 结合并行的INSERT,并且只有很少的UPDATEDELETE语句的情况。
 适用于在整个表上有许多扫描或GROUP BY操作,没有任何写操作的情况。
  • 行级锁(InnoDB)

行级的锁是仅对指定的记录进行加锁,这样其它进程还是可以对同一个表中的其它记录进行操作。行级锁在高并发下可以得到较高的性能,但实现较为复杂,会带来很多bug。行级锁较难避免死锁,许多实现都是采用检测死锁机制来避免死锁。

如果有事务在读表A中的行l的数据,则其他事务此时可以读表A的所有数据,可以插入数据,可以更新除l行以外的数据。如果有事务在更新表A中的行l的数据,则其他事务此时不能不能读取或更新行l的数据,表A中的其他数据不受限制。

行级锁的优势:

1
2
3
 当在许多线程中访问不同的行时只存在少量锁定冲突。
 回滚时只有少量的更改。
 可以长时间锁定单一的行。
  • 页级锁(BDB)

页级锁一次锁定相邻的一组记录。表级锁速度快,但冲突多,行级冲突少,但速度慢。页级是两者的一个折衷方案。

锁的分类

锁可以分为两类,即共享锁(读锁、S锁)、独占锁(排它锁、写锁、X锁)。

  • 共享锁(读锁、S锁)

•如果事务T对数据A加上共享锁后,则其他事务只能对A再加共享锁,不能加排他锁,直到已释放所有共享锁。获准共享锁的事务只能读数据,不能修改数据。

•如果在资源上没有独占锁,把一个共享锁定放在它上面。否则,把锁请求放在共享锁定队列中。

  • 独占锁(排它锁、写锁、X锁)

•如果事务T对数据A加上排他锁后,则其他事务不能再对A加任任何类型的锁,直到在事务的末尾将资源上的锁释放为止。获准独占锁的事务既能读数据,又能修改数据。

•如果在资源上没有锁,在它上面放一个独占锁。否则,把锁定请求放在独占锁定队列中。

MySQL InnoDB中的事务与锁

InnoDB实现了两种类型的行锁:

1
2
共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。

另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁。意向锁是InnoDB自动加的,不需用户干预。

1
2
意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。

上述锁模式的兼容情况具体如表所示:

此处输入图片的描述

如果一个事务请求的锁模式与当前的锁兼容,InnoDB就将请求的锁授予该事务;反之,如果两者不兼容,该事务就要等待锁释放。

在InnoDB中使用锁

1. 对于UPDATE、DELETE和INSERT语句

1
InnoDB 会自动给涉及数据集加排他锁(X),其他会话的事务都将会等待其释放独占锁。

2. 对于SELECT语句

1
2
3
对于普通SELECT语句,InnoDB不会加任何锁;事务可以通过以下语句显示给记录集加共享锁或排他锁。
1) 共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
2) 排他锁(X):SELECT * FROM table_name WHERE ... FOR UPDATE
  • SELECT …… LOCK IN SHARE MODE

会话事务中查找的数据,加上一个共享锁。若会话事务中查找的数据已经被其他会话事务加上独占锁的话,共享锁会等待其结束再加,若等待时间过长就会显示事务需要的锁等待超时。

  • SELECT ….. FOR UPDATE

会话事务中查找的数据,加上一个读更新琐,其他会话事务将无法再加其他锁,必须等待其结束。需要注意的一点是:

1
mysql中使用select for update的必须针对InnoDb,并且必须在事务(BEGIN/COMMIT)中才能生效。

select的条件不一样,采用的是行级锁还是表级锁也不一样。由于 InnoDB 预设是 Row-Level Lock,所以只有明确的指定主键,MySQL 才会执行 Row lock (只锁住被选取的行) ,否则 MySQL 将会执行 Table Lock (将整个表锁住)。下面来举例说明何时会锁表,何时会锁行。

假设有如下products表,其中productID是主键。

此处输入图片的描述

  • (1)查询条件明确指定主键并且有符合条件的行——加行锁
1
select * from products where productID ='3' for update;
  • (2)查询条件明确指定主键但没有符合条件的行——不加锁
1
select * from products where productID='-1' for update;
  • (3)查询条件无主键——表锁
1
select * from products where name='dog' for update;
  • (4)查询条件主键不明确——表锁
1
select * from products where produstID <>'3' for update;
  • (5)查询条件主键不明确——表锁
1
select * from products where productID like '3' for update;

InnoDB行锁实现方式

InnoDB行锁实现方法:

1
InnoDB行锁是通过给索引上的索引项加锁来实现。

这是MySQL与Oracle的不同点,后者是通过在数据块中对相应的数据行加锁来实现的。InnoDB这种行锁的实现特点意味着:

1
只有通过索引条件查询数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁。

MySQL的行锁是针对索引加锁的,不是针对记录加的锁,所以虽然访问不同行的记录,但如果是使用相同的索引键,是会出现锁冲突的。

另外:

1
2
1)当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行
2)不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。

即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,应用explain检查SQL的执行计划,以确认是否真正使用了索引。

间隙锁(Next-key锁)

当用范围条件而不是相等条件来查询数据并请求共享或排他锁时,InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁)。

我们再以products表举例,假设现在表中有4条记录,productID分别为1、2、4、5。查询语句为:

1
select * from products where poductID < 4 lock in share mode

这事会在products表上加一个间隙锁,所以 product < 4的话,会给 0、1、2、3、4 加上行锁这样就保证了不会出现插入 productID=3 这种事情的发生。

为了防止幻读,以满足相关隔离级别的要求。如果不使用间隙所,如果其它事务插入了 productID=3 的任何记录,那么本次事务如果再次执行上述语句,就会出现幻读。

很显然,在使用范围条件检索并锁定记录时,InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入,这会造成严重的锁等待。因此,在实际应用开发中,尤其是并发插入比较多的应用,我们要尽量优化业务逻辑,尽量使用相等条件来访问更新数据,避免使用范围条件。

InnoDB除了通过范围条件加锁时使用间隙锁外,如果使用相等条件请求给一个不存在的记录加锁,InnoDB也会使用间隙锁。

死锁

死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。

死锁发生的条件:

  • 互斥条件:一个资源每次只能被一个进程使用;
  • 请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放;
  • 不剥夺条件:进程 已获得的资源,在末使用完之前,不能强行剥夺;
  • 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

一次封锁法 VS 两段锁

当有大量的并发访问存在时,为了预防死锁,一般应用中推荐使用一次封锁法,就是在方法的开始阶段,已经预先知道会用到哪些数据,然后全部锁住,在方法运行之后,再全部解锁。这种方式可以有效的避免循环死锁,但在数据库中却不适用,因为在事务开始阶段,数据库并不知道会用到哪些数据。

数据库遵循的是两段锁协议,将事务分成两个阶段,加锁阶段和解锁阶段(所以叫两段锁)

1)加锁阶段:在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁(共享锁,其它事务可以继续加共享锁,但不能加排它锁),在进行写操作之前要申请并获得X锁(排它锁,其它事务不能再获得任何锁)。加锁不成功,则事务进入等待状态,直到加锁成功才继续执行。

2)解锁阶段:当事务释放了一个封锁以后,事务进入解锁阶段,在该阶段只能进行解锁操作不能再进行加锁操作。事务加锁/解锁处理过程如下:

1
2
3
4
begininsert into test .....insert对应的锁
update test set...update对应的锁
delete from test ....delete对应的锁commit;
事务提交时,同时释放insertupdatedelete对应的锁

这种方式虽然无法避免死锁,但是两段锁协议可以保证事务的并发调度是串行化(串行化很重要,尤其是在数据恢复和备份的时候)的。

死锁的产生

在MySQL中:

1
2
1) 表级锁(MyISAM表锁)不会产生死锁
2) Innodb会产生死锁

对于InnoDB 和BDB 存储引擎来说,是可能产生死锁的。MyISAM表锁是deadlock free的,这是因为MyISAM总是一次获得所需的全部锁,要么全部满足,要么等待,因此不会出现死锁。但在InnoDB中,除单个SQL组成的事务外,锁是逐步获得的,这就决定了在InnoDB中发生死锁是可能的。

InnoDB中会发生死锁的几种情况:

1
2
3
4
1) 两张表两行记录交叉申请互斥锁
2) 同一张表则存在主键索引锁冲突(同一索引上,两个session相反的顺序加锁多行记录)
3) 主键索引锁与非聚簇索引锁冲突
4) 锁升级导致的锁等待队列阻塞。

死锁的检测

InnoDB会把下面两种情况判断为死锁:

1
2
1) 满足循环等待条件
2) 锁结构超过mysql配置中设置的最大数量或锁的遍历深度超过设置的最大深度时

在MySQl中可以通过 show engine innodb status 命令查看死锁情况,返回结果中包括死锁相关事务的详细信息,如引发死锁的SQL语句,事务已经获得的锁,正在等待什么锁,以 及被回滚的事务等。

InnoDB 会自动检测一个事务的死锁并回滚一个或多个事务来防止死锁。从 4.0.5 版开始,InnoDB 将设法提取小的事务来进行回滚。一个事务的大小由它所插入(insert)、更新(update)和删除(delete)的数据行数决定。当 InnoDB 执行一个事务完整的回滚,这个事务所有所加的锁将被释放。然而,如果只一句的 SQL 语句因结果返回错误而进行回滚的,由这条 SQL 语句所设置的锁定可能会被保持。这是因为 InnoDB r的行锁存储格式无法知道锁定是由哪个 SQL 语句所设置。

死锁的避免

设置锁等待超时参数:innodb_lock_wait_timeout。需要说明的是,这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖跨数据库。我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。

一个锁定记录集的事务,其操作结果集应尽量简短,以免一次占用太多资源,与其他事务处理的记录冲突。 对定点运行脚本的情况,避免在同一时间点运行多个对同一表进行读写的脚本,特别注意加锁且操作数据量比较大的语句。

在应用中,如果不同的程序会并发存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会在程序以批量方式处理数据的时候,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大降低出现死锁的可能。

在事务中,如果要更新记录,应该直接申请足够级别的锁,即排他锁,而不应先申请共享锁,更新时再申请排他锁,因为当用户申请排他锁时,其他事务可能又已经获得了相同记录的共享锁,从而造成锁冲突,甚至死锁。

在REPEATABLE-READ隔离级别下,如果两个线程同时对相同条件记录用SELECT…FOR UPDATE加排他锁,在没有符合该条件记录情况下,两个线程都会加锁成功。程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁。这种情况下,将隔离级别改成READ COMMITTED,就可避免问题。

Ref

MySQL中的日志小结

2015-04-17 01:23

原创声明:本作品采用知识共享署名-非商业性使用 3.0 版本许可协议进行许可,欢迎转载,演绎,但是必须保留本文的署名(包含链接),且不得用于商业目的。

日志是数据库中的重要组成部分,本文将总结一下MySQL中的那些日志。

MySQL日志类型

MySQL中有如下日志类型:

此处输入图片的描述

  • 1)错误日志

错误日志记录的是MySQL运行过程中发生的所有错误信息。错误级别包括warning,error,fatal等。 配置方式如下:

1
2
[mysqld]
log-error = ${MYSQL_BASEDIR}/log/mysql.err
  • 2)查询日志

查询日志记录所有的连接信息和SQL语句,因此又被称为全日志,包括各种DML/DDL等,常用于审计。可以利用该日志查询某客户端发送的所有SQL

MySQL按照接收SQL的顺序记录查询日志,也就是说,查询日志中记录的SQL顺序可能与SQL实际执行的顺序不同这与更新日志和binlog不同,它们在查询执行后,且任何一个锁释放之前记录日志。 配置方式如下:

1
2
[mysqld]
log = ${MYSQL_BASEDIR}/log/mysql.log
  • 3)慢查询日志

慢查询日志记录执行时间超过long-query-time的query、没有使用上索引的query等,long-query-time建议设置为1秒。启用慢查询日志可以更好的定位系统瓶颈,有针对的优化SQL,强制开启。配置方式如下:

1
2
3
4
[mysqld]
log-queries-not-using-index
long-query-time  = 1
log-slow-queries = ${MYSQL_BASEDIR}/log/slow.log
  • 4)二进制日志(binlog)

binlog记录所有写操作SQL语句,语句以事件形式保存。如load data infile语句,在binlog中记录的事件包括获取文件,装载数据等事件。即,一个SQL被分解为多个事件。

binlog除用于主从复制外,还用于恢复下线的数据库,因为binlog中包含备份后的所有写操作

binlog的推荐配置方式是:每台server开启log-bin,为避免机器改名引起文件名变化,需在配置文件中指定文件名,格式为mysql-bin[-级别标识],其中级别标识为可选,主库/主库备机的级别标识为m,一级从库的级别标识为s0,二级从库的级别标识为s1。

1
2
3
4
[mysqld]
log-bin = mysql-bin[-m|-s0|-s1]
expire_logs_days=10
max_binlog_size=100M

下面将重点介绍InnoDB日志。

InnoDB日志

InnoDB的日志(ib_logfile)记录了所有InnoDB表的写操作,用于保证事务的持久性和原子性。该日志并不同于binlog,binlog中记录的是SQL语句,用于主从复制,而InnoDB日志以二进制形式存储,其中记录的信息包括事务id、数据项标识、写操作的新值和旧值等信息,还有诸如事务起止标识等特殊符号。InnoDB采用的是立即修改技术,即,每个事务中的操作都会立即更新内存,因此,为保证事务的原子性,InnoDB记录的是undo日志,为保证数据库的实体完整性,InnoDB记录的是redo日志。

InnoDB日志分为redo日志和undo日志两种,两种日志的区别如下: 此处输入图片的描述

undo日志记录如何撤销对数据库的改变,即当执行 rollback 命令时用于回滚事务以恢复数据库从而保证数据库的一致性。

1
2
3
4
1 事务开始前向undo日志中记录事务开始标记
2 将所有写操作记录到undo日志中
3 写日志成功后才能更新内存中的数据库数据
4 将事务结束标记写到undo日志中

那么为什么要先写日志再写数据库呢? 因为日志是顺序写的,非常快。数据,是随机写的,数据如果非常分散。效率会低得一塌糊涂。简单点说。数据可以延后写,日志却不行。因为数据相对于日志,是根本不重要的。它并不决定数据的安全。

redo日志记录的是如何重做某个操作,即当数据库发生故障时(如断电)如何保证数据库的实体完整性,当数据库恢复时会根据redo日志继续执行没有完成的事务。

总结:

1
2
3
1)InnoDB日志的目的是保证事务的持久性。因此日志中需要记录事务中所有的写操作及其他标记,如事务开始、结束标记,检查点标记等。
2)为保证提交的事务最终写到物理磁盘(持久性),因此,数据库系统中采用先写日志原则,即,每个修改要先写日志,再更新缓存中的页。
3)当系统崩溃需要恢复时,MySQL会从日志中寻找检查点而后重做检查点后的所有操作,从而保证持久性,这点是可以借鉴的。

MySQL中的慢查询

2015-03-28 23:40

原创声明:本作品采用知识共享署名-非商业性使用 3.0 版本许可协议进行许可,欢迎转载,演绎,但是必须保留本文的署名(包含链接),且不得用于商业目的。

在基于mysql的应用中,你可能会碰到一些很慢的查询(通常耗时几秒以上),大多数情况下,这些很慢的查询都可能会与我们使用mysql有关,都有优化的空间和解决的方案,那么如何来找出这些瓶颈所在呢?本文将简单总结一下慢查询分析方法。

1. 监控慢查询

首先,要监控慢查询,就需要对mysql进行配置,使得其记录下mysql在运行过程中的一些行为。监控慢查询方法就是用日志记录下慢查询时的行为。Mysql的日志分为普通日志和慢查询日志两种。如何设置呢?

设置配置文件my.cnf,你可以把如下配置增加到my.cnf中。

1
2
3
log-slow-queries = <file_name>
long_query_time	= 1
log-queries-not-using-indexes

解释一下上述配置项:

log-slow-queries:设置了慢查询日志保存的位置。

long_query_time:设置了超过多长时间则认为其需要记录在慢速日志,上述设置为1秒。该配置只能精确到秒级别,对于某些应用来说控制粒度可能过粗了。

Log-queries-not-using-indexes:则使服务器把没有使用到索引的查询记录到慢查询日志中,无论他们的查询是否超过了这个限制值。

2. 分析慢查询

监控了慢查询后,我们就可以从慢查询日志中找到查询耗时的操作,然后想办法优化这些操作。

下面是慢查询日志的一个片段:

1
2
3
4
5
6
7
8
# Time: 101126 18:12:06
# User@Host: test[test] @ localhost []
# Query_time: 0.001778  Lock_time: 0.000128 Rows_sent: 0  Rows_examined: 235
SET timestamp=1290766326;
SELECT id FROM log_task  WHERE (state = "DONE") AND ( TIME_TO_SEC(TIMEDIFF(NOW(),`refreshTime`)) > 5)  LIMIT 0 , 99999999;
# User@Host: noah[noah] @ localhost []
# Query_time: 0.001560  Lock_time: 0.000043 Rows_sent: 1  Rows_examined: 235
SET timestamp=1290766326;

上述慢查询片段中,第一行显示了日志记录的时间,第二行显示谁执行了这些语句,第三行显示了执行这行语句所花费的时间,在mysql服务器级别等待表锁的时间,执行语句返回的行数,以及语句检索的行数。

从慢查询日志中得到具体的慢查询语句后,接下来就需要对慢查询语句进行分析,常用的分析工具有explainprofiling,下面将分别介绍这两个工具的使用方法。

2.1 用explain分析query

我们先来看一下query请求的处理过程。当MySQL连接线程接收到client端发送过来的query请求时,首先进行解析(parse),然后会通过查询优化器模块(MySQL query optimizer)根据该query所涉及的数据表相关统计信息进行计算分析,再得出一个MySQL认为最合理的执行方式,即“执行计划”,最后按照执行计划,通过调用存储引擎接口来获取相应数据并返回给client端应用程序,流程如下:

此处输入图片的描述

如果MySQL开启了Query Cache且请求命中则不走上述流程。

MySQL Query Optimizer就是通过执行explain命令来告诉我们它是怎样的一个执行计划来优化query的。应该说explain是最直接有效验证我们想法的工具。

下面拿3个简单的例子来说明它的使用:

  • 自然联接

此处输入图片的描述

这是一个简单的内联查询语句,对照附录A的解释,我们发现对于表a,optimizer没能为该语句找到合适的索引也没有任何键作为比较,它只能采用了效率极低的all(全表查询),而为了排序后能与表b进行自然联接,optimizer不得不为表a使用了临时表并使用了具体的排序算法(using temporary、using filesort)。 对于表b情况要稍好,因为我们使用了b的主键ID作为查询条件,range则表示这是一个范围查询。

  • 派生表查询

此处输入图片的描述

最终的查询的表tblTempT是tblVersion查询结果集的派生表,所以id=2的select_type字段值为DERIVED。在表tblVersion子查询中,使用了主键索引,查询条件为范围查询。

  • UNION查询

此处输入图片的描述

UNION操作只能针对两个列完全相同的表进行操作,这里为了举例方便都用tblLemma这个表。 我们看到select_type=PRIMARY说明id=1的查询是最外层查询,select_type=UNION则为UNION关键字后的第一个select及之后的查询。在id=2即union后边的查询中,我们使用了id=100的条件且使用了主键作为索引(唯一匹配),所以type字段为const。最后一行id为NULL,且select_type为UNION RESULT,表明这不是一个Query,仅仅是一个结果集的合并。

2.2 用profiling分析query

Profiling功能是MySQL 5.0版本之后出现的功能,它是一个非常方便、非常强大的Query诊断分析工具,通过该工具可以获得一条Query在整个执行过程中多种资源的消耗情况,如CPU、IO、IPC、SWAP等。具体的使用可以查看MySQL手册。