一文读懂 MySQL 中的索引

1. 索引概述


1.1 索引概述

MySQL官方对索引的定义为：索引（Index）是资助MySQL高效获取数据的数据结构。
索引的本质：索引是数据结构。你可以简朴明确为“排好序的快速查找数据结构”，满意特定查找算法。 这些数据结构以某种方式指向数据， 如许就可以在这些数据结构的基础上实现 高级查找算法 。

1.2 长处

（1）类似大学图书馆建书目索引，进步数据检索的服从，降低数据库的IO本钱 ，这也是创建索引最主 要的缘故原由。
（2）通过创建唯一索引，可以保证数据库表中每一行 数据的唯一性 。
（3）在实现数据的 参考完整性方面，可以 加快表和表之间的连接 。换句话说，对于有依靠关系的子表和父表团结查询时， 可以进步查询速度。
（4）在使用分组和排序子句进行数据查询时，可以显著 减少查询中分组和排序的时间 ，降低了CPU的斲丧。

1.3 缺点

增长索引也有很多倒霉的方面，主要表如今如下几个方面：
（1）创建索引和维护索引要 耗费时间 ，并且随着数据量的增长，所耗费的时间也会增长。
（2）索引需要占 磁盘空间 ，除了数据表占数据空间之外，每一个索引还要占一定的物理空间， 存储在磁盘上 ，如果有大量的索引，索引文件就大概比数据文 件更快达到最大文件尺寸。
（3）虽然索引大大进步了查询速度，同时却会 降低更新表的速度 。当对表 中的数据进行增长、删除和修改的时间，索引也要动态地维护，如许就降低了数据的维护速度。
因此，选择索引时，需要综合考虑索引的长处和缺点。
   提示：
  索引可以进步查询速度，但是会影响插入记录的速度。这种环境下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。
  
1.6 常见索引概念

索引按照物理实现方式，索引可以分为 2 种：聚簇（聚集）和 非聚簇（非聚集）索引。
   我们也把 非聚集索引 称为 二级索引 或者 辅助索引。
  
1.6.1 聚簇索引

特点：

• 使用记录主键值的大小进行记录和页的排序，这包括三个方面的寄义：
  
  
  • 页内的记录是按照主键的大小次序排成一个单向链表 。
    
  • 各个存放用户记录的页也是根据页中用户记录的主键大小次序排成一个双向链表 。
    
  • 存放目录项记录的页分为差异的条理，在同一条理中的页也是根据页中目录项记录的主键大小次序排成一个双向链表 。
    
  
  
• 
  
  • B+树的 叶子节点 存储的是完整的用户记录。 所谓完整的用户记录，就是指这个记录中存储了全部列的值（包括隐藏列）。
    
  
  

长处：

• 数据访问更快 ，由于聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快（非聚簇索引要回表）
  
• 聚簇索引对于主键的 排序查找 和 范围查找 速度非常快
  
• 按照聚簇索引分列次序，查询表现一定范围数据的时间，由于数据都是紧密相连，数据库不消从多个数据块中提取数据，以是 **节流了大量的io **操作 。
  

缺点：

• 插入速度严重依靠于插入次序 ，按照主键的次序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
  
• 更新主键的代价很高 ，由于将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新
  

限制：

1.6.2 二级索引（辅助索引、非聚簇索引）

• 二级索引访问需要两次索引查找（回表） ，第一次找到主键值，第二次根据主键值找到行数据
  

概念：回表 我们根据这个以c2列大小排序的B+树只能确定我们要查找记录的主键值，以是如果我们想根据c2列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程称为 回表 。也就是根据c2列的值查询一条完整的用户记录需要使用到 2 棵B+树！
问题：为什么我们还需要一次 回表 操作呢？直接把完整的用户记录放到叶子节点不OK吗？
回答：

小结：聚簇索引和非聚簇索引的区别：

• 聚簇索引的 叶子节点 存储的就是我们的 数据记录， 非聚簇索引的 叶子节点 存储的是 数据位置（主键）。 非聚簇索引不会影响数据表的物理存储次序。
  
• 一个表只能 有 一个聚簇索引， 由于只能有一种排序存储的方式， 但可以有 多个非聚簇索引， 也就是多个索引目录提供数据检索。
  
• 使用聚簇索引的时间，数据的 查询服从高， 但如果对数据进行插入，删除，更新等操作，服从会比非聚簇索引低。
  

1.6.3 团结索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列创建索引，比方说我们想让B+树按 照 c2和c3列 的大小进行排序，这个包罗两层寄义：

• 先把各个记录和页按照c2列进行排序。
  
• 在记录的c2列雷同的环境下，采用c3列进行排序 。
  

为c2和c3列创建的索引的示意图如下：

留意一点，以c2和c3列的大小为排序规则创建的B+树称为 团结索引 ，本质上也是一个二级索引。它的意思与分别为c2和c3列分别创建索引的表述是差异的，差异点如下：

