欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL怎么使用索引

程序员文章站 2022-05-17 11:47:45
...

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

?