本文提要

最近寫的幾篇文章都是關于數據層優(yōu)化方面的，這幾天也在想還有哪些地方可以優(yōu)化改進罩驻，結合日志和項目代碼發(fā)現穗酥，關于數據層的優(yōu)化，還是有幾個方面可以繼續(xù)修改的惠遏，代碼方面砾跃，整合了druid數據源也開啟了sql監(jiān)控等，修改和規(guī)范了變量的命名方式节吮，建表時的命名方式也做了修改抽高，不過做的這些還不夠，優(yōu)化這件事真是一個長期和自覺的過程透绩，mapper文件中的sql語句翘骂，依然不是十分的符合規(guī)范，有繼續(xù)優(yōu)化的必要帚豪，數據庫中表的結構也需要繼續(xù)優(yōu)化碳竟。

前一篇文章主要講了慢sql的整理，以及explain關鍵字在優(yōu)化查詢語句中的作用狸臣，也對sql做了一點小改動莹桅，但是修改的力度不是特別大，這一篇會稍微補充一下前一篇中關于索引的知識點烛亦，以及使用索引對查詢語句進行優(yōu)化诈泼。本文依然是代碼優(yōu)化和sql優(yōu)化，主要還是講一下索引及案例煤禽，前一篇文章較為簡略铐达，為了彌補這個懶惰造成的結果，這篇文章就詳細一些呜师。

表結構優(yōu)化

建表語句規(guī)范娶桦，對原來的表結構重新做了一下修改贾节，主要有：

• 字段非空修改
• 添加字段注釋
• 添加索引(下面一個段落細講)

這里只貼出部分修改后的代碼汁汗，其余的自行通過github更新：

CREATE TABLE `ssm_article` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `article_title` varchar(100) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '文章標題',
  `article_create_date` varchar(50) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '創(chuàng)建時間',
  `article_content` text COLLATE utf8_bin NOT NULL COMMENT '文章內容',
  `is_top` int(4) NOT NULL DEFAULT '0' COMMENT '是否置頂，1為置頂栗涂，默認為0',
  `add_name` varchar(50) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '添加人',
  PRIMARY KEY (`id`)
) ENGINE=innodb DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

NOT NULL：防止索引失效知牌，對表中一個列添加了索引的話，如果有記錄為NULL斤程，則查詢時不會使用該索引角寸。

表注釋：同代碼注釋一樣菩混，可以幫助開發(fā)人員快速理解的建表的含義，但是同項目中的代碼又不一樣扁藕，沒有注釋沮峡，代碼看久了總會理解其中的邏輯和功能，但是表字段亿柑，如果不懂就真的只能靠猜了邢疙。添加表注釋就是為了大家都能理解字段的意思及建表意圖，不要太相信自己的記憶力望薄，它是比不了白紙黑字的疟游，時間久了總會淡忘一些，如果連自己都忘了真的是搞笑了痕支，因此注釋的添加也是極其必要的颁虐，有些標示類型字段也是需要詳細解釋的，不同的值分別代表什么意思卧须，如果不在注釋中約定好另绩，只靠口頭來傳達是很容易出現差錯的，注釋就是為了減少溝通成本花嘶，也減少理解出錯的幾率板熊，從而提升工作效率。

查詢語句優(yōu)化

mapper文件中sql語句規(guī)范,修改select *語句察绷，這里只貼出部分修改后的代碼干签，其余的自行通過github更新：

<select id="findArticles" parameterType="Map" resultMap="ArticleResult">
        select id,article_title,article_create_date,article_content,add_name from ssm_article
        <where>
            <if test="articleTitle!=null and articleTitle!='' ">
                and article_title like #{articleTitle}
            </if>
        </where>
        <if test="start!=null and size!=null">
            limit #{start},#{size}
        </if>
    </select>    

關于mapper文件中的sql語句，主要是修改了select語句拆撼，原本的select語句都是用select * from TABLE實現的容劳，這種方式有不少缺點而且不是一個優(yōu)雅的寫法，因此做了一下規(guī)范闸度，改為如下格式：

select COL(需要的列名) from TABLE

關于避免使用select *語句的個人想法：

