手把手教新人調(diào)優(yōu)

大家好，我是黎杜，最近給新人code review，差點(diǎn)吐血，也有一些調(diào)優(yōu)方面的知識(shí)，所以順便記錄了下來(lái)分享給大家。

這里的主要是分享索引方面的調(diào)優(yōu)，在工作中，很多同學(xué)都有建立索引的一些經(jīng)驗(yàn)，但是是否有自己深入的思考過(guò)，怎么樣建立索引才最合適。

字符串怎么建立索引、怎么優(yōu)化聯(lián)合索引、怎么避免回表等一些問(wèn)題，是否有結(jié)合自己的實(shí)際項(xiàng)目進(jìn)行深入的思考呢？

這里，我就將自己實(shí)際中遇到的一些問(wèn)題分享給大家，下面開(kāi)始我們的正題，首先開(kāi)始之前，先來(lái)回顧一些基礎(chǔ)的知識(shí)。

什么是Mysql的索引，聯(lián)合索引是什么？回表是什么？回表怎么解決？

Mysql索引

Mysql的索引是一種加快查詢速度的數(shù)據(jù)結(jié)構(gòu)，索引就好比書(shū)的目錄一樣能夠快速的定位你要查找的位置。

Mysql的索引底層是使用B+樹(shù)的數(shù)據(jù)結(jié)構(gòu)進(jìn)行實(shí)現(xiàn)，結(jié)構(gòu)如下圖所示：

索引的一個(gè)數(shù)據(jù)頁(yè)的大小是16kb，從磁盤(pán)加載到內(nèi)存中是以數(shù)據(jù)頁(yè)的大小為單位進(jìn)行加載，然后供查詢操作進(jìn)行查詢，若是查詢的數(shù)據(jù)不在內(nèi)存中，才會(huì)從磁盤(pán)中再次加載到內(nèi)存中。

索引的實(shí)現(xiàn)有很多，比如hash。hash是以key-value的形式進(jìn)行存儲(chǔ)，適合于等值查詢的場(chǎng)景，查詢的時(shí)間復(fù)雜度為O(1)，因?yàn)閔ash儲(chǔ)存并不是有序的。

所以，對(duì)于范圍查詢就可能要遍歷所有數(shù)據(jù)進(jìn)行查詢，而且不同值的計(jì)算還會(huì)出現(xiàn)hash沖突，所以hash并不適合于做Mysql的索引。

有序數(shù)組在等值查詢和范圍查詢性能都是非常好的，那為什么又不用有序數(shù)組作為索引呢？因?yàn)閷?duì)于數(shù)組而言作為索引更新的成本太高，新增數(shù)據(jù)要把后面的數(shù)據(jù)都往后移一位，所以也不采用有序數(shù)組作為索引的底層實(shí)現(xiàn)。

最后二叉樹(shù)，主要是因?yàn)槎鏄?shù)只有二叉，一個(gè)節(jié)點(diǎn)存儲(chǔ)的數(shù)據(jù)量非常有限，需要頻繁的隨機(jī)IO讀寫(xiě)磁盤(pán)，若是數(shù)據(jù)量大的情況下二叉的樹(shù)高太高，嚴(yán)重影響性能，所以也不采用二叉樹(shù)進(jìn)行實(shí)現(xiàn)。

而B(niǎo)+樹(shù)是多叉樹(shù)，一個(gè)數(shù)據(jù)頁(yè)的大小是16kb，在1-3的樹(shù)高就能存儲(chǔ)10億級(jí)以上的數(shù)據(jù)，也就是只要訪問(wèn)磁盤(pán)1-3次就足夠了，并且B+樹(shù)的葉子結(jié)點(diǎn)上一個(gè)葉子結(jié)點(diǎn)有指針指向下一個(gè)葉子結(jié)點(diǎn)，便于范圍查詢：

種類的索引

索引從數(shù)據(jù)結(jié)構(gòu)進(jìn)行劃分的分為：B+樹(shù)索引、hash索引、R-Tree索引、FULLTEXT索引。

