【MySQL】详解索引操作

索引

什么是索引？

索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

🎓简单类比一下，数据库如同书籍，索引如同书籍目录，假如我们需要从书籍查找与 xx 相关的内容，我们可以直接从目录中查找，定位到 xx 内容所在页面，如果目录中没有 xx 相关字符或者没有设置目录（索引），那只能逐字逐页阅读文本查找，效率可想而知。

那换到数据库中，索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录。

所谓的存储引擎，说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。MySQL 存储引擎有 MyISAM 、InnoDB、Memory，其中 InnoDB 是在 MySQL 5.5 之后成为默认的存储引擎。

索引的优势和劣势

优势：

可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

• 被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。
• 如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。

劣势：

索引会占据磁盘空间

索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

索引类型

我们可以按照四个角度来分类索引。

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、全文索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引。

接下来，按照这些角度来说说各类索引的特点。

按数据结构分类

从数据结构的角度来看，MySQL 常见索引有 B+Tree 索引、HASH 索引、Full-Text 索引。

每一种存储引擎支持的索引类型不一定相同，表中总结了 MySQL 常见的存储引擎 InnoDB、MyISAM 和 Memory 分别支持的索引类型。

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
• 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；

在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

按物理存储分类

从物理存储的角度来看，索引分为聚簇索引（主键索引）、二级索引（辅助索引）。

• 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
• 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

所以，在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

按字段特性分类

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、全文索引、前缀索引。

主键索引

主键索引就是建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。

唯一索引

唯一索引建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。

普通索引

普通索引就是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE，允许在定义索引的列中插入重复值和空值。

全文索引

只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。

前缀索引

前缀索引是指对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，可以指定索引列的长度，但是数值类型不能指定。前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。

使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率。

按字段个数分类

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

• 建立在单列上的索引称为单列索引，比如主键索引；
• 建立在多列上的索引称为联合索引；

组合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则）。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

索引操作

创建索引

创建主键索引

第一种方式

-- 在创建表的时候，直接在字段名后指定 primary keycreatetable user1(id intprimarykey, name varchar(30));

第二种方式

-- 在创建表的最后，指定某列或某几列为主键索引createtable user2(id int, name varchar(30),primarykey(id));

第三种方式

createtable user3(id int, name varchar(30));-- 创建表以后再添加主键altertable user3 addprimarykey(id);

主键索引的特点：

• 一个表中，最多有一个主键索引，当然可以使符合主键
• 主键索引的效率高（主键不可重复）
• 创建主键索引的列，它的值不能为null，且不能重复
• 主键索引的列基本上是int

唯一索引的创建

第一种方式

-- 在表定义时，在某列后直接指定unique唯一属性。createtable user4(id intprimarykey, name varchar(30)unique);

第二种方式

-- 创建表时，在表的后面指定某列或某几列为uniquecreatetable user5(id intprimarykey, name varchar(30),unique(name));

第三种方式

createtable user6(id intprimarykey, name varchar(30)）；
altertable user6 addunique(name);

唯一索引的特点：

• 一个表中，可以有多个唯一索引
• 查询效率高
• 如果在某一列建立唯一索引，必须保证这列不能有重复数据
• 如果一个唯一索引上指定not null，等价于主键索引

普通索引的创建

第一种方式

createtable user8(id intprimarykey,
name varchar(20),
email varchar(30),index(name)--在表的定义最后，指定某列为索引);

第二种方式

createtable user9(id intprimarykey, name varchar(20), email varchar(30));altertable user9 addindex(name);--创建完表以后指定某列为普通索引

第三种方式

createtable user10(id intprimarykey, name varchar(20), email varchar(30));-- 创建一个索引名为 idx_name 的索引createindex idx_name on user10(name);

普通索引的特点：

• 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
• 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

全文索引的创建

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用**sphinx的中文版(coreseek)**。

举例：

创建表结构

CREATETABLE articles (
id INTUNSIGNEDAUTO_INCREMENTNOTNULLPRIMARYKEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)--创建全文索引)engine=MyISAM;--存储引擎必须是MyISAM

插入数据

INSERTINTO articles (title,body)VALUES('MySQL Tutorial','DBMS stands for DataBase ...'),('How To Use MySQL Well','After you went through a ...'),('Optimizing MySQL','In this tutorial we will show ...'),('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),('MySQL vs. YourSQL','In the following database comparison ...'),('MySQL Security','When configured properly, MySQL ...');

查询有没有database数据

mysql>select*from articles where body like'%database%';

使用如上查询方式，虽然查询出数据，但是有没有使用到全文索引呢

explain工具

可以用explain工具看一下，是否使用到索引

mysql>explainselect*from articles where body like'%database%'\G

• 如何使用全文索引呢？

mysql>select*from articles wherematch(title,body) against ('database');

通过explain来分析这个sql语句

mysql>explainSELECT*FROM articles WHEREMATCH(title,body) AGAINST ('database')\G

查询索引

第一种方法：

show keys from 表名

  mysql>showkeysfrom goods\G
***********1.row***********Table: goods <= 表名
Non_unique: 0<=0表示唯一索引
Key_name: PRIMARY<= 主键索引
Seq_in_index: 1
Column_name: goods_id <= 索引在哪列
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULLNull:
Index_type: BTREE<= 以二叉树形式的索引
Comment:
1rowinset(0.00 sec)

第二种方法:

show index from 表名;

第三种方法（信息比较简略）：

desc 表名；

推荐使用第二种

删除索引

第一种方法-删除主键索引

alter table 表名 drop primary key;

第二种方法-其他索引的删除

alter table 表名 drop index 索引名；

索引名就是

show keys from 表名

中的

Key_name

字段

例：

mysql>altertable user10 dropindex idx_name;

拓：第三种方法：

drop index 索引名 on 表名

索引最好设置为 NOT NULL

为了更好的利用索引，索引列要设置为 NOT NULL 约束。有两个原因：

• 第一原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。
• 第二个原因：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式 (opens new window)中至少会用 1 字节空间存储 NULL 值列表。

索引创建原则

我们平常在应用场景下，应该注意合理的去创建索引，索引最大的好处是提高查询速度，但是索引也是有缺点的，所以，索引不是万能钥匙，它也是根据场景来使用的。

什么时候适用索引？

• 字段有唯一性限制的，比如商品编码；
• 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
• 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

什么时候不需要创建索引？

• WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
• 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
• 表数据太少的时候，不需要创建索引；
• 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

the end