mysql高级概念

索引

结构

索引类型

索引是帮助mysqlgaoxiao获取数据机构的数据结构

索引是在mysql的存储引擎中实现的,而不是在服务层中实现的,所以每种存储引擎的所以都不一定完全相同,也不是所有的存储引擎都支持所有的索引类型.mysql目前提供了四索引

• B+tree:最常见的索引类型,大部分引擎都支持b+树索引
• HASH:只有Memory引擎支持,使用场景简单.
• R-tree索引(空间索引):空间索引是MYISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少
• Full-text(全文索引):全文索引也是一个MYISAM的一个特殊索引类型,主要用于全文索引,InnoDB从5.6开始支持全文索引

平常说的索引,没有特别指明都是B+树索引,(多路平衡搜索树)结构组织的索引.其中非聚集索引,复合索引前缀索引,唯一索引,默认都是用B+树索引.

推荐一个学习数据结构的网站:https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

b-tree

BTree又叫多路平衡搜索树,一个m叉的BTree特性如下 :

• 树中每个节点最多包含m个叶子
• 除根节点与叶子节点外1,每个节点至少有[ceil(m/2)](m除以二取绝对值)个叶子
• 根节点不是叶子节点,至少有俩个叶子
• 所有的叶子节点都在同一层
• 每个非叶子节点由n个key和n+1个指针组成

总结:bTree和二叉树相比,查询数据效率更高,因为相对于相同的数据量来说,BTHREE的层级结构比二叉树小,因此搜索更快

b+tree

B+树为BTREe的变种,区别

• n叉b+Tree最多含有n个key,而BThree最多含有n-1个key
• B+Tree的叶子1节点保存所有key的信息,按照key大小顺序排列
• 所有的非叶子节点都可以看做是key的索引部分

b+树只有叶子节点保存数据信息,查询任何数据都要从根节点走到叶子节点索引查询效率更加稳定

mysql b+Tree

mysql索引数据结构对于经典的b+Tree进行了优化.在源B+Tree的基础上,增加一个指向相邻节点的链表指针,就形成了带有顺序的B+Tree,提高区间访问性能,增加了分为搜索的速度

存储引擎

MyISAM

myisam索引和数据文件是分离的

myisam在底层对应了三个文件,分别是.frm文件.MYD文件.MYI文件

• .frm文件:表结构的定义
• .MYD文件:d代表着data,这里存放的是myisam的data数据
• .MYI文件:I代表着index,是myisam索引的数据

innodb

innodb有俩个

innodb在底层对应了俩个文件,分别是.frm文件.IDB

• frm文件:表结构的定义
• IDB文件:存储了索引和数据

数据结构如下

mysql最小的io单位是16k,

mysql底层给innodb分配了一页16kb的数据,也就是每一次查询一页索引,每一页16kb大小,查询的时候会把一页的数据放入内存种扫描,如果查询到数据就将数据所对应的索引返回,如果没有则进入俩个索引之间存储的文件地址指针,将下一页加载到内存中,直到获取自己想要的数据

比如:需要查询30这个主键索引,就进入29和31这俩个索引之间的指针地址拿下一页

聚集索引与非聚集索引

聚集

• 聚集索引就是一主键创建的所有

索引和数据是分开存储的就是非聚集索引,索引和数据在一个文件中的就称之位聚集索引

在一个表中只能有一个聚集索引,一般主键为聚集索引,而非聚集索引可以有多个

innodb数据文件本身要按主键聚集,所以innodb要求表必须要有主键,如果没有指定,则mysql系统会自动选择一个可以标识唯一数据的列作为主键,如果不存在这种列,mysql则会自动生成

聚集索引表记录的排列顺序和索引的排列顺序保持一致,所以查询效率相当快.只要找到第一个索引记录的值,其余的连续性的记录也一定是连续存放的.

聚集索引的缺点就是修改起来比较麻烦,在插入新记录的时候就会对数据进行一次重排序(因此给innodb建立主键最好选择数字类型,并且进行自增,防止树的结构频繁的有改动)

在innodb中,表数据文件本身就是按B+树组织的一个索引结构,这棵树的叶子节点data域保存了完整的数据记录

非聚集

• 非聚集索引就是除了主键意外的索引
• 非聚集索引也叫做二级索引
• 非聚集索引未必是单列的,可以创建多个列来

而myisam使用的是非聚集索引

非聚集索引定义了表中记录的逻辑顺序,但记录的物理和索引并不一定保持一致,俩种索引都是采用b+树的结构,非聚集索引的叶子层并不和数据相互重叠,而是采用叶子层包含一个指向表中的记录指针

非聚集索引的缺点就是索引的层次比较多,但是不会造成数据的重排

非聚集索引的二次查询问题

辅助索引

树数据结构中索引又分为聚集索引和辅助索引

innodb

innodb存储引擎辅助索引的叶子节点并不会放整行数据,而存放的是键值和主键ID

当通过辅助索引来查找数据时,innodb存储赢钱会遍历辅助索引树查找对应记录的主键,然后通过主键索引来找到对应的行数据

myisam

在myisam上主索引和辅助索引没有任何区别,只是要求主索引的key是唯一的而辅助索引的key可以重复

