biz_cloudsign_login是登錄記錄速侈， 表數(shù)據(jù)28萬(wàn)绍移。
需求是：查詢(xún)每個(gè)人最新登錄記錄倦微。
表結(jié)構(gòu)
CREATE TABLE `biz_cloudsign_login` (
`id` bigint(20) NOT NULL AUTO_INCREMENT ,
`business_system_code` int(11) NOT NULL ,
`user_department` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`employee_num` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`user_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`identity_number` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`client_id` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`client_ip` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`random_num` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`cert_id` varchar(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`encrypted_token` longtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`),
INDEX `index_eml` (`id`, `employee_num`) USING BTREE ,
INDEX `index_employee_num` (`employee_num`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=289464
ROW_FORMAT=DYNAMIC
;
思路：
1揍瑟、使用子查詢(xún)先將目標(biāo)表的 id,employee_num 查詢(xún)出來(lái)少欺，根據(jù)id倒敘排列（id自增)
2喳瓣、再使用id進(jìn)行自關(guān)聯(lián)查詢(xún)，按employee_num 分組
SELECT
b.*
FROM
( SELECT id,employee_num FROM biz_cloudsign_login ORDER BY id DESC ) a join biz_cloudsign_login b ON a.id = b.id
GROUP BY
a.employee_num order by null
添加了相應(yīng)索引的最優(yōu)執(zhí)行計(jì)劃
是先使用SELECT id,employee_num FROM biz_cloudsign_login ORDER BY id DESC 將所有的id,employee_num 記錄查詢(xún)出來(lái)赞别，盡管加上了索引畏陕。但是由于是全索引的掃描。也是非常慢的氯庆。事實(shí)上這里查詢(xún)出了我們本不需要的數(shù)據(jù)：舊的登錄記錄
優(yōu)化寫(xiě)法
巧妙組合使用 MAX( id )和按 employee_num分組直接達(dá)到了排序的目的蹭秋，因?yàn)檫@個(gè)表的id是int類(lèi)型且自增。那么SELECT MAX( id ) 'id' FROM biz_cloudsign_login GROUP BY employee_num 就直接查詢(xún)出了最新的每個(gè)人的登錄記錄id堤撵。然后再關(guān)聯(lián)查詢(xún)就好了仁讨！這種方式就比上面的方式掃描的數(shù)據(jù)少了很多
SELECT
v.*
FROM
biz_cloudsign_login v
INNER JOIN ( SELECT MAX( id ) 'id' FROM biz_cloudsign_login GROUP BY employee_num ) c ON v.id = c.id
查看執(zhí)行計(jì)劃
比起上面的查詢(xún)，這條sql只需要掃描 1571+1571 三千條數(shù)據(jù)就能得到執(zhí)行結(jié)果实昨。而上面卻要將整個(gè)表掃描一遍洞豁，那就是28萬(wàn)！
若數(shù)據(jù)量實(shí)在是太大荒给，我們可以根據(jù)服務(wù)器配置酌情調(diào)大buff pool丈挟。會(huì)有顯著的優(yōu)化效果。具體看看這篇
http://www.reibang.com/p/5bf36975fd73
SET GLOBAL innodb_buffer_pool_size = 6442450944 -- 6G
