MySQL

MySQL:简介 Ubuntu 安装 MySQL MySQL:官方文档地址 MySQL:DDL、DQL、DML、DCL的含义 MySQL:Mac 中启动 关闭 MySQL 服务 MySQL:有哪些好用的管理工具? MySQL:命令行工具 mycli MySQL:CHAR类型 MySQL:VARCHAR类型 MySQL:整型数字 MySQL:datetime 类型 MySQL:时间戳 MySQL:创建和删除数据库 MySQL:切换和查看数据库 MySQL:创建和删除表 MySQL:在表中增加、删除、修改列 MySQL:创建和删除主键 MySQL:使用 rename 修改表名 MySQL:修改自增主键id的类型 MySQL:如何创建一个相同的表 MySQL:修改表的字符编码 MySQL:增删查改 MySQL:插入数据 MySQL:插入多行数据 MySQL:使用 insert set 插入数据 MySQL:大小写和反引号 MySQL:字符串类型值的大小写 MySQL:SQL注释 MySQL:不要使用utf8 MySQL:NULL的判等 MySQL:InnoDB存储引擎的限制 MySQL:if和case的使用 MySQL:使用 load data 快速导入数据 MySQL:使用 select into outfile 导出数据 MySQL:查询和设置 sql_mode MySQL:严格模式 MySQL:NOT NULL 字段不插入数据,会发生什么? MySQL:无符号整数列插入负数会发生什么? MySQL:关于 null 的那些事 MySQL:大表行数查询 MySQL:自动生成创建时间、更新时间;自动更新更新时间 MySQL:insert ignore MySQL:字符集排序规则 MySQL:如果连续更新一个字段两次,结果是? MySQL:字符串转数字 MySQL:尾部空格 MySQL:添加和删除索引 MySQL:唯一索引与NULL MySQL:唯一索引的单列长度限制 MySQL:InnoDB 索引 MySQL:字符集排序规则对唯一索引的影响 MySQL:唯一索引冲突消耗主键 ID MySQL 使用 index hint 指定索引:ignore index、force index、use index MySQL:查看客户端连接信息 MySQL:查看表的状态 show table status MySQL:如何治理连接数 ? MySQL:如何监控和处理慢查询与长事务 ? MySQL:自定义函数 MySQL:now() 函数 MySQL:unix_timestamp() 函数 MySQL:from_unixtime() 函数 MySQL:version() 函数 MySQL:current_timestamp() 函数 MySQL:cast 函数 MySQL:convert 函数 MySQL:使用 greatest、least 函数获取行最大值、最小值 MySQL:使用 group_concat 函数连接多行数据为一个字符串 MySQL:获取版本号 MySQL:Java 类型映射 MySQL下创建只能有一行记录的table 关于MySQL的字符集 理解数据库中的undo日志、redo日志、检查点 ubuntu下源码安装MySQL MySQL:JOIN解惑 如何快速更新数据库中的百万条数据

MySQL 使用 index hint 指定索引:ignore index、force index、use index


#MySQL


简介

index hint :索引提示,是一种优化手段,通过嵌入 sql 中告知 MySQL 如何选择索引。

官方文档:

按照文档,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")


( 本文完 )