学习笔记(14):MySQL数据库从入门到搞定实战-索引的基本使用

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

没有索引：全表扫描，效率低

 1、创建索引

创建普通索引：

creat index indexName on tableName(columnName(length));

创建唯一索引：

creat unique index indexName on tableName(columnName(length));

创建复合索引：

creat index indexName on tableName(columnName1,columnName2......);

删除索引：

drop index [indexName] on tableName;

查看索引：

show index from tableName;

 

 使用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)

 

show index from employee\G;
*************************** 1. row ***************************
        Table: employee
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 13
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

ERROR:
No query specified

主键默认建立一个索引

 

explain select * from employee where name='柳峰';

 +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employee | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   13 |    10.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

 

建立索引：

create index idx_name on employee(name);

 

explain select * from employee where name='柳峰';
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | ref  | idx_name      | idx_name | 123     | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+

1 row in set, 1 warning (0.00 sec) 

 

删除索引： 

drop index idx_name on employee(name);