MySQL数据库总结篇

一 数据库基础知识

1.1 什么是数据库?

MySQL是一种关系型数据库管理系统，由瑞典的MySQL AB公司开发，属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一，在WEB应用层面，MySQL是最好的RDBMS(Relational Database Management System)关系型数据库管理系统应用软件之一，MySQL在开发中是非常重要的，因为他是开源免费的，功能也比较强大。

1.2 数据库三大范式是什么?

第一范式：每格列都不可以再拆分。

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不是依赖主键的一部分。

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

在设计数据库表结构时，要尽量遵守三大范式，如果不遵守，要有足够的理由，比如性能。

二 SQL的几种连接查询方式

准备表

drop table if exists test;
create table test(
	id int primary key auto_increment,
    username varchar(10) not null,
    password varchar(10) not null
);


drop table exists test_info;
create table test_info(
	id int primary key auto_increment,
    age varchar(10),
    address varchar(50),
    parent_id varchar(10) not null
);

插入数据

insert into test(username,password) values
	('小明','123'),
	('小李','123'),
	('敏敏','123'),
	('老王','123')
;

insert into test_info(age,address,parent_id) values
	('18','上海','1'),
	('19','北京','2'),
	('20','深圳','3'),
	('21','广州','5')
;

2.1 内连接(inner join)

典型的连接运算，使用像 = 或 < >之类的比较运算符。

内连接使用的比较运算符根据每个表共有的列的值匹配两个表中的行，例如，检索students表和courses表中学生标识相同的所有行。

查出的是两张表的交集，两张表都有的结果才查出来。

SQL语法：select * from 表A inner join 表B on 判断条件

select * from test inner join test_info on test.id = test_info.parent_id;

结果

2.2 外连接

1、左外连接 (left join)以左表为主表(查询全部)，右表辅助(没有的用null补充)

SQL语法：select * from 表A left join 表B on 判断条件;

select * from test left join test_info on test.id = test_info.parent_id;

结果：

2、右外连接(right join) 以右表为主表(查询全部)，左表辅助(没有的用null补充)

SQL语句：select * from 表A right join 表B on 判断条件;

select * from test right join test_info on test.id = test_info.parent_id;

结果：

3、联合查询(union) 两个表的所有数据都展示出来

SQL语句：select * from 表A left join 表B on 判断条件 union select * from 表A right join 表B on 判断条件;

select * from test as a left join test_info as b on a.id = b.parent_id
union
select * from test as a right join test_info as b on a.id = b.parent_id
;

结果：

2.3 SQL查询基本原理

单表查询：根据WHERE条件过滤表中的数据，形成中间表，然后根据SELECT选择的字段返回最终结果。

两表连接查询：对两表求笛卡儿积并用on条件和连接类型进行过滤形成中间表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的字段返回最终的结果。

多表连接查询：先对第一个和第二个表按照两表连接查询，然后用查询的结果作为一张表和第三张表做连接查询，以此类推，最终形成一种中间表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的字段返回最终的结果。

三 存储引擎

3.1 存储引擎的区别

存储引擎(Storage engine)：MySQL中的数据、索引以及其他对象是如何存储的，是一套文件系统的实现。

常用的存储引擎有如下：

• innodb引擎：innodb提供了对数据库的事务(ACID)的支持，并且还提供了行锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
• MyISAM引擎(原本MySQL默认存储引擎)：不提供事务的支持，也不支持行锁和外键。
• Memory引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

MyISAM与Innodb的区别

	Innodb	MyISAM
存储结构	每张表都存在同一个文件中	每张表被存放在三个文件中：表定义文件、数据文件、索引文件。
数据和索引存储方式	数据和索引是集中存储的，查询时做到覆盖索引会非常的高效	数据和索引是分开存储的，索引的叶子节点存储的是数据地址，需要在寻址一次才能获取数据。
记录存储顺序	按主键大小有序插入	按照插入顺序保存
索引	聚簇索引	非聚簇索引
索引的实现方式	B+树索引，innodb是索引组织表	B+树索引，myisam是堆表
全文索引	不支持	支持
哈希索引	支持	不支持
外键	支持	不支持
事务(ACID)	支持	不支持
锁粒度	行级锁定、表级锁定、锁定力度越小并发能力越强	表级锁定

3.2 存储引擎选择

MyISAM：适用于管理非事务表，它提供了高速存储和检索，以及全文搜索的能力。比如博客系统、新闻门户网站。

InnoDB：是用于更新操作频繁，或者要保证数据的完整性，并发量高，支持事务和外键的场景，比如自动化办公系统。

如果没有特殊要求，默认使用InnoDB即可。

四 索引

4.1 什么是索引?

索引是一种数据结构，是数据库管理系统中的一个排序数据结构，以协助快速查询数据库表中的数据。索引的实现通常使用B+树和hash表。

更通俗的说，索引就相当于目录，为了方便在书中查找内容，通过内容建立索引形成目录。

4.2 索引有哪些优点和缺点?

索引的优点：

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 通过使用索引，可以在查询过程中，使用优化器，提高系统的性能。

索引的缺点：

• 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增、删、改时，索引也要动态维护，会降低增、删、改的效率。
• 空间方面：索引需要占物理空间。

4.3 索引有哪些类型

主键索引：非空且唯一，一个表有且只有一个主键(primary key)。

唯一索引：数据列不允许重复，可以为NULL，一个表允许多个列创建唯一索引。

