安装演示数据库之最佳实践-亲试ok
[root@Slave-114 ~]# wget http://downloads.mysql.com/docs/sakila-db.tar.gz
--2020-07-02 03:16:05-- http://downloads.mysql.com/docs/sakila-db.tar.gz
HTTP request sent, awaiting response... 301 Moved Permanently
--2020-07-02 03:16:06-- https://downloads.mysql.com/docs/sakila-db.tar.gz
HTTP request sent, awaiting response... 200 OK
Length: 732126 (715K) [application/x-gzip]
Saving to: ‘sakila-db.tar.gz’
100%[===================================================================================================================>] 732,126 236KB/s in 3.0s
2020-07-02 03:16:10 (236 KB/s) - ‘sakila-db.tar.gz’ saved [732126/732126]
[root@Slave-114 ~]# ls -ltr
total 724
-rw-r--r--. 1 root root 732126 Jun 4 15:10 sakila-db.tar.gz
[root@Slave-114 ~]# tar -zxf sakila-db.tar.gz
mysql> use test;
Database changed
mysql> source /root/sakila-db/sakila-schema.sql;
Query OK, 0 rows affected (0.00 sec)
mysql> source /root/sakila-db/sakila-data.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
开始测试
case1
mysql> explain select language_id from film where language_id=1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
partitions: NULL
type: ref
possible_keys: idx_fk_language_id
key: idx_fk_language_id
key_len: 1
ref: const
rows: 1000
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
解析：列 language_id 有索引，且select 部分只取了 language_id ，因此该值完全可以调用index 并从index中获取。即 覆盖索引
case2
mysql> explain select * from film where language_id=1\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
partitions: NULL
type: ALL
possible_keys: idx_fk_language_id
key: NULL
key_len: NULL
ref: NULL
rows: 1000
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (1.58 sec)
解析：列 language_id 有索引，可是select 部分取了所有字段 ，因此该值完全可以调用index 并从全表扫描取出所有数据 然后从index 中过滤数据
case3
mysql> explain select actor_id,last_name from actor where last_name='Joe'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
partitions: NULL
type: ref
possible_keys: idx_actor_last_name
key: idx_actor_last_name
key_len: 182
ref: const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
解析：last_name、actor_id 俱有index，因此该查询完全可以从index中获取数据
创建时间：2021-12-22 10:43
넶浏览量：0
