题目＞＞mysql＞＞1

基础知识

1、说一下mysql主从复制（Replication）的原理

概念：是指数据可以从一个Mysql的master节点复制到一个或多个slaver节点。
用途：

• 读写分离：在开发过程中，会遇到某个sql语句需要锁表（update、delete），导致mysql不可读。主从分离后，master用于写、slaver用于读；
• 数据实时备份：当某个节点发生故障时，可以方便故障切换；
• 高可用HA
• 架构扩展：将流量分散到各个存储节点，提升性能。

主从复制原理

• 主从复制涉及到三个线程，一个在主节点（log dump thread），两个在从节点（I/O thread、SQL thread）。
  • log dump thread：当slaver连接master时，master会创建一个log dump线程，用于发送bin-log。在读取bin-log时，此线程会对master的bin-log加锁；读取完成时，锁被释放。
  • I/O thread：slaver上执行了 start slave之后，slaver会创建一个I/O线程用于连接master，请求master的bin-log。I/O线程接收到master的log dump线程发来的bin-log后，保存在本地的relay-log中。
  • SQL thread：负责读取relay-log 中的内容，解析成具体的操作执行，最终保证主从一致性。
• 复制步骤
  • master的更新SQL（增删改） 被写到bin-log中；
  • slaver的I/O线程连接master，并请求从指定日志文件的指定位置之后的日志；
  • master接收到I/O请求后，log dump线程将指定的日志信息（包含bin-log file）返回给slaver；
  • slaver的I/O线程接收到内容后，将日志内容更新到本地的relay-log中，并将读取到的bin-log文件名和位置保存到master-info文件中，记录下一此请求的日志文件指定位置；
  • slaver的SQL线程检测到relay-log中新增内容后，会将relay-log的内容解析成具体的操作，并在本地数据库中执行。

mysql的主从复制模式

• 异步模式
  
  • mysql主从复制默认是异步模式。mysql的更新操作都保存到bin-log中，slaver连接master时，会主动从master获取最新的bin-log文件，并把bin-log处理为relay-log，再进行同步。
• 半同步模式
  
  • 每次用户commit时，需要master首先将bin-log同步到任意一台slaver上，只要接收到一台slaver的返回，master即可commit。
  • 优点：使主从数据库的数据延迟缩小；
  • 缺点：性能上会有降低，响应时间会变长；且master无法确认slaver是否已经同步到DB；
• 全同步模式
  • master和所有slaver全部执行了commit并确认之后才会向client返回成功。

2、mysql主从框架下，如何保证数据一致性问题

长链接

• master与slaver之间维持了一个长链接；master内部存在一个线程，专门服务于slaver的长链接；
• 为了保证master与slaver同步数据过程中不中断，造成数据丢失。

bin-log模式

• bin-log有三种格式，分别是statement、row和mixed。
  • statement：记录的是SQL原文，是binlog的默认方式。
    • 缺点：可能会存在由于主从数据库结构不一致（如索引不一致）而造成的更改数据错误，从而导致数据不一致。
  • row：记录的不是SQL原文，是两个event：Table_map（说明要操作的表）和Delete_rows（用于定义要删除的行为，记录删除的具体行数）。
    • 缺点：如果SQL删除10万行数据，row格式会很占空间，处理时会很消耗I/O。
  • mixed：mysql会根据执行的每一条具体的sql语句来区分使用statement还是row。

3、数据库主从延迟的原因与解决方案

主从延迟概念

• master写入binlog，当前时刻为T1；slaver接收到binlog，当前时刻为T2；slaver执行完此事务，当前时刻为T3。延迟时间为T3 - T1。

造成延迟的原因

• slaver的性能低于master。
  • 解决方案：提升slaver的性能，如更换机器、修改虚拟机配置等。
• slaver的流量压力过大。用户将大部分查询都打入slaver，导致slaver消耗大量CPU，影响同步速度。
  • 解决方案：增加slaver的机器数来分摊压力。
• 大事务造成的主从延迟。事务执行时间过长，如一次性delete太多SQL；使用了DDL语句（DDL：create、drop、alter；DML：update、delete、insert、select；DCL：grant）
  • 解决方案：将大事务进行拆分；闲时执行DDL语句。
