mysql 面试要点整理

最新推荐文章于 2024-11-15 16:27:37 发布

benben0729

最新推荐文章于 2024-11-15 16:27:37 发布

阅读量360

点赞数

文章标签： mysql 面试数据库

本文链接：https://blog.csdn.net/benben0729/article/details/110857988

版权

mysql 专栏收录该内容

33 篇文章 0 订阅

订阅专栏

本文详细解读了MySQL架构的各个层次，包括连接层的连接池与安全机制，服务层的核心服务功能，存储引擎的选择与作用，数据存储的细节，SQL执行流程，缓存策略，以及索引、锁和事务的原理。还涵盖了表锁与行锁的区别，死锁处理方法，以及SQL优化策略和性能瓶颈分析。

摘要由CSDN通过智能技术生成

mysql架构

1）连接层

最上层的连接池是一些连接服务，包含本地sock通信和大多数基于C/S工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全连接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2）服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口、缓存的查询、SQL的分析和优化、内置函数等。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在频繁读操作的环境中能够很好的提升系统的性能。

3）引擎层

存储引擎真正的负责MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎具有的特性不同，我们可以根据实际需进行选取。下文将对相关存储引擎进行具体介绍。

4）存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

SQL的执行过程：数据库通常不会被单独使用，而是由其它编程语言通过SQL支持接口调用MySQL，由MySQL处理并返回执行结果。首先，其它编程语言通过SQL支持接口调用MySQL，MySQL收到请求后，会将该请求暂时放在连接池，并由管理服务与工具进行管理。当该请求从等待队列进入到处理队列时，管理器会将该请求传给SQL接口，SQL接口接收到请求后，它会将请求进行hash处理并与缓存中的数据进行对比，如果匹配则通过缓存直接返回处理结果；否则，去文件系统查询：由SQL接口传给后面的解析器，解析器会判断SQL语句是否正确，若正确则将其转化为数据结构。解析器处理完毕后，便将处理后的请求传给优化器控制器，它会产生多种执行计划，最终数据库会选择最优的方案去执行。确定最优执行计划后，SQL语句交由存储引擎处理，存储引擎将会到文件系统中取得相应的数据，并原路返回。

缓存

mysql 缓存机制简单来说就是缓存sql文本以及查询结果，如果运行相同的sql,服务器直接从缓存中取到结果，不需要再去解析和执行sql.

通过设置query_cache_type来控制缓存的开关 0off 1on 2 demand

select SQL_NO_CACHE * from my_table where ...

select SQL_CACHE * from my_table where ...

引擎

Innodb

支持事务，最小锁粒度是行锁，更适合写，但是目前已经优化的足够好，读取能力不弱于myisam

支持外键

聚簇索引，聚簇索引的文件存放在主键索引的叶子节点，索引innodb必须要有主键

Myisam

不支持事务，最小锁粒度是表锁，更适合查询

不支持外键

非聚簇索引

MyIsam的优势可能就剩下占用空间更小吧

锁

表级锁：开销小，加锁快；不会出现死锁；锁粒度大，发生锁冲突的概率最高，并发度最低。

页面锁：开销和加锁时间介于表锁与行锁之间；会出现死锁；锁粒度处于表锁与行锁之间；

行级锁：开销大，加锁慢；会出现死锁；锁粒度最小，发生锁冲突的概率最低，并发度也最高；

乐观锁：用一个数据版本（version）记录来实现;每次在更新之前先查到版本号，再根据版本号去更新，保证了这条数据在同一时刻只能被一个事务修改。乐观锁是我们用代码实现的一种加锁方式；

悲观锁：悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时需要先通过获取锁才能对相同的数据进行修改。悲观锁时数据库自己实现的，共享锁和排他锁都属于悲观锁

共享锁：读锁S锁；其他事务只能加S锁；其他事务只能访问该资源不能进行修改必须等到该资源的所有锁被释放；

排他锁：写锁X锁；其他事务不能加任何锁；且不能访问该资源；必须等X锁释放

如果加排他锁可以使用select ...for update 语句，加共享锁可以使用select ... lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。

行锁

行锁：多个事务操作同一行数据时，后来的事务处于阻塞状态；innodb只有在通过索引条件检索数据时使用行级锁，否则使用表锁