alter table table_name add unique(column);  -- 创建唯一索引
alter table table_name add unique(colum1,column);  -- 创建组合唯一索引

普通索引：基本的索引类型，没有唯一性的限制，允许为NULL，一张表允许创建多个普通索引。

alter table table_name add index index_name(column); -- 创建普通索引
alter table table_name add index index_name(column1,column2,column3);  -- 创建组合索引

全文索引：是目前搜索引擎使用的一种关键技术，MyISAM存储引擎才有全文索引。

4.4 索引的数据结构

在MySQL中使用较多的索引有HASH索引，B+树索引等，索引的数据结构和具体的存储引擎的实现有关，而我们经常使用的InnoDB存储引擎，默认索引实现为B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录等值查询的时候，可以选择哈希索引，查询性能最快，其余大部分场景，建议使用B+树索引。

1） B+树索引

MySQL通过存储引擎存取数据，绝大多数用的都是InnoDB，按照实现方式分，InnoDB的索引类型只有两种：BTREE索引和HASH索引。B树索引是MySQL数据库中使用最频繁的索引类型，基本所有存储引擎都支持BTree索引。

B+树数据结构

一棵传统的M阶B+树需要满足以下几个要求

• 从根节点到各个叶子结点的长度一致。
• 所有数据信息都存储在叶子节点上，非叶子节点仅作为叶节点的索引存在。
• 根节点至少有两个子树。
• 每个树节点最多拥有M个子树。
• 每个树节点(除了根节点)最少拥有M/2个子树

B+树的一些特性：

1. B+树种的B不是代表二叉(Binary)，而是代表平衡(Balance)，因为B+树是从最早的平衡二叉树演化而来的，但是B+树不是一个二叉树。
2. B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树，在B+树中，所有的记录节点都是按照键值大小顺序存放在同一层的叶子节点中，由叶子节点指针进行连接。
3. B+树在数据库中的特点就是高扇出，因此在数据库中B+树的高度一般都在2~4层，这也就是说查找一个键值记录时，只需要2到4次IO。
4. B+树索引不能直接找到一个给定值的具体行，B+树索引找到的只是被查找的键值所在的页，然后数据库把页读到内存，在内存中在进行查找，最后找到要查找的数据。
5. 数据库中B+树索引可以分为，聚簇索引和非聚簇索引，但是不管是聚簇索引还是非聚簇索引，其内部都是B+树实现的，即高度是平衡的，叶子节点存放着所有的数据，聚簇索引和非聚簇索引的区别就是，一张表中只能由一个聚簇索引，叶子节点中是否存储的是一整行数据。
6. B+树的每个数据页(叶子节点)是通过一个双向链表进行连接的，数据页上的数据顺序是按照逐主键顺序存储的。

2）哈希索引

类似于数据结构中简单的实现HASH表一样，当我们在MySQL中用哈希索引时，主要就是通过Hash算法(常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法)，将数据库字段数据转换成定长的hash值，与这条数据的行指针存入Hash表中的对应位置，如果发生Hash碰撞，则在对应的键下以链表形式存储。

4.5 数据库为什么用B+树而不用B树?

• B+树的叶子节点存储了所有的数据，而非叶子节点中存储的时比较关键字，而B树中所有的节点都会存数据。B+树的叶子节点之间有一根指针相连，这样的好处是顺着叶子节点从左至右可以遍历整个数据，极大的简化了排序操作，不仅能方便查找，而且有助于排序，在MySQL索引中叶子节点之间的双向链表可以正反遍历，更加灵活。
• B树只适合随机检索，而B+树支持随机检索和顺序检索。
• B+树空间利用率高，可减少I/O次数，磁盘读写代价更低。一般来说，索引本身很大，不可能全部存储到内存中，因此索引往往以索引文件的形式存在磁盘上，这样的话，索引查找时就要产生磁盘I/O，B+树的内部节点并没有指向关键字具体信息的指针，只是作为索引使用，其内部节点比B树小，一次性读入内存中可以查找的关键字也就越多，相对的，I/O读写次数也就降低了。而I/O读写次数是影响索引检索效率的最大因素。
• B+树的查询效率更加稳定。B树搜索时可能在某一个节点就结束了，越靠近跟节点的数据查找时间就越短，只要找到关键字即可确定数据的存在。而在B+树种，顺序检索比较明显，随机检索时，任何关键字查找都必须从根节点到叶子节点，路径长度相同，导致每一个数据的查询效率是相当的。
• 在数据库中基于范围查找是非常频繁的，B+树的叶子节点使用指针顺序连接在一起，只要遍历对应的叶子节点，就可以实现。而B树不支持这样的操作。
• 增删文件时，效率更高。因为B+树的叶子节点包含所有的关键字，并以有序的链表结构存储，这样可很好提高增删效率。

4.6 索引算法有哪些?

BTree算法

BTree算法是最常用的MySQL数据库的索引算法，也是MySQL默认的算法。因为它不仅仅可以被用在比较运算符和between上，还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量。

explain select * from test where username like '小明%';

-- 如果以通配符开头，则不会使用索引
explain select * from test where username like '%小明';

Hash算法

Hash算法只能用于对等比较，例如 = ，由于是一次定位数据，不需要BTree索引需要从根节点到子节点，最后访问到叶子节点这样多次I/O，所以检索效率远高于BTree索引。

4.7 创建索引的原则?索引设计的原则?

索引虽好，但也不是无限制的使用，最好符合以下几个原则。

