数据库面试题

• 数据库面试题
• • 1. 数据库的三范式是什么
  • • 1.1 三大范式
  • 1.2 为什么要反范式设计
  • • 1.3 MySQL数据库引擎有哪些
    • • 1.3.1 MyISAM
      • 1.3.2 Innodb
      • 1.3.3 MEMORY
      • 1.3.4 MRG_MYISAM
      • Blackhole
    • 1.4 MyISAM和Innodb的区别
    • • 1.4.1 事务
      • 1.4.2 性能
      • 1.4.3 行数保存
      • 1.4.4 索引存储
      • 1.4.5 锁的支持
      • 1.4.6 如何选择
  • 2. 如何进行SQL优化
  • • 2.1 SQL优化注意事项
    • • 2.1.1 添加索引
      • 2.1.2 最左前缀法则
      • 2.1.3 尽量全值匹配
      • 2.1.4 不在索引列上做任何操作
      • 2.1.5 范围条件放最后
      • 2.1.6 覆盖索引尽量用
      • 2.1.7 不等于要慎用
      • 2.1.8 如果一定要需要使用不等于,请用覆盖索引
      • 2.1.9 Null/Not 有影响
      • 2.1.10 自定列为NOT NULL
      • 2.1.11 Like查询要当心
      • 2.1.12 字符类型加引号
      • 2.1.13 OR改UNION效率更高
    • 2.2 数据库优化的手段
    • 2.3 为什么使用数据索引能提高效率
    • • 2.3.1 索引使用B+树
    • 2.4 B+树索引和哈希索引的区别
    • • 2.4.1 B+树
      • 2.4.1.1 查询过程
    • 2.4.2 哈希索引
    • 2.4.3 哈希索引的优势
    • 2.4.4 哈希索引不适用的场景
    • 2.4.5 场景区分
    • 2.5 B树和B+树的区别
    • • 2.5.1 B树的特点
      • 2.5.2 B+树的特点
    • 2.6 为什么采用B+树而不采用B树
    • • 2.6.1 磁盘读写代价更低
      • 2.6.2 查询效率更加稳定
    • 2.7 什么是MySQL的联合索引
    • • 2.7.1 联合索引的作用
    • 2.8 为什么要使用联合索引
    • • 2.8.1 减少开销
      • 2.8.2 覆盖索引
      • 2.8.3 效率高
    • 2.9 为什么联合索引必须遵循最左原则
    • • 2.9.1 最左前缀原则
    • 2.10 聚簇索引和非聚簇索引
    • • 2.10.1 聚簇索引
      • 2.10.2 非聚簇索引
      • 2.10.3 优缺点
      • 2.10.4 何时使用聚簇索引与非聚簇索引
    • 2.11 什么情况下应不建或少建索引
    • 2.12 为什么主键通常建议使用自增id
    • 2.13 创建索引时需要注意什么？
  • 3. MySQL的事务特性和隔离级别
  • • 3.1 事务的四个特性
    • 3.2 四种隔离级别
    • • 3.2.1 读取未提交内容
      • 3.2.2 读取已提交内容
      • 3.2.3 可重读
      • 3.2.4 可串行化
    • 3.3 什么是脏读，幻读，重复读
    • 3.4 什么是MVCC
    • • 3.4.1 实现机制
      • 3.4.2 undo-log
      • 3.4.3 Read View
      • • 3.4.3.1 Read View 创建
        • 3.4.3.2 可见行判断
        • 3.4.3.3 判断规则
    • 3.5 MySQL有那些锁
    • • 3.5.1 锁粒度
      • 3.5.2 锁兼容性
      • 3.5.3 锁模式
    • 3.6 如果只有一条select语句，它加锁吗？
    • 3.7 数据库崩溃时事务的恢复机制（REDO日志和UNDO日志）?
    • • 3.7.1 redo log
      • 3.7.2 undo log
    • 3.8 百万级别或以上的数据如何删除
    • • 3.8.1 为什么不能直接删除
      • 3.8.2 新建表方式
      • 3.8.3 删除索引方式
  • 4. MySQL优化手段有哪些
  • • 4.1 优化手段
    • • 4.1.1 优化数据库表结构的设计
      • • 4.1.1.1 字段的数据类型
        • 4.1.1.2 数据类型的长度
        • 4.1.1.3 表的存储引擎
        • 4.1.2 SQL优化
        • 4.1.3 分表
        • 4.1.4 大事务
        • 4.1.5 数据库参数配置优化
        • 4.1.6 主从复制，读写分离
        • 4.1.7 增加缓存层
        • 4.1.8 升级服务器硬件
    • 4.2 大量数据如何分表分库
    • • 4.2.1 单库单表存在的问题
      • 4.2.2 分库分表方式
      • 4.2.3 分库分表存在的问题
    • 4.3 分库分表后如何进行统计分析以及分页查询
    • • 4.3.1 唯一主键
      • 4.3.2 分表分库
      • 4.3.3 客户端查询
      • 4.3.4 其他端查询
      • • 4.3.4.1 双写
        • 4.3.4.2 宽表
        • 4.3.5 分页统计

