# Time: 150528 10:05:06
# User@Host: ufutx[ufutx] @ localhost []
# Query_time: 1.117478 Lock_time: 0.000777 Rows_sent: 200 Rows_examined: 7264
use ufutx;
SET timestamp=1432778706;
select group_concat(uid) as route, count(uid) as cardnum, `fuid` as `target`, 'friend' as source from `ufutx_friend` where `uid` in ('2', '3', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93', '94', '95', '96', '97', '99', '100', '101', '102', '103', '104', '105', '106', '108', '109', '110', '111', '112', '113', '114', '116', '117', '118', '119', '120', '121', '122', '123', '125', '129', '130', '131', '132', '133', '134', '135', '141', '144', '145', '146', '152', '164', '167', '168', '183', '191', '192', '193', '194', '196', '197', '199', '200', '205', '208', '213', '219', '223', '225', '226', '227', '228', '229', '233', '237', '241', '242', '245', '252', '256', '258', '262', '265', '268', '269', '270', '272', '274', '275', '285', '286', '287', '288', '289', '290', '292', '322', '324', '326', '331', '332', '333', '334', '336', '347', '355', '361', '396', '401', '402', '406', '407', '408', '410', '414', '428', '429', '432', '433', '435', '436', '448', '450', '452', '454', '457', '460', '463', '465', '466', '474', '477', '481', '482', '483', '485', '487', '489', '491', '495', '497', '498', '502', '507', '508', '514', '515', '516', '518', '520', '521', '522', '523', '524', '526', '527', '529', '530', '531', '532', '534', '538', '539', '540', '543', '545', '549', '550', '554', '556', '558', '559', '560', '561', '562', '564', '565', '567', '573', '577', '580', '581', '584', '588', '595', '599', '613', '621', '622', '623', '631', '649', '682', '683', '684', '686', '707', '721', '726', '737', '738', '739', '744', '751', '757', '759', '760', '864', '888', '893', '902', '917', '1033', '1036', '1066', '1070', '1071', '1086', '1101', '1104', '1106', '1109', '1111', '1120', '1128') and `fuid` not in ('2', '3', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '34', '35', '36', '37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49', '50', '51', '52', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '67', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '84', '85', '86', '87', '88', '89', '90', '91', '92', '93', '94', '95', '96', '97', '99', '100', '101', '102', '103', '104', '105', '106', '108', '109', '110', '111', '112', '113', '114', '116', '117', '118', '119', '120', '121', '122', '123', '125', '129', '130', '131', '132', '133', '134', '135', '141', '144', '145', '146', '152', '164', '167', '168', '183', '191', '192', '193', '194', '196', '197', '199', '200', '205', '208', '213', '219', '223', '225', '226', '227', '228', '229', '233', '237', '241', '242', '245', '252', '256', '258', '262', '265', '268', '269', '270', '272', '274', '275', '285', '286', '287', '288', '289', '290', '292', '322', '324', '326', '331', '332', '333', '334', '336', '347', '355', '361', '396', '401', '402', '406', '407', '408', '410', '414', '428', '429', '432', '433', '435', '436', '448', '450', '452', '454', '457', '460', '463', '465', '466', '474', '477', '481', '482', '483', '485', '487', '489', '491', '495', '497', '498', '502', '507', '508', '514', '515', '516', '518', '520', '521', '522', '523', '524', '526', '527', '529', '530', '531', '532', '534', '538', '539', '540', '543', '545', '549', '550', '554', '556', '558', '559', '560', '561', '562', '564', '565', '567', '573', '577', '580', '581', '584', '588', '595', '599', '613', '621', '622', '623', '631', '649', '682', '683', '684', '686', '707', '721', '726', '737', '738', '739', '744', '751', '757', '759', '760', '864', '888', '893', '902', '917', '1033', '1036', '1066', '1070', '1071', '1086', '1101', '1104', '1106', '1109', '1111', '1120', '1128') and `fuid` <> '4' group by `fuid`;
需要0.3秒，需要降下去
排查顺序
-
以为是uid fuid没有索引，检查后，都有
-
去除 not in 与 in 语句，速度上去了，怀疑是not in 与 in 的效率问题，但是找不到任何理论的支撑
-
中看到 “最后，应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。”，uid fuid都是int类型，参数给出的却是字符，可以判断是mysql内部进行了类型转换，使得索引不起作用，所有
改进步骤
-
生成这些字条参数的是用laravel 里面的 querybuilder notwherein('uid', $arrayid)
-
在 $arrayid 生成的时候，用 (int) 强制转换成 数字数组
再运行 0.03秒
