Mysql之索引
前言：
我们知道Mysql中的表如果没有索引，查询一条数据，只能遍历表中的所有行。如果对某些经常用来查询的字段创建成索引，会增加查询速度。以下示例通过对比有无索引时查询的区别。
一、索引查询
- 重定向导入库
[root@h3ctest2-mysql-master-1-8d904-0 ~]# mysql -h127.0.0.1 -P3306 -uroot -p < hellodb.sql
Enter password:
- 查看导入的库与表
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbtest |
| hellodb |
| mydb |
| mysql |
| performance_schema |
| sys |
| test |
| testdb |
+--------------------+
9 rows in set (0.00 sec)
mysql> USE hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.01 sec)
- 查看表上的索引
mysql> HELP SHOW INDEX
Name: 'SHOW INDEX'
Description:
Syntax:
SHOW {INDEX | INDEXES | KEYS}
{FROM | IN} tbl_name
[{FROM | IN} db_name]
[WHERE expr]
- 查看students表中的索引（主键索引StuID）
mysql> SHOW INDEXES FROM students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+--------- ------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_co mment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+--------- ------+
| students | 0 | PRIMARY | 1 | StuID | A | 25 | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+--------- ------+
1 row in set (0.00 sec)
mysql> SELECT * FROM students WHERE StuID=3;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
| 3 | Xie Yanke | 53 | M | 2 | 16 |
+-------+-----------+-----+--------+---------+-----------+
1 row in set (0.00 sec)
- 查看SELECT查询过程中是否用到索引或获取数据的过程
mysql> EXPLAIN SELECT * FROM students WHERE StuID=3\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
partitions: NULL
type: const #通过StuID索引查询
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM students WHERE age=53\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
partitions: NULL
type: ALL #Age没有索引只能全表查询
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 25
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
- 为Age字段添加索引
mysql> ALTER TABLE students ADD INDEX(Age);
Query OK, 25 rows affected (0.00 sec)
Records: 25 Duplicates: 0 Warnings: 0
mysql> SHOW INDEXES FROM students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students | 0 | PRIMARY | 1 | StuID | A | 25 | NULL | NULL | | BTREE | | |
| students | 1 | Age | 1 | Age | A | NULL | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
- 通过索引查询Age=53的字段
mysql> EXPLAIN SELECT * FROM students WHERE Age=53\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
partitions: NULL
type: ref
possible_keys: Age
key: Age
key_len: 1
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
- 通过Create直接创建索引
mysql> DESC students;
+-----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Name | varchar(50) | NO | | NULL | |
| Age | tinyint(3) unsigned | NO | MUL | NULL | |
| Gender | enum('F','M') | NO | | NULL | |
| ClassID | tinyint(3) unsigned | YES | | NULL | |
| TeacherID | int(10) unsigned | YES | | NULL | |
+-----------+---------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> CREATE INDEX name ON students(name);
Query OK, 25 rows affected (0.00 sec)
Records: 25 Duplicates: 0 Warnings: 0
mysql> SHOW INDEXES FROM students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students | 0 | PRIMARY | 1 | StuID | A | 25 | NULL | NULL | | BTREE | | |
| students | 1 | Age | 1 | Age | A | NULL | NULL | NULL | | BTREE | | |
| students | 1 | name | 1 | Name | A | NULL | NULL | NULL | | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
- 查询name中带有X的数据
mysql> SELECT * FROM students WHERE Name LIKE 'X%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
+-------+-------------+-----+--------+---------+-----------+
6 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM students WHERE Name LIKE 'X%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
partitions: NULL
type: range
possible_keys: name
key: name
key_len: 152
ref: NULL
rows: 6
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
- 查询name中带有X的数据，X前边加%成为全表扫描，用不到索引
mysql> EXPLAIN SELECT * FROM students WHERE Name LIKE '%X%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 25
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
二、视图管理
视图是一个虚表，把SELECT语句查询出来的结果当作表用的。
- 创建视图
mysql> CREATE VIEW test AS SELECT StuID,Name,Age FROM students;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| test |
| toc |
+-------------------+
8 rows in set (0.00 sec)
mysql> SHOW TABLE STATUS LIKE 'test'\G
*************************** 1. row ***************************
Name: test
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.00 sec)
mysql> SELECT * FROM test;
+-------+---------------+-----+
| StuID | Name | Age |
+-------+---------------+-----+
| 1 | Shi Zhongyu | 22 |
| 2 | Shi Potian | 22 |
| 3 | Xie Yanke | 53 |
| 4 | Ding Dian | 32 |
| 5 | Yu Yutong | 26 |
| 6 | Shi Qing | 46 |
| 7 | Xi Ren | 19 |
| 8 | Lin Daiyu | 17 |
| 9 | Ren Yingying | 20 |
| 10 | Yue Lingshan | 19 |
| 11 | Yuan Chengzhi | 23 |
| 12 | Wen Qingqing | 19 |
| 13 | Tian Boguang | 33 |
| 14 | Lu Wushuang | 17 |
| 15 | Duan Yu | 19 |
| 16 | Xu Zhu | 21 |
| 17 | Lin Chong | 25 |
| 18 | Hua Rong | 23 |
| 19 | Xue Baochai | 18 |
| 20 | Diao Chan | 19 |
| 21 | Huang Yueying | 22 |
| 22 | Xiao Qiao | 20 |
| 23 | Ma Chao | 23 |
| 24 | Xu Xian | 27 |
| 25 | Sun Dasheng | 100 |
+-------+---------------+-----+
25 rows in set (0.00 sec)
mysql> SELECT * FROM students WHERE Age=22;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
+-------+---------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM students WHERE Age=22\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: students
partitions: NULL
type: ref
possible_keys: Age
key: Age
key_len: 1
ref: const
rows: 2
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
三、向表中插入数据的三种用法
mysql> HELP INSERT
Name: 'INSERT'
Description:
Syntax:
#传统用法，传统用法，向指定字段中插入一行或多行数据
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
{VALUES | VALUE} (value_list) [, (value_list)] ...
[ON DUPLICATE KEY UPDATE assignment_list]
#通过SET插入
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
SET assignment_list
[ON DUPLICATE KEY UPDATE assignment_list]
#查询表的结果插入表中
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
SELECT ...
[ON DUPLICATE KEY UPDATE assignment_list]
1、向指定字段中插入一行或多行数据
mysql> INSERT INTO students (Name,Age,Gender) VALUES ('Jinjiao King',100,'m');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | Jinjiao King | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)
2、通过SET某一字段等于某一值
mysql> INSERT INTO students SET Name='Yinjiao King',Age=98,Gender='m';
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | Jinjiao King | 100 | M | NULL | NULL |
| 27 | Yinjiao King | 98 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· 写一个简单的SQL生成工具
· AI 智能体引爆开源社区「GitHub 热点速览」
· C#/.NET/.NET Core技术前沿周刊 | 第 29 期（2025年3.1-3.9）