1. 为常作为查询条件的字段建立索引，where子句中的列，或者连接子句中的列。
2. 为经常需要排序、分组操作的字段建立索引。
3. 更新频繁的字段不建议建立索引。
4. 不能有效区分数据的列不适合做成索引(比如性别)。
5. 对于定义为text、image、bit的数据类型的列不要建立索引。
6. 非空字段：应该指定列为NOT NULL，除非你想存储NULL，在MySQL中，含有控制的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂，你应该用0、一个空串代替NULL。
7. 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能，在修改表时，索引会进行更新或者重构，索引列越多，这个时间就越长。

4.8 什么时候使用了索引，查询还是慢?

• 索引全局扫描
• 索引过滤性不好
• 频繁回表的开销

4.9 MySQL使用自增主键的好处

1. 自增主键按照顺序存放，增删数据速度快，对于检索非常有利。
2. 数字型，占用空间小，易于排序。
3. 使用整型才可以使用PRIMARY KEY，不用担心会重复。

4.10 什么是聚簇索引？何时使用聚簇索引和非聚簇索引?

• 聚簇索引：将数据与索引放在一块，索引结构的叶子节点存储了行数据，找到了索引就找到了数据。
• 非聚簇索引：将数据和索引分开存储，索引结构的叶子节点存放行数据的地址。

聚簇索引的优点：

• 数据访问更快，聚簇索引将索引和数据保存在同一个B+树种，因此从聚簇索引种获取数据通常比非聚簇索引要快。
• 当你需要去除一段范围内的数据时，用聚簇索引也要好一点。
• 使用覆盖索引扫描的查询可以直接使用节点中的主键值。

聚簇索引的缺点：

• 插入速度严重依赖插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB，我们一般定义一个自增的id作为主键。
• 更新逐渐的代价很高，因为将会导致被根性的行移动，因此，对于InnoDB，我们一般设置主键为不可更新。
• 通过辅助索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

4.11 索引相关概念

1. 对于普通字段，比如name字段，需要根据name字段的索引树(非聚簇索引)找到叶子节点对应的主键值，在通过主键值去主键索引树中查找一遍，才可以找到想要的记录，这就叫做回表查询。先定位主键值，在定位行记录，它的性能较扫面一边的索引树效率更低。
2. InnoDB的行锁是建立在主键索引基础上的，行锁锁的是索引，不是数据，所以提高并发写的能力要在查询字段添加索引。
3. 主索引和辅助索引：主索引就是逐渐索引，辅助索引就是根据业务需要，自己设置的普通非主键索引。
4. 聚簇索引：InnoDB采用的是聚簇索引，一个表中只能有一个聚簇索引，因为表数据存储的物理地址都是唯一的，聚簇索引的value村的就是正式的数据，不是数据的地址。主索引树里面包含了真实的数据，key是主键值，value值就是数据，key值按照B+树规则分散排布在叶子节点上。
5. 非聚簇索引：MyISAM的主索引和辅助索引都采用的是非聚簇索引，索引和表数据都是分离的，索引的value值存的是数据的地址。
6. InnoDB的索引：主索引采用的是聚簇索引，辅助索引采用的是非聚簇索引。所以在InnlDB中。根据辅助索引查询需要访问两次B+树，同时主键的长度越短越好，越短辅助索引的value值就越小。InnoDB中根据主键进行范围查询，会特别快。
7. MyISAM的索引：主索引和辅助索引都是非聚簇索引
8. B+树：不管是什么索引，在MySQL中都是B+树结构，可以充分利用数据块，来减少I/O查询的次数，提升查询的效率。一个数据块data里面，存储了很多个相邻的key和value，所有的非叶子节点都不存数据，都是指针。
9. MySQL采用B+树的优点：I/O操作减少(每次都是页读取)，范围查找跟快捷(相邻页之间有指针)。

4.11 联合索引是什么?组合索引是什么?

MySQL可以使用多个字段组合建立一个索引，叫联合索引，在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

4.12 联合索引数据结构实现原理，使用联合索引是怎么进行查询的?

假设，我们对(a,b)字段建立索引，如下图所示

上图时按照a来进行排序的，在a相等的情况下，才按b来排序。

因此，我们看到a是有序的1，1，2，2，3，3。而b是全局无序的1，2，1，4，1，2。因此直接执行b=2这种查询条件是没办法利用索引。

从局部来看，当a的值相等时，b是有序的，因此执行a=1 and b=2是可以利用到索引的，而你执行a>1 and b=2，a字段能用到索引，b字段用不到索引。因为a的值此时是一个范围，不是固定的，在这个范围内b的值不是有序的，一次b字段用不上索引。

综上所述，最左匹配原则，在遇到范围查询的时候，就会停止匹配。

4.13 什么是最左前缀原则?什么是最左匹配原则?为什么需要注意联合索引中的顺序?

• 最左前缀原则：就是最左边的优先，指的是联合索引中，优先走最左边列的索引，对于多个字段的联合索引，如index(a,b,c)联合索引，则相当于创建了a的单列索引，(a,b)联合索引和(a,b,c)联合索引(但并不是建立了多个索引树)。MySQL会一直向右匹配，直到遇到范围查找就停止匹配(> < between like)，比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立的是(a,b,d,c)的索引，都可以用到索引。
• =和in可以乱序，比如a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序，MySQL的查询优化器会帮你优化成所应可以识别的形式。
• 如果建立的索引顺序是(a,b)那么直接采用where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性。

