记一次MySQL 查询慢索引不生效过程二

客官°小女子只卖身不卖艺 2022-12-03 08:36 136阅读 0赞

###  1：简单SQL查询 ###

EXPLAIN
    SELECT * FROM log_member 
    WHERE member_id = 46770 ;

### 2：表索引信息 ###

CREATE TABLE `log_member` (
       `log_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
       `module_code` varchar(30) DEFAULT NULL COMMENT '模块编码',
       `method` varchar(30) DEFAULT NULL COMMENT '业务方法',
       `json_data` varchar(2000) DEFAULT NULL COMMENT '业务数据<创建以及修改均需要存储数据>',
       `ip` char(30) DEFAULT NULL COMMENT 'IP地址',
       `member_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT '会员账户ID',
       `member_name` varchar(30) DEFAULT NULL COMMENT '会员名字',
       `account` varchar(30) DEFAULT NULL COMMENT '操作者账户',
       `operator` varchar(30) DEFAULT NULL COMMENT '操作者',
       `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '操作时间',
       `parent_id` varchar(20) DEFAULT NULL COMMENT '会员父ID',
       PRIMARY KEY (`log_id`),
       KEY `member_name` (`member_name`),
       KEY `IP` (`ip`),
       KEY `moudle_code` (`module_code`),
       KEY `method` (`method`),
       KEY `memberid` (`member_id`),
       KEY `parentid` (`parent_id`)
     ) ENGINE=InnoDB AUTO_INCREMENT=567536 DEFAULT CHARSET=utf8 COMMENT='会员日志记录表'

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70][]

### 3：分析SQL ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70 2][]

** 喵了一眼，数据库字段类型是varchar、查询时放入的是数字类型。所以索引无法生效。**

### 4：解决问题 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70 3][]

《[记一次MySQL 查询慢索引不生效过程 ][MySQL][ 一][MySQL]》

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70]: /images/20221123/06635ab940134d7ebda7c667185fa5fe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70 1]: /images/20221123/32bce7383ac849138ce987f316f7b04d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70 2]: /images/20221123/a873bc5734af448682a40f05d7ddf4e7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3lleGlhb21vZGVtbw_size_16_color_FFFFFF_t_70 3]: /images/20221123/fa7ed40c58674833be4a87f321b57bde.png
[MySQL]: https://blog.csdn.net/yexiaomodemo/article/details/108018462