MySQL怎么使用索引

MySQL如何使用索引 ? ? ?给定特定的列的值查找满足条件的行，索引的使用能够加快查找的速度。如果没有索引，MySQL将从第一行记录开始，穿越整个表找到相应的记录，表越大，相应的查询的代价也就越大。如果针对查询中的列有索引，MySQL就能在数据文件中快速确

MySQL如何使用索引

? ? ?给定特定的列的值查找满足条件的行，索引的使用能够加快查找的速度。如果没有索引，MySQL将从第一行记录开始，穿越整个表找到相应的记录，表越大，相应的查询的代价也就越大。如果针对查询中的列有索引，MySQL就能在数据文件中快速确定需要查找的位置，再也不用穿越整个表来捞数据了。如果一个表有1000条数据，这样至少能比整表顺序读取捞数据快100倍。如果你的查询结果包含了整表的大部分记录，它也比没有索引整表捞数据要快，至少减少了磁盘的寻址时间。

?

? ? 大部分的MySQL索引(PRIMARY KEY, UNIQUE, INDEX, FULLTEXT)都是以B-Tree结构来存储，而空间数据索引则使用R-Tree结构来存储，内存表则使用哈希索引。

?

? ? 字符串在创建索引时会自动去除首尾的空白。

?

? ? MySQL会在以下操作时使用索引：

• 快速查找匹配where语句的行记录时。
• 预计能够缩小结果的范围时。如果查询能够匹配多个索引，MySQL一般会使用能够过滤出结果最少的索引。
• join操作时从其他表捞数据。在join时，如果声明关联的列类型和大小相同，MySQL在使用索引时能够更加高效。在这里，如果VARCHAR 和CHAR的大小相同，他们在类型上会被认为是相同的。例如VARCHAR(10)和CHAR(10)是大小相同的，但是VARCHAR(10)和CHAR(15)的大小是不同的。

? ? ? ?在两个不同的列之间进行比较，例如string和temporal，或者numeric，不能方便直接进行比较，将妨碍 ?

? ? ? ?索引的使用。假设一个numeric列和一个string列进行比较，对于numeric列中给定的一个值，比如1，它可能会和

? ? ? ? ?string中的很多值相同，例如：'1', ' 1', '00001', 或者 '01.e1'。string列上的任何索引对这种查询没有任何意义和帮助。

• 获取已有索引列的MIN()、MAX()值。在执行这两个聚合函数的时候，预处理过程会在使用该列的索引之前会首先检查where语句中是否包含有其他索引列的等于限定条件，并从该索引中分别查询一次MIN和MAX，并将获取到的常量值返回，整个查询将一次返回，而不用做原始列的全索引扫描。例如：在已有索引的列column1上获取其MIN、MAX值，如果在where中包含有”column2=常量“，而column2、column1构建有复合索引，这种情况下，MySQL将不会查找column1的索引，而是在column2、column1的复合索引中进行查找，并能一次获取到结果，不用穿越整个索引。
• 如果在一个已经排序并分组的最左前缀索引上执行sort或者group，例如：ORDER BY key_part1, key_part2，key_part1, key_part2是复合索引的列，如果所有的key都是DESC的，key将会反序读取。
• 在某些情况下，一个查询通过优化，可以不用通过获取行数据而得到结果。如果一个查询只使用了numeric列，并且这些列参与构建了最左前缀索引，那么MySQL可以直接从索引中获取到需要的结果，而不用访问具体的数据。这也就是所谓的”覆盖索引“，例如：

  SELECT key_part3 FROM tbl_name
    WHERE key_part1=1

  ?key_part1、key_part3属于一个最左前缀索引。假设执行以下的SQL语句：

  SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

  ?如果在col1、col2上有一个复合索引，对应的查询结果就能直接获取到。如果在col1、col2只有分别的单列索引，优化器就会尝试使用索引合并进行优化，或者看哪个索引返回的结果集更好，然后根据该结果集去表中读取数据。

? ? ? ? ?如果该表有一个多列索引，该索引的任意最左前缀都能被优化器使用。例如，如果在(col1, col2, col3)上有一个三列索引，则基于(col1)、(col1,col2)、(col1,col2,col3)的查询都会使用到该索引。

? ? ? ? 如果使用的列不能构成一个最左前缀，MySQL就无法使用索引了，假设有如下的SQL查询：

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

? ? ? ?如果在(col1, col2, col3)构建索引，那么就只有头两个SQL查询能够使用索引。第三个、第四个查询虽然也使用到了被索引的列，但是(col2) 和(col2, col3)不是(col1, col2, col3)的最左前缀。

?

?

B-Tree索引的特性

? ? ? ? B-Tree索引在进行=, >, >=,

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

? ? ? ? 而下面的语句将不会使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

? ? ? ? 在第一句中，LIKE操作的字符串以通配符开头，而第二句中，LIKE操作的不是一个常量字符串。

? ? ? ? 如果使用... LIKE '%string%'， 并且string不超过三个字符，MySQL将使用Turbo Boyer-Moore算法来初始化这个字符串模式，然后使用这个模式进行快速查找。

?

? ? ? ? 对于创建了索引的列col_name,如果在where中包含有col_name is NULL，在操作时，MySQL也将使用索引。

?

? ? ? ? 在一个AND组中，必须包含有索引前缀，才能在执行过程中使用索引，下面的WHERE语句将使用索引：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3
    /* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2
    /* optimized like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5
    /* 能在index1上使用索引，不能再index2或者index3上使用索引 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

? ? ? ? ?下面的WHERE语句无法使用索引：

/* index_part1索引没有被使用 */
... WHERE index_part2=1 AND index_part3=2

    /*  两部分的索引都没有使用  */
... WHERE index=1 OR A=10

    /* 没有索引跨越所有行  */
... WHERE index_part1=1 OR index_part2=10

? ? ? ? ?

?

? ? ? ?有时候，及时有索引满足条件，MySQL也不会使用它，会发生这种状况的一种情形是MySQL优化器认为使用索引会导致对整表很大一部分数据的访问，在这种情况下，直接的全表扫描可能更快，它花费的寻址时间更少。不过，如果这种查询使用limit限定只返回结果中的部分行，MySQL就会使用索引，这种只返回少量行的操作，通过索引会更快。

?