sql索引

1.什么情况下使用索引

在经常需要搜索的列上，可以加快搜索的速度
在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度
在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不该创建索引的列

对于那些在查询中很少使用或者参考的列不应该创建索引。
若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
对于那些只有很少数据值或者重复值多的列也不应该增加索引。
这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
对于那些定义为text, image和bit数据类型的列不应该增加索引。
这些列的数据量要么相当大，要么取值很少。
当该列修改性能要求远远高于检索性能时，不应该创建索引。（修改性能和检索性能是互相矛盾的）
原文链接：https://blog.youkuaiyun.com/wangfeijiu/article/details/113409719

2.如何创建索引

CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX 索引名 ON 要增加索引的表名 (列，列…) //普通索引

CREATE UNIQUE INDEX 索引名 ON 要增加索引的表名 (列，列…) //UNIQUE索引

多列时各列之间用逗号分隔。索引名可不写，不写时，MySQL将根据第一个索引列赋一个名称。
规定索引不包含重复值，则索引应该创建为UNIQUE索引。对于单列惟一性索引，这保证单列不包含重复的值。对于多列惟一性索引，保证多个值的组合不重复。

PRIMARY KEY索引和UNIQUE索引非常类似

3.查看已经创建的索引

show index from 表名;

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）。

4.查看索引使用情况

explain函数验证索引是否有效

explain后面跟查询sql语句
各个关键字的含义如下：

table：索引所在表的表名；

type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为：const、eq_reg、ref、range、indexhe和ALL；

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从where语句中选择一个合适的语句；

key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MySQL会选择优化不足的索引。这种情况下，可以在Select语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MySQL忽略索引；

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好；

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数；

rows：MySQL认为必须检查的用来返回请求数据的行数；

Filtered：表示返回结果的行数占需读取行数的百分比 Filtered列的值越大越好 Filtered列的值依赖于统计信息

Extra：关于MySQL如何解析查询的额外信息。

5.删除索引

DROP INDEX 索引名 ON 表名