mysql索引优化及底层原理学习

索引的数据结构及其算法

索引是帮助MySql高效获取数据的排好序的数据结构
索引的发展大概阶段: 二叉树->红黑树->Hash表->B-Tree->B+Tree
二叉树:
极端情况下二叉树会退化成链表,导致查询性能大大降低
红黑树:
虽然自带平衡属性,但是在数据量很大的情况下,树高会比较高。查询性能也会收到大打折扣
B-Tree:
叶节点具有相同的深度,叶节点的指针为空。
所有索引元素不重复
节点中的数据索引从左到右递增排列

如上图,15,56,77…表示具体的索引值,比如主键id。data是表每一行的数据。每次根据索引查询时,查找节点id,然后根据二分查找查询到子节点，以此内推定位到具体数据。但是由于mysql每页大小是固定的(page页是mysql数据在磁盘上的基本存储单位),默认是16K。那么每一个叶节点的空间也是有限的,所以一页存储的索引字段也是确定的，那么在数据量大的情况下，树高必定会增加，虽然比红黑树性能提升比较大，但是还不是不完善。
B+Tree:
非叶子节点不存储data，只存索引。可以放更多的索引
叶子结点包含所有索引字段
叶子结点用指针连接,提高区间访问的性能

与B-Tree相比,非叶子节点不再保存数据。节省空间可以存储更多的索引字段。叶子节点保存了所有的索引字段，非叶子冗余了部分索引字段，每次查询通过二分查找更快找到数据。叶子节点保存索引字段和行数据，每个数据页之间通过指针连接,跨区间访问性能更高
Hash:

对索引的字段进行hash运算,计算具体的位置。和hashMap数据结构类似，hash值以数组保存，每个hash值后面以链表保存数据
很多时候hash索引比B+Tree索引更高效。比如等值查找的情况下,数组查找效率较高。但是hash索引没有排序，所以不能进行范围查找。会存在hash碰撞问题
MyISAM:
在存储引擎MyISAM中,索引文件和数据文件是分离的

存储引擎是对于表而言，如果表的存储引擎为MyISAM.在索引文件保存了索引的具体结构，但是叶子结点的数据值保存的是Mysql数据文件每一行的文件地址
InnoDB:


首先解释一下聚集索引。索引字段和数据在同一个B+Tree上就是聚集索引,每张表有且仅有一个聚集索引。通过是表主键，如果表没有主键mysql会维护一个默认的自助主键作为聚集索引。所以通常建议表必须要有主键(自己维护主键比mysql好,减少mysql额外开销)。
如上图1就是主键聚集索引，非聚集索引存储索引字段和主键索引的值，通过非聚集索引查询时获取具体的主键索引再去聚集索引中获取具体值，这个就叫回表。
为什么建议主键是整形且自增的？因为主键索引是排序的，新增时会进行对比，整型比较比字符串速度要快(字符串是一个个字符比较,极端情况下,可能要到最后一个字符才比较出结果)，所以建议是整型的。新增数据时，如果是非自增的，由于索引是排序的，所以每次要计算这个数据放到哪里，因为每个索引数据页是固定的，可能会造成部分数据页重新分配。如果自增，只要每次加到最后面即可，不有有额外计算和开销
联合索引:

多个字段值组成的索引字段就是联合索引。每个索引字段首先按第一个排序，然后排序第二个字段…最左匹配就是必须得根据第一个字段排序查询，第二个字段才会是有序。

一条sql在Mysql中是怎样执行的

mysql的内部组成结构

