索引概述

 

什么是索引

语法 show index from [tableName] 解析

索引类型

主键索引 PRIMARY KEY

唯一索引 UNIQUE

普通索引 INDEX

组合索引 INDEX

全文索引 FULLTEXT

总结

什么场景不适合创建索引

什么样的字段适合创建索引

索引优缺点

什么情况下索引失效？

什么是索引

索引是存储引擎快速找到记录的一种数据结构

 

语法 show index from [tableName] 解析

 

以 message_data 表为例，执行 show index from message_data

 

索引 属性 解释：

table	表名称
non_unique	如果索引不能包括重复词，为0（也就是平时所说的唯一索引）； 如果可以，则为1
key_name	索引的名称，如果名字相同则表明是同一个索引，而并不是重复，比如上图中的第二、三条数据，索引名称都是index_fID_lastTime，其实是一个联合索引
seq_in_index	索引中的列序号，从1开始。Seq_in_index 表明在联合索引中的顺序，我们就能推断出联合索引中索引的前后顺序
column_name	列名称
collation	列以什么方式存储在索引中，在mysql中，有值'A'（升序）或者NULL（无分类）。 默认的类型是utf8_general_ci，这样的大小写不敏感
cardinality	索引在唯一值的数据的估值，通过运行analyze table xxx_table;或者 myisamchk -a可以更新，技术根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没必要是精确的， 基数越大，当进行联合所饮食，mysql使用该索引的机会越大。myisam中，该值是准确的，INNODB中该值数据是估算的，存在偏差
sub_part	如果列只是部分的编入索引 则为被编入索引的字符的数目，如果整列被编入索引，则为NULL
packed	指示关键词如何被压缩，如果没有被压缩，则为NULL。 压缩一般包括压缩传输协议、压缩列解决方案和压缩表解决方案
NULL	如果列含有NULL，则含有YES，如果没有，则该列为NO
index_type	用过的索引方法（BTREE,FULLTEXT,HASH,RTREE）：  1). FULLTEXT     即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。     2). HASH     由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。    HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。     3). BTREE    BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。     4). RTREE     RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。
comment	备注
index_comment	为索引创建时提供了一个注释属性的索引的任何评论

 

 

 

索引类型

主键索引 PRIMARY KEY

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

注意：一个表只能有一个主键

 

唯一索引 UNIQUE

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

可以通过 ALTER TABLE [table_name] ADD UNIQUE (column); 创建唯一索引

 

 

 

 

普通索引 INDEX

最基本的索引，它没有任何限制。

可以通过 ALTER TABLE [table_name] ADD INDEX index_name (column); 创建普通索引

 

组合索引 INDEX

组合索引，即一个索引包含多个列。多用于避免回表查询。

可以通过ALTER TABLE [table_name] ADD INDEX index_name(column1, column2, column3);创建组合索引

 

全文索引 FULLTEXT

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。

可以通过 ALTER TABLE [table_name] ADD FULLTEXT (column);创建全文索引

 

 

 

总结

什么场景不适合创建索引

1. 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因 为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

2. 对于那 些只有很少数据值的列也不应该增加索引。因为本来结果集合就是相当于全表查询了，所以没有必要。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比 例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

3. 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

4. 当修改性能远远大于检索性能时，不应该创建索 引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因 此，当修改性能远远大于检索性能时，不应该创建索引。

5. 不会出现在where条件中的字段不该建立索引。

 

什么样的字段适合创建索引

1、表的主键、外键必须有索引；外键是唯一的，而且经常会用来查询 

2、数据量超过300的表应该有索引；

3、经常与其他表进行连接的表，在连接字段上应该建立索引；经常连接查询，需要有索引 

4、经常出现在Where子句中的字段，加快判断速度，特别是大表的字段，应该建立索引，建立索引，一般用在select ……where f1 and f2 ，我们在f1或者f2上建立索引是没用的。只有两个使用联合索引才能有用

5、经常用到排序的列上，因为索引已经排序。

6、经常用在范围内搜索的列上创建索引，因为索引已经排序了，其指定的范围是连续的

 

索引优缺点

优点

索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度

索引的优点是可以提高检索数据的速度

缺点

索引的缺点是创建和维护索引需要耗费时间<br/**>

索引可以提高查询速度，会减慢写入速度

 

索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

 

什么情况下索引失效？

Select * from user order by age 索引生效

 

inde(name,age) 复合索引

Select * from user order by age 索引失效 （复合索引中 name都没走，走不到age）

Select * from user order by name,age 索引失效 （复合索引中 按顺序走）

Select * from user where age = 18 and name = 'lisi' 索引失效 （未按顺序走）

 

index(age,name) 复合索引

Select * from user where age > 18 and name = 'lisi' (带范围的，age部分失效，name失效)

index：index_age_name

extra：范围查找

 

注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

 

like查询是以%开头，索引失效，以%结尾，索引可以使用。