索引的概念
索引用于快速找到与特定的列值相同的行。如果没有索引，MySQL必须从第一行开始，然后通过整个表读取找出相关行。表越大越费时。如果表对该列建立了索引，MySQL就能够迅速判断，寻求到该行，而无需看所有的数据的位置。这比顺序读取每一行快得多。
也就是说：
索引是用来解决查询相关问题的。当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引。你创建了合理的索引（实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录），MySQL就会在可以使用索引的时候（特定的操作），更快速地查询出你想要的数据。
索引的分类
从数据结构角度
- B+树索引(O(log(n)))
- hash索引：
a. 仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询
b. 其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引
c.只有Memory存储引擎显示支持hash索引 - FULLTEXT索引（现在MyISAM和InnoDB引擎都支持了）
- R-Tree索引（用于对GIS数据类型创建SPATIAL索引）
从物理存储角度
-
聚集索引（clustered index）
Innodb存储引擎表是索引组织表，即表中数据按主键顺序存放。而聚集索引就是按每张表的主键构造一颗B+树。并且叶节点存放整张表的行记录数据。每张表只能有一个聚集索引（一个主键）。
聚集索引的另一个好处是它对于主键的排序查找和范围的速度非常快。叶节点的数据就是我们要找的数据。 -
非聚集索引（non-clustered index）
辅助索引（也称非聚集索引）。叶级别不包含行的全部数据，
辅助索引的存在并不影响数据再聚集索引中的组织，因此一个表可以有多个辅助索引。
当通过辅助索引查找数据时，innodb会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键。然后再通过主键索引找到一行完整的数据。
从逻辑角度
- 主键索引：主键索引是一种特殊的唯一索引，不允许有空值
- 普通索引或者单列索引
- 多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
- 唯一索引或者非唯一索引
- 空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。
创建索引
创建索引，就是对某个表的一个或多个列上建立索引。官方的定义语句和参数解释非常清晰：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[index_type]
ON tbl_name (index_col_name,...)
[index_option]
[algorithm_option | lock_option] ...
index_col_name:
col_name [(length)] [ASC | DESC]
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
index_type:
USING {BTREE | HASH}
algorithm_option:
ALGORITHM [=] {DEFAULT|INPLACE|COPY}
lock_option:
LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
1、创建索引的时机
通常来说，应该在创建的表的同时创建所有的索引。这一点对于使用InnoDB引擎的表来说尤其重要（因为主键决定了记录在数据文件中的物理结构）。
a.创建表的同时创建索引 （详见：CREATE TABLE
）
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
-- 只看与创建索引相关的部分：create_definition
create_definition:
col_name column_definition
-- 主键索引（指定该列为主键，同时创建主键索引）
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)[index_option] ...
-- 普通索引（为该列创建普通索引）
| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)[index_option] ...
-- 唯一索引（指定该列为唯一键，同时创建唯一索引）
| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY][index_name] [index_type] (index_col_name,...)[index_option] ...
-- 全文索引（为该列创建全文索引）
| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)[index_option] ...
-- 外键索引（指定该列为外键，同时为该列创建外键索引）
| [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
b.已创建的表添加索引（详见： ALTER TABLE
）
-- 普通索引（INDEX或者KEY都会创建索引）
ALTER TABLE tbl_name
ADD {INDEX|KEY} [index_name][index_type] (index_col_name,...) [index_option] ...
-- 主键索引（虽然是添加主键，但是同时也会创建索引）
ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)[index_option] ...
-- 唯一索引
ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name][index_type] (index_col_name,...) [index_option] ...
-- 全文索引
ALTER TABLE tbl_name
ADD FULLTEXT [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
-- 空间索引
ALTER TABLE tbl_name
ADD SPATIAL [INDEX|KEY] [index_name](index_col_name,...) [index_option] ...
-- 外键索引（虽然是添加外键，但是同时也会创建索引）
ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
以上的ALTER语句后面都还可以加入：
| ALGORITHM [=] {DEFAULT|INPLACE|COPY}
| LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
从这里可以看出，并不是只有出现INDEX这个词的时候，才会创建索引。索引的分类也可以从上面总结出来。值得注意的是主键索引也是一种UNIQUE类型的索引，不仅不能重复，也不能含有NULL值。
从上面也可以看出，创建索引的方式有很多种，第一种就是在创建表的时候直接创建索引，第二种就是针对已创建的表：通过ALERT TABLE命令或者直接用CREATE INDEX都是可以的。
2、索引分类
-
UNIQUE
使用这个参数，意味着创建了唯一索引：索引中所有的值都不能相同，但可以放入多个NULL值（所有的引擎都支持）。如果指定了前缀长度，则必须保证在这个长度范围内的列值都不相同。 -
FULLTEXT
使用这个参数，意味着创建了全文索引。但只有InnoDB和MyISAM引擎支持，并且只能在CHAR、VARCHAR、TEXT类型的列上创建该索引。此时，是针对全部列值进行索引，即使定义了前缀长度也是无效的。 -
SPATIAL
使用这个参数，意味着创建了空间索引。
a.仅适用于只有InnoDB和MyISAM引擎，其他引擎使用该索引会导致错误；
b.列值不能为NULL；
c.不允许指定前缀长度，也就是说会使用整个列值进行索引。 -
索引的性质（INDEX, UNIQUE, or PRIMARY KEY，除了SPATIAL）
a.所有支持空间类型列的存储引擎都支持（ARCHIVE存储引擎除外）
b.索引的列值都可为NULL（主键索引PRIMARY KEY除外）
c.索引类型因存储引擎而已，目前使用的是B-tree。
d.You can add an index on a column that can have NULL values only for InnoDB, MyISAM, and MEMORY tables.
e.You can add an index on a BLOB or TEXT column only for using the InnoDB and MyISAM tables.
3、参数index_col_name
index_col_name:
col_name [(length)] [ASC | DESC]
-
如果指定了多个
index_col_name，则意味着创建了联合索引，最多含有16列。 -
[ASC | DESC]
虽然可以指定，但目前版本（截止到5.7.2）只是被解析，最后会被忽略：索引值总是以升序排列的。 -
[(length)]：指定前缀长度
对字符串类型的列，可以只使用列值的一部分来作为来进行索引。这么做的优点是：如果在这个范围（前缀长度）内列值通常都不相同的话，这样做不会降低查询速度（相对使用整个列值而言），而且这样会使索引文件更小，节省磁盘空间，甚至可能会加快插入操作的速度。
对于VARCHAR、CHAR、BINARY和VARBINARY类型的列来说，这是可选的，但是对于BLOB和TEXT类型的列，必须指定前缀长度。
这个长度是以字节来衡量的。在创建表、修改表的语句中，对非二进制字符串类型（VARCHAR、CHAR、TEXT）来说，[(length)]是指字符数；而对二进制类型的字符串类型（BINARY、VARBINARY、BLOB）而言，[(length)]则是指字节数。当为一个使用了多字节字符集的非二进制字符串类型的列指定前缀长度的时候，尤其要注意。
对于空间数据类的列来说，不能指定前缀长度。
不同存储引擎允许的最大前缀长度不同。
4、参数index_option
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
-
KEY_BLOCK_SIZE [=] value
适用于MyISAM。 -
index_type：指明该索引内部使用的数据结构类型：
| 存储引擎 | 允许的类型 |
|---|---|
| InnoDB | BTREE |
| MyISAM | BTREE |
| MEMORY/HEAP | HASH, BTREE |
| NDB | HASH, BTREE |
可以看出，Mysql索引方式主要有两种结构：BTREE和HASH。
HASH：将数据的索引以hash形式组织起来，因此当查找某一条记录的时候，速度非常快。不支持范围查找和排序等功能.
BTREE：数据结构以平衡树的形式来组织。因为是树型结构，所以更适合用来处理排序，范围查找等功能。相对HASH索引，BTREE在查找单条记录的速度虽然比不上HASH索引，但是更适合排序等操作，而且大部分都是范围查找的查询操作。
注意：不能为FULLTEXT INDEX or SPATIAL INDEX指定类型，FULLTEXT INDEX的实现取决于存储引擎，SPATIAL INDEX是使用 R-tree。
更详情见下表：
storage_engine_indeex.png
-
WITH PARSER parser_name
FULLTEXT 索引时才可以使用该参数，指定解析器插件。InnoDB和MyISAM支持解析器插件。 -
COMMENT 'string'
为该索引添加注释，最多为1024个字符。
4、参数ALGORITHM 和 LOCK
这两个参数可以影响表的复制方法和该表的读写并发级别。
删除索引
DROP INDEX index_name ON tbl_name
ALTER TABLE tbl_name DROP INDEX index_name
-- 这种是删除主键，也会删除主键索引
ALTER TABLE tbl_name DROP PRIMARY KEY
使用索引
当执行以下操作的时候，MySQL会使用索引（前提是你为相应字段创建了索引）：
-
快速找出符合where条件的行。
-
根据条件排除某些列。如果有多个索引，MySQL 通常会使用可以找出最少行的的那个索引。
-
如果一个表有联合索引（multiple-column index），MySQL优化时会选择最左边的索引列。比如，你创建了含有三个列
(col1, col2, col3)的联合索引，那么在查询中(col1)、(col1, col2)和(col1, col2, col3)时都会使用索引，其他情况则不会。后面会解释。 -
使用
join关键字从其他表中获取记录的时候。
如果它们（这里的它们是指join语句中on后面的等号两边的字段）拥有相同的类型和大小，那么MySQL使用索引的效率会更高。在这种情况下，VARCHAR和CHAR如果大小相同则认为他们是一样的，比如 VARCHAR(10) 和 CHAR(10)，而VARCHAR(10) 和 CHAR(15)则不同。
如果比较字符串类型的列，那么这两个列应该使用相同的字符集，否则不会使用索引。 -
找出某个创建了索引的列的最大或最小值。
-
根据一个创建了索引的列来排序或分组
对于数据量较小的表，或者需要访问大部分数据的查询而言，索引并不重要。
联合索引的最左原则
假如你创建了这样一张表，同时创建了联合索引：
CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name) -- 创建联合索引
);
那么，在下面的查询语句中会使用索引：
SELECT * FROM test WHERE last_name='Widenius';
SELECT * FROM test
WHERE last_name='Widenius' AND first_name='Michael';
SELECT * FROM test
WHERE last_name='Widenius'
AND (first_name='Michael' OR first_name='Monty');
SELECT * FROM test
WHERE last_name='Widenius'
AND first_name >='M' AND first_name < 'N';
而下面的两条查询语句则不会使用索引：
SELECT * FROM test WHERE first_name='Michael';
SELECT * FROM test
WHERE last_name='Widenius' OR first_name='Michael';
再来举一个例子：
SELECT * FROM tbl_name WHERE col1=val1; -- a
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; --b
SELECT * FROM tbl_name WHERE col2=val2; --c
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3; --d
--
假如联合索引为 (col1, col2, col3)，只有a和b会使用索引。
虽然c和d的查询中也包含了索引列，但是 (col2) 和(col2, col3) 不是 (col1, col2, col3)的最左前缀。
验证索引的使用
使用命令EXPLAIN。待补充。 Optimizing Queries with EXPLAIN
索引选择
索引虽然可以加快查询速度，但是也会带来问题：索引文件会使磁盘空间的开销变大，修改操作由于要同时去修改索引文件所以会导致修改时性能变差。
该部分引用自：MySQL索引入门简述和mysql索引需要了解的几个注意
索引选择原则
-
较频繁的作为查询条件的字段应该创建索引
-
选择性太低的字段不适合单独创建索引，即使频繁作为查询条件。选择性：不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：
Index Selectivity = Cardinality / #T。
选择性取值范围为(0, 1]，选择性越高的索引价值越大。 -
更新非常频繁的字段不适合创建索引
-
不会出现在 WHERE 子句中的字段不该创建索引
-
使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，提升查询速度；
参考
20180101--添加MySQL索引的分类
网友评论