备战2023 金三银四 java面试题训练营之MySQL专题

1.MySQL分表分库底层设计原理2.MySQ分表分库如何查询呢？3.分表分库JOIN连表如何查询4.分表分库之后分页如何查询5.分表分库之后分布式事务如何处理6.分表分库聚合函数如何查询7.分表分库分片策略有哪些8.分表分库后数据如何平滑扩容9.不同场景下如何选择分片算法10.谈谈MySQL启动流程和架构原理11.myisam和innodb索引底层结构12.为什么InnoDb引擎表必须有主键，并且推荐使用整型的自增方式？13.mysql索引优化如何定位慢查询14.EXPLAINType需要达到什么级别15.MySQL索引为什么使用B+树而不是B树16.mysql索引如何避免回表查询17.mysql索引为什么需要遵循遵循最佳左前缀法则18.MySQL分表分库底层原理19.谈谈MySQ分表分库如何查询20.mysql什么原因会引发行锁、间隙锁、表锁21.谈谈MySQL事务隔离级别22.聊聊MySQLMVCC版本链底层原理23.一不小心将数据库数据删除了如何恢复24.undolog、redolog、binlog之间区别

数据库事务：隔离级别实现——MVCC与锁

这是数据库事务分享的第二篇，上一篇讲解了数据库事务并发会产生的问题，这篇会详细讲数据库如何避免这些问题，也就是如何实现隔离，主要是讲两种主流技术方案——MVCC与锁，理解了MVCC与锁，就可以举一反三地看各种数据库并发控制方案，并理解每种实现能解决的问题以及需要开发者自己注意的并发问题，以更好支撑业务开发。先回顾一下上一篇讨论过的，如果没有隔离或者隔离级别不足，会带来的问题：1、脏读（DirtyRead）：事务T1修改完数据之后写回磁盘，但是还没有提交，事务T2这时读取了T1修改的数据，事务T1最后由于某种原因回滚了，此时事务T2就读取到了脏数据。2、不可重复读（UnrepeatableRead）：事务T1按某一条件读取数据之后，事务T2执行了更新或删除操作，导致事务T1按照相同条件读取到的数据不一致，不可重复读主要有三种情况：（1）事务T1按照某一条件读取数据之后，T2进行更新操作，当事务T1再使用相同条件查询时发现结果的值与之前不一样；（2）事务T1按照某一条件读取数据之后，T2进行删除操作，删除了部分记录，当事务T1使用相同条件查询的时候，发现某些记录神秘的”消失“了；（3）事务T1按照某一条件读取数据之后，T2进行插入操作，增加了一些记录，当事务T1使用相同条件查询的时候，发现神秘的多了一些记录；其中后两种不可重复读又被称为幻读。3、丢失更新（LostUpdates）：两个事务T1和T2同时修改一个数据，T2提交的结果把T1修改的结果覆盖了，这就使得事务T1的修改丢失了。可见，所有问题本质上都是由写造成的，根源都是数据的改变。读是不改变数据的，因此无论多少读并发，都不会出现冲突，如果所有的事务都只由读组成，那么无论如何调度它们，它们都是可串行化的，因为它们的执行结果，都与某个串行执行的结果相同，但是写会造成数据的改变，稍有不慎，这个并发调度的结果就会与串行调度的结果不符合。为了解决数据库并发事务带来的问题，就提出了数据库隔离级别的概念。1、读未提交（ReadUncommited）：这个隔离级别中允许一个事务读取另一个事务未提交的数据。这个级别下会导致脏读、不可重复读和丢失更新。2、读已提交（ReadCommited）：这个隔离级别允许读取一个事务已经提交的数据，可以解决脏读，但是不可重复读和幻读仍然会发生。3、可重复读（RepeatableRead）：这个是InnoDB数据库引擎的默认隔离级别。它可以保证同一事务内多次查询的结果是一致的，可以防止脏读和不可重复读，但是幻读仍有可能发生（MySQL在该级别下已经解决了幻读问题）。4、串行化（Serializable）：最高的隔离级别，完全服从事务的ACID特性，可以确保防止脏读、不可重复读、幻读、丢失跟新等并发问题。他完全通过读写都加锁，锁定事务相关数据库表来实现，效率不高。一、基于锁实现可串行化（本节以MySQLInnoDB为基本模型）1.读锁与写锁实现可串行化的基石是控制冲突，强行保证冲突操作的串行化，那么应该遵循以下原则：读-写应该排队写-写应该排队读的时候不能写，写的时候不能读也不能写，但是读的时候可以读，因为读不冲突，于是数据库需要两种锁：排它锁（exclusivelock）又称X锁，这是最好理解的锁，在一般的并发编程中，我们为资源加上的一般都是排它锁，要获取锁，必须是资源处于未被加锁状态，如果有人已经为资源加锁，则需要等待锁释放才能获取锁，这种锁能够保证并发时也能够串行处理某个资源，实现排队的目的。**共享锁（sharelock）**又称S锁，这是比排它锁更加宽松的锁，当一个资源没有被加锁或者当前加锁为共享锁时，可以为它加上共享锁，也就是一个资源可以同时被加无限个共享锁。此时由于资源已经被加锁，虽然可以继续加共享锁，但是不能加排它锁，需要等待资源的锁被完全释放才能获取排它锁。共享锁的目的是为了提高非冲突操作的并发数，同时能够保证冲突操作的排队执行。这两种锁和读、写是什么关系呢？读写都会加锁，但是读-读可以并发，写则需要与任何操作排队，所以：获取记录的共享锁（S锁），则仅允许事务读取它，简单来说共享锁只是读锁，记录被加读锁后，其他记录也可以往上加读锁，也就是大家都可以读。获取记录的排它锁（X锁），则允许这个事务更新它，排它锁让事务既可以读也可以写，是读写通用的锁，记录被加排他锁后，其他事务不论是想加排它锁还是共享锁，都需要排队等待目前的排它锁释放才能加锁。由于强行排队的特性导致效率比较低，读-读不冲突所以大多数读取都不会加排它锁，不过在MySQL中可以使用SELECTFORUPDATE语句指定为记录加上排它锁。通过读写操作加锁，实现了读写、写写的排队，但是靠简单加锁保证的排队，但排队粒度太小，仅仅是操作与操作之间的排队，不足以解决上面图中的不可串行化问题，因为如果事务1读A后马上释放读锁，则事务2可以马上获取到A的写锁，改变A的值，还是会出现上面的不可串行化问题，因此事务需要保证更大粒度的排队——如果一个记录被某个事务读取或者写入，则直到这个事务提交，才能被别的事务修改，严格两阶段加锁（StrictTwo-PhaseLocking）由此诞生。2.严格两阶段加锁（StrictTwo-PhaseLocking）首先提一句什么是两阶段加锁协议（2PL），它规定事务的加锁与解锁分为2个独立阶段，加锁阶段只能加锁不能解锁，一旦开始解锁，则进入解锁阶段，不能再加锁。严格两阶段加锁（S2PL）在2PL的基础上规定事务的解锁阶段只能是执行commit或者rollback后，因此S2PL保证了一个事务曾经读取或写入的记录，在此事务commit或rollback前都不会被释放锁，因此不能被其他记录加锁，不会造成记录的改变，由此实现了可串行化。3.多粒度加锁与意向锁（IntentionLock）InnoDB中不止支持行级锁，还支持表级锁，为了兼容多粒度的锁，设计了一种特殊的锁——意向锁（IntentionLock），它本身不具备锁的功能，只承担“指示”功能。如果要加表级锁，则必须保证行级锁已完全释放，整张表都没有任何锁时，才能为表加上表锁。那么问题来了，怎么判断是否整张表的每一条记录都已经释放锁？如果通过遍历每条记录的加锁状态，未免效率太低，因此需要意向锁，它只是一个指示牌，告诉数据库，在此粒度之下有没有被加锁，被加了什么锁。就像停车场会在门口立一个牌子指示“车位已满”还是“内有空余”，不需要开车进去一个个车位检查，提高了效率。InnoDB如果要对一条记录进行加锁，它需要先向表加上意向锁，然后才能对记录加普通锁，获取意向锁失败，则不能继续向下获取锁。意向锁之间是完全兼容的，很好理解，因为意向锁只代表事务想向下获取锁，具体是哪条记录不确定，因此意向锁是完全兼容的，即使表上已经被其他事务加了某种意向锁，事务还是能够成功为表加意向锁。一般我们不会在事务中加表锁，表锁效率太低，我们加的一般是行级锁，行级锁是加在某条特定的记录上，我们称之为记录锁。这一节的内容主要是对多粒度加锁有个概念，现实中很少用表锁。上面说的共享锁、排它锁是按照锁兼容性定义，表锁、记录锁（RecordLock）则是按加锁范围定义，根据加锁范围不同，还有其他N种锁，下面会提到一些。4.MySQL是如何加行锁的？首先要清楚一点，对于MySQL只有InnoDB支持行锁，并且即使是使用对InnoDB存储引擎，它也只会针对索引去添加行锁。在RR隔离级别下的加锁机制记录锁（RecordLock）记录锁是最简单的一种行锁类型，记录锁封锁的是记录，例如：它会在id=1的记录上加上记录锁，以阻止其他事务插入，更新，删除id=1这一行间隙锁（GapLock）考虑一个例子：事务1执行“SELECTnameFROMstudentsWHEREage=18”返回结果为“张三”，而事务2马上插入一行记录“INSERTINTOstudentsVALUES("李四"，18)”并提交，事务1再次执行相同的SELECT语句，发现结果变为了“张三”+“李四”，这就是一种幻读现象，同一个事务进行的两次相同条件的读取，却读取到了之前没有读到的记录。有了记录锁虽然可以实现对已存在记录进行并发控制，也就是对于更新、删除操作，再也不会有并发问题，但是无法对插入做并发控制，因为插入操作是对不存在的记录，而还不存在的记录，我们无法为其加记录锁，因此可能会产生幻读现象。为了解决这个问题，出现了间隙锁，间隙锁也是加在某一条记录上，可是它并不锁住记录本身，它只锁住这条记录与它的上一条记录之间的间隙，防止插入。如下图所示，如果一张表有主键为1、2、5的三条记录，如果5被加上间隙锁，只会锁住开区间(2,5)间隙，而不会锁住5这条记录本身。如果事务要插入记录，需要获取插入意向锁（InsertIntentionLock）,如果需要插入的间隙有间隙锁，则获取插入意向锁会失败必须进行锁等待，从而实现了阻塞插入。在可串行化隔离级别或MySQL的RR级别，使用锁住间隙去防止插入，从而避免了幻读。产生间隙锁是有条件的条件（RR事务隔离级别下）：使用普通索引锁定；使用多列唯一索引；使用唯一索引锁定多行记录。临键锁(Next-KeyLock)很多时候需要锁住多个间隙以及记录本身，比如执行“SELECTnameFROMstudentsWHEREid>=1”，需要锁住(1,3)、(3,5)、（6、7）以及1、3、5、7四条记录本身：间隙锁和记录锁是两种锁结构，因此不能合并，如果为3个间隙分别加间隙锁，4条记录分别加记录锁，则会产生7条锁记录，很占用内存，因此MySQL有一种锁称为Next-KeyLock，如果在小红的记录上面加Next-KeyLock，则会锁住(1,3]这个前开后闭的区间，也就是锁住了记录本身+记录之前的间隙，可以发现，Next-KeyLock其实就是GapLock+RecordLock。此时锁结构就可以简化成为ID为1的记录加上记录锁+后面连续的3个Next-KeyLock，由于Next-KeyLock类型相同并且连续，可以将它们放入同一个锁记录，最后只有ID为1的记录锁+1个Next-KeyLock。Next-KeyLock并没有什么特别之处，只是对RecordLock+GapLock的一种简化。执行语句后的加锁情况图示：二、使用MVCC实现事物的隔离性可串行化虽然保证了事务的绝对安全，但是并发度很低，很多操作都需要排队进行，为了提高效率，SQL标准在隔离级别上进行了妥协，由此有了可重复读、读已提交的隔离级别，它们都允许部分并发问题。MySQL中RC和RR两个隔离级别就是通过MVCC机制实现的。MVCC全称MultipleVersionConcurrencyControl，也就是多版本并发控制，实现的逻辑就是读不加锁，写加锁，读写不冲突。1、MVCC实现原理（1）版本链（undolog）根据行为的不同，undolog可以分为两种：insertundolog和updateundologinsertundolog是在insert操作中产生的undolog。因为insert操作的记录只对事务本身可见，对于其他事务此几率是不可见的，所以insertundolog可以在事务提交之后而不需要进行purge操作updateundolog是update或delete操作中产生的undolog，因为会对已经存在的记录产生影响，为了提供MVCC机制，因此updateundolog不能在事务提交时就直接删除，而是在事务提交时放入historylist上，等待purge线程进行最后的删除操作。是不是没明白啥意思，别着急，我画图给你看：在MySQL中每条记录除了有我们定义的字段之外，他还为我们默认生成了是三个隐式字段：trx_id、roll_pointe和row_idtrx_id代表这条记录版本是被哪个事务创建的，数据库有一个全局的事务ID分配器，它一定是递增的，新的事务ID一定不会和旧的事务ID重复。roll_pointer是连接版本链的指针。row_id不是必须的，他的作用和数据记录的主键一样。当数据库字段有指定主键时，Mysql会选取唯一字段作为主键，如果没有唯一字段，那就生成一个6字节的row_id来作为主键。在MVCC中，每条记录都有多个版本，串成了一个版本链，也就是说，记录被UPDATE时并不是InPlaceUpdate，而是将记录复制然后修改存一份到版本链，被DELET时，也不是马上从文件删除，而是将记录标记为被删除，它也是版本链的一环。接下来，我们通过一个一个例子来感受一下这个过程：比如，我们有一个表产品表(product)，有三个字段：ID，product_name，price我们开启一个事务插入一条数据：上面这条语句执行时候，就会在undolog中产生一个版本记录，重点说明一下后面的几个隐式字段，trx_id它记录的是执行操作的事物ID，roll_pointer对于插入语句是null，这个表有指定记录id，因此也是null之后我们对这条数据进行一些更新操作：上面这个事务就是一个更新操作了，执行后undolog如下：（2）ReadViewMVCC中最常听到的概念就是快照，其实快照只是最终结果，而不是实现方式，快照=版本链+ReadView。首先要明确只有SELECT操作需要ReadView，换句话说进行读操作时SELECT语句读取到的到底是哪个版本的数据，取决于ReadView。ReadView保存着当前活跃事务的ID，具体有以下信息：ReadView什么时候产生？这个问题对于RC和RR的实现是不同的：RC：在每个事务中的每个SELECT语句执行的时候实时产生一个全新的ReadViewRR：在一个事务中，只会在第一个SELECT语句执行的时候产生一个ReadView，之后的SELECT语句复用这个ReadView从上面描述的ReadView生成时机就可以知道，RC每执行一个SELECT语句，就会产生一个ReadView，因此ReadView就可能会发生改变，上面说过，使用MVCC机制，查询结果完全取决于ReadView，因此即使在同一个事务中，SELECT查询的结果当然会不一致；而RR在一个事务中值有一个ReadView，无论你如何修改，在执行第一个SELECT那一刻，ReadView已经定型了，自然多次读取结果不会改变了。ReadView可不可用如何判断？ReadView结合版本链使用，当事务读取某条记录时，会根据此事务的ReadView判断此记录的哪个版本是这个事务可见的：如果记录的trx_id与creator_trx_id相同，则代表这个版本是此事务创建的，可以读取。如果记录的trx_id小于min_trx_id，代表这个版本是此事务生成ReadView之前就已经创建的，可以读取。如果记录的trx_id大于等于max_trx_id，代表这个版本是此事务生成ReadView之后开启的事务创建的，一定不能被读取。如果记录的trx_id处于min_trx_id与max_trx_id之间，则判断trx_id是否在m_ids中，如果不在，则代表这个版本是此事务生成ReadView时已经提交的，可以读取，否则就不可以读取。有了版本链和ReadView，即使其他事务修改了记录，先生成ReadView的事务也不会读到，只要ReadView不改变，每次读到的版本一定相同。2.MVCC的局限性MVCC取代了读锁的位置，它不阻塞写入虽然有提高效率的优势，但是同时也无法防止所有并发问题。（1）MVCC能避免幻读吗事务是无法读到ReadView生成后别的事务产生的记录版本，因此可以在不加间隙锁的情况下也不会读到别的事务的插入，那MVCC能避免幻读吗？先说结论：MVCC不可以避免幻读。导致这个问题的根本原因是：InnoDB将Update、Insert、Delete都视为特殊操作，特殊操作对记录进行的是当前读（CurrentRead），也就是会读取最新的记录，也就是说ReadView只对SELECT语句起作用。如果users表中有id为1、2、3共3条记录，事务A先读，事务B插入一条记录并提交，事务A更新被插入的记录是可以成功的，因为UPDATE是进行当前读，更新时可以读到id为4的记录存在，因此可以成功更新，事务A成功更新id为4的记录后，将在id为4的记录版本链上新增一条事务A的版本，因此事务A再次SELECT，就可以名正言顺地读到这条记录，符合ReadView规则，但产生了幻读。如果要避免幻读，可以使用MVCC+间隙锁的方式。（2）无法避免ReadSkew与WriteSkew由于MVCC中读-写互不阻塞，因此事务读取的快照可能已经过期，读到的可能已经成为陈旧数据，因此可能出现ReadSkew与WriteSkew。（3）无法避免丢失更新还是由于读-写不阻塞的特性：R1(A)=>R2(A)=>W2(A)=>W1(A)事务1读出的A值已经过期，但是它不知道，还是根据旧的A值去更新A，最后覆盖了事务2的写入。在Postgrel中，RepeatableRead级别就已经避免了丢失更新，因为它使用MVCC+乐观锁，如果事务1去写入A，存储引擎检测到A值已经在事务1开启后被别的事务修改过，则会报错，阻止事务1的写入。单纯的MVCC并不能防止丢失更新，需要配合其他机制。三、事务更佳实践在进行业务开发时应该先了解项目使用的数据库的事务隔离级别以及其原理、表现，然后根据事务实现原理去思考更好的编码方式。1.避免死锁语句顺序不同导致死锁这种情况大家一定很熟悉了：因此建议在不同的业务中，尽量统一操作相同记录语句的顺序。索引顺序不同导致死锁锁都是加在索引上的（这里最好先理解一下B+Tree索引），所以一条SQL如果涉及多个索引，会为每个索引加锁，比如有一张users表（id,user_name,password），主键为id，在user_name上有一个唯一索引（UniqueIndex），以下语句：UPDATEusersSETuser_name='j.huang@aftership.com'WHEREid=1;这条语句中涉及到了id与user_name两个索引，InnoDB是索引组织表，主键是聚簇索引，因此记录是存在主键聚簇索引结构中的，那么这条SQL的加锁顺序为：为表加上IX锁为主键加上X锁为索引user_name加上X锁此时如果另一条事务执行如下语句：UPDATEusersSETpassword='123'WHEREuser_name='j.huang@aftership.com';则可能产生死锁。原因大家可以先思考一下。这条语句的加锁顺序是：找到user_name为'j.huang@aftership.com'的索引，加X锁为表加IX锁为主键加X锁他们都会对同一个主键索引加锁和同一个二级索引，但是加锁顺序不同，因此可能造成死锁，这种情况很难避免，MySQL中可以通过SHOWENGINEINNODBSTATUS查看InnoDB的死锁检测情况。2.避免不必要的事务其实很多业务场景并不需要事务，比如说领取优惠券，并不需要开启一个Serializable级别的事务去SELECT优惠券剩余数量，判断是否有余量，再UPDATE领取优惠券，完全可以一条语句解决：UPDATEcouponsSETbalance=balance-1WHEREid=1andbalance>=1;语句返回后判断更新行数，如果更新行数为1，则代表领取成功，更新行数为0，代表没有符合条件的记录，领取失败。（注意：这里只考虑领取优惠券的场景，如果业务还需要将优惠券写入users表等其他一系列操作，就需要根据业务需求放入事务）3.避免将不必要的SELECT放入事务首先应该理解将SELECT放入事务的意义是什么？需要读取事务自己的版本，则必须将SELECT放入事务需要依赖SELECT结果作为其他语句的前提，此时不止要把SELECT放入事务，还必须保证事务是Serializable级别的如果不是以上两个原因，则SELECT是没有必要放入事务的，比如下单一件产品，如果只是SELECT它的product_name去写入orders表，这种非强一致要求的数据，没有必要放入事务，因为product_name即使被改变了，写入order的product_name是1秒前的旧数据，也是可以接受的。4.不要迷信事务很多开发者误以为将SELECT放入事务，将结果作为判断条件或者写入条件是安全的，其实根据隔离级别不同，是不一定的，举个例子：SELECTusers表某个用户等级信息，如果是钻石会员，则为他3倍积分将算出的积分UPDATE到user_scores表将这两条语句放入事务也不一定是安全的，这取决于事务的实现，如果是InnoDB的RepeatableRead级别，那么这个事务是不安全的，因为SELECT读到的是快照，在UPDATE之前，其他事务可能就已经修改了user的等级信息，他可能已经不满足3倍积分条件，而此时再去UPDATEuser_scores表，这个事务是个业务不安全的事务。因此，要先了解事务，再去使用，否则容易用错。

