MySQL索引总结

Mon 30 January 2012
By dqw

索引简介

系统中最频繁的操作应该就是查询操作了，使用索引可快速访问表中的特定信息，提高查询和排序的操作效率。在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。 索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。当表中有大量记录时如果没有索引，查询时需要对表中的记录逐一匹配，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作。索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引的方式与书籍中的索引的方式很相似，它搜索索引以找到特定值，然后顺指针找到包含该值的行。

MySQL的索引类型

1、普通索引

基本的索引类型，没有唯一性之类的限制，是最常用的索引类型。

2、唯一性索引

这种索引和普通索引基本相同，但索引列的所有值都只能出现一次，即必须唯一。

3、主键

主键是一种唯一性索引，它必须指定为“PRIMARY KEY”,每个表只能有一个主键，并且不能有null值。

主键的选择

1)主键的主要作用就是唯一的标识一行，所以主键不应该与业务和逻辑有太多关系。

2)优先使用自增Integer类型作为主键使用。

3）尽量避免使用字符类型作为主键，因为字符类型比Integer类型占用更多存储空间，而且排序和查找也比Integer效率低。

全文索引Full-text

目前MySQL中只有MyISAM存储引擎支持全文索引，而且仅支持CHAR、VARCHAR、TEXT三种数据类型。一般来讲，全文索引用于替代效率低下的LIKE操作。由于全文索引开销比较大，且对中文支持问题在使用时需要提前做好评估。

组合索引

多个列组合在一起的索引，可以包含15个列，但是会占用更多的存储空间，实际使用时需要根据实际情况做出评估。

索引的优点

  1. 索引可以大幅提高数据的检索速度，数据量越大时，索引的效率越高　　
  2. 主键索引和唯一索引可以保证数据库表中每一行数据的唯一性 　　
  3. 在使用group by和order by检索数据时，可以显著减少查询中分组和排序的时间

索引的缺点

  1. 索引需要占用额外的存储空间，如果有多个组合索引，会占用相当多的磁盘空间，有时索引所占的磁盘空间会比主表还要多。
  2. 在增删改数据时需要额外的开销来维护索引，如果索引较多会对整体性能有比较大的影响。所以使用索引前要对系统的整体进行分析，分析需要建立的表是以查询为主还是以写入为主，然后再决定是否建立索引。

索引的使用原则和注意事项

  1. 主要的查询字段和排序字段应该建立索引。
  2. 过滤效果不佳的字段不应该建立索引，通过该字段并不能有效的过滤出较少的数据集合，这时索引并不能发挥出作用，有时甚至比全表扫描还要慢。
  3. 更新过于频繁的字段不应该建立索引，由于表更新时会同时维护索引，如果更新过于频繁，会对整体性能有很大的影响。
  4. 不等比较时不能使用索引，比如’!=’、’ <>‘、 NOT。
  5. 函数或运算后的字段不能使用索引，比如abs(col)、col+1等。
  6. 使用like语句时，以通配符开头不能使用索引，比如LIKE ‘%key’。
  7. 字段类型和关键字类型不统一时，造成的隐式类型转换，可能会使索引失效。比如字段col的类型为字符型，查询语句为 “where col = 123″，这样MySQL会隐式转换类型，无法使用索引，造成查询效率低下。
  8. 由于MySQL Query Optimizer通常只会选择一个索引，索引具体建立索引时需要根据具体的查询情况，分析使用要建立组合索引还是单一索引，哪个字段需要建立索引。并不是把所有的查询字段都建立索引就能提高检索效率，这样不仅浪费了更多的存储空间还有可能使Query Optimizer错误的选择了不合适的索引，造成查询效率低下。
  9. 尽可能使用短的索引字段，可以提高检索效率占用更少的存储空间。

Explain的使用

通过使用Explain命令，可以让MySQL Query Optimizer显示出查询语句的执行计划。

比如：EXPLAIN SELECT * FROM goods WHERE goods_id = 40；执行计划显示这是一个简单查询，使用了主键索引。

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE goods const PRIMARY PRIMARY 4 const 1

select_type

SIMPLE:简单SELECT(不使用UNION或子查询)

PRIMARY: 最外面的SELECT

UNION: UNION中的第二个或后面的SELECT语句

DEPENDENT UNION: UNION中的第二个或后面的SELECT语句，取决于外面的查询

UNION RESULT: UNION的结果

UBQUERY:子查询中的第一个SELECT

DEPENDENT SUBQUERY:子查询中的第一个SELECT，取决于外面的查询

DERIVED:导出表的SELECT(FROM子句的子查询)

主要的type类型，从优到劣

Const:表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。Const类型速度很快。

Eq_ref:最多匹配一条记录，一般通过主键或者唯一索引访问。

Ref:对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。

index_merge:该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

Range:索引范围扫描。 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。

Index:全索引扫描。该联接类型与ALL相同，除了只有索引树被扫描。通常比ALL快，因为索引文件通常比数据文件小。

ALL:全表扫描，没有可用的索引。

Possible_keys

可以使用的索引列表

Key

选中使用的索引

Key_len

索引长度

Extra

Using filesort:使用了ORDER BY语句但是没有可用索引的时候使用的排序算法，性能会有影响。

Using index：只需要查询索引即可获取数据是一种比较理想的方式。

Using temporary：使用了临时表，性能比较差，一般需要优化。

使用Profiling 分析查询性能

MySQL 5.0.37以上版本提供了MySQL Query Profiler,可以查询到此SQL会执行多少时间,并看出CPU/Memory使用量,执行过程中System lock, Table lock花多少时间等等，Query PRofiler是一个非常方便的Query诊断分析工具，可以用来分析查询语句的性能，分析语句执行过程中的资源消耗情况。

比如：select * from goods where goods_id >10;

状态 时间
starting 0.000075
Opening tables 0.000013
System lock 0.000005
Table lock 0.000006
init 0.000044
optimizing 0.000007
statistics 0.000041
preparing 0.000018
executing 0.000002
Sending data 0.000738
end 0.000016
query end 0.000002
freeing items 0.001547
logging slow query 0.000005
cleaning up 0.000006

Pages

Categories

Tags