【MySQL】索引深入学习

最新推荐文章于 2023-07-11 16:42:54 发布

黑底白字cj

最新推荐文章于 2023-07-11 16:42:54 发布

阅读量155

点赞数

分类专栏： MySQL 文章标签： mysql 数据库索引

本文链接：https://blog.csdn.net/Alan_cqu_cj/article/details/112194771

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

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 索引概述