索引從物理存儲(chǔ)的角度劃分為：聚族索引和非聚族索引。

從邏輯的角度分為：主鍵索引、普通索引、唯一索引、聯(lián)合索引以及空間索引。

什么是回表

再詳細(xì)了解什么事回表之前，我們先來(lái)詳細(xì)的深入了解一下什么是InnoDB的索引存儲(chǔ)形式，InnoDB的主鍵索引存儲(chǔ)形式是聚族索引，索引與數(shù)據(jù)都在一起：

InnoDB的主鍵索引中葉子結(jié)點(diǎn)并不是存儲(chǔ)行指針，而是存儲(chǔ)行數(shù)據(jù)，二級(jí)索引中MyISAM也是一樣的存儲(chǔ)方式，InnoDB的二級(jí)索引的葉子結(jié)點(diǎn)則是存儲(chǔ)當(dāng)前索引值以及對(duì)應(yīng)的主鍵索引值。

InnoDB的二級(jí)索引帶來(lái)的好處就是減少了由于數(shù)據(jù)移動(dòng)或者數(shù)據(jù)頁(yè)分列導(dǎo)致行數(shù)據(jù)的地址變了而帶來(lái)的維護(hù)二級(jí)索引的性能開(kāi)銷，因?yàn)镮nnoDB的二級(jí)索引不需要更新行指針：

上面說(shuō)到InnoDB引擎的主鍵索引存儲(chǔ)的是行數(shù)據(jù)，二級(jí)索引的葉子結(jié)點(diǎn)存儲(chǔ)的是索引數(shù)據(jù)以及對(duì)應(yīng)的主鍵，所以回表就是根據(jù)索引進(jìn)行條件查詢，回到主鍵索引樹(shù)進(jìn)行搜索的過(guò)程：

因?yàn)椴樵冞€要回表一次，再次查詢主鍵索引樹(shù)，所以實(shí)際中應(yīng)該盡量避免回表的產(chǎn)生。

解決回表

解決回表問(wèn)題可以建立聯(lián)合索引進(jìn)行索引覆蓋，如圖所示根據(jù)name字段查詢用戶的name和sex屬性出現(xiàn)了回表問(wèn)題：

那么我們可以建立下面這個(gè)聯(lián)合索引來(lái)解決：

create table user (
 id int primary key,
 name varchar(20),
 sex varchar(5),
 index(name, sex)
) engine = innodb;

建立了如上所示的index(name,sex)聯(lián)合索引，在二級(jí)索引的葉子結(jié)點(diǎn)的位置就會(huì)同時(shí)也出現(xiàn)sex字段的值，因?yàn)槟軌颢@取到要查詢的所有字段，因?yàn)榫筒挥迷倩乇聿樵円淮巍?/p>

最左前綴原則

最左前綴原則可以是聯(lián)合索引的的最左N個(gè)字段，也可以是字符串索引的最左的M個(gè)字符。舉個(gè)例子，假如現(xiàn)在有一個(gè)表的原始數(shù)據(jù)如下所示：

并根據(jù)col3 ，col2的順序建立聯(lián)合索引，此時(shí)聯(lián)合索引樹(shù)結(jié)構(gòu)如圖下所示：

葉子結(jié)點(diǎn)中首先會(huì)根據(jù)col3的字符進(jìn)行排序，若是col3相等，在col3相等的值里面再對(duì)col2進(jìn)行排序，假如我們要查詢where col3 like 'Eri%'，就可以快速的定位查詢到Eric。

若是查詢條件為where col3 like '%se'，前面的字符不確定，表示任意字符都可以，這樣就可以導(dǎo)致全表掃描進(jìn)行字符的比較，就會(huì)使索引失效。

調(diào)優(yōu)案例一

其中第一個(gè)案例新人寫(xiě)了一個(gè)這樣的sql，由于業(yè)務(wù)的原因就不粘貼全部sql，其中sql的條件如下所示WHERE ( STATUS = '1' AND shop_code = 'XXX' )  GROUP BY act_id。

