学习笔记(15):MySQL数据库从入门到搞定实战-复合索引前导列特性

立即学习:https://edu.****.net/course/play/27328/362533?utm_source=blogtoedu

 

使用employee表：

+----+--------+------+--------+-------+
| id | name   | sex  | salary | dept  |
+----+--------+------+--------+-------+
|  1 | 张三   | 男   |   5500 | 部门A |
|  2 | 李洁   | 女   |   4500 | 部门C |
|  3 | 李小梅 | 女   |   4200 | 部门A |
|  4 | 欧阳辉 | 男   |   7500 | 部门C |
|  5 | 李芳   | 女   |   8500 | 部门A |
|  6 | 张江   | 男   |   6800 | 部门A |
|  7 | 李四   | 男   |  12000 | 部门B |
|  8 | 王五   | 男   |   3500 | 部门B |
|  9 | 马小龙 | 男   |   6000 | 部门A |
| 10 | 龙五   | 男   |   8000 | 部门B |
| 11 | 冯小芳 | 女   |  10000 | 部门C |
| 12 | 马小花 | 女   |   4000 | 部门B |
| 13 | 柳峰   | 男   |   8800 | 部门A |
+----+--------+------+--------+-------+

 

show index from employee;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employee |          0 | PRIMARY  |            1 | id          | A         |          13 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.01 sec)
 

创建索引：

create index idx_name_salary_dept on employee(name,salary,dept);

 查询：

explain select * from employee where name='柳峰'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: ref
possible_keys: idx_name,idx_name_salary_dept
          key: idx_name
      key_len: 123
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

 

explain select * from employee where name='柳峰' and salary =8800\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: ref
possible_keys: idx_name,idx_name_salary_dept
          key: idx_name
      key_len: 123
          ref: const
         rows: 1
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

 

 explain select * from employee where name='柳峰' and dept='部门A'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: ref
possible_keys: idx_name,idx_name_salary_dept
          key: idx_name
      key_len: 123
          ref: const
         rows: 1
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified