ITPub博客

首页 > 数据库 > MySQL > MySQL索引提示

MySQL索引提示

原创 MySQL 作者:StevenBeijing 时间:2020-07-30 15:24:16 0 删除 编辑

      SQL提示,是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优化操作的目的。MySQL数据库支持索引提示(INDEX HINT)显式的告诉优化器使用了哪个索引。有以下几种情况可能用到索引提示:

1、MySQL数据库的优化器错误的选择了某个索引,导致SQL运行很慢。这个在情况比较少见。优化器在绝大部分情况下工作的非常有效和正确。

2、某些SQL语句可以选择的索引非常多,这时优化器选择执行计划时间的开销可能会大于SQL语句本身。例如优化器分析Range查询本身就是比较耗时的操作。这时DBA或开发人员分析最优的索引选择,通过index hint来强制使优化器不进行各个路径的成本分析直接选择指定的索引来完成查询。

index hint种类

MySql共有三种索引提示,分别是:USE INDEX、IGNORE INDEX和FORCE INDEX,他们之间的区别是:

1、 use index:use index告诉MySql用列表中的其中一个索引去做本次查询,就可以让MySQL不再考虑其他可用的索引建议MySQL用这些索引,但是MySQL不一定会用。

MySQL > show create table test2 \G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `id` bigint(16) NOT NULL AUTO_INCREMENT,
  `order_seq` bigint(16) NOT NULL,
  `order_type` int(11) DEFAULT NULL,
  `order_flag` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_id` (`id`),
  KEY `idx_id_orderseq` (`id`,`order_seq`),
  KEY `idx_order_seq` (`order_seq`)
) ENGINE=InnoDB AUTO_INCREMENT=15002212 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
MySQL > explain select * from test2 where id>10000 and id<1000000;
+----+-------------+-------+------------+-------+--------------------------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys                  | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+--------------------------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test2 | NULL       | range | PRIMARY,idx_id,idx_id_orderseq | PRIMARY | 8       | NULL | 2021716 |   100.00 | Using where |
+----+-------------+-------+------------+-------+--------------------------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
MySQL > explain select * from test2 use index(idx_id) where id>10000 and id<100000;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | test2 | NULL       | range | idx_id        | idx_id | 8       | NULL | 180580 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
MySQL > explain select * from test2 use index(idx_order_seq) where id=10000;
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | test2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 14611349 |     0.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.01 sec)


2、 ignore index:ignore index告诉mysql不要使用某些索引去做本次查询

MySQL > show create table test2 \G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `id` bigint(16) NOT NULL AUTO_INCREMENT,
  `order_seq` bigint(16) NOT NULL,
  `order_type` int(11) DEFAULT NULL,
  `order_flag` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_id` (`id`),
  KEY `idx_id_orderseq` (`id`,`order_seq`),
  KEY `idx_order_seq` (`order_seq`)
) ENGINE=InnoDB AUTO_INCREMENT=15002212 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
MySQL > explain select * from test2 where id>10000 and id<1000000;
+----+-------------+-------+------------+-------+--------------------------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys                  | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+--------------------------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test2 | NULL       | range | PRIMARY,idx_id,idx_id_orderseq | PRIMARY | 8       | NULL | 2021716 |   100.00 | Using where |
+----+-------------+-------+------------+-------+--------------------------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
MySQL > explain select * from test2 ignore index(primary) where id>10000 and id<1000000;
+----+-------------+-------+------------+-------+------------------------+--------+---------+------+---------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys          | key    | key_len | ref  | rows    | filtered | Extra                 |
+----+-------------+-------+------------+-------+------------------------+--------+---------+------+---------+----------+-----------------------+
|  1 | SIMPLE      | test2 | NULL       | range | idx_id,idx_id_orderseq | idx_id | 8       | NULL | 1971862 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+------------------------+--------+---------+------+---------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)


3、 force index:强制MySQL使用一个特定的索引

MySQL > show create table test2 \G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `id` bigint(16) NOT NULL AUTO_INCREMENT,
  `order_seq` bigint(16) NOT NULL,
  `order_type` int(11) DEFAULT NULL,
  `order_flag` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_id` (`id`),
  KEY `idx_id_orderseq` (`id`,`order_seq`),
  KEY `idx_order_seq` (`order_seq`)
) ENGINE=InnoDB AUTO_INCREMENT=15002212 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
MySQL > explain select * from test2 where id>10000 and id<1000000;
+----+-------------+-------+------------+-------+--------------------------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys                  | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+--------------------------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | test2 | NULL       | range | PRIMARY,idx_id,idx_id_orderseq | PRIMARY | 8       | NULL | 2021716 |   100.00 | Using where |
+----+-------------+-------+------------+-------+--------------------------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
MySQL > explain select * from test2 force index(idx_id) where id>10000 and id<1000000;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+---------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows    | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+---------+----------+-----------------------+
|  1 | SIMPLE      | test2 | NULL       | range | idx_id        | idx_id | 8       | NULL | 1971862 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+---------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)





来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30135314/viewspace-2708079/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-02-10

  • 博文量
    284
  • 访问量
    247553