一、索引介绍
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是一种多路搜索树:
B-树的搜索,从根结点开始,对结点内的关键字(有序)序列进行二分查找,如果命中则结束,否则进入查询关键字所属范围的枝结点;重复,直到所对应的枝指针为空,或已经是叶子结点;
B+Tree索引
在MySQL数据库中,InnoDB存储引擎就是用B+Tree实现其索引结构。
B+树是B-树的变体,也是一种多路搜索树,在叶子节点上增加指向其他叶子的指针:
B+的搜索与B-树也基本相同,区别是B+树只有达到叶子结点才命中(B-树可以在非叶子结点命中),其性能也等价于在关键字全集做一次二分查找;在模糊搜索时,它可以在叶子节点上任意搜索
B*Tree
B*Tree是B+树的变体,在B+树的枝结点再增加指向兄弟的指针:
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');
注意:单独的>、
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';