连接器:
我们知道mysql由于是开源的,他有非常多种类的客户端，Navicat，mysql，front，jdbc，SQLyog等非常丰富的客户端,这些客户端要向mysql发起通信必须先跟server端建立通信连接，而建立连接的工作就是由连接器完成。连接器负责跟客户端建立连接，获取权限，维持和管理连接
查询缓存
连接建立完成后，就可以执行select语句。Mysql收到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果哦可能会以key-value对的形式直接缓存到内存中。key是查询的语句，value是查询的结果，如果你的查询能够直接在这个缓存中找到key，那么value会直接返回给客户端
但是大多数查询缓存很鸡肋。因为查询缓存往往利大于弊，查询缓存的失效非常频繁，又要有对一个表的更新，这个表上的所有查询缓存都会被清空。因为可能我们费劲的把结果缓存起来，还没使用就被清空掉了，对于压力非常大的数据库来说，缓存命中率非常低。
一般建议大家在静态表里面使用缓存，就是极少更新的表。如字典表，配置表。具体参数在my.cnf中可以设置query_cache_type参数
分析器:
如果没有命中缓存，就要开始真正执行语句了。mysql需要知道你要做什么，因此需要对sql语句做解析。分析器先会做词法分析，语法分析，检查你的sql是否满足规则。大概是一下6个步骤:
1.词法分析
2.语法分析
3.语义分析
4.构造执行树
5.生成执行计划
6.计划的执行
优化器:
经过了分析器，mysql就知道你要做什么。在开始执行之前，还要经过优化器的处理。优化器是在表了有多个索引的时候，决定使用哪个索引，或者在一个语句有多表关联的时候，决定各个表的连接顺序
执行器:
开始执行的时候，要先判断一下你对这个表是否有执行查询的权限，如果没有，就会返回没有权限的错误。如果有权限，就继续执行，执行器会根据表的引擎定义，去使用这个引擎提供的接口
bin-log归档:
我们的sql执行时，会将sql语句的执行逻辑记录在我们的bin-log中。bin-log是server层实现的二进制日志，他会记录我们的cud记录。bin-log有以下几个特点:
1.bin-log在mysql的server层实现(所有引擎共用)
2.bin-log为逻辑日志，记录的是一条语句的原始逻辑
3.bin-log不限大小，追加写入，不会覆盖以前的日志
通过my.cnf配置bin-log相关参数配置

Explain详解

使用explain关键字可以模拟优化器执行sql语句，分析你的查询语句或是结构的性能瓶颈，在select语句之前增加explain关键字，mysql会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条sql
注意，如果from中包含子查询，仍会执行该子查询，将结果放入临时表中

上图为加了explain关键所返回的结果
1.id列:
表示执行的顺序，数值越大表示越先执行，相同情况下按显示列表位置从上往下执行
2.select_type列:
表示对应的是简单查询还是复杂查询
simple: 简单查询，查询不包含子查询和union
primary: 复杂查询中最外层的select
derived: 包含在from子句中的子查询，mysql会将结果存放在一个临时表中，也称派生表
3.table列:
表示当前这个执行计划访问的那个表
4.type列:
表示关联类型或访问类型，即mysql决定如何查找表中的行，查找数据记录行的大概范围。依次从优到差的排序: system>const>eq_ref>ref>range>index>all.一般来说，得保证查询达到range级别，最好达到ref。
NULL: mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。例如:在索引列中选取最小值，可以单独查找索引来完成，不需要再执行时访问表，如:

explain select min(id) from order;

const, system: mysql能对查询的某部分进行优化并将其转化成一个常量。用于primary key(主键索引)或unique key(唯一索引)的所有列与常数比较时，所以表最多有一个匹配行，读取一次，速度比较快。system是const的特例，表里只有一条元祖匹配时为system
eq_ref: primary key(主键索引)或unique key(唯一索引)的所有部分被连接使用，最多只会返回一条符合条件的记录，这可能是在const之外最好得联接类型了，简单的select查询不会出现这种type
ref: 相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀
range: 范围扫描 通常出现在in(),between,>,<,>=等操作中
inde: 扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点看是快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的。这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比all快一些
ALL: 即全表扫描，扫描你的聚集索引的所有叶子节点，通常这种情况下需要增加索引进行优化。
5.possible_keys列:
这一列表示查询可能使用哪些索引来查找。explain时可能出现possible_keys有列，而key显示NULL的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能，然后用explain查看效果
6.key列
这一列显示具体使用的索引，如果没有使用索引，则该列是NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用force index
7.key_len:
这一列显示了mysql使用索引的字节数，通过这个值可以算出具体使用了索引中的哪些列。举例来说，film_actor的联合索引idx_film_actor_id有film_id和actor_id两个int列组成，并且每个int是4个字节。通过结果中的key_len=4可以判断出查询使用了第一个列
key_len计算规则如下:
字符串 char(n)和varchar(n),5.0.3以后的版本中，n均代表字符数，而不是字节数。char(n)如果存汉字长度就是3n字节，varchar(n)如果存汉字则长度是3n+2字节，加的2字节用来存储字符串长度，因为varchar是变长字符串
数值类型 tinyint 1字节.smallint 2字节.int 4字节.bigint 8字节
时间类型 date 3字节 timestamp 4字节 datetime 8字节
如果字段允许为null，需要1字节记录是否为null。索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引
8.ref列:
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有const(常量),字段名(如:film.id)
9.row列
这一列是mysql估计要读取并检测的行数，注意这不是结果集里的行数
10.Extra列
这一列展示的是额外信息。
Using index: 使用覆盖索引。覆盖索引定义,mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是覆盖索引，extra里一般都有using index，覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树获取其他字段值
Using where 使用where语句来处理结果，并且查询的列未被索引覆盖

