MySQL-索引原理

MySQL索引原理

---

一、索引类型

索引可以提升查询速度，会影响where查询，以及order by排序。

1.普通索引

基于普通的索引类型，是基于普通字段建立的索引，没有任何限制。
创建索引的方法：

1. CREATE INDEX <索引的名字> ON tablename (字段名);
2. ALTER TABLE tablename ADD INDEX [索引的名字] (字段名)
3. CREATE TABLE tablename ( […], INDEX [索引的名字] (字段名) );

2.唯一索引

索引字段的值必须唯一，但允许空值。创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。
创建唯一索引的方法：

4. CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
5. ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
6. CREATE TABLE tablename ( […], UNIQUE [索引的名字] (字段名) ;

3.主键索引

它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主
键。
创建主键索引的方法如下：

7. CREATE TABLE tablename ( […], PRIMARY KEY (字段名) );
8. ALTER TABLE tablename ADD PRIMARY KEY (字段名);

4.复合索引

单一索引是指索引为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索引，这种索引做复合索引。
创建组合索引的方法如下：

9. CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2…);

10. ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2.

11. CREATE TABLE tablename ( […], INDEX [索引的名字] (字段名1，字段名2…) );
    不要过多使用会影响where查询，影响更新速度。优点是相比单一索引开销更少。

4.全文索引

数据比较时候会使用like查询，但是当数据比较多的时候一般使用全文索引，速度比like快几倍。mysql5.6后都支持MongoDB个MYISAM引擎。
创建索引的方法：

12. CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
13. ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
14. CREATE TABLE tablename ( […], FULLTEXT KEY [索引的名字] (字段名) ;

二、索引原理

存储引擎快速查找记录的一种数据结构，需要额外开辟和数据维护工作。

1. 索引是物理数据页存储，在数据文件中（InnoDB，ibd文件），利用数据页(page)存储
2. 索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。
   涉及的理论知识：二分查找、hash、B+Tree

1.二分查找法

二分查找也叫折半查找法，在有序数组中查找指定数据的搜索算法。优点：等值查询、范围性能优秀；缺点更新数据、新增数据、删除数据和维护成本。
查找方式：

3. 首先定义light和left指针
4. 计算（light+left）/2
5. 判断除2之后索引位置值和目标值的大小对比
6. 索引位置大于目标值-1，right移动，小于目标值+1，left移动
   例如：二分查找
   第一次查找：
   第一次查找比目标值大 所以右指针-1
   
   
   

2.Hash结构

底层是hash表来实现的，是根据键值对<key,value>存储数据的结构。非常适合根据key查找value值，也就是单个key 查询。

3.B-Tree和B+Tree结构

mysql采用的是B+Tree结构，在B-Tree结构上做了优化。

B-Tree结构

7. 索引值和data数据分布在整棵树结构中

8. 每个节点可以存放多个索引值及对应的data数据

9. 树节点中的多个索引值从左到右升序排列

10. B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有
    命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束
    

B+Tree结构（从左到右有指针）

相比于B-Tree多了指针

11. 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
12. 叶子节点包含了所有的索引值和data数据
13. 叶子节点用指针连接，提高区间的访问性能
    
    相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

4.聚簇索引和辅助索引

1 聚簇索引和非聚簇索引

B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引。

2 主键索引和辅助索引

B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引（二级索引）。

三 索引分析与优化

1 EXPLAIN

mysql提供了一个explain命令，用这个，命令可以对select语句进行分析，输出详细信息后可对其进行优化。

EXPLAIN SELECT * from user WHERE id < 3;

EXPLAIN 命令的输出内容大致如下：

2.最左前缀原则

复合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

3.LIKE查询

面试题：MySQL在使用like模糊查询时，索引能不能起作用？
回答：可以使用，只有把%字符放在后面才会使用到索引。

select * from user where name like 'o%'; //起作用

4.NULL查询

面试题：如果MySQL表的某一列含有NULL值，那么包含该列的索引是否有效？

答：对mysql来说，Null是一个特殊的值，用概率上说意味着是一个未知值，他处理方式与其他值有些不同。比如：不能使用=，<，>这样的运算符，对null做算法运算结果都是null，count时不会包括null等。NULL列需要增加额外空间来记录其值是否为NULL。虽然MySQL可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为NULL。最好设置NOT NULL，并给一个默认值，比如0和 ‘’ 空字符串等，如果是datetime类型，也可以=设置系统当前时间或某个固定的特殊值，例如’1970-01-01 00:00:00’。
代码如下（示例）：

5.索引与排序

MySQL查询支持filesort和index两种方式的排序，filesort是先把结果查出，然后在缓存或磁盘进行排序
操作，效率较低。使用index是指利用索引自动实现排序，不需另做排序操作，效率会比较高。

filesort有两种排序算法：双路排序和单路排序

1. 双路排序：需要两次磁盘扫描读取，最终得到用户数据。第一次将排序字段读取出来，然后排序；第二次去读取其他字段数据。
2. 单路排序：从磁盘查询所需的所有列数据，然后在内存排序将结果返回。

该处使用的url网络请求的数据。

---

总结

文章简单讲述了索引原理以及索引优化问题。