索引常见的索引类型常见的索引类型有散列索引、有序数组索引、二叉树索引、跳表等。本文主要讨论MySQL默认存储引擎InnoDB的索引结构。InnoDB的索引结构在InnoDB中是通过一种多路搜索树——B+树来实现的。在B+树中，只有叶子节点会存储数据，所有叶子节点会组成一个链表。在InnoDB中，维护了一个双向链表。大家可能会有疑问，为什么要用B+树而不是二叉树或者B树呢？首先，我们知道访问磁盘需要访问指定的块，而访问指定的块需要磁盘的旋转和磁臂的移动。这是一个耗时的过程。如果增加树的高度，就意味着需要进行更多次磁盘访问，所以会使用n叉树。使用B+树是因为如果用B树进行范围搜索，每次都会重新搜索，叶子节点的链表在B+树中可以得到充分利用。在建表时，可能会添加多个索引，InnDB会为每个索引创建一个B+树来存储索引。比如此时我们创建了一个简单的测试表createtabletest(idintprimarykey,aintnotnull,namevarchar,index(a))engine=InnoDB;这时InnDB会为我们创建两棵B+索引树，一根是主键的聚簇索引，一根是普通索引的辅助索引。这里我直接贴上面mysqltalk(index,lock)的图（因为懒得画图了。。。）可以看到辅助索引上叶子节点的值只有主键的值存储，主键聚集索引上的叶节点存储整条记录的值。回表所以这里要引出一个概念叫回表。比如此时我们执行一个查询操作selectnamefromtestwherea=30;我们知道因为条件MySQL会去到a的索引，但是name的值并没有存储在a的索引上。此时我们需要获取到对应a上的主键值，然后通过这个主键值去到聚簇索引中，最终获取到其中的name值。这个过程称为回表。我们总结一下什么是返回表？MySQL在辅助索引上找到对应的主键值，利用主键值在聚簇索引上找到想要的数据，回调到表中。索引维护我们知道索引是需要占用空间的。索引虽然可以提高我们的查询速度，但是也不能滥用。例如，如果我们在用户表中使用身份证号作为主键，那么每个二级索引的叶子节点大约占用20个字节，如果使用整数作为主键，则只需要4个字节。如果是长整型（bigint）就是8字节。也就是说，如果我维护一个整数后面4g的索引列表，那么身份证就是20g。所以我们可以通过减小索引的大小来减少索引占用的空间。当然，为了维护索引的顺序，B+树在删除和插入时会进行一些必要的维护（在InnoDB中删除会将节点标记为“可重用”以减少对结构的更改）。比如在添加节点的时候，可能会遇到数据页满的情况。这时候就需要对页面进行拆分。这是一个耗时的工作，而且页的拆分也会导致数据页的利用率较低，比如在一个原本存放三个数据的数据页中添加另一个数据时，需要进行分页。这时，现有的4个数据会被分配到2个数据页，从而降低数据页的利用率。上面说的覆盖索引指的是回表，有时候我们查辅助索引的时候，我们要查的数据已经满足了。这时候InnoDB会进行一个覆盖索引的操作来提高效率，减少返表。比如此时，我们执行一个select操作selectidfromtestwherea=1;这时候显然我们可以通过a的索引直接获取id的值。这时候就不需要回表了，这时候我们就用到了覆盖索引。简单的说，覆盖索引就是当我们使用辅助索引的时候能够拿到我们需要的数据的时候，我们就不需要再去回表了。这时候我们新建一个学生表CREATETABLE`stu`(`id`int(11)NOTNULL,`class`int(11)DEFAULTNULL,`name`varchar(255)DEFAULTNULL,PRIMARYKEY(`id`),KEY`class_name`(`class`,`name`)USINGBTREE)ENGINE=InnoDBDEFAULTCHARSET=utf8我们用class（类号）和name做一个联合索引，你可能会问这个联合索引有什么用？我们可以结合上面的Covering索引来理解，比如这时候我们有一个需求，我们需要通过班级号找到对应的学生姓名。selectnamefromstuwhereclass=102;这时候我们可以直接在辅助索引上找到学生的名字，而不需要再次返回到表中。一般来说，设计好索引，充分利用覆盖索引，可以大大提高检索速度。最左前缀原则是基于联合索引的，是联合索引的一种匹配规则。这时候，我们会稍微改变一下上面的要求。这时我们有一个学生迟到了，但是门卫记录信息的时候他只写了自己的名字张三，并没有写班级，所以我们需要找到对应的学生姓名。班级编号。selectclassfromstuwherename='张三';这个时候我们不会使用我们的联合索引，而是进行全表扫描。为什么？因为最左匹配原则。我们可以画个简单的图来理解。我们可以看到整个索引设计就是这样设计的，所以我们在需要查找的时候也需要遵循这个规则。如果我们直接使用名称，那么InnoDB将不知道我们需要做什么。当然，最左匹配原则也有这些规则。当完整值匹配时，优化器将更改顺序。也就是说你的全值匹配的顺序和原来的联合索引顺序不一致也没关系。索引匹配从最左边开始，如果没有全表扫描，比如你设计一个(a,b,c)的联合索引，那么可以用(a),(a,b),(a,b,c)并??且你使用(b),(b,c),(c)将不会使用索引。当遇到范围匹配时，索引将被取消。比如这个时候你执行select*fromstuwhereclass>100andname='ZhangSan'这样的select操作；这时InnoDB会放弃索引，进行全表扫描，因为此时InnoDB并不知道如何遍历索引，所以会进行全表扫描。我通过压低指数给你挖了个坑。刚才的操作需要返回MySQL5.6之前的表，但是在5.6版本之后，做了一个叫做索引下推的优化。select*fromstuwhereclass>100andname='张三';如何优化呢？因为刚才的最左匹配原则，我们放弃了索引，接下来我们通过回表的方式来判断名字。这时候我们要做的操作应该是这样的，但是下推索引之后就变成了这样。这时候“李四”和“小明”就不会回到餐桌上了。因为这里匹配的是后面的name=张三，也就是说如果因为范围查询而终止了最左匹配原则，InnoDB还是会下推索引来优化性能。一些实践在哪些情况下需要创建索引？应该索引经常用作查询条件的字段。多表关联查询时，关联字段要建立索引。查询中的排序字段应该被索引。需要索引统计或分组字段。在这种情况下不需要创建记录很少的索引表。经常增删改查的表。经常更新的字段。where条件使用的字段不高。场大时。其他尽量选择差异化程度高的列作为索引。不要对索引进行一些函数操作，还要注意隐式类型转换和字符编码转换。尽量扩展索引，不要新建索引。比如表中已经有a的索引，现在要增加(a,b)的索引，那么只需要修改原来的索引即可。应更多考虑覆盖索引、索引下推和最左匹配。LockgloballockMySQL提供了一种方法来添加全局读锁，命令是Flushtableswithreadlock(FTWRL)。当需要使整个库处于只读状态时，可以使用该命令，然后其他线程的以下语句将被阻塞：数据更新语句（数据的增删改查）、数据定义语句（包括建表、修改表结构等）和一个更新事务的commit语句。一般在对整个数据库进行逻辑备份时会用到，以保证其他线程无法更新数据库。MVCC中获取一致视图的操作使得备份变得非常简单。想了解MVCC可以参考https://juejin.im/post/5da8493ae51d4524b25add55表锁MDL(MetaDataLock)元数据锁MDL锁使用保证只有一个线程可以对表结构进行修改桌子。怎么说？MDL分为MDL写锁和MDL读锁。加锁规则如下：当一个线程对表进行CRUD操作时，会加MDL读锁。当线程对表进行表结构修改操作时，会加MDL写锁。锁和读锁，写锁和写锁是互斥的，读锁不是互斥的locktablesxxxread/write；这是一个为表设置读锁和写锁的命令，如果锁是在一个线程A表t1读，t2写；这条语句，其他线程写t1，读写t2的语句都会被阻塞。同时线程A在执行unlocktables之前只能进行读t1和读写t2的操作。连写t1都不行，其他表自然访问不了。这种表锁是一种处理并发的方式，但是InnoDB中常用的是行锁。我们知道MySQL在5.5版本之前默认的存储引擎是MyISAM，而MyISAM和InnoDB最大的区别就是两个事务行锁。行锁是我们今天的话题。交易不懂的可以去补习。它的实现锁是两把锁，可以理解为一个写锁（排他锁X锁）和一个读锁（共享锁S锁）共享锁（S锁）：允许一个事务读取一行，防止其他事务读取获取同一个数据集的独占锁。也叫读锁：读锁是共享的，多个客户端可以同时读取同一个资源，但不允许其他客户端修改。排他锁（X锁）：允许获取排他锁的事务更新数据，防止其他事务获取同一数据集的共享读锁和排他写锁。也叫写锁：一个写锁是独占的，一个写锁会阻塞其他的写锁和读锁。而行锁也会引起一个很头疼的问题，那就是死锁。如果事务A对100行加写锁，事务B对101行加写锁，此时事务A要修改101行，事务B要修改100行，所以占用等待会造成死锁问题。面对死锁问题，只有检测和预防。Next-keylockMVCC和rowlock并不能解决幻读问题。这时候InnoDB使用了一个叫做GAP锁（gaplock）的东西，它和rowlock形成了一个next-keylock，解决了幻读问题。但是由于其加锁规则，扩大了一些加锁范围，降低了数据库的并发性。具体加锁规则如下：加锁的基本单位是next-keylock，它是rowlock和GAPlock的组合。查找期间访问的对象被锁定。对于索引的等效查询，当唯一索引被锁定时，next-key锁退化为行锁。在对索引的等价查询中，当向右遍历最后一个值不满足等价条件时，next-key锁退化为间隙锁。对唯一索引的范围查询访问直到第一个不满足条件的值。MVCC解决幻读的思路比较复杂，这里就不做过多验证了。总结对于MySQL的索引，我给出了很多实践，其实这些实践都是从原理上来的，而InnoDB其实是B+树的改进版，存储索引的结构。了解了这些，你就会得心应手。至于MySQL的锁，主要是行锁。InnoDB其实就是使用了行锁，而MVCC也有next-key锁来实现事务并发控制。对于MySQL来说，最重要的其实就是锁和索引，因为里面的内容太多了。本文仅做一些介绍和简单的分析。如果想了解更多，可以查看相应的文章。
