1 MySQL索引类型？

• 普通索引，index
• 主键索引，primary
• 唯一索引，unique index
• 全文索引，fulltext index
• 空间索引，spatial index

2 MySQL索引方法？

• B Tree索引
  • 用于非对等比较，比如范围查询，>, >=, <, <=, between, like
  • B Tree索引的每个叶子结点到根节点距离相等，适合查询某一范围内的数据
  • B Tree索引每个被索引的列都是排过序的，可以直接支持数据排序(order by)
• hash索引
  • 用于对等比较，=，<=>(数学中的等价于，安全的=)，IN等精确查询，不支持范围查询
  • hash索引不支持排序，因为hash索引比较的是进行hash运算后的hash值
  • hash索引检索效率高，B Tree索引需要从根节点开始遍历到叶子结点进行多次I/O访问，hash索引可以一次定位
  • 联合索引中hash索引要么全部使用，要么全部不适用，不支持B Tree联合索引的最左前缀原则
  • hash索引任何时候都不能避免表扫描，因为多个键的hash值可能相同，所以hash索引中的查询还需要访问表中的实际数据进行比较得出结果。
  • 大量hash冲突会严重影响hash索引性能，Memory引擎默认的索引类型是hash索引，同时也支持B Tree索引，Memory引擎支持的hash索引是非唯一的，冲突太多也会影响查询性能。
• R Tree索引
  • R Tree只支持4种空间数据类型，分别是：GEOMETRY、POINT、LINESTRING和POLYGON
  • MySQL支持R Tree索引的存储引擎有InnoDB、Myisam、Archive、BDB、NDB。

3 MySQL为什么不使用hash索引？

• hash索引只能用于等值精确查询，不能进行范围查询
• hash索引不支持排序，对于分组、排序、比较等hash索引的时间复杂度会退化到O(n)，而这类业务在实际中经常出现。
• 联合索引中hash索引要么全部使用，要么全部不使用，不支持B Tree联合索引最左前缀原则使用一部分索引项。
• 数据量很大时候，hash冲突概率很大，大量的hash冲突会严重影响hash索引的效率。

4 MySQL为什么不使用二叉树/红黑树？

• 红黑树是自平衡二叉查找树，每个节点只有两个分支，每个节点只存储一条记录，随着数据量增大，树的高度会增加，I/O次数增加，查找效率变慢。B Tree是多路查找树，每个节点有多个分支，可以存储多条记录，因此树的高度降低了，可以充分发挥局部性原理。
• 红黑树的物理存储形式是一个数组，逻辑上很近的节点物理上可能距离很远，假设当前节点位置为i，那它的左子节点位置为2i，右子节点位置为2i+1，当i很大时，根节点逻辑上与子节点很近，但是物理上其实很远，因此载入内存时候无法利用局部性原理，效率要比B Tree差很多。
  • 局部性原理，大概率使用查询数据附近的数据，这个原理是基于磁盘预读的，这样可以减少磁盘IO。
  • 磁盘预读，将一个节点的大小设置为等于一个页，每个节点只需要一次I/O就可以完全载入。

5 MySQL为什么不使用B Tree？

• B Tree每个节点都存储数据，B+ Tree只有叶子结点存储数据，其他节点用来索引，磁盘I/O一次读出的数据量是有限的，节点越大，磁盘I/O次数增加，效率降低。
• B+ Tree叶子结点之间增加了链表，只需要遍历叶子结点就能得到所有数据；B Tree都只能通过中序遍历所有节点，效率低下；同时B+ Tree支持范围查询，B Tree不支持。
• B+ Tree所有关键字都在叶子结点出现，B Tree的关键字分布在整棵树中。
• B+ Tree只有达到叶子结点才命中，B Tree可以在非叶子结点命中，性能相当于在关键字全集做一次二分查找。

6 B Tree、B+ Tree、B* Tree？

• B Tree(有序数组 + 平衡多叉树)
  • 每个节点存储数据
  • 只能通过中序遍历所有节点，不支持范围查询
  • 关键字分布在整棵树中，每个节点存储[m/2, m]个关键字
  • 可以在非叶子结点命中
• B+ Tree(有序数组 + 链表 + 平衡多叉树)
  • 叶子结点存储数据，其他节点用来索引
  • 叶子结点增加了顺序访问链表指针，遍历叶子结点就可以拿到数据，支持范围查询
  • 所有关键字都出现在叶子结点链表中(稠密索引)，非叶子结点作为叶子结点的索引(稀疏索引)
  • 总是到叶子结点才命中
  • B+ Tree查询速度更稳定，所有关键字都在叶子结点，非叶子结点是叶子结点的索引，所有关键字的查询都要经过非叶子结点到叶子结点的路径，查询路径长度相同，查询速度更稳定。
• B* Tree(丰满的B+树 + 兄弟节点间链表指针)
  • 在B+ Tree基础上，为非叶子结点增加链表指针指向其兄弟，将结点的最低利用率从1/2提高到2/3

7 MySQL索引原理

• hash索引
• B Tree索引，重点讲B Tree和B+ Tree索引原理

8 MySQL索引为什么能加快查找速度？

• 二分查找时间复杂度O(logn)
• B+ Tree原理

9 主键索引为什么快？

• 主键索引也称为聚簇索引，非主键索引也称为非聚簇索引、辅助索引、二级索引。
  • 聚簇索引的叶子结点存储的是数据，非聚簇索引的叶子结点存储的还是索引节点，只是有指向数据块的指针。
  • 聚簇索引是数据存储的物理顺序与逻辑顺序一致，一个表只有唯一的聚簇索引；非聚簇索引的数据存储的物理顺序和逻辑顺序没关系，一个表可以有多个非聚簇索引。