面试之前你不得不知道的MySQL锁”事“

mysql中的锁看起来是很复杂的，因为有一大堆的东西和名词：排它锁，共享锁，表锁，页锁，间隙锁，意向排它锁，意向共享锁，行锁，读锁，写锁，乐观锁，悲观锁，死锁。这些名词有的博客又直接写锁的英文的简写--->X锁，S锁，IS锁，IX锁，MMVC...锁的相关知识又跟存储引擎，索引，事务的隔离级别都是关联的....这就给初学数据库锁的人带来不少的麻烦~~~于是我下面就简单整理一下数据库锁的知识点，希望大家看完会有所帮助。1、为什么需要学习数据库锁知识不少人在开发的时候，应该很少会注意到这些锁的问题，也很少会给程序加锁(除了库存这些对数量准确性要求极高的情况下)一般也就听过常说的乐观锁和悲观锁，了解过基本的含义之后就没了~定心丸：即使我们不会这些锁知识，我们的程序在一般情况下还是可以跑得好好的。因为这些锁数据库隐式帮我们加了对于UPDATE、DELETE、INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预只会在某些特定的场景下才需要手动加锁，学习数据库锁知识就是为了:能让我们在特定的场景下派得上用场更好把控自己写的程序在跟别人聊数据库技术的时候可以搭上几句话构建自己的知识库体系！在面试的时候不虚2、表锁简单介绍首先，从锁的粒度，我们可以分成两大类：表锁开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低行锁开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高不同的存储引擎支持的锁粒度是不一样的：InnoDB行锁和表锁都支持！MyISAM只支持表锁！InnoDB只有通过索引条件检索数据才使用行级锁，否则，InnoDB将使用表锁也就是说，InnoDB的行锁是基于索引的！表锁下又分为两种模式：表读锁（TableReadLock）表写锁（TableWriteLock）从下图可以清晰看到，在表读锁和表写锁的环境下：读读不阻塞，读写阻塞，写写阻塞-读读不阻塞：当前用户在读数据，其他的用户也在读数据，不会加锁读写阻塞：当前用户在读数据，其他的用户不能修改当前用户读的数据，会加锁！写写阻塞：当前用户在修改数据，其他的用户不能修改当前用户正在修改的数据，会加锁！从上面已经看到了：读锁和写锁是互斥的，读写操作是串行。如果某个进程想要获取读锁，同时另外一个进程想要获取写锁。在mysql里边，写锁是优先于读锁的！写锁和读锁优先级的问题是可以通过参数调节的：max_write_lock_count和low-priority-updates值得注意的是：TheLOCALmodifierenablesnonconflictingINSERTstatements(concurrentinserts)byothersessionstoexecutewhilethelockisheld.(SeeSection8.11.3,“ConcurrentInserts”.)However,READLOCALcannotbeusedifyouaregoingtomanipulatethedatabaseusingprocessesexternaltotheserverwhileyouholdthelock.ForInnoDBtables,READLOCAListhesameasREADMyISAM可以支持查询和插入操作的并发进行。可以通过系统变量concurrent_insert来指定哪种模式，在MyISAM中它默认是：如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。但是InnoDB存储引擎是不支持的！参考资料：dev.mysql.com/doc/refman/…--官方手册ourmysql.com/archives/56…---几个参数说明3、行锁细讲上边简单讲解了表锁的相关知识，我们使用Mysql一般是使用InnoDB存储引擎的。InnoDB和MyISAM有两个本质的区别：InnoDB支持行锁InnoDB支持事务从上面也说了：我们是很少手动加表锁的。表锁对我们程序员来说几乎是透明的，即使InnoDB不走索引，加的表锁也是自动的！我们应该更加关注行锁的内容，因为InnoDB一大特性就是支持行锁！InnoDB实现了以下两种类型的行锁。共享锁（S锁）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。也叫做读锁：读锁是共享的，多个客户可以同时读取同一个资源，但不允许其他客户修改。排他锁（X锁)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。也叫做写锁：写锁是排他的，写锁会阻塞其他的写锁和读锁。看完上面的有没有发现，在一开始所说的：X锁，S锁，读锁，写锁，共享锁，排它锁其实总共就两个锁，只不过它们有多个名字罢了~~~Intentionlocksdonotblockanythingexceptfulltablerequests(forexample,LOCKTABLES...WRITE).Themainpurposeofintentionlocksistoshowthatsomeoneislockingarow,orgoingtolockarowinthetable.另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（IntentionLocks），这两种意向锁都是表锁：意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。意向锁也是数据库隐式帮我们做了，不需要程序员操心！参考资料：www.zhihu.com/question/51…dev.mysql.com/doc/refman/…3.1MVCC和事务的隔离级别数据库事务有不同的隔离级别，不同的隔离级别对锁的使用是不同的，锁的应用最终导致不同事务的隔离级别MVCC(Multi-VersionConcurrencyControl)多版本并发控制，可以简单地认为：MVCC就是行级锁的一个变种(升级版)。事务的隔离级别就是通过锁的机制来实现，只不过隐藏了加锁细节在表锁中我们读写是阻塞的，基于提升并发性能的考虑，MVCC一般读写是不阻塞的(所以说MVCC很多情况下避免了加锁的操作)MVCC实现的读写不阻塞正如其名：多版本并发控制通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)，并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好像是数据库可以提供同一数据的多个版本。快照有两个级别：语句级：针对于Readcommitted隔离级别事务级别：针对于Repeatableread隔离级别我们在初学的时候已经知道，事务的隔离级别有4种：Readuncommitted：会出现脏读，不可重复读，幻读Readcommitted：会出现不可重复读，幻读Repeatableread：会出现幻读(但在Mysql实现的Repeatableread配合gap锁不会出现幻读！)Serializable：串行，避免以上的情况！Readuncommitted会出现的现象--->脏读：一个事务读取到另外一个事务未提交的数据例子：A向B转账，A执行了转账语句，但A还没有提交事务，B读取数据，发现自己账户钱变多了！B跟A说，我已经收到钱了。A回滚事务【rollback】，等B再查看账户的钱时，发现钱并没有多。出现脏读的本质就是因为操作(修改)完该数据就立马释放掉锁，导致读的数据就变成了无用的或者是错误的数据。Readcommitted避免脏读的做法其实很简单：就是把释放锁的位置调整到事务提交之后，此时在事务提交前，其他进程是无法对该行数据进行读取的，包括任何操作但Readcommitted出现的现象--->不可重复读：一个事务读取到另外一个事务已经提交的数据，也就是说一个事务可以看到其他事务所做的修改注：A查询数据库得到数据，B去修改数据库的数据，导致A多次查询数据库的结果都不一样【危害：A每次查询的结果都是受B的影响的，那么A查询出来的信息就没有意思了】上面也说了，Readcommitted是语句级别的快照！每次读取的都是当前最新的版本！Repeatableread避免不可重复读是事务级别的快照！每次读取的都是当前事务的版本，即使被修改了，也只会读取当前事务版本的数据。呃...如果还是不太清楚，我们来看看InnoDB的MVCC是怎么样的吧(摘抄《高性能MySQL》)至于虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。注：和不可重复读类似，但虚读(幻读)会读到其他事务的插入的数据，导致前后读取不一致MySQL的Repeatableread隔离级别加上GAP间隙锁已经处理了幻读了。参考资料：www.jianshu.com/p/cb97f76a9…www.zhihu.com/question/26…扩展阅读：www.zhihu.com/question/67…4、乐观锁和悲观锁无论是Readcommitted还是Repeatableread隔离级别，都是为了解决读写冲突的问题。单纯在Repeatableread隔离级别下我们来考虑一个问题：此时，用户李四的操作就丢失掉了：丢失更新：一个事务的更新覆盖了其它事务的更新结果。(ps:暂时没有想到比较好的例子来说明更新丢失的问题，虽然上面的例子也是更新丢失，但一定程度上是可接受的..不知道有没有人能想到不可接受的更新丢失例子呢...)解决的方法：使用Serializable隔离级别，事务是串行执行的！乐观锁悲观锁乐观锁是一种思想，具体实现是，表中有一个版本字段，第一次读的时候，获取到这个字段。处理完业务逻辑开始更新的时候，需要再次查看该字段的值是否和第一次的一样。如果一样更新，反之拒绝。之所以叫乐观，因为这个模式没有从数据库加锁，等到更新的时候再判断是否可以更新。悲观锁是数据库层面加锁，都会阻塞去等待锁。4.1悲观锁所以，按照上面的例子。我们使用悲观锁的话其实很简单(手动加行锁就行了)：select*fromxxxxforupdate在select语句后边加了forupdate相当于加了排它锁(写锁)，加了写锁以后，其他的事务就不能对它修改了！需要等待当前事务修改完之后才可以修改.也就是说，如果张三使用select...forupdate，李四就无法对该条记录修改了~4.2乐观锁乐观锁不是数据库层面上的锁，是需要自己手动去加的锁。一般我们添加一个版本字段来实现：具体过程是这样的：张三select*fromtable--->会查询出记录出来，同时会有一个version字段李四select*fromtable--->会查询出记录出来，同时会有一个version字段李四对这条记录做修改：updateAsetName=lisi,version=version+1whereID=#{id}andversion=#{version}，判断之前查询到的version与现在的数据的version进行比较，同时会更新version字段此时数据库记录如下：张三也对这条记录修改：updateAsetName=lisi,version=version+1whereID=#{id}andversion=#{version}，但失败了！因为当前数据库中的版本跟查询出来的版本不一致！参考资料：zhuanlan.zhihu.com/p/31537871---什么是悲观锁和乐观锁www.zhihu.com/question/27…---乐观锁和MVCC的区别？5、间隙锁GAP当我们用范围条件检索数据而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”。InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。值得注意的是：间隙锁只会在Repeatableread隔离级别下使用~例子：假如emp表中只有101条记录，其empid的值分别是1,2,...,100,101Select*fromempwhereempid>100forupdate;复制代码上面是一个范围查询，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。InnoDB使用间隙锁的目的有两个：为了防止幻读(上面也说了，Repeatableread隔离级别下再通过GAP锁即可避免了幻读)满足恢复和复制的需要-MySQL的恢复机制要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，也就是不允许出现幻读6、死锁并发的问题就少不了死锁，在MySQL中同样会存在死锁的问题。但一般来说MySQL通过回滚帮我们解决了不少死锁的问题了，但死锁是无法完全避免的，可以通过以下的经验参考，来尽可能少遇到死锁：1）以固定的顺序访问表和行。比如对两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；将两个事务的sql顺序调整为一致，也能避免死锁。2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。5）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。参考资料：hedengcheng.com/?p=771#_Toc…www.cnblogs.com/LBSer/p/518…7、锁总结上面说了一大堆关于MySQL数据库锁的东西，现在来简单总结一下。表锁其实我们程序员是很少关心它的：在MyISAM存储引擎中，当执行SQL语句的时候是自动加的。在InnoDB存储引擎中，如果没有使用索引，表锁也是自动加的。现在我们大多数使用MySQL都是使用InnoDB，InnoDB支持行锁：共享锁--读锁--S锁排它锁--写锁--X锁在默认的情况下，select是不加任何行锁的~事务可以通过以下语句显示给记录集加共享锁或排他锁。共享锁（S）：SELECT*FROMtable_nameWHERE...LOCKINSHAREMODE。排他锁（X)：SELECT*FROMtable_nameWHERE...FORUPDATE。InnoDB基于行锁还实现了MVCC多版本并发控制，MVCC在隔离级别下的Readcommitted和Repeatableread下工作。MVCC能够实现读写不阻塞！InnoDB实现的Repeatableread隔离级别配合GAP间隙锁已经避免了幻读！乐观锁其实是一种思想，正如其名：认为不会锁定的情况下去更新数据，如果发现不对劲，才不更新(回滚)。在数据库中往往添加一个version字段来实现。悲观锁用的就是数据库的行锁，认为数据库会发生并发冲突，直接上来就把数据锁住，其他事务不能修改，直至提交了当前事务参考【1】Java3y.数据库两大神器【索引和锁】.掘金

