mysql数据库——索引篇

索引是一个单独的、存储在磁盘上的数据库结构，包含着数据库中记录的引用指针，索引类似书中的目录，没有索引读取数据时数据库必须从第一条记录开始到读完整个表，直到找对相关的行，如果表的查询列有索引，mysql能快速到达某个位置去搜寻数据，索引主要是为了提高数据库的查询速度的。

一、索引

mysql所有存储引擎至少支持16个索引，长度至少256字节，
索引可以加快数据查询速度，减少分组和排序的时间，但也有很多不利，如

1. 创建和维护索引需要耗费时间，数据越大，耗时越长，
2. 索引需要占物理空间，如果有大量索引，索引文件可能比数据文件更快达到最大文件尺寸
3. 表中数据进行增加、删除、修改操作的时候，索引也要去维护，降低了数据库的维护速度，所以频繁修改的列不适合建立索引 

二、存储类型

mysql存储类型有如下几种：BTREE、HASH，FULLTEXT，RTREE

1. FULLTEXT
即为全文索引，目前只有MyISAM引擎支持。5.6版本以上InnoDB引擎也支持FULLTEXT，但是不支持中文的全文索引，其可以在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几种。

二、索引的分类

普通索引：提高查询速度

唯一索引：列值唯一，可以为空值，如果是组合索引，列值的组合必须唯一

主键索引：特殊的唯一索引，不可以为空

组合索引：多列值组合上创建的索引，只有查询条件中使用这些字段的最左边字段时，索引才会使用，遵循最左前缀集合

全文索引：对文本的内容进行分词，进行搜索

空间索引：SPATIAL索引，

最左前缀集合，例如

组合索引（A,B,C）查询条件中(A)、(A,B)、(A,B,C)会使用索引，（B)、（C）、（B,C）的查询都不会使用索引

三、索引操作

 

1、ALTER创建索引

ALTER TABLE table_name ADD [unique|fulltext|statial]  INDEX [index_name] （col_name[length],...） [ASC|DESC]

 index和key是同义词，功能一样， index_name索引名称，为可选参数，不指定默认为col_name列名，length可选参数，指索引长度，只有字符串类型字段的列才能指定索引长度，ASC、DESC指定升序和降序的索引值存储

2、CREATE创建索引

CREATE INDEX index_name ON table_name(col_name);--创建普通索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);--创建唯一索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);--创建普通组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);--创建唯一组合索引

3、创建表时创建索引

CREATE TABLE table_name(
    ID INT NOT NULL,
    col_name VARCHAR (16) NOT NULL,
    INDEX index_name (col_name)
)

4、删除索引

ALTER TABLE table_name DROP INDEX index_name;  
DROP INDEX index_name ON table_name;

四、索引设计原则

1. 索引并非越多越好，一个表中有大量索引，不仅占用擦盘空间，还会影响插入、删除、更新等操作的性能，数据更改的同时，索引也会进行调整和更新
2. 避免对经常更新的表进行过多的索引，并且索引中的列尽可能少，而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段
3. 数据量小的表最好不要使用使用，由于数据量小，查询话费的时候可能比遍历索引的时间还要短
4. 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引，比如性别字段只有男女两个不同值，因此无需建立索引
5. 当某些数据是唯一的时候指定唯一索引，使用唯一索引能确保定义的列的数据完整性，以提高查询速度
6. 在频繁进行排序和分组的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引