innodb的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁

劣势：开销大，加锁慢，会出现死锁

优势：锁粒度小，发生锁冲突的概率低，处理并发能力强

加锁：update、delete、insert语句，innodb会自动加排他锁，普通select 不会加任何锁

间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）

危害：若执行的条件范围过大，则innodb会将整个范围内的所有索引键值全部锁定，很容易对性能造成影响

表锁

只有通过索引条件检索数据，innodb才使用行级锁，否则使用表锁；

行级锁是基于索引的，如果一条sql语句用不到索引是不会使用行级锁的，会使用表级锁；

死锁

两个或两个以上的进程在执行过程中，因竞争资源而造成的一种互相等待的现象，若无外力作用，他们都将无法进行下去

死锁产生的条件

互斥：一次只有一个进程可以使用资源。其他进程不能访问已分配给其他进程的资源

占有且等待：当一个进程在等待分配得到其它资源时，其继续占有已分配到的资源

非抢占：不能强行抢占进程中已占有的资源

循环等待：存在一个封闭的进程链，使得每个资源至少占有此链中下一个进程所需要的资源

处理死锁的方法

死锁预防：确保死锁的一个必要条件不能被满足，保证不会发生死锁

死锁检测：允许死锁的发生，但是可以通过系统设置的检测结构及时的检测出死锁的发生，采取一些措，将死锁解除掉

死锁避免：在资源分配过程中，使用某种方法避免进入不安全的状态，从而避免发生死锁

死锁解除：与死锁检测配套的一种措施，当检测到发生死锁，需将其从死锁状态解脱出来

表锁场景

全表更新

多表查询

隔离级别

隔离级别	脏读	不可重复读	幻读
读未提价(read uncommitted)	Y	Y	Y
读已提交(read committed)	N	Y	Y
可重复读(repeateable )default	N	N	Y
可串行化(Serialiable)	N	N	N

同时开启两个事务c1，c2,时间用t1 t2 t3

脏读：事务c1对某数据进行了修改，但未提交，此时事务c2可以读取到c1的修改

不可重复读：c1在t1读到某数据，c2在t2对数据做了修改或者删除并做了提交，c1在t3重新读取时，发现数据和在t1读取到的不一致 update

幻读：c1在t1读到某数据，c2在t2添加了某条数据并做了提交，c1在t3想要插入一条和c2一样的数据，但是发现插入不了（插入条件中要包含唯一索引或主键索引），但是在c1事务中的查询中并没有要插入的这条记录，好像出现了幻觉一样，就是幻读 insert delete

事务

特性：原子性、一致性、隔离性、持久性

undo log 回滚日志

redo log 前滚日志

日志持久化：

用户->内存用户空间（logbuffer）->osbuffer 内核空间-（fsync系统调用）>磁盘

事务的实现：

undolog

保证事务的原子性和一致性

功能：

实现事务的回滚
实现mvcc

mvcc

MVCC(Multi Version Concurrency Control的简称)，代表多版本并发控制。与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。 MVCC最大的优势：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能

实现：

MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

redolog

保证事务的一致性隔离性持久性

组成：

内存中的重做日志缓冲 log buffer
重做日志文件 log file

主从同步的实现

主master机器上的操作

当master上的数据发生变化时，该事件变化会按照顺序写入bin-log。当slave链接到master的时候，master机器会为slave开启binlog dump线程。当master的binlog发生变化时，bin-log dump线程会通知slave，并将响应的binlog发送给slave

从slave机器上的操作

当主从同步开启的时候，slave上会创建两个线程：I/O线程，该线程连接到master机器，master机器上的binlog dump线程会将binlog的内容发送给I/O线程，I/O线程接收到binlog内容后，再将内容写到本地的relay log。Sql线程，该线程读取到I/O线程写入的relay log。并且根据relay log的内容对slave数据库做响应的操作

主动同步存在延迟，如何保证强一致性

首先看业务的容忍性，如果能容忍一定的延迟，我们可以通过设置同步频率来达到要求；

如果业务要求必须强一致性，可以借助redis缓冲中间件；假设我们现在的同步频率为1s,我们在主库上做插入更新操作时，首先按照协商好的命名规则将其存储到redis并设置过期时间1s。从库在做查询时，首先按照协商好的规则查询redis，如果存在读取主库，如果不存在以及同步到从库上了，直接从从库上查询即可。

