HEBIN博客


一个专注于IT原创技术教程分享的博客

MySQL 索引管理

2020年6月15日 0条评论 3.7k次阅读 1人点赞 HEBIN
文章目录
一、索引介绍
1.什么是索引
2.索引的数据结构
3.索引分类
二、索引管理
1.添加索引
2.查看索引
3.删除索引
4.前缀索引和联合索引
三、EXPLAIN详解
1.EXPLAIN的使用方法
2.EXPLAIN命令字段说明
3.查询数据的方式
四、建立索引的原则（规范）
1.原则
2.查询时不走索引原因

一、索引介绍

1.什么是索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

比如汉语字典的目录页（索引），我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

2.索引的数据结构

索引结构 说明
BTree BTree索引（B-Tree、B+TREE、B*TREE）
HASH HASH索引 （MEMORY存储引擎支持，MyISAM和InnoDB存储引擎不支持）
FULLTEXT 全文索引（只支持MyISAM存储引擎）
RTREE R树索引

B-Tree索引

B-Tree是一种多路搜索树：

MySQL_B-Tree.jpg

B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的枝结点；重复，直到所对应的枝指针为空，或已经是叶子结点；

B+Tree索引

在MySQL数据库中，InnoDB存储引擎就是用B+Tree实现其索引结构。

B+树是B-树的变体，也是一种多路搜索树，在叶子节点上增加指向其他叶子的指针：

MySQL_B_Tree.jpg

B+的搜索与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树可以在非叶子结点命中），其性能也等价于在关键字全集做一次二分查找；在模糊搜索时，它可以在叶子节点上任意搜索

B*Tree

B*Tree是B+树的变体，在B+树的枝结点再增加指向兄弟的指针：

MySQL_B*Tree.jpg

3.索引分类

常见的索引类型有：普通索引、唯一索引、主键索引、全文索引、前缀索引、联合索引

二、索引管理

1.添加索引

1.1 普通索引

  • 创建索引
CREATE INDEX index_name ON tbl_name(column_name);
  • 添加索引
ALTER TABLE tbl_name ADD INDEX index_name(column_name);
  • 建表时指定
CREATE TABLE tbl1(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX name_key(username)  
);

1.2 唯一键索引

  • 创建唯一索引
CREATE UNIQUE INDEX index_name ON tbl_name(column_name);
  • 添加索引
ALTER TABLE tbl_name ADD UNIQUE KEY index_name(column_name);
  • 建表时指定
CREATE TABLE tbl2(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE KEY name_key(username)  
);

==注意：==创建唯一索引时，必须保证该列不能有重复值。

1.3 主键索引

  • 建表时指定
CREATE TABLE tbl3(  
ID INT NOT NULL PRIMARY KEY,   
username VARCHAR(16) NOT NULL,    
);
  • 添加主键索引
ALTER TABLE tbl_name ADD PRIMARY KEY index_name(column_name);

==注意：==创建主键索引时，必须保证该列不能有重复值。

1.4 全文索引

  • 创建索引
CREATE FULLTEXT INDEX index_name ON tbl_name(colunm_name);
  • 添加全文索引
ALTER TABLE tbl_name ADD FULLTEXT INDEX index_name(colunm_name);
  • 建表时指定
CREATE TABLE tbl4(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL, 
content text,
FULLTEXT INDEX content_key(content)  
);
  • 全文索引查询语句格式

全文索引的查询语句与常规不一样，要按照如下的方式查询

SELECT * FROM tbl6 WHERE MATCH(content) AGAINST('关键字');

2.查看索引

  • 查看表结构方式查看
DESC tbl_name;
  • 查看索引详细信息
SHOW INDEX FROM tbl_name;

3.删除索引

  • 使用DROP直接删除索引
DROP INDEX index_name ON tbl_name;
  • 以修改表得形式删除索引
ALTER TABLE tbl_name DROP INDEX index_name;

4.前缀索引和联合索引

4.1 前缀索引

根据字段的前N个字符建立索引

ALTER TABLE tbl4 ADD INDEX qz_index(username(10));

查看前缀索引

mysql> SHOW INDEX FROM tbl4;
+----------+
| Sub_part |
+----------+
|       10 |
|     NULL |
+----------+

注意查看结构中的Sub_part字段，这个字段表示前缀索引的长度。

4.2 联合索引

多个字段建立一个索引，把最常用来做为条件查询的列放在最前面

创建联合索引

  • 新建一个表时指定
CREATE TABLE tbl5(
ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(16) NOT NULL, 
Sex ENUM('man','woman') NOT NULL,
Age TINYINT NOT NULL,
Money INT NOT NULL,
Birthday DATETIME DEFAULT NOW(),
Content TEXT,
INDEX idx_key(Sex,Age,Money)
)
  • 创建联合索引
CREATE INDEX inx_key ON tbl5(Sex,Age,Money);
  • 添加联合索引
ALTER TABLE tbl5 ADD INDEX idx_key(Sex,Age,Money);
  • 查询联合索引
mysql> SHOW INDEX FROM tbl5;
+----------+--------------+-------------+
| Key_name | Seq_in_index | Column_name |
+----------+--------------+-------------+
| idx_key  |            1 | Sex         |
| idx_key  |            2 | Age         |
| idx_key  |            3 | Money       |
+----------+--------------+-------------+

使用联合索引

使用联和索引查询数据

SELECT * FROM tbl5 WHERE Sex='woman' AND Age=22 AND Money>1000000;

验证是否使用索引查询了，查看type字段

mysql> EXPLAIN SELECT * FROM tbl5 WHERE Sex='woman' AND Age=22 AND Money>1000000;

==注意：==

1.要把Seq_in_index的顺序为1的字段作为条件放在第一位。

