MySQL篇

常用的存储引擎？InnoDB与MyISAM的区别？

存储引擎是对底层物理数据执行实际操作的组件，为Server服务层提供各种操作数据的API。常用的存储引擎有InnoDB、MyISAM、Memory。这里我们主要介绍InnoDB 与 MyISAM 的区别：

（1）事务：MyISAM不支持事务，InnoDB支持事务

（2）锁级别：MyISAM只支持表级锁，InnoDB支持行级锁和表级锁，默认使用行级锁，但是行锁只有通过索引查询数据才会使用，否则将使用表锁。行级锁在每次获取锁和释放锁的操作需要消耗比表锁更多的资源。使用行锁可能会存在死锁的情况，但是表级锁不存在死锁

（3）主键和外键：MyISAM 允许没有任何索引和主键的表存在，不支持外键。InnoDB的主键不能为空且支持主键自增长，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键，支持外键完整性约束

（4）索引结构：MyISAM 和 InnoDB 都是使用B+树索引，MyISAM的主键索引和辅助索引的Data域都是保存行数据记录的地址。但是InnoDB的主键索引的Data域保存的不是行数据记录的地址，而是保存该行的所有数据内容，而辅助索引的Data域保存的则是主索引的值。

由于InnoDB的辅助索引保存的是主键索引的值，所以使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这也是为什么不建议使用过长的字段作为主键的原因：由于辅助索引包含主键列，所以，如果主键使用过长的字段，将会导致其他辅助索变得更大，所以争取尽量把主键定义得小一些。

（5）全文索引：MyISAM支持全文索引，InnoDB在5.6版本之前不支持全文索引，5.6版本及之后的版本开始支持全文索引

（6）表的具体行数

• ① MyISAM：保存有表的总行数，如果使用 select count() from table 会直接取出出该值，不需要进行全表扫描。
• ② InnoDB：没有保存表的总行数，如果使用 select count() from table 需要会遍历整个表，消耗相当大。

（7）存储结构

• ① MyISAM会在磁盘上存储成三个文件：.frm文件存储表定义，.MYD文件存储数据，.MYI文件存储索引。
• ② InnoDB：把数据和索引存放在表空间里面，所有的表都保存在同一个数据文件中，InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

（8）存储空间

• ① MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
• ② InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

（9）适用场景

• ① 如果需要提供回滚、崩溃恢复能力的ACID事务能力，并要求实现行锁级别并发控制，InnoDB是一个好的选择；
• ② 如果数据表主要用来查询记录，读操作远远多于写操作且不需要数据库事务的支持，则MyISAM引擎能提供较高的处理效率；

备注：在mysql8.0版本中已经废弃了MyISAM存储引擎。

事务的ACID与实现原理？

数据库的事务是并发控制的基本单位，是指逻辑上的一组操作，要么全部执行，要么全部不执行。

事务的ACID

（1）原子性：事务是一个不可分割的工作单元，事务里的操作要么都成功，要么都失败，如果事务执行失败，则需要进行回滚。
（2）隔离性：事务的所操作的数据在提交之前，对其他事务的可见程度。
（3）持久性：一旦事务提交，它对数据库中数据的改变就是永久的。
（4）一致性：事务不能破坏数据的完整性和业务的一致性。例如在转账时，不管事务成功还是失败，双方钱的总额不变。

ACID的实现原理

原子性

原子性是通过MySQL的回滚日志undo log（回滚日志）来实现的：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

隔离性

（1）事务的隔离级别

为保证在并发环境下读取数据的完整性和一致性，数据库提供了四种事务隔离级别，隔离级别越高，越能保证数据的完整性和一致性，但对高并发性能影响也越大，执行效率越低。（四种隔离级别从上往下依次升高）

• 读未提交：允许事务在执行过程中，读取其他事务尚未提交的数据；
• 读已提交：允许事务在执行过程中读取其他事务已经提交的数据；
• 可重复读（默认级别）：在同一个事务内，任意时刻的查询结果都是一致的；
• 读序列化：所有事务逐个依次执行，每次读都需要获取表级共享锁，读写会相互阻塞。

（2）事务的并发问题

如果不考虑事务的隔离性，在事务并发的环境下，可能存在问题有：

• 更新丢失：两个或多个事务操作相同的数据，然后基于选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题：最后的更新覆盖了其他事务所做的更新。
• 脏读：指事务A正在访问数据，并且对数据进行了修改（事务未提交），这时，事务B也使用这个数据，后来事务A撤销回滚，并把修改后的数据恢复原值，B读到的数据就与数据库中的数据不一致，即B读到的数据是脏数据。
• 不可重复读：在一个事务内，多次读取同一个数据，但是由于另一个事务在此期间对这个数据做了修改并提交，导致前后读取到的数据不一致；
• 幻读：在一个事务中，先后两次进行读取相同的数据（一般是范围查询），但由于另一个事务新增或者删除了数据，导致前后两次结果不一致。

不同的事务隔离级别，在并发环境会存在不同的并发问题：

（3）事务隔离性的实现原理

Innodb事务的隔离级别是由MVVC和锁机制实现的：

