问题
数据库慢日志突然爆炸，导致服务异常。排查了后语句没有什么异常，然后根据阿里云的rds性能监控可以看出流量突然激增。之后的几天也观察了下慢日志，慢日志时常 有出现。那只能改语句，删错误的索引和加正确的索引。然后就是限流这一块没做好，系统工作台前端这一块竟然可以无限F5。后端限流用的是nginx的限流，但是多人F5， 流量可以控制的住，但是数据库的瓶颈太快达到了，所以原因很复杂，只能一步一步优化。下面主要是研究索引的问题！
建立联合索引要慎重
有非聚簇索引不用，为什么选择用聚簇索引了。有非聚簇索引复合索引和聚簇索引的情况下，非聚簇只命中了一个，order by的时候会优先选择id做排序，如下案例：
表结构
mysql> show columns from sima_store_wallet_bill;
+----------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sn | varchar(30) | YES | UNI | NULL | |
| store_id | int(11) | NO | MUL | 0 | |
| source_id | int(11) | NO | MUL | NULL | |
| source_type | tinyint(4) | NO | | NULL | |
| bill_type | tinyint(4) | NO | | NULL | |
| money | decimal(10,2) | NO | | 0.00 | |
| income_money | decimal(10,2) | NO | | 0.00 | |
| user_commission | decimal(10,2) | NO | | 0.00 | |
| poundage | decimal(10,2) | NO | | 0.00 | |
| platform_commission | decimal(10,2) | NO | | 0.00 | |
| wallet_money_before | decimal(10,2) | NO | | 0.00 | |
| wallet_money_after | decimal(10,2) | NO | | 0.00 | |
| desc | varchar(255) | YES | | NULL | |
| remark | varchar(255) | YES | | NULL | |
| create_time | timestamp | YES | | NULL | |
| update_time | timestamp | YES | | NULL | |
| delete_time | timestamp | YES | | NULL | |
| account_type | tinyint(3) unsigned | NO | | 0 | |
| source_payment_model | varchar(20) | YES | | NULL | |
| is_virtual | tinyint(4) | NO | | 0 | |
+----------------------+---------------------+------+-----+---------+----------------+
21 rows in set (0.00 sec)
表索引
mysql> show index from sima_store_wallet_bill;
+------------------------+------------+---------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------+------------+---------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sima_store_wallet_bill | 0 | PRIMARY | 1 | id | A | 1596400 | NULL | NULL | | BTREE | | |
| sima_store_wallet_bill | 0 | sn | 1 | sn | A | 1596400 | NULL | NULL | YES | BTREE | | |
| sima_store_wallet_bill | 1 | source_id_type | 1 | source_id | A | 1254391 | NULL | NULL | | BTREE | | |
| sima_store_wallet_bill | 1 | source_id_type | 2 | source_type | A | 1596400 | NULL | NULL | | BTREE | | |
| sima_store_wallet_bill | 1 | idx_storeid_isvirtual_deletetime_createtime | 1 | store_id | A | 1186 | NULL | NULL | | BTREE | | |
| sima_store_wallet_bill | 1 | idx_storeid_isvirtual_deletetime_createtime | 2 | is_virtual | A | 1583 | NULL | NULL | | BTREE | | |
| sima_store_wallet_bill | 1 | idx_storeid_isvirtual_deletetime_createtime | 3 | delete_time | A | 1070 | NULL | NULL | YES | BTREE | | |
| sima_store_wallet_bill | 1 | idx_storeid_isvirtual_deletetime_createtime | 4 | create_time | A | 1596400 | NULL | NULL | YES | BTREE | | |
+------------------------+------------+---------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.00 sec)
查询一，用主键id排序，用时0.7s
mysql> SELECT *
-> FROM `sima_store_wallet_bill`
-> WHERE `store_id` IN (41)
-> AND create_time BETWEEN '2020-07-04 00:00:00' AND '2020-07-04 23:59:59'
-> AND `sima_store_wallet_bill`.`delete_time` IS NULL
-> ORDER BY `id` DESC
-> LIMIT 0, 20;
+---------+----------------------+----------+-----------+-------------+-----------+--------+--------------+-----------------+----------+---------------------+---------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+-------------+--------------+----------------------+------------+
| id | sn | store_id | source_id | source_type | bill_type | money | income_money | user_commission | poundage | platform_commission | wallet_money_before | wallet_money_after | desc | remark | create_time | update_time | delete_time | account_type | source_payment_model | is_virtual |
+---------+----------------------+----------+-----------+-------------+-----------+--------+--------------+-----------------+----------+---------------------+---------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+-------------+--------------+----------------------+------------+
| 1579963 | F2020070422231584766 | 41 | 1388910 | 1 | 1 | 109.00 | 108.59 | 0.00 | 0.41 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070449575597,支付类型:拉卡拉支付宝支付,支付单号:2020070449575597222313 | NULL | 2020-07-04 14:23:15 | 2020-07-04 14:23:15 | NULL | 3 | lakala_alipay | 0 |
| 1579959 | F2020070422223991218 | 41 | 1388915 | 1 | 1 | 149.00 | 148.43 | 0.00 | 0.57 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070452999952,支付类型:拉卡拉支付宝支付,支付单号:2020070452999952222237 | NULL | 2020-07-04 14:22:39 | 2020-07-04 14:22:39 | NULL | 3 | lakala_alipay | 0 |
| 1579800 | F2020070421262758458 | 41 | 1388804 | 1 | 1 | 199.00 | 198.24 | 0.00 | 0.76 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070410055575,支付类型:拉卡拉微信支付,支付单号:2020070410055575212625 | NULL | 2020-07-04 13:26:27 | 2020-07-04 13:26:27 | NULL | 3 | lakala_wechat | 0 |
| 1579483 | F2020070419570341824 | 41 | 1388461 | 1 | 1 | 200.00 | 199.24 | 0.00 | 0.76 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070497525351,支付类型:拉卡拉微信支付,支付单号:2020070497525351195701 | NULL | 2020-07-04 11:57:03 | 2020-07-04 11:57:03 | NULL | 3 | lakala_wechat | 0 |
| 1579408 | F2020070419404976725 | 41 | 657270 | 2 | 1 | 83.00 | 82.68 | 0.00 | 0.32 | 0.00 | 22562.40 | 22562.40 | 流水类型:销售订单支付,订单编号:2020070454579853,支付类型:拉卡拉微信支付,支付单号: | NULL | 2020-07-04 11:40:49 | 2020-07-04 11:40:49 | NULL | 3 | lakala_wechat | 0 |
| 1579399 | F2020070419391533637 | 41 | 1388568 | 1 | 1 | 180.00 | 179.32 | 0.00 | 0.68 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070456531009,支付类型:拉卡拉微信支付,支付单号:2020070456531009193904 | NULL | 2020-07-04 11:39:15 | 2020-07-04 11:39:15 | NULL | 3 | lakala_wechat | 0 |
| 1579358 | F2020070419301089242 | 41 | 1388534 | 1 | 1 | 80.00 | 79.70 | 0.00 | 0.30 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070457984849,支付类型:拉卡拉微信支付,支付单号:2020070457984849193008 | NULL | 2020-07-04 11:30:10 | 2020-07-04 11:30:10 | NULL | 3 | lakala_wechat | 0 |
| 1579161 | F2020070418415164426 | 41 | 1388449 | 1 | 1 | 180.00 | 179.32 | 0.00 | 0.68 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070456975550,支付类型:拉卡拉支付宝支付,支付单号:2020070456975550184149 | NULL | 2020-07-04 10:41:51 | 2020-07-04 10:41:51 | NULL | 3 | lakala_alipay | 0 |
| 1578672 | F2020070417203183263 | 41 | 657051 | 2 | 1 | 28.00 | 27.89 | 0.00 | 0.11 | 0.00 | 22562.40 | 22562.40 | 流水类型:销售订单支付,订单编号:2020070455975455,支付类型:拉卡拉支付宝支付,支付单号: | NULL | 2020-07-04 09:20:31 | 2020-07-04 09:20:31 | NULL | 3 | lakala_alipay | 0 |
| 1578565 | F2020070417002922534 | 41 | 657037 | 2 | 1 | 3.00 | 2.99 | 0.00 | 0.01 | 0.00 | 22562.40 | 22562.40 | 流水类型:销售订单支付,订单编号:2020070457995751,支付类型:拉卡拉微信支付,支付单号: | NULL | 2020-07-04 09:00:29 | 2020-07-04 09:00:29 | NULL | 3 | lakala_wechat | 0 |
| 1578362 | F2020070416150453758 | 41 | 1388060 | 1 | 1 | 110.00 | 109.58 | 0.00 | 0.42 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070498484999,支付类型:拉卡拉支付宝支付,支付单号:2020070498484999161501 | NULL | 2020-07-04 08:15:04 | 2020-07-04 08:15:04 | NULL | 3 | lakala_alipay | 0 |
| 1578300 | F2020070415581150094 | 41 | 1388024 | 1 | 1 | 160.00 | 159.39 | 0.00 | 0.61 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070449494850,支付类型:拉卡拉微信支付,支付单号:2020070449494850155809 | NULL | 2020-07-04 07:58:11 | 2020-07-04 07:58:11 | NULL | 3 | lakala_wechat | 0 |
| 1577924 | F2020070414103717136 | 41 | 1387761 | 1 | 1 | 80.00 | 79.70 | 0.00 | 0.30 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070497565110,支付类型:拉卡拉微信支付,支付单号:2020070497565110141035 | NULL | 2020-07-04 06:10:37 | 2020-07-04 06:10:37 | NULL | 3 | lakala_wechat | 0 |
| 1577566 | F2020070412151919919 | 41 | 1387424 | 1 | 1 | 125.00 | 125.00 | 0.00 | 0.00 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070410097549,支付类型:现金支付 | NULL | 2020-07-04 04:15:19 | 2020-07-04 04:15:19 | NULL | 2 | cash | 0 |
| 1577497 | F2020070411584244284 | 41 | 1386294 | 1 | 1 | 80.00 | 79.70 | 0.00 | 0.30 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070349535056,支付类型:拉卡拉支付宝支付,支付单号:2020070349535056115840 | NULL | 2020-07-04 03:58:42 | 2020-07-04 03:58:42 | NULL | 3 | lakala_alipay | 0 |
| 1577245 | F2020070410483598923 | 41 | 1387280 | 1 | 1 | 160.00 | 159.39 | 0.00 | 0.61 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070452485453,支付类型:拉卡拉微信支付,支付单号:2020070452485453104833 | NULL | 2020-07-04 02:48:35 | 2020-07-04 02:48:35 | NULL | 3 | lakala_wechat | 0 |
+---------+----------------------+----------+-----------+-------------+-----------+--------+--------------+-----------------+----------+---------------------+---------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+-------------+--------------+----------------------+------------+
16 rows in set (0.74 sec)
查询二，用非主键时间排序，用时0.02s
mysql> SELECT *
-> FROM `sima_store_wallet_bill`
-> WHERE `store_id` IN (41)
-> AND create_time BETWEEN '2020-07-04 00:00:00' AND '2020-07-04 23:59:59'
-> AND `sima_store_wallet_bill`.`delete_time` IS NULL
-> ORDER BY `create_time` DESC
-> LIMIT 0, 20;
+---------+----------------------+----------+-----------+-------------+-----------+--------+--------------+-----------------+----------+---------------------+---------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+-------------+--------------+----------------------+------------+
| id | sn | store_id | source_id | source_type | bill_type | money | income_money | user_commission | poundage | platform_commission | wallet_money_before | wallet_money_after | desc | remark | create_time | update_time | delete_time | account_type | source_payment_model | is_virtual |
+---------+----------------------+----------+-----------+-------------+-----------+--------+--------------+-----------------+----------+---------------------+---------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+-------------+--------------+----------------------+------------+
| 1579963 | F2020070422231584766 | 41 | 1388910 | 1 | 1 | 109.00 | 108.59 | 0.00 | 0.41 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070449575597,支付类型:拉卡拉支付宝支付,支付单号:2020070449575597222313 | NULL | 2020-07-04 14:23:15 | 2020-07-04 14:23:15 | NULL | 3 | lakala_alipay | 0 |
| 1579959 | F2020070422223991218 | 41 | 1388915 | 1 | 1 | 149.00 | 148.43 | 0.00 | 0.57 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070452999952,支付类型:拉卡拉支付宝支付,支付单号:2020070452999952222237 | NULL | 2020-07-04 14:22:39 | 2020-07-04 14:22:39 | NULL | 3 | lakala_alipay | 0 |
| 1579800 | F2020070421262758458 | 41 | 1388804 | 1 | 1 | 199.00 | 198.24 | 0.00 | 0.76 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070410055575,支付类型:拉卡拉微信支付,支付单号:2020070410055575212625 | NULL | 2020-07-04 13:26:27 | 2020-07-04 13:26:27 | NULL | 3 | lakala_wechat | 0 |
| 1579483 | F2020070419570341824 | 41 | 1388461 | 1 | 1 | 200.00 | 199.24 | 0.00 | 0.76 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070497525351,支付类型:拉卡拉微信支付,支付单号:2020070497525351195701 | NULL | 2020-07-04 11:57:03 | 2020-07-04 11:57:03 | NULL | 3 | lakala_wechat | 0 |
| 1579408 | F2020070419404976725 | 41 | 657270 | 2 | 1 | 83.00 | 82.68 | 0.00 | 0.32 | 0.00 | 22562.40 | 22562.40 | 流水类型:销售订单支付,订单编号:2020070454579853,支付类型:拉卡拉微信支付,支付单号: | NULL | 2020-07-04 11:40:49 | 2020-07-04 11:40:49 | NULL | 3 | lakala_wechat | 0 |
| 1579399 | F2020070419391533637 | 41 | 1388568 | 1 | 1 | 180.00 | 179.32 | 0.00 | 0.68 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070456531009,支付类型:拉卡拉微信支付,支付单号:2020070456531009193904 | NULL | 2020-07-04 11:39:15 | 2020-07-04 11:39:15 | NULL | 3 | lakala_wechat | 0 |
| 1579358 | F2020070419301089242 | 41 | 1388534 | 1 | 1 | 80.00 | 79.70 | 0.00 | 0.30 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070457984849,支付类型:拉卡拉微信支付,支付单号:2020070457984849193008 | NULL | 2020-07-04 11:30:10 | 2020-07-04 11:30:10 | NULL | 3 | lakala_wechat | 0 |
| 1579161 | F2020070418415164426 | 41 | 1388449 | 1 | 1 | 180.00 | 179.32 | 0.00 | 0.68 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070456975550,支付类型:拉卡拉支付宝支付,支付单号:2020070456975550184149 | NULL | 2020-07-04 10:41:51 | 2020-07-04 10:41:51 | NULL | 3 | lakala_alipay | 0 |
| 1578672 | F2020070417203183263 | 41 | 657051 | 2 | 1 | 28.00 | 27.89 | 0.00 | 0.11 | 0.00 | 22562.40 | 22562.40 | 流水类型:销售订单支付,订单编号:2020070455975455,支付类型:拉卡拉支付宝支付,支付单号: | NULL | 2020-07-04 09:20:31 | 2020-07-04 09:20:31 | NULL | 3 | lakala_alipay | 0 |
| 1578565 | F2020070417002922534 | 41 | 657037 | 2 | 1 | 3.00 | 2.99 | 0.00 | 0.01 | 0.00 | 22562.40 | 22562.40 | 流水类型:销售订单支付,订单编号:2020070457995751,支付类型:拉卡拉微信支付,支付单号: | NULL | 2020-07-04 09:00:29 | 2020-07-04 09:00:29 | NULL | 3 | lakala_wechat | 0 |
| 1578362 | F2020070416150453758 | 41 | 1388060 | 1 | 1 | 110.00 | 109.58 | 0.00 | 0.42 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070498484999,支付类型:拉卡拉支付宝支付,支付单号:2020070498484999161501 | NULL | 2020-07-04 08:15:04 | 2020-07-04 08:15:04 | NULL | 3 | lakala_alipay | 0 |
| 1578300 | F2020070415581150094 | 41 | 1388024 | 1 | 1 | 160.00 | 159.39 | 0.00 | 0.61 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070449494850,支付类型:拉卡拉微信支付,支付单号:2020070449494850155809 | NULL | 2020-07-04 07:58:11 | 2020-07-04 07:58:11 | NULL | 3 | lakala_wechat | 0 |
| 1577924 | F2020070414103717136 | 41 | 1387761 | 1 | 1 | 80.00 | 79.70 | 0.00 | 0.30 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070497565110,支付类型:拉卡拉微信支付,支付单号:2020070497565110141035 | NULL | 2020-07-04 06:10:37 | 2020-07-04 06:10:37 | NULL | 3 | lakala_wechat | 0 |
| 1577566 | F2020070412151919919 | 41 | 1387424 | 1 | 1 | 125.00 | 125.00 | 0.00 | 0.00 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070410097549,支付类型:现金支付 | NULL | 2020-07-04 04:15:19 | 2020-07-04 04:15:19 | NULL | 2 | cash | 0 |
| 1577497 | F2020070411584244284 | 41 | 1386294 | 1 | 1 | 80.00 | 79.70 | 0.00 | 0.30 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070349535056,支付类型:拉卡拉支付宝支付,支付单号:2020070349535056115840 | NULL | 2020-07-04 03:58:42 | 2020-07-04 03:58:42 | NULL | 3 | lakala_alipay | 0 |
| 1577245 | F2020070410483598923 | 41 | 1387280 | 1 | 1 | 160.00 | 159.39 | 0.00 | 0.61 | 0.00 | 22562.40 | 22562.40 | 流水类型:维修订单支付,订单编号:2020070452485453,支付类型:拉卡拉微信支付,支付单号:2020070452485453104833 | NULL | 2020-07-04 02:48:35 | 2020-07-04 02:48:35 | NULL | 3 | lakala_wechat | 0 |
+---------+----------------------+----------+-----------+-------------+-----------+--------+--------------+-----------------+----------+---------------------+---------------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------+--------+---------------------+---------------------+-------------+--------------+----------------------+------------+
16 rows in set (0.02 sec)
用explain分析，竟然分析出id排序的用了主键id做索引，然后在找store_id的值等于41的，这样想起慢也是有原因的。
mysql> explain SELECT *
-> FROM `sima_store_wallet_bill`
-> WHERE `store_id` IN (41)
-> AND create_time BETWEEN '2020-07-04 00:00:00' AND '2020-07-04 23:59:59'
-> AND `sima_store_wallet_bill`.`delete_time` IS NULL
-> ORDER BY `id` DESC
-> LIMIT 0, 20;
+----+-------------+------------------------+------------+-------+---------------------------------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------------+------------+-------+---------------------------------------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | sima_store_wallet_bill | NULL | index | idx_storeid_isvirtual_deletetime_createtime | PRIMARY | 4 | NULL | 1845 | 0.01 | Using where |
+----+-------------+------------------------+------------+-------+---------------------------------------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT *
-> FROM `sima_store_wallet_bill`
-> WHERE `store_id` IN (41)
-> AND create_time BETWEEN '2020-07-04 00:00:00' AND '2020-07-04 23:59:59'
-> AND `sima_store_wallet_bill`.`delete_time` IS NULL
-> ORDER BY `create_time` DESC
-> LIMIT 0, 20;
+----+-------------+------------------------+------------+------+---------------------------------------------+---------------------------------------------+---------+-------+-------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------------+------------+------+---------------------------------------------+---------------------------------------------+---------+-------+-------+----------+---------------------------------------+
| 1 | SIMPLE | sima_store_wallet_bill | NULL | ref | idx_storeid_isvirtual_deletetime_createtime | idx_storeid_isvirtual_deletetime_createtime | 4 | const | 17302 | 1.11 | Using index condition; Using filesort |
+----+-------------+------------------------+------------+------+---------------------------------------------+---------------------------------------------+---------+-------+-------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)
总结：这个表的索引idx_storeid_isvirtual_deletetime_createtime 有问题。
第一，isvirtual的值只有1，0，而多数查询的时候只需isvirtual = 0和所有，所以没必要建立索引；
第二，delete_time软删除字段，一般都是`delete_time` IS NULL，故不需要加索引，所以修改索引字段idx_storeid_createtime
explain 参数key_len来检验联合索引字段使用
如上面的例子，最后用explain分析使用字段情况，key都为4。这个怎么计算的呢？一个整形占用四个字节，所以为4，也就是idx_storeid_isvirtual_deletetime_createtime索引 只用到了store_id这个字段。另外提示下，是字符串的话，如果选用了utf8，就是占用三个字节。还有可为空字段，带有null，要多占用一个字节存储。
索引字段当条件类型要符合，整形例外。
如你在整形int上建立索引，你可以字符串当搜索条件，如store_id = ‘1’,这样是可以用到索引的。但是如果是字符串上建立索引，用整形当搜索条件是无效的， 如transaction_id = 1234567890。
字符串索引长度太长记得加长度限制，节省存储空间。
如下,支付记录的第三方交易号transaction_id加索引，字段设置默认255。支付宝回调30个数字，微信28个，所以这里长度冗余了5个设置为35，操作如下：
ALTER TABLE `sima_shop_order_payment`
ADD KEY `idx_transactionid`(`transaction_id`(35)) USING BTREE;
count(*) 优化
在innodb引擎中，count(*)会全表扫描一个一个计算。所以表大的时候select count(*) from table最好少点使用，取而代之的可以默认加上时间条件 筛选。实在不行要弄个计数表额外统计，有点麻烦。
感谢
感谢v2网友们的帮助：大佬们求救，慢 SQL 问题
迭代
- 2020年08月06日 15:45:44 初稿
- 2020年08月24日 17:12:44 修改
参考：
- 1、explain之key_len计算
- 2、MySQL带时间字段的范围查询不走索引了？_ＬａｍｂｅｒｔＣＯＬ的博客-CSDN博客_mysql时间范围查询不走索引
- 3、MySQL索引失效的几种情况 - 且过 - 博客园
本作品采用CC BY-NC-ND 4.0进行许可。转载，请注明原作者 chunpat 及本文源链接。