MySQL 索引测试
- 1 索引类型
- 1 主键索引（PRIMARY KEY）
- 1 添加索引
- 2 删除索引
- 2 常规索引（INDEX/KEY）
- 1 单一索引
- 2 组合索引
- 3 唯一索引（UNIQUE KEY）
- 4 全文索引（FULL TEXT）
- 1 创建方式
- 2 使用方式
- 5 空间索引（SPATIAL）
- 2 索引方式
- 1 BTREE
- 2 HASH
- 1 仅支持 "="，"IN" 和 "<=>" 精确查询，不能使用范围查询
- 2 不支持排序
- 3 在任何时候都不能避免表扫描
- 4 检索效率高
- 5 只有Memory引擎支持显式的Hash索引
- 3 RTREE
- 3 索引使用注意事项
- 1 不要使用NULL
- 2 使用短索引
- 3 索引列排序
- 4 Like 语句
- 5 不使用 NOT IN和<>操作
1 索引类型
1 主键索引（PRIMARY KEY）
名称 | 作用 |
主键索引（PRIMARY KEY） | 主键索引就是专门为主键字段创建的索引，不允许值重复或者值为空，且一张表只能有一个主键索引，它可以提高查询效率，并提供唯一性约束。 |
名称 | 作用 |
id | 是表示查询中执行 select 子句或操作表的顺序的数字。ID相同（可以认为是同一组）从上往下执行，子查询ID值会递增，ID值越大越先被执行。 |
select_type | 表示查询中每个select子句的类型（简单 OR复杂） |
table | 表明 |
partitions | |
type | all < index < range < ref < eq_ref < const/system < null |
possible_keys | 指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。 |
key | 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。 |
key_len | 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 |
ref | 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。 |
rows | 表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。 |
filtered | |
Extra | 包含不适合在其他列中显示但十分重要的额外信息 |
select_type | 作用 |
SIMPLE | 查询中不包含子查询或者UNION。 |
PRIMARY | 查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY 。 |
SUBQUERY | 在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY。 |
DERIVED | 在FROM列表中包含的子查询被标记为：DERIVED（衍生）。 |
DERIVED | 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在 FROM子句的子查询中，外层SELECT将被标记为：DERIVED。 |
UNION RESULT | 从UNION表获取结果的SELECT被标记为：UNION RESULT |
type | 作用 |
all | Full Table Scan， MySQL将遍历全表以找到匹配的行. |
index | Full Index Scan，index与ALL区别为index类型只遍历索引树 |
range | 索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询 |
ref | 非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找 |
eq_ref | 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描 |
const/system | 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量 |
null | MySQL在优化过程中分解语句，执行时甚至不用访问表或索引 |
Extra | 作用 |
Using index | 该值表示相应的select操作中使用了覆盖索引（Covering Index）。 |
Using where | 表示MySQL服务器在存储引擎受到记录后进行“后过滤”（Post-filter），如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集。 |
Using temporary | 表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。 |
1 添加索引
alter table `student` add primary key `id` (`id`);explain select * from student where id = 1;id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
1 | SIMPLE | student | ALL | PRIMARY | 18 | 10 | Using where |
2 删除索引
# 如果主键是自增的需要先删除自增（修改字段类型）
alter table `student` drop primary key;explain select * from student where id = 1;id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
1 | SIMPLE | student | ALL | 18 | 10 | Using where |
2 常规索引（INDEX/KEY）
名称 | 作用 |
常规索引（INDEX/KEY） | 常规索引是 MySQL 中最基本的索引类型，它没有数据类型的限制，允许在定义索引的列中插入重复值和空值，能加快系统对数据的访问速度。 |
1 单一索引
使用短索引不仅能够提高查询速度，而且能节省磁盘操作以及I/O操作，如 address 为 varchar(200) 如果经常存储为 100 那么设置索引 100 要优于默认和200
alter table `student` add index `address` (`address`(100)) using btree;2 组合索引
组合索引有最左原则
alter table `student` add index `mult` (`age`，`name`) using btree;3 唯一索引（UNIQUE KEY）
名称 | 作用 |
唯一索引（UNIQUE KEY） | 唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一，唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。 |
alter table `student` add unique index `phone` (`phone`);4 全文索引（FULL TEXT）
名称 | 作用 |
全文索引（FULL TEXT） | 全文索引允许在索引列中插入重复值和空值，全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建，在 MySQL 中只有 MyISAM 存储引擎支持全文索引。 |
1 创建方式
alter table `student` add fulltext index `name` (`name`);2 使用方式
select * from `student` where match(`name`) against('张三')5 空间索引（SPATIAL）
名称 | 作用 |
空间索引（SPATIAL） | 空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展，创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建，空间索引主要用于地理空间数据类型 GEOMETRY。这类索引很少会用到。 |
alter table `student` add spatial index `map` (`map`);2 索引方式
1 BTREE
BTREE索引以树形结构存储，通常用在像 "=，>，>=，<，<=、BETWEEN、Like"等操作符查询效率较高。
B-Tree是最常见的索引类型，所有值（被索引的列）都是排过序的，每个叶节点到跟节点距离相等。所以B-Tree适合用来查找某一范围内的数据，而且可以直接支持数据排序（ORDER BY）
B-Tree在MyISAM里的形式和Innodb稍有不同
MySQL 引擎 | 数据文件 |
MyISAM | MyISAM表数据文件和索引文件是分离的，索引文件仅保存数据记录的磁盘地址。 |
InnoDB | InnoDB表数据文件本身就是主索引，叶节点data域保存了完整的数据记录。 |
2 HASH
1 仅支持 “=”，“IN” 和 “<=>” 精确查询，不能使用范围查询
由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash
2 不支持排序
由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算
3 在任何时候都不能避免表扫描
由于Hash索引比较的是进行Hash运算之后的Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果
4 检索效率高
索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引
5 只有Memory引擎支持显式的Hash索引
但是它的Hash是nonunique的，冲突太多时也会影响查找性能。Memory引擎默认的索引类型即是Hash索引，虽然它也支持B-Tree索引
3 RTREE
R-Tree在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。
3 索引使用注意事项
1 不要使用NULL
只要列中包含NULL值将不会被包含在索引中，组合索引只要有一列含有NULL值，那么这一列对于组合索引就是无效的，所以我们在设计数据库的时候最好不要让字段的默认值为NULL;
2 使用短索引
使用短索引不仅能够提高查询速度，而且能节省磁盘操作以及I/O操作。
3 索引列排序
MySQL在查询的时候只会使用一个索引，因此如果where子句已经使用了索引的话，那么order by中的列是不会使用索引的，所以order by尽量不要包含多个列的排序，如果非要多列排序，最好使用组合索引。
4 Like 语句
一般情况下不是鼓励使用 like，如果非使用那么需要注意 like ‘%张三%’ 不会使用索引；但 like ‘张三%’ 才会使用索引。
5 不使用 NOT IN和<>操作
