文章目录

1. 数据库设计

1.2 数据类型

在mysql中存在三类数据类型，分别为数值型、字符型和日期型。在这里要注意字符型和日期型。

1.2.1 char和varchar的区别

为了更好的利用存储空间，MySQL将普通字符类型划分为char和varchar，其中char是一种固定长度的类型，varchar则是一种可变长度的类型。

  • 存储长度不同

  • char是一种固定长度的类型，char（M）类型的数据列中，每个值都占用M个字节，不足的则填充空格

    • varchar是可变长度的类型，长度为实际占用内存+记录长度的1个字节

  • 存储范围不同

  • char的长度可选范围为0~255之间

    • varchar的长度可选范围在0~65535之间

  • 数据行长度区别：如果某个数据表里的数据行的长度是可变的，那么，为了节约存储空间，MySQL会把这个数据表里的固定长度类型的数据列转换为相应的可变长度类型

  • 查询效率不同：因为char的存储长度固定，所以在寻址时很快，而varchar的长度不固定，需要遍历

1.2.2 varchar（100）和varchar（10）的区别

虽然varchar（100）和varchar（10）在存储相同数据时占据的存储空间是相同的，但是对于内存的消耗是不同的。mysql会使用固定大小的内存块（指定的字符长度）来保存值，而这对于排序或者临时表（这些内容都需要通过内存来实现）作业会产生比较大的不利影响。

因为当MySql创建临时表（SORT，ORDER等）时，VARCHAR会转换为CHAR，转换后的CHAR的长度就是varchar的长度，在内存中的空间就变大了，在排序、统计时候需要扫描的就越多，时间就越久。

1.2.3 varchar能存储多少汉字、数字？

  • UTF8编码中一个汉字（包括数字）占用3个字节

  • GBK编码中一个汉字（包括数字）占用2个字节

1.2.4 blob和text的区别

字符型除了char和varchar以外，还提供了blob和text类型，两者都用来存储大字符串。

  • text存储的是非二进制，不能存储图片
  • blob存储的是二进制数据

2. 标准MySQL语言

2.1 解释MySQL外连接、内连接与自连接的区别

  • 交叉连接: 交叉连接又叫笛卡尔积，它是指不使用任何条件，直接将一个表的所有记录和另一个表中的所有记录一一匹配。

  • 内连接 则是只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合条件的记录不会出现在结果集中，即内连接只连接匹配的行。

  • 外连接 其结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个表中的所有数据行，这三种情况依次称之为左外连接，右外连接，和全外连接。

    • 左外连接，也称左连接，左表为主表，左表中的所有记录都会出现在结果集中，右表只会显示匹配的记录，右边对应的那些字段值以NULL来填充。
    • 右外连接，也称右连接，右表为主表，右表中的所有记录都会出现在结果集中，左表只会显示匹配的记录。
    • 左连接和右连接可以互换，MySQL目前还不支持全外连接。 
# 交叉连接
CROSS JOIN
# 内连接
inner join
# 左外链接
left out join
# 右外连接
right out join

2.2 一些关键字的使用

2.2.1 exist比in更快

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in，

反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。

其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。

2.2.2 union与union all的区别

  • union all只是合并查询结果，并不会进行去重和排序操作，在没有去重的前提下，使用union all的执行效率要比union高

3. 数据安全性

3.1 怎么实现数据安全？

  • 首先在使用数据库前，用户需要进行身份验证，验证通过后才能对数据库进行操作
  • 其次，由于数据库有很多个模式和表，为此MySQL引入了存取控制策略，通过限制用户对表的操作权限，实现更高效的，实现了粒度更小的权限控制。
  • 此外，数据库还提供了数据加密功能
  • 另外，数据库在关系表之上，还提供了视图功能实现字段的访问控制。

3.2 授权和取消授权的一般操作

# 授权
### 使用户能够访问某表（无法作用在数据库上）
GRANT SELECT ON test_2.`student_2` TO '123'；
### 使用户只能修改某表的某列
GRANT UPDATE（Sage） ON test_2.`student_2` TO '123'；

# 取消授权
REVOKE SELECT ON test_2.`student_2` FROM '123'；
REVOKE UPDATE（Sage） ON test_2.`student_2` FROM '123'；

3.3 什么叫视图？游标是什么？

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

游标是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。

一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

4. 数据库完整性

  • 实体完整性：通过主键来标识某个独一无二的实体
  • 参照完整性：通过外键标识某两个实体之间的联系
  • 用户定义的完整性：针对某一具体字段的数据要求

4.1 主键的创建

# 建表时：列约束
CREATE TABLE student_6
（
  `Sno` CHAR PRIMARY KEY,
  `Sname` VARCHAR（20） UNIQUE,
） ENGINE = INNODB  AUTO_INCREMENT = 0 DEFAULT CHARSET=utf8 COMMENT='学生表'；

# 建表时：表约束
CREATE TABLE student_7
（
  `Sno` CHAR,
  `Sname` VARCHAR（20） UNIQUE,
  PRIMARY KEY（Sno,Ssex）
） ENGINE = INNODB  AUTO_INCREMENT = 0 DEFAULT CHARSET=utf8 COMMENT='学生表'；

4.2 外键的创建

## 建表时添加外键约束
CREATE TABLE `student_3` （
 `Sno` CHAR PRIMARY KEY,
 `Sname` VARCHAR（20） UNIQUE,    
 `teacher_id` INT（11） ,
 FOREIGN KEY（teacher_id） REFERENCES teacher（Sno）
）
ENGINE= INNODB 
DEFAULT CHARSET = utf8；


## 修改表时添加外键约束
ALTER TABLE student_2 ADD FOREIGN KEY（teacher_id） REFERENCES teacher（Sno）；

4.3 用户自定义的完整性的创建

有NOT NULL、UNIQUE、DEFAULT、auto_increment、zerofill、unsigned等

# 属性上的
CREATE TABLE `test_1`（
 `id` INT（11） PRIMARY KEY AUTO_INCREMENT,
 `name` VARCHAR（22） UNIQUE NOT NULL,
 `sex` VARCHAR（11） DEFAULT '男',
 `age` INT（11） CHECK（age>=10 AND age <= 30）
） ENGINE=INNODB DEFAULT CHARSET=utf8；

# 更新属性时修改约束
ALTER TABLE test_1 CHANGE COLUMN `name` `namet` VARCHAR（22） UNIQUE NULL；
# 直接修改属性约束
ALTER TABLE test_1 MODIFY COLUMN `namet` VARCHAR（11） UNIQUE NOT NULL；

以上讲解的完整性约束条件都是未命名定义，MySQL还提供了完整性约束命名子句CONSTRAINT，从而可以更灵活地增加、删除一个完整性约束条件。

4.4 触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊过程，其类似于约束，但比约束更灵活，可是实施更精细更复杂的检查和操作。

触发器定义在数据表上，在sql语句执行前/后触发，针对每一行执行，因此在对增删改查频繁的表上不建议使用触发器，除非确定触发器是非常高效的。