• 网络延迟
• slaver过多，一般3-5个比较合适；
• 低版本的MySql只支持单线程复制，在高并发的状态下，master来不及传送给slaver。

4、数据库的高可用方案

双机主备（灾备）

• 架构描述：两台机器A和B，A为主库，负责读写；B为备库，只负责备份数据。如果A发生故障，则B提升为主库，负责读写；当A修复完成后，A变为备库，B的数据同步到A。
• 优点：一台机器故障了可以切换，操作简单；
• 缺点：只有一个库在工作，读写压力大，未能实现读写分离，并发也存在限制。

一主一从
一主多从

MariaDB同步多主机集群

• 架构描述：有代理层实现负载均衡，多个数据库可同时进行读写操作；各个数据库之间通过Galera Replication的方式进行数据同步；
• 优点：读写并发提升明显，可任意节点读写，自动剔除故障节点，具有高可用性；
• 缺点：无法支持大数据量。避免大数据卡死，如果集群中一个节点变慢，集群会变慢。

5、mysql的redo log、undo log、bin log异同？为什么binlog无法crash safe？

bin log

• bin log是mysql数据库级别的文件，记录了mysql数据库执行修改的所有操作，不会记录select和show语句；
• 有statement、row和mixed三种格式；
• 在实际应用中，使用场景为主从复制和数据恢复。
  • 主从复制：Master将binlog发送至slaver，实现主从一致；
  • 数据恢复：使用mysqlbinlog恢复工具来恢复数据。mysqlbinlog --start-position=219 --stop-position=1246 /mysql/data/binlog.000001 |mysql -uroot -p bhs

redo log

• redo log中记录的是要更新的数据。
• 如：数据A已经提交成功，并不会立即同步到磁盘，而是先记录到redo log中，等待合适的实际再刷盘，实现事务的持久性。
• 若不用redo log做缓冲，每次提交直接刷新到磁盘，可能会造成性能问题：
  • 因为Innodb是以页为单位进行磁盘交互，一个事务可能只修改了一个数据页A的几个字节，这时如果将完整的数据页A刷到磁盘的话，浪费资源；
  • 事务A可能修改多个物理上不连续的数据页，若是用随机IO写入，性能太差。
• 在实际应用中，用于持久化（如：主从复制的slaver持久化。）

undo log

• 当数据修改时，除了记录redo log，还会记录undo log。
• undo log用于数据的撤回操作。 它保留了记录修改之前的内容。
• 在实际应用中，可实现事务回滚。可根据undo log回溯到某个特定的版本的数据。

crash safe

• 过程：
  • 当mysql进程异常后，数据库会自动定位到上次checkpoint（是一个数字，每当数据页要刷入磁盘/数据页，checkpoint会记录日志和内容读取的位置）的位置LSN（日志序列号，每个数据页都存在LSN）
  • 系统扫描redo log，获取redo log的LSN；
  • 若redo的LSN大于数据页的LSN，说明重启前redo中的数据并未完全写入数据页中，系统会从checkpoint的LSN开始，从redo log中恢复数据。
• binlog无法实现crash safe的原因
  • redo log
    • 是一个固定大小，循环写的日志文件。具有前置性，只会记录未刷盘的日志，已经刷盘的数据会删除；
    • 只记录innoDB的事务日志；
    • 是物理日志，记录的是某个数据页上的修改；
    • 在事务进行过程中，会有redo log不断写入磁盘。
  • bin log
    • 是一个无限大小，追加写的日志文件。具有后置性，保存的是全量日志。
    • 记录所有的日志，包括innoDB和MyISAM等存储引擎的日志；
    • 是逻辑日志，记录的是SQL语句的原始逻辑；
    • 在事务提交前写入到磁盘，一个事务只写一次。
  • binlog中没有标志让innoDB判断哪些数据已经刷盘，哪些数据还没有。

6、varchar与char的区别？

varchar

• 是变长的，也就是说申请的是最大长度，占用的空间为实际字符长度+1，最后一个字节存储使用了多少空间。

char

• 是一个定长字段。加入申请了char(10)的空间，无论实际存储多少内容，该字段都占10个字符。

• char比varchar检索效率高。因此在使用中，如果确定某个字段的值的长度，可以使用char（如存储用户MD5加密后的密码）。