MySQL数据库COUNT语句面试大坑你确定不了解一下

数据库查询相信很多人都不陌生，所有经常有人调侃程序员就是CRUD专员，这所谓的CRUD指的就是数据库的增删改查。在数据库的增删改查操作中，使用最频繁的就是查询操作。而在所有查询操作中，统计数量操作更是经常被用到。关于数据库中行数统计，无论是MySQL还是Oracle，都有一个函数可以使用，那就是COUNT。但是，就是这个常用的COUNT函数，却暗藏着很多玄机，尤其是在面试的时候，一不小心就会被虐。不信的话请尝试回答下以下问题：1、COUNT有几种用法？2、COUNT(字段名)和COUNT(*)的查询结果有什么不同？3、COUNT(1)和COUNT(*)之间有什么不同？4、COUNT(1)和COUNT(*)之间的效率哪个更高？5、为什么《阿里巴巴Java开发手册》建议使用COUNT(*)6、MySQL的MyISAM引擎对COUNT(*)做了哪些优化？7、MySQL的InnoDB引擎对COUNT(*)做了哪些优化？8、上面提到的MySQL对COUNT(*)做的优化，有一个关键的前提是什么？9、SELECTCOUNT(*)的时候，加不加where条件有差别吗？10、COUNT(*)、COUNT(1)和COUNT(字段名)的执行过程是怎样的？初始COUNT语句关于COUNT函数，在MySQL官网中有详细介绍：COUNT语句的语法很简单：COUNT(expr)，它返回SELECT语句检索的行中expr的值不为NULL的数量。结果是一个BIGINT值。如果检索的行中没有符合要求的就返回0。COUNT语句一般有三种用法：**COUNT(列名)、COUNT(常量)和COUNT(*)**。三种用法中COUNT(*)是SQL92的标准语法以及是阿里推荐的方式。（下图来自阿里巴巴《Java开发规范手册》）COUNT(列名)、COUNT(常量)和COUNT(*)之间的区别COUNT(常量)和COUNT(*)表示的是直接查询符合条件的数据库表的行数。而COUNT(列名)表示的是查询符合条件的列的值不为NULL的行数。COUNT(*)是SQL92定义的标准统计行数的语法，因为他是标准语法，所以MySQL数据库对他进行过很多优化。SQL92：SQL92是数据库的一个ANSI/ISO标准。它定义了一种语言（SQL）以及数据库的行为（事务、隔离级别等）。COUNT(*)的优化前面提到了COUNT(*)是SQL92定义的标准统计行数的语法，所以MySQL数据库对他进行过很多优化。那么，具体都做过哪些事情呢？MySQL不同存储引擎对COUNT(*)的优化是不同的，就拿常用的InnoDB和MyISAM来说：因为MyISAM的锁是表级锁，所以同一张表上面的操作需要串行进行，所以，MyISAM做了一个简单的优化，那就是它可以把表的总行数单独记录下来，如果从一张表中使用COUNT(*)进行查询的时候，可以直接返回这个记录下来的数值就可以了，当然，前提是不能有where条件。MyISAM之所以可以把表中的总行数记录下来供COUNT(*)查询使用，那是因为MyISAM数据库是表级锁，不会有并发的数据库行数修改，所以查询得到的行数是准确的。对于InnoDB来说，就不能做这种缓存操作了，因为InnoDB支持事务，其中大部分操作都是行级锁，所以可能表的行数可能会被并发修改，那么缓存记录下来的总行数就不准确了。在InnoDB中，使用COUNT(*)查询行数的时候，不可避免的要进行扫表了，那么，就可以在扫表过程中下功夫来优化效率了。从MySQL8.0.13开始，针对InnoDB的SELECTCOUNT(*)FROMtbl_name语句，确实在扫表的过程中做了一些优化。前提是查询语句中不包含WHERE或GROUPBY等条件。我们知道，InnoDB中索引分为聚簇索引（主键索引）和非聚簇索引（非主键索引），聚簇索引的叶子节点中保存的是整行记录，而非聚簇索引的叶子节点中保存的是该行记录的主键的值。所以，相比之下，非聚簇索引要比聚簇索引小很多，所以MySQL执行COUNT(*)的时候优先选择最小的非聚簇索引来扫表。所以，当我们建表的时候，除了主键索引以外，创建一个非主键索引还是有必要的。COUNT(*)和COUNT(1)介绍完了COUNT(*)，接下来看看COUNT(1)，对于，这二者到底有没有区别，网上的说法众说纷纭。有的说COUNT(*)执行时会转换成COUNT(1)，所以COUNT(1)少了转换步骤，所以更快。还有的说，因为MySQL针对COUNT(*)做了特殊优化，所以COUNT(*)更快。那么，到底哪种说法是对的呢？看下MySQL官方文档是怎么说的：InnoDBhandlesSELECTCOUNT(*)andSELECTCOUNT(1)operationsinthesameway.Thereisnoperformancedifference.画重点：sameway,noperformancedifference。所以，对于COUNT(1)和COUNT(*)，MySQL的优化是完全一样的，根本不存在谁比谁快！那既然COUNT(*)和COUNT(1)一样，建议用哪个呢？建议使用COUNT(*)！因为这个是SQL92定义的标准统计行数的语法，而且本文只是基于MySQL做了分析，关于Oracle中的这个问题，也是众说纷纭的呢。COUNT(字段)最后，就是我们一直还没提到的COUNT(字段)，他的查询就比较简单粗暴了，就是进行全表扫描，然后判断指定字段的值是不是为NULL，不为NULL则累加。相比COUNT(*)，COUNT(字段)多了一个步骤就是判断所查询的字段是否为NULL，所以他的性能要比COUNT(*)慢。总结数据库的COUNT语句可以用来统计符合SELECT条件的表行数，常见的用法主要有：COUNT(列名)、COUNT(1)和COUNT(*)。因为COUNT(*)是SQL92的标准语法，因此MySQL对他进行了一系列的优化：MyISAM存储引擎借助于表锁可以直接把数据表的总行数记录下来供COUNT(*)使用;而InnoDB则是采用扫描非聚簇索引来降低扫表的成本。当然，这些优化的前提都是没有进行where和group的条件查询。在InnoDB中COUNT(*)和COUNT(1)在实现上是一样的，在性能上也没有差别；但是COUNT(字段)在扫表的时候需要判断字段时候为NULL，因此效率会低一些。最终结论：因为COUNT(*)是SQL92定义的标准统计行数的语法，并且效率高，所以请直接使用COUNT(*)查询表的行数！

