索引优化学习记录-20201020
mysql> explain select language_id from file where language_id=1 \G;
ERROR 1146 (42S02): Table 'sakila.file' doesn't exist
ERROR:
No query specified
#1 查询单个数据
mysql> show create table film\G;
*************************** 1. row ***************************
Table: film
Create Table: CREATE TABLE `film` (
`film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`description` text,
`release_year` year(4) DEFAULT NULL,
`language_id` tinyint(3) unsigned NOT NULL,
`original_language_id` tinyint(3) unsigned DEFAULT NULL,
`rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
`rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
`length` smallint(5) unsigned DEFAULT NULL,
`replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
`rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
`special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`film_id`),
KEY `idx_title` (`title`),
KEY `idx_fk_language_id` (`language_id`),
KEY `idx_fk_original_language_id` (`original_language_id`),
CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
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
#2 查询所有数据
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 (0.00 sec)
ERROR:
No query specified
#3
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=utf8
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: 137
ref: const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
#4
mysql> show create table rental;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| rental | CREATE TABLE `rental` (
`rental_id` int(11) NOT NULL AUTO_INCREMENT,
`rental_date` datetime NOT NULL,
`inventory_id` mediumint(8) unsigned NOT NULL,
`customer_id` smallint(5) unsigned NOT NULL,
`return_date` datetime DEFAULT NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`rental_id`),
UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
KEY `idx_fk_inventory_id` (`inventory_id`),
KEY `idx_fk_customer_id` (`customer_id`),
KEY `idx_fk_staff_id` (`staff_id`),
CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
发现：该表的engine为InnoDB ，得知 主键的排列顺序跟 数据的排列顺序一致
mysql> explain select * from rental where rental_date>'2005-01-01' order by rental_id;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | rental | NULL | index | rental_date | PRIMARY | 4 | NULL | 16005 | 50.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.28 sec)
#Extra: Using where; 指定使用主键排序
type列为 index 得知该sql使用了 rental_date上的索引
新建一个engine为myisam的rental_myisam表
mysql> show create table rental_myisam \G;
*************************** 1. row ***************************
Table: rental_myisam
Create Table: CREATE TABLE `rental_myisam` (
`rental_id` int(11) NOT NULL AUTO_INCREMENT,
`rental_date` datetime NOT NULL,
`inventory_id` mediumint(8) unsigned NOT NULL,
`customer_id` smallint(5) unsigned NOT NULL,
`return_date` datetime DEFAULT NULL,
`staff_id` tinyint(3) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`rental_id`),
UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
KEY `idx_fk_inventory_id` (`inventory_id`),
KEY `idx_fk_customer_id` (`customer_id`),
KEY `idx_fk_staff_id` (`staff_id`)
) ENGINE=MyISAM AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
#在rental_myisam 执行以上sql
mysql> explain select * from rental_myisam where rental_date>'2005-01-01' order by rental_id\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental_myisam
partitions: NULL
type: ALL
possible_keys: rental_date
key: NULL
key_len: NULL
ref: NULL
rows: 16044
filtered: 100.00
Extra: Using where; Using filesort #指定使用文件排序
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> explain select * from rental where rental_date='2005-05-09' order by inventory_id,customer_id\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: ref
possible_keys: rental_date
key: rental_date
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> explain select * from rental where rental_date='2005-05-09' order by inventory_id,customer_id\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: ref
possible_keys: rental_date
key: rental_date
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
得知以上两种存储引擎 sql都可以使用二级索引排序 都没有做 filesort哦
更改以上两组sql 使用索引字段 降序排列
mysql> explain select * from rental where rental_date='2005-05-09' order by inventory_id desc,customer_id\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: ref
possible_keys: rental_date
key: rental_date
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> explain select * from rental_myisam where rental_date='2005-05-09' order by inventory_id desc,customer_id\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental_myisam
partitions: NULL
type: ref
possible_keys: rental_date
key: rental_date
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
得知：做以上调整后 两种存储引擎的sql 都是用了 filesort哦
索引列的 升降序 必须跟 order by 字段的 升降序 一致哦
#修改where条件 对 rental_date做范围查找
mysql> explain select * from rental_myisam where rental_date>'2005-05-09' order by inventory_id desc,customer_id\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental_myisam
partitions: NULL
type: ALL
possible_keys: rental_date
key: NULL
key_len: NULL
ref: NULL
rows: 16044
filtered: 100.00
Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> explain select * from rental where rental_date>'2005-05-09' order by inventory_id desc,customer_id\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: ALL
possible_keys: rental_date
key: NULL
key_len: NULL
ref: NULL
rows: 16005
filtered: 50.00
Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
得知：由于 rental_date为联合索引的左边第一个字段，当使用防伪查找时 无法使用索引做排序哦
