tidb点查询如何使hint生效

在tidb中默认点查询的执行计划使用hint不会生效，如何绕过使用点查询用hint也生效呢

在这里插
MySQL [test]> show create table users;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`id` int(11) NOT NULL,
`user_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
KEY `idx_name` (`user_name`),
KEY `idx_id_name` (`id`,`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
SQL使用了hint执行计划还是点查询
MySQL [test]> explain select /*+
USE_INDEX(users,idx_id_name) */id,user_name from users
where id=100 ;
+-------------+---------+------+---------------+---------------+
| id
| estRows | task | access object | operator info |
+-------------+---------+------+---------------+---------------+
| Point_Get_1 | 1.00
| root | table:users
| handle:100
|
+-------------+---------+------+---------------+---------------+
1 row in set (0.00 sec)
使用cast函数或者其他函数让执行计划不是点查询然后再加hint就可以生效
MySQL [test]> explain select /*+
USE_INDEX(users,idx_id_name) */cast(id as char),cast(user_name
as char) from users
where id=100 ;
+--------------------------+---------+-----------+-----------------------------------------------+---------------------------------------------------------------------------------------------------+
| id
| estRows | task
| access object
| operator info
|
+--------------------------+---------+-----------+-----------------------------------------------+---------------------------------------------------------------------------------------------------+
| Projection_4
| 1.00
| root
|
| cast(test.users.id, var_string(5))->Column#3, cast(test.users.user_name, var_string(5))->Column#4 |
| └─IndexReader_6
| 1.00
| root
|
| index:IndexRangeScan_5
|
|
└─IndexRangeScan_5
| 1.00
| cop[tikv] | table:users, index:idx_id_name(id, user_name) | range:[100,100], keep order:false
|
+--------------------------+---------+-----------+-----------------------------------------------+---------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
入代码片
`
``

最后

以上就是缥缈睫毛膏为你收集整理的tidb点查询如何使hint生效的全部内容，希望文章能够帮你解决tidb点查询如何使hint生效所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。