MySQL知识体系总结
2023-03-31 19:30:34 3 举报AI智能生成
为你推荐
查看更多
MySQL知识体系总结
作者其他创作
大纲/内容
锁是计算机协调多个进程或线程并发访问某一资源的机制。
数据库锁机制简单说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。
读锁（共享锁）
写锁（排他锁）
对数据操作的类型分类
表级锁
行级锁
页面锁
适用：从锁的角度来说，表锁适合已查询为主，只有少量按索引条件更新数据的应用。行锁适合有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用。
对数据操作的粒度分类
表共享读锁
表独占写锁
表锁两种模式
MyISAM表的读操作与写操作之间，以及写操作之间是串行的。
默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列的获取锁请求。
MyISAM表锁
共享锁（S）
排他锁（X）
InnoDB实现了2种行锁类型
意向共享锁（IS）
意向排它锁（IX）
为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（都是表锁）
索引失效hi导致行锁变表锁。比如：varchar 查询不写单引号的情况。
InnoDB行锁
乐观锁
悲观锁
乐观锁与悲观锁
SELECT * FROM table WHERE id = 1 FOR UPDATE;
UPDATE SET age = 50 WHERE id = 1; -- id 列为主键或唯一索引列
记录锁（Record Locks）
间隙锁基于非唯一索引，使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。
SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;
Gap锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
间隙锁（Gap Locks）
可以理解为一种特殊的间隙锁，通过临键锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据的临键锁时，会锁住一段左开右闭区间的数据。InnoDB中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。
临键锁（Next-key Locks）
InnoDB的三种行锁
加锁机制
锁定分类
死锁是指两个或多个事务在同一资源上互相占用，并请求锁定占用的资源，从而导致恶性循环。
当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能回发生死锁。
锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁，有些不会。
死锁有两个原因：真正数据冲突；存储引擎的实现方式；
死锁产生
数据库系统实现了各种死锁检测和死锁超时的机制，InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。
检测死锁
死锁发生后，只有部分或完全回滚其中一个事务，才能打破死锁。InnoDB目前处理死锁的方法是：将持有最少行级排它锁的事务进行回滚。
死锁恢复
发生死锁后，InnoDB一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁或涉及表锁的情况下，InnoDB并不能完全自动检测到死锁，这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。
外部锁的死锁检测
死锁会影响性能而不是产生严重错误，因为InnoDB会自动检测并处理死锁。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。有时当发生死锁时，禁用死锁检测（innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖 innodb_lock_wait_timeout 设置进行事务回滚。
死锁影响性能
在自动加锁的情况下，MyISAM总是一次获得SQL语句所需要的全部锁，所以MyISAM表不会出现死锁。
MyISAM避免死锁
使用 select ... for update 语句获取必要的锁；
直接申请足够级别的锁；
约定多个表的访问顺序；
改变事务隔离级别
InnoDB避免死锁
死锁
数据库的乐观锁和悲观锁？
MySQL 中有哪几种锁，列举一下？
MySQL中InnoDB引擎的行锁是怎么实现的？
MySQL 间隙锁有没有了解，死锁有没有了解，写一段会造成死锁的 sql 语句，死锁发生了如何解决，MySQL 有没有提供什么机制去解决死锁？
for update 仅适用于InnoDB，且必须在事务块（begin/commit）中才能生效。
在进行事务操作时，通过 for update 语句，MySQL会对查询结果集中每行数据都添加排他锁（包含 行锁、表锁），其他线程对该记录的更新与删除操作都会阻塞。
只有通过索引条件检索数据，InnoDB才使用行级锁。否则InnoDB将使用表锁。
select for update有什么含义，会锁表还是锁行还是其他？
如果出现死锁，可以用 show engine innodb status; 命令来确定最后一个死锁产生的原因。返回结果包括：死锁相关事务详细信息，如：引发死锁的SQL语句，事务已经获得的锁，正在等待什么锁，已及被回滚的事务等。
如何在MySQL分析死锁产生的原因？
面试题
锁机制
业务需求（适合度）
二进制多媒体数据
流水队列数据
超大文本数据
不适合放进MySQL的数据
系统各种配置及规则
活跃用户的基本信息
活跃用户的个性化定制信息
准实时的统计信息
其他一些访问频繁但变更较少的数据
需要放进缓存的数据
存储定位
尽量减少对数据库访问的请求
尽量减少无用数据的查询请求
Schema设计对系统的性能影响
硬件环境对系统性能的影响
影响MySQL性能的因素
CPU：CPU在饱和的时候，一般发生在数据装入内存或从磁盘读取数据。
IO：磁盘IO瓶颈发生在装入数据远大于内存容量的时候。
服务器硬件性能：top、free、iostat 和 vmstat 查看系统性能状态。
MySQL常见瓶颈
查询语句写的太烂；
索引失效（单值、复合）；
关联查询太多join（设计缺陷或不得已的需求）；
服务器调优及各个参数设置（缓冲、线程数等）；
性能下降、SQL慢、执行时间长、等待时间长，原因分析
MySQL提供的一种日志记录，用于记录MySQL中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值的收起来，会被记录到慢查询日志中。
long_query_time 的默认值为10，运行10秒以上的语句被记录。
默认情况下，MySQL数据库没有开启慢查询日志，需要手动设置参数开启。
查看开启状态
临时配置
永久配置
开启慢查询日志
得到返回记录集最多的10个SQL
得到访问次数最多的10个SQL
得到按照实际排序的前10条里面含有左连接的查询语句
日志分析工具 mysqldumpslow
慢查询日志
使用Explain 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或表结构的性能问题。
是什么？
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
能干什么？
Explain + SQL语句， 得到以下信息
怎么用？
id相同，执行顺序从上往下；
id全不同，如果是子查询，id的序号会递增，id值越大优先级越高；
id部分相同，执行顺序是按照数字大的先执行，然后数字相同的安装从上往下的顺序执行；
id
SIMPLE：简单的select查询，不含子查询或union；
PRIMARY：查询中若包含复杂的子部分，最外层被标记为PRIMARY；
SUBQUERY：在select或where列表中包含了子查询；
DERIVED：在from列表中包含的资产性被标记为derived，mysql会递归执行这些子查询，把结果放在临时表里；
UNION：若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为derived；
UNION RESULT：从union表获取结果的select；
select_type
table
system：表只有一行记录（等于系统表），是const类型的特例，平时不会出现。
const：表示通过索引一次就找到了，const用于比较primary key或unique索引，因为只要匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。
ref：非唯一索引扫描，范围匹配某个单独值得所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行。
range：只检索给定返回的行，使用一个索引来选择行。key列显示使用了哪个索引。一般是在where语句中出现 between、<、>、in等的查询。
index：full index scan，index与ALL的区别为index类型值遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。（index 和 all 都是全表扫描，但index是从索引中读取，all是从磁盘中读取。）
ALL：Full table scan，将遍历全表找到匹配的行。
type
possible_keys
key
表示索引中使用的字节数，可通过该列计算计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。
key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
key_len
ref
rows
using filesort: 说明mysql会对数据使用一个外部的索引排序，不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。常见于order by和group by语句中
Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现using where，表明索引被用来执行索引键值的查找；否则索引被用来读取数据而非执行查找操作
using where：使用了where过滤
using join buffer：使用了连接缓存
impossible where：where子句的值总是false，不能用来获取任何元祖
select tables optimized away：在没有group by子句的情况下，基于索引优化操作或对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
distinct：优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作
Extra
Explain返回的各字段解析
示例图
第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name......】
第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】
示例
Explain（执行计划）
MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况。
默认情况下，参数处于关闭状态，并保存最近15次的运行结果。
show profile分析查询
常见性能分析手段
性能分析
全值(等值)匹配 是最优选择；
不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转向全表扫描；
存储引擎不能使用索引中范围条件右边的列；
尽量使用覆盖索引，减少select；
like \"xxx%\" 是可以用到索引的，like以通配符开头('%abc'或'%abc%')索引失效会变成全表扫描的操作；
字符串不加单引号索引失效；
少用or，用它来连接时会索引失效；
<、<=、>、>=、BETWEEN、IN 可用到索引，<>、not in、!= 则不行，会导致全表扫描；
索引优化
in 和 exists 的选择
永远小表驱动大表
MySQL支持两种方式的排序：Index和FileSort；index 效果高（扫描索引本身完成排序），FileSort效率较低。
order by 语句使用索引最左前列；
使用where子句与order by子句条件组合满足索引最左前列；
order by 子句尽量使用 Index方式排序，避免使用FileSort方式排序；
双路排序：MySQL4.1之前是使用该方式，两次扫描磁盘，最终得到数据。
单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，效率高于双路排序。
filesort方式排序有两种算法
增大 sort_buffer_size 参数的设置；
增大 max_length_for_sort_data 参数的设置；
优化策略
order by 关键字优化
group by 实际是先排序后进行分组，遵照索引建的最佳左前缀；
当无法使用索引列，增大 max_length_for_sort_data和sort_buffer_size参数的设置；
where 高于having，能在where限定的条件就不要去having限定了；
group by 关键字优化
查询优化
更小的通常更好
简单就好
尽量避免NULL
数据类型优化
性能优化
日常工作中你是怎么优化SQL的？
SQL优化的一般步骤是什么，怎么看执行计划（explain），如何理解其中各个字段的含义？
如何写sql能够有效的使用到复合索引？
一条sql执行过长的时间，你如何优化，从哪些方面入手？
什么是最左前缀原则？什么是最左匹配原则？
查询中哪些情况不会使用索引？
MySQL调优
一般情况下，创建的表对应一组存储文件，使用MyISAM存储引擎时是一个 .MYI 和 .MYD 文件，使用InnoDB存储引擎时是一个 .ibd 和 .frm（表结构）文件。
当数据量较大时（千万条级别以上），MySQL性能会开始下降，这时就需要将数据分散到多组存储文件，保证单个文件的执行效率。
逻辑数据分割；
提高单一文件的写和读应用速度；
提高分区范围读查询的速度；
分割数据能够有多个不同的物理文件路径；
高效的保存历史数据；
能做什么？
SHOW VARIABLES LIKE '%partition%'; -- 5.6及之前版本
show plugins; -- 5.6版本
查看当前数据库是否支持分区
Range 分区
List 分区
Hash 分区
Key 分区
分区类型
怎么操作？
MySQL分区
垂直分表，通常是按照业务功能的使用频次，把主要的、热门的字段放在一起作为主表。把不常用的，按照各自的业务属性进行聚集，拆分到不同的次要表中。主表和次要表的关系一般都是一对一的。
垂直拆分
单表的容量不超过500W，否则建议水平拆分。是把一个表复制成同样表结构的不同表，然后把数据按照一定的规则划分，分别存储到这些表中，从而保证单表的容量不会太大，提升性能；当然这些结构一样的表，可以放在一个或多个数据库中。
使用MD5哈希
按时间划分
按热度划分
按ID值划分
水平分割的几种方法
水平拆分（数据分片）
MySQL分表
数据库集群环境后，都是多台slave，基本满足了读取操作；但是写入或大数据、频繁的写入操作，对master性能影响就比较大，这个时候单库并不能解决大规模并发写入的问题，需要考虑分库。
为什么要分库？
一个库里表太多了，导致了海量数据，系统性能下降，把原本存储于一个库的表拆分存储到多个库上，通常是将表按照功能模块、关系密切程度划分出来，部署到不同的库上。
减少增量数据写入时的锁，对查询的影响；
由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单词查询所需的检索行数变少，减少磁盘IO，延时变短；
优点
分库是什么？
分布式事务的问题，数据完整性和一致性的问题；
数据操作维度问题：用户、交易、订单各个不同的维度；
跨库联合查询的问题，可能需要两次查询跨节点的count、order by、 group by 以及聚合函数问题。可能需要分别在各个节点上得到结果后，在应用程序端进行合并处理，增加额外的数据管理负担；
分库分表后的难题？
MySQL分库
分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁；
一旦数据并发量上来，如果在分区表实施关联，就是一个灾难；
自己分库分表，自己掌控业务场景和访问模式，可控。分区表不太可控；
为什么大部分互联网选择自己分库分表，而不选择分区表呢？
采用数据分片，数据分片指按照某个维度，将存放在单一数据库中的数据分散地存放至多个数据库或表中。
数据分片的有效手段就是对关系型数据库进行分库和分表。
区别于分区的是，分区一般都是放在单机里，用的比较多的是时间范围分区，方便归档。分库分表需要代码实现，分区则是MySQL内部实现。分库分表和分区并不冲突，可以结合使用。
随着业务的发展，数据量越来越多，高并发读写操作超过单个数据库服务器的处理能力怎么办？
分区、分表、分库
slave 会从 master 读取binlog 来进行数据同步；
1. master 将改变记录到二进制日志（binary log）。这些记录过程叫做：二进制日志事件（binary log events）;
salve 将 master 的binary log events 拷贝到它的中继器日志（relay log）;
slave 重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步且串行化的。
原理图
三个步骤
复制的基本原理
每个slave 只有一个master；
每个 slave 只能有一个唯一的服务器ID；
每个 master 可以有多个slave；
复制的基本原则
master提交事务前，先写日志，日志同步给从节点，所有从节点都接收成功，则可以提交事务，完成主从同步；
全量方式
master提交事务前，先写日志，日志只要被其中一个从节点接收成功，则可以提交事务，完成主从同步；
部分方式
复制的方式
延时。
复制的最大问题
主从复制
数据库表中的字段都是单一属性的，不可再分；
这个单一属性有基本类型过程，包括：整型、字符型、逻辑型、日期等；
例如 “地址” 这个属性，如果业务上还可以细分，如拆分为省市区等，那就必须细分，否则不符合第一范式；
第一范式（确保表中每列保持原子性）
数据库表中不存在非关键字段对任一候选关键字的部分函数依赖，即所有非关键字段都完全依赖于任意一组候选关键字；
部分函数依赖指的是存在组合关键字中某些字段觉得非关键字的情况；
第二范式（确保表中每列都和主键列相关）
在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式；
传递函数依赖，指的是如果存在“A->B->C”的决定关系，则C传递函数依赖A。
满足第三范式的数据库表不应存在传递依赖关系：关键字段 -> 非关键字段X -> 非关键字段Y；
第三范式（确保表中每列都和主键列直接相关，而不是间接相关/传递依赖）
数据库三大范式
关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织；
菲关系型数据库严格上不上一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等；
关系型数据库与非关系型数据库的区别？
没有使用覆盖索引，在200万的数据表执行需要4s左右
使用覆盖索引，只需要0.2秒左右
只查询一两列，并且要求是索引列。
局限性
使用覆盖索引
把分页的SQL语句改写成子查询
要求主键ID必须是连续的。
where 子句不允许再添加其他条件。
子查询优化
先在索引列上完成分页操作，然后再回表获取所需要的列，解决子查询的两个局限性
延迟关联
下次分页时直接从这个变量的位置开始扫描，从而避免MySQL扫描大量的数据再抛弃的操作
记录上次查询结束的位置
大数据表分页查询优化方法？
其他
MySQL与众不同主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。
主要完成一些类似连接处理、授权认证及相关安全方案；
引入线程池的概念，为通过认证安全连接的客户端提供线程；
可以实现基于SSL的安全连接，服务器也会为安全接入的客户端验证它所具有的操作权限。
连接层
主要完成大部分的核心服务功能， 包括查询解析、分析、优化、缓存、以及所有的内置函数；
所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等；
服务层
存储引擎真正的负责了MySQL中数据的存储和提取；
服务器通过API与存储引擎进行通信；
不同的存储引擎具有的功能不同，我们可以根据实际需要进行选取；
引擎层
主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互。
存储层
流程图
客户端请求
连接器（验证用户身份，给予权限）
查询缓存（存在缓存则直接返回，不存在则执行后续操作）
分析器（对SQL进行词法分析和语法分析操作）
优化器（主要对执行的sql优化选择最优的执行方案方法）
执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）
去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）
流程
MySQL查询的具体流程？或一条SQL语句在MySQL中如何执行的？
MySQL架构
存储引擎是MySQL的组件，用于处理不同表类型的SQL操作。一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求。
查看支持的存储引擎
查看默认存储引擎
准确查看某个数据库中的某一表所使用的存储引擎
查看存储引擎
建表时指定存储引擎（默认的就是INNODB，不需要设置）
修改存储引擎
修改默认存储引擎，也可以在配置文件my.cnf中修改默认引擎
设置存储引擎
InnoDB支持事务；MyISAM不支持事务；
InnoDB支持外键；MyISAM不支持；对一个包含外键的InnoDB表转MyISAM会失败；
InnoDB是聚簇索引；MyISAM是非聚簇索引；
InnoDB不保存表的具体行数，执行 select count(*) from table 需要全表扫描；MyISAM用一个变量保存了整个表的行数，执行上述语句读出该变量即可，速度非常快；
InnoDB最小的锁粒度是行锁，适合高并发；MyISAM最小的锁粒度是表锁，不适合高并发；一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。
InnoDB不仅缓存索引还缓存真实数据，对内存要求较高，内存大小对性能有决定性的影响；MyISAM只缓存索引，不缓存真实数据；
InnoDB表占用空间大；MyISAM表占用空间小；
InnoDB关注点在事务；MyISAM关注点在性能；
存储引擎对比
如果表的引擎是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失；
如果表的引擎是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失。
MyISAM更快，因为MyISAM内部维护了一个计数器，把表的总行数存储在磁盘上，可以直接调取；
InnoDB没有将表的总行数存储在磁盘上，需要全表扫描累加行数，所以数据表越大越耗时；InnoDB这样做的苦衷与其支持事务有关，由于多版本并发控制（MVCC）的原因，InnoDB表“应该返回多少行”是不确定的。
哪个存储引擎执行 select count(*) 更快，为什么？
存储引擎
整数类型
浮点数类型
字符串类型
日期类型
其他数据类型
char 是固定长度；varchar 长度可变；
char(n)，varchar(n)中的n都代表字符的个数；
超过char，varchar最大长度n的限制后，字符串会被截断；
相同点
char不论实际存储的字符数都会占用n个字符的空间，而varchar只会占用实际字符应该占用的字节空间加1（实际长度length，0<=length<255）或加2（length>255）。
能存储的最大空间限制不一样：char的存储上限为255字节。
char在存储时会截断尾部的空格，而varchar不会。
不同点
char 和 varchar 的区别？
BLOB是一个二进制对象，可以容纳可变数量的数据。有四种类型的BLOB：TINYBLOB、BLOB、MEDIUMBLO和 LONGBLOB
TEXT是一个不区分大小写的BLOB。四种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。
BLOB 保存二进制数据，TEXT 保存字符数据。
BLOB和TEXT有什么区别？
数据类型
MYSQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构，所以说索引的本质是：数据结构。
索引的目的在于提高查询效率，可以类比字典、 火车站的车次表、图书的目录等 。
简单理解为“排好序的快速查找的数据结构”。数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
索引本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上
平常说的索引，没有特别指明的话，就是B+树（多路搜索树，不一定是二叉树）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。此外还有哈希索引等。
下图是一种可能的索引方式示例
左边的数据表，一共有两列七条记录，最左边的是数据记录的物理地址。
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值，和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到对应的数据，从而快速检索出符合条件的记录。
图例说明
索引介绍
创建索引
修改表结构（添加索引）
创建
删除
查看
ALTER TABLE tableName ADD PRIMARY KEY (column_list): 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tableName ADD UNIQUE indexName (column_list) 创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tableName ADD INDEX indexName (column_list) 添加普通索引，索引值可出现多次。
ALTER TABLE tableName ADD FULLTEXT indexName (column_list)指定了索引为 FULLTEXT ，用于全文索引。
常用的 alter 命令
基本语法
B+树索引
Hash索引
Full-Text全文索引
R-Tree索引
数据结构角度
聚簇索引（clustered index）
非聚簇索引（non-clustered index），也叫 辅助索引（secondary index）
以上两个索引都是B+树结构
物理存储角度
主键索引：主键索引是一种特殊的唯一索引，不允许有空值；
普通索引/单列索引：每个索引只包含单个列，一个表可以有多个单列索引；
多列索引（复合索引、联合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合；
唯一索引：限制值必须唯一，可以有一个NULL值；
空间索引：空间索引是对空间数据类型的字段建立的索引；
逻辑角度
索引分类
首先要明白索引（index）是在存储引擎（storage engine）层面实现的，而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别。
3阶B树示例
每个节点最多有m个孩子；
除根节点和叶子节点外，其他每个节点至少有Ceil(m/2)个孩子；
若根节点不是子节点，则至少有2个孩子；
所有叶子节点都在同一层，且不包含其他关键字信息；
每个非终端节点包含n个关键字信息，m/2 <= n <= m，n=孩子个数-1；
看图好理解
每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划。
认识 B-Tree （B树）
B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。
4阶B+树示例
有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
除根节点和子节点外，每个节点至少有 （m+1）/2个孩子。
所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。
认识B+Tree（B+树）
非叶子节点只存储键值信息；
所有叶子节点之间都有一个链指针；
数据记录都存放在叶子节点中；
B+Tree 与 B-Tree 的几点不同
单一节点存储更多的元素，使得查询的IO次数更少。
所有查询都要查找到叶子节点，查询性能稳定。
所有叶子节点形成有序链表，便于范围查询。
B+Tree的优势（相对B-Tree）
MyISAM索引图示（索引和数据存放位置分离）
MyISAM引擎的索引文件和数据文件是分离的。这样的索引称为\"非聚簇索引\"。
MyISAM引擎索引结构的叶子节点的数据域，存放的并不是实际的数据记录，而是数据记录的地址。
MyISAM的主索引与辅助索引区别并不大，只是主键索引不能有重复的关键字。
MyISAM 主键索引与辅助索引
InnoDB主键索引图示
先在辅助索引上检索name，到达其叶子节点获取对应的主键；
②再使用主键在主索引上再进行对应的检索操作。
辅助索引检索过程（以上图name字段为例）
InnoDB辅助（非主键）索引图示
InnoDB的数据文件就是主键索引文件（索引和数据存放在一起），这种索引被称为“聚簇索引”，一个表只能有一个聚簇索引。
InnoDB引擎索引结构的叶子节点的数据域，存放的就是实际的数据记录（对于主索引，此处会存放表中所有的数据记录；对于辅助索引此处会引用主键，检索的时候通过主键到主键索引中找到对应数据行）。
InnoDB主键索引与辅助索引
MyISAM 和 InnoDB 引擎索引都采用B+索引
B+Tree索引
主要就是通过Hash算法，将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。
检索算法：在检索查询时，就再次对待查关键字再次执行相同的Hash算法，得到Hash值，到对应Hash表对应位置取出数据即可，如果发生Hash碰撞，则需要在取值时进行筛选。
MySQL目前有Memory引擎和NDB引擎支持Hash索引。
全文索引是MyISAM的一种特殊索引类型，主要用于全文索引；InnoDB从MYSQL5.6版本提供对全文索引的支持。
它用于替代效率较低的LIKE模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。
同样使用B-Tree存放索引数据，但使用的是特定的算法，将字段数据分割后再进行索引（一般每4个字节一次分割），索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。
空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型。
R-Tree空间索引
索引结构
select 的数据列只用从索引中就能够获取到，不必根据索引再次读取数据文件。查询列要被所建的索引覆盖。
使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为 using index。
判断标准
覆盖索引（Covering Index）
MySQL高效索引
通过唯一性索引可确保数据的唯一性。
加快数据的检索速度（大大减少检索的数据量），这是建索引最主要的原因。
加快表之间的连接。
在使用分组和排序字句进行数据检索的时候，可以减少分组和排序时间。
可以在查询过程中，使用优化隐藏器，提供系统的性能。
为什么要用索引？
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加；
索引需要占物理空间；
当对表的数据进行增删改的时候，索引也要动态的维护，这样降低了数据的维护速度；
创建索引有哪些缺点？
当从算法逻辑上讲，二叉树的查找速度和比较次数是最小的；
但是由于数据库索引是存储在磁盘上的，所以必须考虑磁盘IO的问题，磁盘IO是比较耗时的操作；
当数据量比较大的时候，索引的大小可能有几个G，是不可能全部加载到内存中的；
做法是逐一加载每一个磁盘页，这里的磁盘页对应着索引树的节点；
索引树的高度（层级）就是需要的磁盘IO次数；
在相同数据量的情况下，B+树的高度是小于二叉树的，数据量越大差距越明显。
数据库索引的原理，为什么要用 B+树，为什么不用二叉树？
聚簇索引具有唯一性：将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引。
聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来昨晚聚簇索引。如果已经设置了主键为聚簇索引，必须先删除主键，然后添加想要的聚簇索引，最后恢复设置主键即可。
误区：把主键自动设置为聚簇索引
聚簇索引的查询速度非常的快，因为整个B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据。
依赖于有序的数据，不是有序的数据插入或查找的速度比较慢；
更新代价大；
缺点
聚簇索引（InnoDB特有，Myisam没有）
更新代价比聚簇索引小；
非聚簇索引
案例数据表
检索过程图
InnoDB使用的是聚簇索引，将主键组织到一颗B+树中，而行数据存储在叶子节点上，若使用“where id=7” 这样的条件查找主键，则按B+树的检索算法即可查找到对应的节点，直接获得行数据。
若对Name列进行条件搜索，则需要两个步骤：1、在辅助索引B+树中检索Name，到达其他叶子节点获取对应的主键。2、使用主键在主索引B+树再执行一次B+树检索操作，最终到达叶子节点即可获得整行数据。
聚簇索引
MyISAM使用的是非聚簇索引，非聚簇索引的两颗B+树看上去没什么不同，节点的结构完全一致，只是存储的内容不同；
主键索引B+树的节点存储了主键，辅助索引B+树存储了辅助键；
表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别；
由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
聚簇索引与非聚簇索引 检索过程对比
使用场景
聚簇索引和非聚簇索引的区别？
UUID是字符串，比整型消耗更多的存储空间；
在B+树中进行查找时需要跟经过节点值比较大小，整型数据的比较运算比字符串更快速；
自增的整型索引在磁盘中会连续存储，在读取一页数据时也是连续；UUID是随机产生的，读取的上下两行数据存储是分散的，不适合执行按范围条件（如：where id>5 and id<10）查询语句。
在插入或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏左侧子树的结构；UUID主键很容易出现这样的情况：B+树为了维持自身的特性，有可能会进行结构的重构，消耗更多的时间。
为什么推荐使用整型自增主键而不是选择UUID？
保证数据一致性和节省存储空间。可以这么理解：商城系统订单表会存储一个用户ID作为关联外键，而不推荐存储完整的用户信息，因为当我们用户表中的信息（真实名称、手机号、收货地址···）修改后，不需要再次维护订单表的用户数据，同时也节省了存储空间。
为什么InnoDB非主键索引结构叶子节点存储的是主键值？
用B+树不用B树考虑的是IO对性能的影响，B树的每个节点都存储数据，而B+树只有叶子节点才存储数据，所以查找相同数据量的情况下，B树的高度更高，IO更频繁。
数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。其中在MySQL底层对B+树进行进一步优化：在叶子节点中是双向链表，且在链表的头结点和尾节点也是循环指向的。
为什么Mysql索引要用B+树不是B树？
因为Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，多个数据在存储关系上是完全没有任何顺序关系的；
所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。
哈希索引只适用于等值查询的场景。
而B+ Tree是一种多路平衡查询树，他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描。
哈希索引不支持多列联合索引的最左匹配规则，如果有大量重复键值得情况下，哈希索引的效率会很低，因为存在哈希碰撞问题。
为何不采用Hash方式？
主键自动建立唯一索引；
频繁作为查询条件的字段；
查询中与其他表关联的字段，外键关系建立索引；
单键/组合索引的选择问题，高并发下倾向创建组合索引；
查询中排序的字段，排序字段通过索引访问大幅度提高排序速度；
查询中统计或分组字段；
哪些情况需要创建索引？
表记录太少；
经常增删改的表；
数据重复且分布均匀的表字段；
频繁更新的字段不适合创建索引（会加重IO负担）；
where 条件里用不到的字段不创建索引；
哪些情况不需要创建索引？
如果值是唯一的，那使用唯一索引可以快速确定某条记录；
选择唯一性索引
经常需要Order by、Group by、Distinct、Union等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作；
为经常需要排序、分组和联合操纵的字段建索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此为这样的字段建立索引，可以提高整个表的查询速度；
为常作为查询条件的字段建索引
每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大；修改表时，对索引的重构和更新很麻烦，越多的索引，会使更新表变得很浪费时间；
限制索引的数目
对char(10)类型的检索 会比 char(100)类型的检索 快很多；
尽量使用数据量少的索引
如果索引字段的值很长，例如：text 和 blog类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度；
长字符尽量使用前缀索引
dba定期清理不再使用或很少使用的索引，从而避免这些索引对更新操作的影响；
删除不再使用或者很少使用的索引
最左前缀匹配原则
= 和 in 可以乱序
区分度公式：count(distinct col) / count(*) ，表示字段不重复的比例，比例越大需要扫描的记录数越少，唯一键的区分度为1；
尽量选择区分度高的列作为索引
例如：from_unixtime(create_time) = '2021-04-30' 不能使用索引，因为b+树中存的都是数据表中的字段值；所以应改写成：create_time = unix_timestamp('2021-04-30')；
索引列不能参与计算，保持列“干净”
尽量的扩展索引，不要新建索引
建索引要遵循哪些原则？
索引
手写
机读
SQL执行顺序
数据定义（DDL）：Create、Alter、Drop Table/Index等；
数据操纵（DML）：Insert、Update、Delete
数据控制（DCL）：grant、revoke
数据查询（DQL）：select
SQL语言包括哪几部分？
count(*) 包括了所有列，相当于行数，在统计结果的时候，不会忽略列值为NULL；
count(1) 包括了所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL；
count(列名) 只包括列名那一列，在统计结果的时候，会忽略列值为NULL的计数；
执行效果对比
列名为主键，count(列名) 效率最优；
如果表只有一个字段（不为主键），则count(*) 效率最优；
列名不为主键，count(1) 比 count(列名)快；
如果表有多个列并且没有主键，则count(1) 比count(*)快；
执行效率对比
count(*) 、count(1) 和 count(列名) 的区别？
exists：对外表用loop逐条查询，每次查询都会查看exists的条件语句，当exists里的条件语句能够返回记录时，条件就为真，返回当前loop到的这条记录；反之，如果exists里的条件语句不能返回记录，则当前loop到的这条记录被丢弃；exists的条件就像一个 bool 条件，当能返回结果集则 为true，不能返回结果集则为false。
in：in查询相当于多个or条件的叠加。
如果查询的两个表大小相当，那么用 in 和 exists 差别不大；
如果两个表一个较小，另一个是大表，则子查询表大的用exists，子查询表小的用 in;
MySQL中 in 和 exists 的区别？
两者都是将结果集合并为一个，两个要联合的SQL语句，字段个数必须一样，而且字段类型要“相容”（一致）；
UNION 在进行表连接后会去掉重复的数据记录（效率较低），而UNION ALL则不会去掉重复的数据记录；
UNION会按照字段的顺序进行排序，而UNION ALL只是简单的将两个结果合并就返回；
UNION 和 UNION ALL的区别？
交叉连接会把第一张表的每个值和第二张表的每个值进行匹配，结果如下：
交叉连接（cross join）
两个表有一共同列，且相等
相等连接
两个表有一共同列，且不相等
不等连接
自然连接只有在两张表中有相同的列（列的名称都相同）时才会有用，自然连接就是自动识别相同列的相等连接；
自然连接
内连接（inner join）
左连接（左外连接）
右连接（右外连接）
语法如下
全外连接
外连接（out join）
MySQL的两种连接：内连接、外连接？
查询
事务是由一组SQL语句组成的逻辑处理单元，主要用于处理操作量大，复杂度高的数据。例如：在人员管理系统中删除一个人员，需要删除人员的基本资料，也需要删除人员相关 的信息，这些数据库操作语句就过程一个事务。
原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）
事务的基本要素（ACID）
更新丢失（Lost Update）
脏读（Dirty Reads）
不可重复读（Non-Repeatable Reads）
幻读（Phantom Reads）
并发事务代来的问题
Read-Uncommitted（读未提交）
Read-Committed（读已提交）
Repeatable-read（可重复读）
Serializable（可串行化）
show variables like 'tx_isolation'; 或者 select @@tx_isolation;
show variables like 'transaction_isolation'; 或者 select @@transaction_isolation;
查看当前数据库隔离级别
事务隔离级别
MVCC是行级锁的一个变种，但它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只是锁定必要的行。MVCC的实现是通过保存数据在某个时间的快照来实现的。不管需要执行多长时间，每个事务看到的数据都是一致的。
MVCC只在Committed Read 和 Repeatable Read 两种隔离级别下工作。
InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现。一列保存了行的创建时间，一列保存行的过期时间（或删除时间）。存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来查询到每行的版本号进行比较。
InnoDB只查找创建版本号小于或等于当前事务版本号的数据行，这样可以确保事务读取的行，要么是在开始事务之前已经存在，要么是事务自身插入或修改过的。
过期版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除。
select
insert
delete
update
Repeatable Read 隔离级别下，MVCC工作方式
初始工作
SELECT
UPDATE
DELETE
举例说明
MVCC实现原理
解决不可重复读和幻读的问题；大多数操作都不用加锁，使数据操作简单，性能好。
缺点：每行记录都需要额外的存储空间，需要做更多的行检查工作和一些额外的维护工作。
优缺点
MVCC（多版本并发控制）
InnoDB使用日志来减少提交事务时的开销。因为日志中已经记录了事务，就无需在每个事务提交时把缓冲池的脏块刷新到磁盘中。
事务修改的数据和索引通常会映射到表空间的随机位置，所以刷新这些变更到磁盘需要很多随机IO。随机IO比顺序IO昂贵得多，因为一个IO请求需要时间把磁头移到正确的位置，然后等待磁盘上读出需要的部分，再转到开始位置。
InnoDB用日志把随机IO变成顺序IO。一旦日志安全写到磁盘，事务就持久化了，即使断电了，InnoDB重启后可以通过redo log恢复以及提交的事务。
InnoDB使用一个后台线程智能地刷新这些变更到数据文件。这个线程可以批量组合写入，使得数据写入更顺序，以提高效率。
事务的实现是基于数据库的存储引擎。不同的存储引擎对事务的支持程度不一样。MySQL 中支持事务的存储引擎有 InnoDB 和 NDB。
事务的实现就是实现ACID特性。事务隔离性是通过锁实现，而事务的原子性、一致性和持久性则通过事务日志实现。
在InnoDB存储引擎中，事务日志通过redo log 和日志缓存（InnoDB Log Buffer）实现。
事务开启时，事务中的操作，都会先写入存储引擎的日志缓存中，在事务提交之前，这些缓存的日志都需要提前刷新到磁盘上持久化，这就是“日志先行”（Write-Ahead logging）.
当事务提交之后，在Buffer Pool中影射的数据文件才会慢慢刷新到磁盘。此时如果宕机，那么当系统重启进行恢复时，可以根据redo log中记录的日志，把数据库恢复到奔溃前的一个状态。未完成的事务，可以继续提交或者选择回滚，这基于恢复的策略而定。
redo log 记录示例
在系统启动的时候，就已经为redo log分配了一块连续的存储空间，以顺序追加的方式记录redo log，通过顺序IO改善性能。所有事务共享redo log的存储空间，它们的redo log按语句的执行顺序，一次交替的记录在一起。
redo log（重做日志）实现持久化和原子性
undo log主要为事务的回滚服务。
undo log记录了数据在某个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。
undo log记录的是已部分完成并写入磁盘的未完成（未提交）的事务。
单个事务的回滚，不会影响到其他事务做的操作。
undo log（回滚日志） 实现一致性
两种日志都是为了恢复操作。
redo log是恢复提交事务修改的页操作。而undo log是回滚行记录到特定版本。
两者记录的内容也不同，redo log是物理日志，记录页的物理修改操作。而undo log是逻辑日志，根据每行记录继续记录。
redo log 与 undo log 的区别
假设操作示例
在1-8的任意一步系统宕机，事务未提交，该事务就不会对磁盘上的数据做任何影响。如果在8-9之间宕机，恢复之后可以选择回滚，也可以选择继续完成事务提交，因为此时redo log已经持久化。若在9之后系统宕机，内存映射中变更的数据还来不及刷回磁盘，那么系统恢复之后，可以根据redo log把数据刷回磁盘。
redo + undo log简化过程
事务日志包括：redo log（重做日志）和undo log（回滚日志）
事务的实现
事务日志
事务的隔离级别有哪些？MySQL默认隔离级别是什么？
MySQL事务的四大特性及实现原理？
MVCC是什么，它的底层原理是？
什么是幻读、脏读、不可重复读？
不可重复读的重点是修改
幻读的重点在于新增或删除
不可重复读与幻读的区别？
通常是应该完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此防止更新丢失应该是应用的责任。
更新丢失
一种是加锁
另一种是多版本并发控制
脏读、不可重复读、幻读 都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决
并发事务处理带来的问题及解决办法
错误日志
查询日志
二进制日志
中继日志
MySQL有多少种日志？
事务
MySQL知识体系总结
0 条评论
回复 删除
下一页