MySQL高级—SpringBoot+MyBatis+Sharding-JDBC实现MySQL分库分表

一、什么是Sharding-JDBCSharding-JDBC官方文档：https://shardingsphere.apache.org/document/current/cn/overview/Sharding-JDBC定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。适用于任何基于JDBC的ORM框架，如：JPA,Hibernate,Mybatis,SpringJDBCTemplate或直接使用JDBC。支持任何第三方的数据库连接池，如：DBCP,C3P0,BoneCP,Druid,HikariCP等。支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。二、数据库分库分表和读写分离基本概念扫盲通过分库和分表进行数据的拆分来使得各个表的数据量保持在阈值以下（一般来讲，单一数据库实例的数据的阈值在1TB之内，是比较合理的范围），以及对流量进行疏导应对高访问量，是应对高并发和海量数据系统的有效手段。数据分片的拆分方式又分为垂直分片和水平分片。1、垂直拆分按照业务拆分的方式称为垂直分片，又称为纵向拆分，它的核心理念是专库专用。在拆分之前，一个数据库由多个数据表构成，每个表对应着不同的业务。而拆分之后，则是按照业务将表进行归类，分布到不同的数据库中，从而将压力分散至不同的数据库。如下图所示：垂直拆分可以缓解数据量和访问量带来的问题，但无法根治。如果垂直拆分之后，表中的数据量依然超过单节点所能承载的阈值，则需要水平拆分来进一步处理。2、水平拆分水平分片又称为横向拆分。相对于垂直分片，它不再将数据根据业务逻辑分类，而是通过某个字段（或某几个字段），根据某种规则将数据分散至多个库或表中，每个分片仅包含数据的一部分。例如：根据主键分片，偶数主键的记录放入0库（或表），奇数主键的记录放入1库（或表），如下图所示：水平分片从理论上突破了单机数据量处理的瓶颈，并且扩展相对自由，是分库分表的标准解决方案。3、读写分离通过一主多从的配置方式，可以将查询请求均匀的分散到多个数据副本，能够进一步的提升系统的处理能力。使用多主多从的方式，不但能够提升系统的吞吐量，还能够提升系统的可用性，可以达到在任何一个数据库宕机，甚至磁盘物理损坏的情况下仍然不影响系统的正常运行。与将数据根据分片键打散至各个数据节点的水平分片不同，读写分离则是根据SQL语义的分析，将读操作和写操作分别路由至主库与从库。读写分离的数据节点中的数据内容是一致的，而水平分片的每个数据节点的数据内容却并不相同。将水平分片和读写分离联合使用，能够更加有效的提升系统性能。分库分表和读写分离都可以提高系统的性能和稳定性，但是他们都有着同样的问题，使得应用开发和运维人员对数据库的操作和运维变得更加复杂。因此使用sharding-jdbc可以透明化分库分表和读写分离所带来的影响，让我们像使用一个数据库一样使用主从数据库集群三、配置实例接下来，我就参考官方文档，基于SpringBoot2.1.0.RELEASE，与Sharding-JDBC4.0.0-RC1版本，写了一个demo，实现数据分片的基本功能。1、建库、建表这里在同一台MySQL服务器上建立了两个数据库实例：ds0、ds1然后在ds0上创建：t_order0、t_order_item0、t_config然后在ds1上创建：t_order1、t_order_item1、t_config2、创建SpringBoot工程并配置分片创建Maven工程，这里SpringBoot使用的是2.1.13.RELEASE版本（为了与实际工作中的项目一致），ORM使用的MyBatis，Sharding-JDBC使用的是当前最新版本4.0.0-RC1。（1）完整maven依赖（2）实体类ConfigOrderOrderItem（3）MapperConfigMapperOrderItemMapperOrderMapper（4）application.ymlSharding-JDBC提供了4种配置方式（Java类配置、yml配置文件配置、SpringBoot配置和Spring命名空间配置），用于不同的使用场景。通过不同的配置方式，可以灵活的使用分库分表、读写分离以及分库分表+读写分离共用。（5）application-sharding.xml（实现分片配置的重点）（6）自定义分库规则top.easyblog.sharding.demo.hint.HintSharding（7）ServiceOrderServiceImpl3、测试（1）向t_order_item和t_order表中插入数据插入测试的结果：查询测试：（2）查询OrderItem分别从两个不同的数据表中查询到数据后，由sharding-JDBC合并数据之后返回（3）通过UserId查询Order分别从两个不同的数据表中查询到数据后，由sharding-JDBC合并数据之后返回（4）关联查询（join）测试（5）向Config表插入记录并查询Config表没有做分片，保存了所有的数据，但是在查询的时候sharding-jdbc会随机选择一个数据表进行查询，因此下面这两条数据是来着同一个数据表的。

MySQL主从复制原理以及基于Docker容器搭建MySQL主从复制

一、MySQL主从复制复制是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。MySQL支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库,实现链状复制。1、MySQL主从复制的概念MySQL主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。MySQL主从复制可以用来实现读写分离、数据库实时备份、高可用HA以及架构扩展等场景。2、MySQL主从复制的几种形式一主一从一主多从一主一从和一主多从是最常见的主从架构，实施起来简单并且有效，不仅可以实现HA，而且还能读写分离，进而提升集群的并发能力。多主一从多主一从可以将多个mysql数据库备份到一台存储性能比较好的服务器上。双主复制双主复制，也就是互做主从复制，每个master既是master，又是另外一台服务器的slave。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中。级联复制级联复制模式下，部分slave的数据同步不连接主节点，而是连接从节点。因为如果主节点有太多的从节点，就会损耗一部分性能用于replication，那么我们可以让3~5个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。3、MySQL中主从复制原理MySQL的主从复制主要分为三步：master会在事务提交后将数据变化记录到二进制日志文件binlog中。这些记录过程叫做二进制日志事件，binarylogevents，这一过程主要由主节点的logdump线程执行当savle结点上执行startsavle执行之后，slave会创建一个I/O线程连接主节点，请求主库中的二进制日志文件，并拷贝数据到本地的中继日志文件（Relaylog）中。salve结点的SQL线程读取中继日志中记录的事件，并把改变记录到自己的数据库中。MySQL的复制显然是异步并且是串行化的。二、配置MySQL主从复制接下来，我们基于docker搭建一下MySQL的主从复制。由于docker已经是现在运维比较常用的容器化技术了，并且docker的操作也不难，所以我们就用docker来搭建一下MySQL主从复制。1、启动mysql容器执行如下命令，并将容器内的配置文件和数据文件挂载到宿主机上如果在启动容器的时候出现mysqld:Can'treaddirof'/etc/mysql/conf.d/'(Errcode:13-Permissiondenied)错误，那是因为Centos7安全Selinux禁止了一些安全权限，导致mysql和mariadb在进行挂载/var/lib/mysql的时候会提示如下信息此时我们在命令中加上--privileged=true给容器特定的权限启动之后我们看看容器是否运行了我们把mysql-3307作为主节点，把mysql-3308作为从结点，接下来首先配置Mater结点：在宿主机上挂载的目录/docker/mysql/conf下，执行vimmy.cnf新建并添加如下配置：配置完成之后，需要重启mysql服务使配置生效。重启之后下一步在Master数据库创建数据同步用户，授予用户slaveREPLICATIONSLAVE权限和REPLICATIONCLIENT权限，用于在主从库之间同步数据。进入master数据库命令行模式，执行如下命令：2、配置savle结点容器执行命令vim/docker/mysql/conf2/my.cnf新建并配置如下信息：配置完成后也需要重启mysql服务和docker容器，操作和配置Master(主)一致。3、连接主节点和从结点配置完成会后，我们首先在主节点服务器的mysql命令行模式下执行showmasterstatus查看主节点的状态File和Position字段的值后面将会用到，在后面的操作完成之前，需要保证Master库不能做任何操作，否则将会引起状态变化，File和Position字段的值变化。首先我们查看一下主节点的IP：之后在Slave的mysql命令行模式下，执行如下命令changemastertomaster_host='172.17.0.2',master_user='slave',master_password='123456',master_port=3306,master_log_file='mysql-bin.000001',master_log_pos=617,master_connect_retry=30;命令参数说明：master_host：主节点IP，咋docker中可以使用上面给的命令直接查看master_user：用于数据同步的用户master_password：用于同步的用户的密码master_port：Master的端口号，指的是容器的端口号，并不是容器在主机上映射的端口号master_log_file：指定Slave从哪个日志文件开始复制数据，即上文中提到的File字段的值master_log_pos：从日志文件的哪里开始读，即上文中提到的Position字段的值master_connect_retry：如果连接失败，重试的时间间隔，单位是秒，默认是60秒之后我们还是在slave的mysql命令行模式下执行startslave;启动主从复制过程，然后再次查询主从同步状态showslavestatus\G;。判断主从复制有没有启动成功的关键即使看SlaveIORunning和SlaveSQLRunning都是Yes，那就说明主从复制已经开启。此时可以测试数据同步是否成功。4、测试主从复制测试主从复制的方法就比较多了，最简单的方法就是在主库创建一个表，一顿操作之后看从库是否有和主库一样的数据即可。这里不在重复了，比较简单，即使简单的SQL操作。最后贴上最终测试的结果：主库从库总结MySQL主从复制无论是原理还是实际操作还是比较好配置的，主要就是依靠主节点的二进制日志（在MySQL中日志比数据更重要，有了日志就有了一切！！！，实际上MySQL除了本文用到的二进制日志，还提供了很多的日志，有兴趣的小伙伴可以点击我的另一篇博客了解一下MySQL日志分类及简介）将主库的所有操作记录下来，然后从库读取主库的二进制日志文件，并在自己本地重做文件中的操作就完成了复制。

MySQL重做日志（redo log）、回滚日志（undo log）、二进制日志（binglog）......等日志格式的简单总结