• 非主键索引需要回表操作。主键索引的叶子结点存放的是数据，非主键索引的叶子结点存放的是主键的值，通过主键查询可以很快定位到数据，非主键查询先要定位到主键的值然后根据主键的值查找数据，这个过程称为回表。

主键索引&非主键索引

10 为什么建议MySQL主键使用auto_increment？

• 如果不适用主键递增索引，插入数据时候由于B+树是有序的，需要将叶子结点进行移动，这样比较消耗时间，如果有节点数据页满了，需要进行页分裂操作，大量的页分裂操作严重影响性能。
• 如果主键采用自增的，每次插入的ID比前面大，这时候不需要叶子结点移动、不需要页分裂等操作，直接插入最后即可，提高性能。

11 MySQL自增主键最大值是多少？满了怎么办？

• int类型无符号最大值是2^32 -1=43亿，int类型有符号最大值是2^31=21亿
• bigint类型无符号整型最大值是2^64 -1，有符号整型最大值是2&63，非常大的值
• 满了之后会报主键冲突，Duplicate entry '4294967295' for key 'PRIMARY'
• 一般不会用到最大值，需要做分库分表，由分布式全局ID作为主键

12 聚簇索引 & 非聚簇索引

• 聚簇索引 / 聚集索引

  • 索引文件和数据文件存放在一起，找到了索引就找到了数据；
  • 聚簇索引的叶子结点存储的是数据，一次查找就能定位到数据；
  • InnoDB采用的是聚簇索引，聚簇索引默认是主键，如果没有没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会自动生成一个长度6个字节、类型为长整型的隐含字段作为主键；
  • 一张表只能有一个聚簇索引；
  • 聚簇索引比非聚簇索引检索数据更快，聚簇索引对主键的排序查找和范围查找更快
  • 聚簇索引插入依赖顺序，容易出现页分裂，频繁的页分裂影响性能，所以一般InnoDB建议定义自增ID为主键；
  • 聚簇索引的主键更新代价很高，会导致更新的行移动，一般建议主键不可更新。
  • 聚簇索引适合排序和范围查找；适合某列包含了小数目的不同值。

• 非聚簇索引 / 辅助索引 / 二级索引

  • 索引文件和数据文件分开存放，索引结构的叶子结点存储了指向数据的地址
  • 非聚簇索引的叶子结点存储的是聚簇索引键，一般就是主键，查询先要找到主键值，然后根据主键索引找到数据页记录；
  • Myisam采用的是非聚簇索引；
  • 一张表可能有多个非聚簇索引。
  • 非聚簇索引适用于频繁更新的列；适合某列包含了大数目的不同值。

聚簇索引&非聚簇索引

13 覆盖索引

• 只需要检索索引就可以获取数据，不需要再到数据表中检索，MySQL检索更少的数据，建立的索引字段覆盖了查询语句和查询条件中涉及的字段。
• 索引覆盖查询：explain的extra列是Using index
• 索引都按照值大小存储，避免随机访问，减少I/O
• 覆盖索引要存储列的值，只有B Tree支持，hash索引、全文索引、空间索引都不支持。
• 如果使用覆盖索引，select后面要列出具体的列，不要使用select *，因为如果将所有字段作为索引，索引文件过大，查询性能下降。

14 Using index & Using filesort & Using temporary & Using where

• Using temporary
  • 使用临时表存储中间数据，临时表建立过程是比较耗时的，常用于order by和group by
• Using where
  • MySQL使用where过滤结果集
• Using index
  • 覆盖索引，直接检索索引就可以获取数据，不需要索引回表，explain的Extra列是Using index
• Using filesort
  • MySQL中使用索引排序不能完成检索，还需要文件排序，效率很低。

15 稠密索引 & 稀疏索引

• 稠密索引每一个数据记录都对应一个索引项，更快的定位记录，但是占的空间比较大；
• 稀疏索引将数据记录集分块，块间有序、块内无序，只为每一块中最大的关键码建立索引项，所占用空间小，插入删除维护开销小。
• 稀疏索引查找记录分为两步：
  • 先找到块，块间有序，可以根据折半查找、插值查找等算法找到块
  • 块内无序，只能采用顺序查找
• InnoDB的主键索引是稠密索引、聚簇索引；InnoDB的非主键索引是稀疏索引、非聚簇索引
• Myisam的主键索引和非主键索引都是稀疏索引、非聚簇索引

稠密索引 稀疏索引

16 B+ Tree能存多少数据？

• 磁盘存储数据最小单位是扇区，一个扇区大小是512字节；文件系统最小存储单位是块，一个块大小是4KB；InnoDB最小存储单位是页，一页大小是16KB，InnoDB的所有数据文件(后缀为idb)大小都是16384(16KB)的整数倍。
• 可以通过参数查看，show variables like 'innodb_page_size';
• 互联网业务一般一行数据记录大小为1KB，那么一页可以存储16行数据；
• InnoDB采用B+ Tree结构，一颗B+ Tree总记录数=根节点指针数*单个叶子结点记录数
• 指针大小在InnoDB中是6字节，假设主键ID为bigint类型，长度为8字节，一共就是14字节，所以指针数为16384/14=1170
• 高度为2的B+ Tree可以存储记录数为117016=18720；高度为3的B+ Tree可以存储记录数为11701170*16=21902400，约两千万数据。

17 为什么B+ Tree一般都是3层？

• 一颗3层B+ Tree能存储1170117016=21902400，约两千万数据，能满足千万级数据存储。
• 查找数据一页代表依次I/O，通过主键索引查询通常只需要1~3次I/O即可查找数据，所以InnoDB B+ Tree的高度一般是1~3层。

18 MySQL最多能建多少个库？最多能建多少个表？单表数据量多大就要分库分表？

