【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 key
create table user1(id int primary key, name varchar(30));

第二种方式

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

第三种方式

create table user3(id int, name varchar(30));
-- 创建表以后再添加主键
alter table user3 add primary key(id);

主键索引的特点

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

唯一索引的创建

第一种方式

-- 在表定义时在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);

第二种方式

-- 创建表时在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));

第三种方式

create table user6(id int primary key, name varchar(30)
alter table user6 add unique(name);

唯一索引的特点

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

普通索引的创建

第一种方式

create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) --在表的定义最后指定某列为索引
);

第二种方式

create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name); --创建完表以后指定某列为普通索引

第三种方式

create table user10(id int primary key, name varchar(20), email varchar(30));
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);

普通索引的特点

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

全文索引的创建

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

举例

创建表结构

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)  --创建全文索引
)engine=MyISAM;  --存储引擎必须是MyISAM

插入数据

INSERT INTO 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> explain select * from articles where body like '%database%'\G

---

• 如何使用全文索引呢

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

通过explain来分析这个sql语句

mysql> explain SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database')\G

---

查询索引

第一种方法 show keys from 表名

  mysql> show keys from 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: NULL
Null:
Index_type: BTREE <= 以二叉树形式的索引
Comment:
1 row in set (0.00 sec)

第二种方法: show index from 表名;

第三种方法信息比较简略 desc 表名

推荐使用第二种

删除索引

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

alter table 表名 drop primary key;

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

alter table 表名 drop index 索引名

索引名就是show keys from 表名中的Key_name 字段

例

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

---

the end