MySQL 索引管理

一、索引介绍

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';
点赞

发表回复