• MySQL理论上不限制库表数量，也要看操作系统最大文件数、磁盘容量及存储引擎等限制。
• 阿里巴巴《java开发手册》提出MySQL单表数据超过500万行或者单表容量超过2GB，建议分库分表。
• 跟MySQL配置及机器的硬件有关，MySQL为了提高性能会将索引装载入内存，InnoDB buffer size足够加载入内存没问题，但是内存是有限制的，不能完全载入内存，会加剧磁盘I/O，查询性能下降。
• 经验值，MySQL单表1000万，Oracle 9i单表一亿，建议进行分库分表。

19 MySQL单表超过2千万会怎么样，怎么优化？

• 软件
  • 分区
  • 分表
  • 分库
  • nosql，MongoDB、ES、Hbase等
• 硬件
  • 升级配置资源
  • 换Oracle

20 在线修改表结构？

• 数据量小业务闲时直接修改，会阻塞
• 修改业务逻辑实现
  • 旧表读，新表写；需要修改业务逻辑配合
  • 旧表查不到再查新表；
  • 渐进式同步数据；
• MySQL5.6.17推出在线修改表结构工具：online ddl，不阻塞dml和dql操作，不支持全文索引的表。
• 第三方工具
  • pt-osc，pt-online schema change
  • gh-ost，GitHub开源的工具
  • Facebook-osc，Facebook的online schema change
    • 不需要修改业务逻辑，旧表写操作通过触发器更新到新表
• 改从库表结构，然后主从切换
• 改变思路，增加扩展表
  • 维表的设计思路
• 设计表结构时候保留预留字段

21 explain详解

explain详解

• id(选择标识符)
  • id标识SQL执行顺序，SQL从里往外执行，从id大到小执行
  • id相同，从上到下执行
• select_type(查询类型)
  • simple，简单查询，不包含union、子查询等
  • primary，最外层的select
  • union，union中第二个或后面的select语句
  • dependent union，union中第二个或后面的select语句，结果依赖外部查询
  • union result，union的结果
  • subquery，子查询中的第一个select，结果不依赖外部查询
  • denpendent subquery，子查询中的第一个select，结果依赖外部查询
  • derived，派生的select，from子句的子查询
  • uncacheable subquery
• table
  • 显示这一步访问的表名，有时候是别名
• type(访问类型，最好到最坏)
  • system，const的特例，只有一行记录
  • const，MySQL转化为常量
  • eq_ref，类似ref，使用的是唯一索引，primary key或unique key
  • ref，连接匹配条件
  • ref_or_null，类似ref，包含null
  • index_merge，索引合并优化
  • unique_subquery，索引查找函数，可以替代子查询，效率更高
  • index_subquery，可以替代IN子查询
  • range，检索给定范围的行
  • index，只检索索引文件
  • all，全表扫描
• possible_keys
  • 理论上应该使用索引的列，null表示没有列使用索引
• key
  • 实际上使用了索引的列
• key_len
  • 索引字段长度，不代表实际长度，不损失精确下，长度越短越好
• ref
  • 连接匹配条件
• rows
  • 估算的查找需要扫描的结果集行数
• Extra
  • distinct
  • not exists
  • range checked for each
  • Using filesort
  • Using index
  • Using temporary
  • Using where

22 SQL调优

update

• update必须带上where条件

delete

• delete必须加where条件
• delete < truncate < drop

insert

• 批量insert
  • load data infile
  • select into
  • insert values
• 禁用唯一性检查
• 禁用外键检查
• 禁用事务自动提交

select

• 尽可能准确识别具体问题

1. show status
2. show processlist
3. show profiles

• 查询计划explain反复验证改进
• 优化索引
  • 主键索引
  • 覆盖索引
  • 最左前缀原则
• 优化表结构
  • 中间表
  • 冗余
  • 分解表，反三范式
• 优化procedure
• 具体SQL技巧
  • 避免select *
  • %不在首位
  • or两边都要是索引才走索引
  • limit 1
  • 尽可能使用not null填充数据
  • 避免数据类型隐式转化，字符串用“”括起来
  • 尽量避免大事务操作
• 业务上优化
  • 主键索引优化limit分页
  • 搜索引擎优化like
  • 复制、分片

23 MySQL调优

硬件

• CPU
• memory
• 磁盘
• 带宽

系统配置

• 锁优化
• 参数优化
  • sort_buffer_size，排序缓存
  • query_cache_size，查询缓存
  • read_buffer_size，顺序读取缓存
  • read_rnd_buffer_size，随机读取缓存
  • key_buffer_size，索引缓存
  • thread_concurrency，并发线程数

表结构

• 分解表
• 中间表
• 冗余

SQL调优

• 识别问题
  • show processlist
  • show profiles
  • show status
• explain
• 索引
  • 主键索引
  • 覆盖索引
  • 最左前缀原则
• procedure
• 具体SQL技巧
  • 避免select *
  • %不在首位
  • or两边都要是索引才走索引
  • limit 1
  • 尽可能使用not null填充数据
  • 避免数据类型隐式转化，字符串用“”括起来
  • 尽量避免大事务操作

架构优化

• HA(主从、集群、共享存储SAN、磁盘复制DRBD、基于一致性协议的HA)
• 复制、分片、分库分表
• 缓存减轻DB负担
• 搜索引擎优化like
• nosql

业务优化

• 分库分表join，程序优化SQL
• 需求做一些改造

24 MySQL查询过程

MySQL查询过程

25 count(*)、count(1)、count(列名)

• count(*)和count(1)都返回所有列，包含null；count(列名)返回只包含列名那一列，不包含null
• 性能：count(1) ≈ count(*) > count(主键 id) > count(字段)
• 强制走主键索引还没有二级索引效率高，因为处理的数据量比二级索引更多
• count(*)和count(1)在InnoDB内部优化方式一样，默认走二级索引，性能也基本一样。
• count(*)改进
  • 5.7.18前，扫描聚簇索引获取总记录数
  • 5.7.18后，先扫描最小可用的二级索引，不存在再扫描聚簇索引