5. 数据库编程

5.1 什么是嵌入式SQL？

嵌入式SQL（英文: Embedded SQL）是一种将SQL语句直接写入C语言，COBOL，FORTRAN, Ada等编程语言的源代码中的方法。借此方法，可使得应用程序拥有了访问数据以及处理数据的能力。在这一方法中，将SQL文嵌入的目标源码的语言称为宿主语言。

5.2 什么是过程化SQL

基本的SQL是高度非过程化的语言。嵌入式SQL将SQL语句嵌入程序设计语言，借助高级语言的控制功能实现过程化。过程化SQL是对SQL的扩展，使其增加了过程化语句功能。
过程化SQL程序的基本结构是块。所有的过程化SQL程序都是由块组成的。这些块之间可以相互嵌套，每个块完成一个逻辑操作。

5.3 什么是存储过程？用什么来调用？

答：存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。

5.4 什么是函数？怎么调用函数？

6. 并发控制

在日常使用数据库时，存在多个线程操作同一个表，我们知道对数据库的操作不可能只有一下，可能多次读写，这些操作在多线程并发时容易互相影响，造成不可预料的错误结果。为此，MySQL引入了事务机制。

6.1 什么是事务？

6.1.1 ACID属性

事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，事务满足以下四个属性：

  • Atomicity（原子性）：事务是一个不可分割的工作单元，要么全部完成，要么一个都不做
  • Consistency（一致性）：事务必须使数据库从一个一致性状态变化到另一个一致性状态，比如转账问题
  • Isolation（隔离性）：一个事务的执行不能被其他事务干扰
  • Durability（持久性）：事务一般被提交，它对数据库中数据的改变是永久的

6.1.2 隔离级别

当多个线程都开启事务操作数据库中的数据时，数据库系统要能进行隔离操作，以保证各个线程获取数据的准确性，在介绍数据库提供的各种隔离级别之前，我们先看看如果不考虑事务的隔离性，会发生的几种问题：

  • 脏读：事务1读取了事务2还未提交的数据，当事务2回滚时，事务1读取的内容就是无效的
  • 不可重复读：事务中多次读取同一字段的值并不同，因为被其他事务更改了
  • 幻读：事务1从表中读取了一个字段，事务2插入新行，事务1再读同一个表则多出几行了

为了解决上面三种问题，数据库MySQL为我们提供了四种隔离级别：

  • READ UNCOMMITTED（读未提交），最低隔离级别；

  • READ COMMITTED（读已提交），可避免脏读；

  • REPEATABLE READ（可重复读），默认，可避免脏读和不可重复读

  • SERIALIZABLE（串行化），均可避免；

命令操作

select @@tx_isolation;	# 查看数据隔离级别
set session/globals transaction isolation level XXXXXX; # 更改隔离级别

savepoint 节点名  # 设置保存点
rollback 保存点名 # 回滚到保存点

6.2 什么是MVCC

前面提到了事务和事务的隔离级别，那么四种隔离级别是怎么实现的呢？RU是最低的，不用实现，而RC和RR是通过MVCC机制实现的，SE则是通过锁机制实现的。

6.2.1 MVCC机制

MVCC（multiversion concurrency controll）是一种多版本并发控制机制，大家知道，锁机制能够控制并发操作，但是其系统开销大，而MVCC可以在大多数的情况下代替行锁，从而降低系统开销。

为了实现在并发下保存多个数据版本，MySQL利用日志实现了当前读和快照读，不同的事务根据要求获取到的读取结果实际上区分为当前读和快照读。

**当前读：**读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

**快照读：**当前读取的并不是记录的最新版本，而可能是之前某几个版本的快照

那么在MySQL底层到底是怎么实现快照读的呢？还有怎么实现事务的隔离级别的呢？

6.2.2 MVCC底层实现

MVCC的目的就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的。

隐式字段：

实际上，在数据库底层数据结构中，每条记录不仅有用户定义的字段，还有数据库隐式定义的三个字段，这些字段记录了最近修改的事务ID、回滚指针（指向记录的上一个版本）、删除标记（标记记录是否被删除）、隐藏主键。

当事务1执行到写数据时，数据被更新（隐式字段的事务ID更新），此时其他事务就无法读取到更新值了，他们只能读取到历史版本，那么历史版本存在于哪呢？ 答案是undo日志

undo日志：

undo日志中记录的是数据记录的历史版本，记录也含有更新其的事务ID号。因此undo日志中可能存在某条记录的多个历史版本。那么问题来了，由于undo日志中存在多个版本记录，某个事务该怎么选择呢？

Read view读视图：

读视图就是系统在事务执行快照读时为事务产生一个读视图，视图中记录并维护当前活跃事务的ID，通过读视图，事务就能够判断该读undo日志中的哪一条记录。

记录 含义
m_ids 当前活跃的事务id列表
min_trx_id 当前系统中活跃的读写事务中最小的事务id
max_trx_id 表示生成ReadView时系统中应该分配给下一个事务的id值。
creator_trx_id 表示生成该ReadView的事务的事务id。

访问判断

判断 含义 访问
数据trx_id == creator_trx_id 当前事务修改的 可以访问
trx_id < min_trx_id 已经提交的事务修改的 可以访问
trx_id >= max_trx_id 之后产生的事务修改的 不可访问
min_trx_id <= trx_id <= max_trx_id 可能存在，需要在m_ids查找 存在则不可访问

如果当前数据版本不可访问时，沿着版本链找到下一个版本的数据，直到最后。如果还没找到说明查询不到该条记录。

既然MVCC是通过undo日志实现快照读的，那么undo日志会无限增长吗？如果不能怎么清理历史数据呢？

purge线程：

purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的deleted_bit为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的。

6.2.3 MVCC实现事务隔离

在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。

  • READ COMMITTED —— 每次读取数据前都生成一个ReadView，能够读到最新的版本数据，而不至于读到脏数据
  • REPEATABLE READ —— 在第一次读取数据时生成一个ReadView，只能读到第一次读到的数据，避免被其他事务影响

6.3 MySQL中的锁机制

在事务并发时，还存在幻读，幻读产生原因是事务修改了其他数据，而MVCC只是实现了记录的并发版本控制，所以需要一种新的机制来解决幻读，也就是下面要说的锁机制。

我们知道，MySQL数据在存储中是分为区和页，因此为了实现更高效的锁机制，MySQL的InnD引擎引入了多种锁：行锁，表锁。而BDB还引入了页锁。

6.3.1 锁到底锁住了什么？

实际上，数据库中的锁是锁住索引项实现的！对于innodb而言：

  • 表锁锁住的是整个聚簇索引，如果未建立主键索引或唯一索引时，锁住MySQL默认创建的索引
  • 行锁锁住的是唯一索引或主键索引，当未定义此索引时，默认锁住整个表。

6.3.2 为什么MyISAM不支持行锁？

前面提到，InnoDB和MyISAM在底层数据结构上是不同的，InnoDB索引和数据存储在一个文件中，而MyISAM索引存储的是指向真实物理地址的指针。