建立辅助索引同样也会建立一颗b+树,data保存数据记录的地址.因此myisam中索引的算法为先按照b+tree搜索算法搜索索引,如果指定的key存在,则取出其data的值,然后找到对应的地址读取响应的数据记录

5.6新特性

MRR

mrr全称:multi range read 多范围读取优化

简单的说mrr通过把随机读取转换为顺序磁盘读从而提高了索引查询的性能

mysql > explain select * from stu where age between 10 and 20;
+----+-------------+-------+-------+------+---------+------+------+-------------------+
| id | select_type | table | type  | key  | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+----------------+------+------+--------------------+
|  1 | SIMPLE      |  stu  | range | age  | 5       | NULL |  960 | Using index condition |
+----+-------------+-------+-------+----------------+------+------+--------------------+

当执行sql,mysql会按照下图的方式,去磁盘读取数据

这张图是按照 Myisam 的索引结构画的，不过对于 Innodb 也同样适用。

对于 Myisam，左边就是字段 age 的二级索引，右边是存储完整行数据的地方。

先到左边的二级索引找，找到第一条符合条件的记录（实际上每个节点是一个页，一个页可以有很多条记录，这里我们假设每个页只有一条），接着到右边去读取这条数据的完整记录。

读取完后，回到左边，继续找下一条符合条件的记录，找到后，再到右边读取，这时发现这条数据跟上一条数据，在物理存储位置上，离的贼远！

咋办，没办法，只能让磁盘和磁头一起做机械运动，去给你读取这条数据。

第三条、第四条，都是一样，每次读取数据，磁盘和磁头都得跑好远一段路。

磁盘的简化结构可以看成这样：

可以想象一下，为了执行你这条 sql 语句，磁盘要不停的旋转，磁头要不停的移动，这些机械运动，都是很费时的。

10,000 RPM（Revolutions Per Minute，即转每分） 的机械硬盘，每秒大概可以执行 167 次磁盘读取，所以在极端情况下，MySQL 每秒只能给你返回 167 条数据，这还不算上 CPU 排队时间。

上面讲的都是机械硬盘，SSD 的土豪，请随意 - -

对于 Innodb，也是一样的。 Innodb 是聚簇索引（cluster index），所以只需要把右边也换成一颗叶子节点带有完整数据的 B+ tree 就可以了。

顺序读：一场狂风暴雨般的革命

到这里你知道了磁盘随机访问是多么奢侈的事了，所以，很明显，要把随机访问转化成顺序访问：

mysql > set optimizer_switch='mrr=on';
Query OK, 0 rows affected (0.06 sec)

mysql > explain select * from stu where age between 10 and 20;
+----+-------------+-------+-------+------+---------+------+------+----------------+
| id | select_type | table | type  | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+-------+------+---------+------+------+----------------+
|  1 | SIMPLE      | tbl   | range | age  |    5    | NULL |  960 | ...; Using MRR |
+----+-------------+-------+-------+------+---------+------+------+----------------+

开启了mrr,重新执行sql语句会发现extra里多了一个using MRR

对于我

对于myisam,在去磁盘获取完整的数据前,会先按照rowid排好序,再去顺序的读取磁盘

对于innodb则会按照聚集索引键值排好序,再顺序的读取聚集索引

顺序读带了几个好处

• 磁盘和磁头不再需要来回做运动
• 可以充分利用磁盘预读

比如客户在请求一页的数据时,可以把后面几页的数据一起返回,放到数据缓冲池中,这样如果下一次刚好需要下一页的数据,就不需要到磁盘读取

• 再一次查询中每一页的数据只会从磁盘读取一次

mysql从磁盘读取页的数据只会,会把数据放到数据缓冲池,下次如果还用到这个页,就不需要读取,直接从内存中读取

MRR本质上是一种用空间换时间的算法.mysql不可逆给无线的内存来进行排序,如果read_rnd_buffer满了,就会先把满了的rowid排好序去磁盘读去,接着清空,然后再往里面放rowid,知道read_rnd_buffer又打到read_rnd_buff配置的上线,如此结束循环

以下是mysql官方文档中的介绍

当表较大且为存储在缓存中的时候,在辅助索引上使用范围扫描来读取会导致对表的多随机访问,.通过磁盘扫描范围读取(MRR),mysql尝试通过首先扫描索引并收集相关的行的键来酱烧用于范围扫描的随机磁盘访问次数.然后对键进行排序,最后使用主键的顺序从基表中检索行.磁盘扫描mrr的动机是减少随机磁盘的访问次数和对基表的数据进行更顺序的扫描

多范围读取优化具有以下优点

• mrr使数据行可以基于索引元组进行访问,而不是随机顺序访问.服务器获取一组满足查询条件的索引元组,并根据数据行ID顺序对他们进行排序,然后使用排序后的元组按顺序检索数据行.这使得数据访问更加高效且成本更低
• 对于需要通过索引元组访问数据航的操作(列入范围索引扫描和使用索引作为连接属性的等连操作),MRR支持对键访问请求的批处理.MRR在一系列规范内进行迭代以获合格的元组.随着这些结果的积累,他们用于访问相应的数据行.在开始读取数据行之前不必获取所有索引元组

InnoDB,MyISAM如果不需要访问整个表以产生结果,则不要使用mrr.如果可以完全根据索引元组中的信息(覆盖索引)产生结果,MRR是没有任何好处的

ICP