数据库面试题

1. 数据库的三范式是什么

1.1 三大范式

第一范式
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

比如xx省xx市xx县，这就不符合第一范式，要拆分成省，市，县都独立一个字段

第二范式
第二范式在第一范式的基础之上更进一层，第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言），也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如用户表里放了个角色，应该用中间表做关联管理

第三范式
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

比如用户表中有一个字段是角色id，这是可以的，但是不能在用户表中加入角色的名称等信息

第四范式（4NF）：（一个表的主键只对应一个多值）
在第三范式的基础之上，数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合BC范式。
也就是说如果是复合关键字，则复合关键字之间也不能存在函数依赖关系

举一个简单的例子：

例如，供应商表(供应商，商品和商品数量，供应商联系人)，在这个表中，同一个供应商可能会有多个商品和商品数量，同样，同一个供应商也可能会有多个供应商联系人，即这里存在着多值事实，不符合第四范式。如果要符合第四范式，只需要将上表分为两个表，使它们只有一个多值事实，例如供应商表一(商品id，商品数量)，供应商表二(供应商，供应商联系人），两个表都只有一个多值事实，所以符合第四范式。

总结
总结：三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。

1.2 为什么要反范式设计

我们知道范式设计只会让表的数量越来越多，在数据表优化上，简化了数据的冗余。但是有冗余一定不好么？如果数据表越来越多，查询一条记录，需要多表联查，反而会增加查询效率。此时如果让数据表允许少量的冗余，通过空间换时间，也是查询优化的一种思想。
反范式设计应用场景

1. 当冗余信息有价值，或者能提高查询效率的时候可采用反规范化设计。
2. 不经常增删改的数据表，可以进行反范式设计。
3. 数据仓库的设计，可采用反范式设计，数据仓库主要用于存储历史数据，不经常进行增删改
   总结
   范式与反范式设计没有优劣之分，只有更合适的说法，两者需要混合使用，范式与反范式设计是不是有点中庸之道的思想，好多东西都是这样，有利有弊，混合使用才是最大的利，不存在完美的设计，没有最好，只有最合适

1.3 MySQL数据库引擎有哪些

可用通过 show engines; 来查看支持的引擎有哪些， show variables like '%storage_engine%'; 可以查看默认的引擎数据库引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是插件式存储引擎。

1.3.1 MyISAM

不支持事务，不支持外键约束，不支持行级锁，操作时需要锁定整张表，不过会保存表的行数，所以当执行 select count(*) from tablename 时执行特别快。
适用场景：用于管理非事务表，提供高速检索及全文检索能力，适用于有大量的select操作的表，如 日志表
MyISAM 管理非事务表、是ISAM 的扩展格式。除了提供ISAM里所没有的索引的字段管理等的大量功能、MyISAM 还使用一种表格锁定的机制、来优化多个并发的读写操作。
MyISAM 提供高速存储和检索、以及全文搜索能力；在MYSQL5.5版本及以下的所有MYSQL配置里被支持。也是默认的存储引擎。

1.3.2 Innodb

支持事务，是事务安全的，提供行级锁与外键约束，有缓冲池，用于缓冲数据和索引。
适用场景：用于事务处理，具有ACID事物支持，应用于执行大量的insert和update操作的表。

1.3.3 MEMORY

所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY，它对表的大小有要求，不能建立太大的表，所以，这类数据库只使用在相对较小的数据库表。
适用场景：主要用于内容变化不频繁的表，或者作为中间的查找表，对表的更新要谨慎因为数据没有被写入到磁盘中，服务关闭前要考虑好数据的存储

1.3.4 MRG_MYISAM

他将MyIsam引擎的多个表聚合起来，但是他的内部没有数据，真正的数据依然是MyIsam引擎的表中，但是可以直接进行查询、删除更新等操作
比如：我们可能会遇到这样的问题，同一种类的数据会根据数据的时间分为多个表，如果这时候进行查询的话，就会比较麻烦，Merge可以直接将多个表聚合成一个表统一查询，然后再删除Merge表（删除的是定义），原来的数据不会影响。

Blackhole

任何写入到此引擎的数据均会被丢弃掉， 不做实际存储；Select语句的内容永远是空。

1.4 MyISAM和Innodb的区别

1.4.1 事务

MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持，提供事务支持以及外部键等高级数据库功能。
InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “a%”

1.4.2 性能

MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快。

1.4.3 行数保存

InnoDB 中不保存表的具体行数，也就是说，执行 select count(*) fromtable 时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时，两种表的操作是一样的。

1.4.4 索引存储

对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
MyISAM是非聚集索引，也是使用B+Tree作为索引结构，MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

1.4.5 锁的支持

MyISAM只支持表锁。InnoDB支持表锁、行锁 行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的

1.4.6 如何选择

1. 是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
2. 如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。
3. 系统奔溃后，MyISAM恢复起来更困难，能否接受；
4. MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

2. 如何进行SQL优化

2.1 SQL优化注意事项

2.1.1 添加索引

当建立了索引列后，能在where条件中使用索引的尽量所用。

2.1.2 最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

2.1.3 尽量全值匹配

针对联合索引需要遵循最左前缀原则以及中间不可断

2.1.4 不在索引列上做任何操作

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

2.1.5 范围条件放最后

中间有范围查询会导致后面的索引列全部失效

2.1.6 覆盖索引尽量用

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select * 使用

2.1.7 不等于要慎用

mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

2.1.8 如果一定要需要使用不等于,请用覆盖索引

使用覆盖索引能够减少回表操作

2.1.9 Null/Not 有影响

注意null/not null对索引的可能影响

2.1.10 自定列为NOT NULL

在字段为not null的情况下，使用is null 或 is not null 会导致索引失效

2.1.11 Like查询要当心

like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作

2.1.12 字符类型加引号

字符串不加单引号索引失效

2.1.13 OR改UNION效率更高

2.2 数据库优化的手段

1. 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
2. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
3. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫
   描。
4. 尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。
5. in 和 not in 也要慎用，否则会导致全表扫描，对于连续的数值，能用 between 就不要用 in 了
6. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
7. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为
   条件时才能保证系统使用该索引，否则该索引将不会被使 用，并且应尽可能的让字段顺序与索引
   顺序相一致。
8. 很多时候用 exists 代替 in 是一个好的选择
9. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复
   时，SQL查询可能不会去利用索引，如一表中有字段 sex，male.female几乎各一半，那么即使在
   sex上建了索引也对查询效率起不了作用。
10. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update
    的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数较好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
11. 应尽可能的避免更新 聚簇索引数据列，因为 簇索引数据列的顺序就是表记录的物理存储顺序，一
    旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新聚
    簇索引数据列，那么需要考虑是否应将该索引建 聚簇索引。
12. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性
    能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而
    对于数字型而言只需要比较一次就够了。
13. 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存
    储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
14. 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
15. 避免频繁创建和删除临时表，以减少系统表资源的消耗。
16. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
17. 尽量避免大事务操作，提高系统并发能力。

2.3 为什么使用数据索引能提高效率

• 数据索引的存储是 有序的
• 在有序的情况下, 通过索引查询一个数据是无需遍历所有记录的

2.3.1 索引使用B+树

Mysql索引使用的是B+树，因为索引是用来加快查询的， B+树通过对数据进行排序所以是可以提高查询
速度的，然后通过1个节点中可以存储多个元素，从 可以使得B+树的出度不会太大 ，在Mysql中1个
Innodb 的大小就是1个B+树节点，1 个Innodb 默认16kb，所以一般情况下一颗两层的B+树可以存2000万
左右的数据，然后通过利用B+树叶 节点存储了所有数据并且进行了排序，并且叶子节点之间有指针，可
以很好的支持全表扫描，范围查找等SQL语句。

2.4 B+树索引和哈希索引的区别

2.4.1 B+树

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接，是有序的。
叶子层为B+树的叶子节点，所有的叶子节点存储的是指向数据的指针而非数据本身。同时每个叶子结点都有指向下一个叶子结点的双向链接。
枝干层和Level 2 树干层就是纯索引节点（数据）和主键，同一层的索引节点也都有指向下一个索引节
点的双向链接。

2.4.1.1 查询过程

B+树查询过程的简易说明，使用数字来说明其查询过程

1. B+树的上一层存储的是下一层的最小数编号
2. 查询数据是从最上层及树干层开始向下查询最后查询到叶子层
3. 叶子层仅存储真实数据的指针
   我们以找数字7的数据为例
4. 把树干分为相邻的分支5、28、65
5. 树干层：7属于5-28之间，28为一个分支的最少值，7不应该属于28所在分支；7大于5，7在5所在
   分支
6. 树枝层：同理树干层选择5所在分支
7. 最终在叶子层查到数字7所在的真实数据的指针。最终查询到数据内容

2.4.2 哈希索引

采用 Hash 进行检索效率非常高，基本上一次检索就可以找到数据，而 B+ 树需要自顶向下依次查找，
多次访问节点才能找到数据，中间需要多次 I/O 操作，理论上来说 Hash 比 B+ tree更快。下图是引用
网上的Hash索引图片和 B+tree 索引图片，便于直观的理解2种索引结构。

2.4.3 哈希索引的优势

等值查询，哈希索引具有绝对优势（前提是：没有大量重复键值，如果大量重复键值时，哈希索引的效率很低，因为存在所谓的哈希碰撞问题。）

2.4.4 哈希索引不适用的场景

不支持范围查询
不支持索引完成排序
不支持联合索引的最左前缀匹配规则

2.4.5 场景区分

大多数场景下，都会有组合查询，范围查询、排序、分组、模糊查询等查询特征，Hash 索引无法
满足要求，建议数据库使用B+树索引。
在离散型高，数据基数大，且等值查询时候，Hash索引有优势。

2.5 B树和B+树的区别

2.5.1 B树的特点

B树，每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null，叶子节点不
包含任何关键字信息

1. 节点排序
2. 1个节点了可以存多个元素，多个元素也排序了

2.5.2 B+树的特点

B+树，所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接，所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。(而B 树的非终节点也包含需要查找的有效信息)

1. 拥有B树的特点
2. 叶子节点之间有指针
3. 非叶子节点上的元素在叶子节点上都冗余了，也就是叶子节点中存储了所有的元素，并且排好顺序

2.6 为什么采用B+树而不采用B树

2.6.1 磁盘读写代价更低

1. B+的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B树更小
2. 如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一
   次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

2.6.2 查询效率更加稳定

由于非叶子点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查
找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

2.7 什么是MySQL的联合索引

联合索引又叫复合索引，MySQL 中的联合索引，遵循最左匹配原则，比如，联合索引为key(a,b,c)，则能触发索引的搜索组合是 a|ab|abc 这三种查询。

2.7.1 联合索引的作用

• 用于多字段查询，比如，建了一个 key(a,b,c) 的联合索引，那么实际等于建了 key(a)、key(a,b)、 key(a,b,c)等三个索引，我们知道，每多一个索引，就会多一些写操作和占用磁盘空间的开销，尤
  其是对大数据量的表来说，这可以减少一部分不必要的开销；
• 覆盖索引，比如，对于联合索引 key(a,b,c) 来说，如果使用 SQL： select a,b,c from table where a=1 and b = 1 ，就可以直接通过遍历索引取得数据，而无需回表查询，这就减少了随机 的 IO 操作，减少随机的 IO操作，可以有效的提升数据库查询的性能，是非常重要的数据库优化 手段之一；
• 索引列越多，通过索引筛选出的数据越少。

2.8 为什么要使用联合索引

2.8.1 减少开销

建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个
索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

2.8.2 覆盖索引

对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and
col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少
io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提
升性能的优化手段之一。

2.8.3 效率高

索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where
col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通
过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3=
3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率
提升可想而知！

2.9 为什么联合索引必须遵循最左原则

2.9.1 最左前缀原则

有时候我们会遇到不常见的查询请求，比如根据身份证号查询市民的家庭住址。如果我们走全表扫描效
率太低，单独创建一个索引又浪费空间，这会就可以用B+树索引的最左前缀原则。用联合索引 (name,
age) 来说明这个概念：

比如需求是查询所有名字是张三的人，可以快速定位到 ID4，然后向后扫描所有满足条件的数据。如果查询姓张的人，sql里条件部分可以这样写 “ where name like ‘张%’ ”，同样也能用到上面的联合索引。
这里最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。
那我们该如何建立联合索引呢？首先要遵循一个原则：如果通过调整索引顺序，可以少维护一个索引，
那么这个顺序就是优先考虑的。比如上面的(id_card，name)索引，根据 id_card 查询家庭住址就不要
再建立 联合索引了。
如果既有联合查询，又有基于a、b各自的查询，比如查询语句中只有索引b是不能用联合索引 (a，b)
的。那么就要建立两个索引，考虑的原则是索引占用空间。比如市民信息表中 name 字段比 age 占用
空间大，就创建一个 (name, age)联合索引和一个 (age)的单字段索引。
对于联合索引，还有一个问题：字段中不符合最左前缀的部分会怎么样？同样用上面的 (name, age) 索
引来说明，现在有一个需求：“查询出名字第一个字是张，年龄为10岁的男孩”。sql是这样的：

select * from T where name like '张%' and age = 10 and ismale = 1;

在MySQL5.6之前，只能不停的回表查到最后的结果；5.6之后引入了索引下推优化，就是在索引遍历过
程中，对索引中包含的字段中先做判断，过滤掉不满足条件的记录，减少回表的次数。用两个图来解
释：分别是优化前后


可以看到原来是4次回表，优化后变成2次，减少了对资源的访问。

2.10 聚簇索引和非聚簇索引

2.10.1 聚簇索引

聚集索引决定数据在磁盘上的物理排序，一个表只能有一个聚集索引，一般用primary key来约束。

2.10.2 非聚簇索引

它并不决定数据在磁盘上的物理排序，索引上只包含被建立索引的数据，以及一个行定位符rowlocator，这个行定位符，可以理解为一个聚集索引物理排序的指针，通过这个指针，可以找到行数据。
innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇
索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位
置，而是主键值

2.10.3 优缺点

优点

1. 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比
   非聚簇索引更快
2. 聚簇索引对于主键的排序查找和范围查找速度非常快
   缺点
3. 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影
   响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
4. 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键
   为不可更新。
5. 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

2.10.4 何时使用聚簇索引与非聚簇索引

非聚簇索引一定会回表查询吗？
不一定，如果查询语句的字段全部命中了索引，那么就不必再进行回表查询（覆盖索引就是这么回
事）。

2.11 什么情况下应不建或少建索引

1. 表记录太少
2. 经常插入、删除、修改的表
3. 数据重复且分布平均的表字段
4. 经常和主字段一块查询但主字段索引值比较多的表字段

2.12 为什么主键通常建议使用自增id

**聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。**如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

2.13 创建索引时需要注意什么？

非空字段
应该指定列为NOT NULL，除非你想存储NULL。 在 MySQL 中，含有空值的列很难进行查 询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂，你应该用0、一个特殊的值或者 一个空串代替空值；
取值离散大的字段
（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数 查看字段的差异
值，返回值越大说明字段的唯一值越多字段的离散程度高；
索引字段越小越好
数据库的数据存储以页为单位，一页存储的数据越多，一次IO操作获取的数据越大，效率越高。

3. MySQL的事务特性和隔离级别

3.1 事务的四个特性

事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和
持续性（ Durability ），这四个特性简称为 ACID 特性。
原子性
一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

Undo log(记录之前SQL的相关信息，如果需要回滚，根据日志去做相反操作)

一致性
在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性
**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。**事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

写-写操作：锁
写-读：MVCC

持久性
事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失
Redo log（修改操作记录到redo log 预写式日志 追加模式-顺序IO，只需要同步真正需要的数据）
刷盘机制

1. 秒一次
2. 同步写入
3. 异步写入

3.2 四种隔离级别

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是
不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

3.2.1 读取未提交内容

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果，本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

3.2.2 读取已提交内容

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的），它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。
这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例
处理其间可能会有新的commit，所以同一select可能返回不同结果。

3.2.3 可重读

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据
行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。
简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制
（MVCC，Multiversion Concurrency Control）机制解决了该问题。

3.2.4 可串行化

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁，在这个级别，可能导致大量的超时现象和锁竞争。

3.3 什么是脏读，幻读，重复读

这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。
脏读
某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
不可重复读
在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
幻读
在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

3.4 什么是MVCC

MVCC其实就是一个多版本并发控制，即多个不同版本的数据实现并发控制的技术，其基本思想是为每次事务生成一个新版本的数据，在读数据时选择不同版本的数据即可以实现对事务结果的完整性读取。
MVCC (Multi-Version Concurrency Control) (注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)是一种基于多版本的并发控制协议，只有在InnoDB引擎下存在。MVCC是为了实现事务的隔离性，通过版本号，避免同一数据在不同事务间的竞争，你可以把它当成基于多版本号的一种乐观锁。当然，这种乐观锁只在事务级别未提交锁和已提交锁时才会生效。MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能。
MVCC只工作在REPEATABLE READ和READ COMMITED隔离级别下。READ UNCOMMITED不是MVCC兼容的，因为查询不能找到适合他们事务版本的行版本；它们每次都只能读到最新的版本。SERIABLABLE也不与MVCC兼容，因为读操作会锁定他们返回的每一行数据

3.4.1 实现机制

InnoDB在每行数据都增加三个隐藏字段

• 6字节的事务ID(DB_TRX_ID)字段：用来标识最近一次对本行记录做修改(insert|update)的事务的标识符，即最后一次修改(insert|update)本行记录的事务id。至于delete操作，在innodb看来也不过是一次update操作，更新行中的一个特殊位将行表示为deleted，并非真正删除。
• 7字节的回滚指针(DB_ROLL_PTR)字段：指**写入回滚段(rollback segment)的 undo log record (撤销日志记录记录)。**如果一行记录被更新, 则 undo log record 包含 ‘重建该行记录被更新之前内容’ 所必须的信息。
• 6字节的DB_ROW_ID字段：包含一个随着新行插入而单调递增的行ID，当由innodb自动产生聚集索引时，聚集索引会包括这个行ID的值，否则这个行ID不会出现在任何索引中。

结合聚簇索引的相关知识点，如果表中没有主键或合适的唯一索引，也就是无法生成聚簇索引的时候，InnoDB会帮我们自动生成聚集索引，但聚簇索引会使用DB_ROW_ID的值来作为主键；如果有主键或者合适的唯一索引，那么聚簇索引中也就不会包含 DB_ROW_ID了 。

在多版本并发控制中，为了保证数据操作在多线程过程中，保证事务隔离的机制，降低锁竞争的压力，保证较高的并发量。在每开启一个事务时，会生成一个事务的版本号，被操作的数据会生成一条新的数据行（临时），但是在提交前对其他事务是不可见的，对于数据的更新（包括增删改）操作成功，会将这个版本号更新到数据的行中，事务提交成功，将新的版本号更新到此数据行中，这样保证了每个事务操作的数据，都是互不影响的，也不存在锁的问题。

3.4.2 undo-log

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

3.4.3 Read View

事务快照是用来存储数据库的事务运行情况。一个事务快照的创建过程可以概括为：

1. 查看当前所有的未提交并活跃的事务，存储在数组中
2. 选取未提交并活跃的事务中最小的XID，记录在快照的xmin中
3. 选取所有已提交事务中最大的XID，加1后记录在xmax中

3.4.3.1 Read View 创建

对于隔离级别read committed,每次都生成一个新的readview

对于隔离级别repeatable read，只在事务开启时，生成一个readview，直到事务提交，不发生变化，所以可以保证可重复读。

生成readview时会生成以下信息

• m_ids:表示活跃事务id列表
• min_trx_id:活跃事务中的最小事务id
• max_trx_id:已创建的最大事务id
• creator_trx_id:当前的事务id

3.4.3.2 可见行判断

Read View （主要是用来做可见性判断的）：创建一个新事务时，copy一份当前系统中的活跃事务列表。意思是，当前不应该被本事务看到的其他事务id列表。
对于Read View快照的生成时机，也非常关键，正是因为生成时机的不同，造成了RC，RR两种隔离级别的不同可见性

• 在innodb中(默认repeatable read级别)，事务在begin/start transaction之后的第一条select读操 作后，会创建一个快照(Read View)，将当前系统中活跃的其他事务记录记录起来
• 在innodb中(read committed级别)，事务中每条select语句都会创建一个快照(Read View)

3.4.3.3 判断规则

• 被访问的trx_id与readview中的creator_trx_id相同，表示当前事务在访问自己修改的记录，可 见；
• 被访问的trx_id小于min_trx_id，表明该版本已提交，可见，返回；
• 被访问的trx_id大于等于max_trx_id，表明该版本在生成readview时，还未提交，不可见；
• 被访问的trx_id在min_trx_id和max_trx_id之间，判断是否在m_ids中，如果在，则说明生成
• readview时，版本事务未提交，该版本不可见；如果不在，则说明生成readview时，该版本事务 已提交可见。

3.5 MySQL有那些锁

3.5.1 锁粒度

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

3.5.2 锁兼容性

共享锁
共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

排它锁
排他锁又称写锁、独占锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

3.5.3 锁模式

记录锁
记录锁锁住的是索引记录，如果使用索引作为条件命中了记录，那么就是记录锁，被锁住的记录不能被别的事务插入相同的索引键值，修改和删除。
间隙锁
当我们使用索引无论是等值还是范围查询，没有命中一条记录时候，加的就是间隙锁。
间隙锁是对索引记录之间的间隙的锁定，或者是对第一个或最后一个索引记录之前的间隙的锁定
临间锁
临键锁是记录锁和间隙锁的组合锁
意向锁
意向锁是表级别的锁，mysql有两种意向锁：意向共享锁(IS) 意向排它锁(IX)。
意向锁遵循两个规则，在事务可以获取表中某行的共享锁之前，它必须首先获取该表中的IS锁或更强的锁；在事务可以获取表中某行的排它锁之前，它必须首先获取该表中的IX锁。

3.6 如果只有一条select语句，它加锁吗？

MySQL5.5版本引入了MDL锁（metadata lock），用于解决或者保证DDL操作与DML操作之间的一致性
MDL锁是系统默认会加的，但却是你不能忽略的一个机制，如果一个事务中需要进行查询，就会自动加上MDL锁，会阻塞对于表的元数据的修改操作，必须要等到事务提交后，对于表的修改操作才能执行。

3.7 数据库崩溃时事务的恢复机制（REDO日志和UNDO日志）?

3.7.1 redo log

重做日志(redo log)用来保证事务的持久性，即事务ACID中的D。实际上它可以分为以下两种类型：

• 物理Redo日志
• 逻辑Redo日志

在InnoDB存储引擎中，大部分情况下 Redo是物理日志，记录的是数据页的物理变化。而逻辑Redo日志，不是记录页面的实际修改，而是记录修改页面的一类操作，比如新建数据页时，需要记录逻辑日志。关于逻辑Redo日志涉及更加底层的内容，这里我们只需要记住绝大数情况下，Redo是物理日志即可，DML对页的修改操作，均需要记录Redo。 redo日志文件名格式为 ib_logfile0或ib_logfile1，文件大小默认50M，Redo log的主要作用是用于数据库的崩溃恢复

3.7.2 undo log

undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。
undo log存在于一个特殊的段中，存在于表空间中，和主键id组织的数据存在一个文件中，毕竟每行数据都有个指向undo log的指针。
当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

3.8 百万级别或以上的数据如何删除

由于索引需要额外的维护成本，因为索引文件是单独存在的文件，所以当我们对数据的增加，修改，删除都会产生额外的对索引文件的操作，这些操作需要消耗额外的I/O，会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询mysql官方手册得知删除数据的速度和创建的索引数量是成正比的。

3.8.1 为什么不能直接删除

DELETE操作不会减少表或索引所占用的空间。
delete是DML语言，删除数据后，并不会真正的删除数据，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。
还有，InnoDB 数据库在使用 delete 进行删除操作的时候，只会将已经删除的数据标记为删除，并没有把数据文件删除，因此并不会彻底的释放空间。这些被删除的数据会被保存在一个链接清单中，当有新数据写入的时候，MySQL 会重新利用这些已删除的空间进行再写入。

3.8.2 新建表方式

适用于需要保留的数据不多的情况

1. 基于老表新建新表！
2. 插入不需要删除的数据（几千万的数据量一定要分批插入，一次50万为最佳，毕竟mysql的数据处理能力有限），可以按ID查询后插入！
3. 删除掉老表
4. 将新表重名了为老表

3.8.3 删除索引方式

适用于大量数据删除

1. 备份数据库
2. 关闭日志记录
3. 所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多种）
4. 然后删除其中无用的数据（此过程需要不到两分钟）
5. 删除完成后重新创建索引（此时数据较少了）创建索引也非常快，约十分钟左右。
6. 与之前的直接删除绝对是要快速很多，更别说万一删除中断，一切删除会回滚。

4. MySQL优化手段有哪些

4.1 优化手段

4.1.1 优化数据库表结构的设计

4.1.1.1 字段的数据类型

不同的数据类型的存储和检索方式不同，对应的性能也不同，所以说要合理的选用字段的数据类型。比如人的年龄用无符号的unsigned tinyint即可，没必要用integer

在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执行查询的时候，数据库不用去比较NULL值，对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。

4.1.1.2 数据类型的长度

数据库最终要写到磁盘上，所以字段的长度也会影响着磁盘的I/O操作，如果字段的长度很大，那么读取数据也需要更多的I/O, 所以合理的字段长度也能提升数据库的性能。比如用户的手机号11位长度，没必要用255个长度。

4.1.1.3 表的存储引擎

常用的存储引擎有MyISAM、InnoDB、Memory，不同的存储引擎拥有不同的特性，所以要合理的利用每种存储引擎的长处和优点来提供数据的性能。MyISAM不支持事务，表级锁，但是查询速度快，InnoDB支持事务，行锁。

4.1.2 SQL优化

MySQL性能优化的一个很重要的手段就是对SQL语句的优化。其中最重要的方式就是使用索引。

4.1.3 分表

当一个表的数据量很大的时候，查询就变的很慢，所以减少表里的记录的数量是优化的一种方式，这种方式就是将一张表的数据拆分成多张表，这样每张表的数量就减少了，这样查询速度就相对来说就快了一些。

4.1.4 大事务

大事务：运行时间比较长，操作的数据比较多的事务
风险：锁定太多的数据，造成大量的阻塞和锁超时，回滚时所需时间比较长，执行时间长容易造成主从延迟
避免一次处理太多的数据，移除不必要在事务中的select操作

4.1.5 数据库参数配置优化

mysql是一个高度定制化的数据库系统，提供了很多配置参数(如最大连接数、数据库占用的内存等)，这些参数都有默认值，一般默认值都不是最佳的配置，一般都需要根据应用程序的特性和硬件情况对
mysql的配置进行调整。
例如最大连接数默认为100，即使SQL语句优化的再好，硬件设备配置再高，当请求超过100时都要再等待，这就是配置不合理导致MySQL不能发挥它的最大能力。

4.1.6 主从复制，读写分离

一台MySQL服务器同一时间点支持的并发数是有限的，当大量并发(如秒杀活动等，很多用户都同一时刻访问数据库)时，一台数据库处理不过来，所以增加MySQL服务器的数量也是一种增强数据库性能的方式。
通过使用MySQL主从复制，增删改操作走Master主服务器，查询走Slaver从服务器，这样就减少了只有一台MySQL服务器的压力。

4.1.7 增加缓存层

减少数据库连接也是一种优化手段，有些查询可以不用访问数据库，可以通过使用缓存服务器如redis、memcache、elasticsearch等增加缓存，减少数据库的连接

4.1.8 升级服务器硬件

当所有优化手段都用了，性能仍需要优化，那么只有升级MySQL服务器端硬件了，更快的磁盘IO设备，更强的CPU，更大的内存，更大的网卡流量(带宽)等。

4.2 大量数据如何分表分库

面试官：为什么要分库分表，以及常见的分表分库方案

4.2.1 单库单表存在的问题

单表太大
一张表的字段太多，数据太多，查询起来困难
单库太大
数据库里面的表太多，所在服务器磁盘空间装不下，IO次数多CPU忙不过来。

4.2.2 分库分表方式

水平分表分库
水平切分，当一个表中的数据量过大时，我们可以把该表的数据按照某种规则
垂直分表分库
垂直切分，即将表按照功能模块、关系密切程度划分出来，部署到不同的库上

4.2.3 分库分表存在的问题

事务问题
在执行分库分表之后，由于数据存储到了不同的库上，数据库事务管理出现了困难。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。
跨库跨表的join问题
在执行了分库分表之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上，这时，表的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表，结果原本一次查询能够完成的业务，可能需要多次查询才能完成。
增加数据管理负担和数据运算压力
额外的数据管理负担，最显而易见的就是数据的定位问题和数据的增删改查的重复执行问题，这些都可以通过应用程序解决，但必然引起额外的逻辑运算。
分页统计问题
业务要求查出成绩最好的100位，在进行分表之前，只需一个order by语句就可以搞定，但是在进行分表之后，将需要n个order by语句，分别查出每一个分表的前100名用户数据，然后再对这些数据进行合并计算，才能得出结果

4.3 分库分表后如何进行统计分析以及分页查询

有些电商的数据规模达到一定程度之后，比如淘宝或者美团的日订单量可能有几千万。在这样数据规模下，数据库面临很大的压力。通常，当数据库达到一定规模后需要对数据进行切分，对数据库或者表进行切分，有的需要纵向切分，有的需要横向切分。伴随着库表切分之后，对于数据库的查询就增加很大的难度，比如我们常会遇到分页查询。通常我们把分表使用的字段称作shardingkey，比如订单表按照用户ID，那么如果查询条件中不带用户ID查询怎么做分页？

4.3.1 唯一主键

一般我们数据库的主键都是自增的，那么分表之后主键冲突的问题就是一个无法避免的问题，最简单的办法就是以一个唯一的业务字段作为唯一的主键，比如订单表的订单号肯定是全局唯一的。
常见的分布式生成唯一ID的方式很多，最常见的雪花算法Snowflake、滴滴Tinyid、美团Leaf。以雪花算法举例来说，一毫秒可以生成4194304多个ID。
第一位不使用，默认都是0，41位时间戳精确到毫秒，可以容纳69年的时间，10位工作机器ID高5位是数据中心ID，低5位是节点ID，12位序列号每个节点每毫秒累加，累计可以达到2^12 4096个ID。

4.3.2 分表分库

分表后要怎么保证订单号的唯一搞定了，现在考虑下分表的问题。首先根据自身的业务量和增量来考虑分表的大小。
举个例子，现在我们日单量是10万单，预估一年后可以达到日100万单，根据业务属性，一般我们就支持查询半年内的订单，超过半年的订单需要做归档处理。

那么，因为唯一主键都是以订单号作为依据，以前你写的那些根据主键ID做查询的就不能用了，这就涉及到了历史一些查询功能的修改。都改成以订单号来查就行了。

4.3.3 客户端查询

首先说带shardingkey的查询，比如就通过订单号查询，不管你分页还是怎么样都是能直接定位到具体的表来查询的，显然查询是不会有什么问题的。
如果不是shardingkey的话，上面举例说的以订单号作为shardingkey的话，像APP、小程序这种一般都是通过用户ID查询，那这时候我们通过订单号做的sharding怎么办？很多公司订单表直接用用户ID做shardingkey，那么很简单，直接查就完了。那么订单号怎么办，一个很简单的办法就是在订单号上带上用户ID的属性。举个很简单的例子，原本41位的时间戳你觉得用不完，用户ID是10位的，订单号的生成规则带上用户ID，落具体表的时候根据订单号中10位用户ID hash取模，这样无论根据订单号还是用户ID查询效果都是一样的。
那么无论你是订单号还是用户ID作为shardingkey，按照以上的两种方式都可以解决问题了。那么还有一个问题就是如果既不是订单号又不是用户ID询怎么办？最直观的例子就是来自商户端或者后台的查询，商户端都是以商户或者说卖家的ID作为查询条件来查的，后台的查询条件可能就更复杂了。
现实中真正的流量大头都是来自于用户端C端，所以本质上解决了用户端的问题，这个问题就解了大半，剩下来自商户卖家端B端、后台支持运营业务的查询流量并不会很大，这个问题就好解。

4.3.4 其他端查询

4.3.4.1 双写

双写，双写就是下单的数据落两份，C端和B端的各自保存一份，C端用你可以用单号、用户ID做shardingkey都行，B端就用商家卖家的ID作为shardingkey就好了。有些同学会说了，你双写不影响性能吗？因为对于B端来说轻微的延迟是可以接受的，所以可以采取异步的方式去落B端订单。你想想你去淘宝买个东西下单了，卖家稍微延迟个一两秒收到这个订单的消息有什么关系吗？你点个外卖商户晚一两秒收到这个订单有什么太大影响吗？

4.3.4.2 宽表

另外一个方案就是走离线数仓或者ES查询，订单数据落库之后，不管你通过binlog还是MQ消息的形式，把数据同步到数仓或者ES，他们支持的数量级对于这种查询条件来说就很简单了。同样这种方式肯定是稍微有延迟的，但是这种可控范围的延迟是可以接受的。
而针对管理后台的查询，比如运营、业务、产品需要看数据，他们天然需要复杂的查询条件，同样走ES或者数仓都可以做得到。如果不用这个方案，又要不带shardingkey的分页查询，兄弟，这就只能扫全表查询聚合数据，然后手动做分页了，但是这样查出来的结果是有限制的。比如你256个片，查询的时候循环扫描所有的分片，每个片取20条数据，最后聚合数据手工分页，那必然是不可能查到全量的数据的。

4.3.5 分页统计

如果走了ES的宽表的话，我们对于分页以及统计就可以很轻松的解决了，我们分页以及统计查询直接从es中进行统计分析以及分页就可以的。