SQL优化

sql优化的点：

表字段类型选择
引擎的选择
建立索引
开启缓存，或者redis缓存
分区
分库分表：垂直分表、水平分表
集群：主从分离、读写分离、负载均衡、高可用
开启慢查询，优化sql语句

sql性能下降的原因：

查询语句写的不好
未建立索引，索引创建的不合理或者索引失效
关联太多join
服务器磁盘空间不足
服务器调优配置参数设置的不合理

explain 执行计划

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	extra

select的查询序号，表示查询中执行sql语句的顺序，一般有三种情况：
第一种：id全部相同，sql语句的执行顺序由上到下
第二种：id全不相同，sql的执行顺序是根据id大的优先执行
第三种：id既存在相同，也存在不同。先根据大的优先执行，再根据相同的从上到下执行

select_type

select查询的类型，主要用于区分普通查询，联合查询和嵌套查询
simple:简单的select查询，查询中不包含子查询和union
primary:查询中如果包含了任何复杂的子查询，最外层查询被标记为primary
subquery:在select或where列表中包含了子查询
derived:在from列表中包含的子查询会被标记为derived
union:若第二个select出现在union之后，则被标记为union,若union包含在from子句的查询中，外层select将被标记为：deriverd
union reselt:从union表获取结果的select

type

连接类型;常见的有：all,index,range,ref,eq_ref,const,system,null八个级别
性能从最优到最差的排序：system>const>eq_ref>ref>range>index>all
对java程序员来说，若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。 
all(full table scan):全表扫描
index(full index scan):全索引文件扫描，从索引树中查询 速度提升很多
range:只检索给定范围的行，使用索引来匹配行
ref；非唯一性索引扫描，本质山也是一种索引访问，返回所有匹配某个单独值的行
eq_ref:唯一性索引扫描，对于每个索引键，表中有一条记录与之匹配
const:表示通过索引一次就可以找到
system:表示只有一条记录（等于系统表）

possible_keys

显示查询中可能使用到的索引，不一定被查询实际使用

key

显示查询中实际用到的索引

key_len

显示索引中使用的字节数

ref

显示索引的那一列或常量被用于查找索引列上的值

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，值越大越不好

extra

Using filesort： 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。
Using temporary： 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。 出现这个更要立刻优化sql。
Using index： 表示相应的select 操作中使用了覆盖索引（Covering index），避免访问了表的数据行，效果不错！如果同时出现Using where，表明索引被用来执行索引键值的查找。如果没有同时出现Using where，表示索引用来读取数据而非执行查找动作。
覆盖索引（Covering Index） ：也叫索引覆盖，就是select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select 列表中的字段，而不必根据索引再次读取数据文件。
Using index condition： 在5.6版本后加入的新特性，优化器会在索引存在的情况下，通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。
Using where： 表明使用了where 过滤
Using join buffer： 表明使用了连接缓存
impossible where： where 语句的值总是false，不可用，不能用来获取任何元素
distinct： 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

fitered

一个百分比的值，和rows列一起使用，可以估计出查询执行计划中的前一个表的结果集，从而确定join操作的循环次数。小表驱动大表，减轻连接的次数

通过explain的参数介绍，我们可以得知: 1 表的读取顺序(id) 2 数据读取操作的操作类型(type) 3 哪些索引被实际使用(key) 4 表之间的引用(ref) 5 每张表有多少行被优化器查询(rows)

mysql缓存

索引

索引是存储引擎没了快速找到记录的一种数据结构。

优势：

提高检索效率，降低I/O成本

降低数据排序的成本，降低CPU的消耗

劣势：

索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存
索然大大提高了查询速度，同时却会降低更新表的速度，如对表的insert、update、和delete。因为更新表时，mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

索引的分类

数据结构角度

B+树索引
Hash索引
Full-Text索引
R-Tree索引

从物理存储角度

聚集索引（clustered index）
非聚集索引(non-clustered index)，也叫辅助索引（secondary index）

聚集索引和非聚集索引都是B+树结构

从逻辑角度

主键索引：主键索引是一种特殊的唯一索引，不允许有空值

普通索引或单列索引：每个索引只包含单个列，一个表可以有多个单列索引

