1 知识点回顾
为什么使用索引
如图,这是一张无索引的表,当我们想去检索其中一条数据的时候,需要一行一行去匹配,即全表扫描。
然而,当有了索引之后,可以从索引开始的位置线性扫描/二分搜索,快速找到匹配项,加快了检索速度。
- 优点:加快检索速度(但是要确保索引生效)
- 缺点:减低了和写相关操作的速度,因为不仅是写数据,还要写索引
索引的分类
- 唯一索引(unique index):对单列,不允许重复值;对多列,不允许重复组合值
- 普通索引(index):允许重复值
- 主键索引(primary key):可以看成是特殊的唯一索引,一张表只能有1个
- 组合索引:将数据库表中的多个字段联合起来作为一个组合索引,遵循最左前缀规则
- FULLTEXT: FULLTEXT索引仅可用于 MyISAM 表
- HASH: 由于HASH的唯一(几乎100%的唯一)及类似键值对的形式,很适合作为索引
- …还有一些不常见的暂未列举
唯一索引和primary key的区别
- primary key 每个表只能有一个,unique key 可以有多个
- primary key 不能存在null, unique key 可以存在多个null(因为null不与任何值相等)
创建/删除索引sql
# 普通索引
ALTER TABLE tb_name ADD INDEX index_name (index_columns)
# 唯一索引
ALTER TABLE tb_name ADD UNIQUE index_name (index_columns)
# 主键索引
ALTER TABLE tb_name ADD PRIMARY KEY index_name (index_columns)
# FULLTEXT
ALTER TABLE tb_name ADD FULLTEXT index_name (index_columns)
# SPATIAL
ALTER TABLE tb_name ADD SPATIAL index_name (index_columns)
# 删除普通索引
ALTER TABLE tb_name DROP INDEX index_name
# 删除常见索引
ALTER TABLE tb_name DROP PRIMARY KEY index_name
最左前缀原则
使用于组合索引,假设表A 设有联合索引,(a,b,c)则,当检索条件中出现(a,), (a,b), (a,b,c) 时,会使用到索引
(ps:没有索引的情况下会全表扫描)
2 MySQL 存储引擎 – MyISAM和InnoDB
MySQL 中 InnoDB和 MyISAM引擎中的 B-tree 索引使用的是 B+tree (即每一个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点的范围遍历,并且除叶子节点外其他节点只存储键值和指针)
MyISAM引擎中leaf node存储的内容:
- 主键索引 :仅仅存储行指针;
- 二级索引:存储的也仅仅是行指针;
InnoDB引擎中leaf node存储的内容
- 主键索引(聚簇索引) :聚集索引存储完整的数据(整行数据)
- 二级索引:存储索引列值+主键信息
引用一张网图,了解下原理
ps: 针对与聚簇索引回表的概念
MySQL回表:
- 如果语句是 select * from User where id=3,即主键查询方式,则只需要搜索 主键索引树。
- 如果语句是 select * from User where uid=23,即普通索引查询方式,则需要先搜索 普通索引树,得到其对应的主键值为 3,再到主键索引树搜索一次。这个过程称为回表
3 索引挑选原则与优化
选索引原则
-
用于搜索,排序或分组的列创建索引,即经常出现在where,order_by,group_by 语句中的表字段
-
考虑这一列的重复值个数,重复值越多,索引越没用。假设这一列的值都是相同的,那么索引也就失去了“索引”的意义
-
索引尽量选择短小的数据类型。可以加快速度
-
如果是以字段串作为索引,可以指定索引长度,理由同上
-
当使用组合索引时候,注意最左前缀原则
利用explain检验优化
explain 的关键字的作用
- 检验某条查询语句是否用到了索引
- 验证增加索引能否有助于优化程序,更有效地执行某条语句
举个栗子
# 这里有张表
| persons | CREATE TABLE `persons` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`last_name` varchar(255) DEFAULT NULL,
`first_name` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |
# 表里有3条数据
mysql> select * from persons;
+----+-----------+------------+---------+---------+
| id | last_name | first_name | address | city |
+----+-----------+------------+---------+---------+
| 1 | re_update | j | haidian | beijing |
| 2 | new | record | haidian | beijing |
| 3 | another | record | haidian | beijing |
+----+-----------+------------+---------+---------+
第一个查询
mysql> explain select * from persons;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | persons | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
这里重点关注一下 possible_keys 和 key ,前者说明这条查询语句可能用到的索引, 后者是说该条查询语句实际用到的索引。NULL表示没有用到。
我们通过这个方法可判断出查询语句是否编写合理,检验索引是否被成功使用。
更多的expline解释可参考 explaine用法介绍
什么情况下会使用索引
会使用
- 对一个键码使用>, >=, =, <, <=, IF NULL和BETWEEN
SELECT * FROM table_name WHERE key_part1=1 and key_part2 > 5;
SELECT * FROM table_name WHERE key_part1 IS NULL;
- 当使用不以通配符开始的LIKE
SELECT * FROM table_name WHERE key_part1 LIKE 'jani%'
- 在进行联结时从另一个表中提取行时
SELECT * from t1,t2 where t1.col=t2.key_part
- 找出指定索引的MAX()或MIN()值
SELECT MIN(key_part2),MAX(key_part2) FROM table_name where key_part1=10
- 一个键码的前缀使用ORDER BY或GROUP BY
SELECT * FROM foo ORDER BY key_part1,key_part2,key_part3
- 在所有用在查询中的列是键码的一部分时间
SELECT key_part3 FROM table_name WHERE key_part1=1
- 索引列需要在表达式中单独出现
where mycol * 2 < 4 # 不会使用mycol索引
where mycol < 4 /2 # 会使用mycol索引
不会使用索引
- MySQL能估计出它扫描整张表更快时
- select * from xxx
- 以%开头的模糊查询,如 like ‘%cc’
- 数据类型出现隐式转换时
- 复合索引不符合最左前缀原则时候
- 对索引列进行运算,或者函数操作
参考
1 MySQL 技术内幕 第五版
2 explaine用法介绍
3 mysql btree 索引概述