① MVCC（Multi-Version Concurrency Control，多版本并发控制）是 MySQL 的 InnoDB 存储引擎实现事务隔离级别的一种具体方式，用于实现读已提交和可重复读这两种隔离级别。而读未提交隔离级别总是读取最新的数据行，无需使用 MVCC。读序列化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

MVCC是通过在每行记录后面保存两个隐藏的列来实现的，一个保存了行的事务ID，一个保存了行的回滚段指针。每开始一个新的事务，都会自动递增产生一个新的事务ID。事务开始时会把该事务ID放到当前事务影响的行事务ID字段中，而回滚段的指针有该行记录上的所有版本数据，在undo log回滚日志中通过链表形式组织，也就是说该值实际指向undo log中该行的历史记录链表。

在并发访问数据库时，对正在事务中的数据做MVCC多版本的管理，以避免写操作阻塞读操作，并且可以通过比较版本解决快照读方式的幻读问题，但对于当前读的幻读，MVCC并不能解决，需要通过临键锁来解决。

② 锁机制：

MySQL锁机制的基本工作原理就是：事务在修改数据库之前，需要先获得相应的锁，获得锁的事务才可以修改数据；在该事务操作期间，这部分的数据是锁定，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

• 排它锁解决脏读
• 共享锁解决不可重复读
• 临键锁解决幻读

持久性

持久性的依靠redo log日志（重做日志）实现，在执行SQL时会保存已执行的SQL语句到一个redo log文件，但是为了提高效率，将数据写入到redo log之前，会先写入到内存中的redo log buffer缓存区中。写入过程如下：当向数据库写入数据时，执行过程会首先写入redo log buffer，redo log buffer中修改的数据会定期刷新到磁盘的redo log文件中，这一过程称为刷盘（即redo log buffer写日志到磁盘的redo log file中 ）。

redo log buffer的使用可以大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时redo log buffer中修改的数据在内存还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。为了确保事务的持久性，在当事务提交时，会调用fsync接口对redo log进行刷盘 ，刷新频率由 innodb_flush_log_at_trx_commit变量来控制的：

• 0：表示不刷入磁盘；
• 1：事务每次提交的时候，就把缓冲池中的数据刷新到磁盘中；
• 2：提交事务的时候，把缓冲池中的数据写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件。可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。

一致性

一致性指的是事务不能破坏数据的完整性和业务的一致性 ：

• 数据的完整性： 实体完整性、列完整性（如字段的类型、大小、长度要符合要求）、外键约束等
• 业务的一致性：例如在银行转账时，不管事务成功还是失败，双方钱的总额不变。

了解数据库中的锁机制吗？

当数据库中多个事务并发存取同一数据的时候，若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。MySQL锁机制的基本工作原理就是，事务在修改数据库之前，需要先获得相应的锁，获得锁的事务才可以修改数据；在该事务操作期间，这部分的数据是锁定，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

按照不同的分类方式，锁的种类可以分为以下几种：

• 按锁的粒度划分：表级锁、行级锁、页级锁； 
• 按锁的类型划分：共享（锁S锁）、排他锁（X锁）；
• 按锁的使用策略划分：乐观锁、悲观锁；

表级锁、行级锁、页级锁

• 表级锁：最大粒度的锁级别，发生锁冲突的概率最高，并发度最低，但开销小，加锁快，不会出现死锁；
• 行级锁：最小粒度的所级别，发生锁冲突的概率最小，并发度最高，但开销大，加锁慢，会发生死锁；
• 页级锁：锁粒度界于表级锁和行级锁之间，对表级锁和行级锁的折中，并发度一般。开销和加锁时间也界于表锁和行锁之间，会出现死锁；

不同的存储引擎支持不同的锁机制：

• InnoDB存储引擎支持行级锁和表级锁，默认情况下使用行级锁，但只有通过索引进行查询数据，才使用行级锁，否就使用表级锁。
• MyISAM和MEMORY存储引擎采用的是表级锁；
• BDB存储引擎使用的是页面锁，但也支持表级锁；

InnoDB的行锁

InnoDB的行锁有两种类型：

• 共享锁（S锁、读锁）：多个事务可以对同一数据行共享一把S锁，但只能进行读不能修改；
• 排它锁（X锁、写锁）：一个事务获取排它锁之后，可以对锁定范围内的数据行执行写操作，在锁定期间，其他事务不能再获取这部分数据行的锁（共享锁、排它锁），只允许获取到排它锁的事务进行更新数据。

对于update，delete，insert 操作，InnoDB会自动给涉及的数据行加排他锁；对于普通SELECT语句，InnoDB不会加任何锁。

InnoDB的表锁与意向锁

因为InnoDB引擎允许行锁和表锁共存，实现多粒度的锁机制，但是表锁和行锁虽然锁定范围不同，但是会相互冲突。当你要加表锁时，势必要先遍历该表的所有记录，判断是否有排他锁。这种遍历检查的方式显然是一种低效的方式，MySQL引入了意向锁，来检测表锁和行锁的冲突。

意向锁也是表级锁，分为读意向锁（IS锁）和写意向锁（IX锁）。当事务要在记录上加上行锁时，则先在表上加上对应的意向锁。之后事务如果想进行锁表，只要先判断是否有意向锁存在，存在时则可快速返回该表不能启用表锁，否则就需要等待，提高效率。

