MySQL索引的总结

一，MySQL中索引的语法

概念
索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，
作用
①数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。
②索引所起的作用类似书籍目录，可用于快速定位、检索数据。
③索引对于提高数据库的性能有很大的帮助
使用场景
要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：
(1)数据量较大，且经常对这些列进行条件查询。
(2)该数据库表的插入操作，及对这些列的修改操作频率较低。
(3)索引会占用额外的磁盘空间。
满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。
使用
创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。
查看索引

show index from 表名;

创建索引
在创建表的时候添加索引

create table test(
	id int not null,
	username varchar(20) not null,
	index [indexname] (username(length))
	);

在创建表以后添加索引

create index index_name on 表名

注意
①索引需要占用内存空间，因此在创建索引的时候要考虑磁盘是否够用
②创建索引时需要对表进行加锁，因此实际操作需要业务空闲期间进行

删除索引

drop index 索引名 on 表名;

二，索引的优缺点

优势：可以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；

劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；

三，索引的分类

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引
1、主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；

2、唯一索引：用来建立索引的列的值必须是ALTER TABLE ‘table_name’ ADD FULLTEXT INDEX ft_index(‘col’)；唯一的，允许空值

ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；

3、普通索引：用表中的普通列构建的索引，没有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('col')；

4、全文索引：用大文本对象的列构建的索引

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；

5、组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

四，索引的使用策略

什么时候使用索引
①主键自动建立唯一索引；
②经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
③作为排序的列要建立索引；
④查询中与其他表关联的字段，外键关系建立索引
⑤高并发条件下倾向组合索引；
⑥用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

什么时候不使用索引
①经常增删改的列不要建立索引；
②有大量重复的列不建立索引；
③表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。