count()查询表的具体行数在InnoDB和Myisam的不同处理

• Myisam会保存表的具体行数，内置了一个计数器，count()时候直接从计数器读取；
• InnoDB不保存表的具体行数，需要每次执行时候全表扫描计算有多少行。

26 MySQL有哪些存储引擎？各自有什么优缺点？

查看MySQL存储引擎

show engines;
show variables like 'storage_engine';

InnoDB

• 主要处理OLTP(On-line Transaction Processing，联机事务处理)的数据库应用
• 支持事务
• 支持外键
• 支持行锁，支持非锁定读
• 从5.5.5开始，InnoDB为MySQL默认存储引擎
• InnoDB的数据文件本身就是主索引文件，InnoDB的聚簇索引叶子结点可以直接定位到数据
• InnoDB的辅助索引叶子结点data域存储的不是物理地址而是主键的值，检索需要进过索引回表两次查询。
• InnoDB支持B Tree索引、fulltext索引(5.6+)
• 不仅缓存索引，还缓存数据
• 支持4种隔离级别，默认是Reaptable，通过MVCC来解决幻读
• 支持热备份
• InnoDB在5.6以后也支持全文本索引

InnoDB主索引 InnoDB辅助索引

MyISAM

• MyISAM基于ISAM，Indexed Sequential Access Method(有索引的顺序访问)，主要处理OLAP(On-line Analytical Processing，联机分析处理)的数据库应用。
• 不支持事务
• 支持表锁，不支持行锁
• 支持全文本索引(InnoDB在5.6以后也支持全文本索引)
• Myisam的索引文件和数据文件分开存放的，B+ Tree的叶子结点的data域存放的是页物理地址，指向数据记录
• Myisam的主索引和辅助索引结构一样，只是主索引要求key唯一，辅助索引的key可以重复
• Myisam支持B Tree索引、fulltext索引、R Tree索引
• 只缓存索引
• NULL值被允许在索引的列中，这个值占每个键的0~1个字节
• 每个MyISAM表最大索引数是64，可以通过重新编译来改变，每个索引最大的列数是16个。
• 最大的键长度是1000B，可以通过编译来改变，对于键长度超过250B的情况，一个超过1024B的键将被用上。
• BLOB和TEXT列可以被索引。

Myisam索引结构

Memory

• 数据存在内存中，重启即消失
• 不支持事务
• 不支持外键
• 支持表锁，不支持行锁
• 不支持text、blob字段
• 每个表可以有多达32个索引，每个索引16列，以及500B的最大键长度。
• memory存储引擎支持B Tree索引和Hash索引
• 支持auto_increment列
• 支持对可包含NULL值得列索引
• 当不再需要memory表的内容时，要释放被memory表使用的内存，应该执行delete from或truncate table，或者drop table。

Archive

• 只支持insert和select
• 支持高并发的写入操作，但不是事务安全的。
• 使用zlib算法对数据行进行无损数据压缩，适合存储归档数据、日志信息等

不同存储引擎比较

image.png

27 数据库六大范式

范式是具有最小冗余的表结构。

• 1NF
  原子性，列不可再分
• 2NF
  主键且唯一
• 3NF
  不存在非主码对主码的传递函数依赖
• BCNF
  • 所有非主属性对每一个码都是完全函数依赖；
  • 所有主属性对每一个不包含它的码也是完全函数依赖；
  • 没有任何属性完全函数依赖于非码的任何一组属性。
• 4NF
  • 限制关系模式的属性之间不允许有非平凡且非函数依赖的多值依赖
  • 相互独立的多值情况
• 5NF
  • 必须满足4NF
  • 表可以分解为更小的表
  • 相互依赖的多值情况

28 索引原则

• 主外键要建索引
• 经常进行连接的字段要建索引
• 索引要建在选择性高的字段，性别这种字段索引利用率很低
• 索引尽量建在小字段上，大文本字段或超长字段最好不建索引
• 索引列不参与计算、不要有函数等
• 删除不再使用或很少使用的索引
• 索引的最左匹配原则
• %不在首位
• or两边都要有索引
• 利用覆盖索引

29 存储过程优化思路

• 中间结果存放临时表，加索引
• 少用游标
• 尽量使用短事务
• 使用try-catch处理异常
• 查询语句最好不要放在循环内

30 MySQL有哪些锁？

MySQL锁分类 image.png

按照锁的粒度分类

• 表级锁
  • 锁的粒度大，发生锁冲突概率最高，并发度最低，不会出现死锁，加锁快，开销小
• 页级锁
  • 介于表锁和行锁之间，会出现死锁
• 行级锁
  • 锁粒度最小，最大程度并发支持，最大锁开销，加锁慢，会出现死锁
  • 行级锁只在存储引擎层实现，MySQL服务层没有实现。
  • MySQL InnoDB行级锁是锁在索引项，Oracle的行级锁是锁住数据行
  • 只有使用索引条件检索数据，InnoDB才使用行锁，否则使用表锁

按照加锁机制分类

• 乐观锁
  • 版本号version实现，应用层面实现
• 悲观锁，数据库层面实现
  • 悲观共享锁 / 读锁 / S锁
    • select ... lock in share mode
  • 悲观排它锁 / 写锁 / X锁
    • select ... for update

按照锁模式分类

• 意向锁，表锁，InnoDB自动加锁，目的是在事务中揭示下一行将要被请求锁的类型
  • 意向共享锁，IS，事务给数据行加S锁前先要获得IS锁
  • 意向排它锁，IX，事务给数据行加X锁前先要获得IX锁
• 间隙锁，使用范围检索加的锁，开区间，主要有两个目的：
  • 防止幻读
  • 满足MySQL主从复制和恢复机制要求
  • 插入意向锁，特殊的间隙锁，间隙锁锁住的是一个区间，插入意向锁锁住的是一个点，只用于并发插入操作