在任何系统库中,都会有各种各样的日志,记录着系统工作的方方面面,以帮助系统管理员追踪系统曾经发生过的各种事件。MySQL也不例外,MySQL中有六种日志文件，分别是：重做日志（redolog）、回滚日志（undolog）、二进制日志（binlog）、错误日志（errorlog）、慢查询日志（slowquerylog）、查询日志（generallog），中继日志（relaylog）。可以说，在数据库的世界里，数据从来都不重要，日志才是最重要的，有了日志就有了一切！！！一、错误日志（errorlog）错误日志是MySQL中最重要的日志之一,它记录了当mysqld启动和停止时,以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时,可以首先查看此日志。该日志是默认开启的,默认存放目录为mysql的数据目录(var/lib/mysql),默认的日志文件名为[hostname].err(hostname是主机名)。查看日志位置指令:第一行log_error就是错误日志，这里是采用的是标准输出直接将错误打印在屏幕上。当然也可以配置把错误日志输出到某个文件。1.1错误日志配置为了方便管理，用户可以根据自己的需求来配置错误日志存储位置和日志级别，配置参数如下:log_error=on|文件路径是否启用错误日志,on表示开启,文件路径表示指定自定义日志路径log_warnings=1|0是否记录warnings信息到错误日志中1.2错误日志记录信息1、服务器启动和关闭过程中的信息未必是错误信息，比如mysql是如何去初始化存储引擎的过程记录在错误日志里等等2、服务器运行过程中的错误信息比如sock文件找不到，无法加载mysql数据库的数据文件，如果忘记初始化mysql或datadir路径找不到，或权限不正确等都会记录在此3、事件调度器运行一个事件时产生的信息一旦mysql调度启动一个计划任务的时候，它也会将相关信息记录在错误日志中4、在从服务器上启动从服务器进程时产生的信息在复制环境下，从服务器进程的信息也会被记录进错误日志1.3删除错误日志在mysql5.5.7之前：数据库管理员可以删除很长时间之前的错误日志，以保证mysql服务器上的硬盘空间。mysql数据库中，可以使用mysqladmin命令开启新的错误日志。mysqladmin命令的语法如下：二、二进制日志（binglog）二进制日志(BINLOG)记录了所有的DDL(数据定义语言)语句和DML(数据操纵语言)语句,但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用,MySQL的主从复制，就是通过该binlog实现的。二进制日志,默认情况下是没有开启的,需要到MySQL的配置文件中开启,并配置MySQL日志的格式。Linux下同下MySQL的配置文件默认位置：/usr/my.cnf日志存放位置：配置时,给定了文件名但是没有指定路径,日志默认写入Mysq的数据目录。2.1binlog日志格式binlog格式分为:STATEMENT、ROW和MIXED三种，详情如下:STATEMENTSTATEMENT格式的binlog记录的是数据库上执行的原生SQL语句。这种方式有好处也有坏处。好处就是相当简单，简单地记录和执行这些语句，能够让主备保持同步，在主服务器上执行的SQL语句，在从服务器上执行同样的语句。另一个好处是二进制日志里的时间更加紧凑，所以相对而言，基于语句的复制模式不会使用太多带宽，同时也节约磁盘空间。并且通过mysqlbinlog工具容易读懂其中的内容。坏处就是同一条SQL在主库和从库上执行的时间可能稍微或很大不相同，因此在传输的二进制日志中，除了查询语句，还包括了一些元数据信息，如当前的时间戳。即便如此，还存在着一些无法被正确复制的SQL。另外一个问题就是基于语句的复制必须是串行化的。这要求大量特殊的代码，配置，例如InnoDB的next-key锁等。并不是所有的存储引擎都支持基于语句的复制。ROW从MySQL5.1开始支持基于行的复制，也就是基于数据的复制，基于行的更改。这种方式会将实际数据记录在二进制日志中，它有其自身的一些优点和缺点，最大的好处是可以正确地复制每一行数据。一些语句可以被更加有效地复制，另外就是几乎没有基于行的复制模式无法处理的场景，对于所有的SQL构造、触发器、存储过程等都能正确执行。主要的缺点就是二进制日志可能会很大，而且不直观，所以，你不能使用mysqlbinlog来查看二进制日志。也无法通过看二进制日志判断当前执行到那一条SQL语句了。现在对于ROW格式的二进制日志基本是标配了，主要是因为它的优势远远大于缺点。并且由于ROW格式记录行数据，所以可以基于这种模式做一些DBA工具，比如数据恢复，不同数据库之间数据同步等。MIXEDMIXED是MySQL默认使用的二进制日志记录方式，但MIXED格式默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。比如用到UUID()、USER()、CURRENT_USER()、ROW_COUNT()等无法确定的函数。2.2binlog的查看方式binlog使用直接打开是我们看不懂的一堆“乱码”，我们需要借助工具来分析binlog日志，这个工具即使mysql自带的mysqlbinlog语法：示例：发现虽然不是乱码了，但是日志中的内容把人看的还是云里雾里的。我们还需要添加一个命令参数：--base64-output=decode-rows-v2.3binlog日志文件的删除对于比较繁忙的系统,由于每天生成日志量大,这些日志如果长时间不清楚,将会占用大量的磁盘空间。下面我们将会讲解几种删除日志的常见方法:方式一通过ResetMaster指令删除全部binlog日志,删除之后,日志编号,将从xx.000001重新开始。查询之前,先查询下日志文件:执行删除日志的指令：执行之后在查看日志文件：方式二执行指令purgemasterlogsto'mysqlbin.xxxxxx',该命令将会删除指定日志文件之前的所有日志文件。方式三执行指令purgemasterlogsbefore'yyyy-mm-ddhh24:mi:ss'，该命令将删除日志为“yyyy-mm-ddhh24:mi:ss”之前产生的所有日志文件。方式四在MySQL配置文件中设置参数--expire_logs_days=xxxx，此参数的含义就是设置日志的过期天数，过期之后会自动删除日志。比如设置--expire_logs_days=3;的含义就是日志文件的过期天数是3天。三、查询日志（generallog）查询日志在MySQL中被称为generallog(通用日志)，查询日志里的内容不要被"查询日志"误导，认为里面只存储select语句，其实不然，查询日志里面记录了数据库执行的所有命令，不管语句是否正确，都会被记录。默认情况下，查询日志是没有开启的。如果需要开启查询日志，可以在mysql配置文件中做如下配置：或者也可以使用命令设置：四、慢查询日志（slow_query_log）慢查询会导致CPU，IOPS，内存消耗过高。当数据库遇到性能瓶颈时，大部分时间都是由于慢查询导致的。开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，之后运维人员通过定位分析，能够很好的优化数据库性能。慢查询日志记录的慢查询不仅仅是执行比较慢的SELECT语句，还有INSERT，DELETE，UPDATE，CALL等DML操作，只要超过了指定时间，都可以称为"慢查询"，被记录到慢查询日志中。默认情况下，慢查询日志是不开启的，只有手动开启了，慢查询才会被记录到慢查询日志中。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。4.1开启慢查询日志如永久生效需要修改配置文件my.cnf中[mysqld]下配置4.2慢查询日志分析工具—mysqldumpslow先看一下它的帮助使用示例：五、事务日志（redolog和undolog）数据库数据存放的文件称为datafile；日志文件称为logfile；数据库数据是有缓存的，如果没有缓存，每次都写或者读物理disk，那性能就太低下了。数据库数据的缓存称为databuffer，日志（redo）缓存称为logbuffer；既然数据库数据有缓存，就很难保证缓存数据（脏数据）与磁盘数据的一致性。但是万一数据库发生断电，因为缓存的数据没有写入磁盘，导致缓存在内存中的数据丢失而导致数据不一致怎么办？为了保证事务的ACID特性，就不得不说MySQLInnoDB引擎的事务日志:重做日志redo和回滚日志undoinnodb通过forcelogatcommit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redologfile和undologfile中进行持久化。注:在数据库的世界里，数据从来都不重要，日志才是最重要的，有了日志就有了一切！！！5.1Redo日志Redolog的作用主要是确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redolog进行重做，从而达到事务的持久性这一特性。它是物理格式的日志，记录的是物理数据页面的修改的信息，其redolog是顺序写入redologfile的物理文件中去的。redolog包括两部分：一是内存中的日志缓冲(redologbuffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redologfile)，该部分日志是持久的，并且是事务的记录是顺序追加的，性能非常高(磁盘的顺序写性能逼内存的写性能差不了太多)。InnoDB使用日志来减少提交事务时的开销。因为日志中已经记录了事务，就无须在每个事务提交时把缓冲池的脏块刷新(flush)到磁盘中。事务修改的数据和索引通常会映射到表空间的随机位置，所以刷新这些变更到磁盘需要很多随机IO。InnoDB假设使用常规磁盘，随机IO比顺序IO昂贵得多，因为一个IO请求需要时间把磁头移到正确的位置，然后等待磁盘上读出需要的部分，再转到开始位置。InnoDB用日志把随机IO变成顺序IO。一旦日志安全写到磁盘，事务就持久化了，即使断电了，InnoDB可以通过redo日志恢复已经提交的事务。5.2Undo日志undolog有两个作用：保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。它是一个逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现的，这一点是不同于redolog的在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。undolog和redolog记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undolog中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undolog中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过undolog来实现的：当读取的某一行被其他事务锁定时，它可以从undolog中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

MySQL常用工具

1、mysql（连接数据库的客户端工具）这里的mysql不是指mysql服务，也不是指mysql数据库，而是只连接数据库的客户端工具。语法：options可选参数：执行选项：可以在Shell脚本中使用，去执行SQL语句而不用登录到MySQL服务器上：2、mysqladmin（客户端管理工具）mysqladmin是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前的状态、创建并删除数据库等。它的功能和mysql客户端类似，主要区别在于它更侧重于一些管理方面的功能，比如关闭数据库：mysqladmin-uroot-pshutdown3、mysqlbinlog（日志管理工具）由于服务器生成的二进制文件以二进制格式保存，所以如果要检查这些文件的文本格式，就会用到mysqlbinlog日志管理工具。语法：4、mysqldump（逻辑备份工具，MySQL自带的）MySQL最经典的逻辑备份工具,也是MySQL工具集里默认的工具，适用于一些数据量不大的数据备份工作。语法：options可选参数输出内容可选参数4、mysqldumper（逻辑备份工具，需要安装）MySQL自身的mysqldump工具支持单线程工作，依次一个个导出多个表，没有一个并行的机，这就使得它无法迅速的备份数据。mydumper作为一个实用工具，能够良好支持多线程工作，可以并行的多线程的从表中读入数据并同时写到不同的文件里，这使得它在处理速度方面快于传统的mysqldump。其特征之一是在处理过程中需要对列表加以锁定，因此如果我们需要在工作时段执行备份工作，那么会引起DML阻塞。但一般现在的MySQL都有主从，备份也大部分在从上进行，所以锁的问题可以不用考虑。这样，mydumper能更好的完成备份任务下载安装5、mysqlimportmysqlimport是客户端数据导入工具,用来导入mysqldump加-T参数后导出的文本文件。语法:示例:如果需要导入sq|文件,可以使用mysq|中的source指令:6、mysqlshowmysqlshow是客户端对象查找工具,用来很快地查找存在哪些数据库、数据库中的表、表中的列或者索引。语法:参数:示例:

MySQL高级—应用优化（参数优化）

