何时要使用索引?


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

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

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

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

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

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




何时不要使用索引?


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

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

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

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




建索引的几大原则


1.最左前缀匹配原则


非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and 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%’可以使用索引。


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


例如:select from users where YEAR(adddate)<2007,将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:select from users where adddate<’2007-01-01′。其它通配符同样,也就是说,在查询条件中使用正则表达式时,只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。

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


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

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


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


更准确的说是类型不一致会导致失效,比如字段email是字符串类型的,使用WHERE email=99999 则会导致失败,应该改为WHERE email=’99999’。

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


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


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




索引的优化

1、最左前缀


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

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

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


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

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

1
SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);

3、使用短索引


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