Index Condition Pushdown (IPC)索引下推条件优化,mysql默认是打开索引下推的

以下是mysql官网文档中的介绍

索引条件下推是针对Mysql使用索引从表中检索行的情况的一种优化.如果不使用IPC,则存储引擎将遍历索引在基表中的定位行,并将其返回给mysql服务器,后者将评估where执行的条件.启用ipc后,如果where可以仅适用索引中的列来评估,部分,则mysql服务器会将这部分压如where条件下降到存储引擎.然后存储引擎通过索引条目来评估推送索引条件

创建索引时，我们也要考虑空间代价，使用较少的空间来创建索引
假设我们现在不需要通过username查询password了，相反，经常需要通过username查询age或通过age查询username,这时候，删掉（username,password）索引后，我们需要创建新的索引，我们有两种选择
1、（username,age）联合索引+age字段索引
2、（age,username）联合索引+username单字段索引

对于user_table表，我们现在有（username,age）联合索引
如果现在有一个需求，查出名称中以“张”开头且年龄小于等于10的用户信息，语句C如下："select * from user_table where username like '张%' and age > 10".
语句C有两种执行可能：
1、根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后回表查询出相应的全行数据

2、根据（username,age）联合索引查询所有满足名称以“张”开头的索引，然后直接再筛选出年龄小于等于10的索引，之后再回表查询全行数据。过程如下图。

明显的，第二种方式需要回表查询的全行数据比较少，这就是mysql的索引下推。mysql默认启用索引下推，我们也可以通过修改系统变量optimizer_switch的index_condition_pushdown标志来控制

SET optimizer_switch = 'index_condition_pushdown=off';

对于innodb的表,IPC仅用于辅助索引.ICP的目标是减少全行的读取的次数,从而减少IO操作,innodb的主键索引树叶子节点上保存的是全行数据,所以这个时候索引下推并不会减少查询全行数据的作用

部分转载https://www.jianshu.com/p/bdc9e57ccf8b

BKA和BNL

Batched Key Access(BKA)提高join性能的算法.当被join的表能够使用索引时,就先排好顺序然后再去检索被join的表,听起来和mrr类似

如果join表上没有索引则使用老版本的BNL策略

BKA原理

对于多表join语句,当mysql使用索引访问第二个join表的时候,使用joinbuffer来收集第一个操作对象生成相关的列值.BKA构建好key后,批量传给引擎层做索引查找.key是通过MRR接口提交给引擎的(MRR目的是较为顺序让查找更有效率)

过程如下

• BKA使用join的第一个操作产生的符合条件的数据
• 然后BKA算法构建key来访问被连接的表,并批量使用MRR接口提交keys到数据库存储引擎中去查找
• 提交keys之后,MRR使用最佳方式来虎丘行并反馈给BKA

BNL和BKA都是批量的提交一部分行给被join的表，从而减少访问的次数，那么它们有什么区别呢？

BNL比BKA出现的早，BKA直到5.6才出现，而NBL至少在5.1里面就存在。

BNL主要用于当被join的表上无索引

bka主要是指在被join表上索引可以利用,那么就在提交给被join的表之前,对这些表按照字段顺序进行排序,因此减少了随机IO排序这才是俩者最大的区别,但是如果被join的表没有使用索引呢?那么就使用NBL

BAK使用了MRR，要想使用BAK必须打开MRR功能，而MRR基于mrr_cost_based的成本估算并不能保证总是使用MRR，官方推荐设置mrr_cost_based=off来总是开启MRR功能。打开BAK功能(BAK默认OFF)：

SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

BKA使用join buffer size来确定buffer的大小，buffer越大，访问被join的表/内部表就越顺序。

BNL默认是开启的，设置BNL相关参数：

SET optimizer_switch=’block_nested_loop’

支持inner join, outer join, semi-join operations,including nested outer joins

BKA主要适用于join的表上有索引可利用，无索引只能使用BNL

存储引擎总结

InnoDB

缓冲池buffer poll

缓存表数据与索引数据,吧磁盘上的数据加载到缓冲池,避免每次访问都进行操盘IO,起到加速访问的作用

同步

对于数据库中页的修改操作，则首先修改缓存池中的页，然后再以一定的频率刷新到磁盘上。注意：缓冲池刷新回磁盘并不是每次页发生更新时触发，而是通过一种称为Checkpoint的机制刷新回磁盘。这样，是为了进一步提高数据库整体性能。

预读

