MySQL索引简介

失忆的X

已于 2024-05-20 11:45:56 修改

阅读量104

点赞数

分类专栏： MySQL 文章标签： mysql

于 2020-08-05 00:23:20 首次发布

本文链接：https://blog.csdn.net/she1026/article/details/107792527

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

索引是什么

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。

下文，默认指的都是MySQL的innodb存储引擎，不再累述。

可以将索引简单理解为：排好序的快速查找数据结构。数据库在存储数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

下图就是一种可能的索引方式：

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

我们平时所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉树)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然,除了B+树这种类型的索引之外，还有哈希索引(hash index)等。

索引的优势

提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

索引的劣势

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
实际上索引也类似于一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用一定的存储空间。一张表上，创建的索引越多，占用的存储空间越大，INSERT、UPDATE和DELETE操作时更新表的时间，就会越长。

索引结构(B+Tree)

MySQL作为一个关系型数据库，数据的关联性是非常强的，区间访问是常见的一种情况。在原理图中，可以看到B+树的数据全部存储在叶子节点，并且通过指针串在一起，这样就很容易的进行区间遍历甚至全部遍历。

聚簇索引与非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。术语“聚簇”表示数据行和相邻的键值聚簇的存储在一起。如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。

聚簇索引的好处：

按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不不用从多个数据块中提取数据，所以节省了大量的io操作。

聚簇索引的限制：

对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。
由于数据物理存储排序方式只能有一种，所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。
为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。

索引分类

单值索引：一个索引只包含单个列，一个表可以有多个单列索引。建议一张表索引不要超过5个；优先考虑复合索引。

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name)
);
  
单独建单值索引：
CREATE  INDEX idx_customer_name ON customer(customer_name); 
 
删除索引：
DROP INDEX idx_customer_name  on customer;

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

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name),
UNIQUE (customer_no)
);
  
单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
 
删除索引：
DROP INDEX idx_customer_no on customer ;

主键索引：设定为主键后数据库会自动建立索引。

随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id) 
);
   
CREATE TABLE customer2 (
id INT(10) UNSIGNED,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id) 
);
 
 单独建主键索引：
ALTER TABLE customer add PRIMARY KEY customer(customer_no);  
 
删除建主键索引：
ALTER TABLE customer drop PRIMARY KEY;  
 
修改建主键索引：
必须先删除掉(drop)原索引，再新建(add)索引

复合索引：一个索引包含多个列。

 随表一起建索引：
CREATE TABLE customer (
id INT(10) UNSIGNED  AUTO_INCREMENT,
customer_no VARCHAR(200),
customer_name VARCHAR(200),
PRIMARY KEY(id),
KEY (customer_name),
UNIQUE (customer_name),
KEY (customer_no,customer_name)
);
 
单独建索引：
CREATE  INDEX idx_no_name ON customer(customer_no,customer_name); 
 
删除索引：
DROP INDEX idx_no_name  on customer ;

基本语法

-- 创建
CREATE  [UNIQUE ]  INDEX [indexName] ON table_name(column)

-- 删除
DROP INDEX [indexName] ON mytable

-- 查看
SHOW INDEX FROM table_name\G

-- 使用ALTER命令
-- 有四种方式来添加数据表的索引：该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

-- 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)

-- 添加普通索引，索引值可出现多次
ALTER TABLE tbl_name ADD INDEX index_name (column_list)

-- 该语句指定了索引为 FULLTEXT ，用于全文索引
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

哪些情况需要创建索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
单键/组合索引的选择问题，组合索引性价比更高
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

哪些情况不要创建索引

表记录太少
经常增删改的表或者字段
Where条件里用不到的字段不创建索引
过滤性不好的不适合建索引

总结

MySQL的主键索引就是采用的B+树。采用B+树，主要是因为B+树的特性决定的，树高较低，磁盘读取次数少。MySQL定义的一组数据结构为：指针地址+数据，分别为6B+8B=14B，第一层结构可以存储1170个数据。根据innodb的页大小进行界定的，根据命令 show variables like '%innodb_page_size%'，可以查看到页大小为16384B(16KB)，可以计算出来1170个数据。可以得出第一和第二层中可以存储1170*1170个索引。
InnoDB存储引擎是面向行的（row-oriented），也就是说数据的存放按行进行存放。每个页存放的行记录也是有硬性定义的，每页（16KB）最多允许存放2～200行的记录。
InnoDB的结构为聚簇索引，所谓聚簇索引就是在第三层结构中，包含索引和行数据，每页读出16KB，假定每行数据大小为2KB，则每页有8行数据，则一个索引树中包含1170*1170*8个数据(1095W数据)。
16Kb是根据操作系统的文件最小单位页进行定义，操作系统的文件最小为4Kb，取幂数进行。
三层树结构，能够最小的读取硬盘，读取两次数据页可以获取到1170*1170个索引，增大数据的覆盖率。
由于B+树的特性，修改和物理删除索引比较复杂，所以在操作过程中尽量减少物理删除和修改，物理删除可以修改成逻辑删除，增加标志位，进行表示删除。
由于使用的是聚簇索引，在第三层结构中，数据页是固定的为16KB，所以在定义数据结构时，应该定义适合的数据类型，避免使用过大的数据类型，如blob,text,clob等类型，大的数据类型，必然导致一次读取进内存的数据就会越少。
一定要设置主键且主键类型为自增数字类型，在生成索引树时，效率更高。
最左索引原则，是指在第三层中，已排序的数据能够快速的定位到，而且不必要扫描全部表。
myisam引擎也采用B+树，但是数据和索引是进行分离的，是典型的非聚簇索引，在第三层结构中，记录的索引和数据的物理地址。

参考：实力圈粉,人手一套尚硅谷教程