mysql中索引的存储 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 specifiedmysql> 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)