因此在InnoDB中，可以通过锁住索引项来封锁数据，而MyISAM不行（可能存在多个索引指向通一个物理地址）。

6.3.3 行锁的实现分类

行锁在使用上可以分为以下三种：

  • 记录锁：总是会锁住索引记录。
    • 生效：当使用唯一性索引进行等值查询、精准匹配时并请求共享锁或排他锁时
  • 间隙锁： 只在RR级别存在，是为了解决Phantom Problem（幻象/幻读），间隙锁锁定的是一个范围
    • 生效：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时
  • 临键锁： 是结合了记录锁和间隙锁的一种锁定算法，在此种算法下，InnoDB对于行的查询都是采用这种锁定算法。
    • 生效：

6.3.4 锁的思想分类

在实现锁时，为了避免锁带来的阻塞，根据思想将锁分为了共享锁和独占锁，而这两种思想都成功地应用在表锁和行锁之上。

表共享/独占锁

  • **表共享读锁：**给聚簇索引添加表锁后，不会阻塞其他用户对同一表的读取，但会阻塞写请求，直至对该表的共享锁释放

  • 表独占写锁： 添加该锁以后, 则会阻塞其他用户对同一表的读和写操作；直至对表的该锁释放以后才能进行读或者写操作.

实际上，存储引擎（MyISAM）在执行查询语句前，会自动给涉及的所有表加读锁，执行更新操作前，加写锁。

行共享/独占锁

同样，行锁也实现了共享模式和独占模式。

select * from xxx lock in share mode 	# 行共享读锁
select…for update		# 行独占写锁

6.3.5 死锁

上文讲过，MyISAM表锁是不存在死锁的，这是因为MyISAM总是一次获得所需的全部锁，要么全部满足，要么等待。

但在InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，当两个事务都需要获得对方持有的排他锁才能继续完成事务，这种循环锁等待就是典型的死锁。

死锁检测

在InnoDB的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。当InnoDB检测到系统中产生了死锁之后，InnoDB会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚，而让另外一个较大的事务成功完成。（一般通过两个事务操作的数据量大小判断）

锁超时

在并发访问比较高的情况下，如果大量事务因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。我们通过设置合适的锁等待超时阈值，可以避免这种情况发生，一般也可以用此参数来检测是否发生死锁

6.3.6 悲观锁和乐观锁

实现悲观锁：

# 关闭mysql数据库的自动提交属性
set autocommit=0；

# 窗口1：开启事务
BEGIN；
SELECT * FROM tbl_user WHERE id = 1 for update；	# 加排它锁
SELECT * FROM tbl_user WHERE id = 1 LOCK IN SHARE MODE；	# 加共享锁

# 窗口2：
SELECT * FROM tbl_user where id=1 for update；	# 失败

当执行 select ... for update时，将会把数据锁住，因此，我们需要注意一下锁的级别。MySQL InnoDB 默认为行级锁。当查询语句指定了主键时，MySQL会执行「行级锁」，否则MySQL会执行「表锁」（InnoDB的行锁是通过给索引上的索引项加锁实现的，因此，只有通过索引检索数据，才会采用行锁，否则使用的是表锁。）。

  • 若明确指明主键，且结果集有数据，行锁；
  • 若明确指明主键，结果集无数据，则无锁；
  • 若无主键，且非主键字段无索引，则表锁；
  • 若使用主键但主键不明确，则使用表锁；

实现乐观锁：

MySQL并不提供乐观锁实现，一般由用户在更新时会判断其他线程在这之前有没有对数据进行修改，一般会使用版本号机制或CAS操作实现。

update user_infoset password='somelog' where username='somelog' and time='2018-07-11'；

7. 底层结构

7.1 物理实现

7.1.1 段、区、页、行

从MySQL 5.6版本开始innodb_file_per_table参数默认设置为1。该配置下你的每一个表都会单独作为一个文件存储（如果有分区也可能有多个文件）。

文件的结构是不会随着数据行的删除而变化的，但段则会跟着构成它的更小一级单位——区的变化而变化。区仅存在于段内，并且每个区都是固定的1 MB大小（页体积默认的情况下）。页则是区的下一级构成单位，默认体积为16 KB，而一个页可以容纳2-N行（InnoDB要求每一页最少2行）。

7.1.2 为什么要划分这么多等级？

  • 我们知道，段的意义是为了对应特定表，因此将表空间划分为段
  • 划分区：由于一个数据表可能包含上百万条数据，而实际上可能没有那么大的数据块分配，因此以区为单位进行分配，区内部是连续的存储空间，一个表可能包含很多个区块
  • 划分页：我们查询都是先从外存中加载到内存，一般查询数据并没有很多条，所以为了提升加载效率和降低内存使用，我们使用页来作为磁盘管理的最小单位。

7.1.3 页的内部结构

每个页（逻辑上讲即叶子节点）是包含了2-N行数据，根据主键排列。树有着特殊的页区管理不同的分支，即内部节点（INodes）。

所有的B树都有着一个入口，也就是根节点，在上图中#3就是根节点。根节点（页）包含了如索引ID、INodes数量等信息。INode页包含了关于页本身的信息、值的范围等。最后还有叶子节点，也就是我们数据实际所在的位置。在示例中，我们可以看到叶子节点#5有57行记录，共7524 bytes。在这行信息后是具体的记录，可以看到数据行内容。

这里想引出的概念是当你使用InnoDB管理表和行，InnoDB会将他们会以分支、页和记录的形式组织起来。InnoDB不是按行的来操作的，它可操作的最小粒度是页，页加载进内存后才会通过扫描页来获取行/记录。

页的内部原理

页默认大小为16 KB，页可以空或者填充满（100%），行记录会按照主键顺序来排列。当你插入数据时，如果数据（大小）能够放的进页中的话，那他们是按顺序将页填满的。若当前页满，下一行记录则会插入到下一页中

根据B+树的特性，它可以自顶向下遍历，但也可以在各叶子节点水平遍历。因为每个叶子节点都有着一个指向包含下一条（顺序）记录的页的指针。

页合并

当你删了一行记录时，实际上记录并没有被物理删除，记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。

当页中删除的记录达到MERGE_THRESHOLD（默认页体积的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

页分裂

前面提到，页可能填充至100%，在页填满了之后，下一页会继续接管新的记录。但如果有下面这种情况呢？页A记录着20~26行数据（填满），页B记录着28~34行数据，现在要插入第27行数据，怎么办呢？

InnoDB的做法是（简化版）：

  1. 创建新页C
  2. 判断当前页A可以从哪里进行分裂（记录行层面）
  3. 移动记录行到页C
  4. 重新定义页之间的关系（页A->页C->页B）

这样会导致B树水平方向的一致性仍然满足，因为满足原定的顺序排列逻辑。然而从物理存储上讲页是乱序的，而且大概率会落到不同的区。

总结：页分裂会发生在插入或更新，并且造成页的错位（dislocation，落入不同的区）

