摘要

最近遇到一個(gè)慢sql，在排查過(guò)程中發(fā)現(xiàn)和分庫(kù)分表后的索引設(shè)置有關(guān)系，總結(jié)了下問(wèn)題。

問(wèn)題

在進(jìn)行應(yīng)用健康度盤(pán)點(diǎn)時(shí)，發(fā)現(xiàn)有個(gè)慢sql

如下

select brandgoodid from brandgood_0020

where userid = xxx AND

brandgoodid in("xxx1","xxx2")

表結(jié)構(gòu),按照userid進(jìn)行的分表。

CREATE TABLE`brandgood_0020` (

`brandgoodid` char(30) NOT NULL COMMENT ,

`user_id` int(10) unsigned DEFAULT NULL COMMENT '用戶(hù)id',

`created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,

`last_modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`deleted` bit(1) NOT NULL DEFAULT b'0',

PRIMARY KEY (`brandgoodid`),

KEY `idx_userid` (`userid`) USING BTREE,

) ENGINE=InnoDB DEFAULT CHARSET=utf8

explain一下發(fā)現(xiàn)走的是userid這個(gè)索引，一個(gè)用戶(hù)下面有很多商品，也就有了很多brandgoodid，所以有可能會(huì)很慢，因?yàn)橐獟呙韬芏嗟乃饕I去過(guò)濾brandgoodid值。

而寫(xiě)這個(gè)SQL的人期望走的主鍵索引，而不是'userid'的索引。因?yàn)橛弥麈I索引，就是N次主鍵掃描(N表示in中的數(shù)量)。

分析

直接原因很明顯

IN 這個(gè)查詢(xún)誤導(dǎo)了mysql的優(yōu)化器，選錯(cuò)了索引

IN 查詢(xún)常常會(huì)影響mysql server的判斷。主要是IN里面的值數(shù)量不同，會(huì)影響掃描行數(shù)的不同，所以常常會(huì)出現(xiàn)索引選擇不一致。之前也總結(jié)過(guò)一篇SQL IN 一定走索引嗎

解決

因?yàn)橛脩?hù)查詢(xún)的brandgoodlid是限定在某個(gè)group維度下的，一個(gè)group對(duì)應(yīng)的brandgood是有限的，在這個(gè)業(yè)務(wù)中，通常小于10。所以這個(gè)地方使用主鍵索引，效率更高。解決方法也就是這地方需要force index強(qiáng)制走PRIMARY index。

擴(kuò)展

分庫(kù)分表后的索引

為什么題目叫分庫(kù)分表后的索引問(wèn)題的，直接原因和分庫(kù)分表并沒(méi)有什么關(guān)系啊？

因?yàn)樵谂挪閱?wèn)題時(shí)，犯了一個(gè)錯(cuò)誤。以為路由到具體的brandgood_0020表后，可以直接根據(jù)brandgoodid主鍵索引來(lái)查詢(xún)了。認(rèn)為和一些分布式數(shù)據(jù)庫(kù)(cassandra)一樣,是clustering key+partition key這種索引數(shù)據(jù)。可以根據(jù)clustering key到數(shù)據(jù)的節(jié)點(diǎn)的partition塊，然后根據(jù)local index 找到對(duì)應(yīng)的數(shù)據(jù)。

但其實(shí)mysql的分庫(kù)分表不一樣，分表鍵不是索引，只是客戶(hù)端路由。只負(fù)責(zé)找到對(duì)應(yīng)的表。到表以后，就是和單表一樣查詢(xún)邏輯。

因?yàn)榉直礞I不是索引，但是查詢(xún)語(yǔ)句是必須要帶著分表鍵，那意味著我們的分庫(kù)分表以后的表索引大部分要建成聯(lián)合索引了，分表鍵+索引鍵。

要不然我們的查詢(xún)語(yǔ)句 select xx from table where 分表鍵=xxx AND a =xxx，是走不了聯(lián)合索引的。只能走單索引。單索引mysql server要面臨著索引選擇的問(wèn)題。

當(dāng)然并不是絕對(duì)的，比如上面我舉的那個(gè)案例。按照這個(gè)思路查看了下其他的分表索引。果然表上的大部分索引都是非聯(lián)合索引，還是直接從單表copy過(guò)來(lái)的索引。這些索引基本上都是無(wú)用的，因?yàn)槎嫉氖莡serid索引.

索引選擇的問(wèn)題

我們這個(gè)案例是因?yàn)榕袛鄴呙栊袛?shù)的時(shí)候出問(wèn)題了。

總結(jié)

以上是生活随笔為你收集整理的mysql分表后怎么索引_分库分表后的索引问题的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。