何时要使用索引？

主键自动建立唯一索引(其实是主键索引)；

经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；

作为排序的列要建立索引；

查询中与其他表关联的字段，外键关系建立索引;

高并发条件下倾向组合索引；

用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

何时不要使用索引？

经常增删改的列不要建立索引；

有大量重复的列不建立索引；

表记录太少不要建立索引。

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

建索引的几大原则

1.最左前缀匹配原则

非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a 1="" and="" b="2" c=""> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3.尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

4.索引列不能参与计算

保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

5.尽量的扩展索引，不要新建索引。

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

索引失效的情况:

可参考谈谈索引失效

在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。

在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了。

LIKE操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。

在索引的列上使用表达式或者函数会使索引失效

例如：【因涉及一些执行语句，禁止写入，请联系客服获取】，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：【因涉及一些执行语句，禁止写入，请联系客服获取】。其它通配符同样，也就是说，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。

在查询条件中使用不等于，包括<符号、>符号和！=会导致索引失效。

特别的是如果对主键索引使用！=则不会使索引失效，如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效。（其实, 不等于，包括<符号、>符号和！，如果占总记录的比例很小的话，也不会失效）

在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。

字符串不加单引号会导致索引失效。

更准确的说是类型不一致会导致失效，比如字段email是字符串类型的，使用W H E R E email=99999 则会导致失败，应该改为W H E R E email='99999'。

在查询条件中使用OR连接多个条件会导致索引失效，除非OR链接的每个条件都加上索引，这时应该改为两次查询，然后用U N I O N ALL连接起来。

如果排序的字段使用了索引，那么select的字段也要是索引字段，否则索引失效。特别的是如果排序的是主键索引, 则s e l e c t * 不会导致索引失效。

尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引；

索引的优化

1、最左前缀

索引的最左前缀和和B+Tree中的“最左前缀原理”有关，举例来说就是如果设置了组合索引那么以下3中情况可以使用索引：col1，，，其它的列，比如，，col2，col3等等都是不能使用索引的。

根据最左前缀原则，我们一般把排序分组频率最高的列放在最左边，以此类推。

2、带索引的模糊查询优化

在上面已经提到，使用LIKE进行模糊查询的时候，'%aaa%'不会使用索引，也就是索引会失效。如果是这种情况，只能使用全文索引来进行优化。

为检索的条件构建全文索引，然后使用

因涉及一些执行语句，禁止写入，请联系客服获取

3、使用短索引

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