InnoDB行锁的实现与临键锁

InnoDB的行锁是通过给索引上的索引项加锁来实现的。只有通过索引检索数据，才能使用行锁，否则将使用表锁。

在InnoDB中，为了解决幻读的现象，引入了临键锁(next-key)。根据索引，划分为一个个左开右闭的区间。当进行范围查询的时候，若命中索引且能够检索到数据，则锁住记录所在的区间和它的下一个区间。其实，临键锁(Next-Key) = 记录锁(Record Locks) + 间隙锁(Gap Locks)。

• 间隙锁：当使用范围查询而不是精准查询进行检索数据，并请求共享或排它锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙(GAP)。
• 记录锁：当使用唯一索引，且记录存在的精准查询时，使用记录锁

利用锁机制解决并发问题

• X锁解决脏读
• S锁解决不可重复读
• 临键锁解决幻读

MySQL索引的数据结构

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

InnoDB 索引实现（聚集）

存储引擎InnoDB索引底层是一棵B+树。

聚集索引-叶子节点包含了完整的数据记录。

为什么 InnoDb 表必须有主键，并且推荐使用整型的自增主键？

• 如果没有设置索引的话，MySQL 会选择一个数据唯一的列作为主键索引， 如果找不到这样的列，会去做创建一个隐藏列，类似rowid。
• 表数据文件按照 B+Tree 的数据结构维护，在叶子节点维护的是该行的数据。所以必须有主键。
• 整型更方便 B+Tree 排序，自增的话，对于数据结构的存放更快,  顺序存放，不需要进行大量树的平衡操作。

为什么非主键索引结构叶子节点的存储的是主键值？

• 一致性， 让主键索引先成功，然后再去更新非主键索引关系
• 节省存储空间。

主键索引示意图：

非主键索引示意图图片：

非主键索引示意图图片：

如果查询的是通过 name = Alice 去查询的时候：

1. 走非主键索引去查询，查询完后拿到信息（Alice, 18）。其实这里也是一个非聚簇索引
2. 然后进行回表查询，再次通过主键去查询做回表查询。

两个数据文件：

• .frm 主要是存储表结构信息
• .ibd 主要是存储索引和数据

索引文件和数据文件是分离的（非聚集）

聚集索引和非聚集索引

• 聚集/非聚集主要是索引文件是否和数据文件在一起。
• 查询效率上来说聚集索引不会跨文件查询效率会更加快。

联合/复合索引

多个字段组织成一个共同的索引。

为什么使用B+Tree作为索引？

索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，磁盘I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的数据结构要尽量减少查找过程中磁盘I/O的存取次数。

详情看：Mysql索引为什么要用B+Tree实现?

MySQL的 InnoDB 和 MyISAM 存储引擎中B+Tree索引的实现？

MyISAM和InnoDB都是使用B+树索引，MyISAM的主键索引和辅助索引的Data域都是保存行的地址，但是InnoDB的主键索引保存的不是行的地址，而是保存该行的所有所有数据，而辅助索引的Data域保存的则是主索引的值。

索引的长度限制：

• 对于 Innodb 的组合索引，如果各个列中的长度超过767字节的，则会对超过767字节的列取前缀索引；对于 Innodb 的单列索引，如果列的长度超过767的，则取前缀索引（取前255字符）
• 对于 MyISAM 的组合索引，所创建的索引长度和不能超过1000 bytes，否则会报错，创建失败；对于 MyISAM 的单列索引，最大长度也不能超过1000，否则会报警，但是创建成功，最终创建的是前缀索引（取前333个字符）

哪些原因会导致索引失效？

（1）如果条件中有or，除非将or两边的字段都加上单列索引，否则不会走索引；

（2）对于联合列索引，如果不是使用的第一部分，则不会使用索引（需符合最左前缀规则）；

（3）like查询是以%开头，不会使用索引；

（4）如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引；

（5）重复值高的字段建议创建索引（比如性别字段等)，弊大于利。

如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

查看索引的使用情况：show status like 'Handler_read%';

handler_read_key：这个值越高越好，越高表示使用索引查询到的次数

handler_read_rnd_next：这个值越高，说明查询低效

索引的分类

Mysql中索引的种类也不是很多，不同类型的索引有不同的作用，Mysql中索引主要分为以下几类：

• 普通索引：最基本的索引类型，而且它没有唯一性之类的限制。
• 主键索引：主键索引一般都是在创建表的时候进行指定，「一个表只有一个主键索引」，特点是「唯一、非空」。MYSQL常用就是 自增主键；
• 唯一索引：唯一索引具有的特点就是唯一性，即指定列不能出现重复数据；
• 前缀索引：前缀索引建立的基础就指定列数据有很多的共同前缀；
• 联合索引：联合索引又称复合索引，是在表中两个或者两个列以上的基础上创建索引；
• 覆盖索引：当一个索引包含(或者说是覆盖)需要查询的所有字段的值时,我们称之为覆盖索引；
• 全文索引：全文索引仅可用于 MyISAM 表，并只支持从CHAR、VARCHAR或TEXT类型，用于替代效率较低的like 模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。

普通索引

创建普通索引：

