MySQL索引

1 为什么要用索引

首先，我们问自己为什么需要用索引，什么时间使用。如果一张表的数据量不高于十万，改变的查询不强调时间效率，是不是就没必要用了。
如果没有索引，我要查询user表中name为“Tom”的小伙儿，这时就会一行行的顺序查找name知道匹配成功；如果添加了索引，通过一定的方法进行存储，好让查询name上的信息时，能够快速找到对应的数据，而不必在整张表的数据了。对，就是为了效率，为了快才建立索引的。

2 索引的优缺点

索引并不是一切都好，想用便用，来对比下它的优缺点，就能够把握使用索引的时机，提高效率。

优点

1 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
2 可以大大加快，数据的检索速度（创建索引的最主要的原因）
3 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义
4 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
5 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

缺点
1 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
2 创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间）3
3 会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长

3 索引的分类

MYSQL的索引主要分为主键索引(PRIMARY KEY)，唯一索引(UNIQUE) ，普通索引(INDEX)和全文索引(FULLTEXT) 。
MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换MEMORY/HEAP存储引擎：支持HASH和BTREE索引
主键索引： 它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会创建主键索引， CREATE INDEX不能用来创建主键索引，使用 ALTER TABLE来代替。

唯一索引： 与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须一。

普通索引： 这是最基本的索引，它没有任何限制。

全文索引： FULLTEXT索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR， VARCHAR和 TEXT列。

4 索引使用原则

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

1 在经常需要搜索的列上，可以加快搜索的速度
2 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
3 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度
4 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
5 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
6 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度

5 索引的创建和删除

1 用 ALTER TABLE 创建普通索引、UNIQUE索引或PRIMARY KEY索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE table_name ADD UNIQUE (column_list);

ALTER TABLE table_name ADD PRIMARY KEY (column_list);
主键是一种唯一性索引，建表时指定列属性为“PRIMARY KEY”，也可以创建；

2 用CREATE INDEX 对表增加普通索引或UNIQUE索引

CREATE INDEX index_name ON table_name (column_list);

CREATE UNIQUE INDEX index_name ON table_name (column_list);

3 删除索引

DROP INDEX index_name ON talbe_name;

ALTER TABLE table_name DROP INDEX index_name;

ALTER TABLE table_name DROP PRIMARY KEY;