• 创建 团结索引 只会创建如上图一样的1棵B+树。
  
• 为c2和c3列分别创建索引会分别以c2和c3列的大小为排序规则创建2棵B+树。
  

1.8 MyISAM索引的原理

下图是MyISAM索引的原理图

如果我们在Col2上创建一个二级索引，则此索引的结构如下图所示：

1.9 MyISAM 与 InnoDB对比

MyISAM的索引方式都是“非聚簇”的，与InnoDB包罗1个聚簇索引是差异的。
小结两种引擎中索引的区别：
① 在InnoDB存储引擎中，我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录，而在 MyISAM 中却需要进行一次 回表 操作，意味着MyISAM中创建的索引相称于全部都是 二级索引 。
② InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是 分离的 ，索引文件仅保存数 据记录的地点。
③ InnoDB的非聚簇索引data域存储相应记录 主键的值 ，而MyISAM索引记录的是 地点 。换句话说， InnoDB的全部非聚簇索引都引用主键作为data域。
④ MyISAM的回表操作是非常 快速 的，由于是拿着地点偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地点去访问。
⑤ InnoDB要求表必须有主键 （ MyISAM可以没有 ）。如果没有显式指定，则MySQL系统会主动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL主动为InnoDB表天生一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

1.10 索引的代价

索引是个好东西，可不能乱建，它在空间和时间上都会有斲丧：

• 空间上的代价
  

每创建一个索引都要为它创建一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个 页 默认会占用 16KB 的存储空间，一棵很大的B+树由很多数据页组成，那就是很大的一片存储空间。

• 时间上的代价
  

每次对表中的数据进行 增、删、改 操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值 从小到大的次序排序 而组成了 双向链表 。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的次序而形成了一个单向链表。而增、删、改操作大概会对节点和记录的排序造成破坏，以是存储引擎需要额外的时间进行一些 记录移位 ， 页面分裂 、 页面回收 等操作来维护好节点和记录的排序。如果我们建了很多索引，每个索引对应的B+树都要进行相关的维护操作，会给性能拖后腿。
   一个表上索引建的越多，就会占用越多的存储空间，在增编削的时间，性能就会越差。为了能创建又好又少的索引，我们得学学这些索引在哪些条件下起作用的。
  

2. 索引的创建与筹划原则


2.1 索引的声明与使用


2.1.1 索引的分类


MySQL的索引包括平凡索引、唯一性索引、全文索引、单列索引、多列索引 和 空间索引等。

• 从 功能逻辑 上说，索引主要有 4 种，分别是平凡索引、唯一索引、主键索引、全文索引。
  
• 按照 物理实现方式 ，索引可以分为 2 种：聚簇索引 和 非聚簇索引。
  
• 按照 作用字段个数 进行划分，分成 单列索引 和 团结索引。
  

• 平凡索引
  

• 唯一性索引
  

• 主键索引
  

• 单列索引
  

• 多列(组合、团结)索引
  

• 全文索引
  

• 补充：空间索引
  

   小结：差异的存储引擎支持的索引类型也不一样
InnoDB ：支持 B-tree、Full-text 等索引，不支持 Hash索引；
MyISAM ： 支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory ：支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive ：不支持 B-tree、Hash、Full-text 等索引；
  
2.1.2 创建索引

2.1.2.1 创建表的时间创建索引

1. CREATE TABLE dept(
2.   -- 主键自动创建索引
3.   dept_id INT PRIMARY KEY AUTO_INCREMENT,
4.   dept_name VARCHAR(20)
5. );
6. CREATE TABLE emp(
7.   emp_id INT PRIMARY KEY AUTO_INCREMENT,
8.   --        自动创建唯一性索引
9.   emp_name VARCHAR(20) UNIQUE,
10.   dept_id INT,
11.   --        自动创建外键索引
12.   CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
13. );

复制代码

但是，如果显式创建表时创建索引的话，根本语法格式如下：

1. CREATE TABLE table_name [col_name data_type]
2. [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC |
3. DESC]

复制代码

• UNIQUE 、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；
  
• INDEX 与 KEY 为同义词，两者的作用雷同，用来指定创建索引；
  
• index_name 指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；
  
• col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
  
• length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
  
• ASC 或 DESC 指定升序或者降序的索引值存储。
  

• 创建平凡索引
  在book表中的year_publication字段上创建平凡索引，SQL语句如下：
  

1. CREATE TABLE book(
2.   book_id INT ,
3.   book_name VARCHAR(100),
4.   authors VARCHAR(100),
5.   info VARCHAR(100) ,
6.   comment VARCHAR(100),
7.   year_publication YEAR,
8.   -- 声明索引
9.   INDEX index_bname(book_name)
10. );

复制代码

• 创建唯一索引
  