一旦创建分裂的页，将原先顺序恢复的办法就是利用合并阈值（merge threshold）将新分裂出来的页删掉。这时候InnoDB用页合并将数据合并回来。

另一种方式就是用OPTIMIZE重新整理表。这可能是个很重量级和耗时的过程，但可能是唯一将大量分布在不同区的页理顺的方法。

另一方面，要记住在合并和分裂的过程，InnoDB会在索引树上加写锁（x-latch）。在操作频繁的系统中这可能会是个隐患。它可能会导致索引的锁争用（index latch contention）。如果表中没有合并和分裂（也就是写操作）的操作，称为“乐观”更新，只需要使用读锁（S）。带有合并也分裂操作则称为“悲观”更新，使用写锁（X）。

7.2 索引

7.2.1 什么是索引？

如果在没有索引的情况下，数据被按顺序存放在数据文件中，每次查询数据都要进行全表扫描，非常耗时耗力，因此引入了索引机制，通常情况下通过B+树或hash映射实现索引。

索引（index）是帮助MySQL高效获取数据的数据结构（有序）。数据库系统通过维护索引来加快数据的查找长度，常见的索引有B树索引、hash索引（InnoDB和MyISAM中未实现）。

当然，通过索引能很快的查找到数据，但维护索引也要付出一定代价，一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间。

7.2.2 索引在InnoDB存储引擎中的实现

在InnoDB中，以主键索引来组织数据存储，叶子节点按主键索引排序，索引的顺序和数据存放的顺序一致，这就叫做聚簇索引。InnoDB存储引擎的表只有一个“.ibd”文件，里面存放着B+树形式的索引和数据，数据存放在叶子节点上。

回表

由于InnoDB数据和主键索引存储在一起，因此其他索引里面存放的是指向的主键，当通过其他索引查询数据时，会先查到主键索引，再回聚簇索引树查询结果，此现象称为回表。

隐式聚簇索引

上面提到了查询一定会经过聚簇索引树，那么聚簇索引树是怎么生成的呢？

  • 如果你创建了Pirmary Key，那么这个key就是聚集索引，即存放数据
  • 如果没有主键索引，但是有个唯一索引（unique Key）而且它是NOT NULL，那么就会把这个唯一索引当做存放数据的聚集索引。
  • 第三种情况就是这个表中什么索引都没有，怎么办？其实在InnoDB中它会有一个隐藏的列rowId，它会把这个rowId作为聚集索引，并存放数据。要记住一张表不可能没有索引和聚集索引的。

索引覆盖

覆盖索引就是要找的数据刚好就在辅助索引上，比如，select name from user where name = 'zs'; 如果用name这个字段建立辅助索引时，执行这条语句就不需要回表拿数据了，因为你要查的name字段就在辅助索引上，索引不需要进行回表了。

索引下推

当存在索引的列做为判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器。

比如说，在SELECT * from user where name like '陈%' and age=20时，InnoDB并没有忽略age这个字段，而是在索引内部就判断了age是否等于20，对于不等于20的记录直接跳过，因此在(name,age)这棵索引树中只匹配到了一个记录。

7.2.3 索引在MyISAM存储引擎中的实现

在MyISAM中，数据并不和任何索引存放在同一文件（单独存在），任何索引树中的叶子节点都存放着指向数据地址的指针。当检索时，通过任意索引都可以找到真实数据的物理地址。

MYISAM存储引擎的数据表有两个文件：“.MYD”和“.MYI”，分别用来存储数据和索引。

7.2.4 索引分类

根据索引树的不同实现，可以将索引分为如下几种：

主键索引：

主键索引（primary key）是一种特殊的唯一索引，不允许有空值，一般在建表时指定主键，采用自增策略。

为什么推荐主键索引要有序？

因为B+树是有序的，如果主键乱序，插入B+树时需要移动，也可能产生页分裂，而自增索引只需要在树的最右端插入即可，提高了插入性能。

普通索引：（key或index）

可以加快对数据的访问速度，普通索引允许被索引的数据列包含重复的值。

唯一索引：（unique）

如果能确定某个数据列将只包含彼此各不相同的值，这就是唯一索引，其可以保证数据记录的唯一性。

联合索引：

联合索引可以覆盖多个列，联合索引需要满足最左匹配，否则索引失效。

前缀索引：

有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。

全文索引：

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，**如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。**全文索引就是为这种场景设计的。

  • 全文检索就是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。
  • 它可以根据需要获得全文中有关文章，节，段，句，词等信息，也可以进行统计和分析。

全文索引的底层实现：

倒排索引（请看Elasticsearch实现），倒排索引其实就是根据一个辅助表，表中存储了每个单词出现的文档id，甚至存储其在文档出现的位置。通过这个辅助表进行全文检索。

之前所说，倒排索引需要将word（全文中的关键字）存放到一张辅助表中，这个表称为Auxiliary Table（辅助表）。在InnoDB存储引擎中，为了提高全文检索的并行性能，共有6张Auxiliary Table，目前每张表会根据word的Latin编码进行分区。

Auxiliary Table是持久的表，存放在磁盘上。然而InnoDB为了提高全文检索的性能，引入了全文检索索引缓存（FTS Index Cache）。 其实就是增加了一个缓冲！每次先修改缓冲，再刷新回磁盘。

FTS index cache

FTS Index Cache 是一个红黑树结构，根据（word，ilist）进行排序。也就是说，当我们进行插入操作的时候，我们的数据已经更新了磁盘上的表，但是有可能全文索引的更新还没有刷新回磁盘，仅仅是在缓冲池中修改了。InnoDB会批量的对Auxiliary Table进行更新。这种机制类似于插入缓冲！

分词的插入和删除操作

文档中分词的插入操作是在事务提交时完成的，但是对于删除操作，其事务提交时，不会删除 Auxiliary Table中的记录，而是只删除FTS Index Cache中的记录。对于Auxiliary Table中被删除的记录，InnoDB存储引擎会记录其FTS Document ID，将其保存在DELETED auxiliary中。

7.2.5 创建索引

创建索引：

create table aaa()		# 创建表时添加
alter table 表名 add 索引类型[unique/index/。。。] 索引名(列名)	# 修改表时添加
create [unique] index 索引名 on 表名(列名)# create 添加索引（create只能添加普通索引和唯一索引

删除索引：

drop index 索引名 on 表名;
alter table 表名 drop index 索引名;
alter table table drop primary key;

7.3 数据库存储引擎

7.3.1 InnoDB

InnoDB是MySQL的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。 但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

7.3.2 MyISAM

MyISAM：不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表 。

7.3.3 Memory

Memory存储引擎： 将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件，格式是.frm ，该文件中只 存储表的结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。MEMORY 类型的表访问非常地快，因为他的数据是存放在内存中的，并且默认使用HASH索引 ， 但是服务一旦关闭，表中的 数据就会丢失。

7.3.4 Merge

MERGE存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，MERGE表本身并没有存储数据，对 MERGE类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行的。

8. MySQL执行过程

