1.索引类型

1.主键索引 PRIMARY KEY
它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。
注意：一个表只能有一个主键

2.普通索引 INDEX
最基本的索引，它没有任何限制。
可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

3.唯一索引 UNIQUE
唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
index_name :可选
可以通过ALTER TABLE table_name ADD UNIQUE [index_name] (column);创建唯一索引
可以通过ALTER TABLE table_name ADD UNIQUE [index_name](column1,column2);创建唯一组合索引

4.组合索引 INDEX(注意和唯一组合索引的区别)

组合索引，即一个索引包含多个列。多用于避免回表查询。

可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

5.全文索引 FULLTEXT

全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。

可以通过ALTER TABLE table_name ADD FULLTEXT [index_name] (column);创建全文索引

注意: 索引一经创建不能修改，如果要修改索引，只能删除重建。
可以使用DROP INDEX index_name ON table_name;删除索引。

2.索引优化:

索引优化规则：

1）如果MySQL估计使用索引比全表扫描还慢，则不会使用索引。

返回数据的比例是重要的指标，比例越低越容易命中索引。记住这个范围值——30%，后面所讲的内容都是建立在返回数据的比例在30%以内的基础上。

2）前导模糊查询不能命中索引。

explain select * from t where name like '%user_%'

非前导模糊查询则可以使用索引，可优化为使用非前导模糊查询：
explain select * from t where name like 'user_%'

3）复合索引的情况下，查询条件不包含索引列最左边部分（不满足最左原则），不会命中符合索引。

创建复合索引:alter table t add index dept_name_index(dept,name)

根据最左原则，可以命中复合索：
explain select * from t where dept=1 and name='user_2'

注意，最左原则并不是说是查询条件的顺序,而是查询条件中是否包含索引最左列字段(即dept字段)：

例如:explain select * from t where name='user_2'此时where后并不包含最左字段dept,那么就不会使用索引

5）union、in、or都能够命中索引，建议使用in。

查询的CPU消耗：or>in>union。

6）用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。

因为or后面的条件列中没有索引，那么后面的查询肯定要走全表扫描，在存在全表扫描的情况下，就没有必要多一次索引扫描增加IO访问。

7）负向条件查询不能使用索引，可以优化为in查询。

负向条件有：!=、<>、not in、not exists、not like等。

例如:EXPLAIN SELECT * FROM user WHERE status !=1 AND status != 2;
可以优化为in查询，但是前提是区分度要高，返回数据的比例在30%以内：
EXPLAIN SELECT * FROM user WHERE status IN (0,3,4);

8）范围条件查询可以命中索引。

范围条件有：<、<=、>、>=、between等。
例如:如果是范围查询和等值查询同时存在，优先匹配等值查询列的索引：
EXPLAIN SELECT * FROM user WHERE status>5 AND age=24;

9）数据库执行计算不会命中索引。

会用到EXPLAIN SELECT * FROM user WHERE age>24;
不会用到EXPLAIN SELECT * FROM user WHERE age+1>24;
计算逻辑应该尽量放到业务层处理，节省数据库的CPU的同时最大限度的命中索引。

10)利用覆盖索引进行查询，避免回表。

被查询的列，数据能从索引中取得(意思就是:被查询的列是索引列)，而不用通过行定位符row-locator再到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速查询速度。
因为被查询字段是索引列，所以直接从索引中就可以获取值，不必回表查询：
当查询其他列时，就需要回表查询，这也是为什么要避免SELECT*的原因之一：

11)建立索引的列，不允许为null。

单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集，所以，请使用not null约束以及默认值。使他们的默认值为''而不是NULL更好

虽然IS NULL可以命中索引，但是NULL本身就不是一种好的数据库设计，应该使用NOT NULL约束以及默认值。