1. CREATE TABLE test1(
2.   id INT NOT NULL,
3.   name varchar(30) NOT NULL,
4.   -- 声明唯一索引
5.   UNIQUE INDEX uk_index_name(name)
6. );

复制代码

该语句执行完毕之后，使用SHOW CREATE TABLE检察表结构：

1. SHOW INDEX FROM test1 \G

复制代码

• 主键索引
  设定为主键后数据库会主动创建索引，innodb为聚簇索引，语法：
  

• 随表一起建索引：
  

1. CREATE TABLE student (
2.   id INT(10) UNSIGNED AUTO_INCREMENT ,
3.   student_no VARCHAR(200),
4.   student_name VARCHAR(200),
5.   PRIMARY KEY(id)
6. );

复制代码

• 删除主键索引：
  

1. ALTER TABLE student drop PRIMARY KEY ;

复制代码

• 修改主键索引：必须先删撤除(drop)原索引，再新建(add)索引
  

• 创建单列索引
  

1. CREATE TABLE test2(
2.   id INT NOT NULL,
3.   name CHAR(50) NULL,
4.   INDEX single_idx_name(name(20))
5. );

复制代码

该语句执行完毕之后，使用SHOW CREATE TABLE检察表结构：

1. SHOW INDEX FROM test2 \G  

复制代码

• 创建团结索引
  举例：创建表test3，在表中的id、name和age字段上创建组合索引，SQL语句如下：
  

1. CREATE TABLE test3(
2.   id INT(11) NOT NULL,
3.   name CHAR(30) NOT NULL,
4.   age INT(11) NOT NULL,
5.   info VARCHAR(255),
6.   INDEX multi_idx(id,name,age)
7. );

复制代码

  留意：查询时要遵循最左前缀原则，否则索引不能名中。
  

• 创建全文索引
  举例1：创建表test4，在表中的info字段上创建全文索引，SQL语句如下：
  

1. CREATE TABLE test4(
2.   id INT NOT NULL,
3.   name CHAR(30) NOT NULL,
4.   age INT NOT NULL,
5.   info VARCHAR(255),
6.   FULLTEXT INDEX futxt_idx_info(info)
7. ) ENGINE=MyISAM;

复制代码

  
在MySQL5.7及之后版本中可以不指定末了的ENGINE了，由于在此版本中InnoDB支持全文索引。
  举例2：

1. CREATE TABLE articles (
2. id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
3. title VARCHAR (200),
4. body TEXT,
5. FULLTEXT index (title, body)
6. ) ENGINE = INNODB ;

复制代码

举例3：

1. CREATE TABLE `papers` (
2.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
3.   `title` varchar(200) DEFAULT NULL,
4.   `content` text,
5.   PRIMARY KEY (`id`),
6.   FULLTEXT KEY `title` (`title`,`content`)
7. ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

复制代码

差异于like方式的的查询：

1. SELECT * FROM papers WHERE content LIKE ‘%查询字符串%’;

复制代码

全文索引用match+against方式查询：

1. SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);

复制代码

  
留意点:
  

• 使用全文索引前，搞清楚版本支持环境；
  
• 全文索引比 like + % 快 N 倍，但是大概存在精度问题；
  
• 如果需要全文索引的是大量数据，建议先添加数据，再创建索引。
  

  

• 创建空间索引
  空间索引创建中，要求空间类型的字段必须为 非空 。
  举例：创建表test5，在空间类型为GEOMETRY的字段上创建空间索引，SQL语句如下：
  

1. CREATE TABLE test5(
2.   geo GEOMETRY NOT NULL,
3.   SPATIAL INDEX spa_idx_geo(geo)
4. ) ENGINE=MyISAM;

复制代码

2.1.2.2 在已经存在的表上创建索引


在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句。

• 使用ALTER TABLE语句创建索引 ALTER TABLE语句创建索引的根本语法如下：
  

1. ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
2. [index_name] (col_name[length],...  [ASC | DESC])

复制代码

• 使用CREATE INDEX创建索引CREATE INDEX语句可以在已经存在的表上添加索引，在MySQL中，
  CREATE INDEX被映射到一个ALTER TABLE语句上，根本语法结构为：
  

1. CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
2. ON table_name (col_name[length],...) [ASC | DESC]

复制代码

2.1.3 删除索引

• 使用ALTER TABLE删除索引 ALTER TABLE删除索引的根本语法格式如下：
  

1. ALTER TABLE table_name DROP INDEX index_name;  

复制代码

• 使用DROP INDEX语句删除索引 DROP INDEX删除索引的根本语法格式如下：
  

1. DROP INDEX index_name ON table_name;

复制代码

  提示: 删除表中的列时，如果要删除的列为索引的组成部门，则该列也会从索引中删除。如果组成
索引的全部列都被删除，则整个索引将被删除。
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。