• 執(zhí)行SELECT * 語句時竭贩，SQL Server首先要查找出表中有哪些列，然后才能開始執(zhí)行SELECT * 語句莺禁，這在某些情況會產生性能問題留量。
• SELECT * 語句取出表中的所有字段，所有數據都會返回到結果集中哟冬。
• SELECT * 中返回的字段中有可能存在一個功能中不需要的字段楼熄。
• 使用SELECT * 語句可能會影響到索引的使用，不利于查詢的性能優(yōu)化浩峡。

針對這四點分別講一下個人理解：

• 第一條可岂，對于性能的影響可能不會太大，幾毫秒或者幾微妙的事情翰灾，似乎不是那么嚴重缕粹，而且SQL Server也有對應的緩存策略稚茅，不會每次都去查表中有哪些列。但是平斩，我們現在是在做優(yōu)化工作亚享，既然做就要盡量做到最好，有優(yōu)化的地方還是要去嘗試一下绘面，提升0.01秒也是提升虹蒋，不要因為量小而忽略它。
• 第二條飒货，所有的字段都返回魄衅，結果集的數據量也會相應的增大，對應的塘辅，也就會增加數據傳輸的時間晃虫，即使mysql服務和客戶端是在同一臺機器上，二者間通信時使用的協議還是tcp扣墩，通信也是需要額外的時間哲银，因此呻惕，還是取所需內容為好。
• *第三條亚脆，舉個例子，表中有16個字段濒持，我們要實現一個功能键耕，但是這個方法中需要其中6個字段就可以了柑营，那么剩下的10個字段就是沒用的了，如果用select 返回結果官套，一個sql返回列可能就要多創(chuàng)建10個String對象到jvm中酒奶，10000個呢？再多呢奶赔？這是浪費纺阔。而且，如果返回的列中有大字段质况，例如很長的varchar玻靡，blob，text臼朗，占用空間也更大蝎土，更浪費。
• 第四條挡毅，舉個例子暴构，對于ssm_user表，創(chuàng)建了user_name和role_name的聯合索引耗绿，對于以下兩條sql語句：select role_name from ssm_user where user_name='admin'和 select * from ssm_user where user_name='admin'误阻，前者要比后者的速度快，因為表中存在user_name和role_name的聯合索引堕绩，因此role_name可以在索引樹上直接拿到邑时，不再需要讀取表中的這條記錄，在下面會給出具體代碼黍氮。

其實我也沒想到一個select *會寫這么多知識點沫浆。

既然優(yōu)化select * from TABLE有那么多好處滚秩，那么是不是select count(*) from TABLE也要優(yōu)化呢？OK本股，我們接著來分析拄显，select count(*) from TABLE在不同情況下，性能表現的不同：

• innodb引擎：select count(*)與select count(COL)的效率沒有太大差距棘街，都會掃描全表或者where條件語句后的結果承边，累加可能得到的結果不同博助。
• myisam引擎且沒有where子句：可以直接得出myisam會記錄總行數，很快罗心。
• myisam有where子句：與innodb引擎相同城瞎。

注：
count(col) 是表示結果集中有多少個column字段不為空的記錄。
count(*) 是表示整個結果集有多少條記錄飒箭。

因此弦蹂，通過上面的分析發(fā)現强窖，select count(*)語句的性能提升，首先是引擎的選擇(這個不重要脑漫，目前選擇的是innodb引擎)优幸，重點是在where條件語句的優(yōu)化褪猛，也就是索引的優(yōu)化。

索引和優(yōu)化案例

前面兩個段落中基本都提到了一個知識點--索引碳却，在sql語句優(yōu)化中追城，合理和正確的索引真的是一個利器燥撞。

優(yōu)化的意義：我們在前一篇講到了慢sql給項目帶來的危害，這里再補充一下色洞，首先我們大多數用到的都是innodb引擎來建表火诸，這個引擎已經做了挺大的優(yōu)化荠察，支持行級鎖和表鎖，并且默認鎖為行級鎖盯荤，對表鎖做了一定的優(yōu)化秋秤，因此對于行級鎖的優(yōu)化來說脚翘，就是盡量減少sql語句執(zhí)行而帶來的行級鎖，盡量使得處于鎖狀態(tài)中的記錄數減少鞋真。如果一條sql鎖住過多的記錄涩咖，那么對于其他sql語句的執(zhí)行就是一個阻礙揽涮，需要等到鎖釋放并且競爭到鎖才能正常的執(zhí)行蒋困，因此我們優(yōu)化慢sql不僅僅是提升執(zhí)行速度和執(zhí)行時間，同時也是優(yōu)化查詢過程零院，優(yōu)化mysql的資源使用，減少鎖資源的開銷以及系統開銷撰茎，同時打洼，合理的查詢語句也能降低各種錯誤的產生募疮，比如事務死鎖的產生。
從以上分析中我們可以得出一個結論他嚷，盡量使得sql語句執(zhí)行時innodb使用行級鎖而不是表鎖筋蓖，innodb行鎖是通過給索引上的索引項加鎖來實現的退敦，innodb這種行鎖實現特點意味著：只有通過索引條件檢索數據，innodb才使用行級鎖涂炎，否則唱捣，innodb將使用表鎖网梢！