五 事务

5.1 什么是数据库事务？

事务是逻辑上的一组操作，要么都执行，要么都不执行。

5.2 事务的四大特性(ACID)

特性	说明
原子性(Atomic)	事务是最小执行单位，不允许分割，事务包含的所有操作要么全部成功，要么全部失败回滚。
一致性(Consistency)	事务执行之前和执行之后必须处于一致状态。转账前后的钱数综合一致。
隔离性(Isolation)	当多个用户并发访问数据库时，操作同一张表，数据库为每一个用户开启的事务，不能被其它事务的操作所干扰，多个并发事务之间是隔离的，数据库规定了多种隔离级别，不同的隔离级别对应不同的干扰程度。隔离级别越高，数据一致性越好，并发能力越低。
持久性(Durability)	指一个事务一旦提交，那么对数据库中的数据的改变就是永久的，即使是在数据库系统遇到故障的情况下，也不会丢失提交事务的操作。

5.3 什么时脏读？不可重复读？幻读？

• 脏读(Dirty Read)：一个事务读取到另外一个事务未提交的数据，读取到的数据是无效的。

• 不可重复读(Non-repeatable read)：一个事务读取同一条记录，得到的结果不一致。这可能是在两次查询之间，别的事物更新了这条记录。

• 幻读(Phantom Read)：发生在两个完全相同的查询上，得到的结果不一致。可能是在两次查询的过程中，另一个事务增加或减少了记录。

不可重复读和幻读的区别

不可重复读的重点是修改，幻读的重点是新增或删除。

5.4 什么是事务的隔离级别？MySQL的默认隔离级别是什么？

为了达到事务的事务，数据库定义了四种不同的事物隔离级别，由低到高依次是Read uncommitted、Read committed、Repeatable read、Serializable，后三个级别可以依次解决脏读，不可重复读，幻读。

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE READ	×	×	√
SERIALIZABLE	×	×	×

SQL标准定义了四个隔离级别

• READ-UNCOMMITTED(读未提交)：最低的隔离级别，一个事务可以读取另一个事物更新单位提交的数据。可能会导致脏读、不可重复读、幻读。
• READ-COMMITTED(读已提交)：一个事务提交以后才嗯那个被其他的事物读取到，可以阻止脏读，但是阻止不了不可重复读和幻读。
• REPEATABLE READ(可重复读)：对同一记录的多次读取结果都是一致的，除非是本身事物修改的，可以阻止脏读和不可重复读。
• SERIALIZABLE(可串行化)：最高的隔离级别，完全服从ACID的隔离级别，所有的事物依次逐个执行，这样事物之间就完全不会产生干扰。

MySQL默认采用REPEATABLE READ隔离级别，Oracle默认采用READ-COMMITTED隔离级别。

InnoDB在分布式事务的情况下会使用SERIALIZABLE(可串行化)隔离级别。

5.5 MySQL数据库可重复读隔离级别是怎么实现的，MVCC并发版本控制原理

MySQL可重复读是通过MVCC实现的

MVCC(Multi Version Concurrency Control)，代表多版本并发控制。与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control。

MVCC最大的优势：读不加锁，读写不冲突。在读多写少的OLTP(联机事务处理)应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能。

MVCC是通过在每一行记录后边加两个隐藏的列来实现的，这两个列，一个保存了行的创建时间，一个保存了行的过期时间(删除时间)。当然，存储的并不是实际的时间值，而是系统版本号(system version number)。没开始一个新的事务，系统版本号就会自动递增，事务开始的系统号会作为事物的版本号，用来和查询到的每行记录的版本号进行比较。

InnoDB MVCC 实现原理

InnoDB 中 MVCC 的实现方式为：每一行记录都有两个隐藏列：DATA_TRX_ID、DATA_POLL_PTR(如果没有主键，则还会对一个隐藏的主键)。

DATA_TRX_ID

记录最近更新这条记录的事务ID，大小为6个字节

DATA_POLL_PTR

表示指向该行回滚段的指针，大小为7个字节，InnoDB便是通过这个指针找到之前版本的数据。改行记录上所有的旧版本，在undo中都通过链表的形式组织。

DB_ROW_ID

行标识(隐藏单调递增ID)，大小为6个字节，如果表没有主键，InnoDB会自动生成一个隐藏主键，因此会出现这个列。另外，每条记录的头信息里都有一个专门的bit来表示当前记录是否已经被删除。

增删改查

INSERT

insert into user(id, name) values(1,'Lili');

id	name	create_version	delete_version
1	Lili	1

SELECT(事务A)

select * from user where id = 1;

此时读到的版本号是1

UPDATE(事务B)

update user set name = 'Jasper' where id = 1;

在跟新操作的时候，该事务的版本号在原来的基础上加1，所以版本号为2。先将要根性的这条数据标记为已删除，并且删除版本号为当前事务的版本号，然后插入新的记录

id	name	create_version	delete_version
1	Lili	1	2
2	Jasper	2

SELECT(事务A)

此时事务A在重新读取数据

select * from user where id = 1;

由于事务A一直没提交，所以此时独到的版本还是1，读到的是Lili这条数据，这就是可重复读。

DELETE

delete from user where id = 1;

在删除操作的时候，该事务的版本号在原来的基础上加1，所以版本号为3的删除，将当前版本号作为删除版本号。

id	name	create_version	delete_version
1	Jasper	2	3

