mysql索引使用原则
索引使用的一些原则
(1)表一定要有主键,显式定义主键且采用与业务无关的列以避免修改。InnoDB表在有主键时会自动将主键设为聚集索引,建议采用自增列来使数据顺序插入。
(2)关于合理添加索引,有一个通常的法则,即对于经常被查询的列、经常用于表连接的列、经常排序分组的列,需要创建索引。
(3)创建索引之前,还要查看索引的选择性(不重复的索引值和表的记录总数的比值)来判断这个字段是否合适创建索引。索引的选择性越接近于1,说明选择性越高,非常适合创建索引。
(4)组合索引(表中两个或两个以上的列上创建的索引),一般把选择性高的列放在前面。组合索引字段数不建议超过5个,如果5个字段还不能极大地缩小row范围,那么肯定是设计有问题。
(5)合理利用覆盖索引(只需通过索引就可以返回查询所需要的数据,不必在查到索引之后再回表查询数据)。禁止使用select *,只获取必要字段,指定字段能有效利用索引覆盖。
(6)使用explain判断SQL语句是否合理使用了索引,尽量避免Extra列出现Using File Sort、Using Temporary。
(7)单张表的索引数量建议控制在5个以内,索引太多也会浪费空间且降低修改数据的速度,影响性能。
(8)不建议在频繁更新的字段上建立索引。
(9)Where条件中的索引列不能是表达式的一部分,避免在Where条件中在索引列上进行计算或使用函数,因为这将导致索引不被使用而进行全表扫描。
(10)如果要进行join查询,那么被join的字段必须类型相同并建立索引,因为join字段类型不一致会导致全表扫描。
(11)隐式类型转换会使索引失效,导致全表扫描。
以下演示违反索引使用规则的情形,会导致全表扫描。
(1)案例一:在表tt1中,给字段log_time增加了索引,如图4-28所示。
通过explain查看查询SELECT * FROM t1 WHERE DATE(log_time)='2015-04-09'的执行计划,如图4-29所示。我们发现type列是ALL,这条语句进行了一个全表扫描。虽然给字段log_time加了索引,但是没有用到索引,因为违背避免在Where条件中在索引列上进行计算或使用函数。在MySQL里,一般修改为SELECT * FROM t1 WHERE log_time >= '2015-04-09 00:00:00' AND log_time <= '2015-04-10 00:00:00',通过explain查看查询执行计划时使用到了索引。MySQL 5.7的虚拟列(Generated Columns)特性实现表达式索引,就是用来解决这个问题的。可以增加一个可被索引的列,但实际上并不存在于数据表中。可以对log_time创建一个虚拟列,然后对虚拟列创建索引,但是后期会增加运维人员的运维难度。
(2)案例二:在表test_2中,SQL语句非常简单,就是“select id,user_id,name from test_1 where user_id=1”这种类型,而且user_id上已经建立索引,查询还是很慢,如下图所示,利用explain查看语句执行计划,发现进行了全表扫描,type列还是ALL,并没有用上user_id的索引。
认真观察一下表结构,user_id的字段类型是字符串,而用户传入的是int,这里会有一个隐式转换的问题。隐式类型转换会使索引失效,导致全表扫描。把输入改成字符串类型,查看执行计划“explain select id, user_id, name from test_2 where user_id='1'”,就会用到索引