定义
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可以快速访问数据库表中的特定信息。
- 索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。
- 索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。
索引操作
创建索引
CREATE INDEX indexName ON mytable(username(length));
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
创建表时直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
在已有表中添加索引
ALTER TABLE my_table ADD [UNIQUE] INDEX index_name(column_name);
或者
CREATE INDEX index_name ON my_table(column_name);
注意：
- 创建索引时如果是blob 和 text 类型，必须指定length；
- 索引需要占用磁盘空间，因此在创建索引时要考虑到磁盘空间是否足够；
- 创建索引时需要对表加锁，因此实际操作中需要在业务空闲期间进行；
删除索引
drop index_name on tablename;
alter TABLE tablename drop index name_index ;
查看索引
SHOW INDEX FROM tablename;
查看查询语句使用索引的情况
//explain 加查询语句
explain SELECT * FROM table_name WHERE column_1='123';
索引分类
主键索引
即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；
普通索引
仅加速查询 最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。
唯一索引
与普通索引类型不同的是：索引列的值必须唯一，但允许有空值（可以有null）。如果是组合索引，则列值的组合必须唯一。
CREATE UNIQUE INDEX indexName ON mytable(username(length))
全文索引
全文索引（FULLTEXT）仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。
组合索引
将几个列作为一条索引进行检索，使用最左匹配原则。
索引优缺点
优点
- 可以快速检索，减少I/O次数，加快检索速度；
- 根据索引分组和排序，可以加快分组和排序；
缺点
- 创建和维护索引需要耗费时间；
- 减慢写入速度；
- 增加磁盘空间占用，索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；
- 降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；
什么场景不适合创建索引
-
查询中很少使用列不应该创建索引。既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
-
只有很少数据值的列也不应该增加索引。因为本来结果集合就是相当于全表查询了，所以没有必要。例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比 例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
-
定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
-
当修改性能远远大于检索性能时，不应该创建索引。 这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因 此，当修改性能远远大于检索性能时，不应该创建索引。
-
不会出现在where条件中的字段不该建立索引。
索引失效条件
- 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描；
- 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select * from；
- mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描；
- is null,is not null也无法使用索引；
- 字符串不加单引号索引失效；
- 存储引擎不能使用索引中范围条件右边的列；
- like 以通配符开头（’%abc…’) MySQL索引会失效,变成全表扫描的操作；所以尽量把%写右边，
如果一定要在两边写% %，则可以使用覆盖索引； - 少用or，用它来连接时会索引失效；
建议
- 对于单键索引，尽量选择针对当前查询过滤性更好的索引
- 在选择组合索引的时候，当前查询中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
- 在选择组合索引的时候，尽量选择可以能够包含当前查询中的where子句中更多字段的索引
- 尽可能通过分析统计信息和调整查询的写法来达到选择合适索引的目的
- 使用短索引。对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
组合索引示例
建立的组合索引顺序：（a,b,c,d,e,f）
1、如果组合索引中的索引都在，则索引全部生效
2、如果组合索引中最左边的索引a不存在，那么索引都不生效
3、如果组合索引中最左边的索引a存在但没有使用全部的索引。首先按照建立索引的顺序排好，从a开始往后判断是否具有连续性（依据建立索引的顺序），如果直至最后都具有连续性，那么这些索引生效。如果在下一个节点处的索引连续性断掉，那么当前节点的索引和左边的索引生效。当前节点右边的索引不生效
对于第三种情况举个例子：
... where a =1,b=2,c=3,d=4,f=6 则当前节点的索引的是 d ， 所以索引 d和之前的索引生效，d之后的索引 f 不生效。
连续性断掉：
1、e不存在;
2、当前节点d使用的范围条件 如b>2
参考资料：
网友评论