MySQL索引优化

MySQL索引类型分为：普通索引、唯一索引、主键、全文索引。
普通索引允许重复的值出现。
唯一索引除了不能有重复的记录外，其它和普通索引一样。
主键是一种特殊的唯一索引。
全文索引只能对(char，varchar，text)进行索引，MyISAM引擎支持全文索引。

1.什么情况下需要建立索引？
通常情况下来讲，在WHERE、JOIN以及LIKE子句中出现的列需要建立索引。

2.多个单列索引和一个多列索引有什么区别？
多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引。
因此当WHERE使用多个单列索引时候，MySQL只能选择最优的一个作为索引。

3.组合索引的使用原则?
MySQL组合索引遵循最左前缀原则。
例如建立一个组合索引：INDEX(title,author,category)有效索引为:
-title,author,category
-title,author
-title

4.索引是不是越多越好？
不是。索引可以提高SELECT的效率，但会降低数据更新的效率。
因为在数据更新时(INSERT、UPDATE、DELETE)需要更新索引结构。

5.使用索引注意事项
*索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，
那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

*使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。
例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，
那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

*索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，
那么order by中的列是不会使用索引的。
因此数据库默认排序可以符合要求的情况下不要使用排序操作。
尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
在ORDER BY操作中，MySQL只有在排序条件不是一个查询条件的情况下才使用索引。

*LIKE语句操作
一般情况下不鼓励使用LIKE操作。
如果非使用不可，如何使用才能有效使用索引也是一个问题。
LIKE "%aaa%"不会使用索引而LIKE "aaa%"可以使用索引。

*不要在列上进行运算
SELECT * FROM article WHERE YEAR(ctime)>'2010';
这将在每行上都进行运算，将导致索引失效而进行全表扫描。
可以修改为：
SELECT * FROM article WHERE ctime>'2010-01-01 00:00:00';

*不要使用!=、<>和NOT IN操作
当使用多个单列索引的时候，!=、<>和NOT IN会使索引失效。
以下操作符能够使用索引：<、<=、=、>、>=、BETWEEN、IN以及某些时候的LIKE。

6.分析索引效率
EXPLAIN SELECT * FROM article WHERE title='beijing'\G;

           id: 1
  select_type: SIMPLE
        table: article
         type: ref
possible_keys: title
          key: title
      key_len: 92
          ref: const
         rows: 1
        Extra: Using where

possible_keys：
    可能可以利用的索引的名字。
    这里的索引名字是创建索引时指定的索引昵称；
    如果索引没有昵称，则默认显示的是索引中第一个列的名字。
Key：
    它显示了MySQL实际使用的索引的名字。如果为NULL，则MySQL不使用索引。
rows：
    MySQL在找到正确的结果之前必须扫描的记录数。