多列索引（复合索引、联合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合

唯一索引或者非唯一索引：

空间索引：间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。 MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

B-Tree和B+Tree的区别

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。 InnoDB 存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB 存储引擎中默认每个页的大小为16KB，可通过参数 innodb_page_size 将页的大小设置为 4K、8K、16K，在 MySQL 中可通过如下命令查看页的大小：show variables like 'innodb_page_size'; 而系统一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB。InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。 B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述 B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data 为一行记录中除主键外的数据。对于不同的记录，key值互不相同。

一棵m阶的B-Tree有如下特性：

每个节点最多有m个孩子
除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
若根节点不是叶子节点，则至少有2个孩子
所有叶子节点都在同一层，且不包含其它关键字信息
每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
ki(i=1,…n)为关键字，且关键字升序排序
Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

有关b树的一些特性，注意与后面的b+树区分：

关键字集合分布在整颗树中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
其搜索性能等价于在关键字全集内做一次二分查找；

B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个 3 阶的 B-Tree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。模拟查找关键字29的过程：

根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】比较关键字29在区间（17,35），找到磁盘块1的指针P2。根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】比较关键字29在区间（26,30），找到磁盘块3的指针P2。根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

B+Tree

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB 存储引擎就是用 B+Tree 实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

非叶子节点只存储键值信息；
所有叶子节点之间都有一个链指针；
数据记录都存放在叶子节点中

将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算： InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为103）。也就是说一个深度为3的B+Tree索引可以维护103 * 10^3 * 10^3 = 10亿条记录。实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

B+Tree性质

通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。

MyISAM主键索引与辅助索引的结构

MyISAM引擎的索引文件和数据文件是分离的。MyISAM引擎索引结构的叶子节点的数据域，存放的并不是实际的数据记录，而是数据记录的地址。索引文件与数据文件分离，这样的索引称为"非聚簇索引"。MyISAM的主索引与辅助索引区别并不大，只是主键索引不能有重复的关键字。

在MyISAM中，索引（含叶子节点）存放在单独的.myi文件中，叶子节点存放的是数据的物理地址偏移量（通过偏移量访问就是随机访问，速度很快）。

主索引是指主键索引，键值不可能重复；辅助索引则是普通索引，键值可能重复。

通过索引查找数据的流程：先从索引文件中查找到索引节点，从中拿到数据的文件指针，再到数据文件中通过文件指针定位了具体的数据。辅助索引类似。

InnoDB主键索引与辅助索引的结构

InnoDB引擎索引结构的叶子节点的数据域，存放的就是实际的数据记录（对于主索引，此处会存放表中所有的数据记录；对于辅助索引此处会引用主键，检索的时候通过主键到主键索引中找到对应数据行），或者说，InnoDB的数据文件本身就是主键索引文件，这样的索引被称为"“聚簇索引”，一个表只能有一个聚簇索引。

主键索引：

我们知道InnoDB索引是聚集索引，它的索引和数据是存入同一个.idb文件中的，因此它的索引结构是在同一个树节点中同时存放索引和数据，如下图中最底层的叶子节点有三行数据，对应于数据表中的id、stu_id、name数据项。

在Innodb中，索引分叶子节点和非叶子节点，非叶子节点就像新华字典的目录，单独存放在索引段中，叶子节点则是顺序排列的，在数据段中。Innodb的数据文件可以按照表来切分（只需要开启innodb_file_per_table)，切分后存放在xxx.ibd中，默认不切分，存放在xxx.ibdata中。

辅助（非主键）索引：

这次我们以示例中学生表中的name列建立辅助索引，它的索引结构跟主键索引的结构有很大差别，在最底层的叶子结点有两行数据，第一行的字符串是辅助索引，按照ASCII码进行排序，第二行的整数是主键的值。

这就意味着，对name列进行条件搜索，需要两个步骤：

① 在辅助索引上检索name，到达其叶子节点获取对应的主键；

② 使用主键在主索引上再进行对应的检索操作

这也就是所谓的“回表查询”

InnoDB 索引结构需要注意的点

数据文件本身就是索引文件
表数据文件本身就是按 B+Tree 组织的一个索引结构文件
聚集索引中叶节点包含了完整的数据记录
InnoDB 表必须要有主键，并且推荐使用整型自增主键