在进行MySQL优化之前，我们必须了解MySQL语句的执行过程。

8.1 衡量查询开销的指标

  • 响应时间：包括，cpu处理时间和I/O等待时间等。
  • 扫描行数：mysql在本次查询一共扫描了多少行。
  • 返回行数：mysql在本次查询一共返回的行数。

8.2 查询流程

客户端与服务器之间是半双工通信,意味着服务器与客户端之间的传递数据不可以同时发生。

  1. 客户端使用一个单独的数据包将查询传给服务器。当语句过长时，可能受到服务器端max_allowed_packet的限制。
  2. 服务器响应给用户的数据通常会很多，由多个数据包组成。（客户端不断接受服务器推送的数据，客户端没有办法让服务器停下来。客户端只能被动的接受）。

8.2.1 mysql客户端和服务器通讯（tcp/ip或socket）

  • 客户端发起一条Query请求，服务器端的连接管理模块接收请求。
  • 将请求转发到连接进/线程模块。
  • 调用用户模块来进行授权检查。
  • 通过检查后，连接进/线程模块从线程连接池中取出空闲的被缓存的连接线程和客户端请求对接，如果失败则创建一个新的连接请求。

8.2.2 查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中查询缓存中的数据，然后在返回查询结果前还要检查一次用户权限。（此时缓冲中存放了当前查询需要访问的表信息）

  • 如果命中且权限通过，则返回结果（无需解析并生成执行计划）
  • 如果命中但权限不通过，直接返回
  • 如果不命中，则解析语句，生成执行计划，并执行

8.2.3 查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划，mysql在依照这个执行计划和存储引擎进行交互。包括：解析SQL、预处理、优化SQL执行计划。

语法解析器

首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询；比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。

预处理

预处理器则根据一些mysql规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

优化执行计划

当语法树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。

有非常多的原因会导致MySQL选择错误的执行计划，而MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短），因此我们可以指定某个执行计划。

执行过程和函数就是提前编译好，经过了语法解析器和预处理，然后存储在数据库中，当调用存储过程时就可以直接进入执行计划优化阶段了

8.2.4 查询执行引擎

在解析和优化阶段，mysql将生成查询对应的执行计划，mysql的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样对应的字节码。

8.2.5 返回结果

查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果给客户端，mysql仍然会返回这个查询的一些信息，如该查询影响到的行数。如果查询可以被缓存，那么mysql在这个阶段也会将结果放到查询缓存中。

mysql将结果集返回客户端是一个增量、逐步返回的过程。这样有两个好处：服务器端无须存储太多的结果，也就不会因为返回太多结果而消耗太多的内存；这样处理也让msyql客户端第一时间获得返回的结果。

结果集中的每一行都会以一个满足mysql客户端/服务器通信协议的包发送，再通过tcp协议进行传输，在tcp传输的过程中，可能对mysql的封包进行缓存然后批量传输。

8.3 缓存池

为了提升数据库的操作性能，MySQL同样引入了缓存（缓冲池）机制，以避免每次查询数据都进行磁盘IO。此外，还可以通过异步落盘实现查询性能的提升。

我们知道，缓存必然存在淘汰机制，在MySQL中一般通过LRU算法实现数据淘汰。

8.3.1 Free List、LRU List、Flush List

读线程将数据页从磁盘中调入内存之中时，如果内存中的缓存区没做任何规划时，就可能出现很多问题（比如覆盖数据，脏数据存放等），因此MySQL设计了三个List。

LRU List

MySQL中的缓冲池通过LRU算法来进行管理，当缓冲池不能存放新读取到的页时，将优先淘汰LRU列表中尾端的页。

注意，新读取到的页并不是直接放入LRU列表的首部，而是放入LRU列表的midPoint位置，midpoint前面的列表称为new列表，后面的为old列表，midpoint的数据经过一定时间（可设置）后才可以被加入到LRU列表的时热端。为什么这里要分为两个列表呢？

Free List

LRU列表用来管理已经读取的页，但当数据库刚启动时，LRU列表是空的，即没有任何的页。

这时页都存放在Free列表中。当需要从缓冲池中分页时，首先从Free列表中查找是否有可用的空闲页，若有则将该页从Free列表中删除，放入到LRU列表中。否则，根据LRU算法，淘汰LRU列表末尾的页，将该内存空间分配给新的页。

Flush List

在LRU列表中的页被修改后，称该页为脏页（dirty page），即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过CHECKPOINT机制将脏页刷新回磁盘，而Flush列表中的页即为脏页列表。需要注意的是，脏页既存在于LRU列表中，也存在于Flush列表中。LRU列表用来管理缓冲池中页的可用性，Flush列表用来管理将页刷新回磁盘，二者互不影响。

8.3.2 checkPoint

前面提到，数据库通过CHECKPOINT机制将脏页刷新回磁盘，那么这个机制是什么呢？我们知道，脏页所在的Flush List并不是无限大的，必然每过一段时间都会发生脏页落盘，这个时间点的标志就是checkPoint（类似JVM的GC回收）。

引入CheckPoint机制后，当数据库发生宕机时，数据库不需要重做所有的日志，因为 Checkpoint 之前的页都已经刷新回磁盘。故数据库只需对 Checkpoint 后的重做日志进行恢复。这样就大大缩短了恢复的时间。

8.3.3 缓冲池问题

预读失效

为了充分利用缓冲池功能，一般在数据库启动时会采取预读机制，将可能要访问的页加入到缓冲池。那么当预读的页并没有用到，会发生什么呢？ 此时无效数据会占用大量空间，在热点数据加载进来后还要需要将无效数据清理，怎么办呢？

有人想到可以让预读失败的页在缓冲池中停留的时间尽可能短，这就是上面的midPoint机制。

midPoint将LRU List分为两个部分：新生代<—老生代

  • 新页（预读页）加入缓冲池时，只加入到老生代头部。
  • 而只有老年代的新页真正被读取时，才会加入到新生代的头部

因此没有被读取的新页会被新生代的热数据页更早地淘汰出缓冲池。

缓冲池污染

当某一个 SQL 语句，要批量扫描大量数据时，可能导致把缓冲池的所有页都替换出去，导致大量热数据被换出，MySQL 性能急剧下降，这种情况叫缓冲池污染。

为此，MySQL 缓冲池加入了一个 “老生代停留时间窗口” 的机制：**只有满足 “被访问” 并且 “在老生代停留时间” 大于 T，才会被放入新生代头部。**这就避免了大量热点数据被立即替换。

8.4 数据库持久化

8.4.1 全量备份

备份数据库的全部数据

# 导出整个库 [-d]表示只导出表结构，否则导出结构和数据
mysqldump -uroot -p [-d] databasename > name.sql
# 导出库下某几个表
mysqldump -uroot -p databasename test1 test2 test3 > createtab.sql

8.4.2 增量备份

备份上一次全量备份/增量备份后的数据，没有重复数据，备份数据量不大，所需时间短，备份速度快，恢复也快。