2.如果不放在第一位则需要写多个索引字段作为条件。

3.不能使用符号<>、OR、!=

三、EXPLAIN详解

1.EXPLAIN的使用方法

mysql> EXPLAIN SELECT Sex,Age,Money FROM tbl5 WHERE ID=1;

2.EXPLAIN命令字段说明

字段 注释
id 执行sql的id，值越大越先执行
select_type 查询类型
table 查询的表
type 索引扫描类型
possible_keys 可能用到的索引
key 实际上用到的索引
key_len 索引长度（可以使用前缀索引控制），越小越好
ref 记录查询级别在ref之上的
rows 查询数据的数量，查询的内容越多，越不准确（越小越好）
Extra 使用的方法

3.查询数据的方式

3.1 全表扫描

  • 在explain语句结果中type为ALL

  • 什么时候出现全表扫描

1.业务需要获取所有的数据
2.不走索引导致的全表扫描
   2.1 没有索引
   2.2 索引创建有问题
   2.3 查询语有问题

3.2.常见的索引扫描类型

index : 全索引扫描，index与ALL区别为index类型只遍历索引树。

mysql> CREATE INDEX pt_idx ON city(District);
mysql> EXPLAIN SELECT District FROM city;

range : 索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询 。

mysql> ALTER TABLE city ADD INDEX popul_idx(Population);
mysql> EXPLAIN SELECT * FROM city WHERE Population > 1000000;

ref : 使用非唯一索引扫描或者唯一索引的前缀扫描，返回单条记录，常出现在关联查询中。（精确查找）

mysql> EXPLAIN SELECT * FROM city WHERE Population = 1000000;

eq_ref : 类似ref，区别就在使用的索引是唯一索引，使用主键的关联查询。（精确查找）

mysql> EXPLAIN SELECT country.Name,city.Name,city.Population FROM country JOIN city ON city.CountryCode=country.Code WHERE city.Population < 100;

const、system : 精确查找,当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。（查找的条件是主键索引）,system是const类型的特例，当查询的表只有一行的情况下，使用system

mysql> EXPLAIN SELECT * FROM city WHERE id=1000;

NULL : MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

mysql> EXPLAIN SELECT * FROM city WHERE id=100000000;
mysql> EXPLAIN SELECT MIN(Population) FROM city;

四、建立索引的原则（规范）

为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。

1.原则

  • 选择唯一索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

  • 为经常需要排序、分组和联合操作的字段建立索引

  • 为常作为查询条件的字段建立索引

  • 尽量使用前缀索引

  • 限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
  • 删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

2.查询时不走索引原因

2.1 没有查询条件，或者查询条件没有建立索引

  • 全表扫码
SELECT * FROM city;
SELECT * FROM city WHERE 1=1;

2.2 查询结果集是原表中的大部分数据，应该是25％以上

mysql> EXPLAIN SELECT * FROM city WHERE Population>3000 ORDER BY Population;
  • 避免不走索引

如果业务允许，可以使用limit控制。(避免不走索引)

mysql> EXPLAIN SELECT * FROM city WHERE Population>3000 ORDER BY Population LIMIT 1000;

结合业务判断，有没有更好的方式。如果没有更好的改写方案就尽量不要在mysql存放这个数据了，放到redis里面

2.3 索引本身失效，统计数据不真实

索引有自我维护的能力。
对于表内容变化比较频繁的情况下，有可能会出现索引失效。
重建索引就可以解决

*2.4 查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，等)**

  • 错误的写法（不走索引）
SELECT * FROM city WHERE id-1=10;
  • 正确写法（走索引）
SELECT * FROM city WHERE id=10;

2.5 隐式转换导致索引失效。这一点应当引起重视，也是开发中经常会犯的错误

常见的就是把varchar类型字段的值写成int类型的，导致不走索引。

#建表
mysql> create table test(id int, name varchar(10), tel varchar(10));

#插入数据
mysql> insert into test values(1,'abc','110'),(2,'acd',119),(3,'aef',120);

#建索引
mysql> alter table test add unique key tel_key(tel);

#不走索引：
    mysql> explain select * from test where tel=120;
#走索引：
    mysql> explain select * from test where tel='120';

2.6 <>、!=、NOT IN不走索引

mysql> EXPLAIN SELECT * FROM city WHERE Population <> 100000;
mysql> EXPLAIN SELECT * FROM city WHERE CountryCode NOT IN ('CHN','USA');

注意：单独的>、<、IN有可能走索引，也有可能不走，和结果集有关，尽量结合业务添加LIMIT。OR和IN尽量改成UNION。

EXPLAIN SELECT * FROM city WHERE CountryCode IN ('CHN','USA');
#改写成
EXPLAIN SELECT * FROM city WHERE CountryCode = 'CHN'
UNION ALL
SELECT * FROM city WHERE CountryCode = 'USA';

2.7 LIKE "%_" 百分号在最前面不走索引

#不走range索引扫描
EXPLAIN SELECT * FROM city WHERE Population LIKE '%00';

#走索引
EXPLAIN SELECT * FROM city WHERE Population LIKE '31%';

2.8 单独引用联合索引里非第一位置的索引列

CREATE TABLE t1 (id INT,NAME VARCHAR(20),age INT ,sex ENUM('m','f'),money INT);
ALTER TABLE t1 ADD INDEX t1_idx(money,age,sex);
DESC t1
SHOW INDEX FROM t1
#走索引的情况测试
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30  AND sex='m';
#部分走索引
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30 AND age=30;
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE money=30  AND sex='m'; 
#不走索引
EXPLAIN SELECT  NAME,age,sex,money FROM t1 WHERE age=20
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM t1 WHERE sex='m';
本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可
点赞
最后编辑：2020年7月23日

发表回复