MySQL索引最左前綴原則導致系統癱瘓

早上九點半左右 業務人員反映他們頁面打開緩慢，后續頁面出現502。

然后我這邊收到報警 ，登錄數據庫服務器（4核cpu）查看  cpu 400% load 30左右

進入到數據庫中查看發現好多慢查詢

本以為這些慢查詢是來自該系統每天的定時任務（該系統相當于一個olap系統，每天會進行批量的數據查詢提取。）于是先crontab -e 把所有的定時任務都停掉。但是慢查詢還是存在。

所有的慢查詢都是同一個模板， 后來詢問開發的同事昨天上線了新版本

SELECT (plan.due_amount + IF(plan.overdue_day > 0, (plan.due_amount * extend.supplement_penalty_rate /100), 0) - plan.reduce_amount) due_amount_total, plan.repay_status
                     FROM mostop_xiaodai_supplement_loan_repay_plan plan LEFT JOIN mostop_xiaodai_loan_info_extend extend ON extend.loan_id = plan.loan_id WHERE plan.base_plan_id = 11124546 AND plan.step_no = 2

查看執行表結構

mysql> show create table mostop_xiaodai_loan_info_extend\G
*************************** 1. row ***************************
       Table: mostop_xiaodai_loan_info_extend
Create Table: CREATE TABLE `mostop_xiaodai_loan_info_extend` (
  `id` bigint(20) unsigned NOT NULL COMMENT '編號',
  `agentid` int(10) unsigned NOT NULL COMMENT '渠道 ID',
  `loan_id` bigint(20) unsigned NOT NULL COMMENT '貸款編號',
  `create_time` datetime NOT NULL COMMENT '創建時間',
  `update_time` datetime NOT NULL COMMENT '更新時間',
  `total_rate` decimal(10,6) unsigned DEFAULT NULL COMMENT '總利率',
  `service_rate` decimal(10,6) unsigned DEFAULT NULL COMMENT '服務費率',
  `intrest_rate` decimal(10,6) unsigned DEFAULT NULL COMMENT '利息費率',
  `overdue_rate` decimal(10,6) unsigned DEFAULT NULL COMMENT '逾期總利率',
  `overdue_service_rate` decimal(10,6) unsigned DEFAULT NULL COMMENT '逾期服務費率',
  `penalty_rate` decimal(10,6) unsigned DEFAULT NULL COMMENT '違約金率',
  `is_split` tinyint(4) DEFAULT '0' COMMENT '息費打平，是否需要拆單',
  `desired_repay_type` varchar(9) DEFAULT NULL COMMENT '息費打平，理想還款方式',
  `desired_total_rate` decimal(10,6) DEFAULT NULL COMMENT '息費打平，理想總利率',
  `supplement_overdue_rate` decimal(10,6) DEFAULT NULL COMMENT '息費打平，白條訂單逾期總利率',
  `supplement_penalty_rate` decimal(10,6) DEFAULT NULL COMMENT '息費打平，白條訂單違約金率',
  `investor_rate` decimal(10,6) DEFAULT NULL COMMENT '投資人利率',
  `investor_repay_type` varchar(9) DEFAULT NULL COMMENT '投資人利率',
  PRIMARY KEY (`id`,`agentid`),
  UNIQUE KEY `agentid` (`agentid`,`loan_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='貸款信息擴展表'

查看執行計劃

mysql> explain SELECT (plan.due_amount + IF(plan.overdue_day > 0, (plan.due_amount * extend.supplement_penalty_rate /100), 0) 
| id | select_type | table  | partitions | type  | possible_keys   | key             | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | plan   | NULL       | const | idx_base_planid | idx_base_planid | 8       | const |       1 |   100.00 | NULL        |
|  1 | SIMPLE      | extend | NULL       | ALL   | NULL            | NULL            | NULL    | NULL  | 4690305 |   100.00 | Using where |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+-------+---------+----------+-------------+

雖然表中的聯合索引上有loan_id這個列

 UNIQUE KEY `agentid` (`agentid`,`loan_id`)

但是根據索引的最左前綴原則，where條件中直接出了loan_id，復合索引出現了斷開，所以索引失效。研發同學以為是可以用到表中的索引，沒有審核就上線了，所以導致了全表掃描導致服務器的負載超高。

解決辦法

添加索引

alter table  mostop_xiaodai_loan_info_extend add index IDX_loan_id (loan_id);

添加索引后執行計劃

explain SELECT (plan.due_amount + IF(plan.overdue_day > 0, (plan.due_amount * extend.supplement_penalty_rate /100), 0) - plan.reduce_amount) due_amount_total, plan.repay_status
    ->                      FROM mostop_xiaodai_supplement_loan_repay_plan plan LEFT JOIN mostop_xiaodai_loan_info_extend extend ON extend.loan_id = plan.loan_id WHERE plan.base_plan_id = 11124546 AND plan.step_no = 2 ;
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys   | key             | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | plan   | NULL       | const | idx_base_planid | idx_base_planid | 8       | const |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | extend | NULL       | ref   | IDX_loan_id     | IDX_loan_id     | 8       | const |    1 |   100.00 | Using where |
+----+-------------+--------+------------+-------+-----------------+-----------------+---------+-------+------+----------+-------------+

服務器負載立馬回復正常

通過本次事故

上線前進行SQL審核

應用和數據庫單獨部署在不同服務器上