ALTER TABLE 表名ADD INDEX 索引名 (列名);

删除索引：

ALTER TABLE table_name DROP INDEX index_name

主键索引和唯一索引

主键索引我们经常使用，一张表中仅允许有一个主键，可以由一个或者多字段组成；主键索引满足如下特征：

• 主键必须唯一；
• 主键不能包含NULL值；
• 主键必须自增；

-- 创建主键
alter table 表名 add primary key (字段名称)
-- 创建唯一索引
alter table 表名 add unique (字段名称)

删除PRIMARY KEY索引：因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

ALTER TABLE table_name DROP PRIMARY KEY

前缀索引

当对字符串进行索引时,如果数据库中该字段有许多的前缀重复就可以使用前缀索引，,这样可以大大的节约索引空间,从而提高索引效率；但其「缺点也很明显，不能在 order by 和 group by 中使用」；

前缀索引经常使用在地名，比如 xx省xx市xx县这种情形，有一个统一的前缀 xx省xx市；

创建语法：

alter table 表名 add key (字段名称(前缀长度))

覆盖索引

回表查询：MYSQL 如果只通过索引就可以返回查询所需要的数据，就是不是回表查询，否则查到索引数据后还需要回到表中查询数据就是回表查询。

不需要回表查询就是覆盖索引。

联合索引

联合索引是在表中用2个或者2个以上的字段创建索引，其创建索引方式与普通索引相同；其能减小检索范围；

语法格式：

alter table 表名 add index 索引名称(字段1，字段2...)

最左前缀匹配原则：使用联合索引有一个非常重要的因素就是所有的索引列只可以进行最左前缀匹配原则。

举例说明：复合索引（a,b,c）--------> [仅供参考]

（1）AND AND 只要用到了最左侧a列，和顺序无关，都会使用 索引

a = 1 AND b = 2 AND c = 3 ; 使用索引
c = 1 AND b = 2 AND a = 3 ; 使用索引 
a = 1 AND b = 2 ; 使用索引
a = 1 AND c = 3 ; 使用索引
c = 1 AND a = 2 ; 使用索引

（2）不包含最左侧的 a 的不使用索引

c = 3 ; 未使用索引
b = 2 ; 未使用索引
b = 2 AND c = 3 ; 未使用索引
c = 1 AND b = 2 ; 未使用索引

（3）or两边放联合索引，不使用索引

a = 1 AND b = 2 OR c = 3 未使用索引
a = 1 OR b = 2 AND c = 3 未使用索引
a = 1 OR b = 2 OR c = 3 未使用索引

注：

• 如果是单列索引，or两边只要有一个不是索引就不使用索引；
• or两边一个是联合索引的最左索引一个是单例索引才使用索引，否则失效；
• 数据量过小，or也会使本该有效的索引失效；

（4）最左侧的'a'列被大于，小于，不等于比较的 ，不使用索引

a > 1 AND b = 2 AND c = 3  未使用索引
a < 1 AND b =  2 AND c = 3  未使用索引
a > 1 ; 未使用索引
a <> 1 AND b = 2 AND c = 3 未使用索引

（5）最左侧a=某某，后面列大于小于无所谓，都使用索引（但后面必须 and and ）

a = 1 AND b < 2 AND c = 3 使用索引
a = 1 AND c = 2 AND b < 3 使用索引
a = 1 AND b < 2 使用索引
a = 1 AND b <> 2 AND c = 3 使用索引
// 可以说 OR一出现就不使用
a = 1 AND b < 2 OR c = 2 未使用索引

（6）ORDER BY

a = 某，后面order 无所谓 都 使用索引 （和最上面的最左匹配一样）

a = 1 AND b = 2 AND c = 3 ORDER BY a;// 或者 ORDER BY b ， ORDER BY c ，ORDER BY d, 使用索引
a = 1 ORDER BY a; // 或者 ORDER BY b,ORDER BY c,ORDER BY d 使用abc索引

b = 某，不使用

b = 1 ORDER BY a; //ORDER BY b 都 未使用索引

什么是聚集索引？

聚集索引是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚集索引，因为一个表的物理顺序只有一种情况，所以，对应的聚集索引只能有一个。与非聚集索引相比，一般情况下聚集索引有着更快的检索速度。

Innodb如何选择一个聚集索引？

• 如果一个主键被定义了，那么这个主键就是作为聚集索引；
• 如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引；
• 如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增。

注意：非聚集索引的叶子节点并不存储行数据的物理地址。而是存储的该行的主键值。非聚集索引包含了两次查找。一次是查找次级索引自身。然后查找主键（聚集索引）

MySQL中如何创建聚集索引：

create CLUSTERED INDEX 索引名称 ON 表名(字段名)

创建非聚集索引：

--创建非聚集索引
create NONCLUSTERED INDEX 索引名称 ON 表名(字段名)

聚集索引和非聚集索引的区别及优缺点

区别

聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个；
聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续；
聚集索引：物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。
非聚集索引：物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。
索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

优缺点

聚集索引插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入），查询数据比非聚集数据的速度快。

在数据库中通过什么描述聚集索引与非聚集索引的？