explain select * from order where order_number = 1

Using index condition 查询的列不完全被索引覆盖，where条件中式一个前导列的范围

explain select * from order where order_number > 1 

Using temporary mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化
Using filesort 将用外部排序而不是索引排序，数据较小从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑用索引来优化
索引日常优化注意事项
1.全值匹配，联合索引中，尽量使用全部查询条件
2.最左匹配原则，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列
3.不在索引列上做任何操作,计算，函数，自动或手动类型转换，会导致索引失效而转向全表扫描
4.存储引擎不能使用索引中范围条件右边的列
5.尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少select * 语句
6.mysql在使用不等于(!=或者<>)，not in, not exists的时候无法使用索引会导致全表扫描<小于， >大于，<=, >=这些，mysql内部优化器会根据检索比例，表大小等多个因素整体评估是否使用索引
7.is null、is not null 一般情况下也无法使用索引
8.like以通配符开头(’%abc…’)mysql索引会失效变为全表扫描
9.字符串不加单引号索引失效
10.少用or或in，用他查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例，表大小等多个因素整体评估是否使用索引
11.范围查询优化，尽量范围不要过大，否则优化器检查可能不会走索引
12.like aa%相当于=常量，%aa和%aa%相当于范围
回表
回表概念:由于每张表都有主键聚集索引存储了索引字段和行数据，在二级索引中保存的是索引字段和主键索引data，当我们用二级索引时通过索引字段查询到主键索引，然后去主键聚集索引获取具体数据，这个过程就叫回表。为了避免回表，所以在联合索引中查询字段和条件都是索引字段
索引下推
mysql5.6版本开始引入。like aa%就用到了索引下推，mysql5.6版本之前，like aa%会在二级索引中拿到主键索引，然后一个个回表去获取具体数据，mysql5.6版本之后,like aa%在二级索引查询中会根据联合索引的后面字段继续查询部分数据，然后再进行回表。就是在查询中先根据条件索引字段获取所有数据，减少了回表次数
优化总结：
1.mysql支持两种方式的排序filesort和index，Using index是指Mysql扫描索引本身完成排序，index效率高，filesort效率低
2.order by 满足两种情况会使用Using index.(1).order by 语句使用索引最左前列。(2).使用where子句与order by 子句条件列组合满足索引最左前列
3.尽量在索引列上完成排序，遵循索引建立时的最左前缀法则
4.如果order by的条件不在索引列上，就会产生Using filesort
5.能用覆盖索引尽量用覆盖索引
6.group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则，对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写where中的限定条件就不要去having限定了
Using filesort文件排序原理详解
filesort文件排序方式。
单路排序: 是一次行取出满足条件行的所有字段，然后在sort buffer中进行排序
双路排序: 又叫回表排序模式，是根据相应的条件取出相应的排序字段和可以直接定位行数据的行id，然后在sort buffer中进行排序，排序完后需要再次取回其它需要的字段。
mysql通过比较系统变量max_length_for_sort_data(默认1024字节)的大小和需要查询的字段总大小来判断使用哪种排序模式。小于max_length_for_sort_data，使用单路排序。大于max_length_for_sort_data，使用双路排序
表关联常见两种算法
嵌套循环连接(BNL)
一次一行循环地从第一张表(驱动表)中读取行，在这行数据中取到关联字段，根据关联字段在另一张表(被驱动表)里取出满足条件的行，然后取出两张表的结果集
基于块的嵌套循环连接算法(NLJ)
把驱动表的数据读入到join_buffer中，然后扫描被驱动表。把被驱动表每一行取出来跟join_buffer中的数据对比
Mysql对于被驱动表的关联字段没索引的关联查询，一般都会使用BNL算法，如果有索引一般选择NLJ算法。有索引的情况下NLJ算法比BNL算法性能更高。
对于关联sql的优化
关键字段加索引，让mysql做join操作时尽量算账NLJ算法
小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用sraight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间
straight_join解释:straght_join功能同join类似，但能让左边的表来驱动右边的表，能改变优化器对于联表查询的执行顺序
count(*)优化
字段有索引:count() ≈ count(1) > count(字段) > count(id) 字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段) > count(id)
字段无索引:count() ≈ count(1) > count(id) > count(字段) 字段没有索引count(字段)统计走不了索引，count(id)还可以走主键索引，所以count(id)>count(字段)

