MySql短记

索引介绍

覆盖索引

定义：就是说当业务的需求是直接可以根据索引就能查询出所需要的数据的时候，不需要查表数据的。覆盖索引不需要考虑最左匹配原则的，因为他压根不查表数据。对于覆盖索引，因为索引中包含了查询所需的全部字段，所以不需要回表查询实际的数据行。这意味着覆盖索引可以直接满足查询的需求，不受最左匹配原则的限制。

举例说明：

--- 创建表
CREATE TABLE `user` (
                        `user_id` int NOT NULL,
                        `username` varchar(50) DEFAULT NULL,
    `password` varchar(50) DEFAULT NULL,
    `email` varchar(100) DEFAULT NULL,
    `phone_number` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
--- 创建一个索引
CREATE INDEX idx_covering_index ON user (password, email);
--- 执行一个查询语句
select password,email from user where email = 'xxx@xx.com';

那么如上就使用到了覆盖索引，因为这个查询语句中返回的值都能在索引本身能够查询到，所以==完全不需要去查表数据==，需要满足的就是查询的条件和索引的字段的一个子集。

通过上面的例子应该就能明白覆盖索引是什么了吧。说简单点就是查询的数据信息都在索引的列中含有。在就是这个概念问题：我觉得是其实有了这种查询行为（查询的数据都在索引中）才有了这个名称，而不是有这个名称，才有这个行为。

组合索引

这个名词和覆盖索引相像的地方就是他们都是由1个或多个列组成的索引。当查询的数据都在索引中含有就叫覆盖索引，其他的情况则称之为组合索引。所以很明显覆盖索引也是一种特殊的组合索引。这个索引我觉得可以理解为有了这个名称才有的这索引。

举例说明：

# 还是上面那张表，索引也是那样的
# 执行sql语句1
select password,email from user where password = '******';
# 执行sql语句2
select password,email,phone_number from user where password = '******';
# 执行sql语句3
select * from user where password = '******';
# 执行sql语句4
select password,email,phone_number from user where email = 'xxx@xx.com';
# 执行sql语句5
select * from user where email = 'xxx@xx.com';

sql语句1运行结果：

很明显这个其实也是一个覆盖索引。
sql语句2运行结果：

这里表示用到了索引查询，但并不是覆盖索引。
sql语句3运行结果：

和语句2是一样的结果，说明这个查询的结果集是不影响索引的
sql语句4运行结果：

没用到索引，为什么呢？因为我们组合索引的使用需要满足最左匹配原则，当不满足最左匹配原则，则索引会失效。tips:最左匹配原则是指你建索引的时候，后面的查询条件必须按照索引的顺序来，当你创建的时候是password,email时，查询条件也必须是这样的一个顺序，当你将email写前面时，则索引失效。
sql语句5运行结果：

结论同上。

MySql锁机制介绍

锁机制用于管理对共享资源的并发访问，实现事务的隔离级别。

我这里介绍采用的是根据锁的粒度级别来介绍MySql的锁机制。

锁类型

按照粒度划分可以分为：全局锁、表级锁、行级锁。全局锁是针对数据库加锁；表级锁是针对表或页进行加锁；行级锁是针对表的索引加锁。

全局锁

锁的范围：锁数据库。
全局锁用于全库逻辑备份，在备份数据库期间，不会因为数据或表结构的更新，而导致出现备份文件的数据与预期的不一样。也就是说在备份期间，业务只能读数据，不能写数据。

-- 全局锁，整个数据库处于只读状态，其他操作均阻塞
 FLUSH TABLES WITH READ LOCK
-- 释放全局锁
UNLOCK TABLES

备份数据库时，采用其他什么方式可以避免影响业务？
如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View。
所以数据库的隔离级别最好设置可重复的，也就是RR级别。

表级锁

锁的范围：整张表。

表锁
锁的是整张表，锁的粒度比较大，影响并发，尽量少使用表锁;

1
2
3

LOCK TABLES 表名 READ|WRITE

UNLOCK TABLES

元数据锁
元数据锁 (MDL) ：避免 DML(我们操作数据库表的内容增删改查) 和 DDL(数据库表的结构的操作) 冲突，防止表的结构改变，维护元数据一致性。
当对数据库的表进行操作时，自动添加 MDL。当事务提交后，MDL 释放。事务执行期间 MDL 一直存在。
- 对一张表进行 CRUD 操作时，加的是 MDL 读锁
- 对一张表做结构变更操作的时候，加的是 MDL 写锁
  例如：当有线程在执行 select 语句（加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（释放 MDL 读锁）。反之，当有线程对表结构进行变更（加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（释放 MDL 写锁）。
意向锁
意向锁也即是意图，就是说你来访问我（表）是个什么意图。
当一个事务想要获得一张表某些行（记录）的锁，必须先获得对应表的意向锁。可以快速判断表里是否有行记录加锁，从而避免表锁逐行检查行锁。
由于 innoDB 存储引擎支持的是行级锁，因此意向锁不会阻塞除全表扫描以外的任何请求。意向锁互相兼容，与表级 S | X 锁互斥，与行级的 S | X 锁兼容（意向锁不会和行锁冲突）。
- 意向共享锁 IS：事务想要获取一张表某些行的 S 锁，必须先获得表的 IS 锁。
- 意向排他锁 IX：事务想要获取一张表某些行的 X 锁，必须先获得表的 IX 锁。
  例如：事务A 获取保持表中某些行的 X 锁，此时表中有两把锁：X 锁和 IX 锁。此时，事务 B 想要获得表中某一行的 X 锁，检测到表中存在 IX 锁，得知表中某些行必然存在 X 锁，事务 B 阻塞。这样，无需检测表中的每一行数据是否存在 X 锁。
  个人理解后的说明：意向锁完全都是MySql自己产生的，起到的是一个辅佐作用。
  在以下情况下，MySQL会自动添加意向锁：
  1. 当事务获取某个范围的行级锁（比如行级排他锁或共享锁）时，MySQL会在表级别添加意向锁。意向锁的类型（IS或IX）取决于事务请求的行级锁类型。
  2. 如果某个事务获取了表级排他锁（X锁），则会自动添加意向排他锁（IX锁）。IX锁表示事务有意向在表的某个范围上加排他锁。
    这个锁是为了减少死锁的可能性，当一个事务获取到某些行或对表加了排他锁的写操作，其他的事务就只能在等待了。还有一个重要的点就是:意向锁不影响单行的操作，你只是对某一行的写操作是不会触发数据库的机制产生意向锁的。
自增锁
AUTO-INC锁，实现自增约束 AUTO_INCREMENT，插入语句执行完后释放锁，并非事务结束后释放锁。
例如：在插入数据时，加自增锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把自增锁释放掉。这样，在一个事务在持有自增锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。
但是，自增锁在对大量数据进行插入操作时，阻塞其他事务的插入操作，影响性能。因此，在 Mysql 5.1.22 版本后仅对 AUTO_INCREMENT字段加上轻量级锁，当字段自增后，立即释放锁，而不需要等待整个插入语句执行完后才释放锁。
最后就是自增锁不影响意向锁，意向锁只是在上面提到的两个条件（某个范围的行级锁、表级排他锁）中产生。

行级锁

事务提交后，锁被释放。

行级锁的类型有：

记录锁，也就是仅仅把一条记录锁上；
间隙锁，锁定一个范围，但是不包含记录本身；
临键锁：记录锁 + 间隙锁的组合，锁定一个范围，并且锁定记录本身

记录锁
Record Lock：锁住一条行记录。
S 锁：共享锁，读锁，允许其他事务读取，不允许修改
X 锁：排他锁，写锁，不允许其他事务读取和修改
互斥关系如下图：
间隙锁
Gap Lock：锁定一个范围，但不包含记录本身，RR级别及以上支持，目的是为了部分解决幻读问题
间隙锁间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系。部分解决了幻读问题，解决了快照读的幻读问题。对于当前读，仍需要手动加锁，防止其他事务在记录间插入新的记录，避免幻读问题。
插入意向锁
一种间隙锁形式的意向锁，表中INSERT操作时产生。在索引记录间的间隙上的锁，在查询索引未命中，或查询辅助非唯一索引时添加.多事务同时写入不同数据至同一索引间隙，并不需要等待其他事务完成，不会发生锁等待。因为它只是代表想插入的意向。
例如：假设有一个记录索引包含键值 4 和 7。若两个不同的事务分别插入 5 和 6，每个事务都会获取加在 (4, 7) 之间的插入意向锁，获取在对应插入行上的排他锁，此时并不会互相锁住，因为数据行并不冲突；若两个不同事务都插入 5，同理每个事务都会产生一个加在 (4, 7) 之间的插入意向锁，意向锁并不冲突，再获取插入行的排他锁，后获取插入行排他锁的事务会被阻塞。
临键锁
Next-Key Lock:记录锁 + 间隙锁的组合,锁定一个范围,并且锁住记录本身。左开右闭，RR级别及以上支持，解决了幻读问题。
例如：一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。从而既能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

锁兼容

横向：表示已经持有的锁；纵向：表示正在请求的锁。
注：
一个事务已经获取了插入意向锁，对其他事务没有任何影响。
一个事务想要获取插入意向锁，若其他事务加了 gap lock 或 next-key lock 会阻塞

分库分表

记录下相关的概念，防止以后自己忘记了。

分表分库是两种操作，一种是分表，一种是分库。
但是他们的中心思想都是将数据分散，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。
例如，将某业务的数据库分为若干个独立的数据库，并且对于大表也拆分为若干小表，这样就很大程度上降低了并发数据查询时的数据冲突。

分表

垂直分表

定义：将一个表按照字段分为多表，每个表里面都存储其中一部分字段。简单来说就是将一张表的字段结构拆分成多张，拆分的原则可以根据原来那张表字段常用不常用来决定。
我们以商品表来举例子：
商品信息中，一般包括多条字段，如商品名、价格、简介……
而其中商品名和价格可能是最重要的，而简介就相对没有那么重要。
所以我们可以拆分成两张表：

这样做的好处是：
查看详情的用户与商品信息浏览互不影响，避免了IO争抢并减少锁表的几率。
充分发挥高频数据（商品名和价格）的操作效率，商品名和价格的操作的高效率不会被商品简介的低效率所拖累。

水平分表

定义：同一个数据库内，对数据行拆分，不影响表结构。通俗的意思就是相当于建立两张一模一样的表（表名称不一样），然后存储数据的时候根据一些雪花算法来存到不同的表中。

优点：
优化单一表数据量过大而产生的性能问题。
避免IO争抢而减少锁表的几率。

分库

虽然通过分表性能得到一定程度的提升，但是很多时候还无法达到预期效果。
因为数据库始终限制在一台服务器上，所以分表有如下几个局限性：

磁盘空间可能不够。
只解决了单一表数据量过大的问题。
每个表还是竞争同一个物理机的物理资源

垂直分库

概念：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
结果：

每个库的结构都不一样；
每个库的数据也不一样，没有交集；
所有库的并集是全量数据；
场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。
分析：到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化

水平分库

定义：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。比如一张订单表可以根据地点字段，将数据根据地点进行分配到不同的库中。
结果：

每个库的结构都一样；
每个库的数据都不一样，没有交集；
所有库的并集是全量数据；
场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。
分析：库多了，io和cpu的压力自然可以成倍缓解

分库分表的缺点

分页/排序
在同一张表时，只需要用 limit、order by 便可轻松搞定。
跨节点多库进行查询时，分页、排序，就变得很复杂。
先在不同的分片节点中将数据进行排序并返回，然后将不同分片返回的结果集进行汇总和再次排序
主键重复
分表分库会让平时经常使用的主键自增长形同虚设。生成的ID无法保证全局唯一。
因此我们需要单独设计全局主键，以便面跨库主键重复问题。
事务的一致性
因为分库分表把数据分布在不同的库、不同服务器，所以不可避免的带来分布式事务问题。
当一个请求要先请求数据库A，再请求数据库B，这两个属于同一个事务，多个库会导致分布式事务问题。
需要有一些措施来保证事务一致性的问题，这里不在展开，有兴趣自行了解。
关联查询
分库后，如果两个表不在同一个数据库，甚至不在同一台服务器上，无法进行关联查询。
解决方案：
将原关联查询分为两次查询,第一个查询的结果找出关联数据id,根据id发起第二次请求得到关联数据,最后将获得的数据进行拼装。

总结

分库分表的诞生是为了解决数据库的性能瓶颈，虽然有很多好处，但相应的也有很多坏处。
但在业务量还不大的时候，我们其实应该首先考虑索引、缓存、读写分离等方案，盲目使用分表分库技术，会导致业务变得臃肿，反而徒增烦恼。