MySQL如何使用索引

MySQL如何使用索引

索引用来快速查找指定列值的行。没有索引,MySQL必须从第一行开始,读取整个表来寻找相关的行。表越大,成本越高。如果被查找的表有索引,MySQL可以在数据文件中快速定位,而不用查找所有的数据。这要比按顺序读取每一行快得多。

大多数MySQL索引(PRIMARY KEY,UNIQUE,KEY,FULLTEXT)存储在B-TREE中,除了:空间数据类型的索引使用R-TREE;MEMORY表使用hash索引;InnoDB的全文索引使用倒排索引。

MySQL使用索引做这些操作:

  • 快速匹配匹配WHERE子句的行。

  • 剔除行。如果在多个索引中做选择,MySQL通常使用匹配最少行的索引。(选择性最好的索引)

  • 如果一个表上有多列索引,任何最左前缀索引都能被优化器使用来查找行。例如:如果你有一个三列索引(col1,col2,col3),那么(col1),(col1,col2),(col1,col2,col3)都能利用索引搜索。

  • 执行连接的时候从其他表获取行。如果列的类型被声明为同样大小和类型,MySQL能更有效率的使用索引。在这种背景下,VARCHAR和CHAR被认为是一样的如果他们的大小相同。例如:VARCHAR(10)和CHAR(10)是相同的大小,VARCHAR(10)和CHAR(15)不同。

    为了比较非二进制字符串列,所有的列应该使用同样的字符集。例如:比较utf8和latin1列不能使用索引。

    对比不相似的列(例如:将比较字符串类型与时间类型或者数字类型比较),如果值在没有转换的情况下不能直接被比较,可能会阻碍索引的使用。对于一个给定的数字列1,它可能和字符串列的‘1’,‘ 1’,‘0001’,‘01.e1’相等。这就情况应排除使用任何字符串做索引。

  • 为指定的索引列key_col寻找MIN(),MAX()值。预处理器做了优化:在计算key_col前检查是否在所有的索引部分使用了WHERE key_part_n = 常量,在这种情况下,MySQL为每个MIN()和MAX()表达式做单独的key循环,并且用常量替换它,如果所有的表达式被常量替换,查询被立即返回。(这里不好理解,大概意思是针对多列索引,索引前缀在where子句中等于常量,那么MIN和MAX只在前缀索引过滤后的行中进行计算)例如:

    1
    2
    SELECT MIN(key_part2),MAX(key_part2)
    FROM tbl_name WHERE key_part1=10;
  • 如果排序和分组被作用在索引的最左前缀上(例如:ORDER BY key_part1, key_part2),那么使用索引来排序和分组表。如果所有的索引部分跟着DESC,key被倒序读取(索引的排序应该按相同的规则,即都是正序或倒序)。

  • 在某些情况下,查询能被优化来获取值而不需要访问数据行(提供了所有必要的查询结果索引叫覆盖索引)。如果查询仅使用包含在索引中的某些列,被查找的值在索引树中被极快的返回:

    1
    2
    SELECT key_part3 FROM tbl_name
    WHERE key_part1=1

在小表上的查询,或者在大表上查询返回大多数或者所有的行,索引就不那么重要了。当查询需要访问大多数行,顺序读取比使用索引快。顺序读取有最小化的磁盘寻道,即使查询不需要所有的行。

(完)