六 锁

6.1 什么是MySQL的锁

当数据库有并发任务时，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

6.2 隔离级别与锁的关系

在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突。

在Read Committed级别下，读取需要加共享锁，在语句执行完成后释放共享锁。

在Repeatable Read级别下，读操作需要加共享锁，事务执行完毕后才释放共享锁。

在SERIAZABLE级别下，是限制性最强的隔离级别，该级别下锁定整个范围的键，并一直持有锁，知道事务完成。

6.3 按照锁的粒度分数据库锁有哪些？

咋关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(InnoDB引擎)、表级锁(MyISAM引擎)和页级锁(BDB引擎)。

MyISAM和InnoDB引擎使用的锁：

• MyISAM采用表级锁(table-level locking)。
• InnoDB支持行级锁(row-level locking)和表级锁，默认采用行级锁。

行级锁，表级锁和页级锁对比

行级锁：行级锁是MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但开销也越大。行级锁分为共享锁和排他锁。

特点：锁定粒度最小，对当前操作行记录加锁，发生锁冲突的概率最低，并发度页最高。加锁开销的，加锁慢，会出现死锁。

表级锁：表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表进行加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持，最常使用的InnoDB和MyISAM都支持。表级锁定分为表共享读锁(共享锁)和表独占写锁(排他锁)

特点：锁定粒度大，对当前操作的整张表加锁，发生锁冲突的概率最高，并发度最低，加锁开销小，加锁快，不会出现死锁。

页级锁：页级锁是MySQL中锁定粒度介于中等的一种锁，行级锁速度慢，冲突少，表级锁速度快但冲突多，所以有了两种所得折中的锁，一次锁定当前页的一组记录。

特点：开销和枷锁时间中等，会出现死锁，锁定粒度介于两者之间，并发度一般。

6.4 共享锁和排他锁的区别

共享锁

共享锁 share lock 又称读锁 read lock ，简称s锁，是读取操作创建的锁，其他用户可以并发读取数据，但任何事物都不能对数据进行修改(获取数据上的排他锁)，直到以释放所有的共享锁。

如果事务T对数据A加上了共享锁后，其他事务只能对A再加共享锁，不能加排他锁。获得共享锁的事务只能读取数据，不能修改数据。

读取为什么要加锁：防止数据在被读取时被别的线程加上了锁。

使用方式：在需要执行的语句后面加上 for update 就可以了。

排他锁

排他锁 exclusive lock 又称写锁 write lock，简称x锁。排他锁是悲观锁的一种实现。

若事务T对数据A加上了排他锁，则只允许事务T读取和修改数据A，其他任何事物都不能对A加任何类型的锁，直到事务T释放X锁，排他锁会阻塞所有的排他锁和共享锁。

6.5 数据库的乐观锁和悲观锁是什么？怎么实现的？

数据库管理系统中的并发控制的任务是确保咋多个事务同时存取统一数据是不破换事物的隔离性、一致性以及数据库的统一性。乐观并发控制(乐观锁)和悲观并发控制(悲观锁)是并发控制主要采用的技术手段。

悲观锁：假定会发生冲突，认为每次查询的时候都会修改数据，每次查完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制。

乐观锁：假设不会发生冲突，认为每次只是查询不修改数据，所以不会上锁，再修改数据的时候才把事务锁起来。实现方式：乐观锁一般会使用版本号机制或CAS算法实现。

两种锁的使用场景

两种锁各有优缺点，不可认为其中一种好于另一种，像乐观锁适用于多读场景，即冲突真的很少发生，这样节省了所得开销。

如果是多写的场景下，一般会产生冲突，这就会到这上层应用会不断的进行retry，这样反而是降低了性能，所以一般多写的场景使用悲观锁比较合适。

七 SQL优化

7.1 如何定位及优化SQL语句的性能问题？创建的索引有没有被使用到？或者说怎么样才可以知道这条语句运行很慢的原因？

对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划，MySQL提供了explain命令查看语句的执行计划。我们知道，不管是哪种数据库，或者是那种存储引擎，正在对一条SQL语句进行执行的过程中都会做很多相关的优化，对于查询语句，最重要的优化方式就是使用索引。而执行计划，就是显示数据库引擎对SQL语句执行的详细情况，其中包含了是否使用了索引，使用什么索引，使用的索引的相关信息等。

EXPLAIN SELECT
	test.username,
	test_info.age,
	test_info.address 
FROM
	test
	INNER JOIN test_info ON test.id = test_info.parent_id;

执行计划包含的信息：

id:由一组数字组成。表示一个查询中各个子查询的执行顺序

• id相同执行顺序由上至下。
• id不同，id越大优先级越高，越先被执行。
• id为NULL是，表示一个合并集的操作的执行id为NULL，常出现在包含union等查询语句中。

select_type:每个子查询的查询类型，一些常见的查询类型。

id	select_type	description
1	SIMPLE	不包含任何子查询或union查询
2	PRIMARY	包含子查询时最外层查询就显示为PRIMARY
3	SUBQUERY	在select或where子句中出现的子查询
4	DERIVED	from自居中出现的子查询
5	UNION	union连接的两个select查询，第一个查询的是dervied派生表，除了第一个表外，第二个以后的表select_type都是union
6	UNION RESULT	包含union的结果集，在union和union all语句中，因为它不需要参与查询，所以id字段为NULL
7	dependent subquery	与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
8	dependent union	与union一样，出现在union或union all语句中，但是这个查询要受到外部查询的影响

