mysql的一些概念与面试常见问题，烦人(小声bb)。
Mysql常见的问题
索引相当于一个字典，存放一个文件及在文件中的偏移量。索引存在的位置是存放在磁盘位置。基本概念：局部性原理(时间局部性和空间局部性)和磁盘预读(以页为单位)
1.mysql原子性和持久性怎么保证？
read log为了提升不会将每次修改的数据实时同步到磁盘中，而是先存放到缓存中，然后再使用线程去做缓冲池和磁盘中的同步。read log是重做日志，当事务提交的时候会把所有的修改信息存放在read log中，并且会把read Log持久化到磁盘中，当电脑重启后就会重新执行read log的修改信息，进而达到持久化的目的。read log包括两部分，重做日志缓冲和重做日志文件，一个是缓存一个是持久化的文件。read log分为两个步骤，prepare和commit也就是两阶段提交。
undo log：一个事务中的操作要么全部成功提交、要么全部失败回滚，这就是事务的原子性。undo log称之为回滚日志，每条数据的变化(insert/update/delete)都会产生一条记录，并且日志持久化到磁盘，undo log用来记录数据修改前的信息。
read log恢复事务导致的数据页的修改，是一种物理日志，undo log能够恢复数据记录到某个特定的版本，是一种逻辑日志
2.innode和myisam区别
innodb存储引擎的数据文件和索引文件放在一个文件夹中，myisam分为不同的文件
innodb支持事务，myisam不支持
innodb支持表锁和行锁，而myisam支持表锁
innodb有外键，myisam没有外键
3.索引分类：主键索引、唯一索引、普通索引和全文索引、组合索引。
主键索引：主键是一种唯一性索引，但它必须指定为PRIMARY_KEY，每个表只能有一个主键
唯一索引：索引列的所有值都只能出现一次，即必须唯一，值可以为空
普通索引：基本的索引类型，值可以为空，没有唯一性限制
全文索引：全文索引的类型为FULLTEXT，全文索引可以在varchar、char、text类型的列上创建
组合索引：多列值组成一个索引，专门用于组合搜索
4.innodb的底层数据结构（最左匹配、索引下推）
innodb的索引文件和数据文件存放在一起，b+树的叶子节点存放的是数据，是索引位置的那一行数据。
innodb是通过B+Tree结构对主键创建索引，然后叶子节点存储记录，如果没有主键，那么会选择唯一键，如果没有唯一键，那么就会生成一个6位的row_id来作为主键
如果创建索引的键是其他字段，那么会在叶子节点中存储的是该记录的主键，然后通过主键索引找到对应的记录，叫做回表。
索引覆盖：根据普通索引查找到维护的主键，再根据主键通过回表查询另一个b+树找到对应的数据；在进行一个查询是只查询主键，不需要回表
5.为什么使用b+树而不适用b树
数据结构的选取：
hash表：
不使用hash表的原因有，利用hash存储的话需要将所有的数据文件添加到内存，比较耗费内存空间；如果所有的查询都是等值查询，效率高，但是常用数据库为范围查询查找数据。
二叉搜索树和红黑树：
不使用该树无论是二叉树还是红黑树，都会因为树的深度过深而造成io次数变多，影响数据读取的效率。
数据库三范式：
第一范式：确保每列的原子性，如果每列都是不可再分的最小数据单元，则满足第一范式。
第二范式：首先需满足第一范式，并且表中非主键列不存在对主键的部分依赖，要求每张表只描述一件事。
第三范式：满足第二范式，并且表中的列不存在对非主键列的传递依赖，也有称为外键约束
数据库事务以及数据库事务属性：(相当重要)
事务作为单个逻辑工作单元执行的一系列操作，这些操作作为一个整体一起向系统提交，要么都执行，要么都不执行。事务需具备的属性ACID：
原子性(Atomicity)：事务是一个完整的操作。事务的各步操作是不可分的（原子的）；要么都执行，要么都不执行。
一致性(Atomicity)：当事务完成时，数据必须处于一致状态。
隔离性(Atomicity)：对数据进行修改的所有并发事务是彼此隔离的， 这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
持久性(Atomicity)：事务完成后，它对数据库的修改被永久保持，事务日志能够保持事务的永久性。
存储过程：
一组为了完成特定功能的 SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。
存储过程的优化:
1.尽量利用一些 sql 语句来替代一些小循环，例如聚合函数，求平均函数等。
2.中间结果存放于临时表，加索引。
3.少使用游标。 sql 是个集合语言，对于集合运算具有较高性能。而 cursors 是过程运算。比如对一个 100 万行的数据进行查询。游标需要读表 100 万次，而不使用游标则只需要少量几次读取。
4.事务越短越好。 sqlserver 支持并发操作。如果事务过多过长，或者隔离级别过高，都会造成并发操作的阻塞，死锁。导致查询极慢， cpu 占用率极地。
5.使用 try-catch 处理错误异常。
6.查找语句尽量不要放在循环内。
触发器:一段能够自动执行的程序
触发器是一段能自动执行的程序，是一种特殊的存储过程。
触发器和普通的存储过程的区别是：触发器是当对某一个表进行操作时触发。 诸如： update、 insert、 delete 这些操作的时候，系统会自动调用执行该表上对应的触发器。 SQL Server 2005 中触发器可以分为两类： DML 触发器和DDL 触发器，其中 DDL 触发器它们会影响多种数据定义语言语句而激发，这些语句有 create、alter、 drop 语句
数据库并发策略：乐观锁、悲观锁、时间戳
乐观锁是一种不加锁的状态，认为每次读取数据时，没有人更改数据
悲观锁认为自己访问数据时一定会被别人修改，每次操作数据时对数据进行加锁，悲观锁又有排他锁(写锁)和共享锁(读锁)
时间戳：在数据库中加入一列时间戳，每次读数据时，读取该字段，写数据时把该字段+1，提交数据时先进行比较，如果数据库的值大时，允许操作，否则不允许保存。
数据库锁:
行锁:排他锁的一种，防止其他事务修改此行
表锁：对当前整张表加锁，资源消耗大，mysql引擎支持，表锁分为表共享锁和表独占锁
页级锁:介于行锁与表锁之间，表级锁速度快，冲突多，行级锁冲突少，速度慢。
mysql分区分表：
分库分表有垂直切分和水平切分两种:
垂直切分(按照功能模块)
将表按照功能模块、关系密切程度划分出来， 部署到不同的库上。例如，我们会建立定义数
据库 workDB、商品数据库 payDB、用户数据库 userDB、日志数据库 logDB 等，分别用于
存储项目数据定义表、商品定义表、用户数据表、日志数据表等
水平切分(按照规则划分存储)
当一个表中的数据量过大时，我们可以把该表的数据按照某种规则，例如 userID 散列，进行划分，然后存储到多个结构相同的表，和不同的库上
再谈索引(2020年7月1日)
你好啊，七月！
为什么需要索引？
索引是为了更快速地查找到文件或查找到数据，类似于查字典时的前面的拼音或偏旁部首。索引一般需要存储的是：
关键值Key 、文件名称、偏移量offset
select name from user where id = 1;其中在id上建立索引，根据id = 1进行查找相关的数据
Mysql中的存储引擎：
MySam（B+树）、InnoDB（B+树）、Memory（Hash表）
磁盘读取与cpu速度之间的不匹配性，因此在做相关的IO读取时会利用局部性原理，局部性原理是程序的数据的访问都有聚集性成群的倾向，在一个时间段内，仅适用其中的一小部分，即空间局部性，或者最近访问过的程序代码和数据，很快又被访问的可能性跟大，即时间局部性原理。因此，再设计磁盘读取文件时一般会有预读，以页(页的大小通常为4k或4k的整数倍)为单位进行数据读取。
页是存储器的逻辑块，操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页，主存和磁盘以页为单位交换数据。
InnoDB
InnoDb是Mysql的默认事务型引擎，也是最重要、最广泛的存储引擎，用来处理大量短期事务。
InnoDB的数据存储在表空间中，是一系列的数据文件组成，InnoDB可以将每个表的数据和索引存放在单独的文件中。InnoDB采用MVCC来支持高并发，并实现四个隔离级别，默认级别为可重复读(REPEATABLE READ)，可以通过间隙锁策略(这个并不了解)放置幻读。
InnoDB内部做了很多的优化，从磁盘读取数据时采用可预读性预读，能够自动在内存中创建hash索引以加速读操作的自适应哈希索引(adaptive hash index)，以及能够加速插入操作的插入缓冲区(insert buffer)。
MYISAM存储引擎
myisam是默认的引擎，拥有全文索引、压缩、空间函数等特性，但是MYISAM不支持事务和行级锁 崩溃后无法安全恢复
MyISAM将数据文件和索引文件存放在两个文件中，分别为.MYD和.MYI的扩展名文件，该引擎能够对整张数据表进行加锁操作，读取数据时加共享锁，写入时则对表加排他锁，数据崩溃时可以手动或者自动检查和修复操作。
索引分类
B-Tree索引(这个不叫B减)
B-tree（多路搜索树，并不是二叉的）是一种常见的数据结构。B-Tree每个节点包含有：本结点所含关键字的个数、指向父节点的指针、关键字、指向子节点的指针(图片来自MYsql高性能)
通过从根节点开始进行向下查找，通过比较节点页与要查找的值可以适合进入下一层子节点，在节点中有页的上限和下限，对于叶子节点，其指向的是被索引的数据，而不是其他的节点页
1.全值匹配：与所有的列进行匹配。
2.匹配最左前缀：从左进行匹配，只是用索引中的最左边的信息
3.匹配列前缀：只匹配某一列的值的开头部分
4.匹配范围值：查询时使用范围查询，因此只是用某一列进行匹配
5.精确匹配某一列并范围匹配另外一列：使用某两列进行先关的数据信息查询
6.只访问索引的查询：查询只访问索引，不访问数据行。
B-Tree索引的限制：
必须进行最左匹配，否则无法使用索引
不能跳过索引的列
如果查询中是某个列的范围，则右边的所有列无法使用索引优化查找
哈希索引
其基于hash表实现，只有精确匹配索引所有的列的查询才有效，对于没一行数据，存储引擎都会对所有的索引列计算一个hash码，hash码作为键指向数据行。Mysql中的Memory引擎显示的支持hash索引。
hash索引的限制：
1.hash索引只包含哈希值和行指针，不存储字段值，因此不能使用索引中的值来避免读取行
2.不可以排序
3.哈希索引不支持部分索引列匹配，即不支持范围查询，只支持等值查询
4.可能存在哈希冲突，哈希冲突高时，会导致索引维护代价高
空间数据索引
MYISAM支持空间数据索引，一般作为地理数据存储，这类索引无需前缀信息，空间索引会进行所有维度的索引数据，查询时，可以任意维度组合查询。空间函数的支持GIS
全文索引
全文索引是一种特殊的类型的索引，其是从文件中找出关键词，而不是进行比较索引中的值。
索引的优点
1.索引大大减少服务器需要扫描的数据量
2.索引可以帮助服务器避免排序和临时表
3.索引可以将随机I/O变为顺序I/O