• 临键锁
  • 行锁+间隙锁，临键锁是一个左开右闭的区间，比如(3,5]
• 自增锁
  • 特殊的表级锁，主要用于事务中插入自增字段，通过innodb_autoinc_lock_mode参数可以控制自增主键生成策略

31 死锁

产生死锁条件

• 互斥条件
  一个资源每次只能被一个进程使用。
• 请求与保持条件
  一个进程因请求资源而阻塞时，对已获得的资源保持不放。
• 不可剥夺条件
  进程已获得的资源，在末使用完之前，不能强行剥夺。
• 循环等待条件
  若干进程之间形成一种头尾相接的循环等待资源关系。

死锁解决方法

• 死锁检测
• 死锁预防
• 死锁恢复

32 MySQL分库分表方式

• 垂直切分
  按照业务功能模块
• 水平切分
  按照某种规则。
  • 顺序分片 / 连续分片
    • 按照范围分片，0_1000,10012000,....
  • 随机分片 / hash分片
    • 哈希取余分片
    • 一致性哈希分片
    • 虚拟槽分片

33 分布式事务

• 2PC
• 3PC
• TCC
• 异步消息队列
  • 本地时间表 + MQ
  • 事务消息
• 柔性事务
  • BED

34 事务隔离级别及封锁协议

34.1 事务隔离级别

• Read Uncommitted
  读未提交。一个事物的修改还未提交，就对其他事务可见。会出现脏读、不可重复读、幻读。一般不用。
• Read Committed
  读已提交。大多数数据库默认的隔离级别，Oracle默认的隔离级别也是这一级。可以避免脏读。
• Repeatable Read
  可重复读。同一事务中多次读取相同的记录结果是一样的。MySQL默认的隔离级别。可以避免脏读、不可重复读，会出现幻读。InnoDB通过MVCC解决了幻读问题。
• Serializable
  串行化。强制事务串行执行，最高的隔离级别。没有并发可言，可避免脏读、不可重复读、幻读，一般不用。

34.2 封锁协议

在使用数据库的排它锁、共享锁等对数据对象加锁的时候需要遵循一定规则，比如何时申请锁、何时释放锁、锁持有时间等，这些规则就叫做封锁协议。

• 一级封锁协议
  • 对应的是read uncommitted，加共享锁，不能避免脏读、不可重复度、幻读。
• 二级封锁协议
  • 对应的是read committed，写操作加排它锁，读操作加共享锁，避免了脏读。
• 三级封锁协议
  • 对应的是repeatable read，在二级封锁协议基础上，对读操作加共享锁，直到事务结束后才释放，避免了不可重复度，当然也避免了脏读。
• 四级封锁协议
  • 对应的是serialization，在三级封锁协议基础上，对所有读写操作加表锁，避免了脏读、不可重复度、幻读。

35 不考虑事务隔离性发生的问题

• 脏读
  一个事务读取到另一个事务还未提交的记录
• 不可重复读
  一个事物读取到另一个事务提交的记录，同一个事务内多次读取相同的记录结果却不一样，因为中间有其他事物修改了记录并提交了。
• 幻读
  事务A把表中所有行的一个字段flag从1改成了2，这时事务B新增了一行数据，测试flag字段的值还是1，这时操作事务A的用户再次查看数据，发现有一行flag的值是1没有改成2，好像产生了幻觉一样。MySQL通过MVCC解决幻读问题。

36 什么是MVVC？

• MVCC Definition
  • Multi version concurrency control，多版本并发控制。MVCC是行级锁的一个变种，不同的数据库实现机制不同，但大多实现了非阻塞的读操作，写操作也只锁定必要的行，因此开销很低，并发较好。
  • MVCC只在Read Committed和Repeatable Read两个隔离级别下工作
• InnoDB的MVCC原理
  • 每一行记录加两个隐藏的列，一个保存创建时间，一个保存删除时间，这两个字段的真实值是系统版本号，每开始一个新事务，版本号会自动递增。
• MVCC分类
  • 乐观并发控制
  • 悲观并发控制

37 什么场景会出现filesort？出现了怎么优化？

filesort出现场景

• MySQL索引排序不能检索出数据，还需要文件排序，filesort一般出现在order by和group by(先分组后排序)。
• select * from test where a=*** order by b; a是索引列
• 执行explain，Extra列出现Using where；Using filesort

优化filesort

• 将(a,b)建成联合索引，再执行explain，Extra列出现Using where

38 filesort底层原理？

内存排序缓冲区sort_buffer_size

filesort是通过相应的排序算法,将取得的数据在内存中进行排序。
MySQL需要将数据在内存中进行排序，所使用的内存区域也就是我们通过sort_buffer_size 系统变量所设置的排序区。
这个排序区是每个Thread 独享的，所以说可能在同一时刻在MySQL 中可能存在多个 sort buffer 内存区域。

• 在MySQL中filesort 的实现算法实际上是有两种：
  • 双路排序
    • MySQL4.1版本之前的排序算法，首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序。
  • 单路排序
    • 一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。
    • MySQL4.1开始的改进算法，主要目的是为了减少第一次算法中需要两次访问表数据的 IO 操作，将两次变成了一次，但相应也会耗用更多的sort buffer 空间。当然，MySQL4.1开始的以后所有版本同时也支持第一种算法。

怎么选择排序算法？max_length_for_sort_data

• MySQL主要通过比较我们所设定的系统参数 max_length_for_sort_data的大小和Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。
  • 如果 max_length_for_sort_data更大，则使用第二种优化后的算法，反之使用第一种算法。
  • 如果希望 ORDER BY 操作的效率尽可能的高，一定要注意max_length_for_sort_data 参数的设置。如果filesort过程中，由于排序缓存的大小不够大，那么就可能会导致临时表的使用。
  • max_length_for_sort_data的默认值是1024。