7、count函数的用法？

• count(*)：包括了所有的列，相当于行数。统计结果中包含列值为NULL的情况；
• count(1)：忽略所有的列。1表示一个固定值，也可以用count(2)、count(3)等。统计结果中包含值为NULL的情况；
• count(列名)：只包括列名那一列。会忽略列值为NULL的情况。

8、exist与in的区别？

-- in 
select * from a where id in (select id from b);

-- exists
select * from A where exists(select 1 from B where B.id = A.id);

• in
  • 使用in时，sql语句先执行子查询（先查询表b），在查询主查询（查询a）；
  • 根据小表驱动大表的原则，如果表a较大且有索引时，应用in查询;
  • 若查询语句使用了not in，则内外表都进行全表扫描，不用索引。

• exists
  • 使用exists时，先查询表A，在查询表B；
  • 若表B较大，表A较小，则推荐使用exists。
  • 若查询语句使用了not exists，依然能用到索引。无论哪个表大，not exists都比not in效率高。

9、union与union all的区别？

• union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
• union all：对两个结果集进行并集操作，包括重复行，不进行排序。

union all比union的合并效率要高得多。若合并的两个数据集中不包含重复行的话，使用union all。

10、Blob与text的区别？

• Blob
  • 存储二进制数据；
  • Blob值被视为二进制字符串（字节字符串），它们没有字符集，并且排序和比较基于列值中的字节的数值；
  • text值被视为非二进制字符串（字符字符串），它们有一个字符集，并根据字符集的排序规则对值进行排序和比较。

11、InnoDB与MyISAM的区别？

• MyISAM
  • 事务支持：不支持事务；
  • 表锁差异：只支持表级锁；
  • 索引结构：MyISAM引擎使用B+树作为索引，叶子结点存储了数据记录的地址，即索引文件只保存数据记录的地址，索引文件与数据文件是分离的；
  • 表主键：允许没有任何索引和主键的表的存在，索引保存的都是行的地址；
  • 表的具体行数：保存有表的具体行数，select count(1)的时候会直接取值，速度快；
  • 外键：不支持外键。

• InnoDB
  • 事务支持：支持事务；
  • 表锁差异：支持事务和行级锁。行锁大幅度提高了多用户并发操作的功能，但是只能在索引上才生效，其他列还是表锁；
  • 索引结构：InnoDB使用了B+树作为索引，叶子结点存储了完整的数据，即InnoDB数据文件也是索引文件；
  • 表主键：如果没有设置主键或非空的唯一索引，则会自动生成一个6个字节的主键（用户不可见）；
  • 表的具体行数：没有保存表的总行数（只能遍历）；效率较低；
  • 外键：支持外键。

12、数据库的三大范式？

• 第一范式：数据表中的每一列（字段）都不可再拆分；
• 第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能只依赖于主键的一部分；
• 第三范式：在满足第二范式的基础上，表中非主键只能依赖于主键，而不能依赖于其他非主键。

13、主键使用自增ID还是UUID？

推荐使用自增ID

• 原因：
  • 因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，即主键索引的B+树的叶子结点上存储了主键索引以及全部数据。
  • 如果主键索引使用自增ID，则只需要不断向后排列即可；如果是UUID，则会造成数据插入、数据移动，进而产生很多内存碎片，造成性能下降。

14、innodb的双写缓冲

形成原因

• Partial page write（部分页写入）：
  • InnoDB的pagesize为16k，且数据写入是以page为单位进行操作的；操作系统的文件是以4k为单位的；
  • 这样就导致了数据库需要写入4次，在操作系统形成4个块。无法保证原子性，如果写入过程中出现了异常，只有一部分写入成功，会形成一个断裂的page，使数据混乱，产生Partial page write的问题，InnoDB对这种情况无法处理。

双写缓冲过程

• 在InnoDB将page写入操作系统的date files之前，先将page写入一个叫做doublewrite buffer的连续区域内；
• 在doublewrite buffer完成后，InnoDB才会把pages从doublewrite buffer写到 date files的适当位置；
• 如果page在写入的过程中出现异常，InnoDB在恢复数据的过程中会从doublewrite buffer中找到完好的page副本用于恢复。