table:显示的查询表名，如果查询使用了别名，那么这里显示的就是别名

type:访问类型(非常重要 可以看到有没有走索引)

type	description
system	表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是innodb引擎表，type列在这个情况下通常都是all或者index。
const	使用唯一索引或者主键，返回记录是1行记录的等值where条件时，通常type时const。其他数据库也叫做唯一索引扫描。
eq_ref	出现在要连接多张表的查询计划，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须是not null，唯一索引和主键是多列时，只会所有的列都用作比较是才会出现eq_ref.
ref	像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见于普通索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。
fulltext	全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。
ref_or_null	与ref方法类似，只是增加了null值的比较。实际用的不多。
unique_subquery	用于where中的in形式子查询，子查询返回不重复值唯一值。
index_subquery	用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
range	索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
index_merge	表示查询使用了两个以上的索引，最后取交集或者并集。常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能都不如range。
index	索引全表扫描。把索引从头到尾扫一遍，常见于使用索引列就可以处理，不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
ALL	*全表扫描数据文件

possible_keys:可能使用的索引，注意不一定会使用，查询涉及到的字段上若存在索引，则该索引将被列出来，当该列为NULL时就要考虑当前SAL是否需要优化了。

key:显示MySQL在查询中实际使用过的索引，若没有索引，显示为NULL。

key_length:索引长度

ref:表示上鼠标的连接匹配条件，即那些列或常量被用于查找索引列上的值。

rows:这里是执行计划中估算的扫描行数，不是精确值。

extra:

1. Using index 使用覆盖索引
2. Using where 使用了where子句来过滤结果集
3. Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
4. Using temporary 使用临时表

7.2 SQL优化目标

参考阿里开发手册

SQL性能优化的目标：至少要达到range级别，要求时ref级别，如果是const最好。

说明：

1. consts单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据。
2. ref指的是使用普通的索引(normal index)
3. range对索引进行范围检索

7.3 SQL的生命周期？一条SQL查询语句是如何执行的？MySQL总体架构–>SQL执行流程–>语句执行顺序

MySQL逻辑架构图

-- 比如有一张简单的表，表里只有一个id字段，在执行下面这个查询语句时
select * from T where id=10;

7.3.1 MySQL的框架有几个组件, 各是什么作用? Server层和存储引擎层各是什么作用？

大体来说，MySQL可以分为Server层和存储引擎层两部分。

Server层包括连接器、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数(如日期、时间、数字和加密函数等)，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、Memory等多个存储引擎。现在最常用的存储引擎式InnoDB，它从MySQL 5.5.5 版本开始成为了默认存储引擎。

7.3.2 SQL执行流程，SQL的生命周期？

连接器

第一步，客户端与数据库server层的连接器进行连接。连接器负责跟客户端建立连接、获取权限、维持和管理连接。

查询缓存

连接建立好之后，会判断查询缓存是否开启，如果已经开启，会判断SQL语句是查询还是修改，如果是select的话，尝试去缓存中查询，如果命中，直接返回数据给客户端，如果缓存没有命中，或者没有开启缓存，进入下一步。

分析器

分析器进行词法分析和语法分析，分析器会先做词法分析，分析SQL中的字符串分别是什么，校验数据库表和字段是否存在，然后进行语法分析，判断SQL是否满足SQL语法。

优化器

优化器对SQL执行计划分析，得到最终执行计划，得到优化否的执行计划交给执行器。

优化器是在表里面有多个索引时，决定使用哪个索引，或者在一个语句中有多表关联的时候，决定各个表的连接顺序。

执行器

开始执行的时候，要先判断一下你对这个表有没有执行查询的权限，如果没有，就会返回没有权限的错误，如果有权限，之想起调用存储引擎api执行SQL，得到响应结果，将结果返回给客户端，如果缓存是开启的，会更新缓存。

详细逻辑架构图

1. 连接：应用服务器与数据库服务器建立一个连接
2. 获取请求SQL：数据库进程拿到SQL
3. 查询缓存：如果查询命中直接返回数据
4. 语法解析和预处理：首先通过mysql关键字将语句解析，会生成一个内部解析树，mysql解析器将对其解析，查看是否是有错误的关键字，关键字顺序是否正确；预处理器则是根据mysql的规则进行进一步的检查，检查mysql语句是否合法，如库表是否存在，字段是否存在，字段之间是否模棱两可等等，预处理器也会验证权限。
5. 查询优化器：sql语句在优化器中转换成执行计划，一条sql语句可以有多种方式查询，最后返回的结果肯定是相同，但是不同的查询方式效果不同，优化器的作用就是：选择一种合适的执行计划。mysql是基于成本的优化器，他将预测执行此计划的成本，并选择成本最小的那条
6. 执行计划：在解析和优化后，MySQL将生成查询对应的执行计划，由执行计划调用存储引擎的API来执行查询
7. 将结果返回给客户端
8. 关掉连接，释放资源

7.3.3 一条更新语句的执行流程又是怎样的呢？

-- 如果要将 id=2 这一行的值加1
update T set c=c + 1 where id = 2; 

你执行语句前要先连接数据库，这是连接器的工作。

前面我们说过，在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表 T 上所有缓存结果都清空。这也就是我们一般不建议使用查询缓存的原因。

接下来，分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用 ID 这个索引。然后，执行器负责具体执行，找到这一行，然后更新。