随机读取缓冲区read_rnd_buffer_size

• mysql在使用双路排序的时候，需要根据排好序的key，第二次去读取真正要返回的数据的。这样就会用到read_rnd_buffer_size这个参数定义的缓冲区。将读取的数据放到这个缓冲区中。
• 随机读取数据缓冲区使用内存(read_rnd_buffer_size)和顺序读取相对应，当 MySQL 进行随机读取数据块的时候，会利用这个缓冲区暂存读取的数据。如根据索引信息读取表数据，根据排序后的结果集与表进行Join等等。总的来说，就是当数据块的读取需要满足一定的顺序的情况下，MySQL 就需要产生随机读取，进而使用到 read_rnd_buffer_size 参数所设置的内存缓冲区。
• read_rnd_buffer_size，默认8MB。

顺序读取缓冲read_buffer_size

• 顺序读取数据缓冲区使用内存(read_buffer_size)：这部分内存主要用于当需要顺序读取数据的时候，如无法使用索引的情况下的全表扫描，全索引扫描等。在这种时候，MySQL 按照数据的存储顺序依次读取数据块，每次读取的数据块首先会暂存在read_buffer_size中，当buffer 空间被写满或者全部数据读取结束后，再将buffer中的数据返回给上层调用者，以提高效率。
• read_buffer_size，默认2MB

39 为什么要小表驱动大表？(假设小表100条,大表100万条)

每次在树搜索里面做一次查找都是log(n), 所以对比的是100log(100万)和100万log(100)哪个小，显然是前者，所以结论应该是让小表驱动大表。

• 小表和大表都有索引

  • 小表优先能极大减少比较次数，效率更高
  • 从小表找只需要确定大表中有无满足条件数据，比较100次
  • 从大表找要比较100万次

• 小表和大表都没有索引

  • 因为没有索引，所以nature join会对两个表做全表扫描。大致过程是从前表中load 一个输入缓冲区大小的数据块进内存，然后从后表中load 一个输入缓冲区大小的数据进内存，然后两个输入缓冲区中的数据做nature join，join结果写进输出缓冲区。然后后表的输入缓冲区清空，再从后表中load 第二块输入缓冲区大小的数据，继续和前表输入缓冲区中的数据做nature join，将结果写进输出缓冲区。以此类推，当后表遍历完一遍之后，前表load第二块输入缓冲区大小的数据，后表再依次load 第一块，第二块，到第n块。那cost 是多少呢？
  • 比如前表有a块缓冲区大小的数据，后表有b块缓冲区大小的数据，那cost 就是 ab（transfer time）+ 2a * （seek time）+ a*（transfer time），其中 transfer time 表示一块缓冲区大小的数据从硬盘load 到内存的时间，seek time表示磁盘块的寻址时间所以，前表较小的时候（即a较小的时候），总体时间会小。
  • 结论：小表在前好

• 小表无索引，大表有索引

  • 基于索引列做nature join这种情况，必须小表在前，但小表在前，有多大的性能提升，要看大表的索引类型。
  • 大表聚集索引，性能提升较大；
  • 大表非聚集索引，性能有可能同全表扫描差不多。
  • 结论：小表在前好

• 小表有索引，大表无索引

  • 基于索引列做nature join，这种情况需要关注大表在前，使用小表索引，会不会带来性能提升。
  • 小表聚集索引，性能可能提升较大
  • 小表非聚集索引，性能有可能同全表扫描差不多，那性能就赶不上小表在前了
  • 结论：大小表谁在前不一定，需要看小表的索引情况和大小表的相对数据量大小

40 单台MySQL支持最大连接数？服务器响应的最大连接数？

MySQL最大连接数/用户数：max_connections

• max_connections：MySQL最大连接数/用户数，每个连接MySQL的用户都算作一个连接；
• 查看命令：show variables like '%max_connections%';
• 修改命令：
  • 临时修改：set global max_connections=新值
  • 永久修改：在my.cnf/my.ini直接修改max_connections
• 默认值是100
• 实际起作用的最大可用连接数为16384，可以设置超过16384，但是真正起作用的最大连接数还是16384；
• MySQL实际最大连接数为max_connections+1，MySQL任何时候都会为root用户分配一个连接。
• max_connections设置过小会报错"ERROR 1040: Too many connections"

MySQL当前状态最大连接数：max_used_connections

• max_used_connections：MySQL服务器当前状态的最大连接数
• max_used_connections / max_connctions的比值一般在10%以上，85%是最理想的值；
  • 比值过高说明max_connections设置过小或者MySQL负载过高(可以加从库缓解读压力)
  • 比值过低说明max_connections设置过大

41 MySQL高可用方案

• 主从
  • 主从复制
  • MMM，双主
  • MHA，多主
• 集群
  • 官方Cluster，需要NDB存储引擎
  • PXC
• 共享存储SAN
• 磁盘复制DRBD
• 基于一致性协议的HA
  • MGR
  • Galera

41.1 主从复制

• 主从复制(一主多从)
• MMM架构(双主多从)
• MHA架构(多主多从)

主从复制(一主多从)

• 优点
  • MySQL自身提供的一种高可用方案，数据同步基于MySQL replication，从拉取主的bin-log然后在从上redo实现数据同步。
  • 通过keepalived实现自动failover，可以实现95%的SLA
• 缺点
  • 主从同步延时会导致数据不一致

MySQL高可用-主从复制

MMM架构(双主多从，Master-Master replication manager for Mysql)

• 优点
  • 数据同步基于MySQL replication，自动failover基于monitor实现。
  • 虽然有两主，但是同一时点只有一个主在写，另一个承担部分读以加速主主切换时候备选主的预热。