doublewrite buffer

• 位置：doublewrite buffer是InnoDB在tablespace上的128个页（2个区），大小是2MB；
• 作用：当mysql将脏数据（即内存数据页与磁盘数据页不一致时的数据称呼）flush（刷脏页）到date file时，首先使用memcopy将脏数据复制到内存中的doublewrite buffer；然后通过doublewrite buffer再分两次，每次写入1MB到共享磁盘表；然后调用fsync函数，同步到磁盘上。

是否一定需要doublewrite

• 在slaver上可以关闭，因为即使出现了partial page write问题，数据还是可以从中继日志中恢复。

15、mysql慢查询是什么？如何优化？

慢查询

• 慢查询日志是mysql的一种日志记录，用于记录mysql语句执行时间超过指定的long_query_time的SQL语句，其中long_query_time的默认值是10s；
• 慢查询日志默认不开启。查询方式show variables like '%slow_query_log%'；
• 慢查询分析命令 mysqldumpslow；

慢查询优化

• 开启mysql慢查询
  • 在my.ini中添加long_query_time=2
• 分析慢查询日志
  • 使用explain查询分析sql慢查询语句；
• 常见的慢查询优化
  • 索引没起作用；
  • 需要优化数据库结构：分库分表、增加中间表
  • 分解关联查询。

16、日常开发如何优化SQL

• 添加合适的索引
  • 对频繁查询或order by的字段建立索引；
  • 对于频繁多个查询字段的考虑建立组合索引。需要注意组合索引的顺序，应符合最左匹配原则，即常用作限制条件的字段放到最左列；
  • 索引不宜太多，一般在3 - 5个。

• 优化表结构
  • 数字型字段优于字符串类型：若只包含数值信息的字段不要设计为字符串类型，会降低查询和连接的性能，并增加存储开销。
  • 数据类型更小通常更好：使用小的数据类型，会减少磁盘空间、内存和cpu缓存。如：时间类型尽量使用timeStamp，其存储空间是dateTime的一半。
  • 尽量使用NOT NULL：NULL类型比较特殊，SQL难以优化。如果是组合索引，NULL的类型字段会极大影响整个索引的效率；另外，NULL在索引中会占用额外的存放空间。

• 优化查询语句
  • 分析语句：去除不必要的字段/数据；
  • 查看是否命中索引，或索引是否生效；
  • 如果SQL很复杂，尽量优化SQL结构；
  • 如果表数据量太大，考虑分表。

17、产生临时表的原因有哪些？如何解决临时表的问题？

• 产生临时表的标识：使用explain分析sql时，发现[extra]中有Using temporary，就说明使用了临时表保存中间数据。mysql在对查询结果排序时会使用临时表。
• 产生临时表的情况：
  • order by 子句与group by的不同。如：order by price group by name
  • order by中使用了dinstinct关键字。如：order by distinct(price)
  • 直接使用磁盘临时表的场景（数据量过大）：
    • 表包含text或blob列；
    • group by或distinct子句中包含长度大于512字节的列；
    • 使用union或union all时，select子句中包含大于512字节的列。

解决临时表的问题

• 使用临时表一般意味着性能比较低，在实际应用中需要尽量避免使用临时表。
• 解决方案：
  • 创建索引：在order by或者group by的列上创建索引；
  • 字符串的字节数尽量不超过512字节；
  • 拆分长列：长列（text、blob）一般不用于查询，只用于显示，在设计时，可以独立存放到一张表里。

18、大表的查询优化方案

• 优化sql语句，合适字段添加索引；
• 考虑增加缓存——redis；
• 主从复制、读写分离；
• 分库分表。

19、百万级别及以上的数据，如何删除数据呢？

• 需要先删除索引；
• 然后删除其中的无用数据；
• 删除完成后再重新建立索引。

20、描述下分库分表？

原因：

• 分表
  • 单标数据量太大：会影响sql执行的性能；
• 分库
  • 并发太高：一个库一般而言，可稳定支撑的最高并发2000左右，若超过，则影响效率。

分库分表方案

• 水平分库：以字段为依据，按照一定策略，将一个库中的数据拆分到多个库中；
• 水平分表：以字段为依据，按照一定策略，将一个库中的数据拆分到多个表中；
• 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中；
• 垂直分表：以字段为依据，按照字段的活跃性，将表中的字段拆分到不同的表中（主表、子表）。