# 1. 修改配置文件，开启增量备份
[mysqld] 
server_id
log_bin=/MySQL/my3306/log/binlog/binlog  
binlog_format = row 

# 2. 进行日志备份
mysqladmin -uroot -p flush-logs  	'进行增量备份'

8.4.3 bin-log

bin log是一个二进制格式的命令文件，用于记录用户对数据库更新的SQL语句信息（除了select和show），当有数据写入到数据库时，还会同时把更新的SQL语句写入到对应的binlog文件里。

当使用mysqldump进行全量备份时，只是对一段时间的数据进行全备，但是如果备份后突然发现数据库服务器故障，这个时候就要用到binlog的日志，也就是增量备份。还有主从复制。

8.4.4 redo log

redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页。redo log会被首先写入log buffer；当客户端执行COMMIT命令时，log buffer中的内容会被视情况刷新到磁盘。

因为直接写磁盘，系统只给当前的数据分配磁盘空间，而下一次写磁盘时，又分配另一块空间，因此磁头需要频繁移动，写磁盘就很慢，这就是随机读写。而先写redo log，然后系统根据redo log再申请大块磁盘空间（连续的），磁盘在写数据时不要频繁移动，速度也就很快了。

8.4.5 undo log

undo log一般是数据日志，根据每行记录进行记录，具体内容就是将copy事务前的数据库内容（行）写到undo buffer，在适合的时间把undo buffer中的内容刷新到磁盘。undo buffer与redo buffer一样，也是环形缓冲，但当缓冲满的时候，undo buffer中的内容会也会被刷新到磁盘；与redo log不同的是，磁盘上不存在单独的undo log文件，所有的undo log均存放在主ibd数据文件中（表空间），即使客户端设置了每表一个数据文件也是如此。

引入undo log是为了实现并发下数据版本控制，以及事务的回滚

9. 数据库优化

我们知道MySQL是一种关系型数据库的管理系统，而其中关系型数据库讲的是保存一种现实中的关系，比如说某个班级有多个学生，这就可以构成一张学生表，表中维护的多个学生的信息。而学生关联着多种信息，比如年龄、班级等，那么我们该怎么设计出一张表呢？见下方的四大范式：

9.1 结构优化

9.1.1 四大范式

  • 1NF：字段不可再分,否则就不是关系数据库。
    • 比如：我就是一个学生，当前班级是19级计专班，但是在数据库中不能这样存储，因为这里的班级是年级+专业组成的，当我要查询年级或专业的时候，就查不到了。所以要求字段必须不可再分。
  • 2NF：在第一范式的基础上更进一层，确保每一个非主属性完全函数依赖于任何一个候选码。
    • 比如：我选修了数据结构这门课，这门课有名字，编号等。那么能不能写入学生表呢？因为此门课并不依赖于主键，那么在操作中则会存在某些问题
      • 插入异常：如果在数据库中插入学生，但是还没有选课，那么课程就为null，可能并不允许为空
      • 删除异常：如果学生要删除某门课，而有时候此课程为主属性，造成整个元组被删除
      • 修改复杂：如果多个学生选择了一门课，当修改课程信息时，要修改多条数据
  • 3NF：在第二范式的基础上更进一层,目标是确保每个非主属性既不传递依赖于码，也不部分依赖于码，即直接依赖于码
    • 比如：我是属于信工学院的，而信工学院包含地址、联系电话等，那么能保存吗？不能，因为按这种插入方式，可能在一张表中每条记录都保存了相同的学院信息，此时存在大量的数据冗余，造成修改复杂等。
  • BCNF：在第三范式的基础上，消除主属性对候选码的部分依赖和传递依赖
    • 比如：现在有学生S，教师T和课程J，有如下依赖关系：（S, T） -> J，（S,J）->T,J->T，

实际上并不是所有的数据库设计都会应用高等级范式，虽然高等级范式保证数据存在更少的数据冗余，但是这也带来了数据库操作性能的降低，比如在查询时需要连接多个表。

因此常采用反范式化来适当的增加冗余，以达到优化查询效率的目的。如果以前需要连接其他表查询的字段直接插入到查询表，以空间换取时间。

9.1.2 分库分表

分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成 ，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

分库分表包括分库和分表两个部分，在生产中通常包括：垂直分库、水平分库、垂直分表、水平分表四种方式。

  • 垂直分表： 将一个表按照字段分成多表，每个表存储其中一部分字段。
    • 举例：一件商品有商品描述、具体信息等属性，将访问频率高的放在一张表（商品描述），访问频率低的放在另一张表
    • 优势1：为了避免IO争抢并减少锁表的几率，查看详情的用户与商品信息浏览互不影响。
    • 优势2：充分发挥热门数据的利用效率，比如大字段独立放一个表
  • 垂直分库：垂直分表只解决了单一表过大的问题，但在此表上的所有操作还是竞争同一个物理机的CPU等资源，因此需要根据业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用
    • 举例：淘宝网存储多种信息，比如店家信息，商品信息、个人信息等。这时候就可以进行分库
    • 优势1：能对不同业务的数据进行分级管理、维护、监控、扩展等
    • 优势2：高并发场景下，垂直分库一定程度的提升IO、数据库连接数、降低单机硬件资源的瓶颈
  • 水平分表：将某一表的所有记录行划分到同一个数据库的多张表中，每个表只有这个表的部分数据
    • 举例：按照ID的奇偶性将数据分到两张表，然后根据ID奇偶性来访问
    • 优势1：优化单一数据量过大而产生的性能问题
    • 优势2：避免IO争抢并减少锁表的几率
  • 水平分库：把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上，类似水平分表。
    • 优势1：解决了单库大数据，高并发的性能瓶颈
    • 优势2：提高了系统的稳定性及可用性

一般来说，在系统设计阶段就应该根据业务耦合松紧来确定垂直分库，垂直分表方案，在数据量及访问压力不是特别大的情况，首先考虑缓存、读写分离、索引技术等方案。若数据量极大，且持续增长，再考虑水平分库水平分表方案。

9.1.3 分区

分区表由多个相关的底层表实现，这个底层表也是由句柄对象标识，我们可以直接访问各个分区。存储引擎管理分区的各个底层表和管理普通表一样（所有的底层表都必须使用相同的存储引擎），分区表的索引知识在各个底层表上各自加上一个完全相同的索引。从存储引擎的角度来看，底层表和普通表没有任何不同，存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

通过实现分区表，事务操作/锁的粒度被限制在分区而不是段，大大提升了数据库的性能。

常见分区：Range分区、list分区、hash分区、key分区

9.2 数据类型优化

9.2.1 合适的数据类型

在大量数据的情况下，更小的数据类型能节省大量存储空间。最好使用可以存下你的数据的最小的数据类型，尽量少用text等大数据类型，非要使用可以考虑分表（将text分到另一个表）。

常用的数据类型选择

  • ip地址一般存储为整形（ip先转换为数字）
  • 电话一般存为char类型
  • 日期时间一般用MySQL自建时间类型而不是字符串类型