• 缺点
  • monitor节点存在单点故障，可以结合keepalived做高可用。
  • 数据同步延时存在数据一致性问题，适用于数据一致性不高的场景。

MySQL高可用-MMM架构

MHA架构(多主多从，Master High Availability Manager and Toolsfor MySQL)

• 优点
  • 日本MySQL专家用Perl语言开发的脚本管理工具，用它来实现自动failover，数据同步还是基于MySQL replication。
  • MHA Manager(管理节点)：部署在独立机器，也可以部署在一台slave节点，管理节点定时探测集群中的master节点，当master宕机自动将最新数据的slave升级为master，并将其他slave指向新的master。
  • MHA Node(数据节点)：每台MySQL服务器存储数据。
• 缺点
  • 管理节点存在单点故障，可以结合keepalived实现高可用。

MySQL高可用-MHA架构

41.2 集群

• MySQL Cluster(NDB存储引擎，比较复杂， 业内没有大规模使用)
• PXC(Percona XtraDB Cluster)

MySQL NDB Cluster

• 优点
  • 需要使用NDB存储引擎，所有组件都是MySQL官方的，管理节点需要做冗余。
• 缺点
  • 需要使用NDB存储引擎；
  • 管理和配置都很复杂，某些SQL语句比如join需要避免。

MySQL高可用-MySQL NDB Cluster

PXC

• 优点
  • 同步复制，事务要么在所有节点提交或者不提交
  • 多主复制和并行复制，任意节点都可以读写；
  • 节点自动配置
• 缺点
  • 有多少节点就有多少重复数据
  • 新节点加入开销大，需要复制完整的数据
  • 所有写操作发生在所有节点，不能有效解决写缩放问题。

MySQL高可用-PXC

41.3 共享存储-SAN

一般共享存储采用比较多的是SAN，处理failover用heartbeat，可以实现99.99%的SLA。

• 优点
  • 数据强一致性
• 缺点
  • 需要考虑共享存储的高可用
  • 价格昂贵

MySQL高可用-共享存储

41.4 磁盘复制DRBD

DRBD是一个用软件实现的、无共享的、服务器之间镜像块设备内容的存储复制解决方案。DRBD是linux内核模块实现的快级别的同步复制技术，可以与SAN达到相同的共享存储效果。和SAN网络不同，它并不共享存储，而是通过服务器之间的网络复制数据。处理failover也是用heartbeat。

• 优点
  • 强一致性
  • 相比较SAN共享存储，价格低廉
• 缺点
  • 从库不提供读操作
  • 对IO性能影响较大

MySQL高可用-DRBD

41.5 基于一致性协议的HA

• MGR
• Galera

MGR(MySQL Group Replication)

• 优点
  • MySQL官方推荐的组复制方案，基于paxos协议解决传统异步复制和半同步复制的数据一致性问题，第一个GA版本正式发布于MySQL5.7.17。
  • 复制组是一个通过消息传递相互交互的Server集群。复制组由多个Server成员组成，当client发起一个更新事务时，会将复制写集广播出；
  • 单主模式下，组复制具有自动选主功能，每次只有一个 Server成员接受更新，其它成员只提供读服务。
  • 多主模式下，所有的Server 成员都可以同时接受更新，没有主从之分，成员角色是完全对等的。
• 缺点
  • 基于消息可靠性投递

MySQL高可用-MGR

Galera

• 优点
  • 多主写入，无延迟复制，能保证数据强一致性；
  • 有成熟的社区，有互联网公司在大规模的使用；
  • 自动故障转移，自动添加、剔除节点；
• 缺点
  • 需要为原生MySQL节点打wsrep补丁
  • 只支持innodb储存引擎

MySQL高可用-Galera

42 MySQL读写分离方案？

42.1 客户端解决方案(应用层)

• TDDL
• Sharding-Sphere

读写分离-应用层解决方案

• 优点
  • 开发程序完成，不涉及中间件，运维简单
  • 理论上支持任何数据库，基于JDBC
• 缺点
  • 开发成本高，代码侵入性强
  • 不能动态增加数据源
  • 主要开发完成，运维参与少

42.2 中间件解决方案(代理层)

• MySQL Proxy
• mycat
• altas

读写分离-代理层解决方案

• 优点
  • 应用层程序不需要改造
  • 可以动态增加数据源不需要重启程序
• 缺点
  • 依赖代理层中间件，切换数据库变得困难
  • 增加了代理层，增加了运维成本
  • 代理层存在单点故障，需要做高可用

43 数据库开发规范

43.1 基础规范

（1）必须使用InnoDB存储引擎解读：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高
（2）必须使用UTF8字符集解读：万国码，无需转码，无乱码风险，节省空间
（3）数据表、数据字段必须加入中文注释解读：N年后谁知道这个r1,r2,r3字段是干嘛的
（4）禁止使用存储过程、视图、触发器、Event解读：高并发大数据的互联网业务，架构设计思路是“解放数据库
CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。数据库擅长存储与索引，CPU计算还是上移吧
（5）禁止存储大文件或者大照片解读：为何要让数据库做它不擅长的事情？大文件和照片存储在文件系统，数据库里存URI多好

43.2 命名规范

（6）只允许使用内网域名，而不是ip连接数据库
（7）线上环境、开发环境、测试环境数据库内网域名遵循命名规范业务名称：xxx线上环境：
my10000m.mysql.jddb.com开发环境：yf10000m.mysql.jddb.com测试环境：test10000m.mysql.jddb.com从
库在名称后加-s标识，备库在名称后加-ss标识线上从库：my10000sa.mysql.jddb.com
（8）库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用
（9）表名t_xxx，非唯一索引名idx_xxx，唯一索引名uniq_xxx
（10）单实例表数目必须小于500
（11）单表列数目必须小于30
（12）表必须有主键，例如自增主键解读：
a）主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用
b）主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引
的磁盘空间，提高索引的缓存效率
c） 无主键的表删除，在row模式的主从架构，会导致备库夯住
（13）禁止使用外键，如果有外键完整性约束，需要应用程序控制解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先。

