数据库索引

什么是索引

MySQL官方对索引的定义为：索引是帮助MySQL高效获取数据的数据结构。

mysql简单创建索引方式：

CREATE [UNIQUE|CLUSTERED] INDEX index_name
ON table_name (column_name)
        --------其中UNIQUE和CLUSTERED为可选项，分别是建立唯一索引和聚簇索引，具体解释为：UNIQUE:表示此索引的每一个索引值只对应唯一的数据。CLUSTERED:表示要建立的索引时聚簇索引，即索引项的顺序与表中记录的物理顺序一致的索引组织。

为什么需要索引了？

大家在写sql查询语句的时候有没有想过数据库是如何执行查找的。如果向mysql发出一条sql查询语句请求，所查询的字段没有定义索引的话，很有可能就会导致全表扫描，这是因为数据库服务器搜索记录，在没有索引的情况下是搜索表的每一条记录，直到所有符合给定条件的数据被返回为止。说到这里，可能有小伙伴要问，为什么有了索引（在索引不失效的情况下）不会导致这种全表扫描了。这就要从索引的数据结构说起了。

适合做索引的数据结构

既然索引是为了优化查询速度，优化数据库性能。那么用的数据结构至少应具备查询快的特点，并且我们要明白的是mysql的数据是存储在磁盘的，查询的时候需要从磁盘中读取数据到内存，再到cpu进行筛选。（磁盘--->内存--->cpu）因此我们还需要避免多次从磁盘读取数据的过程，也就是减少IO操作。以下博主将简单根据上述特性来比较4中适合做索引的数据结构的优劣。

1>Hash算法

散列表（Hash table，也叫哈希表），是根据关键码值（Key value）而直接进行访问的数据结构。通过key的hash值来快速定位value的映射位置来达到快速查找。

在查询条件定义了索引的情况下，查询的时间复杂度位O(1)

优点：查询速度快，但存在Hash冲突的问题 ​ 缺点：不能进行范围查找，因为存储是无序的。不能拿着hash值进行大小比较

2>树结构-平衡二叉树

平衡二叉树的查询原理：

优点：支持范围查找，查找时间复杂度位logn，小于hash算法 ​ 缺点：插入和删除效率低。 在未查找到需要的元素的时候，访问左右孩子需要从磁盘中加载，这导致了多次IO操作并且范围查找可能要回溯，这样会导致更多的IO操作 （上图中范围查找有解释），这是因为mysql查找时需要 磁盘->内存->cpu 。

3>B树

AVL在查找的时候每访问一个新的节点需要进行IO操作，本质上IO操作的次数和树的高度有关，因此B树出现了

 

B树中的m：最多能含有m个孩子节点，同样插入1-10，B树的高度要比AVL树低，如果B树的m更大那么高度会更低。那么使用B树数据结构的索引进行查询操作的IO操作就会优于AVL树，但是还存在一个问题，在范围查找的时候还是可能需要回溯。

优点：支持范围查询，查找时间复杂度会优于AVL树(减少了IO），但是小于hash算法。
缺点：插入和删除效率低，范围查找可能需要回溯。

4>B+树

为了解决这个回溯问题，B+树来了，看下图，小伙伴应该发现了B+树的所有叶子节点中包含了所有非叶子节点。每个叶子节点增加了一个指向相邻叶子节点的指针,每个叶子节点也是最多容纳m-1个元素。

这样做的好处就是，在范围查询时不需要再进行回溯了，直接通过简单的链表操作即可。
注意：B+树中的非叶子节点仅仅存储了key就 是子树的最大最小值，只有叶子节点才存储了key和value，value就是真实的数据。

聚簇索引和稀疏索引

聚簇索引：将索引和数据存在一起，一个表仅有一个聚簇索引 默认位主键
稀疏索引：将数据存储和索引分开存储，，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中 时，速度慢的原因

辅助索引： 聚簇索引默认为主键，而将非主键列设为索引称为辅助索引  辅助索引需要二次查询，因为辅助索引中存储的value值是主键值

联合索引左前缀原则*

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配（不需要按照顺序来写，比如对于(a,b)联合索引写成 a and b 和 b and a都可以使用上索引)

索引分类

唯一索引：加速查询 + 列值唯一（可以有null）
​
主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
​
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并  
 
全文索引：对文本的内容进行分词，进行搜索

数据库优化方法

1、选取最适用的字段属性 ​ 2、使用连接（JOIN）来代替子查询(Sub-Queries) ​ 3、使用联合(UNION)来代替手动创建的临时表 ​ 4、事务 ​ 5、锁定表 ​ 6、使用外键 ​ 7、使用索引 ​ 8、优化的查询语句

什么情况下索引会失效

1.条件中有or，及时定义了索引也不会使用（这也就是为什么要少用or） ​ 2.想要使用or，但又不想要索引不失效那么就需要or两边的条件中的每一列都需要加上索引 ​ 3.多列索引，如果不是使用的第一部分，索引失效 ​ 4.模糊查询使用like的时候，条件是%开头，索引失效 ​ 5.条件是字符串，不适用引号的情况下索引失效 ​ 6.如果mysql估计使用全表扫描要比索引快的情况下不适用索引