关于索引
背景及定义：
一种数据结构，包含着对数据表里所有记录的引用指针，被用于快速定位信息，不使用索引的话，mysql必须从第一条记录开始读完整个表，直到找到相关的行，表越大，查询所需要的时间就越多，当存在索引的时候，直接根据索引就能快速定位，节省时间提高效率
被创建于已有的表中，可以对行的定位更快速有效，可以在表格的一个或多个列上创建索引，每个索引都会被起个名字，利用名字来快速查询
分类
- 普通索引
- 唯一索引
- 主键索引
- 全文索引（不怎么使用）
使用索引的优点
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
- 可以大大加快数据的检索速度，这也是创建索引的最主要原有
- 可以加快表和表之间的连接，特别是在实现数据的完整性方面有意义
- 在使用分组和排序自居进行数据检索时，同样可以减少查询时间
- 提供使用索引，提高系统的性能
使用索引的缺点
- 索引是一种数据结构，需要占用物理空间，在数据表占用一定空间的基础上所需要的空间会更大
- 创建索引和维护索引要耗费时间，时间随着数据量的增加而增加
- 当对表中的数据进行增加、删除和修改的时候，索引也要进行动态维护，这样降低了数据的维护速度
语法
创建索引
第一种：创建表时创建索引
需指定长度
INDEX index_name (column_list(length))
例子：
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,//自增长的主键id
`name` VARCHAR(10) NOT NULL,//非空的姓名
age INT DEFAULT 18,//默认年龄18
teacher_id varchar(10),
INDEX index_teacher_id (teacher_id(5))//为teacher_id创建索引
);
第二种方式：CREATE INDEX
可以创建普通索引和UNIQUE索引
- 索引名不可以选
- 不可以创建
PRIMARY KEY索引
CREATE INDEX index_name ON table_name(column_list);
CREATE UNIQUE INDEX index_name ON table_name(column_list);
例子
CREATE INDEX index_teacher_id ON student(teacher_id)；
第三种方式： ALTER TABLE
在创建表之后再创建索引
- 添加普通索引，索引值可以出现多次
ALTER TABLE table_name ADD INDEX index_name(column_list);
创建唯一索引，可以为NULL
ALTER TABLE table_name ADD UNIQUE index_name(column_list);
创建主键索引
ALTER TABLE table_name ADD PRIMARY KEY (column_list);
例子
ALTER TABLE student ADD INDEX index_teacher_id(teacher_id);
注意
创建多列索引时使用逗号分隔，索引名可以自己设立，当缺省时，mysql自动命名
删除索引
第一种：DROP INDEX
DROP INDEX [index_name] ON [table_name];
例子
DROP INDEX index_teacher_id ON student;
第二种：ALTER TABLE
在第二条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引
ALTER TABLE [table_name] DROP INDEX [index_name];
ALTER TABLE [table_name] DROP PRIMARY KEY;
例子
ALTER TABLE student DROP INDEX index_teacher_id;
注意
如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。
查看索引
SHOW INDEX FROM table_name;
什么时候需要创建索引
- 定义主键的数据类会自动创建索引
- 定义外键的数据列需要建立索引
- 对于经常查询的数据列最好建立索引，但是唯一性太差的字段不适合单独创建索引，如状态字段、类型字段等这些字段中存放的数据可能总共就是那么几个或几十个值重复使用，每个值都会存在于成千上万或更多的记录中。
- 对于需要在指定范围内快速或频繁查询的数据列
- 经常用在
WHERE子句中的数据列 - 经常出现在
ORDER BY``GROUP BY``DISTINCT后面的字段，建立索引 - 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引
- 对于定义为
TEXT``IMAGE``BIT的数据类型不要建立索引 - 对于经常存取的列避免建立索引
- 应限制表上的索引数目。对一个存在大量更新操作的表，所见索引数量一般不超过3个