索引的類型：

• 普通索引：這是最基本的索引類型战虏，沒唯一性之類的限制。
• 唯一性索引：和普通索引基本相同巡社，但所有的索引列值保持唯一性手趣。
• 主鍵索引：主鍵是一種唯一索引，但必須指定為”PRIMARY KEY”燕耿。
• 全文索引：不講這個姜胖，想了解的自己去看一下

結合前文提到的ssm_user表來講一個小例子:

未使用索引-all

從結果來看右莱，依然是全表掃描。

對user_name,role_name字段添加索引：

alter table ssm_user add index idx_un_rn(user_name,role_name);

添加索引

再次執(zhí)行上面的分析語句：

explain select role_name from ssm_user where user_name='admin';

索引樹種查詢

通過結果來看阀捅，添加了索引之后饲鄙，type已由原來的全表掃描ALL變成了ref級別，也如前文所說帆谍，role_name直接通過索引樹返回轴咱，extra中的using index參數表示直接訪問索引就足夠獲取到所需要的數據朴肺，不需要通過索引回表。

這個例子是對上一段落select *和select COL間性能比較的補充西土，下面再講解一個ssm_picture表的例子需了，對前一篇文章《Spring+SpringMVC+MyBatis+easyUI整合優(yōu)化篇（十二）數據層優(yōu)化-explain關鍵字及慢sql優(yōu)化》做一個補充般甲。

首先在數據庫中插入20000條數據:

記錄總數

分析查詢語句explain select id,path,type,time,url,grade from ssm_picture where type=1 and grade=1 limit 0,10;：

explain-01

explain-08

在沒有添加索引前，依然是全表掃描杠娱，記錄總數為20134摊求，前文做的一個小改動：

explain-02

是通過order by id使得查詢語句使用主鍵索引室叉，type由ALL變?yōu)閕ndex硫惕，但是依然為全表掃描。因為where條件中是通過type和grade對結果集進行過濾踪旷，因此添加一個type字段和grade字段組成的多列索引令野。
在ssm_picture表中添加一個名稱為idx_type_grade的索引：

alter table ssm_picture add index idx_type_grade (type,grade);

此時再次分析查詢語句得到結果如下：

explain-06

多次執(zhí)行后的執(zhí)行時間如下：

explain-07

比原來應該是有一些提升的气破，不僅僅是時間上的提升现使，關鍵是結構上的整理旷痕，不僅優(yōu)化了查詢過程和結果，也避免全表掃描節(jié)省了mysql的開銷殴胧，降低可預見的錯誤發(fā)生幾率团滥。

最左前綴原則：

多列索引有一個特點报强，即最左前綴（Leftmost Prefixing）秉溉。假如有一個多列索引為key(firstname lastname age)碗誉，當搜索條件是以下各種列的組合和順序時哮缺，MySQL將使用該多列索引：
firstname甲喝，lastname埠胖，age
firstname，lastname
firstname
也就是說非竿，相當于還建立了key(firstname lastname)和key(firstname)谋竖。
按照最左前綴原則圈盔，本例中的idx_type_grade(type,grade)即創(chuàng)建了兩個索引：
type,grade
type
show index from ssm_picture查看表的索引信息：

explain-05

圖中Key_name 為索引的名稱驱敲，Seq_in_index為索引中的列序列號(從1開始)宽闲，因此結果集表示的是容诬，有一個索引名為idx_type_grade，第一列為type狈定，第二列是grade纽什，而不是像id主鍵索引表示單獨的兩個索引躲叼。分別以type和grade為where語句中的條件來執(zhí)行sql驗證一下：