并且他在表中建立的這樣的一個(gè)索引idx_status_shop_code，當(dāng)時(shí)看到就吐血一地。

我給新人提出建議是把shop_code為''空字符串的（數(shù)據(jù)庫(kù)默認(rèn)值）是設(shè)為一個(gè)特殊的00000這樣的類型，不要把商店的shop_code默認(rèn)值設(shè)置為空字符串。

并且，索引中shop_code字段放在前面，建idx_shop_code_status_act_id索引。

因?yàn)橐话銧顟B(tài)值status只有0和1，區(qū)分度不大，而shop_code的區(qū)分度大，在執(zhí)行where條件篩選的時(shí)候，區(qū)分度大的放在前面，第一層過(guò)濾的時(shí)候就能過(guò)濾掉大部分行數(shù)，減少掃描的行數(shù)，提高效率。

并且將act_id，和store_code也加入其中，sql中涉及分組（group by）操作，這樣就能避免filesort排序，因?yàn)樗饕緛?lái)就是有序的：

并且從他的sql中可以看到他只要返回act_id，而實(shí)際中只用到了act_id，所以將act_id也加入索引中就可以避免回表的操作，所以再索引中最后加入act_id有兩大好處：避免回表以及避免filesort排序。

因?yàn)閷?xiě)sql的不良習(xí)慣造成回表操作，平時(shí)也沒(méi)有注意建立索引的一些原則，以及理解索引的一些原理，所以新人對(duì)于一些優(yōu)化的理解還有要一步一步的指導(dǎo)，畢竟自己也是從新人過(guò)來(lái)的。

對(duì)于索引的順序的建立，以及出現(xiàn)filesort的解決方案在阿里的開(kāi)發(fā)手冊(cè)中也有提到：

order by和group by都可以利用索引的有序性。

說(shuō)真的這本開(kāi)發(fā)手冊(cè)還是非常好用的，里面很多經(jīng)驗(yàn)總結(jié)，慢慢的遇到場(chǎng)景就能夠瞬間頓悟，畢竟是眾多阿里人的開(kāi)發(fā)經(jīng)驗(yàn)的結(jié)晶。

調(diào)優(yōu)案例二

第二個(gè)案例是關(guān)于字符串的，新人接到一個(gè)需求需要比較電話，但是一般電話在數(shù)據(jù)庫(kù)中都會(huì)加密md5。

所以，新人為了提高查詢的效率新建KEY idx_mobile_md5_type (mobile_md5,type)使用md5全字段建立索引。

我是建議他使用select count(distinct left(mobile_md5, 5))/count(*) from XXX.users查找最大的區(qū)分度：

在實(shí)際中當(dāng)md5值長(zhǎng)度為5以及大于5的長(zhǎng)度都不變了：

實(shí)際情況只要前五個(gè)字符就能達(dá)到80%的區(qū)分度，并且再加字段長(zhǎng)度區(qū)分度也不變，所以個(gè)人提出只要建立前五個(gè)字符的索引即可，可以大大節(jié)約空間。

這個(gè)在阿里的開(kāi)發(fā)手冊(cè)也有提到，其實(shí)一般來(lái)說(shuō)達(dá)到90%的區(qū)分度是比較好的，區(qū)分度越大，就類似于越趨向于唯一索引，過(guò)濾的行數(shù)就越多：

調(diào)優(yōu)案例三

最后一個(gè)字符串的案例就是userId，這個(gè)userId使用有20位的長(zhǎng)度的字符串左右，有點(diǎn)類似于身份證號(hào)碼，大家都知道身份證號(hào)碼的前多少位是基本一樣的，區(qū)別大的在后面的幾位（具體幾位沒(méi)去了解過(guò)）。

我們這邊的場(chǎng)景也是一樣，userId前10位左右基本都是一樣，反而只有后面的幾位區(qū)別度高達(dá)90%以上。

所以，建議新人建立userId的反轉(zhuǎn)之后的前幾位索引即可，區(qū)別度可以通過(guò)：select count(distinct left(REVERSE(userId), 7))/count(*) as '區(qū)分度' from XXX.users;

