MySQL暗黙型変換によるインデックス失効
4933 ワード
今日、where条件の列にインデックスがあるのに、計画を実行するか、テーブル全体をスキャンするかという問題が見つかりました.
テーブル構造の表示
warningsの表示
この2つの列は文字列タイプなので、SQLには引用符が付いていません!!
引用符を付けて実行計画を表示
mysql> explain select task_id FROM mostop_xiaodai_collection_call_auto WHERE task_id = 433423882127424 AND task_data_id = 27739131157286912;
+----+-------------+-------------------------------------+------------+------+------------------------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------------------------+------------+------+------------------------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | mostop_xiaodai_collection_call_auto | NULL | ALL | IDX_task_id,IDX_task_data_id | NULL | NULL | NULL | 3101134 | 1.00 | Using where |
+----+-------------+-------------------------------------+------------+------+------------------------------+------+---------+------+---------+----------+-------------+
1 row in set, 5 warnings (0.00 sec)
テーブル構造の表示
mysql> show create table mostop_xiaodai_collection_call_auto\G
*************************** 1. row ***************************
Table: mostop_xiaodai_collection_call_auto
Create Table: CREATE TABLE `mostop_xiaodai_collection_call_auto` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT ' ',
`agentid` int(10) unsigned NOT NULL COMMENT ' ID',
`loan_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT ' ID',
`user_id` bigint(20) unsigned NOT NULL COMMENT ' ID',
`call_mobile` varchar(20) NOT NULL COMMENT ' ',
`call_name` varchar(20) NOT NULL COMMENT ' ',
`call_sex` varchar(20) NOT NULL COMMENT ' ',
`call_due_date` date NOT NULL COMMENT ' ',
`call_overdue_day` int(10) NOT NULL COMMENT ' ( )',
`call_talking_type` varchar(50) NOT NULL COMMENT ' (A: 7 B: 3 C: 3 D: 1 Ps: , , due_one )',
`call_platform_name` varchar(100) NOT NULL COMMENT ' ',
`third_party_system` tinyint(3) unsigned NOT NULL COMMENT ' IVR ,1 = ( ),2 = ,3 = ( ), 4 = , 5 = ,99 = ',
`send_time` datetime DEFAULT NULL COMMENT ' ',
`send_status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT ' ,0 = ,1 = ,2 = ',
`send_result` tinyint(3) DEFAULT '0' COMMENT 'IVR ( 0 = , ,1 = , )',
`talking_time` int(10) NOT NULL DEFAULT '0' COMMENT ' ',
`task_id` varchar(32) NOT NULL DEFAULT '0' COMMENT ' ID ( )',
`task_data_id` varchar(32) NOT NULL DEFAULT '0' COMMENT ' ID ( )',
`resend_num` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT ' ',
`receive_time` datetime DEFAULT NULL COMMENT ' ',
`receive_content` text COMMENT ' ',
`unique_tab` varchar(100) DEFAULT NULL COMMENT ' ',
`created_at` int(10) unsigned NOT NULL DEFAULT '0' COMMENT ' ',
`updated_at` int(10) unsigned NOT NULL DEFAULT '0' COMMENT ' ',
PRIMARY KEY (`id`),
KEY `loan_id` (`agentid`,`loan_id`),
KEY `send_result` (`call_mobile`,`send_time`),
KEY `receive_time` (`receive_time`),
KEY `IDX_task_id` (`task_id`),
KEY `IDX_task_data_id` (`task_data_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5292233 DEFAULT CHARSET=utf8 COMMENT=' '
1 row in set (0.00 sec)
warningsの表示
この2つの列は文字列タイプなので、SQLには引用符が付いていません!!
`task_id` varchar(32)
`task_data_id` varchar(32)
引用符を付けて実行計画を表示
mysql> explain select task_id FROM mostop_xiaodai_collection_call_auto WHERE task_id = '433423882127424' AND task_data_id =' 27739131157286912';
+----+-------------+-------------------------------------+------------+------+------------------------------+------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------------------------+------------+------+------------------------------+------------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | mostop_xiaodai_collection_call_auto | NULL | ref | IDX_task_id,IDX_task_data_id | IDX_task_data_id | 98 | const | 1 | 5.00 | Using where |
+----+-------------+-------------------------------------+------------+------+------------------------------+------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)