索引是通过二叉树的形式进行描述的，我们可以这样区分聚集与非聚集索引的区别：聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节仍然是索引节点，但它有一个指向最终数据的指针。

是不是聚集索引就一定要比非聚集索引性能优呢？

如果想查询学分在60-90之间的学生的学分以及姓名，在学分上创建聚集索引是否是最优的呢？

答：否。既然只输出两列，我们可以在学分以及学生姓名上创建联合非聚集索引，此时的索引就形成了覆盖索引，即索引所存储的内容就是最终输出的数据，这种索引在比以学分为聚集索引做查询性能更好。

非聚簇索引一定会回表查询吗?

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。

在建立索引的时候,都有哪些需要考虑的因素呢?

建立索引的时候一般要考虑到字段的使用频率，经常作为条件进行查询的字段比较适合。如果需要建立联合索引的话，还需要考虑联合索引中的顺序。此外也要考虑其他方面，比如防止过多的索引对表造成太大的压力，这些都和实际的表结构以及查询方式有关。

为什么非主键索引结构叶子节点存储的是主键值？

一是保证一致性，更新数据的时候只需要更新主键索引树，二是节省存储空间。

为什么推荐InnoDB表必须有主键？

保证会有主键索引树的存在（因为数据存放在主键索引树上面），如果没有mysql会自己生成一个rowid作为自增的主键主键索引。

为什么推荐使用整型的自增主键？

一是方便查找比较，而且新增数据的时候只需要在最后加入，不会大规模调整树结构，如果是UUID的话，大小不好比较，新增的时候也极有可能在中间插入数据，会导致树结构大规调整，造成插入数据变慢。

为什么重复值高的字段不能建索引（比如性别字段等)？

• 非聚簇索引存储了对主键的引用，如果select字段不在非聚簇索引内，就需要跳到主键索引上，再获取select字段值；
• 如果非聚簇索引值重复率高，那么查询时就会大量出现上图中从右边跳到左边的情况，导致整个流程很慢；
• 如果where值重复率高的字段，select用了limit，只查较少数据，也就是跳的次数很少的情况下，还是可以建索引的（后来想想也没必要，limit限制了数量，全表扫描也很快，除非字段值是排序的，必须扫描完前面的所有值）；
• 如果没有3这个前提，则不建议在值重复率高的字段上建索引，因为查询效率低，还需要维护索引。

MySQL的主从复制

MySQL主从复制的原理

Slave从Master获取binlog二进制日志文件，然后再将日志文件解析成相应的SQL语句在从服务器上重新执行一遍主服务器的操作，通过这种方式来保证数据的一致性。由于主从复制的过程是异步复制的，因此Slave和Master之间的数据有可能存在延迟的现象，只能保证数据最终的一致性。在master和slave之间实现整个复制过程主要由三个线程来完成：

（1）Slave SQL thread线程：创建用于读取relay log中继日志并执行日志中包含的更新，位于slave端
（2）Slave I/O thread线程：读取 master 服务器Binlog Dump线程发送的内容并保存到slave服务器的relay log中继日志中，位于slave端
（3）Binlog dump thread线程(也称为IO线程)：将bin-log二进制日志中的内容发送到slave服务器，位于master端

注意：如果一台主服务器配两台从服务器那主服务器上就会有两个Binlog dump 线程，而每个从服务器上各自有两个线程。

主从复制流程

（1）master服务器在执行SQL语句之后，记录在binlog二进制文件中；
（2）slave端的IO线程连接上master端，并请求从指定bin log日志文件的指定pos节点位置（或者从最开始的日志）开始复制之后的日志内容。
（3）master端在接收到来自slave端的IO线程请求后，通知负责复制进程的IO线程，根据slave端IO线程的请求信息，读取指定binlog日志指定pos节点位置之后的日志信息，然后返回给slave端的IO线程。该返回信息中除了binlog日志所包含的信息之外，还包括本次返回的信息在master端的binlog文件名以及在该binlog日志中的pos节点位置。
（4）slave端的IO线程在接收到master端IO返回的信息后，将接收到的binlog日志内容依次写入到slave端的relay log文件的最末端，并将读取到的master端的binlog文件名和pos节点位置记录到master-info文件中（该文件存slave端），以便在下一次同步的候能够告诉master从哪个位置开始进行数据同步；
（5）slave端的SQL线程在检测到relay log文件中新增内容后，就马上解析该relay log文件中的内容，然后还原成在master端真实执行的那些SQL语句，再按顺序依次执行这些SQL语句，从而到达master端和slave端的数据一致性；

主从复制的好处

（1）读写分离，通过动态增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上执行读功能。
（2）提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据。
（3）在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。

MySQL支持的复制类型及其优缺点

binlog日志文件有两种格式，一种是Statement-Based（基于语句的复制），另一种是Row-Based（基于行的复制）。默认格式为Statement-Based。

基于语句的复制（Statement-Based）

在主服务器上执行的SQL语句，在从服务器上执行同样的语句。效率比较高。 一旦发现没法精确复制时，会自动选择基于行的复制。 

优点

• ① 因为记录的SQL语句，所以占用更少的存储空间。binlog日志包含了描述数据库操作的事件，但这些事件包含的情况只是对数据库进行改变的操作，例如 insert、update、create、delete等操作。相反对于select、desc等类似的操作并不会去记录。
• ② binlog日志文件记录了所有的改变数据库的语句，所以此文件可以作为数据库的审核依据。

