索引：帮助mysql高效获取数据，排好序的数据结构
索引的数据类型hash表，B+树，索引是key value 是磁盘 文件地址
早期索引：二叉树 1-2-3-4-5-6-7-8-9 层数太高
红黑树又叫二叉平衡树
特点：左右节点的层数不会超过一倍，根节点是黑色，通过左旋或者右旋保持平衡，比val树查询快速，平衡了插入和查询的速度。
B树：
节点下带着数据，每个索引元素不重复，多叉树，叶节点具有相同的深度，叶节点的指针为空，节点中的数据索引从左到右递增排列。
B+树
非叶子节点不存储data，只存在索引，这样可以存在更多的索引，叶子节点包含所有索引字段，叶子节点用指针连接，提高区间访问的性能
存储引擎是存表数据的，非聚集索引 索引跟数据没有放到一起，所以mylsam存储文件有三个
myslsam存储引擎，非聚集索引：叶子节点存放的是磁盘文件地址，拿到后还要拿着文件地址去查询数据。
没有设置索引列，系统会自动查找每一列，是否有一列数据都不同，如果有把这一列设置成索引列，形成B+树，如果找不到，mylsam会设置一个隐藏列
Innodb
聚集索引：索引和数据放在了一起，ID列的索引和数据默认放在一起的 表数据文件本身就是按B+Tree组织的一个索引结构文件，聚集索引叶子节点包含完整的数据记录
二级索引是非聚集索引，二级索引存放的数据值是ID的索引
ID 是必须有索引 是聚集索引 叶子节点包含数据 Innodb只有一个聚集索引
二级索引，叶子节点存的是ID的 通过ID查询
整形类型的主键，性能比UUID强
Hash 索引
对索引的key进行一次hash计算就可以定位出数据存储的位置
很多时候Hash索引要比B+树索引更高效
hash冲突问题
联合索引： 创建联合索引是，搜索就按照索引的排序进行查询
最左前簇优先原理：联合索引优先使用的是联合索引中第一个加索引的列，用联合索引查找数据值时，sql 优化会把联合索引顺序进行优化
索引的基本原理
索引是用来快捷查找那些具有特定值的记录，如果没有索引，查询的时候会遍历整张表
索引的原理就是把无序的数据变成有序的查询
1、把创建的索引列的内容进行排序
2、对排序结果生产倒排表
3，在倒排表内容上拼上数据地址链
4、在查询的赎回，先拿倒排内容，再取出数据地址链，从而拿出了具体的数据
mysql聚簇索引和非聚簇索引的区别
聚簇索引就是索引和数据在一起的，非聚簇索引是索引下面记录的是数据的地址。
-
聚簇索引：将数据存储和索引放到一起，并且是按照一定顺序组织的，找到索引也就是找到了数据，数据的物理存放循序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻的存放在磁盘上的
-
非聚簇索引：叶子节点不存储数据，存储的是数据行地址，也就是说根据索引查到数据行的位子，在取磁盘查找数据，这个就有点类似查字典
优势： 1、查询通过聚簇索引可以直接查询数据，相比 非聚簇索引需要二次查询，效率要高 2、聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的 劣势： 维护索引的很昂贵，特别是插入新的数据或者主键更新导致分页的时候，建议大量插入新行数据后，选在负载低的时间段，通过optimize table优化表，因为必须被移动的行数可能造成碎片，使用独享表空间可以弱锁片 如果表使用uuid作为主键，使数据存储稀疏，这就会出现聚簇索引有可能造成全表扫描更慢，所以建议使用int的auto_increment作为主键
mysql索引的数据结构，各有优劣
索引的数据结构和具体的存储引擎有关，在mysql中使用较多的所以就有hash索引，B+树索引，innodb存世引擎的默认索引为：B+索引，对于哈希表索引来说，底层数据结构即使哈希表，因此在绝大多数需求为单条记录查询的时候，就可以选择哈希索引，查询性能最快，其他大部分场景，建议选择BTree索引。
B+树：
B+树是一个平衡多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接，在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高，因此，B+树索引广泛应用于数据库，文件系统等场景。
对比：
如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可以找到相应的键值，前提是键值都是唯一的，如果键值不是唯一的，就先找到改键所在的位置，然后再根据链表往后扫描，直到找到相应的数据，
如果是范围查询索引，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没有办法再利用索引完成范围查询检索
哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询，哈希索引不支持多列联合索引的最左匹配原则
B+索引的关键字检索效率比较平均，不想B树那样波动幅度大。再有大量重复键值情况下，哈希索引的效率也是极地的，因为存在哈希碰撞问题。
索引设计的原则
查询更快，占空间更小
1、适合索引的列是出现在where子句中的列，或者连接子句中指定的列
2、基数较小的类，索引效果较差，没有必要在此列建立索引
3、使用短索引，如果随长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词长度超过索引前缀长度，泽使用索引排除不匹配的行，然后检查其余行是否可能匹配
4、不要过度索引，索引需要额外饿磁盘空间，并降低写操作的性能，在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间会越长，所以只保持需要的索引有利于查询即可
5、定义有外键的列一定要建立索引
6、更新频繁字段不适合创建索引
7、若是不能有效区分数据的列不适合做索引列，比如性别列，最多有三种情况男女未知
8、尽量的扩展索引，不要新建索引，比如表中已经有a的事索引，现在要加a，b的索引，那么就只需要修改原来的索引即可
9、对于那些查询中很少涉及的列，重复值较多的列不要建立索引
10、对于定义为text，image 和bit的数据类型不要建立索引
mysql的执行计划怎么看
执行计划就是看这条sql语句的执行顺序，以及如何使用索引查询，返回的结果集的行数
explain select * from A where x=？and y=？
执行这一条sql语句会出现下面一列信息
1、id：是一个有顺序的编号，是查询的顺序号，有几个select就显示几行，id的顺序是按select出现的顺序增长的，id列的值越大执行优先级越高，id列的值相同则从上往下执行，id列的值为null最后执行。
2、selecttype 表示查询中每一个select子句的类型
| select_type | 说明 |
|---|---|
| SIMPLE | 简单查询 |
| PRIMARY | 最外层查询 |
| SUBQUERY | 映射为子查询 |
| DERIVED | 子查询 |
| UNION | 联合 |
| UNION RESULT | 使用联合的结果 |
3、table : 正在访问的表名
| type | 说明 |
|---|---|
| ALL | 全数据表扫描 |
| index | 全索引表扫描 |
| RANGE | 对索引列进行范围查找 |
| INDEX_MERGE | 合并索引，使用多个单列索引搜索 |
| REF | 根据索引查找一个或多个值 |
| EQ_REF | 搜索时使用primary key 或 unique类型 |
| CONST | 常量，表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。 |
| SYSTEM | 系统，表仅有一行(=系统表)。这是const联接类型的一个特例。 |
性能：all< index < range < index_merge < ref_or_null < ref< eq_ref < system/const
性能在 range 之下基本都可以进行调优
4、possible_keys: 可能使用的索引
5、key : 真实查询使用的索引
6、key_len : MySQL中使用索引字节长度，这个字段可以评估组合索引是否完全被使用，这也是我们优化sql时，评估索引的重要指标
7、rows : mysql 预估为了找到所需的行而要读取的行数，这个值很重要，索引优化之后，扫描读取的行数越多，说明索引设置不对，或者字段传入类型之类的问题，说明优化空间越大
8、extra
| extra | 说明 |
|---|---|
| Using index | 此值表示mysql将使用覆盖索引，以避免访问表。 |
| Using where | mysql 将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当(并且如果)它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。 |
| Using temporary | mysql 对查询结果排序时会使用临时表。 |
| Using filesort | mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。 |
| Range checked for each record(index map: N) | 没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的 |
9、filter：返回结果的行占需要读到行的百分比，就是百分比越高，说明需要查询到数据越准
事务的隔离级别
事务基本特性ACID分别是：
原子性是指一个事务中操作要么全部成功，要么全部失败
一致性是指数据库总是从一个一致性的状态装换成另外一个一致性的状态。
隔离性是指一个事务的修改在最终提交前，对其他事务是不可见的
持久性指的是一旦事务提交，所做的修改就会永久保存到数据库中
隔离性有四个隔离级别，分别是
-
read uncommitted 读未提交，可能会读到其他事务未提交的数据，也叫做脏读
用户原本读取id=1的用户age应该是10，但是读取到其他没有提交的age，结果读取到age=20，这就是脏读
-
read committed 读已经提交，只能读取已经提交的事务数据，也可以叫做不可重复读，它解决了脏读。可能照成两次读取的结果不一致，一次事务第一次读到得数据age=10 再次读取的结果是age=20，这可能在，两次查询的过程中间插入了另外的一个修改事务。
-
repeatable read 可重复读，就是mysql的默认级别，就是每次读取结果都一样，但是有可能产生幻读
-
serializable 串行化，一般不会使用，他会给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。
幻读，在一个事务的两次查询 中数据笔数不一致，例如一个事务查询了几列数据，而另一个事务，却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是他先前没有的。
关心过业务系统里面的sql耗时没么，统计过慢查询么，对慢查询都怎么优化过
-
在业务系统中，除了使用主键进行的查询，其他的都会在测试库上测试其耗时，慢查询的统计主要是运维在做，会定期将业务中的慢查询反馈给我们。
-
慢查询的优化首先要搞明白慢查询的原因，是查询条件没有命中索引，还是load了不需要的数据列。还是本身数据量大。
所以优化也是针对这三个方面
1、首先分析语句看看是否加载了额外的数据，可能是查询了多余了行，或者加载了不需要的列，对语句进行分析以及重写。
2、分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引。使语句尽量命中索引。
3、如果对语句无法优化，可能考虑表的数据量太多，如果是的，可能进行对表的横向或者纵向分表
简述innodb和mylsqm存储引擎的区别
mylsam：不支持事务，但是没次查询都是原子的，支持表级锁，没次操作就对整个表加锁
存储表的总行数，存储文件有3个，索引文件，表结构文件，数据文件。采用非聚集索引，索引文件的数据域存储的数据文件的地址，辅索引与住索引基本一致，但是辅索引不用保证唯一性。
innodb：支持事务ACID，支持事务事务的四种隔离级别，支持行级锁乃外键约束，因此支持并发，不存储总行数。
一个innodb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里）也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制。
之间索引是聚集索引，索引和数据存放在一起，福索引的数据域存储的是主键的值，因此辅索引查找数据，需要先通过辅索引查到主键索引，才能查到值，最好使用自增主键，防止插入的数据时，为维持B+树结构，导致索引重排，消耗mysql的性能
ACID靠什么保证的？
A：原子性有undo log 日志保证，它记录了需要回滚的日志信息，事务回滚是撤销已经自行成功的sql。
C：一致性有其他三大特性保证，程序代码要保证业务上的一致性
D：持久性有内存加 redo log 来保证，mysql修改数据同时在内存和redo log记录这次操作，宕机的时候可以从redo log 恢复
innodb redo log 写盘，innodb 事务进入 prepare（准备） 状态
如果前面prepare 成功 binlog 写盘，在继续将事务日志持久化到 binlog 如果持久化成功，那么innodb事务泽进入commit状态（在redo log 里面写一个commit 记录）
redo log的刷盘会在系统空闲进行
事务的特点
- 在事务的是个特点中，一致性是事务的根本追求，而在某些情况下对事务一致性造成破坏
- 事务的并发执行
- 事务故障或系统故障
- 数据库系统通过并发控制技术的日志恢复技术来避免这种情况的发型
- 并发控制技术保证了事务的隔离性，是数据库的一致性状态不会因为并发执行的操作被破坏。
- 日志恢复技术保证了事务的原子性，使一致性状态不会因为事务或系统被破坏，同时使已提交的对数据库的修改不会因系统崩溃而丢失，保证了事务的持久性。
原子性实现原理：undo log
- undo log 是为了实现事务的原子性，在mysql数据库innodb存储引擎中，还用undo log来实现多版本并发控制（简称 MVCC）
- 在操作任何数据之前，首先将数据备份到一个地方(这个存储数据备份的地方称之为undo log)。然后进行数据的修改，如果出现了错误或者用户执行了rollback 语句，系统可以利用undo log 中的备份将数据恢复到事务开始之前状态
- 注意：undo log的逻辑日志，可以理解为：
- 当delete一条记录时，undo log或记录一条 对应的inset记录
- 当inset一条记录时，undo log或记录一条 对应的delete记录
- 当update一条记录时，undo log或记录一条 对应的update记录
逻辑日志，每一条数据，相当于一条行记录
物理日志，16kb一个磁盘块真实存在的
隔离性MVCC锁
mysql的隔离级别
- 事务具有隔离性，理论上来说事务之间的执行不应该相互产生影响，其对数据库的影响应该和它们串行执行时一样。
- 然而完成的隔离性会导致系统并发性能很低，减低对资源的利用率，因而实际上对隔离性的要求会有所放宽，这也会一定程度造成对数据库一致性要求降低
- sql标准为事务定义了不通的隔离级别，从低到高依次是
- 读未提交
- 读以提交
- 可重复读
- 串行化
mysql默认的隔离级别 可重复读
Oracle默认隔离级别 读已提交
区别：幻读，读取的版本号不一样
间隙锁 解决幻读
读完提交隔离级别
脏读：一个事务能够读取没有提交的数据
读以提交隔离级别：
这一个事务不会读取到另外一个事务没有提交的数据，但是当另外一个事务提交了，会发现这一个事务查询的两次数据是不一样的，这一个事务第一次查询的时候显示，之前数据，但是另外事务修改了该数据并且已经提交了，这一个事务再次查询会得到别外一个数据提交后的数据，这种就是不可重复读，是有问题的
可重复的隔离级别
解决了不可重复读问题，一个事务开启查询数据，只会查询这个事务开启当前版本的数据，如果这时另外一个事务开启并且已经提交了，这个事务读取的数据还原来的数据，这个隔离级别解决了不可重复读问题。
幻读：幻读出现在插入的情况，两个事务，一个事务新增一条数据，如（insert into user values（5，“zhangsan”）并且提交事务，另一个事务也提交这一个sql语句 会报错 ID重复，但是这个事务查询了发现没有ID为5的数据。这种情况就是幻读。
解决方法，可以加一个间隙锁 如一个事务执行select * from user where id>5 for update 另一个事务新增数据 （insert into user values（6，“zhangsan”） 就不会新增成功，因为被锁住了，等这个查询事务执行结束提交了，这个数据才能新增成功。这样就解决了幻读问题。
隔离性实现原理：锁
- 基于锁的并发流程控制
- 事务根据自己多数据项进行的操作类型申请相应的锁（读申请共享锁，写申请排它锁）
- 申请锁的请求被发送给锁管理器，锁管理器根据当前数据项是否已经有锁以及申请的和他有的锁是否冲突决定对否为该请求授予锁
- 若锁被授予，则申请锁的事务可以继续执行，若被拒绝，则申请锁的事务将进行等待，知道锁没其他事务释放
- 可能出现的问题
- 死锁：多个事务持有锁并互相等待其他事务的锁导致所有事务都无法继续执行
- 扩展：出了锁可以实现并发控制之外，好友其他策略：
- 基于时间戳的并发控制
- 基于有效性检查的并发控制
- 基于快照隔离的并发控制
for update 排他锁
lock in share mode 共享锁
innodb：共享锁，排它锁
读锁 写锁
mystem：共享锁，独占锁
行数：锁一行数据
间隙锁：锁某一个范围 比如 （1,7）范围1到7到不包括1和7
临界锁：行锁和间隙锁的统称【1,7】范围1到7到包括1和7
自增锁 ：ID就是自增锁
持久性实现原理 redo log
和undo log 相反，redo log 记录的新数据的备份，在事务提交前，只要将redo log 持久化即可，不需要将数据持久化，当系统崩溃时，虽然数据没有进行持久化，但是 redo log已经持久化系统可以根据redo log的内容，将所有数据恢复到最新的状态。
数据先写到内存中然后再进行持久化，日志也一样。
mysql主从同步原理
mysql的主从同步过程：
mysql的主从复制送主要只有3个线程，master（binlog dump thread）、slave （I/0 thread , sql thread ）,master一条线程好slave中的两条线程
- 主节点binlog 只从复制的基础就是主库记录数据库的所有变更记录到binlog，binlog 是数据库服务器启动的那一刻器，报存所有修改数据库结构或内容的一个文件。
- 主节点log dump 线程，当binlog有变动时，log dump线程读取其内容发送给从节点
- 从节点I/O线程接收binlog内容，并将其写到relay log文件中
- 从节点的sql线程读取relaylog文件内容对数据更新进行重放，最终保证主从数据库的一致性。
注：主节点使用binlog问价+position偏移量来定位主从同步的位置，从节点报错其已接收到的偏移量，如果从节点发生宕机重启，则会自动position的位置发起同步
由于mysql默认的复制方法是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后没日志就会丢失了，由此产生两个概念。
全同步复制
- 主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回客户端，但是很显然这个方式的话性能会受到严重影响。
半同步复制
- 和全同步不同的是，半同步复制逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。
简述mysql中索引类型及多数据库性能的影响
普通索引允许被索引的数据列包含重复的值
唯一索引：可以保证数据记录的唯一性
主键：一种特殊索引，在一张表中只能定义一个主键索引，用于唯一标识
联合索引：索引可以覆盖多个列
全文检索：通过建立倒排索引，可以极大的提升检索效率模式目前搜索引擎使用一种关键技术，可以通过 alter table table_name ADD FULLTEXT(column)创建索引
索引可以极大的提升搜索效率，通过使用索引，可以查询过程中，使用优化隐藏器，提高系统的性能。但是会减低，插入删除，更新表的数据，这个这操作也会操作索引文件
索引需要占物理空间，处理数据表占数据空间之外，每一个索引还要占一定的物理空间，如果建立聚簇索引，那么空间需要的更大，如果非聚索引很多，一旦聚簇索引改变，那么所有非聚集索引都会跟着变。
mysql的索引一般有几层
一般情况下3到4层就足以支撑千万级别的变查询
创建索引的字段长了好还是短的好？
短了好，原因是在层数不变的情况下，可以存储耕读的数量
我们在创建表的的时候用代理主键还是自然主键？
能使用代理主键尽可能多使用代理主键
主键设置好之后建议只增，否则会增加索引文件维护成本
分布式应用场景中，用雪花算法，snowflake，自定义id生成器
回表：第一次查询name的b+树，然后查询出id，再去根据id查询数据，这就是回表
索引覆盖：select id，name from user where name=张三 在进行检索的时候，直接根据name去nameB+获取到了id，此时就不用回表。所以有的时候，在复杂的sql中，可以考虑将不相关的列都设置为索引列，作为联合索引
索引下推：在没有索引下推之前，先根据name去存储引擎中拉取符合结果的数据，返回到server层，在server层中对age的条件进行过滤
有了索引下推之后，根据name，age两个条件直接从存储引擎中拉取结果，不需要在server层做条件过滤
innodb 的主键索引就是聚簇索引，必须包含一个主键列，如果创建表的时候制定了主键，就是唯一主键，如果没制定，有一列数据是是不同的，也可以作为唯一键，如果没有那么key就是6字节的rowid
什么是MVCC
多版本并发控制：读取数据时通过一种类似快照的方式将数据报错下来，这样读锁和写锁就不会冲突了，不同的事务session会看到自己特定版本的数据，版本链
mvcc只在read committed 读已提交 和repeatable read 不可重复读两个隔离级别下工作，其他两个隔离级别够和mvcc不兼容，因为可重复读总是没次读取最新的数据，而不是符合当前事务版本的数据行，而可串行化会对所有的读取的数据加锁
聚簇索引有两个必要的隐藏列：
trx_idL用来存储内存对某条聚簇索引记录进行修改的时候的事务id
roll_pointer：内存对那条聚簇索引记录有修改的时候，都会把老版本写入undo日志中，这个roll_ponter就是存了一个指针，指向这条聚簇索引记录的上一个版本的位置，通过他来获得上一个版本的记录信息。
