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只在前缀索引过滤后的行中进行计算）例如：

  SELECT MIN(key_part2),MAX(key_part2)
      FROM tbl_name WHERE key_part1=10;
  

• 如果排序和分组被作用在索引的最左前缀上（例如：ORDER BY key_part1, key_part2），那么使用索引来排序和分组表。如果所有的索引部分跟着DESC，key被倒序读取（索引的排序应该按相同的规则，即都是正序或倒序）。

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

   SELECT key_part3 FROM tbl_name
       WHERE key_part1=1
  

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

（完）