mysql索引设计原则_MySQL索引设计原则

1.最左前缀原则

一般在where条件中两个及以上字段时，我们会建联合索引。若查询语句：select name,address,country from people where name='XXX' and country='XXX';

索引建立有下面两种方案 A(name,country) B(country,name)。将选择性好的字段放在前面(因为people name重复率相对于country低)，所以应该建name,country索引。无论where name='XXX' and country='...' 还是 where country='XXX' and name='...'，MySQL会帮你优化查询条件，不用担心查询顺序。若组合索引有四五个字段，那么按照选择性进行排列，选择性好的字段放前面。

假设create index idx_name_address_country on t1(name,address,country);按照最左匹配有下面几个原则，判断查询是否会走索引idx_name_address_country：

1.1 若查询的条件不包含索引的最左列，无法使用索引

where name=xxx and address=xxx and country=XXX

可以走索引

where name=xxx

可以走索引

where name=xxx  and country=XXX

可以走索引

where address=xxx

不可以走索引，不包含最左列name

where country=xxx  and name=xxx

不可以走索引,不包含最左列name，mysql没有优化是因为索引字段里还包含了address

1.2 查询包含了索引中所有的字段，查询效率较高，如果只包含了索引中部分字段，查询效率会低一些

where name=xxx 和 where name=xxx  and country=XXX 虽然都包含了索引最左列，也可以走索引，但是他们都只能根据name字段进行过滤数据，效率比 where name=xxx and address=xxx and country=XXX低很多

1.3 如果组合索引中已经包含了字段，可以不用单独再建索引，提高索引使用率

比如已经有了索引(name,address,country)，就不需要在单独建索引(name)或 (name,address)

1.4 无法对组合索引中多个字段进行范围查询，只能按照最左原则，对最左边第一个范围查询有效

例如create index idx_A_B_C on table(A,B,C); 红字表示不走索引

A=5

索引

A BETWEEN 5 AND 10

索引

A=5 AND B BETWEEN 5 AND 10

索引

A BETWEEN 5 AND 10 AND B=5

部分索引

A IN (1,2,3) AND B=5

索引

B=5 AND ...

不走索引

A = 5 AND B > 5 AND C > 10

部分索引

1.5 覆盖索引： 即索引中包含了查询中的所有字段，可以避免回表查询，减少访问磁盘次数

1.6 利用索引的有序性，进行排序，有效减少CPU开销，需要遵从最左原则

采用idx_A_B_C，下列查询可使用索引

ORDER BY A

ORDER BY A,B

ORDER BY A DESC, B DESC

WHERE A = 5 ORDER BY B [ASC\DESC]

WHERE A > 5 ORDER BY A [ASC\DESC]

WHERE A = 5 ORDER BY A,B

ORDER BY B

ORDER BY A[ASC\DESC] ,B [DESC/ASC]

1.7 其他设计

Join查询中连接字段建立索引

只返回需要的字段，避免使用select *

不使用全模糊查询 like '%xxx%’(无法走索引)，可以使用like 'XXX%'(走索引)

不等于查询not in ， =(无法走索引)

类型不匹配，比如存了数值的字符串类型字段(如手机号)，查询时记得不要丢掉值的引号，否则无法用到该字段相关索引

2.最大选择性原则

该不该在一个字段上建立索引，主要考虑选择性，选择性就是这个字段里面的值重复率高不高。公式是 distinct(建索引的列)/count(*) ，区间范围在[0,1]，如果是0，表示该列中所有值都一样，如果是1，表示该列是唯一约束。该比例越趋向于1，查询性能越好。这是由B+Tree的性质决定的。一般情况，status(状态)、is_deleted(是否删除)、sex(性别)列都不建议建索引。

转载至链接:https://my.oschina.net/u/2302503/blog/1924940