具體sql如下的sql：select u.city_code from XXX.city_role_user u where  role_key = 'XXX' and uc_id = 'XXX' and status = 1;

這個(gè)sql有兩個(gè)問(wèn)題，一個(gè)是把區(qū)分度不大的role_key放在前面，因?yàn)橐话憬巧玨ey在PC端只有幾種，區(qū)別度很小；另一個(gè)就是前面說(shuō)的uc_id字符串問(wèn)題。

我是建議把where條件的條件uc_id放在前面，建立索引也是如此，并且uc_id是由20位的數(shù)字組成，前面的10位左右都是一樣的，只有后面的幾位區(qū)分度才大。

所以個(gè)人也建議通過(guò)查詢區(qū)分度，并且建立翻轉(zhuǎn)字符串后的索引來(lái)達(dá)到節(jié)省空間，并且還可以提升查詢效率，最后就是city_code也加入索引中建立聯(lián)合索引就可以避免回表操作。

所以，這就要sql優(yōu)化的關(guān)鍵點(diǎn)有三個(gè)：區(qū)分度大的放在前面、字符串減少長(zhǎng)度、避免回表。

其它的code review

通過(guò)code review新人的代碼，還發(fā)現(xiàn)一些問(wèn)題，就是不遵循接口的單一原則，比較喜歡寫(xiě)通用的接口，一個(gè)接口多個(gè)場(chǎng)景使用，通常使用select * 返回?cái)?shù)據(jù)，對(duì)于一些where條件的查詢需要大量的回表操作，但是一些接口中只需要用到其中select 回來(lái)的一個(gè)字段，所以導(dǎo)致慢sql，慢接口的產(chǎn)生。

并且，在實(shí)際的編碼中主要是面向于實(shí)現(xiàn)，對(duì)于一些整體的模塊沒(méi)有把控，類似于一些可以使用到策略模式、建造者模式等設(shè)計(jì)模式的，都沒(méi)有使用，代碼的擴(kuò)展性比較差。

還要在代碼中大量的使用Java 8的stream流操作，代碼的可讀性差，對(duì)于stream流其實(shí)可以用來(lái)并行流處理還是挺高效的，因?yàn)閟tream流的底層使用到了Fork/Join。

在服務(wù)器配置允許的條件下，使用如下代碼，數(shù)據(jù)量大的時(shí)候是可以有效率提升的，下面引用redspider的一個(gè)案例：

public class StreamParallelDemo {
    public static void main(String[] args) {
        System.out.println(String.format("本計(jì)算機(jī)的核數(shù)：%d", Runtime.getRuntime().availableProcessors()));

        // 產(chǎn)生100w個(gè)隨機(jī)數(shù)(1 ~ 100)，組成列表
        Random random = new Random();
        List<Integer> list = new ArrayList<>(1000_0000);

        for (int i = 0; i < 1000_0000; i++) {
            list.add(random.nextInt(100));
        }

        long prevTime = getCurrentTime();
        list.stream().reduce((a, b) -> a + b).ifPresent(System.out::println);
        System.out.println(String.format("單線程計(jì)算耗時(shí)：%d", getCurrentTime() - prevTime));

        prevTime = getCurrentTime();
        list.stream().parallel().reduce((a, b) -> a + b).ifPresent(System.out::println);
        System.out.println(String.format("多線程計(jì)算耗時(shí)：%d", getCurrentTime() - prevTime));

    }

    private static long getCurrentTime() {
        return System.currentTimeMillis();
    }
}

一路code review，發(fā)現(xiàn)還是挺多問(wèn)題，也是非常基礎(chǔ)的東西，這里就順手做了個(gè)記錄，不過(guò)也情有可原，畢竟是新人，只能慢慢的指導(dǎo)，一行代碼一行代碼的手把手教。

好了，這一期就到這里，我是黎杜，我們下一期見(jiàn)。

歡迎關(guān)注微信公眾號(hào)：互聯(lián)網(wǎng)全棧架構(gòu)，收取更多有價(jià)值的信息。