43.3 字段设计规范

（14）必须把字段定义为NOT NULL并且提供默认值解读：
a）null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化
b）null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性；同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多。
c）null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识
d）对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null值的记录
（15）禁止使用TEXT、BLOB类型解读：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能。
（16）禁止使用小数存储货币解读，小数容易导致钱对不上
（17）必须使用varchar(20)存储手机号解读：
a）涉及到区号或者国家代号，可能出现+-()
b）手机号会去做数学运算么？
c）varchar可以支持模糊查询，例如：like“138%”
（18）禁止使用ENUM，可使用TINYINT代替解读：a）增加新的ENUM值要做DDL操作b）ENUM的内部实际存储就是整数，你以为自己定义的是字符串？
（19）单表索引建议控制在5个以内
（20）单索引字段数不允许超过5个解读：字段超过5个时，实际已经起不到有效过滤数据的作用了
（21）禁止在更新十分频繁、区分度不高的属性上建立索引解读：a）更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能b）“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似
（22）建立组合索引，必须把区分度高的字段放在前面解读：能够更加有效的过滤数据
（23）禁止使用SELECT ，只获取必要的字段，需要显示说明列属性解读：
** a）读取不需要的列会增加CPU、IO、NET消耗*
** b）不能有效的利用覆盖索引**
** c）使用SELECT 容易在增加或者删除字段后出现程序BUG*
（24）禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性解读：容易在增加或者删除字段后出现程序BUG
（25）禁止使用属性隐式转换解读：SELECT uid FROM t_user WHERE phone=13800000000 会导致全表扫描，而不能命中phone索引，猜猜为什么？（这个线上问题不止出现过一次）
（26）禁止在WHERE条件的属性上使用函数或者表达式解读：SELECT uid FROM t_user WHERE
from_unixtime(day)>='2017-01-15' 会导致全表扫描正确的写法是：SELECT uid FROM t_user WHERE day>=unix_timestamp('2017-01-15 00:00:00')
（27）禁止负向查询，以及%开头的模糊查询解读：
a）负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描
b）%开头的模糊查询，会导致全表扫描
（28）禁止使用JOIN查询，禁止大表使用子查询解读：会产生临时表，消耗较多内存与CPU，极大影响数据库性能
（29）禁止使用OR条件，必须改为IN查询解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮助实施查询优化呢？
（30）应用程序必须捕获SQL异常，并有相应处理
（31）同表的增删字段、索引合并一条DDL语句执行，提高执行效率，减少与数据库的交互。

44 MySQL自增ID表insert了9条记录，又删掉了第7,8,9条记录，再把MySQL重启，再insert一条记录，这条记录的ID是10还是7？

• 表的存储引擎是Myisam，新纪录的ID是10，因为Myisam将自增主键最大ID记录到文件中，重启数据库后最大ID记录不会丢失。
• 表的存储引擎是InnoDB，新纪录的ID是7，因为Myisam将自增主键最大ID记录到内存中，重启数据库后最大ID记录会丢失。

45 传统的RDBMS&NoSQL区别？

• RDBMS SQL

  • 高度组织化结构化数据
  • 结构化查询语言（SQL）
  • 数据和关系都存储在单独的表中。
  • 数据操纵语言，数据定义语言
  • 严格的一致性
  • 基础事务

• NoSQL

  • 代表着不仅仅是SQL
  • 没有声明性查询语言
  • 没有预定义的模式
  • -键 - 值对存储，列存储，文档存储，图形数据库
  • 最终一致性，而非ACID属性
  • 非结构化和不可预知的数据
  • CAP定理
  • 高性能，高可用性和可伸缩性

46 数据的垂直拆分和水平拆分优缺点？拆分的原则是什么？多数据源怎么管理？

共同的缺陷

1. 引入分布式事务的问题。
2. 跨节点Join 的问题。
3. 跨节点合并排序分页问题。

垂直拆分

优点：

1. 拆分后业务清晰，拆分规则明确。
2. 系统之间整合或扩展容易。
3. 数据维护简单。

缺点：

1. 部分业务表无法join，只能通过接口方式解决，提高了系统复杂度。
2. 受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高。
3. 事务处理复杂。

水平拆分

优点：

1. 不存在单库大数据，高并发的性能瓶颈。
2. 对应用透明，应用端改造较少。
3. 按照合理拆分规则拆分，join操作基本避免跨库。
4. 提高了系统的稳定性跟负载能力。

缺点：

1. 拆分规则难以抽象。
2. 分片事务一致性难以解决。
3. 数据多次扩展难度跟维护量极大。
4. 跨库join性能较差。

拆分原则

1. 尽量不拆分，架构是进化而来，不是一蹴而就。(SOA)
2. 最大可能的找到最合适的切分维度。
3. 由于数据库中间件对数据Join 实现的优劣难以把握，而且实现高性能难度极大，业务读取 尽量少使用多表Join -
   尽量通过数据冗余，分组避免数据垮库多表join。
4. 尽量避免分布式事务。
5. 单表拆分到数据1000万以内。

多数据源管理

A. 客户端模式，在每个应用程序模块中配置管理自己需要的一个（或者多个）数据源，直接访问各个 数据库，在
模块内完成数据的整合。
优点：相对简单，无性能损耗。
缺点：不够通用，数据库连接的处理复杂，对业务不够透明，处理复杂。

B. 通过中间代理层来统一管理所有的数据源，后端数据库集群对前端应用程序透明；
优点：通用，对应用透明，改造少。
缺点：实现难度大，有二次转发性能损失。

47