与查询流程不一样的是，更新流程还涉及两个重要的日志模块，它们正是我们今天要讨论的主角：redo log（重做日志）和 binlog（归档日志）。

在 MySQL 里也有这个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。

而粉板和账本配合的整个过程，其实就是 MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事。

如果今天赊账的不多，掌柜可以等打烊后再整理。但如果某天赊账的特别多，粉板写满了，又怎么办呢？这个时候掌柜只好放下手中的活儿，把粉板中的一部分赊账记录更新到账本中，然后把这些记录从粉板上擦掉，为记新账腾出空间。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

要理解 crash-safe 这个概念，可以想想我们前面赊账记录的例子。只要赊账记录记在了粉板上或写在了账本上，之后即使掌柜忘记了，比如突然停业几天，恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。

重要的日志模块：binlog

前面我们讲过，MySQL 整体来看，其实就有两块：一块是 Server 层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。上面我们聊到的粉板 redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。

我想你肯定会问，为什么会有两份日志呢？

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

这两种日志有以下三点不同。

1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
2. redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
3. redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

7.3.4 常用SQL查询语句优化方法

• 不要使用select * from table，用具体的字段来代替 * ，使用星号会降低查询效率，如果数据库字段改变，可能出现不可预知隐患，
• 应尽量避免在where子句中使用 != 或< >操作符，避免在where子句中字段进行null值判断，存储引擎将放弃使用索引而进行全局扫描。
• 避免使用左模糊，左模糊查询将导致全表扫描。
• IN语句查询时包含的值不应过多，否则将导致全表扫描。
• 为经常作为查询条件的字段、经常需要排序的字段、分组操作的字段建立索引。
• 在使用联合索引字段作为条件时，应遵守左前缀原则。
• OR前后两个条件都要由索引，整个sql才会有索引。
• 尽量用union all 代替 union，union需要将结果集合后在进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗。

八 数据库优化

8.1 数据库结构优化

一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。

需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

将字段很多的表分解成多个表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。

通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

增加冗余字段

设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。

表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

注意：

冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

8.2 大表怎么优化？某个表有近千万数据，CRUD比较慢，如何优化？分库分表是怎么做的？分表分库了有什么问题？有用到中间件么？他们的原理知道么？

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

1. 限定数据的查询范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：当用户在查询订单历史的时候，我们可以控制在一个月的范围内；
2. 读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读；
3. 缓存： 使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；
4. 分库分表

分库分表主要有垂直分表和水平分表

1. 垂直分表：

   垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。如下图所示，这样来说大家应该就更容易理解了。

   

   适用场景：如果一个表中某些列常用，另外一些列不常用

   垂直拆分的优点：可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

   垂直拆分的缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。对于应用层来说，逻辑算法增加开发成本。此外，垂直分区会让事务变得更加复杂；

2. 水平分表：

   保持数据表结构不变，通过某种策略进行存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。

   水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

   

   水平拆分可以支持非常大的数据量。需要注意的一点是：水平分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库 。

   适用场景：支持非常大的数据量存储

   水平拆分优点：支持非常大的数据量存储

   水平拆分缺点：给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需UNION操作；分片事务难以解决 ，跨库join性能较差，逻辑复杂。

《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。

下面补充一下数据库分片的两种常见方案：

• 客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
• 中间件代理： 在应用和数据库中间加了一个代理层。分片逻辑统一维护在中间件服务中。 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

8.3 MySQL的主从复制原理以及流程

主从复制：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行，从而使得从数据库的数据与主数据库保持一致。

主从复制的作用

1. 高可用和故障切换：主数据库出现问题，可以切换到从数据库。
2. 负载均衡：可以进行数据库层面的读写分离。
3. 数据备份：可以在从数据库上进行日常备份。

复制过程

Binary log：主数据库的二进制日志

Relay log：从数据库的中继日志

第一步：master在每个事务更新数据完成之前，将该操作记录串行地写入到binlog文件中。

第二步：salve开启一个I/O Thread，该线程在master打开一个普通连接，将这些事件写入到中继日志中。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。

第三步：SQL Thread会读取中继日志，并顺序执行该日志中的SQL事件，从而与主数据库中的数据保持一致。

8.4 读写分离有哪些方案？

读写分离是依赖于主从复制，而主从复制又是为读写分离服务的。主从复制要求slave不能写只能读

方案一

利用中间件来做代理，使用mysql-proxy代理，负责对数据库的请求识别出读还是写，并分发到不同的数据库中。

优点：直接实现读写分离和负载均衡，不用修改代码，数据库和应用程序弱耦合，master和slave用一样的帐号，mysql官方不建议实际生产中使用

缺点：降低性能， 不支持事务，代理存在性能瓶颈和可靠性风险增加。

方案二

使用AbstractRoutingDataSource+aop+annotation在dao层决定数据源。

如果采用了mybatis， 可以将读写分离放在ORM层，比如mybatis可以通过mybatis plugin拦截sql语句，所有的insert/update/delete都访问master库，所有的select 都访问salve库，这样对于dao层都是透明。plugin实现时可以通过注解或者分析语句是读写方法来选定主从库。

不过这样依然有一个问题， 也就是不支持事务， 所以我们还需要重写一下DataSourceTransactionManager， 将read-only的事务扔进读库， 其余的有读有写的扔进写库。

方案三

使用AbstractRoutingDataSource+aop+annotation在service层决定数据源，可以支持事务

