MySQL索引机制(详细+原理+解析)

Python微信订餐小程序课程视频

https://edu.csdn.net/course/detail/36074

Python实战量化交易理财系统

https://edu.csdn.net/course/detail/35475

MySQL索引机制

永远年轻，永远热泪盈眶

一.索引的类型与常见的操作

• 前缀索引

MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。

• 复合索引

集一个索引包含多个列（最左前缀匹配原则）

• 唯一索引

索引列的值必须唯一，但允许有空值

• 全文索引

在MySQL 5.6版本以前,只有MyISAM存储引擎支持全文引擎.在5.6版本中,InnoDB加入了对全文索引的支持,但是不支持中文全文索引.在5.7.6版本,MySQL内置了ngram全文解析器,用来支持亚洲语种的分词.

全文索引为FUllText，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值，全文索引可以在CHAR,VARCHAR,TEXT类型列上创建

• 主键索引

设定主键后数据会自动建立索引，InnoDB为聚簇索引

• 单列索引

即一个索引只包含单个列，一个表可以有多个单列索引

• 覆盖索引

覆盖索引是指一个查询语句的执行只用从所有就能够得到，不必从数据表中读取，覆盖索引不是索引树，是一个结果，当一条查询语句符合覆盖索引条件时候，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后的回表操作，减少了I/O效率

|  | -- 目前有一个key(name)索引，聚簇索引是key(id) |
|  | -- 使用了覆盖索引 |
|  | select id from stu where key = '天天'; |
|  | -- 不使用覆盖索引，因为查询的结果无法从普通索引树中得到 |
|  | select * from stu where key = '天天' |

---

查看索引

|  | show index from table\_name; |

列名解析：

列名title	解释desc	取值value
table	索引对应表的名称	DB中的表
Non_unique	索引包含value是否为唯一（是否为唯一索引）	0代表是唯一，1代表不是
Key_name	索引的名称	不命名为创建时列名称，联合查询为Seq_in_index为1的列名称，重复是使用_+number区分
Seq_in_index	索引中列的序列号，从1开始,表明在联合查询中的顺序，我们可以根据这个推断出联合索引中索引的前后顺序（使用最左优化原则）	从1递增至联合索引的列数
Column_name	索引的列名	索引的列名
Collation(n.排序方式，校队)	指排序方式	A表示升序，B表示降序，NULL表示未排序。
Cardinality	基数的意思，表示索引中唯一值的数目的估计值，我们知道某个字段的重复值越少越适合建立索引，所以我们一般根据Cardinality来判断索引是否具有高选择性，如果这个值非常小，就需要评估这个字段是否适合做索引	最小值为1，表示索引的列字段值都重复，最大为表中