磁盘读写,并不是按需读取,而是按页读取,一次至少读取一页的数据(一般是16k如果未来要读取的数据就在页中,就能够省去后续的磁盘IO,提高效率

作用:数据访问通常都遵循集中度斜原则,使用一些数据大概率会使用附件的数据,这就是所谓的就不行原理,它表明提前加载是有效的,能减少磁盘的io

他和innodb的缓冲区有什么关系

磁盘访问按页读取能够提高性能,所以缓冲区一般也是按页存储数据

预读机制启示了我们,能把一些可能要访问的页提前加入缓冲池,避免未来的磁盘IO操作

innodb使用俩种预读算法来提高I/O性能:线性预读和随机预读

change buffer写缓冲

最开始的时候只能是insert操作，所以叫做insert buffer，现在已经改叫做change buffer了

changebuffer是一种特殊的数据结构,当要修改的辅助索引页不在buffer pool中时,用来cache对辅助索引页的修改.对辅助索引页可能是insert,update,和delete操作.等到相关的所有也被读到buffer pool中后,才会使用change buffer的内容对辅助索引进行修改.

和聚集索引不同,辅助索引通常不是唯一的,插入辅助索引通常也是随机的.同样对辅助索引的删除,更新通常也是不连续的

情况一

假如要修改页号为4的索引页，而这个页正好在缓冲池内。

直接修改缓冲池中的页,一次内存操作

写入redo log 一次磁盘顺序操作

这样的效率是最高的

是否会出现一致性问题呢？

并不会。

（1）读取，会命中缓冲池的页；

（2）缓冲池LRU数据淘汰，会将“脏页”刷回磁盘；

（3）数据库异常奔溃，能够从redo log中恢复数据；

什么时候缓冲池中的页，会刷到磁盘上呢？

定期刷磁盘，而不是每次刷磁盘，能够降低磁盘IO，提升MyS L的性能。

画外音：批量写，是常见的优化手段。

情况二

假如要修改页号为40的索引页，而这个页正好不在缓冲池内。

此时麻烦一点要1-3

先把需要为40的所爷爷从磁盘加载到缓冲池中,一次磁盘随机读写操作

修改缓冲池中的页,一次内存操作

写入redo log 一次磁盘顺序写操作

没有命中缓冲池的时候，至少产生一次磁盘IO，对于写多读少的业务场景，是否还有优化的空间呢？

这即是InnoDB考虑的问题，又是本文将要讨论的写缓冲(change buffer)。

画外音：从名字容易看出，写缓冲是降低磁盘IO，提升数据库写性能的一种机制。

什么是InnoDB的写缓冲？

在MySQL5.5之前，叫插入缓冲(insert buffer)，只针对insert做了优化；现在对delete和update也有效，叫做写缓冲(change buffer)。

它是一种应用在非唯一普通索引页(non-unique secondary index page)不在缓冲池中，对页进行了写操作，并不会立刻将磁盘页加载到缓冲池，而仅仅记录缓冲变更(buffer changes)，等未来数据被读取时，再将数据合并(merge)恢复到缓冲池中的技术。写缓冲的目的是降低写操作的磁盘IO，提升数据库性能。

InnoDB加入写缓冲优化，上文“情况二”流程会有什么变化？

假如要修改页号为40的索引页，而这个页正好不在缓冲池内。

加入写缓冲优化后，流程优化为：

（1）在写缓冲中记录这个操作，一次内存操作；

（2）写入redo log，一次磁盘顺序写操作；

其性能与，这个索引页在缓冲池中，相近。

画外音：可以看到，40这一页，并没有加载到缓冲池中。

是否会出现一致性问题呢？

也不会。

（1）数据库异常奔溃，能够从redo log中恢复数据；

（2）写缓冲不只是一个内存结构，它也会被定期刷盘到写缓冲系统表空间；

（3）数据读取时，有另外的流程，将数据合并到缓冲池；

不妨设，稍后的一个时间，有请求查询索引页40的数据。

此时的流程如序号1-3：

（1）载入索引页，缓冲池未命中，这次磁盘IO不可避免；

（2）从写缓冲读取相关信息；

（3）恢复索引页，放到缓冲池LRU里；

画外音：可以看到，40这一页，在真正被读取时，才会被加载到缓冲池中。

除了数据页被访问，还有哪些场景会触发刷写缓冲中的数据呢？

还有这么几种情况，会刷写缓冲中的数据：

（1）有一个后台线程，会认为数据库空闲时；

（2）数据库缓冲池不够用时；

（3）数据库正常关闭时；

（4）redo log写满时；

什么业务场景，适合开启InnoDB的写缓冲机制？

先说什么时候不适合，如上文分析，当：

（1）数据库都是唯一索引；

（2）或者，写入一个数据后，会立刻读取它；

这两类场景，在写操作进行时（进行后），本来就要进行进行页读取，本来相应页面就要入缓冲池，此时写缓存反倒成了负担，增加了复杂度。

什么时候适合使用写缓冲，如果：

（1）数据库大部分是非唯一索引；

（2）业务是写多读少，或者不是写后立刻读取；

可以使用写缓冲，将原本每次写入都需要进行磁盘IO的SQL，优化定期批量写磁盘。

参数：innodb_change_buffer_max_size

介绍：配置写缓冲的大小，占整个缓冲池的比例，默认值是25%，最大值是50%。

画外音：写多读少的业务，才需要调大这个值，读多写少的业务，25%其实也多了。

参数：innodb_change_buffering

介绍：配置哪些写操作启用写缓冲，可以设置成all/none/inserts/deletes等。
原文链接：https://blog.csdn.net/shenjian58/article/details/93691224

MVCC

通俗的讲就是mvcc通过保存数据的历史版本,根据比较版本号来处理数据的是否显示,从而达到读取数据的时候不需要加锁就可以保证事务的隔离性的效果

mvcc即多版本并发控制.mvcc是一种并发控制方法.一般在数据库管理中,实现对1数据库的并发访问.mvcc是的大部分支持航所的事务引擎,不再单纯使用航所来进行数据库的并发控制,取而代之的是把数据库的行锁与行的多个版本结合起来,只需要很小的开销就可以实现非锁定读写,从而大大提高数据库系统的并发性能

有人从数据库读数据的同时,有另外的人写数据,有可能读数据的人会看到半写或者不一致的数据.有很多方法解决这个问题,叫做并发控制方法.最简单的是通过加锁,但是这样效率会很差.mvcc使用了一种不同的手段,每个链接到数据库的读者,在摸个瞬间看到的是数据库的一个快照,写着写操作造成的变化在写操作完成之前,对于其他的读者来说是不可见的.

mvcc会保存摸个时间点上的数据快照.这意味着事务可以看到一个一致的数据视图,不管他们需要跑多久.这同时也意味着不同的事务在同一个时间点看到的同一个表的数据可能是不同的

实现策略

每一次的增删改操作都会保存当前系统版本号作为行版本号.(select不会生成事务id)

当执行查询sql时会生成一致性视图read-view

提交读每次查询都会生成一次最新的readview,可重复读会沿用事务第一次readview

它由执行查询时所有未提交事务id数组(数组里最小的id为min_id)和已创建的最大事务id(max_id)组成,查询的数据结果需要根据read-view做比对从而得到快照结果

每一行数据额外保存跨个隐藏的列:当前创建时的版本号和删除时的版本号.这里的版本号并不是实际的时间值,而是系统的版本号.每天开始新的事务,系统版本号都会自动递增.事务开始时刻的系统版本号会作为事务的版本号,用来和查询没行记录的版本号进行比较.每个事务又有自己的版本号,这样事务内执行CURD操作时,就通过版本号的比较来达到数据版本控制的目的.

不是乐观锁!不是乐观锁!不是乐观锁!

比对规则

如果落在绿色部分(trx_id<min_id),表示这个版本是已提交的事务生成的,这个数据是可见的

如果落在红色的部分(trx_id>max_id)表示这个版本是由将来启动的事务生成的,是肯定不可见的

如果落在黄色部分(min_id<=trx_id<=max_id)有俩种情况

• a:若row的trx_id在数组中,表示这个版本是由还没提交的事务生成的,不可见,当前自己的事务是可见的
• b:若row的trx_id不在数组中,表示这个版本是已提交了的事务生成的,可见(如果事物是自己当前的事务那么久可见)

对于删除的情况可以认为是update的特殊情况,会将版本链上最新的数据复制一份,然后将trx_id修改成删除操作的trx_id,同时在该条记录的删除的头信息里的标记位上协商true,来表示当前记录已经被删除,在查询时按照上面的规则差到对应的记录如果delete_flag标记为位true,意味着记录以被删除,不反悔数据

最后多版本并发控制是一种解决读写冲突的问题,而乐观锁是用来解决写-写冲突实现无锁并发控制俩者不是一个概念

特点

innodb存储引擎提供了提交回滚崩溃恢复能力的事物安全,锁定粒度较小,采用行锁,采用聚集索引,使用了mvcc让读与写可以进行并发.适用于高并发读写的情况.另外innodb的性能其实还是不错的特别是在处理大数据量的情况下,不过innodb备份比较麻烦,他的数据文件并不是独立对应于每张表的.而是使用的共享表空间,简单的拷贝覆盖方法对她不适用,必须在停掉mysql后才能进行数据恢复.

优点

• 支持事务处理
• 实现了标准的隔离级别和事务的ACID特性
• 支持行锁和外检约束
• 可以利用事务日志进行数据恢复
• 锁级别为表锁,航所的优点使适用于高并发的频繁表修改,高并发性能是由于myisam.缺点是系统消耗较大.
• 索引不仅缓存自身,也缓存数据,相比mysiam需要更大的内存

缺点

• 没有保存表的行数,当使用count统计的时候会扫描全表

MyISAM

特点

是mysql最早的存储引擎之一.采用了非聚集索引,支持表锁,锁定粒度大,没有提供事务能力.性能要优于innodb,备份比较简单,读与写不能够并发

MyISAM在读操作占主导的情况下是很高效的。可一旦出现大量的读写并发，同InnoDB相比，MyISAM的效率就会直线下降，而 且，MyISAM和InnoDB的数据存储方式也有显著不同:在myisam里新数据会被附加到数据文件的结尾,可以如果时长做一些update,delete操作之后,数据文件就不再是连续的

形象一点来说，就是数据文件里出现了很多洞洞，此时再插入新数据时，按缺省设置会先看这些 洞洞的大小是否可以容纳下新数据，如果可以，则直接把新数据保存到洞洞里，反之，则把新数据保存到数据文件的结尾。之所以这样做是为了减少数据文件的大 小，降低文件碎片的产生。但InnoDB里则不是这样，在InnoDB里，由于主键是cluster的，所以，数据文件始终是按照主键排序的，如果使用自 增ID做主键，则新数据始终是位于数据文件的结尾。

锁调度

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预

MySQL表级锁有两种模式：表共享锁（Table Read Lock）和表独占写锁（Table Write Lock）。也叫排他锁
对MyISAM的读操作，不会阻塞其他用户对同一表请求，但会阻塞对同一表的写请求；
对MyISAM的写操作，则会阻塞其他用户对同一表的读和写操作；
MyISAM表的读操作和写操作之间，以及写操作之间是串行的。
当一个线程获得对一个表的写锁后，只有持有锁线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。

手动加读锁和写锁

lock table (表名) read #读锁
lock table (表名) write #写锁
unlock tables  #解锁

Myisam存储引擎的读和写锁是互斥的,读操作是串行.那么一个进程请求摸个myisam表的读锁,另一个请求写锁,MySQL如何处理呢?

案是写进程先获得锁。不仅如此，即使读进程先请求先到锁等待队列，写请求后到，写锁也会插到读请求之前！这是因为MySQL认为写请求一般比读请求重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！

部分转载自https://www.jianshu.com/p/d8bdecbe64ad

分区分库分表

分区

介绍

概念

所谓分区就是将一个表分解成多个区块进行操作,从而降低每次操作的数据,提高性能.而对应用来说是透明的,逻辑上只有一个表(这里和分库分表不一样),但在物理上这个表可能是由多个物理分区组成的,每个分区都是独立的一个对象,可以进行独立处理.

作用

• 进行逻辑数据分隔,分隔数据能够有多个不同的物理文件路径
• 可以存储更多的数据,突破系统单个文件最大限制
• 提升性能,提高每个分区的读写速度,提高分区范围查询速度
• 可以通过删除相关分区来快速删除数据
• 通过跨多个磁盘来分散数据查询,从而提高I/O性能
• 涉及到sum()和count()这样的聚合函数查询(需要做全表扫描),可以很容易的进行并行处理
• 可以备份和恢复独立的分区,对大数据量有好处

支持

mysql支持大部分的存储引擎创建分区,如myisam,innodb等,不支持merge和csv等来创建分区.同一个分区表中的所有分区必须是同一个存储引擎.

mysql从5.1开始支持分区

类型

• range分区:基于一个给定的连续区间的列值,吧多行分配给分区
• list分区:类似于range分区,list列值匹配一个离散集合中的某个值来进行选择
• hash分区:基于用于定义的表达式的返回值来进行选择分区,该表达式将要插入到表中的这些列值进行计算,这个函数必须缠身非负整数
• key分区:类似于按hash分区,由mysql服务器提供自身的哈希函数

不论什么分区都要注意以下问题

如果表中存在主键,或者说唯一索引,分区的列必须是主键或者唯一索引的一部分,也就是说分区函数的列只能从主键或者唯一索引这些键中取子集

如果表当中不存在任何的主键或者唯一索引,可以指定任何一个列作为分区

5.5版本前的range,list,hash分区要求分区键必须是int;mysql.5.5以上支持整形的range和list分区即range columns和list colums

建立分区

range

create table <table> (
    // 字段
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
partition by range (分区字段) (
  partition <分区名称> values less than (Value),
  partition <分区名称> values less than (Value),
  ...
  partition <分区名称> values less than maxvalue
);

执行如下sql语句

CREATE TABLE tbl_users(
uuid INT NOT NULL,
customerId VARCHAR (20),
pwd VARCHAR (20),
showName VARCHAR (100),
trueName VARCHAR (100) ,
registerTime VARCHAR(100)
) 
PARTITION BY RANGE (uuid) (
PARTITION p0 VALUES LESS THAN (5), #小于5放在p0
PARTITION p1 VALUES LESS THAN (10), #5-10之间放在p1
PARTITION p2 VALUES LESS THAN (15), #10-15p2
PARTITION p3 VALUES LESS THAN MAXVALUE #其他的更大在p3
);

之后去查看数据库(innodb引擎)

分区对于应用是透明的,插入和查询都不会有任何问题,5.7之后,分区定义文件.par文件被移除了

查看分区

select * from information_schema.PARTITIONS where table_schema='test' and table_name='tbl_users' ; #table_schema之后是数据库名,tbl_users是数据表名

查看分区上的数据

select* from tbl_users partition(p0) #p0为分区名

可以用explain中的partitions来查看mysql会操作的分区

explain PARTITIONS select * from tbl_users where uuid=2

list

一般用作根据类型进行分区

创建

CREATE TABLE tbl_users2(
uuid INT NOT NULL,
customerId VARCHAR (20),
pwd VARCHAR (20),
showName VARCHAR (100),
trueName VARCHAR (100) ,
registerTime VARCHAR(100)
) 
PARTITION BY List (uuid) (
PARTITION p0 VALUES in (1,2,3,5),
PARTITION p1 VALUES in (7,9,10),
PARTITION p2 VALUES in (11,15)
);

视图操作列值不在分区值列表当中的时候就会报错

假如像上面的表插入8这条数据就会报错

list分区除能和range,hash,key结合起来生成复合的子分区

hash分区

哈希分区只支持证书分区

hash分区主要用来确保数据在预先确定数目的分区中平均分布.在range和list分区中,必须明确指定一个给定的列值或列值集合以指定应该保存在哪个分区中;而在hansh分区1中,mysql自动完成这些工作,要做的只是基于将要被hash的列值制定一个表达式,以及指定备份区的表将要被分割成的分区数量

默认的hash是按照取模的方式 ,比如有三个表就把传入的数字除以三,放到对应的分表中

建立

CREATE TABLE tbl_users3(
uuid INT NOT NULL,
customerId VARCHAR (20),
pwd VARCHAR (20),
showName VARCHAR (100),
trueName VARCHAR (100) ,
registerTime VARCHAR(100)
) 
PARTITIONS by hash (uuid)
partitions 3;

插入数据

-- insert into tbl_users3 values (10,'id2','a','s12','t12','') #这条语句除以三为1所以应该放在第二个分区(p1)中

由于每次插入更新,删除一行都要重新计算一次,这意味着非常复杂的表达式可能会引起性能问题,尤其是在执行同事影响大量行的运算(比如批量插入的时候)

最有效的哈希函数1是只对单个表列进行计算,并且它的值随着列进行移植地增大或减小,因为这考虑了再分区范围上的修建.也就是说,表达式值和它所给予的列的值变化越接近,就能越有效的使用该表达式来进行hahs分区

线性hash分区

线性hash分区在partition by 子句中添加linear关键字

线性哈希分区的优点在于增加,删除合并和拆分分区将变得更加快捷,有利于处理含有极大数据的表,缺点在于,各个表区间数据的分布不大可能均衡 (大部分情况下用一般hash分区)

key分区

类似于按照hash分区,hash分区允许用户自定义的表达式,而key分区不允许使用用户自定义的表达式,hash分区只支持整数分区,key分区除了支持blob或text类型之外的全部数据类型分区

与hash分区不同,创建key分区的时候,可以不指定分区键,默认会选择唯一键或主键作为分区键,没有主键或者唯一键,就必须制定分区键

CREATE TABLE tbl_users(
uuid INT NOT NULL,
customerId VARCHAR (20),
pwd VARCHAR (20),
showName VARCHAR (100),
trueName VARCHAR (100) ,
registerTime VARCHAR(100)
)
PARTITION by linear key (uuid)
partitions 3

子分区

子分区是分区表中每个分区的再次分隔,适合保存非常大量的数据

建立

CREATE TABLE tbl_users5(
uuid INT NOT NULL,
customerId VARCHAR (20),
pwd VARCHAR (20),
showName VARCHAR (100),
trueName VARCHAR (100) ,
registerTime Date
)
partition by range(year(registerTime))
subpartition by hash(TO_DAYS(registerTime))
subpartitions 2 #这里写的2是小分区俩个,下面定义的是主分区三个
(
    PARTITION p0 values less than (2008),
    PARTITION p1 values less than (2015),
    PARTITION p2 values less than MAXVALUE
);

查看执行计划会发现有2*3=6个分区

在mysql5.1中,对于语句通过range或list分区了的表进行子分区是可能的.子分区既可以使用hash分区,也可以使用key分区

如果在一个分区表上任何分区使用subpartition来明确定义任何子分区,那么必须定义所有的子分区(命名就要全部命名)

在每个分区1内子分区的名称必须是唯一

子分区还可以用于特别大的表,可以在多个磁盘间分配数据和索引

分区管理

mysql中的分区在禁止空值null上没有进行处理,无论它是一个列值还是一个用户定义表达式的值,一般而言,在这种情况下爱mysql把null视为0.如果希望回避这种做法,一个在设计表时声明 not null

删除

alter table tbl_users drop partition p0;

• 当删除了一个分区,同时也删除了该分区中所有的数据
• 可以通过show create table tbl_users;来查看新的创建表的语句
• 如果是list分区的话,删除的数据不能新增进来,因为这些行的列值包含在已经删除了的分区的值列中

添加

alter table tbl_users add partition(partition p3 values less than(50))

• 对于range分区的表,值可以添加新的分区到分区表的高端
• 对于list分区的表,不能添加已经包含在现有分区值列表中的任意值()

如果希望能不丢失数据的情况下重新定义分区,可以使用如下语句

alter table tbl_name reorganize paptition partition_list into (partition_definitions)

拆分分区如:alter table tbl_users reorganize partition p1 into (partition s0 values less than(5),partition s1 values less than(10))

合并分区如:alter table tbl_users2 reorganize partition s0,s1 into (partition p0 values in (1,2,3,4,5))

删除所有分区并保留数据:alter table tbl_users remove partitioning

hash和key

减少分区数量语句:alter table tbl_user3 coalesce partition 2;

添加分区数量语句:alter table tbl_user3 add partition partitions 2;

注意

• 最大的分区数目不能超过1024,一般建议对单标的分区数不要超过150个
• 如果含有唯一索引或者主键,则分区列必须包含在所有的唯一索引或者主键内
• 不支持外键
• 不支持全文索引
• 按日期进行分区很合适,因为很多日期函数可以使用.对于字符串来说合适的函数不多
• 只有range和list分区能进行子分区,hash和key分区不能
• 临时表不能被分区
• 分区表要注意对于单条记录的查询没有天然优势
• 要注意选择分区的成本没插入的一行数据都需要按照表达式筛选进入分区

分库分表

介绍

为什么要分库分表

数据库的复制能解决访问问题,并不能解决大规模的并发写入问题,由于无法进行分布式的部署,我而一套服务器的资源(cpu,磁盘,内存io等)是有限的,最终数据库能承载的数据量,数据处理能力都将遭遇瓶颈

好处如下

• 解决磁盘系统最大文件限制
• 减少增量数据写入的时的锁对查询的影响,减少长时间查询造成的表锁,影响写入操作等锁竞争的情况,节省排队的时间开支,增加吞吐量
• 由于单标数量下降,常见的查询操作由于少了需要扫描的记录,使得单次查询的检索行数变少,减少了磁盘io,时延变短

分库

分库又叫垂直切分,就是把原本存储于一个库的表拆分存储到多个库上,通常是将表按照功能模块,关系密切程度划分出来,部署到不同的库上

如果数据库是因为表太多而造成海量数据,并且项目的各业务逻辑划分清晰,低耦合,那么规则简单明了,容易实施的首选就是分库

优点:实现简单,库与库之间的界限分明,便于维护,缺点是不利于频繁跨库操作,单标数据量太大的问题解决不了

分表

分表又叫水平切分,是按照一定的业务逻辑,将一个表的数据拆成多份,分别存储在多个表结构一样的表中,这多个表可以存在一个到多个数据库中.分表又分成垂直分表和水平分表

• 垂直分表:将本来可以在同一个表中的内容,人为划分为多个表.(所谓的本来是指按照关系型数据库第三范式要求,是应该在同一个表的,一般在设计的时候就做了)
• 水平分表也被称为数据分片:是吧一个表复制成同样表结构不同的表,日后把数据按照一定规则划分,分别存储到这些表中,从而保证单张表的容量不会太大,替身性能;当然这些结构一样的表,可以放在一个或者多个数据库当中

优点:解决分库不足点,缺点实现起来比较复杂,特别是分区的规则的划分,程序的编写,以及后期的数据库拆分移植维护

流程

一般当遇到数据库性能瓶颈的时候,先考虑进行主从复制进行读写分离,如果还不够考虑使用垂直分库,将不同的表划分到不同的机器中.还可以把分完之后的库做主从复制,垂直分库到最细可以到单库单表,之后可以采用分区,分区如果解决不了问题可以采用水平分表部署到数据库中

分库

分表的优点是解决分库的不足点,但是缺点是实现起来比较复杂,特别是分规则的规划,程序的编写,以及后期的数据库拆分移植维护.

一般都是先分库再分表,俩者结合使用,取长补短,这样能发挥最大优势,但缺点是架构很大,很复杂,应用程序的编写比较复杂

如何分库

基本的思路就是分析业务功能,以及表间的聚合关系,吧关系紧密的表放在一起

分库的粒度指的是做切分时允许几级的关联表放在一起,这个问题对应用程序实现有着很大的影响,但单标的路由会越简单,与业务的关联性会越小,就越容易使用同一机制处理.

实际的粒度掌握需要结合业务紧密程度和表的数据量俩个因素考虑,一般来说:若划归到一起的表关系紧密,且数据量不大,增速也缓慢,则适宜放在一起,不需要再进行水平切分;若划归到一起表的数据量巨大且增速迅猛,则势必要在分库的基础上再进行分表,这就意味着原单一的库可能会被拆分成多个库,这会导致更多的复杂性,一开始最好就要考虑进去

分表

垂直分表

对于垂直分表,通常是吧按照业务功能的使用频次,把主要的,热门的字段放在一起作为主表;日后吧不常用的,按照各自的业务属性进行聚集,拆分到不同的次要表中;主要和次要表的关系都是一一对应的

对于水平分表通常是按照具体的业务规则和数据的格式,选择能够把数据进行合理拆分的业务数据作为拆分的标准,以此来对数据进行拆分

常见的一些拆分方式:按业务属性,按时间,按区间,hash,按数据会阅读,按数据量等,不管采用什么方式,都要结合具体的业务常见进行分析和考量

水平分表

水平分表的实现面临一系列的问题:切分策略,库节点路由,表路由,全局主键生成,跨节排序/分组/分页/表关联等操作,多数据源事务处理,数据库扩容等

分布式数据库中间件分为俩种,proxy(代理)和客户端式架构。proxy模式有MyCat、DBProxy等，客户端式架构有TDDL、Sharding-JDBC等。

那么proxy和客户端式架构有何区别呢

proxy模式的话我们的select和update语句都是发送给代理，由这个代理来操作具体的底层数据库。所以必须要求代理本身需要保证高可用，否则数据库没有宕机，proxy挂了，那就走远了。

客户端模式通常在连接池上做了一层封装，内部与不同的库连接，sql交给smart-client进行处理。

总结

拿商品查询的案例举例子

垂直分表

用户在列表中查看商品的时候,通常是不会显示商品信息的,只有感兴趣才会点击去访问商品详情,因此商品信息中商品具体内容访问频次较低,占用空间较大,可以将商品信息独立存放一张表,访问频次较高的基本信息单独放在一张表中

讲一个字段按照字段分成多表,每个表存储其中一部分字段

水平分表

单品表存商品存储问题超出了预估,这时候可以尝试水平分表将ID为单数的和双数的Id的用户放在俩个表中

垂直分库

磁盘空间不够的时候,由于商品信息与商品描述耦合度较高,因此放在一个数据库,而店铺信息相对独立,因此被单独存放在另一个数据库中.

垂直分库通过将表按照业务分类,然后分布在不同的数据库,并且可以将这些数据库部署在不同的服务器上,达到多个服务器共同分担压力的效果

水平分库

单品库存商品存储问题超出了预估,这时候可以尝试水平分表将ID为单数的和双数的Id的用户放在俩个表中,与水平分表类似

真正分表比较少,大部分都是分库,分表大多数情况下可以用分区来代替