性能优化
2021-01-31 本文已影响0人
AD刘涛
MySQL中使用索引的典型场景
- 匹配全值
对索引中所有列都指定具体值，即是对索引中的所有列都有等值匹配的条件。
如我们有以下表结构：
mysql> show create table rental\G;
*************************** 1. row ***************************
Table: rental
Create Table: CREATE TABLE `rental` (
`rental_id` int NOT NULL AUTO_INCREMENT,
`rental_date` datetime NOT NULL,
`inventory_id` mediumint unsigned NOT NULL,
`customer_id` smallint unsigned NOT NULL,
`return_date` datetime DEFAULT NULL,
`staff_id` tinyint 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 DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
mysql> show create table payment\G;
*************************** 1. row ***************************
Table: payment
Create Table: CREATE TABLE `payment` (
`payment_id` smallint unsigned NOT NULL AUTO_INCREMENT,
`customer_id` smallint unsigned NOT NULL,
`staff_id` tinyint unsigned NOT NULL,
`rental_id` int DEFAULT NULL,
`amount` decimal(5,2) NOT NULL,
`payment_date` datetime NOT NULL,
`last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`payment_id`),
KEY `idx_fk_staff_id` (`staff_id`),
KEY `idx_fk_customer_id` (`customer_id`),
KEY `fk_payment_rental` (`rental_id`),
KEY `idx_payment_date` (`payment_date`,`amount`,`last_update`),
CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
当我们执行以下sql语句：
mysql> explain select * from rental where rental_date="2005-05-25 17:22:10" and inventory_id=373 and customer_id=343\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: const
possible_keys: rental_date,idx_fk_inventory_id,idx_fk_customer_id
key: rental_date
key_len: 10
ref: const,const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
字段key的值为rental_date,表示优化器选择索引rental_date进行扫描。
- 匹配值的范围查询
对索引的值能够进行范围查询。
如检索租赁表rental中客户编号customer_id在指定范围内的记录：
mysql> explain select * from rental where customer_id>= 373 and customer_id < 400\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: range
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: NULL
rows: 718
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
Extra: Using index condition 表示MySQL使用了ICP来进一步优化查询，在检索的时候，把条件customer_id的过滤操作下推到存储引擎层来完成，这样可以降低不必要的IO访问。
-
匹配最左前缀
仅仅使用索引中的最左边列进行查找，比如在col1+col2+col3字段上的联合索引能够被包含col1、（col1+col2）、（col1+col2+col3）的等值查询利用到，可是不能被col2、（col2+col3）的等值查询利用到。 -
仅仅对索引进行查询，当查询的列都在索引的字段上时，查询的效率更高。
mysql> explain select last_update from payment where payment_date='2006-02-14 15:16:03' and amount=3.98\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: ref
possible_keys: idx_payment_date
key: idx_payment_date
key_len: 8
ref: const,const
rows: 8
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
Extra部分变成了Using index,也就意味着，现在直接访问索引就足够获取到所需要的数据，不需要通过索引回表，Using index也就是平常说的覆盖索引扫描。只访问必须访问的数据，在一般情况下，减少不必要的数据访问能提高效率。
如何计算 key_len 列的值？
这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
ken_len计算规则如下：
| 数据类型 | 含义 |
|---|---|
| char(n) | n字节长度 |
| varchar(n) | 使用2字节存储字符串长度，如果是utf-8，则长度 3n + 2 |
| tinyint | 1字节 |
| smallint | 2字节 |
| int | 4字节 |
| bigint | 8字节 |
| date | 3字节 |
| timestamp | 4字节 |
| datetime | 8字节 |
- 如果字段允许为 NULL，需要1字节记录是否为 NULL 索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
如何理解ICP特性（索引下推）。
MySQL 5.6 引入了Index Condition Pushdown （ICP）的特性，进一步优化了查询。PushDown 表示操作下放，某些情况下的条件过滤操作下放到存储引擎。
如执行以下sql语句，其内部执行流程如图所示：
mysql> explain select * from rental where rental_date='2006-02-14 15:16:03' and customer_id >= 300 and customer_id <= 400 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
partitions: NULL
type: ref
possible_keys: rental_date,idx_fk_customer_id
key: rental_date
key_len: 5
ref: const
rows: 182
filtered: 16.85
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
尚未使用ICP特性
使用ICP特性
