59. MySQL索引是如何提高查询效率的呢?(MySQL面试第二弹)中
考虑维度优势
例如action列的值包含:1、2、3、4、5,那么该列的维度就是5。
维度越高(理论上维度的最大值就是数据行的总数),数据列包含的独一无二的值就越多,索引的使用效果越好。
对于维度很低的数据列,索引几乎不会起作用,因此没有必要加索引。
例如性别列的值只有男和女,每种查询结果占比大约50%。一般当查询优化处理器发现查询结果超过全表的30%的时候,就会跳过索引,直接进行全表扫描。
对短小的值加索引
对短小的值加索引,意味着索引所占的空间更小,可以减少I/O活动,同时比较索引的速度也更快。
尤其是主键,要尽可能短小。
另外,InnoDB使用的是聚集索引(clustered index),也就是把主键和数据行保存在一起。主键之外的其他索引都是二级索引,这些二级索引也保留着一份主键,这样在查询到索引以后,就可以根据主键找到对应的数据行。如果主键太长的话,会造成二级索引占用的空间变大。
比如下面的action索引保存了对应行的id。
为字符串前缀加索引
前边已经讲过短小索引的种种好处了,有时候一个字符串的前几个字符就能唯一标识这条记录,这个时候设置索引的长度就是非常划算的做法。
前面已经提供了设置索引length
的例子,这里就不举例子了。
复合索引的左侧索引
创建复合索引的语法如下:
我们可以看到,最左侧的column1索引总是有效的。
索引加锁
对于InnoDB来说,索引可以让查询锁住更少的行,从而可以在并发情况下拥有更佳表现。
下面演示一下查询锁与索引之间的关系。
前面使用的t_user_action_log表目前有一个id为主键,还有一个二级索引action。
下面这条语句的修改范围是id值为1 2 3 4所在的行,查询锁会锁住id值为1 2 3 4 5所在的行。
update ijiangtao_local_db_mysql.t_user_action_log set name='c1' where id<5;
首先创建数据库连接1,开启事务,并执行update语句
set autocommit=0; begin; update ijiangtao_local_db_mysql.t_user_action_log set name='c1' where id<5;
然后开启另外一个连接2,分别执行下面几个update语句
-- 没有被锁 update ijiangtao_local_db_mysql.t_user_action_log set name='c2' where id=6; -- 被锁 update ijiangtao_local_db_mysql.t_user_action_log set name='c2' where id=5;
你会发现id=5
的数据行已经被锁定,id=6
的数据行可以正常提交。
- 连接1提交事务,连接2的
id=1
和id=5
的数据行可以update成功了。
-- 在连接1提交事务 commit;
如果不使用索引
ip_address没有索引的话,会锁定全表。
连接1开启事务以后commit;之前,连接2对该表的update全部需要等待连接1释放锁。
set autocommit=0; begin; update ijiangtao_local_db_mysql.t_user_action_log set name='c1' where ip_address='8.8.8.1';
覆盖索引
如果索引包含满足查询的所有数据,就被称为覆盖索引(Covering Indexes),覆盖索引非常强大,可以大大提高查询性能。
覆盖索引高性能的原因是:
索引通常比记录要小,覆盖索引查询只需要读索引,而不需要读记录。
索引都按照值的大小进行顺序存储,相比与随机访问记录,需要更少的I/0。
大多数数据引擎能更好的缓存索引,例如MyISAM只缓存索引。
ijiangtao_local_db_mysql表的action列包含索引。使用explain分析下面的查询语句,对于索引覆盖查询(index-covered query),分析结果Extra的值是Using index,表示使用了覆盖索引 :
explain select `action` from ijiangtao_local_db_mysql.t_user_action_log;
聚簇索引
聚簇索引(Clustered Indexes)保证关键字的值相近的元组存储的物理位置也相同,且一个表只能有一个聚簇索引。
字符串类型不建议使用聚簇索引,特别是随机字符串,因为它们会使系统进行大量的移动操作。
并不是所有的存储引擎都支持聚簇索引,目前InnoDB支持。
如果使用聚簇索引,最好使用AUTO_INCREMENT列作为主键,应该尽量避免使用随机的聚簇主键。
从物理位置上看,聚簇索引表比非聚簇的索引表,有更好的访问性能。