一、MySQL查询缓存优化在MySQL8.0之前我们的数据库是存在缓存这样的情况的，因为存在缓存，sql怎么执行都是很快，当然第⼀次其实不快但是我没注意到，以⾄于上线后因为缓存经常失效，导致rt（Responsetime）时高时低。后来就发现了是缓存的问题，我们在执⾏SQL的时候，记得加上SQLNoCache去跑SQL，这样跑出来的时间就是真实的查询时间了。我说一下为什么缓存会失效，而且是经常失效。如果我们当前的MySQL版本支持缓存并且我们也开启了缓存，那每次请求的查询语句和结果都会以key-value的形式缓存在内存中的，大家也看到我们下方的结构图了，每个请求会先去看缓存是否存在，不存在才会走解析器。缓存失效比较频繁的原因就是，只要我们一对表进行更新，那这个表所有的缓存都会被清空，其实我们很少存在不更新的表，特别是我之前的电商场景，可能静态表可以用到缓存，但是我们都走大数据离线分析，缓存也就没用了。大家如果是8.0以上的版本就不用担心这个问题，如果是8.0之下的版本，记得排除缓存的干扰。下面我们就来了解一下再8.0版本下如何优化SQL查询缓存。1、概述开启MySQL的查询缓存,当执行完全相同的SQL语句的时候,服务器就会直接从缓存中读取结果,当数据被修改,之前的缓存会失效,修改比较频繁的表不适合做查询缓存。2、操作流程一条SQL语句的执行流程（下面的流程是在MySQL开启了查询缓存下的情景）：（1）MySQL客户端和MySQL服务器建立连接，客户端发来一条SQL语句；（2）MySQL服务器收到SQL语句之后，首先去查询缓存中寻找有没有目标数据，如果命中就直接返回，否则把SQL语句叫给SQL解析器解析；（3）SQL解析器把SQL语句按照MySQL的语法规范解析成一个语法树，然后交给预处理器进一步检查最终生成一颗解析树。（4）查询优化器拿到解析树后生成执行计划化。通常一条查询语句可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。（5）完成语法解析和查询优化之后，查询执行引擎会将根据执行计划到存储引擎中获取数据。获取到数据后返回给客户端，并在查询缓存中缓存一份同样的数据。3、查询缓存配置（1）查看当前的MySQL数据库是否支持查询缓存:（2）查看当前MySQL是否开启了查询缓存:（3）查看查询缓存的大小（4）查询缓存的状态变量4、开启查询缓存MvSOL的查询缓存默认是关闭的,需要手动配置参数aquervcachetvpe，来开启查询缓存、auervcachetvpe该参数的可取值有三个:值含义OFF或0查询缓存功能关闭ON或1查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定SQLNO_CACHE，不予缓存DEMAND或2查询缓存功能按需进行,显式指定SQLCACHE的SELECT语句才会缓存;其它均不予缓存_在/usr/my.cnf配置中,增加以下配置:配置完毕之后,重启服务既可生效;然后就可以在命令行执行SQL语句进行验证,执行条比较耗时的SQL语句,然后再多执行几次,查看后面几次的执行时间;获取通过查看查询缓存的缓存命中数,来判定是否走查询缓存。5、查询缓存SELECT选项可以在SELECT语句中指定两个与查询缓存相关的选项:SQLCACHE:如果查询结果是可缓存的,并且query_cache_type系统变量的值为ON或DEMAND,则缓存查询结果。SQLNO_CACHE:服务器不使用查询缓存。它既不检查查询缓存,也不检查结果是否已缓存,也不缓存查询结果。例子:6、查询缓存失效的情况（1）SQL语句不一致的情况，要想命中查询缓存,查询的SQL语句必须-致。（2）当查询语句中有一些不确定的时,则不会缓存。如:now),currentdate(),curdate(),curtime(),rand(),uuid(),user(),database()。（3）不使用任何表查询语句。（4）查询mysql，information_schema或performance_schema数据库中的表时,不会走查询缓存。（5）在存储的函数,触发器或事件的主体内执行的查询。（6）如果表更改,则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。这包括使用MERGE映射到已更改表的表的查询。一个表可以被许多类型的语句,如被改变INSERT，UPDATE，DELETE，TRUNCATETABLE,ALTERTABLE,DROPTABLE,或DROPDATABASE二、Mysql内存管理及优化1、内存优化原则（1）将尽量多的内存分配给MySQL做缓存,但要给操作系统和其他程序预留足够内存。（2）MyISAM存储弓|擎的数据文件读取依赖于操作系统自身的I0缓存,因此,如果有MyISAM表,就要预留更多的内存给操作系统做1I0缓存。（3）排序区、连接区等缓存是分配给每个数据库会话(session)专用的,其默认值的设置要根据最大连接数合理分配,如果设置太大,不但浪费资源,而且在并发连接较高时会导致物理内存耗尽。2、MyISAM内存优化MyISAM存储引擎使用key_buffer缓存索引块,加速MyISAM索弓|的读写速度。对于MyISAM表的数据块,mysql没有特别的缓存机制,完全依赖于操作系统的I0缓存。key.buffer.sizekey_buffer_size决定MylSAM索引块缓存区的大小,直接影响到MyISAM表的存取效率。可以在MySQL参数文件中设置key_buffer_size的值,对于一般MyISAM数据库,建议至少将1/4可用内存分配给key_buffer_size。在/usr/my.cnf中做如下配置:read_buffer_size如果需要经常顺序扫描myisam表,可以通过增大read_buffer_size的值来改善性能。但需要注意的是read_buffer_size是每个session独占的，如果默认值设置太大,就会造成内存浪费。read_rnd_buffer_size对于需要做排序的myisam表的查询,如带有orderby子句的sql,适当增加read_rnd_buffer_size的值,可以改善此类的sql性能。但需要注意的是read_rnd_buffer_size每个session独占的,如果默认值设置太大,就会造成内存浪费。3、InnoDB内存优化innodb用一块内存区做IO缓存池,该缓存池不仅用来缓存innodb的索引块,而且也用来缓存innodb的数据块。innodb_buffer_pool_size该变量决定了innodb存储引擎表数据和索弓|数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下,innodb_buffer_pool_size的值越大,缓存命中率越高,访问InnoDB表需要的磁盘I/O就越少,性能也就高。1innodb_buffer_pool_size=512Minnodb._log_buffer.size决定了innodb重做日志缓存的大小,对于可能产生大量更新记录的大事務,增加innodb_log_buffer_size的大小,可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。三、Mysql并发参数调整从实现上来说,MySQLServer是多线程结构,包括后台线程和客户服务线程。多线程可以有效利用服务器资源,提高数据库的并发性能。在Mysql中,控制并发连接和线程的主要参数包括maxconnections、backlog、thread.cachesize、table_opencahce。1、max_connections采用max_connections控制允许连接到MySQL数据库的最大数量，默认值是151。如果状态变量connection_errors.maxconnections不为零,并且一直增长,则说明不断有连接请求因数据库连接数已达到允许最大值而失败,这是可以考虑增大max_connections的值。Mysql最大可支持的连接数,取决于很多因素,包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度,期望的响应时间等。在Linux平台下,性能好的服务器,支持500-1000个连接不是难事,需要根据服务器性能进行评估设定。2、back_logback_log参数控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时,新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源,将会报错。5.6.6版本之前默认值为50，之后的版本默认为50+(max_connections/5)，但最大不超过900。如果需要数据库在较短的时间内处理大量连接请求，可以考虑适当增大back.log的值。3、table_open_cache该参数用来控制所有SQL语句执行线程可打开表缓存的数量,而在执行SQL语句时,每-一个SQL执行线程至少要打开1个表缓存。该参数的值应该根据设置的最大连接数max_connections以及每个连接执行关联查询中涉及的表的最大数量来设定:max_connectionsxN4、thread_cache_size为了加快连接数据库的速度,MySQL会缓存一定数量的客户服务线程以备重用,通过参数thread_cache_size可控制MySQL缓存客户服务线程的数量。5、innodb_lock_wait_timeout该参数是用来设置InnoDB事务等待行锁的时间,默认值是50ms，可以根据需要进行动态设置。对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小,以避免事务长时间挂起;对于后台运行的批量处理程序来说，可以将行锁的等待时间调大，以避免发生大的回滚操作。

MySQL高级—SQL优化的常见手段（SQL执行频率、慢查询日志、Explain等）

一、优化SQL步骤在应用的的开发过程中,由于初期数据量小,开发人员写SQL语句时更重视功能上的实现,但是当应用系统正式上线后,随着生产数据量的急剧增长,很多SQL语句开始逐渐显露出性能问题,对生产的影响也越来越大,此时这些有问题的SQL语句就成为整个系统性能的瓶颈,因此我们必须要对它们进行优化,本章将详细介绍在MySQL中优化SQL语句的方法。当面对一个有SQL性能问题的数据库时,我们应该从何处入手来进行系统的分析,使得能够尽快定位问题SQL并尽快解决问题。1、查看SQL执行频率MySQL客户端连接成功后,通过show[session|global]status命令可以提供服务器状态信息。show[session|global]status可以根据需要加上参数"session”或者"global"来显示session级(当前连接)的计结果和global级(自数据库上次启动至今)的统计结果。如果不写,默认使用参数是"session"，即当前会话的sql执行频率。下面的命令显示了当前session中所有统计参数的值:针对InnoDB引擎数据表的操作频率记录：Com_xxx表示每个xxx语句的执行次数，我们通常关心一下几个参数：参数含义Com_select执行insert操作的次数，批量插入的insert操作只累加一次Com_insert执行select操作的次数，一次查询只累加1Com_update执行delete操作次数Com_delete执行update操作次数，提交和回滚均会累加Innodb_rows_readselect查询返回的函数Innodb_rows_inserted执行insert操作插入的行数Innodb_rows_deleted执行delete操作删除的行数Innodb_rows_updatedupdate操作更新的函数2、定位低效率执行SQL可以通过以下两种方式定位执行效率较低的SQL语句。2.1慢查询日志通过慢查询日志定位那些执行效率较低的SQL语句,用slow_query_log_file=路径，选项启动时,mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。如果你接手了一个项目，跑得很慢，想找到执行比较慢的语句，不仅仅是select语句这时候mysql的慢查询日志可能会对你有很大帮助。数据库默认情况下是不会开启慢查询日志记录的。在配置文件my.cnf或者mariaDB的my.ini或者命令行：执行上面命令之后就可以查询到默认的慢查询时间，也就是sql语句超时的时间，mysql默认是10秒太长，设置成1秒这种写法只对当前数据库链接有效，链接资源销毁即失效，如果需要一直有效的话，需要更改数据库配置文件，找到long_query_time这个参数改成相应的秒数就行慢查询日志文件慢查询超时后，mysql默认是不会记录到日志文件的，也是需要手动开启（1）方法一：在命令行使用命令开启慢查询日志此方法适用于在MySql8.0以上版本使用，直接在命令行输入命令就可以了上面的路径一旦设置好不要轻易修改（2）方法二：在MySQL配合文件中配置相关参数在mysql配置配置文件中直接配置如下信息：2.2showprocesslist慢查询曰志在查询结束以后才纪录,所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题,可以使用showprocesslist命令查看当前MySQL在进行的线程,包括线程的状态、是否锁表等,可以实时地查看SQL的执行情况,同时对一-些锁表操作进行优化。3、explain分析SQL执行计划—重要（1）什么是Explain?使用explain关键字可以模拟SQL优化器执行SQL语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。用法：explain执行后返回的信息表中各个字段的含义：字段含义id查询中执行select子句或操作表的顺序，数值越大优先级越高select_type表示SELECT的类型,常见的取值有SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一一个SELECT)等table这个数据是基于哪张表的。type查询的访问类型。是较为重要的一个指标，下面会详细说到。possible_key表示查询时可能使用的索引，不表示出现在这里的就是表的全部索引。key实际使用的索引。如果为NULL，则没有使用索引。key_len表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len字段能够帮你检查是否充分的利用上了索引。ken_len越长，说明索引使用的越充分。ref显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。rowsrows列显示MySQL认为它执行查询时必须检查的行数。越少越好！Extra其他的额外重要的信息。接下来我们来详细了解一下各个字段的含义。（2）环境准备（3）explain之idselect查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序。①id相同，执行顺序由上至下②id不同，如果有子查询，id的序号会递增，id值越大优先级越高，越先被执行③有相同也有不同（4）explain之select_typeselect_type代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。select_type属性含义SIMPLE简单的select查询，查询中不包含任何子查询或UNIONPRIMARY查询中若包含任何复杂的子部分，最外层主查询则被标记为PrimaryDERIVED在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询,把结果放在临时表里。SUBQUERY在SELECT或WHERE列表中包含了子查询DEPEDENTSUBQUERY在SELECT或WHERE列表中包含了子查询,子查询基于外层UNCACHEABLESUBQUERY无法使用缓存的子查询UNION若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVEDUNIONRESULT从UNION表获取结果的SELECT这几种查询类型的效率，从上到下越来越差。SIMPLESIMPLE简单理解就是单表查询PRIMARY查询中若包含任何复杂的子部分，最外层查询则被标记为Primary。SUBQUEUERY在SELECT或WHERE列表中包含了子查询。DERIVED在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表里UNION若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED。UNIONRESULT从UNION表获取结果的SELECT。（5）explain之typetype是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL，一般来说，得保证查询至少达到range级别，最好能达到ref。system表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计const表示通过索引一次就找到了,const用于比较主键或索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量。eq_ref唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。ref非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。创建索引之前：创建索引之后：ref_or_null对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。index_merge在查询过程中需要多个索引组合使用，通常出现在有or的关键字的sql中。unique_subquery该联接类型类似于index_subquery。子查询中的唯一索引。index_subquery利用索引来关联子查询，不再全表扫描。range只检索给定范围的行,使用一个索引来选择行。key属性列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。index出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组。ALL遍历全表以找到匹配的行，效率最低。（6）explain之possible_keys、key、key_lenpossible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用，如果没有就是NULL。key：使用到的索引，如果没用索引就是NULL。key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len字段能够帮你检查是否充分的利用上了索引。ken_len越长，说明索引使用的越充分。（7）explain之ref显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值（8）explain之Extra其他的额外重要的信息。常见的信息有以下几种：信息含义Usingfilesort说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。Usingtemporary使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序orderby和分组查询groupby。UsingindexUsingindex代表表示相应的select操作中使用了覆盖索引(CoveringIndex)，避免访问了表的数据行，效率不错！如果同时出现usingwhere，表明索引被用来执行索引键值的查找;如果没有同时出现usingwhere，表明索引只是用来读取数据而非利用索引执行查找。Usingwhere表明使用了where过滤。Usingjoinbuffer使用了连接缓存。impossiblewherewhere子句的值总是false，不能用来获取任何元组。selecttablesoptimizedaway在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。4、showprofile分析执行SQL花费的时间有时候光靠explain分析执行计划并不能准确的定位sql问题，好在MySQL从5.0.37版本开始增加了对showprofiles和showprofile的语句支持。通过have_profiling参数，可以看到sql是否支持profile：默认profiling是关闭的，可以通过set语句在session级别开启profiling:如果profiling没有开启，可以使用setprofiling=1开启通过profiling，我们可以更清楚的了解SQL语句的执行过程。之后我们就可以使用showprofiles来分析每个SQL语句执行花费的时间：定位到怀疑有问题的SQL语句之后，可以在使用showprofileforqueryQuery_Id来查看一个SQL语句执行的各个过程中花费的时间：另外，我们还可以使用showprofileallforqueryQuery_Id来查看SQL语句执行过程中的全部信息。总之，通过showprofile这一系类命令可以在sql优化的时候告诉我们时间主要浪费在哪些地方了。5、通过trace分析优化器如何选择执行计划mysql5.6是通过trace文件进一步告诉我们优化器是如何选择执行计划的。（即mysql5.6提供了对sql的跟踪trace文件，从而得知优化器为何选择a执行计划而不选择b执行计划，有助于我们理解优化器的行为。）首先打开trace,设置格式为json,设置trace最大能够使用内存的大小，避免解析过程中因为默认内存过小不能完全显示：然后执行一下想做追踪的语句，之后使用如下命令就可以查看语句执行的详细信息了。至此，通过上面几部就可以定位大部分SQL执行缓慢的问题了，找到问题之后就可以对症下药。其中使用explain分析SQL执行计划是非常重要的一个环节。二、SQL优化的些许经验SQL优化主要需要借助explain工具进行，总的来说，通常有以下几种优化原则:让主要查询语句使用到合适的索引,type出现ALL(全表扫描)需格外注意,同时建立合适的索引以减少possible_keys的数量。type最好能达到ref级别。Extra列出现Usingtemporary、Usingfilesort（文件排序）务必去除。（1）对orderby/groupby后面的字段建立索引当我们使用orderby将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序，Usingfilesort（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），这个操作是很影响性能的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过大或者数据量过多都会降低效率），更无论读到内存之后的排序了。但是如果我们对该字段建立索引，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。（从磁盘取数据是最影响性能的）。groupby同理。因此对于orderby后面的字段我们可以适当的建立索引。（2）尽量少使用子查询，而是使用join替代对join语句匹配关系（on）涉及的字段建立索引能够提高效率。（3）索引覆盖如果要查询的字段都建立过索引，那么引擎会直接在索引表中查询而不会访问原始数据（否则只要有一个字段没有建立索引就会做全表扫描），这叫索引覆盖。因此我们需要尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。这里值得注意的是不要想着为每个字段建立索引，因为优先使用索引的优势就在于其体积小。（4）like查询，不能以通配符开头比如下面这种写法：这种SQL语句是没办法使用索引的（like语句匹配表达式以通配符开头），只能做全表扫描，效率极低，在实际工程中几乎不被采用。而一般会使用第三方提供的支持中文的全文索引来做。但是下面这种写法是可以的：（5）使用复合索引尽量对多个字段创建复合索引，而不是创建多个单独索引（6）OR优化对于包含OR的查询子句,如果要利用索引,则OR之间的每个条件列都必须用到索引，而且不能使用到复合索引;如果没有索引,则应该考虑增加索引。（7）limit查询优化我们平常使用分页语句是这么写的：这么写需要对前1000500个记录排序，但是仅仅返回500条数据，这样做的代价太大了，因此我们可以使用索引天生是排序的特性来优化SQL语句。优化思路一在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容。虽然有一个嵌套查询，并且还是使用了limit，但是那是基于主键id的，主键id一般都是由索引的，因此这一块走索引会快很多。优化思路二该访案适用于主键自增的表,可以把limit查询转换成某个位置的查询。这个方法对于主键ID是连续的表很有用，但是如果表的ID不连续或无序那还是用第一种方法吧。

