MySQL 索引

MySQL 索引

什么是索引

索引(INDEX)是对数据库表中一列或多列的字段值进行排序的一种结构

• 未建立索引的数据表：当查询数据表时，MySQL需要扫描遍历全表，直到找到查询的数据为止。如果数据表数据

  非常大（如超过10W行记录）时，查询性能将会很慢。

• 已建立索引的数据表：当查询数据表时，MySQL利用各种各样的快速定位技术，能够大大提高查询效率。特别是

  当数据量非常大，查询涉及多个表时，使用索引往往能使查询速度加快成千上万倍。

使用索引可快速访问数据库表中的特定信息

索引分类

单列索引

一个单列索引只包含一个列（一个表可以有多个单列索引）

• 创建普通索引（最基本的索引，没有任何限制）

  ALTER TABLE '表名'ADD INDEX '索引名'('字段名')
  

• 创建唯一索引（索引列的值必须唯一，但允许有空值）

  ALTER TABLE '表名'ADD UNIQUE INDEX'索引名'('字段名')
  

• 创建主键索引（一种特殊的唯一索引，不允许有空值，一般建表时创建）

  ALTER TABLE `表名` ADD PRIMARY KEY ( `主键字段名` )
  

• 创建全文索引（用于检索文本信息，仅限于 MyISAM数据库引擎有效）

  ALTER TABLE `表名` ADD FULLTEXT ( `字段名` )
  

• 查找索引

  SHOW  INDEX FROM '表名'
  

• 删除索引

  DROP INDEX '索引名' ON '表名'
  

组合索引

一个组合索引包含两个或两个以上的列（一个表可以有多个组合索引）

• 创建组合普通索引

  ALTER TABLE '表名'ADD INDEX '索引名'('字段名1'，'字段名2'…)
  

• 创建组合唯一索引

  ALTER TABLE '表名'ADD UNIQUE  INDEX '索引名'('字段名1'，'字段名2'…)
  

  注意：组合索引遵循”最左前缀“原则

使用索引的优点

1. 可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性
2. 建立索引可以大大提高检索的数据,以及减少表的检索行数
3. 建立索引可以加速表与表直接的相连，提升联表查询效率
4. 在分组和排序字句进行数据检索,可以减少查询时间中分组和排序时所消耗的时间(数据库的记录会重新排序)
5. 建立索引可以在查询中使用索引,可以大大提高性能

使用索引的缺点

1. 在创建索引和维护索引，会耗费时间,随着数据量的增加而增加
2. 索引文件会占用物理空间，除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
3. 当对表的数据进行更新操作（INSERT,UPDATE,DELETE） 时，索引也要动态的维护,这样就会降低数据的维护速度（建立索引会占用磁盘空间的索引文件）

创建索引的建议

• 表的主键、外键建议创建索引
• 表记录数非常大（一般超过300行记录）建议创建索引
• 经常与其他表进行连接的表，在连接字段上建议创建索引
• 经常出现在where字句中的字段，特别是大表的字段，建议创建索引
• 索引一般建在经常搜索的字段上
• 索引一般建在小字段上（短索引），对于大的文本字段或超长字段，不要建索引
• 经常需要排序、分组的字段上，建议创建索引
• 不要在字段上进行运算，这样会导致该字段上创建的索引失效

索引的创建必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据；不正确的创建索引，不但不会提升性能，相反还会降低数据操作的性能

不创建索引的建议

• 查询中很少使用到的字段列，不应该创建索引；如果建立了索引反而还会降低 MySQL 的性能和增大了存储空间需求
• 很少数据的字段列，不应该建立索引（比如一个性别字段0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率）
• 定义为 text、image 或 bit 数据类型的字段列不应该增加索引
• 当表更新操作(UPDATE,INSERT,DELETE)远远大于查找操作(SELECT)时不应该创建索引,这两个操作是互斥的关系