分库分表的数据分片规则？
比较常见的是：Hash取模分表、数值Range分表、一致性Hash算法分表。

• Hash取模分表
  • 概念：一般采用Hash取模的切分方式。如：按照user_id分四张表（user_id%4取整）。
  • 优点：数据分片相对比较均匀，不容易出现热点和并发访问的瓶颈。
  • 缺点：
    • 后期分片扩容时，迁移旧数据很困难；
    • 面临跨分片查询的问题。需要强依赖于user_id，否则将对四张表同时发出请求。
• Range分表
  • 概念：按照时间区间或ID区间来切分。
  • 优点：
    • 单表的大小可控；
    • 天然便于水平扩展，后期如果想对整个分片集群扩容时，只需要添加节点即可，无需对其它分片进行数据迁移；
    • 使用分片字段范围查询时，连续分片效率很高。
  • 缺点：
    • 热点数据成为性能瓶颈：若按照时间字段分片，历史数据可能很少被查询。
• 一致性Hash算法
  • 暂略

分库分表带来了什么问题

• 分布式事务问题
  • 可以使用分布式事务中间件来解决（todo）
• 跨节点关联查询join问题
  • 切分之前，可以通过join来完成；切分后的join需要连接各个节点/分片的数据。尽量避免使用join。
  • 解决方案：
    • 字段冗余：利用空间换时间。如：订单表存储了userId，也会存储userName。
    • 数据组装：分两次查询，第一次查询找出关联数据id，第二次请求获取关联数据。
• 跨节点分页、排序、函数问题
  • 解决方案：需要先在不同的分片/节点上将数据进行排序并返回，然后将这些数据集进行汇总和再次排序，最终返回给用户。

如果现在有一个未分库分表的系统，未来要分库分表，如何设计才能让系统从未分库分表动态切换到分库分表上

• 停机迁移方案
  • 此为基础方案，首先挂维护，禁止访问；然后将老数据全部导出来，写到分库分表的表里面去；修改数据库配置，上线相关代码；启动链接到新的分库分表中去；
• 双写迁移方案
  • 不用停机；
  • 在原有写入老库的基础上，同时对新库进行增删改；同时从老库中读数据写入新库，通过modifyTime做校验是否需要写入。

---

事务相关

1、什么是数据库事务？

是数据库执行过程中的一个逻辑单位。由一个有限的数据库操作序列构成，这些操作要么全部执行，要么全部不执行。是一个不可分割的工作单位。

2、事务的四大特性？实现原理是什么？

原子性

• 特性：事务作为一个整体被执行。包含在事务中的数据库操作序列要么全部被执行，要么全部不执行；
• 实现原理：是使用undo log实现的。如果事务执行过成中出错或用户执行了rollback，系统通过undo log日志返回事务开始的状态。
  隔离性
• 特性：多个事务并发访问时，事务之间是互相隔离的。一个事务的执行不会影响其他事务运行的效果。
• 实现原理：通过锁和MVCC，使事务相互隔离开；
  一致性
• 特性：指事务开始之前与事务结束以后，数据不会被破坏。如：A账户转账给B账户10块，不管成功与否，A、B账户总金额不变。
• 实现原理：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。
  持久性
• 特性：事务完成之后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。
• 实现原理：使用redo log来实现。只要redo log日志持久化了，数据库可以使用redo log来恢复数据。

3、事务的隔离级别有哪些？

• 读未提交：事务A开始写数据时，事务B不允许同时写数据；但是允许事务C读数据。解决了更新丢失问题，可能引起脏读；
• 读已提交：事务A开始写数据时，其他事务不可读写数据。解决了脏读的问题，但是可能出现不可重复读；
• 可重复读：事务A开始写数据时，多次读取数据a；事务A没结束时，其他事务不能读取或修改数据a。这样保证了事务A多次读取数据a的结果相同。解决了不可重复读的问题，但是会出现幻读。
• 可序化：提供严格的事务隔离。串行执行事务，需要保证新插入的数据不会被正在执行查询操作的事务访问到。解决了幻读，但是事务顺序执行，处理效率极低。