MySQL高级—谈谈MySQL的存储引擎

一、MySQL存储引用概述​和大多数的数据库不同，MySQL中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎。​存储引擎就是存储数据、建立索引，更新阐述数据等等技术的实现方式。MySQL存储引擎是基于表的而不是基于数据库的，即在一个数据库中的不同表可以使用不同的存储引擎。​Oracle、SqlServer等数据库只支持一种存储引擎。MySQL提供了插件式的引擎结构。所以MySQL有很多存储引擎，并且可以根据需求使用不同的存储引擎，甚至可以自定义编写存储引擎。​MySQL5.0支持的存储引擎包括：InnnoDB、MyISAM、MEMORY、BDB、MERGE、EXAMPLR、NDBCLUSTER、ARCHIVE、CSV等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。在mysql命令行，可以使用showengines;命令查看当前数据库支持的存储引擎，如下图所示：从表中可以看到，我当前使用的数据库版本支持：MEMORY、InnoDB、PERFORMANCESCHEMA、MyIsAM、MRG_MYISAM、BLACKHOLE、CSV、以及ARCHIVE，其中InnoDB是默认引擎，也即在创建数据表的时候如果没有指定存储引擎，就会默认使用InnoDB作为存储引擎。当然我们还可以使用命令查看当前MySQL数据库默认的存储引擎：二、各种存储引擎的特性下面重点介绍几种常用的存储引擎，并比较各个存储引擎之间的区别，如下表所示特点InnoDBMyISAMMEMORYMERGENDB存储限制64TB256TBRAM大小事务安全支持锁机制行锁(适合高并发)表锁表锁表锁行锁B树索引支持支持支持支持支持哈希索引不支持支持全文索引支持支持集群索引支持支持支持数据索引支持支持支持支持支持索引缓存支持空间使用高低低低内存使用高低中等低高批量插入使用低支持外键支持对于我们来说，我们需要种地那关注的存储引擎特性是是否支持事物、锁机制以及是否支持外键。接下来我们详细的聊一下InnoDB、MyISAM，以及MEMORY这三种存储引擎的特性。1、InnoDB存储引擎的特性InnoDB是一种通用存储引擎，可兼顾高可靠性和高性能。在MySQL5.7中，InnoDB是默认的MySQL存储引擎。除非你配置了其他存储引擎，否则发出不带ENGINE=子句的CREATETABLE语句将创建一个InnoDB表。InnoDB的优势（1）支持事物：其DML操作遵循ACID模型，并具有具有提交，回滚和崩溃恢复功能的事务，以保护用户数据。（2）支持行级锁，可以支持更大的并发：行级锁定可提高多用户并发性和性能。（3）InnoDB表将数据存储在磁盘上，以基于主键优化查询。每个InnoDB表都有一个称为聚集索引的主键索引，该索引组织数据以最小化主键查找的I/O。（4）支持外键，保证数据完整性：为了保持数据完整性，InnoDB支持FOREIGNKEY约束。使用外键检查插入，更新和删除，以确保它们不会导致不同表之间的不一致。InnoDB和ACID模型ACID模型是一组数据库设计原则，强调对业务数据和任务关键型应用程序非常重要的可靠性方面。MySQL包含了与ACID模型紧密结合的InnoDB存储引擎等组件，这样数据就不会被破坏，结果也不会被软件崩溃和硬件故障等异常情况所扭曲。当依赖于符合ACID的特性时，不需要重新发明一致性检查和崩溃恢复机制。如果有额外的软件保护措施、超可靠的硬件或能够容忍少量数据丢失或不一致的应用程序，则可以调整MySQL设置，以牺牲部分ACID可靠性，从而获得更高的性能或吞吐量。ACID描述的是一个事务所应该具有的4大特性，即：A（Atomicity，原子性）：一个事务就是一个不可在分隔的最小工作单元，事务中的操作要么全部执行，要么全部不执行。C（Consistency，一致性）：一致性是指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏I（Isolation，隔离性）：一个事务在做做的修改提交之前，对于其他事务是不可见的。D（Durability，持久性）：一旦事务提交，则器所做的修改就会永久的保存到数据库中。数据库中隔离性还有其对应的不同等级的隔离级别：READUNCOMMITED读未提交：在此级别里，事务的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，也就是会产生脏读，在实际应用中一般很少使用。READCOMMITED读以提交：大多数数据库系统的默认隔离级别都是它，但是MySQL不是。它能够避免脏读问题，但是在一个事务里对同一条数据的多次查询可能会得到不同的结果，也就是会产生不可重复读问题。REPEATABLEREAD可重复读：MYSQLInnoDB存储引擎的默认隔离级别。它能够防止不可重复读问题，但是在一个事务里对一段数据的多次读取可能会导致不同的结果，也就是会有幻读的问题。SERIALIZABLE可串行化：该隔离级别是级别最高的，它通过锁来强制事务串行执行，避免了前面说的所有问题。在高并发下，可能导致大量的超时和锁争用问题。实际应用中也很少用到这个隔离级别，因为可重复度级别解决了所有问题。几种隔离级别分别可以解决的问题：InnoDB的外键约束CASCADE、SETNULL、RESTRICT、NOACTION首先我们来了解一下几个外键约束属性的作用：CASCADE：在父表上update/delete记录时，同步update/delete掉子表的匹配记录SETNULL：在父表上update/delete记录时，将子表上匹配记录的列设为null(要注意子表的外键列不能为notnull)NOACTION：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作RESTRICT：同noaction,都是立即检查外键约束外键约束属性：RESTRICT|CASCADE|SETNULL|NOACTION外键的使用需要满足下列的条件：两张表必须都是InnoDB表，并且它们没有临时表。建立外键关系的对应列必须具有相似的InnoDB内部数据类型。建立外键关系的对应列必须建立了索引。假如显式的给出了CONSTRAINTsymbol，那symbol在数据库中必须是唯一的。假如没有显式的给出，InnoDB会自动的创建。InnoDB的文件存储方式InnoDB存储表数据和索引有以下两种方式：使用共享表空间存储，这种方式下表结构存储在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_data_file_path定义的.ibdata文件中，可以是多个文件。使用多表空间存储，这种方式在表结构仍然存储在.frm文件中，但是每个表的数据和索引单独保存在.ibd文件中。在Linux系统中，MySQL的这些文件被默认保存在/var/lib/mysql目录下了，如下图所示：2、MyISAM存储引擎的特性MyISAM是MySQL5.5版本之前的默认存储引擎，它不支持事务、不支持行级锁，只支持并发插入的表锁，主要用于高负载的select。（1）不支持事务：因此不适合数据安全非常重要的场合下使用。（2）不支持行级锁，最小的锁粒度是表锁，但是它就有较高的插入和查询速度，比较适合查询比较多的场合下使用。（3）每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快MyISAM的文件存储方式每个MyISAM表在磁盘上都会保存是3个文件，文件名和表名相同，单文件扩展名分别是：.frm：用于存储表结构的定义.MYD：用于存放数据.MYI：用于存放索引，仅保存记录所在页的指针，索引的结构是B+树结构。同时，MyISAM类型的表支持三种不同的存储结构：静态型、动态型、压缩型。（1）静态固定长度表这种方式的优点在于存储速度非常快，容易发生缓存，而且表发生损坏后也容易修复。缺点是占空间。这也是默认的存储格式。（2）动态可变长表优点是节省空间，但是一旦出错恢复起来比较麻烦。（3）压缩表上面说到支持数据压缩，说明肯定也支持这个格式。在数据文件发生错误时候，可以使用checktable工具来检查，而且还可以使用repairtable工具来恢复。3、MEMORY存储引擎的特性MEMORY存储引擎相比前面的一些存储引擎，有点不一样，其使用存储在内从中的数据来创建表，而且所有的数据也都存储在内存中。每个基于MEMORY存储引擎的表实际对应一个磁盘文件，该文件的文件名和表名是相同的，类型为.frm。该文件只存储表的结构，而其数据文件，都是存储在内存中，这样有利于对数据的快速处理，提高整个表的处理能力。MEMORY存储引擎默认使用哈希（HASH）索引，其速度比使用BTree要快，如果希望使用B树型，则在创建的时候可以指定使用。MEMORY存储引擎文件数据都存储在内存中，如果mysqld进程发生异常，重启或关闭机器这些数据都会消失。所以MEMORY存储引擎中的表的生命周期很短，一般只使用一次。因此MEMORY表可以用于存储临时表数据。三、InnoDB、MyISAM和MEMORY三种存储引擎比较InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果

MySQL索引以及索引优化分析

