MySQL-索引优化篇(1)_安装演示库 & [前缀索引、联合索引、覆盖索引] & explain参数

[root@artisan ~]# wget http://downloads.mysql.com/docs/sakila-db.tar.gz
.....
.....
.....
2020-02-01 21:31:15 (2.74 KB/s) - ‘sakila-db.tar.gz’ saved [732161/732161]

[root@artisan ~]# tar -xvzf sakila-db.tar.gz 
sakila-db/
sakila-db/sakila-data.sql
sakila-db/sakila-schema.sql
sakila-db/sakila.mwb
[root@artisan ~]# cd sakila-db
[root@artisan sakila-db]# ls
sakila-data.sql  sakila.mwb  sakila-schema.sql
[root@artisan sakila-db]# mysql -uroot -p < sakila-schema.sql 
Enter password: 
[root@artisan sakila-db]# mysql -uroot -p < sakila-data.sql 
Enter password: 
[root@artisan sakila-db]# 

select  .... from t_order
where to_days(out_date) - to_days(current_date) < = 30 

select  .... from t_order
where out_date <= data_add(current_date , interval 30 day) ; 

create index index_name on table(col_name(n)); 

[root@artisan sakila-db]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 63
Server version: 5.7.29-log MySQL Community Server (GPL)
.......
.......
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| artisan            |
| artisanBinLog      |
| data               |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
+--------------------+
8 rows in set (0.01 sec)

mysql> use sakila;
No connection. Trying to reconnect...
Connection id:    64
Current database: *** NONE ***

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_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.00 sec)

mysql> desc film;
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field                | Type                                                                | Null | Key | Default           | Extra                       |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| film_id              | smallint(5) unsigned                                                | NO   | PRI | NULL              | auto_increment              |
| title                | varchar(128)                                                        | NO   | MUL | NULL              |                             |
| description          | text                                                                | YES  |     | NULL              |                             |
| release_year         | year(4)                                                             | YES  |     | NULL              |                             |
| language_id          | tinyint(3) unsigned                                                 | NO   | MUL | NULL              |                             |
| original_language_id | tinyint(3) unsigned                                                 | YES  | MUL | NULL              |                             |
| rental_duration      | tinyint(3) unsigned                                                 | NO   |     | 3                 |                             |
| rental_rate          | decimal(4,2)                                                        | NO   |     | 4.99              |                             |
| length               | smallint(5) unsigned                                                | YES  |     | NULL              |                             |
| replacement_cost     | decimal(5,2)                                                        | NO   |     | 19.99             |                             |
| rating               | enum('G','PG','PG-13','R','NC-17')                                  | YES  |     | G                 |                             |
| special_features     | set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') | YES  |     | NULL              |                             |
| last_update          | timestamp                                                           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------------------+---------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
13 rows in set (0.00 sec)


#  查询 索引列  language_id    重点看  Extra 



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.07 sec)

ERROR: 
No query specified

# 查看执行计划   重点看  Extra 

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  -------------------->   using where：表示优化器需要通过索引回表查询数据；
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> 

mysql> show create table actor \G ;
*************************** 1. row ***************************
       Table: actor
Create Table: CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

ERROR: 
No query specified

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   ------> using index 表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表；
1 row in set, 1 warning (0.30 sec)

        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