Mysql索引

1.简介

1.1 索引的含义和特点

不使用索引的话，mysql会遍历整个表，效率较低
不同的存储引擎，索引也不一样：

• MyISAM和InnoDB:只支持BTREE索引。
• MEMORY/HEAP：支持HASH和BTREE索引。

索引的优点：

• 创建唯一索引保证数据表中每一行数据的唯一性。
• 可以大大加快数据的查询时间。
• 可以实现数据的参考完整性方面，可以加速表与表之间的链接。
• 在使用分组和排序子句查询时，也可以减少分组和排序的时间。

索引的缺点：

• 创建和维护索引耗费时间，随着数据量的增加也会增加时间。
• 索引会占用磁盘空间，数据越多占用越多。
• 增删改操作也会动态的维护索引，降低了数据的维护速度。

1.2 分类

• 普通索引和唯一索引：普通索引允许空值和重复值；唯一索引要求列值必须唯一，但允许有空值。如果是组合索引，则组合的列值必须唯一。主键是特殊的唯一索引，不能有空值。
• 单列索引和组合索引：单列索引一个索引只包含一个列，一个表可以有多个单列索引；组合索引指多个字段组合上创建索引，遵循最左前缀原则（只有使用了这些字段的最左边字段时，索引才会被使用）。
• 全文索引：索引类型为FULLTEXT，支持索引列上的全文查找（可以在char,varchar和text类型上创建），只有MyISAM支持。
• 空间索引：对空间数据类型的字段建立索引，Mysql的空间数据类型有4中：GEOMETRY、POINT、LINESTRING和POLYGON。必须NOT NULL，只有MyISAM支持。

1.3 设计原则

1. 并非越多越好，太多会占用磁盘空间，还会影响增删改的性能。
2. 避免对经常更新的表进行过多索引，并且索引中的列要尽可能少。经常用于查询的字段创建索引，避免添加不必要的字段。
3. 数据量小的表最好不要用索引。查询话费的时间可能比遍历索引的时间还要短。
4. 条件表达式中用到的不同值较多的列上创建索引，不同值较少的不要建立索引（如“男”，“女”列，只有两种值，无需建立索引）
5. 当列唯一时，使用唯一索引。
6. 在频繁排序或分组（group by 、order by）上建立索引，如果需要排序的列有多个，建立组合索引。

2.创建索引

2.1创建表时指定索引

CREATE TABLE table_name[col_name date_type]
--分别表示唯一索引，全文索引和空间索引
[UNIQUE|FULLTEXT|SPATIAL]
[INDEX|KEY] [index_name](col_name [length]) 
--升序或降序的索引值存储
(ASC|DESC)

2.2 已存在的表上增加索引

ALTER TABLE table_name ADD INDEX index_name(col_name(length))

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table_name (col_name[length],...)[ASC|DESC]

3.删除索引

ALTER TABLE table_name DROP INDEX index_name;

DROP INDEX index_name ON table_name