mysql默认的事务登记是可重复读。

4、脏读、不可重复读、幻读

脏读：读到了其他事务未提交的数据。未提交意味着数据可能被回滚，读到的数据最终不一定会存在；
不可重复读：在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据出现不一致的情况；
幻读：事务A在更新全表数据的某一个字段，已经读取完全表数据；此时事务B在表中新增了一行数据a；事务A的修改就不包含数据a。

---

索引相关

1、什么是索引？

索引是一种数据结构，可以快速检索数据库中的数据。

2、索引的特点？

优点

• 提高数据检索效率，降低数据库IO成本；
• 通过索引对数据进行排序，降低数据的排序成本，进而降低CPU的消耗。

缺点

• 建立索引需要占用磁盘空间；
• 会降低表的增删改的效率。因为每次对表进行修改，都要动态维护索引。

3、什么情况下需要索引？什么情况不需要？

需要

• 主键自动创建唯一索引；
• 频繁作为查询条件的字段；
• 查询中需要排序、统计或分组的字段。

不需要

• 表记录太少的字段；
• 经常增删改的字段；
• 唯一性太差的字段，不适合单独作为索引。如：民族、政治面貌等

4、介绍一下索引的分类？

• 普通索引：最基本的索引，无任何限制；
• 唯一索引：索引列（如id）的值必须是唯一的，但允许有空值。如果是组合索引，则列值的组合必须唯一；
• 主键索引：一个表只能有一个主键，是一种特殊的唯一索引，不允许有空值。
• 组合索引：一个索引包含多个列。
• 全文索引：全文搜索的索引。用于搜索很长一篇文章的时候效果最好。只能为数据结构为Char、Varchar、Text列创建。

主键索引与唯一索引的区别：

• 主键必唯一，但是唯一索引不一定是主键；
• 一张表只能有一个主键，但可能有一个或多个唯一索引。

5、索引的数据结构有哪些？

索引的数据结构主要有B+树与Hash表，对应的索引为B+树索引与**Hash索引。**默认索引类型为B+树索引。

Hash索引

• Hash索引是基于Hash表实现的。
• 当对某张表添加索引时，会将对应列数据进行hash计算，得到的结果作为Hash表的key，将指向数据行的指针作为hash表的value。
• 查找时间复杂度为O(1)，一般多用于**精确查找。**如 = in <=> 等。但是开发一般用BTree，因为Hash索引存在缺点。
• 缺点：
  • Hash索引仅能满足 = in <=> 等查询，不能应用于范围查询；
  • 无法用来避免数据的排序操作；
  • 不能利用部分索引键查询；
  • 任何时候都不能避免表扫描；
  • 在遇到大量Hash碰撞的情况下性能并不比B-Tree索引高。

B+树索引

• B树（balance） - 多路自平衡查找树
  
  • B树是一种平衡的多分树，M阶的B树，必须满足以下条件：
    • 所有节点都直接存储数据；
    • 每个节点最多只有M个子节点；如上图所示为4阶B数；
    • 每个非叶子节点（出了根节点）至少具有（m / 2）个子节点；
    • 若根不是叶子结点，则根需要有至少2个子节点；
    • 具有K个子节点的节点，拥有K-1个键；
    • 所有叶子结点都必须出现在同一水平层，即使没有数据，也要补全。

• B+树
  
  • B+树的特点
    • 有K个子节点的节点，拥有K个键；
    • 所有非叶子节点不保存数据，只保存键的key信息；
    • 所有叶子结点保存了全部数据，且叶子结点之间都存在链指针；叶子结点本身按照key从小到大排列。

6、为什么B+树比B树更适合实现数据库索引？

• B+树叶子结点保存了所有数据，且使用链表有序连接，所以要扫描全部数据只需扫描一遍叶子结点。有利于扫库和范围查询；
• B树非叶子结点也存放数据，所以只能通过中序遍历扫描所有节点，效率较B+树更低。

• B+树比B树减少了I/O读写次数
  • 由于索引文件很大，因此存放在磁盘上；
  • B+树的非叶子节点只存关键字，不存放数据。 所以每个节点比B树可存放更多关键字，即一次性读入内存的关键字就越多，磁盘的I/O读取次数相对更少。

