索引相关知识点
什么是索引
索引是一种用于快速查询和检索数据的数据结构。常见的索引结构有: B 树， B+树和 Hash。为什么mysql数据库没有使用哈希表作为索引的数据结构？
1)哈希表存在哈希冲突的问题 2)哈希索引不支持顺序和范围查询，这是它最大的缺点，当我们要对表中的数据进行排序或者进行范围查询时， 哈希索引就不行了。例如：SELECT * FROM tb1 WHERE id < 500 在这种范围查询中，优势非常大，直接遍历比500小的叶子节点就够了。而Hash索引是根据hash算法来定的， 难不成还要把1 - 499的数据，每个都进行一次hash计算来定位吗?这就是Hash最大的缺点了。B树和B+树的异同
1)B树的所有节点既存放键(key)也存放数据(data),而B+树只有叶子节点存放key和data,其他内节点只存放key。 2)B树的叶子节点都是独立的; B+树的叶子节点有一条引用链指向与它相邻的叶子节点。 3)B树的检索的过程相当于对范围内的每个节点的关键字做二分查找，可能还没有到达叶子节点，检索就结束了。 而B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。MyISAM引擎和InnoDB引擎都是使用B+树作为索引，但两者实现方式不一样
MyISAM引擎中，B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候， 首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值， 然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。 InnoDB引擎中，其数据文件本身就是索引文件(相比MyISAM，索引文件和数据文件是分离的), 其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。 这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引 (或聚集索引)”，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址， 这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据； 在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。因此,在设计表的时候, 不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。MyISAM引擎与InnoDB引擎的区别
1)MyISAM只有表级锁(table-level locking)，而InnoDB支持行级锁(row-level locking)和表级锁, 默认为行级锁。 2)MyISAM不提供事务支持。InnoDB提供事务支持，具有提交(commit)和回滚(rollback)事务的能力。 3)MyISAM不支持外键，而InnoDB支持外键。 4)MyISAM不支持MVCC，而InnoDB支持MVCC。 * MVCC中文名叫多版本并发控制，是现代数据库（如MySql）引擎实现中常用的处理读写冲突的手段， 目的在于提高数据库高并发场景下的吞吐性能。 * MySQL的InnoDB存储引擎默认事务隔离级别是RR(可重复读)，是通过"行级锁+MVCC"一起实现的， 正常读的时候不加锁，写的时候加锁。而MVCC的实现依赖：隐藏字段、Read View、Undo log。 * 另外MVCC只在 Read Committed 和 Repeatable Read两个隔离级别下工作， 其他两个隔离级别和MVCC不兼容索引的类型
主键索引 数据表的主键列使用的就是主键索引。 一张数据表有只能有一个主键，并且主键不能为null，不能重复。 在MySQL的InnoDB的表中，当没有显示的指定表的主键时，InnoDB会自动先检查表中是否有唯一索引的字段， 如果有，则选择该字段为默认的主键，否则InnoDB将会自动创建一个6Byte的自增主键。
二级索引(辅助索引)
二级索引又称为辅助索引，是因为二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，
可以定位主键的位置。唯一索引，普通索引，前缀索引等索引属于二级索引。
1)唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，
但是允许数据为 NULL，一张表允许创建多个唯一索引。建立唯一索引的目的大部分时候都是为了
该属性列的数据的唯一性，而不是为了查询效率。
2)普通索引(Index)：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，
并允许数据重复和 NULL。
3)前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引，
相比普通索引建立的数据更小，因为只取前几个字符。
4)全文索引(Full Text) ：全文索引主要是为了检索大文本数据中的关键字的信息，
是目前搜索引擎数据库使用的一种技术。
Mysql5.6之前只有MYISAM引擎支持全文索引，5.6 之后 InnoDB 也支持了全文索引。聚集索引与非聚集索引
聚集索引 聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。 在Mysql中，InnoDB引擎的表的.ibd文件就包含了该表的索引和数据， 对于InnoDB引擎表来说，该表的索引(B+树)的每个非叶子节点存储索引， 叶子节点存储索引和索引对应的数据。 聚集索引的优点 聚集索引的查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的， 定位到索引的节点，就相当于定位到了数据。 聚集索引的缺点 1)依赖于有序的数据：因为B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序， 如果数据是整型还好,否则类似于字符串或UUID这种又长又难比较的数据,插入或查找的速度肯定比较慢。 2)更新代价大：如果对索引列的数据被修改时，那么对应的索引也将会被修改，而且况聚集索引的 叶子节点还存放着数据,修改代价肯定是较大的,所以对于主键索引来说,主键一般都是不可被修改的。 非聚集索引 非聚集索引即索引结构和数据分开存放的索引。 二级索引属于非聚集索引。 MYISAM引擎的表的.MYI文件包含了表的索引，该表的索引(B+树)的每个叶子非叶子节点存储索引， 叶子节点存储索引和索引对应数据的指针，指向.MYD文件的数据。 非聚集索引的叶子节点并不一定存放数据的指针，因为二级索引的叶子节点就存放的是主键， 根据主键再回表查数据。 非聚集索引的优点 更新代价比聚集索引要小,非聚集索引的更新代价就没有聚集索引那么大了, 非聚集索引的叶子节点是不存放数据的 非聚集索引的缺点 1)跟聚集索引一样，非聚集索引也依赖于有序的数据 2)可能会二次查询(回表)：这应该是非聚集索引最大的缺点了。当查到索引对应的指针或主键后， 可能还需要根据指针或主键再到数据文件或表中查询。非聚集索引一定回表查询吗(覆盖索引)?
非聚集索引不一定回表查询。 试想一种情况，用户准备使用SQL查询用户名，而用户名字段正好建立了索引。 SELECT name FROM table WHERE name='guang19'; 那么这个索引的 key 本身就是 name，查到对应的 name 直接返回就行了，无需回表查询。 覆盖索引 如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。 我们知道在InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”， 也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！ 覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了，而无需回表查询。 如主键索引，如果一条SQL需要查询主键，那么正好根据主键索引就可以查到主键。 再如普通索引，如果一条SQL需要查询name，name字段正好有索引，那么直接根据这个索引就可以查到数据， 也无需回表。创建索引的注意事项
1)选择合适的字段创建索引 * 不为NULL的字段 * 被频繁查询的字段 * 被作为条件查询的字段 * 频繁需要排序的字段 * 被经常频繁用于连接的字段 2)被频繁更新的字段应该慎重建立索引。 虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。如果一个字段不被经常查询， 反而被经常修改，那么就更不应该在这种字段上建立索引了。 3)尽可能的考虑建立联合索引而不是单列索引。 因为索引是需要占用磁盘空间的，可以简单理解为每个索引都对应着一颗B+树。 如果是联合索引，多个字段在一个索引上，那么将会节约很大磁盘空间，且修改数据的操作效率也会提升。 4)注意避免冗余索引。 冗余索引指的是索引的功能相同，能够命中索引(a, b)就肯定能命中索引(a)，那么索引(a)就是冗余索引。 5)考虑在字符串类型的字段上使用前缀索引代替普通索引。 前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引代替普通索引。使用索引的一些建议
1)对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引 2)避免where子句中对字段施加函数，这会造成无法命中索引。 3)在使用InnoDB时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。 4)删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询sys 库的schema_unused_indexes视图来查询哪些索引从未被使用 在使用limit offset查询缓慢时，可以借助索引来提高性能MySQL如何为表字段添加索引？
1)添加 PRIMARY KEY（主键索引） ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2)添加 UNIQUE(唯一索引) ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3)添加 INDEX(普通索引) ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4)添加 FULLTEXT(全文索引) ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 5)添加多列索引 ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )最左匹配原则
最左匹配原则建立在联合索引的基础之上，最左优先，以最左边的为起点任何连续的索引都能匹配上。
同时遇到范围查询(>、<、between、like)就会停止匹配。索引下推
索引下推（index condition pushDown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。 索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。 例如：假设有一张表，有id,name,age,gender四个字段，id是主键，name,age是联合索引 当sql为：select * from table where name =? and age =?时 在没有索引下推之前,先根据name从存储引擎中获取复合规则的数据，然后在mysql的server层对age进行过滤 有索引下推之后，根据name,age两个的条件从存储引擎中获取对应的数据。 索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。 mysql默认开启索引下推功能。
一条SQL语句的执行过程
先看看mysql的基本组件
* 连接器：身份认证和权限相关(登录 MySQL 的时候)。 * 查询缓存：执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。 * 分析器：没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的SQL语句要干嘛， 再检查你的SQL语句语法是否正确。 * 优化器：按照MySQL认为最优的方案去执行。 * 执行器：执行语句，然后从存储引擎返回数据。 简单来说MySQL主要分为Server层和存储引擎层： Server层:主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现， 比如存储过程、触发器、视图，函数等，还有一个通用的日志模块binLog 日志模块。 存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持InnoDB、MyISAM、Memory等多个 存储引擎，其中InnoDB引擎有自有的日志模块redoLog模块。现在最常用的存储引擎是InnoDB， 它从MySQL5.5.5版本开始就被当做默认存储引擎了。一条SQL语句是如何执行的呢？
我们的sql可以分为两种，一种是查询，一种是更新（增加，更新，删除）。 查询语句 select * from tb_student A where A.age='18' and A.name=' 张三 '; 1)先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在MySQL8.0版本以前， 会先查询缓存，以这条sql语句为key在内存中查询是否有结果，如果有直接缓存,如果没有,执行下一步。 2)通过分析器进行词法分析，提取sql语句的关键元素，比如提取上面这个语句是查询select， 提取需要查询的表名为tb_student,需要查询所有的列，查询条件是这个表的 id='1'。 然后判断这个sql语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。 3)接下来就是优化器进行确定执行方案，上面的sql语句，可以有两种执行方案： a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是18。 b.先找出学生中年龄18岁的学生，然后再查询姓名为“张三”的学生。 那么优化器根据自己的优化算法进行选择执行效率最好的一个方案(优化器认为，有时候不一定最好)。 那么确认了执行计划后就准备开始执行了。 4)进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口， 返回引擎的执行结果。 更新语句 update tb_student A set A.age='19' where A.name=' 张三 '; 其实这条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志啦， 这就会引入日志模块了，MySQL自带的日志模块式binlog（归档日志），所有的存储引擎都可以使用， 我们常用的InnoDB引擎还自带了一个日志模块redoLog（重做日志）， 我们就以InnoDB模式下来探讨这个语句的执行流程。流程如下： 1)先查询到张三这一条数据，如果有缓存，也是会用到缓存。 2)然后拿到查询的语句，把age改为19，然后调用引擎 API 接口，写入这一行数据，InnoDB引擎把数据 保存在内存中，同时记录redoLog，此时redoLog进入prepare状态，然后告诉执行器，执行完成了， 随时可以提交。 3)执行器收到通知后记录binlog，然后调用引擎接口，提交redoLog为提交状态。 4)更新完成。