缺点

• ① 不安全，不是所有的改变数据的语句都会被记录。对于非确定性的行为不会被记录。例如：对于 delete 或者 update 语句，如果使用了 limit 但是并没有 order by ，这就属于非确定性的语句，就不会被记录。
• ② 对于没有索引条件的update，insert……select 语句，必须锁定更多的数据，降低了数据库的性能。

基于行的复制（Row-Based）

把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从mysql5.0开始支持。

优点

• ① 所有的改变都会被复制，这是最安全的复制方式；
• ② 对于 update、insert……select等语句锁定更少的行；

缺点

• ① 不能通过binlog日志文件查看什么语句执行了，也无从知道在从服务器上接收到什么语句，我们只能看到什么数据改变。
• ② 因为记录的是数据，所以说binlog日志文件占用的存储空间要比Statement-based大。
• ③ 对于数据量大的操作其花费的时间有更长。

混合类型的复制

默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

MySQL读写分离

实现原理

读写分离解决的是，数据库的写操作，影响了查询的效率，适用于读远大于写的场景。读写分离的实现基础是主从复制，主数据库利用主从复制将自身数据的改变同步到从数据库集群中，然后主数据库负责处理写操作（当然也可以执行读操作），从数据库负责处理读操作，不能执行写操作。并可以根据压力情况，部署多个从数据库提高读操作的速度，减少主数据库的压力，提高系统总体的性能。

读写分离提高性能的原因

（1）增加物理服务器，负荷分摊；
（2）主从只负责各自的写和读，极大程度的缓解X锁和S锁争用；
（3）从库可配置MyISAM引擎，提升查询性能以及节约系统开销；
（4）主从复制另外一大功能是增加冗余，提高可用性，当一台数据库服务器宕机后能通过调整另外一台从库来以最快的速度恢复服务。

MySQL读写分离的实现方式

（1）基于程序代码内部实现：在代码中根据select 、insert进行路由分类。优点是性能较好，因为程序在代码中实现，不需要增加额外的硬件开支，缺点是需要开发人员来实现，运维人员无从下手。
（2）基于中间代理层实现：代理一般介于应用服务器和数据库服务器之间，代理数据库服务器接收到应用服务器的请求后根据判断后转发到后端数据库，如Mycat。

分库分表：垂直分表、垂直分库、水平分表、水平分库

读写分离解决的是数据库读写操作的压力，但是没有分散数据库的存储压力，利用分库分表可以解决数据库的储存瓶颈，并提升数据库的查询效率。

垂直划分

（1）垂直分表：将一个表按照字段分成多个表，每个表存储其中一部分字段。一般会将常用的字段放到一个表中，将不常用的字段放到另一个表中。

优点：

• 避免IO竞争减少锁表的概率。因为大的字段效率更低，第一，大字段占用的空间更大，单页内存储的行数变少，会使得IO操作增多；第二数据量大，需要的读取时间长。
• 可以更好地提升热门数据的查询效率。

（2）垂直分库：按照业务模块的不同，将表拆分到不同的数据库中，适合业务之间的耦合度非常低、业务逻辑清晰的系统。

优点：

• 降低业务中的耦合，方便对不同的业务进行分级管理
• 可以提升IO、数据库连接数、解决单机硬件存储资源的瓶颈问题

（3）垂直拆分（分库、分表）的缺点：

• 主键出现冗余，需要管理冗余列
• 事务的处理变得复杂
• 仍然存在单表数据量过大的问题

水平拆分

（1）水平分表：在同一个数据库内，把同一个表的数据按照一定规则拆分到多个表中。

优点：

• 解决了单表数据量过大的问题
• 避免IO竞争并减少锁表的概率

（2）水平分库：把同一个表的数据按照一定规则拆分到不同的数据库中，不同的数据库可以放到不同的服务器上。

优点：

• 解决了单库大数据量的瓶颈问题
• IO冲突减少，锁的竞争减少，某个数据库出现问题不影响其他数据库，提高了系统的稳定性和可用性

（3）水平拆分（分表、分库）的缺点：

• 分片事务一致性难以解决
• 跨节点JOIN性能差，逻辑会变得复杂
• 数据扩展难度大，不易维护

分库分表存在的问题的解决

（1）事务的问题

① 方案一：使用分布式事务：

• 优点：由数据库管理，简单有效。
• 缺点：性能代价高，特别是shard越来越多。

② 方案二：程序与数据库共同控制实现，原理就是将一个跨多个数据库的分布式事务分解成多个仅存在于单一数据库上面的小事务，并交由应用程序来总体控制各个小事务。

• 优点：性能上有优势；
• 缺点：需要在应用程序在事务上做灵活控制。如果使用了spring的事务管理，改动起来会面临一定的困难。

（2）跨节点 Join 的问题

解决该问题的普遍做法是分两次查询实现：在第一次查询的结果集中找出关联数据的id，根据这些id发起第二次请求得到关联数据。

（3）跨节点count，order by，group by，分页和聚合函数问题