• B+树的查询效率更加稳定。任何关键字的查找都必须从根节点走到叶子结点，所有关键字查找路径长度相同，导致每一次数据查询效率相当。

7、聚簇索引与非聚簇索引的关系？

聚簇索引

• 概念：按照每张表的主键构造一棵B+树，同时叶子结点存放的是整张表的行数据。每张表只能有一个聚簇索引。
• 特点：
  • 如果表设置了主键，则主键为聚簇索引；
  • 若表无主键，则会默认第一个NOT NULL，且唯一（unique）的列为聚簇索引；
  • 若不满足以上条件，则会默认创建一个隐藏的row_id作为聚簇索引。
• InnoDB的主键使用的就是聚簇索引。

非聚簇索引

• 概念：除去聚簇索引外，其他的索引被称为非聚簇索引。
• InnoDB的非聚簇索引的叶子结点存储的是主键（聚簇索引）的值。

8、什么是回表查询？

非聚簇索引的查询。

• 先通过费局促索引定位到叶子结点，叶子结点指向了主键（聚簇索引）的值；
• 再通过聚簇索引的值定位到聚簇索引的叶子结点，返回数据。

需要扫描2此B+树，效率会低。

9、什么是索引覆盖？

只需要在一个B+树上就能获得SQL所需的所有列数据，无需回表，速度更快。

• 如：user表主键id，索引age，用SQLselect id,age from user where age = 30; 搜索，则无需回表；若使用select id,age,name from user where age = 30; 来搜索，则需要回表。

10、什么是最左匹配原则？

针对组合索引。

• 概念：当设置组合索引的时候，应将最常用作限制条件的列放在最左边，依次递减。可以使用左侧字段进行查询，不可单独使用右侧字段。
• 如：user表中创建了组合索引（age, name），age是常用查询条件，所以放在左边。可以单独使用age作为条件查询，不能单独使用name。
  
• 原因：索引结构中，索引key的排序，首先按照age进行排序；age相同时，再按照name进行排序。因此如果单独查询name，由于无法确定age的值，因此无法使用索引。

11、索引失效的场景有哪些？

• 组合索引未使用最左前缀，使用右侧字段单独查询；
• or会使索引失效。查询字段相同时生效 age = 20 or age = 30；查询字段不同时失效；
• 列类型为字符串，查询不使用引号时。where name = 张三 ，而应该是 where name = '张三'；
• like未使用最左前缀。
• 在索引列上做计算、使用函数；
• 若mysql估计使用全表扫描比使用索引快，则使用索引。

12、索引设计原则

• 索引列的区分度越高（索引越长，区分度越高），效果越好。比如：性别区分度就很低；
• 尽量使用短索引。对于长字符串，应该指定一个较短的前缀长度。小索引使用磁盘I/O少，查询效率高；
• 索引不是越多越好，会占用磁盘空间；
• 利用组合索引的 最左匹配原则。
• 删除使用较少或失效的索引，恢复磁盘空间。

---

应用题

1、超大分页如何处理

• 用id优化
  • 使用上次分页查询的最大ID，然后使用id上的索引来查询。select * from user where id > 10000 limit 100;
  • 优点：效率非常快，因为使用了id上的索引；
  • 缺点：id必须是连续的，并且查询条件不能有其他where语句，因为这样会造成过滤。
• 用覆盖索引优化
  • 覆盖索引：mysql的查询能完全命中索引的时候。效率非常高，因为只需要在索引上进行查询；
  • 所以日常查询可以先查出id，然后根据id拿数据。select * from user where id in (select id from table where age > 20 limit 100000, 10);
• 在业务允许的情况下限制页数
  • 与业务讨论最多可提供的页数。

2、表a数据量6亿，表b数据量3亿，两表通过外键tid关联。如果最快查询满足条件的第50000条到第50200条这200条记录？

方法1

• 如果a表的tid是自增且连续的，b表的id为索引
• select * from a,b where a.tid = b.id and a.tid > 50000 limit 200;

方法2

• 如果表a的tid不是连续的，需要使用覆盖索引。需要条件是a表的tid是索引、b表的id是索引
• select * from b, (select tid from a limit 50000, 200) a where b.id = a.tid;

---

热门SQL编写

-- 
select * from