缺点：类内部方法通过this.xx()方式相互调用时，aop不会进行拦截，需进行特殊处理。

九 大数据量处理

9.1 大批量数据删除，怎么一次删除100万条数据

方法一：

在MySQL数据库使用中，有的表存储数据量比较大，达到每天三百万条记录左右，此表中建立了三个索引，这些索引都是必须的，其他程序要使用。由于要求此表中的数据只保留当天的数据，所以每当在凌晨的某一时刻当其他程序处理万其中的数据后要删除该表中昨天以及以前的数据，使用delete删除表中的上百万条记录时，MySQL删除速度非常缓慢每一万条记录需要大概4分钟左右，这样删除所有无用数据要达到八个小时以上，这是难以接受的。

查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的，于是删除掉其中的两个索引后测试，发现此时删除速度相当快，一百万条记录在一分钟多一些，可是这两个索引其他模块在每天一次的数据整理中还要使用，于是想到了一个折中的办法：

在删除数据之前删除这两个索引，此时需要三分钟多一些，然后删除其中无用数据，此过程需要不到两分钟，删除完成后重新创建索引，因为此时数据库中的数据相对较少，约三四十万条记录(此表中的数据每小时会增加约十万条)，创建索引也非常快，约十分钟左右。这样整个删除过程只需要约15分钟。对比之前的八个小时，大大节省了时间。

删除大表的部分数据，通常采用以下步骤：

删除大表的多行数据时，会超出innod block table size的限制，最小化的减少锁表时间的方案是：

1、选择不需要删除的数据，并把它们存在一张相同结构的空表里

2、重命名原始表，并给新表命名为原始表名

3、删掉原始表

方法二：

分批删除，如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：

• 第一种，直接执行 delete from T limit 10000;
• 第二种，在一个连接中循环执行 20 次 delete from T limit 500;
• 第三种，在 20 个连接中同时执行 delete from T limit 500。

方案一，单个语句占用锁的时间较长，会导致其他客户端等待资源时间较长。

方案二，串行化执行，将相对长的事务分成多次相对短的事务，则每次事务占用锁的时间相对较短，其他客户端在等待相应资源的时间也较短。这样的操作，同时也意味着将资源分片使用（每次执行使用不同片段的资源），可以提高并发性。

方案三，人为制造锁竞争，加剧并发。

方案二相对比较好，具体还要结合实际业务场景。

9.2 MySQL大数据量分页查询方法及其优化

limit偏移量不变，随着查询记录量越来越大，所花费的时间也会越来越多。

limit查询记录数不变，随着查询偏移的增大，尤其查询偏移大于10万以后，查询时间急剧增加。

原因分析

select * from user where sex = 1 limit 100,10

由于 sex 列是索引列，MySQL会走 sex 这棵索引树，命中 sex=1 的数据。

然后又由于非聚簇索引中存储的是主键 id 的值，且查询语句要求查询所有列，所以这里会发生一个回表的情况，在命中 sex 索引树中值为1的数据后，拿着它叶子节点上的值也就是主键 id 的值去主键索引树上查询这一行其他列（name、sex）的值，最后返回到结果集中，这样第一行数据就查询成功了。

最后这句 SQL 要求limit 100, 10，也就是查询第101到110个数据，但是 MySQL 会查询前110行，然后将前100行抛弃，最后结果集中就只剩下了第101到110行，执行结束。

小结一下，在上述的执行过程中，造成 limit 大偏移量执行时间变久的原因有：

• limit a, b会查询前a+b条数据，然后丢弃前a条数据

MySQL数据库的查询优化器是采用了基于代价的方式，而查询代价的估算是基于CPU代价和IO代价。如果MySQL在查询代价估算中，认为全表扫描方式比走索引扫描的方式效率更高的话，就会放弃索引，直接全表扫描。

优化方式：

t5表有200万数据，id为主键，text为普通索引

使用覆盖索引

如果一条SQL语句，通过索引可以直接获取查询的结果，不再需要回表查询，就称这个索引为覆盖索引。

在MySQL数据库中使用explain关键字查看执行计划，如果extra这一列显示Using index，就表示这条SQL语句使用了覆盖索引。

让我们来对比一下使用了覆盖索引，性能会提升多少吧。

没有使用覆盖索引

select * from t5 order by text limit 1000000, 10;

这次查询花了3.690秒，让我们看一下使用了覆盖索引优化会提升多少性能吧。

使用了覆盖索引

select id, `text` from t5 order by text limit 1000000, 10;

从上面的对比中，超大分页查询中，使用了覆盖索引之后，花了0.201秒，而没有使用覆盖索引花了3.690秒，提高了18倍多，这在实际开发中，就是一个大的性能优化了。

子查询优化

因为实际开发中，用SELECT查询一两列操作是非常少的，因此上述的覆盖索引的适用范围就比较有限。

所以我们可以通过把分页的SQL语句改写成子查询的方法获得性能上的提升。

select * from t5 where id>=(select id from t5 order by text limit 1000000, 1) limit 10;

其实使用这种方法，提升的效率和上面使用了覆盖索引基本一致。

但是这种优化方法也有局限性：

• 这种写法要求主键ID必须是连续的
• Where子句不允许再添加其他条件

延迟关联

和上述的子查询做法类似，我们可以使用JOIN，先在索引列上完成分页操作，然后再回表获取所需要的列。

select a.* from t5 a inner join (select id from t5 order by text limit 1000000, 10) b on a.id=b.id;