数据库索引类型-分析工具

1.普通索引
2.唯一索引
3.主键索引
4.组合索引
5.全文索引

缺点
索引失效
分析工具

逻辑上分类：

1 普通索引

普通索引是 MySQL 中最基本的索引类型，它没有任何限制，少数任务就是加快系统对数据的访问速度。

普通索引允许在定义索引的列中插入重复值和空值。

语法：
CREATE INDEX index_name ON table(column(length))
ALTER TABLE table_name ADD INDEX index_name ON (column(length))

2 唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

语法：
CREATE UNIQUE INDEX indexName ON table(column(length))

3 主键索引

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引

4 组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

5 全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。

目前只有char、varchar，text 列上可以创建全文索引。

在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

使用索引缺点

1. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。

2. 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。

索引失效的情况

1 不使用not in和<>操作

2 不要在where条件下，进行列上运算

3 like语句操作
不推荐使用like操作，如果非使用不可，like “aaa%”可以使用索引

4 索引列排序
查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。
尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

5 索引不会包含有null值的列
只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。

索引优化分析工具 Explain

可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

可以分析出表的读取顺序，哪些索引可以使用，数据读取操作的操作类型，哪些索引被实际使用，表之间的引用，每张表有多少行被物理查询

用法：
在执行的SQL前加上Explain关键字就可以了

各个列的作用:

字段	含义
id	select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
select_type	查询类型 或者是 其他操作类型
table	正在访问哪个表
partitions	匹配的分区信息
type	访问方式
possible_keys	显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
key	实际使用到的索引，如果为NULL，则没有使用索引
key_len	表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
ref	显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
rows	根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
filtered	表示符合查询条件的数据百分比
Extra	附加信息

关键参数

rows

结果值从最好到最坏依次是：
const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

const	表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量
eq_ref	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
range	只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
index	出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组
all	Full Table Scan，将遍历全表以找到匹配的行
index_merge	在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中
ref_or_null	对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。
index_subquery	利用索引来关联子查询，不再全表扫描。
unique_subquery	该联接类型类似于index_subquery。 子查询中的唯一索引

rows

rows列显示MySQL认为它执行查询时必须检查的行数。越少越好。