由于这类问题都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作，解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和 join 不同的是每个结点的查询可以并行执行，因此速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。

分库分表后，ID键如何处理？

分库分表后不能每个表的ID都是从1开始，所以需要一个全局ID，设置全局ID主要有以下几种方法：

（1）UUID：

• 优点：本地生成ID，不需要远程调用，全局唯一不重复。
• 缺点：占用空间大，不适合作为索引。

（2）数据库自增ID：在分库分表表后使用数据库自增ID，需要一个专门用于生成主键的库，每次服务接收到请求，先向这个库中插入一条没有意义的数据，获取一个数据库自增的ID，利用这个ID去分库分表中写数据。

• 优点：简单易实现。
• 缺点：在高并发下存在瓶颈。

（3）Redis生成ID：

• 优点：不依赖数据库，性能比较好。
• 缺点：引入新的组件会使得系统复杂度增加

（4）Twitter的snowflake算法：是一个64位的long型的ID，其中有1bit是不用的，41bit作为毫秒数，10bit作为工作机器ID，12bit作为序列号。

• 1bit：第一个bit默认为0，因为二进制中第一个bit为1的话为负数，但是ID不能为负数。
• 41bit：表示的是时间戳，单位是毫秒。
• 10bit：记录工作机器ID，其中5个bit表示机房ID，5个bit表示机器ID。
• 12bit：用来记录同一毫秒内产生的不同ID。

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

分区

分区就是将表的数据按照特定规则存放在不同的区域，也就是将表的数据文件分割成多个小块，在查询数据的时候，只要知道数据数据存储在哪些区域，然后直接在对应的区域进行查询，不需要对表数据进行全部的查询，提高查询的性能。同时，如果表数据特别大，一个磁盘磁盘放不下时，我们也可以将数据分配到不同的磁盘去，解决存储瓶颈的问题，利用多个磁盘，也能够提高磁盘的IO效率，提高数据库的性能。在使用分区表时，需要注意分区字段必须放在主键或者唯一索引中、每个表最大分区数为1024；常见的分区类型有：Range分区、List分区、Hash分区、Key分区

• Range分区：按照连续的区间范围进行分区
• List分区：按照给定的集合中的值进行选择分区。
• Hash分区：基于用户定义的表达式的返回值进行分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
• Key分区：类似于按照HASH分区，区别在于Key分区只支持计算一列或多列，且key分区的哈希函数是由 MySQL 服务器提供。

表分区的优点

① 可伸缩性：

• 将分区分在不同磁盘，可以解决单磁盘容量瓶颈问题，存储更多的数据，也能解决单磁盘的IO瓶颈问题。

② 提升数据库的性能：

• 减少数据库检索时需要遍历的数据量，在查询时只需要在数据对应的分区进行查询。
• 避免Innodb的单个索引的互斥访问限制
• 对于聚合函数，例如sum()和count()，可以在每个分区进行并行处理，最终只需要统计所有分区得到的结果

③ 方便对数据进行运维管理：

• 方便管理，对于失去保存意义的数据，通过删除对应的分区，达到快速删除的作用。比如删除某一时间的历史数据，直接执行truncate，或者直接drop整个分区，这比detele删除效率更高；
• 在某些场景下，单个分区表的备份很恢复会更有效率。

主键一般用自增ID还是UUID？

（1）自增ID

使用自增ID的好处：

• 字段长度较 UUID 会小很多。
• 数据库自动编号，按顺序存放，利于检索
• 无需担心主键重复问题

使用自增ID的缺点：

• 因为是自增，在某些业务场景下，容易被其他人查到业务量。
• 发生数据迁移时，或者表合并时会非常麻烦
• 在高并发的场景下，竞争自增锁会降低数据库的吞吐能力

（2）UUID：通用唯一标识码，UUID是基于当前时间、计数器和硬件标识等数据计算生成的。

使用UUID的优点：

• 唯一标识，不用考虑重复问题，在数据拆分、合并时也能达到全局的唯一性。
• 可以在应用层生成，提高数据库的吞吐能力。
• 无需担心业务量泄露的问题。

使用UUID的缺点：

• 因为UUID是随机生成的，所以会发生随机IO，影响插入速度，并且会造成硬盘的使用率较低。
• UUID占用空间较大，建立的索引越多，造成的影响越大。
• UUID之间比较大小较自增ID慢不少，影响查询速度。

一般情况下，MySQL推荐使用自增ID，因为在MySQL的 InnoDB 存储引擎中，主键索引是聚簇索引，主键索引的B+树的叶子节点按照顺序存储了主键值及数据，如果主键索引是自增ID，只需要按顺序往后排列即可，如果是UUID，ID是随机生成的，在数据插入时会造成大量的数据移动，产生大量的内存碎片，造成插入性能的下降。

视图View

视图是从一个或者多个表（或视图）导出的表，其内容由查询定义。视图是一个虚拟表，数据库中只存储视图的定义，不存储视图对应的数据，在对视图的数据进行操作时，系统根据视图的定义去操作相应的基本表。可以说，视图是在基本表之上建立的表，它的结构和内容都来自基本表，依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

（1）视图的优点：