数据库事务及机制

事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性，通常简称为事务的ACID属性
原子性(Atomicity): 事务是一个院子操作单元，其对数据的修改，要么全部执行，要么全部不执行
一致性(Consistent): 在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性
隔离性(Isolation): 数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的独立环境执行，这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
持久性(Durable): 事务完成之后，它对于数据的修改是永久性，即时出现系统故障也能够保持
脏读:
一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态，这时另一个事务也来读取同一条就。如果不加控制，第二个事务读取了这些脏数据，并据此作进一步的处理，就会产生未提交的数据依赖干洗，这种现象被形象的叫做脏读。一句话就是事务A读取到了事务B已经修改但未提交的数据。
不可重复读
同一个事务前后两次读取的数据不一致，就是不可重复读
幻读
同一个事务两个读取的数据条数不一致，就是幻读
事务隔离级别

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上串行化，这显然与并发是矛盾的。查看当前数据库的隔离级别:show variables like ‘tx_isolation’; 设置事务隔离级别:set tx_isolation=‘REPEATABLE-READ’; Mysql默认的事务隔离级别是可重复读，用spring开发，如果不设置隔离级别默认使用mysql的隔离级别
数据库锁
从性能上分为乐观锁和悲观锁
从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)
读锁(共享锁,S锁(Shared)):针对同一份数据，多个读操作可以同时进行而不会相互影响
写锁(排他锁,X锁(eXclusive)):当前写操作没有完成前，他会阻断其他写锁和读锁
从对数据操作的粒度分，分为表锁和行锁
表锁 每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低，一般用在整张表数据迁移的场景
行锁 每次操作锁住一行数据。开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
MyISAM在执行查询语句select前，会自动给涉及的所有表加读锁，在执行update，insert，delete操作会自动给涉及的表加写锁
InnoDB在执行查询语句select时，不会加锁。但是update，insert，delete操作会加行锁
简而言之,就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞
间隙锁
间隙锁，锁的就是两个值之间的空隙。是可重复读事务隔离级别下解决幻读的一种锁机制

MVCC多版本并发控制机制

mysql在可重复读隔离级别下如何保证事务较高的隔离性，这个隔离性就是靠MVCC(Multi-Version Concurrency Control)机制来保证的，对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥。而在串行化隔离级别为了保证较高的隔离性是通过将所有操作加锁互斥来实现的
Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制
undo日志版本链与read view机制详解
undo日志版本链式指一行数据被多个事务依次修改后，在每个事务修改完后，mysql会保留修改钱的数据undo回滚日志。并且用两个隐藏字段trx_id(事务id)和roll_pointer(上一个事务日志指针)把这些undo日志串联起来形成一个历史记录版本链

如上图，在可重复读隔离级别下，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view(在读已提交的隔离级别下每次查询都会生成),这个视图是由所有未提交的事务id数组和已提交的最大的事务id组成(如:[100, 200], 300)，每次sql查询时需要把undo版本记录链和read-view做对比得到结果.
版本链对比规则：
如果当前行的事务id<read-view中的最小id，表示这个版本数据是已提交事务生成的，这个数据是可见的
如果当前事务id>read-view中的最大id，表示这个版本是由将来启动的事务生成的，是不可见的
如果当前最小id<事务id<最大id。需要进行判断，如果这个事务id在read-view未提交事务数组中，则不可见。否则可见
对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的trx_id,同时在该条记录的头信息(record header)里的(deleted_flag)标记写上true，来表示当前记录已经被删除，在查询按照上面的规则查到对应的记录如果delete_flag标记为true，意味着记录已被删除，则不返回数据
总结:MVCC机制的实现就是通过read-view机制与undo版本链对比机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链的不同版本数据
InnoDB引擎sql执行的BufferPool缓存机制

为什么mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行sql？因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差。因为磁盘随机读写的性能是非常差的，所以直接更新磁盘文件是不能让数据库抗住高并发的，mysql这套机制看起来很复杂，但是它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同事还能保证各种异常情况下的数据一致性。
更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高，要远高于随机读写磁盘文件。