如我们上面介绍 InnoDB 存储结构，索引与数据是共同存储的，不管是主键索引还是辅助索引，在查找时都是通过先查找到索引节点才能拿到相对应的数据，如果我们在设计表结构时没有显式指定索引列的话，MySQL 会从表中选择数据不重复的列建立索引，如果没有符合的列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，并且这个字段长度为6个字节，类型为整型。

那为什么推荐使用整型自增主键而不是选择UUID？

UUID是字符串，比整型消耗更多的存储空间；
在B+树中进行查找时需要跟经过的节点值比较大小，整型数据的比较运算比字符串更快速；
自增的整型索引在磁盘中会连续存储，在读取一页数据时也是连续；UUID是随机产生的，读取的上下两行数据存储是分散的，不适合执行where id > 5 && id < 20的条件查询语句。
在插入或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏左侧子树的结构；UUID主键很容易出现这样的情况，B+树为了维持自身的特性，有可能会进行结构的重构，消耗更多的时间。

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

保证数据一致性和节省存储空间，可以这么理解：商城系统订单表会存储一个用户ID作为关联外键，而不推荐存储完整的用户信息，因为当我们用户表中的信息（真实名称、手机号、收货地址···）修改后，不需要再次维护订单表的用户数据，同时也节省了存储空间。

Hash索引

主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。

检索算法：在检索查询时，就再次对待查关键字再次执行相同的Hash算法，得到Hash值，到对应Hash表对应位置取出数据即可，如果发生Hash碰撞，则需要在取值时进行筛选。目前使用Hash索引的数据库并不多，主要有Memory等。

MySQL目前有Memory引擎和NDB引擎支持Hash索引。

full-text全文索引

全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MYSQL5.6版本提供对全文索引的支持。
它用于替代效率较低的LIKE模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。
同样使用B-Tree存放索引数据，但使用的是特定的算法，将字段数据分割后再进行索引（一般每4个字节一次分割），索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。

R-Tree空间索引

空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型

为什么Mysql索引要用B+树不是B树？

用B+树不用B树考虑的是IO对性能的影响，B树的每个节点都存储数据，而B+树只有叶子节点才存储数据，所以查找相同数据量的情况下，B树的高度更高，IO更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。其中在MySQL底层对B+树进行进一步优化：在叶子节点中是双向链表，且在链表的头结点和尾节点也是循环指向的。

面试官：为何不采用Hash方式？

因为Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+ Tree是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描。

哈希索引不支持多列联合索引的最左匹配规则，如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

哪些情况需要创建索引？

主键自动建立唯一索引
频繁作为查询条件的字段
查询中与其他表关联的字段，外键关系建立索引
单键/组合索引的选择问题，高并发下倾向创建组合索引
查询排序的字段，排序字段通过索引大幅度提高排序速度
查询中统计或分组字段

哪些情况不需要创建索引？

表记录太少
经常增删改的表
数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据建立索引（如果某个数据类含太多的重复数据，建立索引没有太大意义）
频繁更新的字段不适合创建索引（会加重IO负担）
where字段里用不到的字段不创建索引

索引失效

【优化口诀】全值匹配我最爱，最左前缀要遵守；带头大哥不能死，中间兄弟不能断；索引列上少计算，范围之后全失效； LIKE百分写最右，覆盖索引不写*；不等空值还有OR，索引影响要注意； VAR引号不可丢， SQL优化有诀窍。

估算指定表结构最优性能的最大行数

估算最大高度为3的b+索引树能够检索的最大行数

对于b+树，我们知道数据是放在叶子节点，所以第一层和第二层只有索引，一个longint类型为8byte,指针类型6byte,所以对于第一层和第二层，每页存储的索引数量为161024/14=1170 第三层存放的数据，每行数据假设为1kb，则每页可存储16条数据，总数：11701170*16=21902400 大概是2100万的数据量

分表

分表的分页查询（二次查询法）

假设分成了三个表，每页假设5条数据，现在要查询第200页的数据

第一步：
select * from t1 order by sort offset 333 limit 5
select * from t4 order by sort offset 333 limit 5
select * from t3 order by sort offset 333 limit 5

第一个表的数据如下：
sort    uid
10000   u1
10003   u2
10005   u3
10005   u4
10006   u5

