mysql 索引 使用注意细节

在查询时,如果使用到LIKE关键字,就要注意有没有使用到索引;

 

  1. 没有使用索引前:

    mysql> explain select * from employees where first_name=‘Georgi‘\G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: employees
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 294382
            Extra: Using where
    1 row in set (0.00 sec)

  2. 添加索引: create index first_index on employees (first_name);

  3. 索引后:

     

    mysql> explain select * from employees where first_name=‘Georgi‘\G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: employees
             type: ref
    possible_keys: first_index
              key: first_index
          key_len: 16
              ref: const
             rows: 253
            Extra: Using where
    1 row in set (0.00 sec)

  4. 查询中使用LIKE:

    mysql> explain select * from employees where first_name like ‘%Georgi‘\G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: employees
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 294382
            Extra: Using where
    1 row in set (0.05 sec)

  5. mysql> explain select * from employees where first_name like ‘%Georgi%‘\G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: employees
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 294382
            Extra: Using where
    1 row in set (0.00 sec)

  6. mysql> explain select * from employees where first_name like ‘Georgi%‘\G;
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: employees
             type: range
    possible_keys: first_index
              key: first_index
          key_len: 16
              ref: NULL
             rows: 253
            Extra: Using where
    1 row in set (0.00 sec)

 

可以发现只有在查询关键字后面的加%才可以用到索引;

还有使用了is not null,<>都不可以用到索引:

mysql> explain select * from employees where first_name is not null\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
         type: ALL
possible_keys: first_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 294382
        Extra: Using where
1 row in set (0.00 sec)

 

mysql> explain select * from employees where first_name <> ‘Georgi‘\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
         type: ALL
possible_keys: first_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 294382
        Extra: Using where
1 row in set (0.00 sec)

 

如果在索引的列上面用了函数或运算都不能引用到索引的:

mysql> explain select * from employees where UPPER(first_name)=‘Ult‘\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 294382
        Extra: Using where
1 row in set (0.00 sec)

 

在使用复合索引的时候也要注意第一列有没有在WHERE中;

正确使用索引的:

mysql> explain select * from employees where first_name=‘Georgi‘ and last_name=‘
Facello‘\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
         type: ref
possible_keys: name_index
          key: name_index
      key_len: 34
          ref: const,const
         rows: 2
        Extra: Using where
1 row in set (0.00 sec)

mysql> explain select * from employees where first_name=‘Georgi‘\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
         type: ref
possible_keys: name_index
          key: name_index
      key_len: 16
          ref: const
         rows: 253
        Extra: Using where
1 row in set (0.00 sec)

 

没有使用到索引的:

mysql> explain select * from employees where last_name=‘Facello‘\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 300252
        Extra: Using where
1 row in set (0.00 sec)

 

mysql> explain select * from employees where last_name=‘Facello‘ or first_name=‘
Georgi‘\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
         type: ALL
possible_keys: name_index
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 300252
        Extra: Using where
1 row in set (0.00 sec)

第二个例子因为用了OR所以WHERE中也只是对first_name单独检索;

mysql 索引 使用注意细节,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。