mysql管理索引_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是一种多路搜索树：

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