9.2.2 尽量避免null

如果查询中包含可为NULL的列，对Mysql来说更难优化，因为可为NULL的列使得索引，索引统计和值比较都更复杂。此外包含NULL的复合索引是无效的。

9.3 查询优化

MySQL优化的实质就是加快SQL语句的查询速度，在速度和查询复杂度之间做一个折衷。因此，我们需要根据慢查询语句快速定位原因，并进行改进。

9.3.1 慢查询日志

为了找到慢SQL，一般需要先开启慢查询日志，设置

开启慢查询定位：

SHOW VARIABLES LIKE "%query%"；	# 查看慢查询日志
SET GLOBAL slow_query_log = ON；	# 开启慢查询日志
SET @@long_query_time=10；	# 设置超过10s即为慢查询 

SELECT * FROM student；

根据配置的慢查询日志记录文件/var/lib/mysql/iZ2zebm9lsglek26h07uyeZ-slow.log ,查看以上的命令操作。

# Time: 2019-09-20T09:17:49.791767Z
# User@Host: root[root] @ localhost [] Id: 150
# Query_time: 0.002549 Lock_time: 0.000144 Rows_sent: 1 Rows_examined: 4079
SET timestamp=1566292669；
select * from city where Name = 'Salala'；

现在我们看到了慢查询语句，那怎么优化呢？

9.3.2 explain

通过explain关键字，可以看到语句的执行计划，关注其中最重要的几个字段（type、possible_keys 、key）

  • type：表示语句以何种方式来访问数据，有全表扫描、索引、范围查询索引等
  • key：表示语句实际使用的索引
  • rows：扫描的行数，一般越少越好

9.3.3 show profile

假如通过explain还是无法定位sql运行慢的问题，则需要分析sql在数据传输、网络、连接是否是死锁等需要使用比explain更加细粒度的查询和排查show profiles；

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yWiS8kRw-1631090503971)(…/images/
)]

通过其可以看到更小的数据问题。一般来说通常看一下几个参数：

  • converting heap to MyISAM：查询结果太多，内存都不够用，切换到磁盘了
  • Creating tmp table：创建了临时表，数据被拷贝到临时表，用完再删除数据
  • Copying to tmp table on disk： 把内存中临时表复制到磁盘，危险操作
  • locked：加锁

此外还有其他操作，

  • show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否 锁表等，可以实时地查看 SQL的执行情况，同时对一些锁表操作进行优化。
  • trace分析优化器执行计划

9.3.4 避免临时表

临时表是MySQL用于存储一些中间结果集的表，临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

临时表分为两种，一种是内存临时表，一种是磁盘临时表。内存临时表采用的是memory存储引擎，磁盘临时表采用的是innodb存储引擎。特别是磁盘临时表会导致大量磁盘IO，从而降低数据库性能。

9.3.5 使用索引

该怎么不建立索引：

  • 索引不能存储null值，因此数据列中存在null无法建立索引
  • 不适合键值较少的列
  • 经常插入、删除、修改的表要减少索引
  • text，image等类型不应该建立索引，这些列的数据量大

什么时候索引失效：

  • 使用模糊查询时，在左边没有通配符时，才会使用索引，如"2%"
  • 如果出现OR(或者)运算，要求所有参与运算的字段都存在索引，才会使用到索引。（MyISAM专属）
  • 对于创建的多列(复合)索引，只要查询条件使用了最左边的列，索引一般就会被使用。
  • 当取出的数据量超过表中数据的20%，优化器就不会使用索引，而是全表扫描。
  • 如果列类型是字符串，要使用引号。如where name = 'cao'会失效（隐式转换）
  • 使用一些操作符，如聚簇函数、!=、not in、in等

9.4 系统配置

  • 设置MySQL使用过程中的常用参数，如最大连接数等。
  • 设置MySQL使用系统资源的限制，如打开文件数、使用线程数、缓冲区大小等等

9.5 硬件资源

  • 读写分离
  • 主从复制

10. 拓展题

10.1 分库分表后，id 主键如何处理？

因为要是分成多个表之后，每个表都是从1 开始累加，这样是不对的，我们需要一个全局唯一的id 来支持。
生成全局id 有下面这集中方式：

UUID

UUID是指在一台机器上生成的数字，它保证对在同一时空中的所有机器都是唯一的。通常平台会提供生成的API。

UUID生成的是一串36位的16进制数。

优点：

  • 简单，容易实现

缺点：

  • UUID生成的主键是无序的，作为主键时会导致维护索引的代价过高，造成数据插入性能降低
  • UUID的长度过长，MySQL主键应该越短越好

MySQL的自增主键

在多表或者集群模式下，需要实现主键同步，成本过高

利用Redis的INCR生成id

因为Redis是单线程的，天生满足原子性，可以用Redis的INCR去生成唯一ID来获取更高的吞吐量。

但是这样也存在问题，和mysql一样，在集群环境下，需要设置不同的增长补偿，而且redis还涉及到key的过期时间，这样去维护一个Redis集群成本是很高的。

雪花算法（snowflake）

根据时间+机器ID+数据中心id+自增毫秒内序列，综合起来形成64位16进制数，因此生成了id自增的全局唯一long类型id。

public class SnowFlake {
   
    /** * 开始时间截 （2015-01-01） */
    private final long twepoch = 1420041600000L；

    /** * 机器id所占的位数 */
    private final long workerIdBits = 5L；

    /** * 数据标识id所占的位数 */
    private final long datacenterIdBits = 5L；

    /** * 支持的最大机器id，结果是31 （这个移位算法可以很快的计算出几位二进制数所能表示的最大十进制数） */
    private final long maxWorkerId = -1L ^ （-1L << workerIdBits）；

    /** * 支持的最大数据标识id，结果是31 */
    private final long maxDatacenterId = -1L ^ （-1L << datacenterIdBits）；

    /** * 序列在id中占的位数 */
    private final long sequenceBits = 12L；

    /** * 机器ID向左移12位 */
    private final long workerIdShift = sequenceBits；

    /** * 数据标识id向左移17位（12+5） */
    private final long datacenterIdShift = sequenceBits + workerIdBits；

    /** * 时间截向左移22位（5+5+12） */
    private final long timestampLeftShift = sequenceBits + workerIdBits + datacenterIdBits；

    /** * 生成序列的掩码，这里为4095 （0b111111111111=0xfff=4095） */
    private final long sequenceMask = -1L ^ （-1L << sequenceBits）；

    /** * 工作机器ID（0~31） */
    private long workerId；

    /** * 数据中心ID（0~31） */
    private long datacenterId；

    /** * 毫秒内序列（0~4095） */
    private long sequence = 0L；

    /** * 上次生成ID的时间截 */
    private long lastTimestamp = -1L；