一、索引的概念1、什么是索引？MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。从官方的定义可以得到索引的本质：索引是数据结构，可以简单理解为索引就是一种数据结构，它可以帮助我们快速的从数据库查询到数据。索引就类似于一本书的目录，通过目录可以快速找到需要查找对内容。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。2、索引的优缺点分析？优势提高查询效率：提高对数据的检索效率，降低数据库的IO成本。天生排序：通过索引对数据进行排序，降低数据的排序成本，从而降低了CPU的消耗。劣势降低更新表的速度：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。因此，如果你的表INSERT/UPDATE/DELETE操作比较多的话还是不建议使用索引，这样反而会降低数据库的速度。占用额外的空间：实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。二、索引的数据结构索引是在MySQL的存储引擎层实现的，所以每种存储引擎支持的缩影都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型，MySQL目前提供了以下4中数据类型的索引：B+树索引：最常见的索引类型，大部分存储引擎都支持BTREE索引。Hash索引：只有Memory引擎支持，使用场景比较简单。R-tree索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，通常使用较少。Full-text索引（全文索引）：全文索引也是MyISAM支持的一种特殊索引类型，主要用于全文索引，InnoDB从MySQL5.6开始也支持全文索引。InnoDB、MyISAM、Memory三种常用存储引擎对各种索引的支持索引InnoDBMyISAMMemoryB+树索引支持支持支持Hash索引不支持不支持支持R-tree索引不支持支持不支持Full-tree全文索引5.6以后支持支持不支持我们平常所说的索引，没有特殊说明，都是指**B+树（多路搜索树）**结构组织组织的索引。下面我们就来详细了解一下B树索引。这里经常可能会有面试问B+树和Hash索引的区别，这里总结一下。Hash索引的底层数据结构就是hash表，它适用于等值查询，不适用于范围查询；Hash索引没有办法实现排序，但是B+树索引就有这方面的优点；Hash索引不支持多列复合索引的最左前缀规则，如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题1、B树结构B树又叫多路平衡搜索树，一颗m叉的B树具有如下特性：每个节点最多可以存储m-1个关键字。根节点最少要有1个关键字；非根节点至少有m/2个关键字。每个节点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同。每个非叶子节点有n个key与n+1个指针组成，其中m/2向上取整-1<=n<=m-1。以5叉B树为例，key的个数范围就是：2<=n<=4。当n>4的时候，中间节点分裂成父节点，两边结点做子节点。这里我们以插入CNGAHEKQMFWLTZDPRXYS数据为例。插入过程分析B树的插入，我们只需要记住一个规则：判断当前结点key的个数是否小于等于m-1，如果满足，直接插入即可，如果不满足，将节点的中间的key将这个节点分为左右两部分，中间的节点放到父节点中即可。并且在插入中是根据关键字大小来决定一个关键字位置的。（1）插入前4个字母：CNGA，结点key的个数<=4，key只需要按字典序排好位置就可以了（2）插入H，此时n>4了，中间的结点G应该分裂成为父节点，其余结点按大小分裂到两边做子节点（3）插入EKQ不需要分裂（4）插入M，中间元素M分裂到父节点（5）插入FWLT不需要分裂（6）插入Z，中间节点T分裂到父节点（7）插入D时，中间节点D分裂到父节点，之后接着插入PRXY都不会导致节点分裂（8）最后插入S，NPQR节点个数>4了，中间接单Q向上分裂，但是分裂后父节点的关键字个数也大于4了，此时父节点中间元素M再次分裂至此，B树的构建就完成了，B树相对于二叉树来说，B树的搜索效率更高，因为兑入同等两级的数量，B树的层数更少，因此比较的次数就越少。B树的查找过程分析下面展示的是一个3层B树的结构图，实例图说明:每个节点占用一个磁盘块，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例。关键字为16和34,P1指针指向的子树的数据范围为小于16,P2指针指向的子树的数据范围为16~34，P3指针指向的子树的数据范围为大于34。比如要找75这个数据块，那么查找关键字的过程如下：（1）根据根节点找到磁盘块1，读入内存。【第一次I/O】（2）比较关键字75在大于34，找到磁盘块的1的指针p3。（3）根据p3指针找到磁盘块4，读入内存。【第二次I/O】（4）比较关键字75在64~88之间，找到磁盘块4的指针p2。（5）根据p2指针找到磁盘块10，读入内存。【第三次I/O】（6）比较关键字，在磁盘块10找到了关键字75简单分析一下B树的性能：我们都知道计算机在存储数据的时候，有最小存储单元，这就好比我们今天进行现金的流通最小单位是一毛。在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是512字节，而文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是4k，而对于我们的InnoDB存储引擎也有自己的最小储存单元——页（Page），一个页的大小是16K。磁盘扇区、文件系统、InnoDB存储引擎都有各自的最小存储单元。在MySQL中我们的InnoDB页的大小默认是16k，当然也可以通过参数设置：现在假设每个磁盘块中只有data占用空间，并且一条行记录占用1k的空间，那么一个磁盘块中就可以存储16条数据，那么3层B树理论上就可以存储：16*16*16=4096条记录。当数据继续增加的时候，B树的层数还会增加，层数增加那就意味则I\O次数增加，那效率就慢下来了。为了解决这个问题，B+树就应运而生了，简单来说就是，把在B树中每个结点存储的data去掉，只存储指针，这样一来每个磁盘块就可以存储更多的指针了。2、B+树结构B+树是B树的变种，他与B树的区别如下：B+树有两种类型的节点：内部结点（也称索引结点）和叶子结点。内部节点就是非叶子节点，内部节点不存储数据，只存储索引（本质是指针），数据都存储在叶子节点。内部结点中的key也按照从小到大的顺序排列，对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列。每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序链接。父节点存有右孩子的第一个元素的索引。下图所示就是一个B+树结构，上面两层节点都不存数据的，只是一个索引，只在最后一层叶子节点存数据。B+树性能分析：一棵3层的B+树可以存放多少行数据？上文分析B树的时候我们已经说明InnoDB单个叶子节点（页）中的记录数=16K/1K=16。（这里假设一行记录的数据大小为1k，实际上现在很多互联网业务数据记录大小通常就是1K左右）。那么现在我们需要计算出非叶子节点能存放多少指针？其实这也很好算，我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。那么现在我们就可计算出3层B+树可以存放：1170*1170*16=21902400条这样的记录。所以在InnoDB中B+树高度一般为3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。通过上面的分析我们就可以明白一个道理，为什么要求MySQL的主键应该尽量的小，因为更小的主键可以允许InnoDB一页可以存储更多的索引，这也就可以存储更多的记录。思考：为什么B+树更适合实际操作系统的文件索引或数据库的索引?现在这个问题的复杂版本可以参考本文；他的简单版本回答是：（1）B+树的磁盘读写代价更低。B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。（2）B+树的查询效率更稳定。由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当3、哈希索引除了B+树之外，还有一种常见的是哈希索引。哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。本质上就是把键值换算成新的哈希值，根据这个哈希值来定位。看起来哈希索引很牛逼啊，但其实哈希索引有好几个局限(根据他本质的原理可得)：哈希索引也没办法利用索引完成排序不支持最左匹配原则在有大量重复键值情况下，哈希索引的效率也是极低的---->哈希碰撞问题。不支持范围查询三、MySQL的索引分类索引从数据存储方式上说，分成两类：聚簇索引和非聚簇索引从功能上说分为6种：单值索引、唯一索引、主键索引、复合索引、外键索引和全文索引1、聚簇索引和非聚簇索引（1）聚簇索引聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。聚簇索引的优缺点优点：　　　　1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快　　　　2.聚簇索引对于主键的排序查找和范围查找速度非常快缺点：　　　　1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键　　　　2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。　　　　3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。（2）非聚簇索引非聚簇索引就是以非主键构建的B+树，非聚簇索引访问数据总是需要二次查找。非聚簇索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的PageDirectory找到数据行。　　Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。　　辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。2、单值索引、唯一索引、主键索引、复合索引、外键索引、全文索引2.1单值索引概念：即一个索引只包含单个列，一个表可以有多个单列索引。语法：创建表：CREATETABLEcustomer(idINT(10)UNSIGNEDAUTO_INCREMENT,customer_noVARCHAR(200),customer_nameVARCHAR(200),PRIMARYKEY(id),KEY(customer_name));建一个单值索引：createindexidx_customer_nameoncustomer(customer_name);2.2唯一索引概念：索引列的值必须唯一，但允许有空值语法：创建表：CREATETABLEcustomer(idINT(10)UNSIGNEDAUTO_INCREMENT,customer_noVARCHAR(200),customer_nameVARCHAR(200),PRIMARYKEY(id),KEY(customer_name));建一个单值索引：createuniqueindexidx_customer_nooncustomer(customer_no);2.3主键索引概念：特殊的唯一索引，不允许有空值。设定为主键后数据库会自动建立索引，innodb为聚簇索引。主键索引会随着表的创建而创建。2.4复合索引概念：一个索引中包含多个列。语法：创建表：CREATETABLEcustomer(idINT(10)UNSIGNEDAUTO_INCREMENT,customer_noVARCHAR(200),customer_nameVARCHAR(200),customer_ageint,PRIMARYKEY(id),KEY(customer_name));建一个单值索引：createindexidx_customeroncustomer(customer_no,customer_name,customer_age);在mysql建立复合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从复合索引的最左边开始匹配。比如上面这个索引语句其实就是创建了（customer_no）、（customer_no,customer_name）以及（customer_no,customer_name,customer_age）是三个索引。简单来说复合索引有以下几个好处：（1）**可以减少开销。**每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！（2）覆盖索引。MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。（3）效率高。索引列越多，通过索引筛选出的数据越少。四、索引的设计原则索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑复合这些原则，便于提升索引的使用效率，更高效的使用索引。（1）对查询频次高，且数据量适量的表建立索引。一般来说，小表使用全表扫描更快，中大表才使用索引。超级大表索引基本无效。（2）索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用，过滤效果最好的列的组合。（3）尽量使用唯一性高的字段作为索引，字段区分度越高，索引的效率越高。（4）索引可以有效的提高数据查询的效率，但事事都是物极必反的，过多的索引不仅会占用大量的磁盘空间，还会增加数据库系统维护索引的代价。（5）使用短索引，索引的字段的内容应该尽可能少，不然索引也会占用过多的磁盘空间。（6）利用最左前缀原则，N个列组合而成的复合索引，相当于创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么查询效率会大大提升。

MySQL高级—体系结构简介

一、整体架构图和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。MySQL官方给出的系统逻辑架构分为4层，如下图所示：这四层自顶向下分别是连接层，服务层（核心层），存储引擎层，系统文件层。我们自顶向下开始讲解。1、连接层最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上使用了线程池技术，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。2、服务层第二层服务层是MySQL的核心，MySQL的核心服务层都在这一层：查询解析，SQL执行计划分析，SQL执行计划优化，查询缓存。以及跨存储引擎的功能都在这一层实现：存储过程，触发器，视图等。重要的组件的功能如下图所示：ManagementServeices&Utilities系统管理和控制工具SQLInterfaceSQL接口。接受用户的SQL命令，并且返回用户需要查询的结果。比如select*fromxxx就是调用SQLInterfaceParserSQL解析器。SQL命令传递到解析器的时候会被解析器验证和解析OptimizerSQL查询优化器。SQL语句在查询之前会使用查询优化器对查询进行优化，比如有where条件时，优化器来决定先投影还是先过滤。Cache和Buffer查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等在这一层就是SQL语句被执行的一层，接下来我们分析一下SQL语句的执行过程：（1）mysql客户端通过协议和mysql服务器连接，发送查询语句，首先会检查查询缓存(querycache)，如果命中，直接返回结果，否则进行语句解析。查询缓存(querycache)——它存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。（2）语法解析器和预处理：首先mysql解析器会将SQL语句根据关键字进行解析，并生成一颗"解析树"。语法解析器会根据mysql语法规则对解析树进行语法检查；预处理器则根据一些mysql规则进一步检查解析数是否合法。（3）当解析树被认为是合法的了，会通过查询优化器将其转化成执行计划。通常一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。（4）在完成语法解析和优化之后，mysql会生成执行计划。查询执行引擎根据执行计划给出的指令调用存储引擎的结构得到结果。在查询到结果后除了返回给客户端外，还会将结果缓存一份到查询缓存中。SQL语句的执行过程可以用下图表示：3、存储引擎层存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。MySQL采用插件式的存储引擎。MySQL为我们提供了许多存储引擎，每种存储引擎有不同的特点。我们可以根据不同的业务特点，选择最适合的存储引擎。如果对于存储引擎的性能不满意，可以通过修改源码来得到自己想要达到的性能。这里需要说明的是：存储引擎是针对于表的而不是针对库的（一个库中不同表可以使用不同的存储引擎），服务器通过API与存储引擎进行通信，用来屏蔽不同存储引擎之间的差异。在MySQL中常用的存储引擎有3个：InnoDB、MyISAM以及Memory。这三种引擎就是因为很常用，所以在面试中也经常被问到，因此我们有必要详细了解一下，这里不做过多的说明，只是简单的介绍一下，详情参考InnoDB：支持事物，适合OLTP应用，假设没有特殊的需求，使用InnoDB就可以满足需求。InnoDB支持行级锁，从MySQL5.5.8开始InnoDB就成为了MySQL的默认存储引擎。MyISAM：不支持事务，表锁设计，支持全文索引，主要应用于OLAP应用Memory：数据都存放在内存中，数据重启或崩溃，表中的数据都将消失，但是表的结构还是会保存下来。默认使用Hash索引。适用于OLTP应用的临时表或中间表。4、系统文件层该层主要是将数据库的数据存储在文件系统之上，并完成与存储引擎的交互。MyISAM文件格式MyISAM在磁盘存储上有三个文件，每个文件名以表名开头，扩展名指出文件类型：.frm文件：用于存储表结构的定义.MYD文件：用于存放数据，可以及理解为MyISAMData===>MYD.MYI文件：用于存放表索引，可以理解为MYISAMIndex===>MYIMyISAM引擎还支持三种不同类型的存储格式：静态表、动态表、压缩表InnoDB文件格式InnoDB属于索引组织表，InnoDB有两种存储方式：共享表空间和独享表空间存储。两种存储方式的表结构信息和MyISAM一样，以表名开头，扩展名是.frm。.frm文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等。.ibd/.ibdata文件：存放innodb表的数据文件。独享表空间存储方式使用.ibd文件格式。共享表空间存储方式使用.ibdata存储方式，所有的表共同使用一个ibdata文件，即所有的数据文件都存在一个文件中。决定使用哪种表的存储方式可以通过mysql的配置文件中innodb_file_per_table选项来指定。InnoDB默认采用的是独享表空间存储数据，这种方式的好处是当数据库产生大量文件碎片的时，整理磁盘碎片对线上运行环境的影响较小。