explain-03

explain-04

type用到了索引枫慷，而grade依然是全表掃描，即無grade索引笋婿，也符合最左前綴原則的分析顿颅。

總結

使用索引的優(yōu)點：

• 可以通過建立唯一索引或者主鍵索引,保證數據庫表中每一行數據的唯一性
• 建立索引可以大大提高檢索的數據,以及減少表的檢索行數
• 在表連接的連接條件可以加速表與表直接的相連
• 在分組和排序字句進行數據檢索,可以減少查詢時間中分組和排序時所消耗的時間(數據庫的記錄會重新排序)
• 建立索引,在查詢中使用索引可以提高性能

使用索引的缺點:

• 雖然索引大大提高了查詢速度元镀，同時卻會降低更新表的速度，如對表進行INSERT讨永、UPDATE和DELETE卿闹。因為更新表時萝快，MySQL不僅要更新數據揪漩，還要更新一下索引文件,創(chuàng)建索引和維護索引 會耗費時間,隨著數據量的增加而增加
• 建立索引會占用磁盤空間的索引文件。一般情況這個問題不太嚴重冰更，但如果你在一個大表上創(chuàng)建了多種組合索引昂勒，索引文件的會膨脹很快戈盈。
• 索引只是提高效率的一個因素，如果你的MySQL有大數據量的表归斤，就需要花時間研究建立最優(yōu)秀的索引官册，或優(yōu)化查詢語句

使用索引需要注意的地方:

• 在經常需要搜索的列上,可以加快索引的速度
• 在表與表的而連接條件上加上索引,可以加快連接查詢的速度
• 在一些where 之后的 < <= > >= BETWEEN IN 以及某個情況下的like 建立字段的索引(B-TREE)
• 如果你對nickname字段建立了一個索引,當查詢的時候的語句是 nickname lick '%ABC%'那么這個索引講不會起到作用,而nickname lick 'ABC%'那么將可以用到索引
• 索引不會包含NULL列,如果列中包含NULL值都將不會被包含在索引中,復合索引中如果有一列含有NULL值那么這個組合索引都將失效,一般需要給默認值0或者' '字符串
• 使用短索引,如果你的一個字段是Char(32)或者int(32),在創(chuàng)建索引的時候指定前綴長度 比如前10個字符 (前提是多數值是唯一的..)那么短索引可以提高查詢速度,并且可以減少磁盤的空間,也可以減少I/0操作.
• 不要在列上進行運算,這樣會使得mysql索引失效,也會進行全表掃描
• 選擇越小的數據類型越好,因為通常越小的數據類型通常在磁盤,內存,cpu,緩存中 占用的空間很少,處理起來更快

什么情況下不創(chuàng)建索引:

• 查詢中很少使用到的列 不應該創(chuàng)建索引,如果建立了索引然而還會降低mysql的性能和增大了空間需求.
• 很少數據的列也不應該建立索引,比如一個性別字段0或者1,在查詢中,結果集的數據占了表中數據行的比例比較大,mysql需要掃描的行數很多,增加索引,并不能提高效率
• 定義為text和image和bit數據類型的列不應該增加索引
• 當表的修改(UPDATE,INSERT,DELETE)操作遠遠大于檢索(SELECT)操作時不應該創(chuàng)建索引,這兩個操作是互斥的關系

索引不是越多越好膝宁，也不是所有的表都要創(chuàng)建索引员淫，根據需求和實際運行的sql語句進行合理的索引創(chuàng)建。本文中的案例都是圍繞ssm-demo這個項目來進行的拴事，都是單表的簡單查詢sql語句圣蝎，并沒有復雜的連接查詢或者復雜的where語句徘公，join查詢等復雜查詢关面，以后有時間專門寫幾篇文章來詳細講解一下，本項目實在沒有太好的切入點捂齐，只能重新建表來單獨講解缩抡，OK缝其，本文結束。

這周并沒有更新博客，是因為完成這兩篇博客花費了不少的時間漠其，從構思到整理資料和理出文章結構再到完成和屎，前前后后大概有6個晚上春瞬，白天要上班或者有其他事情宽气，基本都是抽出晚上的一段時間來寫潜沦，也慢慢感覺隨著項目的修改以及知識點的深入唆鸡，后面會越來越難吧枣察，也不知道自己堅不堅持得下來序目，加油啦。