MySQL索引及注意事项

Posted by Yezhiwei on April 8, 2018

索引规范

  • 自增列或全局ID做InnoDB主键,推荐用独立于业务的 AUTO_INCREMENT 列或全局ID生成器做代理主键
  • 非唯一索引命名默认为 idx_字段名 来命名
  • 唯一索引使用 uk_字段名 来命名
  • 主键索引 pk_ 来命名
  • 单张表索引数量建议控制在5个以内
  • 合理添加索引可以改善查询,但会减慢更新,并不是索引越多越好,索引字段数最好不超过表字段的20%
  • 字符字段必须建前缀索引, like '%name%' 不会使用索引
  • 不在索引列做运算,否则无法使用索引,导致全表扫描
  • 尽量不用外键,高并发时容易死锁(外键使得表之间相互耦合,影响update/delete等SQL性能,有可能造成死锁,高并发情况下容易成为数据库瓶颈)
  • 非必要不要进行 JOIN 查询,如果要进行 JOIN 查询,被 JOIN 的字段必须类型相同,并建立索引
  • 理解组合索引最左前缀原则,避免重复建设索引,如果建立了(a,b,c),相当于建立了(a), (a,b), (a,b,c)
  • 防止因字段类型不同造成的隐式转换,导致索引失效
  • 避免负向查询和%前缀模糊查询,使用不了索引,导致全表扫描

哪些字段上建立索引效果好?

  • 索引列应该建立在数据区分度值大的列上,区分度越大,数据的不重复率就越大,新建索引效果就越好。

为什么要创建组合索引呢?

  • 在MySQL中执行查询时,只能使用一个索引,如果我们在多列(last_name、first_name、age)上分别建索引,执行查询时,只能使用一个索引,MySQL会选择一个最严格(获得结果集记录数最少)的索引。
  • 理解组合索引最左前缀原则,避免重复建设索引,如果建立了(a,b,c),相当于建立了(a), (a,b), (a,b,c)
  • 如果有一个组合索引(col_a,col_b,col_c),下面的情况都会用到这个索引:

col_a = “value”;

col_a = “value” and col_b = “value”;

col_a = “value” and col_b = “value” and col_c = “value”;

col_b = “value” and col_a = “value” and col_c = “value”;

  • 对于最后一条语句,MySQL会自动优化成第三条一样( =in 可以乱序,比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,MySQL的查询优化器会优化成索引可以识别的形式)。下面的情况就不会用到索引:

col_b = “value”;

col_b = “value” and col_c = “value”;

理解单列索引、多列索引以及最左前缀原则

示例: 现在我们想查出满足以下条件的用户id:

mysql>SELECT `id` FROM user WHERE last_name`=’L’ AND `first_name`=’ZW’ AND `age`= 30 ,因为我们不想扫描整表,故考虑用索引。

单列索引:

ALTER TABLE user ADD INDEX last_name (last_name);

将lname列建索引,这样就把范围限制在 last_name='L' 的结果集1上,之后扫描结果集1,产生满足 first_name='ZW' 的结果集2,再扫描结果集2,找到 age=30 的结果集3,即最终结果。

由于建立了 last_name 列的索引,与执行表的完全扫描相比,效率提高了很多,但我们要求扫描的记录数量仍旧远远超过了实际所需要的。虽然我们可以删除 last_name 列上的索引,再创建 first_name 或者age 列的索引,但是,不论在哪个列上创建索引搜索效率仍旧相似。

多列索引:

ALTER TABLE user ADD INDEX lname_fname_age (last_ame,first_name,age);

为了提高搜索效率,我们需要考虑运用多列索引,由于索引文件以B-Tree格式保存,所以我们不用扫描任何记录,即可得到最终结果。**多列索引中其实还有一个字段的先后顺序问题,一般是将区分度较高的放在前面,这样联合索引才更有效。 **

最左前缀:

顾名思义,就是最左优先,上例中我们创建了 lname_fname_age 多列索引,相当于创建了(last_name)单列索引,(last_name,first_name)组合索引以及(last_name,first_name,age)组合索引。

注:在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。 MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配

在什么情况下建立索引呢?

一般来说,在 WHEREJOIN 中出现的列需要建立索引。

索引的不足之处

  • 上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:

虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

使用索引的注意事项

  • 索引不会包含有NULL值的列

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

  • 使用短索引

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

  • 索引列排序

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

  • like语句操作

一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

  • 不要在列上进行运算

select id from user where YEAR(adddate)<2007;

将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成

select id from user where adddate<‘2018-01-01’;

  • 不使用NOT IN、<>、BETWEEN操作

总之,养成一个好习惯吧,把SQL发布生产环境前用 explain 跑一下!