简介
index hint ：索引提示，是一种优化手段，通过嵌入 sql 中告知 MySQL 如何选择索引。
官方文档：
- 5.6 https://dev.mysql.com/doc/refman/5.6/en/index-hints.html
- 5.7 https://dev.mysql.com/doc/refman/5.7/en/index-hints.html
按照文档，5.6 和 5.7 都支持 index hint。
仅 select 和 update 支持 index hint。
index hint 信息放在 SQL 的表名后面。
三种 index hint 作用如下：
- use index：指定索引。如果优化器认为全表扫描更快，会使用全表扫描，而非指定的索引。
- force index：强制指定索引。即使优化器认为全表扫描更快，也不会使用全表扫描，而是用指定的索引。
- ignore index：忽略指定索引。
示例1
数据准备
use test;
create table `user_balance` (
`id` bigint not null auto_increment,
`user_id` varchar(32) not null,
`balance` bigint unsigned not null,
`created_at` bigint not null,
`updated_at` bigint not null,
primary key (`id`),
unique index uk_user_id(`user_id`),
index idx_user_id_balance(`user_id`, `balance`),
index idx_created_at(`created_at`),
index idx_updated_at(`updated_at`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
插入 10w条数据：
import pymysql
import random
conn = pymysql.connect(host='127.0.0.1',
user='root',
password='123456pass',
port=3306,
database='test',
autocommit=True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
for batch in range(0, 100):
print('batch: ', batch)
conn.begin()
for num in range(0, 1000):
user_id = batch * 1000 + num
balance = num
created_at = random.randint(1612340000,1612340999)
updated_at = created_at + 1
cursor.execute('INSERT INTO `user_balance` (user_id, balance, created_at, updated_at) values(%s, %s, %s, %s)',
[user_id, balance,created_at, updated_at])
conn.commit()
cursor.close()
conn.close()
查询数据：
> select * from user_balance limit 10
+----+---------+---------+------------+------------+
| id | user_id | balance | created_at | updated_at |
+----+---------+---------+------------+------------+
| 1 | 0 | 0 | 1612340557 | 1612340558 |
| 2 | 1 | 1 | 1612340294 | 1612340295 |
| 3 | 2 | 2 | 1612340470 | 1612340471 |
| 4 | 3 | 3 | 1612340157 | 1612340158 |
| 5 | 4 | 4 | 1612340237 | 1612340238 |
| 6 | 5 | 5 | 1612340427 | 1612340428 |
| 7 | 6 | 6 | 1612340162 | 1612340163 |
| 8 | 7 | 7 | 1612340001 | 1612340002 |
| 9 | 8 | 8 | 1612340033 | 1612340034 |
| 10 | 9 | 9 | 1612340910 | 1612340911 |
+----+---------+---------+------------+------------+
> select count(*) from user_balance
+----------+
| count(*) |
+----------+
| 100000 |
+----------+
explain select force index:
-- 不带 force index 提示
> explain select * from user_balance where user_id = '123' \G
***************************[ 1. row ]***************************
id | 1
select_type | SIMPLE
table | user_balance
partitions | <null>
type | const
possible_keys | uk_user_id,idx_user_id_balance -- 候选索引
key | uk_user_id -- 实际使用的索引
key_len | 130
ref | const
rows | 1
filtered | 100.0
Extra | <null>
-- 不带 force index 提示
> explain select * from user_balance where user_id = '123' and balance = 123 \G
***************************[ 1. row ]***************************
id | 1
select_type | SIMPLE
table | user_balance
partitions | <null>
type | const
possible_keys | uk_user_id,idx_user_id_balance -- 候选索引
key | uk_user_id -- 实际使用的索引
key_len | 130
ref | const
rows | 1
filtered | 100.0
Extra | <null>
-- 通过 force index 提示使用 uk_user_id 索引
> explain select * from user_balance force index(uk_user_id) where user_id = '123' \G
***************************[ 1. row ]***************************
id | 1
select_type | SIMPLE
table | user_balance
partitions | <null>
type | const
possible_keys | uk_user_id
key | uk_user_id
key_len | 130
ref | const
rows | 1
filtered | 100.0
Extra | <null>
-- 通过 force index 提示使用 idx_user_id_balance 索引
> explain select * from user_balance force index(idx_user_id_balance) where user_id = '123' \G
***************************[ 1. row ]***************************
id | 1
select_type | SIMPLE
table | user_balance
partitions | <null>
type | ref
possible_keys | idx_user_id_balance
key | idx_user_id_balance
key_len | 130
ref | const
rows | 1
filtered | 100.0
Extra | <null>
-- 通过 force index 提示使用 idx_user_id_balance 索引
> explain select *
from user_balance force index(idx_user_id_balance)
where balance = 123 and user_id = '123' \G
***************************[ 1. row ]***************************
id | 1
select_type | SIMPLE
table | user_balance
partitions | <null>
type | ref
possible_keys | idx_user_id_balance
key | idx_user_id_balance
key_len | 138
ref | const,const
rows | 1
filtered | 100.0
Extra | <null>
-- 通过 force index 提示使用 idx_user_id_balance 索引
> explain select id, user_id, balance, created_at, updated_at
from user_balance force index(idx_user_id_balance)
where balance = 123 and user_id = '123' \G
***************************[ 1. row ]***************************
id | 1
select_type | SIMPLE
table | user_balance
partitions | <null>
type | ref
possible_keys | idx_user_id_balance
key | idx_user_id_balance
key_len | 138
ref | const,const
rows | 1
filtered | 100.0
Extra | <null>
-- 通过 force index 提示使用 idx_created_at 索引
-- where 条件中无索引中字段
> explain select id, user_id, balance, created_at, updated_at
from user_balance force index(idx_created_at)
where balance = 123 and user_id = '123' \G
***************************[ 1. row ]***************************
id | 1
select_type | SIMPLE
table | user_balance
partitions | <null>
type | ALL
possible_keys | <null> -- 候选索引为空
key | <null> -- 实际使用索引也为空
key_len | <null>
ref | <null>
rows | 100008
filtered | 1.0
Extra | Using where
-- 通过 force index 提示使用 idx_created_at 索引
> explain select id, user_id, balance, created_at, updated_at
from user_balance force index(idx_created_at)
where balance = 123 and user_id = '123' and created_at > 123 \G
***************************[ 1. row ]***************************
id | 1
select_type | SIMPLE
table | user_balance
partitions | <null>
type | range
possible_keys | idx_created_at
key | idx_created_at
key_len | 8
ref | <null>
rows | 50004
filtered | 1.0
Extra | Using index condition; Using where
-- 通过 force index 提示使用 idx_user_id_balance 索引
-- 注意，这里是 select for update 语句
> explain select id, user_id, balance, created_at, updated_at
from user_balance force index(idx_user_id_balance)
where balance = 123 and user_id = '123' for update \G
explain select ignore index:
> explain select * from user_balance where user_id = '123' \G
***************************[ 1. row ]***************************
id | 1
select_type | SIMPLE
table | user_balance
partitions | <null>
type | const
possible_keys | uk_user_id,idx_user_id_balance
key | uk_user_id
key_len | 130
ref | const
rows | 1
filtered | 100.0
Extra | <null>
> explain select *
from user_balance ignore index(uk_user_id)
where user_id = '123' \G
***************************[ 1. row ]***************************
id | 1
select_type | SIMPLE
table | user_balance
partitions | <null>
type | ref
possible_keys | idx_user_id_balance
key | idx_user_id_balance
key_len | 130
ref | const
rows | 1
filtered | 100.0
Extra | <null>
explain update force index:
> explain update user_balance
set balance = 124
where balance = 123 and user_id = '123' \G
***************************[ 1. row ]***************************
id | 1
select_type | UPDATE
table | user_balance
partitions | <null>
type | range
possible_keys | uk_user_id,idx_user_id_balance
key | uk_user_id
key_len | 130
ref | const
rows | 1
filtered | 100.0
Extra | Using where
> explain update user_balance force index(idx_user_id_balance)
set balance = 124
where balance = 123 and user_id = '123' \G
***************************[ 1. row ]***************************
id | 1
select_type | UPDATE
table | user_balance
partitions | <null>
type | range
possible_keys | idx_user_id_balance
key | idx_user_id_balance
key_len | 138
ref | const,const
rows | 1
filtered | 100.0
Extra | Using where; Using temporary
explain select use index:
> explain select id, user_id, balance, created_at, updated_at
from user_balance use index(idx_user_id_balance)
where balance = 123 and user_id = '123' \G
***************************[ 1. row ]***************************
id | 1
select_type | SIMPLE
table | user_balance
partitions | <null>
type | ref
possible_keys | idx_user_id_balance
key | idx_user_id_balance
key_len | 138
ref | const,const
rows | 1
filtered | 100.0
Extra | <null>
explain delete:
> explain delete from user_balance
where balance = 123 and user_id = '123' \G
***************************[ 1. row ]***************************
id | 1
select_type | DELETE
table | user_balance
partitions | <null>
type | range
possible_keys | uk_user_id,idx_user_id_balance
key | uk_user_id
key_len | 130
ref | const
rows | 1
filtered | 100.0
Extra | Using where
> explain delete from user_balance use index(idx_user_id_balance)
where balance = 123 and user_id = '123' \G
-- 因为 delete 不支持 indext hint，所以会报错
(1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'use index(idx_user_id_balance) \nwhere balance = 123 and user_id = '123'' at line 1")