本文深入解析MySQL索引的本质、种类及创建方法，并提供实用案例帮助理解索引如何提高查询效率。
MySQL 索引
参考资料：
https://www.cnblogs.com/chenshishuo/p/5030029.html
https://segmentfault.com/a/1190000003072424
索引的本质
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。
|
数据库查询是数据库的最主要功能之一。 我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。 最基本的查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数据量很大时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查找算法，例如二分查找（binary search）、二叉树查找（binary tree search）等。 如果稍微分析一下会发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。 |
单列索引(普通索引、唯一索引、主键索引)
组合索引
全文索引
本文案例使用的表：
CREATE TABLE `award` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
`nickname` varchar(12) NOT NULL DEFAULT '' COMMENT '用户昵称',
`account` varchar(12) NOT NULL DEFAULT '' COMMENT '帐号',
`password` char(32) NOT NULL DEFAULT '' COMMENT '密码',
`message` varchar(255) NOT NULL DEFAULT '' COMMENT '获奖信息',
`created_time` int(11) NOT NULL DEFAULT 0 COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='获奖信息表';
我往数据库中随便插入了50万条数据，以供测试使用。
award表中一共有50万数据，其中一条数据'account = 账号-499998'，如果想要拿到这条数据，需要的SQL是：
SELECT * FROM award WHERE account = '账号-499998';
一般情况，没有建立索引的时候，mysql需要扫描全表以及扫描50万数据找到这条数据。
如果我在account字段上，建立索引，那么mysql只需要一行数据就能为我们找到'account = 账号-499998'这条数据。
索引创建
1. 单列索引
普通索引
这个是最基本的索引
格式：
CREATE INDEX '列索引名称' ON 表名(列名);
mysql> CREATE INDEX account_index ON award(account);
Query OK, 500000 rows affected (7.74 sec)
Records: 500000 Duplicates: 0 Warnings: 0
在没有创建account索引之前，查询时间要这么久：
创建索引之后：
唯一索引
唯一索引与普通索引类似，但是不同的是，唯一索引要求所有列的值必须是唯一的。
这一点同主键索引一样，但唯一索引允许有空值。
格式：
CREATE UNIQUE INDEX '列索引名称' ON 表名(列名);
mysql> CREATE UNIQUE INDEX password_unique_index ON award(password);
Query OK, 500000 rows affected (11.43 sec)
Records: 500000 Duplicates: 0 Warnings: 0
在没创建索引之前，查询时间：
创建索引之后：
主键索引
(在B-Tree中的InnoDB引擎中，主键索引起到了至关重要的地位)
主键索引的规则：
int 型 优于 varchar，一般在建表的时候创建。
最好是与表的其他字段不相关的列或者是业务不相关的列。
一般会设置为int，而且是AUTO_INCREMENT自增类型的
上面的建表语句，就有主键，我们写个语句看下：
查询数据库中所有表的主键以及数量：
SELECT
t.TABLE_NAME,
t.CONSTRAINT_TYPE,
c.COLUMN_NAME,
c.ORDINAL_POSITION
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c,
information_schema.TABLES AS ts
WHERE
t.TABLE_NAME = c.TABLE_NAME
-- AND t.TABLE_SCHEMA = 数据库名称
AND t.CONSTRAINT_TYPE = 'PRIMARY KEY';
我们随便查一条：
2. 组合索引
一个表中包含多个单列索引不代表是组合索引；
通俗一些讲，组合索引是：包含多个字段(列名)但是只有一个索引名称。
就像是一个Map<String , List<String>>结构一样。
key：只有一个，就是索引名称。
value：就是多个列名。
格式：
CREATE INDEX 组合索引名 ON 表名(列名1,列名2);
mysql> CREATE INDEX account_password_index ON award(account,password);
Query OK, 500000 rows affected (8.05 sec)
Records: 500000 Duplicates: 0 Warnings: 0
在使用查询的时候，遵循mysql组合索引的'最左前缀'。
下面我们分析什么是'最左前缀'？以及索引where时的条件要按照建立索引的时候字段的排序方式。
1. 索引最左列开始查询(组合索引)
例如：组合索引index('c1','c2','c3')；
where c2 = 'aaa' 不使用索引
where c2 = 'aaa' and c3 = 'sss'不使用索引
2. 查询中某个列有范围查询，则其右边的所有列都无法使用查询(多列查询)
where c1 = 'xxx' and c2 like 'aa%' and c3 = 'sss'
该查询只会使用索引中的前两列，因为立刻是范围查询
3.不能跳过某个字段进行查询，这样利用不到索引
比如：
explain select * from `award` where nickname > 'rSUQFzpkDz3R' and account = 'DYxJoqZq2rd7' and created_time = 1449567822;
因为我的索引是(nickname, account, created_time)；
如果第一个字段出现返回符号的查找，那么将不会使用到索引。
如果我第一个字段使用等值查询，第二第三个字段使用返回符号查询，那么他会利用索引，利用的索引是(nickname)
3. 全文索引
文本字段上(text)如果建立的是普通索引，那么只有对文本的字段内容 前面的字符进行索引;
其字符大小根据索引建立索引时声明的大小来规定的。
如果文本中出现多个一样的字符，而且需要查找的话，那么其条件只能是where column lick '%xxxx%'，
这样做会让索引失效。
这个时候全文索引就起到作用了...
格式：
ALTER TABLE 'tablename' ADD FULLTEXT(column1,column2);
有了全文索引，就可以用SELECT 查询命令去检索那些包含着一个或多个给定单词的数据记录了。
ELECT * FROM tablename WHERE MATCH(column1, column2) AGAINST(‘xxx′, ‘sss′, ‘ddd′)
4. 查看索引
show index from award;
show keys from award;
显示之后，我们看下字段是什么意思。
|
Table |
表名 |
|
Non_unique |
如果索引不能包括重复词，则为0。如果可以，则为1 |
|
Key_name |
索引的名称 |
|
Seq_in_index |
索引中的列序列号，从1开始 |
|
Column_name |
列名称 |
|
Collation |
列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类） |
|
Cardinality |
索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机 会就越大 |
|
Sub_part |
如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL |
|
Packed |
指示关键字如何被压缩。如果没有被压缩，则为NULL |
|
NULL |
如果列含有NULL，则含有YES。如果没有，则该列含有NO |
|
Index_type |
用过的索引方法（BTREE, FULLTEXT, HASH, RTREE） |
|
Comment |
多种评注 |
5. 删除索引
DROP INDEX index_name ON talbe_name
6. 设计索引的原则
搜索的索引列， 不一定是所要选择的列 。
最适合索引的列是出现在 WHERE子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列
使用惟一索引。考虑某列中值的分布。
索引的列的基数越大，索引的效果越好。例如，存放出生日期的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“ M”和“F”，则对此列进行索引没有多大用处，因为不管搜索哪个值，都会得出大约一半的行
使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。
例如，如果有一个 CHAR(200)列，如果在前 10 个或 20 个字符内，多数值是惟一的， 那么就不要对整个列进行索引。
对前 10 个或 20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。
较小的索引涉及的磁盘 IO 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值。
因此，MySQL 也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性
利用最左前缀。
在创建一个 n 列的索引时， 实际是创建了 MySQL 可利用的 n 个索引。
多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
不要过度索引。
不要以为索引“越多越好”，什么东西都用索引是错误的。
每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。
如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。
此外，MySQL在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。
索引太多，也可能会使 MySQL 选择不到所要使用的最好索引。 只保持所需的索引有利于查询优化
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