    public SnowFlake（long workerId, long datacenterId） {
   
        if （workerId > maxWorkerId || workerId < 0） {
   
            throw new IllegalArgumentException（String.format（"worker Id can't be greater than %d or less than 0", maxWorkerId））；
        }
        if （datacenterId > maxDatacenterId || datacenterId < 0） {
   
            throw new IllegalArgumentException（String.format（"datacenter Id can't be greater than %d or less than 0", maxDatacenterId））；
        }

        this.workerId = workerId；
        this.datacenterId = datacenterId；
    }

    /** * 获得下一个ID （该方法是线程安全的） * * @return SnowflakeId */
    public synchronized long nextId（） {
   
        long timestamp = timeGen（）；

        //如果当前时间小于上一次ID生成的时间戳，说明系统时钟回退过这个时候应当抛出异常
        if （timestamp < lastTimestamp） {
   
            throw new RuntimeException（
                    String.format（"Clock moved backwards. Refusing to generate id for %d milliseconds", lastTimestamp - timestamp））；
        }

        //如果是同一时间生成的，则进行毫秒内序列
        if （lastTimestamp == timestamp） {
   
            sequence = （sequence + 1） & sequenceMask；
            //毫秒内序列溢出
            if （sequence == 0） {
   
                //阻塞到下一个毫秒,获得新的时间戳
                timestamp = tilNextMillis（lastTimestamp）；
            }
        }
        //时间戳改变，毫秒内序列重置
        else {
   
            sequence = 0L；
        }

        //上次生成ID的时间截
        lastTimestamp = timestamp；

        //移位并通过或运算拼到一起组成64位的ID
        return （（timestamp - twepoch） << timestampLeftShift） //
                | （datacenterId << datacenterIdShift） //
                | （workerId << workerIdShift） //
                | sequence；
    }

    /** * 阻塞到下一个毫秒，直到获得新的时间戳 * * @param lastTimestamp 上次生成ID的时间截 * @return 当前时间戳 */
    protected long tilNextMillis（long lastTimestamp） {
   
        long timestamp = timeGen（）；
        while （timestamp <= lastTimestamp） {
   
            timestamp = timeGen（）；
        }
        return timestamp；
    }

    /** * 返回以毫秒为单位的当前时间 * * @return 当前时间（毫秒） */
    protected long timeGen（） {
   
        return System.currentTimeMillis（）；
    }

    /** * 测试 */
    public static void main（String[] args） {
   
        long start = System.currentTimeMillis（）；
        SnowFlake idWorker = new SnowFlake（1, 3）；
        for （int i = 0； i < 50； i++） {
   
            long id = idWorker.nextId（）；
            System.out.println（Long.toBinaryString（id））；
            System.out.println（id）；
        }
        long end = System.currentTimeMillis（）；
        System.out.println（end - start）；

    }
}

SnowFlake算法的缺点：
依赖与系统时间的一致性，如果系统时间被回调，或者改变，可能会造成id冲突或者重复。

实际中我们的机房并没有那么多，我们可以改进改算法，将10bit的机器id优化，成业务表或者和我们系统相关的业务。

美团的Leaf分布式ID生成系统

美团的Leaf算法有两种模式：leaf-segment和leaf-snowflake

Leaf-segment号段模式

Leaf-segment号段模式是对直接用数据库自增ID充当分布式ID的一种优化，减少对数据库的频率操作。相当于从数据库批量的获取自增ID，每次从数据库取出一个号段范围，例如 （1,1000] 代表1000个ID，业务服务将号段在本地生成1~1000的自增ID并加载到内存.。

Leaf-snowflake

Leaf-snowflake基本上就是沿用了snowflake的设计，ID组成结构：正数位（占1比特）+ 时间戳（占41比特）+ 机器ID（占5比特）+ 机房ID（占5比特）+ 自增值（占12比特），总共64比特组成的一个Long类型。

Leaf-snowflake不同于原始snowflake算法地方，主要是在workId的生成上，Leaf-snowflake依靠Zookeeper生成workId，也就是上边的机器ID（占5比特）+ 机房ID（占5比特）。Leaf中workId是基于ZooKeeper的顺序Id来生成的，每个应用在使用Leaf-snowflake时，启动时都会都在Zookeeper中生成一个顺序Id，相当于一台机器对应一个顺序节点，也就是一个workId。

10.2 MySQL有关权限的表都有哪几个?

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在MySQL数据库里，由mysql_install_db脚本初始化。

  • user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
  • db权限表：记录各个帐号在各个数据库上的操作权限。
  • table_priv权限表：记录数据表级的操作权限。
  • columns_priv权限表：记录数据列级的操作权限。
  • host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

10.3 drop、truncate、delete的区别

应用不同

  • drop是DDL语言（数据定义），是对结构的删除，会删除表的结构，和表上的约束、触发器、索引等，但依赖于该表的存储过程/函数将会被保留，但无法使用。

  • truncate属于DDL语言，其会删除表中所有字段，且会重置自增长字段，不能回滚，删除速度比delete快

  • delete属于DML语言，可以有条件的删除，如delete from xx where id = 1，且可以回滚

释放空间不同

  • drop会将表、表上索引、触发器、约束等占用的所有空间全部释放
  • truncate则会将表和索引占据的空间恢复到初始大小
  • delete操作会减少表或索引占用的空间（删除部分数据）

事务回滚

  • drop属于DDL语言，不存在事务
  • truncate通过释放表数据所在的数据页来删除数据，在事务日志（undo log）中并不记录数据，只记录（bin log）操作，因此不能回滚，不能触发触发器
  • delete删除几行都会在事务日志中将删除的每行数据记录下来，可以回滚，可以触发触发器

注意:

  • 在删除外键约束时，不能使用truncate，因为删除时系统不允许删除外键（正在被其他表引用），因此需要先关闭外键约束，再使用truncate，再还原外键约束。或者使用delete
  • 对于参与索引视图的表，不能使用truncate

10.4 千万数据实现分页查询，以及与单纯只用limit分页的区别

一般开发人员对于web分页的解决方案是使用limit子句来完成，

对于小规模数据应用来讲，可能并不会有大问题，在一定程度上还是比较有效率的，但是如果在大数据应用来讲，它的效率就可能很低了，因为它是全表扫描，数据越往后，即ID越大，数据越慢，

10.4.1 利用覆盖索引

10.5 百万级别数据进行搜索匹配

使用全文索引（倒排索引）

10.6 千万行数据查询

使用子查询

我们可以先定位偏移位置的id，然后再查询数据

select * from test limit 1000000,10
select id from test limit 1000000,1
select * from test where id>=(select id from test limit 1000000,1)limit 10

id限定的方式

这种方法要求更高，id必须是连续递增，而且还得计算id的范围，然后使用between，sql如下：

select * from test where id between 1000000 and 1000100 limit 100;
select * from test where id>=1000000 limit 100

结果很快，这里limit是限制了条数，没有采用偏移量

10.7 数据库自增主键用完后分两种情况

  • 有主键，报主键冲突
  • 无主键，InnDB 会自动生成一个全局的 row_id。它到达最大值后会从 0 开始算，遇到 row_id 一样时，新数据覆盖旧数据。所以，我们还是尽量给表设置主键。