索引使用规范

      01.索引字段数据类型隐式转换，将导致索引失效。

        案例如下：

work@ (xxx) > show create table signG;
*************************** 1. row ***************************
       Table: user_sign
Create Table: CREATE TABLE `sign` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `appid` bigint(20) unsigned NOT NULL,
  `sign_task_id` bigint(20) unsigned NOT NULL,
  `uid` varchar(64) NOT NULL,
  `push_flag` int(10) NOT NULL DEFAULT '0' COMMENT '签到提醒标志 0=不提醒, 1=提醒',
  `current_counter` bigint(20) unsigned NOT NULL DEFAULT '0',
  `total_counter` bigint(20) unsigned NOT NULL DEFAULT '0',
  `last_sign_time` bigint(20) NOT NULL DEFAULT '0',
  `create_time` bigint(20) NOT NULL DEFAULT '0',
  `update_time` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`appid`),
  UNIQUE KEY `uk_appid_task_id_uid` (`sign_task_id`,`uid`,`appid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1259002273035255811 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

ERROR: 
No query specified


 work@ (xxx) > select * from sign WHERE sign_task_id = 1 AND uid = 1 and appid = 959462;  
+---------------------+--------+--------------+-----+-----------+-----------------+---------------+----------------+---------------+---------------+
| id                  | appid  | sign_task_id | uid | push_flag | current_counter | total_counter | last_sign_time | create_time   | update_time   |
+---------------------+--------+--------------+-----+-----------+-----------------+---------------+----------------+---------------+---------------+
| 1194541191434104834 | 959462 |            1 | 1   |         0 |               1 |            69 |  1583115350530 | 1573635788187 | 1583115350530 |
+---------------------+--------+--------------+-----+-----------+-----------------+---------------+----------------+---------------+---------------+
1 row in set (5.30 sec)

Sat May  9 14:13:33 2020 work@ (xxx) > desc select * from sign WHERE sign_task_id = 1 AND uid = 1 and appid = 959462;
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+---------+----------+-----------------------+
| id | select_type | table     | partitions | type | possible_keys        | key                  | key_len | ref   | rows    | filtered | Extra                 |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+---------+----------+-----------------------+
|  1 | SIMPLE      | user_sign | NULL       | ref  | uk_appid_task_id_uid | uk_appid_task_id_uid | 8       | const | 4762484 |     1.00 | Using index condition |
+----+-------------+-----------+------------+------+----------------------+----------------------+---------+-------+---------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)

Sat May  9 14:18:32 2020

发现上面的sql执行花费了5秒多，该表数据量为9825755
##########################################################################################################


 work@ (xxx) > select * from sign WHERE sign_task_id = 1 AND uid = '1' and appid = 959462;     
+---------------------+--------+--------------+-----+-----------+-----------------+---------------+----------------+---------------+---------------+
| id                  | appid  | sign_task_id | uid | push_flag | current_counter | total_counter | last_sign_time | create_time   | update_time   |
+---------------------+--------+--------------+-----+-----------+-----------------+---------------+----------------+---------------+---------------+
| 1194541191434104834 | 959462 |            1 | 1   |         0 |               1 |            69 |  1583115350530 | 1573635788187 | 1583115350530 |
+---------------------+--------+--------------+-----+-----------+-----------------+---------------+----------------+---------------+---------------+
1 row in set (0.01 sec)

Sat May  9 14:18:39 2020 work@ (xxx) > desc select * from sign WHERE sign_task_id = 1 AND uid = '1' and appid = 959462;
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys        | key                  | key_len | ref               | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | user_sign | NULL       | const | uk_appid_task_id_uid | uk_appid_task_id_uid | 274     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+----------------------+----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

Sat May  9 14:18:46 2020

发现上面的sql执行，花费了0.01秒，仅仅是将“uid = 1”改成了“uid = '1'”而已，结果差距这么大。这是因为uid字段是一个varchar类型。若使用uid=1，则将会发生数据类型的隐式转换，故而导致索引失效，从而导致是一个慢查询。
############################################################################################################