第二个表的数据：
sort          uid
9998          u11
10004         u22
10005         u33
10006         u44
10006         u55

第三个表的数据：
sort          uid
10002         u111
10003         u222
10004         u333
10005         u444
10006         u555

第二步：比较三个记过中的最小值min=9998，然后以9998作为最小值，再次查表

第一个表查询sort>9998and sort<10009：
sort    uid
9999    ua
10000   u1
10003   u2
10005   u3
10005   u4
10009   u5

第二个查询sort>9998and sort<10006：
sort          uid
9998          u11
10004         u22
10005         u33
10006         u44
10006         u55

第三个查询sort>9998and sort<10008：
sort          uid
9998          uaaa
9999          ubbb
10000         uccc
10002         u111
10003         u222
10004         u333
10005         u444
10008         u555

第三步：
针对上一步的结果可以看成 第一个表的查询是offset 332 ,第二个表的查询是limit 333 ，第三个表是limit 330

332+333+330=995

第四步：
将上一步的结果合并得到全局视野，只需对合并的结果offset (1000-995) limit 5即可得到结果。

慢查询

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

MySQL 慢查询的相关参数解释

 slow_query_log    ：是否开启慢查询日志，1表示开启，0表示关闭。
 
 log-slow-queries  ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
 
 slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
 
 long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
 
 log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
 
 log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

Mysql 极限

20万张表 50亿条记录

每个表最多64个索引，每个索引1-16列 innodb最大索引宽度767字节或3072字节

红黑树

红黑树相比于BST和AVL树有什么优点？

红黑树是牺牲了严格的高度平衡的优越条件为代价，它只要求部分地达到平衡要求，降低了对旋转的要求，从而提高了性能。红黑树能够以O(log2 n)的时间复杂度进行搜索、插入、删除操作。此外，由于它的设计，任何不平衡都会在三次旋转之内解决。当然，还有一些更好的，但实现起来更复杂的数据结构能够做到一步旋转之内达到平衡，但红黑树能够给我们一个比较“便宜”的解决方案。

相比于BST，因为红黑树可以能确保树的最长路径不大于两倍的最短路径的长度，所以可以看出它的查找效果是有最低保证的。在最坏的情况下也可以保证O(logN)的，这是要好于二叉查找树的。因为二叉查找树最坏情况可以让查找达到O(N)。

红黑树的算法时间复杂度和AVL相同，但统计性能比AVL树更高，所以在插入和删除中所做的后期维护操作肯定会比红黑树要耗时好多，但是他们的查找效率都是O(logN)，所以红黑树应用还是高于AVL树的. 实际上插入 AVL 树和红黑树的速度取决于你所插入的数据.如果你的数据分布较好,则比较宜于采用 AVL树(例如随机产生系列数),但是如果你想处理比较杂乱的情况,则红黑树是比较快的

红黑树的各种操作的时间复杂度是多少？

能保证在最坏情况下，基本的动态几何操作的时间均为O（lgn）

红黑树相对于哈希表，在选择使用的时候有什么依据？

权衡三个因素: 查找速度, 数据量, 内存使用，可扩展性。　　总体来说，hash查找速度会比map快，而且查找速度基本和数据量大小无关，属于常数级别;而map的查找速度是log(n)级别。并不一定常数就比log(n) 小，hash还有hash函数的耗时，明白了吧，如果你考虑效率，特别是在元素达到一定数量级时，考虑考虑hash。但若你对内存使用特别严格，希望程序尽可能少消耗内存，那么一定要小心，hash可能会让你陷入尴尬，特别是当你的hash对象特别多时，你就更无法控制了，而且 hash的构造速度较慢。

红黑树并不适应所有应用树的领域。如果数据基本上是静态的，那么让他们待在他们能够插入，并且不影响平衡的地方会具有更好的性能。如果数据完全是静态的，例如，做一个哈希表，性能可能会更好一些。

在实际的系统中，例如，需要使用动态规则的防火墙系统，使用红黑树而不是散列表被实践证明具有更好的伸缩性。Linux内核在管理vm_area_struct时就是采用了红黑树来维护内存块的。

红黑树通过扩展节点域可以在不改变时间复杂度的情况下得到结点的秩。

todo 旋转？