【MySQL】MySQL学习笔记（九）索引

  索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间越多，如果表中查询的列有一个索引，MySQL能快速到达某个位置去搜寻数据文件，而不必查看所有数据。

1 索引简介

  数据库中的索引是某个表中一列或者若干列值的集合，是一个独立的、物理的数据库结构，一般是与图或视图中的列相关联，数据库用户可以利用索引快速地访问数据表中的数据信息。通常情况下，只有在需要经常查询索引列中的数据时，才在表上创建索引，因为索引的使用会占用磁盘的空间，并且降低添加、删除和更新数据行的速度。
  索引是在存储引擎中实现的，所有存储引擎支持每个表至少16个索引，MySQL中索引的存储类型有两种：BTREE和HASH，具体和表的存储引擎相关，MyISAM和InnoDB存储引擎只支持BTREE索引；MEMORY存储引擎可以支持HASH和BTREE索引。

• 在数据库中，索引主要有以下的一些作用：

  • 通过创建索引，可以保证数据记录的唯一性；
  • 可以大大加快数据检索速度；
  • 可以加速表与表之间的连接；
  • 在使用分组或排序子句进行检索数据时，可以显著减少查询中分组和排序的时间。

• 索引的分类：

  • 普通索引和唯一索引
    
    • 普通索引：基本索引类型，允许在定义索引的列中插入重复值和空值
    • 唯一索引：索引列的值必须唯一，但允许有空值，主键索引是一种特殊的唯一索引，不允许有空值
  • 单列索引和组合索引
    
    • 单列索引：一个索引值包含单个列，一个表中可以有多个单列索引
    • 组合索引：在表的多个字段上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
  • 全文索引：全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入空值和重复值，全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建，MySQL中只有MyISAM存储引擎支持全文索引。
  • 空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4中，分别是：GEOMETRY、POINT、LINESTRING和POLYGON。

• 索引的设计原则：

  • 索引并非越多越好，索引会影响INSERT、DELETE和UPDATE语句的性能
  • 避免对经常更新的表进行过多的索引，索引列应该尽可能减少，对于经常查询的字段应该创建索引，但是避免添加不必要的字段
  • 数据量小的表最好不要使用索引，此时建立索引并不会优化性能
  • 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引
  • 灵活使用唯一索引
  • 在频繁进行排序或分组的列上建立索引，可以提升性能

2 创建索引

  MySQL支持多种方法在单个或多列上创建索引：在创建表的定义语句CREATE TABLE中指定索引，使用ALTER TABLE语句在存在的表上创建索引，或者使用CREATE INDEX语句在已存在的表上添加索引。

• 创建表的时候创建索引：create table table_name [col_name data_type] [unique|fulltext|spatial] [index|key] [index_name] (column_name,...) [asc|desc];
• 在已经存在的表上创建索引：
  
  • 使用ALTER TABLE语句创建索引，alter table table_name add [unique|fulltext|spatial] [index|key] [index_name] (column_name,...) [asc|desc];
  • 使用CREATE INDEX语句创建索引，create [unique|fulltext|spatial] [index|key] [index_name] on table_name (column_name,...) [asc|desc];

3 删除索引

  MySQL中删除索引使用ALTER TABLE或者DROP INDEX语句。

• 使用ALTER TABLE删除索引：alter table table_name drop index index_name;
• 使用DROP INDEX删除索引：drop index index_name on table_name;