当前位置:Gxlcms > mysql > MySQL怎么使用索引

MySQL怎么使用索引

时间:2021-07-01 10:21:17 帮助过:26人阅读

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索引在进行=, >, >=, <, <=, 或者 BETWEEN等比较操作的时候能够被使用,在使用LIKE操作符时,如果操作的字符串是常量,且不以通配字符开头,也可以使用索引,如下所示的select查询将使用索引:

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就会使用索引,这种只返回少量行的操作,通过索引会更快。

?

人气教程排行