1.创建索引
1.1 创建一个B+tree索引
- 给name创建一个B+tree索引
CREATE INDEX idx_name ON table_name (name);
- 给name,age创建一个联合索引
CREATE INDEX idx_name_age ON table_name (name, age);
1.2 创建一个hash索引
CREATE INDEX idx_hash ON table_name (column_name) USING HASH;
table_name 是表的名称,column_name 是要在其上创建哈希索引的列名称。执行此 SQL 语句后,数据库管理系统将在 column_name 列上创建一个哈希索引,适用于
等值筛选的场景
,以提高等值查询的性能。
1.2.1 等值筛选
等值筛选是数据库查询操作的一种,指的是通过某个列的精确值来筛选出符合条件的行。例如,查询员工表中工资等于某个特定值的员工记录,或者查询客户表中客户名等于某个特定值的客户记录,这些都属于等值筛选操作。
1.2.2 hash索引缺点
- 不支持范围查询和排序
- 不适用于模糊查询
- 不支持索引覆盖
- 内存消耗较大
- 哈希冲突
1.3 创建一个全文索引
CREATE FULLTEXT INDEX idx_content ON articles (content);
1.3.1 全文索引的适用场景
1.3.2 全文索引的缺点
- 占用更多的存储空间
- 索引更新开销较高
- 无法处理所有文本类型
- 不支持所有的搜索操作
- 可能存在词汇歧义问题
2. 查找索引
2.1 describe语句
DESCRIBE T;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| ID | int | NO | PRI | NULL | |
| k | int | NO | MUL | 0 | |
| s | varchar | NO | | '' | |
+-------+---------+------+-----+---------+-------+
在上面的示例中,k 列有一个索引,并且在 Key 列中显示为 MUL。这表示 k 列上有一个普通索引。
2.2 show index语句
SHOW INDEX FROM T;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| T | 0 | PRIMARY | 1 | ID | A | 6 | NULL | NULL | | BTREE | | |
| T | 1 | k | 1 | k | A | 6 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
在上面的示例中,显示了表 T 的两个索引:PRIMARY 主键索引和 k 列上的普通索引。
3. 删除索引
DROP INDEX index_name ON table_name;
4. 注意事项
4.1 针对创建索引
4.1.1 DISTINCT 字段需要创建索引
有时候我们需要对某个字段进行去重,使用 DISTINCT,那么对这个字段创建索引,也会提升查询效率。这是因为索引会对数据按照某种顺序进行排序,所以在去重的时候也会快很多。
4.1.2 字段的数值有唯一性的限制
业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引。(来源:Alibaba)
4.1.3 频繁作为 WHERE 查询条件的字段
某个字段在SELECT语句的 WHERE 条件中经常被使用到,那么就需要给这个字段创建索引了。尤其是在数据量大的情况下,创建普通索引就可以大幅提升数据查询的效率。
比如学生表数据表(含100万条数据),我们肯定会经常用到学生的学号和姓名等。
4.1.4 经常 GROUP BY 和 ORDER BY 的列
索引就是让数据按照某种顺序进行存储或检索,因此当我们使用 GROUP BY 对数据进行分组查询,或者使用ORDER BY对数据进行排序的时候,就需要对分组或者排序的字段进行索引 。如果待排序的列有多个,那么可以在这些列上建立组合索引 。
4.2 针对不创建索引
4.2.1 有大量重复数据的列上不要建立索引
举例1:要在 100 万行数据中查找其中的 50 万行(比如性别为男的数据),一旦创建了索引,你需要先访问 50 万次索引,然后再访问 50 万次数据表,这样加起来的开销比不使用索引可能还要大。
4.2.2 不建议用无序的值作为索引
例如身份证、UUID(在索引比较时需要转为ASCII,并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。
5. 索引优化
5.1 前缀索引优化
- 假设,你现在维护一个支持邮箱登录的系统,用户表是这么定义的
create table SUser(
ID bigint unsigned primary key,
email varchar(64),
name varchar(64),
...
)engine=innodb;
- 使用邮箱登录,所以业务代码中一定会出现类似于这样的语句
select f1, f2 from SUser where email='xxx';
- 如果email这个字段上没有索引,那么这个语句就只能做全表扫描。
-- 全部索引,索引字段为email整个字符串
alter table SUser add index index1(email);
- 你可以定义字符串的一部分作为索引
-- 前缀索引,6表示索引字段为email的前6位
alter table SUser add index index2(email(6));
5.2 覆盖索引优化
mysql> create table T (
-> ID int primary key,
-> k int NOT NULL DEFAULT 0,
-> s varchar(16) NOT NULL DEFAULT '',
-> index k(k))
-> engine=InnoDB;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from T;
+-----+---+----+
| ID | k | s |
+-----+---+----+
| 100 | 1 | aa |
| 200 | 2 | bb |
| 300 | 3 | cc |
| 500 | 5 | ee |
| 600 | 6 | ff |
| 700 | 7 | gg |
+-----+---+----+
6 rows in set (0.00 sec)
-
如果我执行 select *fromTwhere k between 3 and 5,需要执行几次树的搜索操作,会扫描多少行?
-
要避免回表过程,可以考虑在辅助索引 k 上创建覆盖索引,即包含查询结果所需的所有列。这样,在执行查询时就可以直接从覆盖索引中获取数据,而不必再次访问主键索引。
CREATE INDEX idx_k_covering ON T(k, s);
SELECT * FROM T WHERE k BETWEEN 3 AND 5;
使用情况 | 使用覆盖索引前 | 使用覆盖索引后 |
---|---|---|
回表次数 | 需要回表,执行额外的查找操作 | 不需要回表,直接从索引中获取所需数据 |
IO 操作 | 需要额外的IO操作,访问主键索引获取完整数据 | 不需要额外的IO操作,直接从覆盖索引获取完整数据 |
CPU 消耗 | 需要额外的CPU消耗,执行回表操作和数据合并 | 不需要额外的CPU消耗,直接从索引中获取完整数据 |
查询性能 | 性能较低,需要额外的IO和CPU消耗 | 性能较高,减少了IO和CPU消耗 |
存储空间 | 索引和数据存储在不同的位置,需要额外的存储空间 | 索引和数据存储在一起,不需要额外的存储空间 |