• 简化了操作，把经常使用的数据定义为视图
• 安全性，用户只能查询和修改能看到的数据
• 逻辑上的独立性，屏蔽了真实表的结构带来的影响

（2）视图的缺点：

• 性能差，数据库必须把对视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也要把它变成一个复杂的结合体，需要花费一定的时间。
• 视图不能建立索引

存储过程Procedure

SQL语句需要先编译然后执行，而存储过程就是一组为了完成特定功能的SQL语句集，经过编译后存储在数据库中，用户通过制定存储过程的名字并给定参数来调用它。

用程序也可以实现操作数据库的复杂逻辑，那为什么需要存储过程呢？主要是因为使用程序调用API执行，其效率相对较慢，应用程序需通过引擎把SQL语句交给MYSQL引擎来执行，那还不如直接让MySQL负责它最精通最能够完成的工作。

存储过程的优点：

（1）标准组件式编程：存储过程创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。并且DBA可以随时对存储过程进行修改，对应用程序源代码毫无影响。
（2）更快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
（3）增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判杂的断和较复运算。
（4）减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
（5）作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

触发器Trigger

触发器是与表有关的数据库对象，当触发器所在表上出现指定事件并满足定义条件的时候，将执行触发器中定义的语句集合。触发器的特性可以应用在数据库端确保数据的完整性。触发器是一个特殊的存储过程，不同的是存储过程要用call来调用，而触发器不需要使用call，也不需要手工调用，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

游标Cursor

游标，就是游动的标识，可以充当指针的作用，使用游标可以遍历查询数据库返回的结果集中的所有记录，但是每次只能提取一条记录，即每次只能指向并取出一行的数据，以便进行相应的操作。当你没有使用游标的时候，相当于别人一下给你所有的东西让你拿走；用了游标之后，相当于别人一件一件的给你，这时你可以先看看这个东西好不好，再自己进行选择。

---

MySQL优化建议

• SQL优化与索引优化
• 数据库参数优化
• 表结构优化

explain的执行计划

执行计划是SQL语句经过查询分析器后得到的 抽象语法树 和 相关表的统计信息 作出的一个查询方案，这个方案是由查询优化器自动分析产生的。由于是动态数据采样统计分析出来的结果，所以可能会存在分析错误的情况，也就是存在执行计划并不是最优的情况。通过explain关键字知道MySQL是如何执行SQL查询语句的，分析select 语句的性能瓶颈，从而改进我们的查询，explain的结果如下：

重要的有id、type、key、key_len、rows、extra：

（1）id：id列可以理解为SQL执行顺序的标识，有几个select 就有几个id。

• id值不同：id值越大优先级越高，越先被执行；
• id值相同：从上往下依次执行；
• id列为null：表示这是一个结果集，不需要使用它来进行查询。

（2）select_type：查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询；

（3）table：表示 explain 的一行正在访问哪个表

（4）type：访问类型，即MySQL决定如何查找表中的行。依次从好到差：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，除了all之外，其他的 type 类型都可以使用到索引，除了 index_merge 之外，其他的type只可以用到一个索引。一般要求type为 ref 级别，范围查找需要达到 range 级别。

• system：表中只有一条数据匹配（等于系统表），可以看成 const 类型的特例
• const：通过索引一次就找到了，表示使用主键索引或者唯一索引
• eq_ref：主键或者唯一索引中的字段被用于连接使用，只会返回一行匹配的数据
• ref：普通索引扫描，可能返回多个符合查询条件的行。
• fulltext：全文索引检索，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引。
• ref_or_null：与ref方法类似，只是增加了null值的比较。
• index_merge：表示查询使用了两个以上的索引，索引合并的优化方法，最后取交集或者并集，常见and ，or的条件使用了不同的索引。
• unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值；
• index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
• range：索引范围扫描，常见于使用>,<,between ,in ,like等运算符的查询中。
• index：索引全表扫描，把索引树从头到尾扫描一遍；
• all：遍历全表以找到匹配的行（Index与ALL虽然都是读全表，但index是从索引中读取，而ALL是从硬盘读取）
• NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

（5）possible_keys：查询时可能使用到的索引

（6）key：实际使用哪个索引来优化对该表的访问

（7）key_len：实际上用于优化查询的索引长度，即索引中使用的字节数。通过这个值，可以计算出一个多列索引里实际使用了索引的哪写字段。

（8）ref：显示哪个字段或者常量与key一起被使用

（9）rows：根据表统计信息及索引选用情况，大致估算此处查询需要读取的行数，不是精确值。

（10）extra：其他的一些额外信息

• using index：使用覆盖索引
• using index condition：查询的列未被索引覆盖，where筛选条件使用了索引
• using temporary：用临时表保存中间结果，常用于 group by 和 order by 操作中，通常是因为 group by 的列上没有索引，也有可能是因为同时有group by和order by，但group by和order by的列又不一样，一般看到它说明查询需要优化了
• using filesort：MySQL有两种方式对查询结果进行排序，一种是使用索引，另一种是filesort（基于快排实现的外部排序，性能比较差），当数据量很大时，这将是一个CPU密集型的过程，所以可以通过建立合适的索引来优化排序的性能

 

参考：

• 深入解析MySQL索引数据结构
• MySQL数据库常见面试题总结