MySQL中能够使用索引的典型场景

MySQL中能够使用索引的典型场景
(1)匹配全值;
(2)匹配值的范围查询,对索引的值能够进行范围查找;
(3)匹配最左前缀,仅仅使用索引中的最左边列进行查找;
(4)仅仅对索引进行查询,查询的列都在索引的字段中时,查询的效率较高;
(5)匹配列前缀,仅仅使用索引中的第一列,并且只包含索引第一列的开头一部分进行查找;
(6)能够实现索引匹配部分精确而其他部分进行范围查询;
(7)如果列名是索引,那么使用column_name is null 就会使用到索引(区别于oracle)

下面以t_user举例说明

create table t_user (id integer not null auto_increment primary key, name varchar(30), salary decimal(7, 2), bonus decimal(7,2), sex tinyint(1));

alter table t_user add index index_name_salary_bonus(name, salary, bonus);

(1)select * from t_user where name = ‘zhangsan‘;

mysql> explain select * from t_user where name = ‘zhangsan‘ \G;
*************************** 1. row ***************************
            id: 1
  select_type: SIMPLE
        table: t_user
         type: ref
possible_keys: index_name_salary_bonus
          key: index_name_salary_bonus
      key_len: 93
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

ERROR:
No query specified

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

ERROR:

No query specified

(2)select * from t_user where name = ‘zhangsan‘ and salary between 1.2 and 2.4;

mysql> explain select * from t_user where name = ‘zhangsan‘ and salary between 1.2 and 2.4 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_user
         type: range
possible_keys: index_name_salary_bonus
          key: index_name_salary_bonus
      key_len: 98
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

ERROR:
No query specified
(3)select * from t_user where salary between 1.2 and 2.4;

mysql> explain select * from t_user where salary between 1.2 and 2.4 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_user
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)

ERROR:
No query specified
(4)select name from t_user where salary between 1.2 and 2.4;
mysql> explain select name from t_user where salary between 1.2 and 2.4 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_employee
         type: range
possible_keys: idx_salary_bonus
          key: idx_salary_bonus
      key_len: 5
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

ERROR:
No query specified

(5)select name from t_user where name like ‘zhang%‘;

mysql> explain select name from t_user where name like ‘zhang%‘ \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_employee
         type: range
possible_keys: idx_salary_bonus
          key: idx_salary_bonus
      key_len: 5
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

ERROR:
No query specified

(6)select * from t_user where name = ‘zhangsan‘ and sex like ‘%1%‘ ;

mysql> explain select * from t_user where name = ‘zhangsan‘ and sex like ‘%1%‘ \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_employee
         type: ref
possible_keys: idx_salary_bonus
          key: idx_salary_bonus
      key_len: 5
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

ERROR:
No query specified

(7)select * from t_user where name is null ;

mysql> explain select * from t_user where name is null \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_employee
         type: ref
possible_keys: idx_salary_bonus
          key: idx_salary_bonus
      key_len: 5
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

 

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