程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql性能優化之索引優化,

mysql性能優化之索引優化,

編輯:MySQL綜合教程

mysql性能優化之索引優化,


  作為免費又高效的數據庫,mysql基本是首選。良好的安全連接,自帶查詢解析、sql語句優化,使用讀寫鎖(細化到行)、事物隔離和多版本並發控制提高並發,完備的事務日志記錄,強大的存儲引擎提供高效查詢(表記錄可達百萬級),如果是InnoDB,還可在崩潰後進行完整的恢復,優點非常多。即使有這麼多優點,仍依賴人去做點優化,看書後寫個總結鞏固下,有錯請指正。

  完整的mysql優化需要很深的功底,大公司甚至有專門寫mysql內核的,sql優化攻城獅,mysql服務器的優化,各種參數常量設定,查詢語句優化,主從復制,軟硬件升級,容災備份,sql編程,需要的不是一星半點的知識與時間來掌握,作為一名像俺這樣的菜鳥開發,強吃這麼多消化不了也沒意義:沒地兒用啊,況且還有運維和dba,還不如把手頭的業務寫好,也就是寫好點的sql,而且很多sql語句優化跟索引還是有很大關系的。

  首先,mysql的查詢流程大致是:mysql客戶端通過協議與mysql服務器建立連接,發送查詢語句,先檢查查詢緩存,如果命中,直接返回結果,否則進行語句解析,有一系列預處理,比如檢查語句是否寫正確了,然後是查詢優化(比如是否使用索引掃描,如果是一個不可能的條件,則提前終止),生成查詢計劃,然後查詢引擎啟動,開始執行查詢,從底層存儲引擎調用API獲取數據,最後返回給客戶端。怎麼存數據、怎麼取數據,都與存儲引擎有關。然後,mysql默認使用的BTREE索引,並且一個大方向是,無論怎麼折騰sql,至少在目前來說,mysql最多只用到表中的一個索引。

  mysql通過存儲引擎取數據,自然跟存儲引擎有很大關系,不同的存儲引擎索引也不一樣,如MyISAM的全文索引,即便索引叫一個名字內部組織方式也不盡相同,最常用的當然就是InnoDB了(還有完全兼容mysql的MariaDB,它的默引擎是XtraDB,跟InnoDB很像),這裡寫的是InnoDB引擎。而索引的實現也跟存儲引擎,按照實現方式分,InnoDB的索引目前只有兩種:BTREE索引和HASH索引。通常我們說的索引不出意外指的就是B樹索引,InnoDB的BTREE索引,實際是用B+樹實現的,因為在查看表索引時,mysql一律打印BTREE,所以簡稱為B樹索引。至於B樹與B+樹的區別,原諒的俺數據結構沒好好學,也是需要補的地方。

  使用了BTREE索引,意味著所有的索引是按順序排列存儲的(升序),mysql就是這麼干的,mysl中的BTREE索引抽象結構如下圖(參考高性能mysql)。

  結構中,每一層節點均從左往右從小到大排列,key1 < key2 < ... < keyN,對於小於key1或在[key1,key2)或其他的值的節點,在進入葉子節點查找,是一個范圍分布,同時,同一層節點之間可直接訪問,因為他們之間有指針指向聯系(MyISAM的BTREE索引沒有)。每次搜索是一個區間搜索,有的話就找到了,沒有的話就是空。索引能加快訪問速度,因為有了它無需全表掃描數據(不總是這樣),根據查找的值,跟節點中的值比較,通常使用二分查找,對於排好序的數值來說,平均速度幾乎是最快的。

  val指向了哪裡,對於InnoDB,它指向的就是表數據,因為InnoDB的表數據本身就是索引文件,這是與MyISAM索引的顯著區別,MyISAM的索引指向的是表數據的地址(val指向的是類似於0x7DFF..之類)。比如對於InnoDB一個主鍵索引來說,可能是這樣

        

  InnoDB的索引節點val值直接指向表數據,即它的葉子節點就是表數據,它們連在一起,表記錄行沒有再單獨放在其他地方,葉子節點(數據)之間可訪問。

  前面在BTREE的抽象結構中,索引值的節點是放在頁中的,這裡有兩個需注意的問題:

  1. 葉子頁、頁中的值(上上圖),即所謂的頁是啥,俺加了個節點注釋,即這裡的頁最小可近似當做是單個節點。我們知道計算機的存儲空間是一塊一塊的,通常一塊用完了再用另一塊,如果上一塊只剩余5kb,但這裡剛好要申請8kb的空間,就得在一個新的塊上申請這個空間,然後以後的申請又接在這個8kb後面,只要這個塊的空間足夠,那麼上一塊的5kb通常就成了所謂的“碎片”,電腦用多了會有很多這樣零散的碎片空間,因此有碎片整理。在mysql中,這裡的頁可理解為塊存儲空間,即索引的樹節點是存放在頁中的,每一頁(稱為邏輯頁)有固定大小,InnoDB目前是16kb,一頁用完了,當繼續插入表生成新的索引節點時,就去新的頁中存儲這個節點,再有新的節點就繼續放在這個新的頁的節點後面。

  2. 頁分裂問題,一頁總要被存滿,然後新開一頁繼續,這種行為被稱作頁分裂。何時開辟新的頁,mysql規定了一個分裂因子,達到頁存儲空間的15/16則存到下一頁。頁分裂的存在可能極大影響性能維護索引的性能。通常提倡的是,設定一個無意義的整數自增索引,有利於索引存儲

    

  如果非自增或不是整數索引,如非自增整數、類似MD5的字符串,以他們作為索引值時,因為待插入的下一條數據的值不一定比上一條大,甚至比當前頁所有值都小,需要跑到前幾頁去比較而找到合適位置,InnoDB無法簡單的把新行插入到上一行後面,而找到並插入索引後,可能導致該頁達到分裂因子閥值,需要頁分裂,進一步導致後面所有的索引頁的分裂和排序,數據量小也許沒什麼問題,數據量大的話可能會浪費大量時間,產生許多碎片。

    

  主鍵總是唯一且非空,InnoDB自動對它建立了索引(primary key),對於非主鍵字段上建立的索引,又稱輔助索引,索引排列也是順序排列,只是它還附帶一個本條記錄的主鍵值的數據域,不是指向本數據行的指針,在使用輔助索引查找時,先找到對應這一列的索引值,再根據索引節點上的另一個數據域---主鍵值,來查找該行記錄,即每次查找實際經過查找了兩次。額外的數據域存儲主鍵值的好處是,當頁分裂發生時,無需修改數據域的值,因為即使頁分裂,該行的主鍵值是不變的,而地址就變了。比如name字段的索引簡示如下 

      

   包含一列的索引稱為單列索引,多列的稱為復合索引,因為BTREE索引是順序排列的,所以比較適合范圍查詢,但是在復合索引中,還應注意列數目、列的順序以及前面范圍查詢的列對後邊列的影響。

  比如有這樣一張表

create table staffs(
    id int primary key auto_increment,
    name varchar(24) not null default '' comment '姓名',
    age int not null default 0 comment '年齡',
    pos varchar(20) not null default '' comment '職位',
    add_time timestamp not null default current_timestamp comment '入職時間'
  ) charset utf8 comment '員工記錄表';

  添加三列的復合索引

alter table staffs add index idx_nap(name, age, pos);

  在BTREE索引的使用上,以下幾種情況可以用到該索引或索引的一部分(使用explain簡單查看使用情況):

  1. 全值匹配

  如select * from staffs where name = 'July' and age = '23' and pos = 'dev' ,key字段顯示使用了idx_nap索引

  2. 匹配最左列,對於復合索引來說,不總是匹配所有字段列,但是可以匹配索引中靠左的列

  如select * from staffs where name = 'July' and age = '23',key字段顯示用到了索引,注意,key_len字段(表示本次語句使用的索引長度)數值比上一條小了,意思是它並未使用全部索引列(通常這個長度可估摸著用了哪些索引列,埋個坑),事實上只用到了name和age列

  再試試select * from staffs where name = 'July',它也用了索引,key_len值更小,實際只用到了索引中的name列

  3. 匹配列前綴,即一個索引中列的前一部分,主要用在模糊匹配,如select * fromstaffs where name like 'J%',explain信息的key字段表示使用了索引,但是mysql的B樹索引不能非列前綴的模糊匹配,如select * from staffs where name like '%y' 或者 like '%u%',據說是由於底層存儲引擎的API限制

  4. 匹配范圍,如select * from staffs where name > 'Mary',但俺在測試時發現>可以,>=卻不行,至少在字符串列上不行(測試mysql版本5.5.12),然而在時間類型(timestamp)上卻可以,不測試下還真不能確定說就用到了索引==

  出於好奇測了下整型字段的索引(idx_cn(count, name),count為整型),發現整型受限制少很多,下面的都能用到索引,連前模糊匹配的都行

select * from indexTest1 where count > '10'
  select * from indexTest1 where count >= '10'
  select * from indexTest1 where count > '10%'
  select * from indexTest1 where count >= '10%'
  select * from indexTest1 where count > '%10%'
  select * from indexTest1 where count >= '%10%'

  5. 精確匹配一列並范圍匹配右側相鄰列,即前一列是固定值,後一列是范圍值,它用了name與age兩個列的索引(key_len推測)

  如select * from staffs where name = 'July' and age > 25

  6. 只訪問索引的查詢,比如staffs表的情況,索引建立在(name,age,pos)上面,前面一直是讀取的全部列,如果我們用到了哪些列的索引,查詢時也只查這些列的數據,就是只訪問索引的查詢,如

select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev'
  select name,age from staffs where name = July and age > 25

  第一句用到了全部索引列,第二句只用了索引前兩列,select的字段就最多只能是這兩列,這種查詢情況的索引,mysql稱為覆蓋索引,就是索引包含(覆蓋)了查詢的全部字段。是不是用到了索引查詢,在explain中需要看最後一個Extra列的信息,Using index表明使用了覆蓋索引,同時Using where表明也使用了where過濾

  7. 前綴索引

  區別於列前綴(類似like 'J%'形式的模糊匹配)和最左列索引(順序取索引中靠左的列的查詢),它只取某列的一部分作為索引。通常在說InnoDB跟MyISAM的區別時,一個明顯的區別是:MyISAM支持全文索引,而InnoDB不行,甚至對於text、blob這種超長的字符串或二進制數據時,MyISAM會取前多少個字符作為索引,InnoDb的前綴索引跟這個類似,某些列,一般是字符串類型,很長,全部作為索引大大增加存儲空間,索引也需要維護,對於長字符串,又想作為索引列,一個可取的辦法就是取前一部分(前綴),代表一整列作為索引串,問題是:如何確保這個前綴能代表或大致代表這一列?所以mysql中有個概念是索引的選擇性,是指索引中不重復的值的數目(也稱基數)與整個表該列記錄總數(#T)的比值,比如一個列表(1,2,2,3),總數是4,不重復值數目為3,選擇性為3/4,因此選擇性范圍是[1/#T, 1],這個值越大,表示列中不重復值越多,越適合作為前綴索引,唯一索引(UNIQUE KEY)的選擇性是1。

  比如有一列a varchar(255),以它作前綴索引,比如以7個測試,逐個增加看看選擇性值增長到那個數基本不變,就表示可以代表整列了,再結合這個長度的索引列是否存儲數據太多,做個權衡,基本就行了。但如果這個選擇性本來就小的可憐還是算了

select count(distinct left(a, 7))/count(*) as non_repeat from tab;

  定好一個前綴數目,如9,添加索引時可以這樣

alter table tab add index idx_pn(name(9)) --單獨前綴索引
  alter table tab add index idx_cpn(count, name(9)) --復合前綴索引

  以上為常見的使用索引的方式,有這麼些情況不能用或不能全用,有的就是上面情況的反例,以key(a, b, c)為例

  1. 跳過列,where a = 1 and c = 3,最多用到索引列a;where b = 2 and c = 3,一個也用不到,必須從最左列開始

  2. 前面是范圍查詢,where a = 1 and b > 2 and c = 3,最多用到 a, b兩個索引列;

  3. 順序顛倒,where c = 3 and b = 2 and a = 1,一個也用不到;

  4. 索引列上使用了表達式,如where substr(a, 1, 3) = 'hhh',where a = a + 1,表達式是一大忌諱,再簡單mysql也不認。有時數據量不是大到嚴重影響速度時,一般可以先查出來,比如先查所有有訂單記錄的數據,再在程序中去篩選以'cp1001'開頭的訂單,而不是寫sql過濾它;

  5. 模糊匹配時,盡量寫 where a like 'J%',字符串放在左邊,這樣才可能用得到a列索引,甚至可能還用不到,當然這得看數據類型,最好測試一下。

  排序對索引的影響

  order by是經常用的語句,排序也遵循最左前綴列的原則,比如key(a, b),下面語句可以用到(測試為妙)

select * from tab where a > 1 order by b
  select * from tab where a > 1 and b > '2015-12-01 00:00:00' order by b
  select * from tab order by a, b

  以下情況用不到

  1. 非最左列,select * from tab order by b;

  2. 不按索引列順序來的,select * from tab where b > '2015-12-01 00:00:00' order by a;

  3. 多列排序,但列的順序方向不一致,select * from tab a asc, b desc。

  聚簇索引與覆蓋索引

  前面說到,mysql索引從結構上只有兩類,BTREE與HASH,覆蓋索引只是在查詢時,要查詢的列剛好與使用的索引列完全一致,mysql直接掃描索引,然後就可返回數據,大大提高效率,因為不需再去原表查詢、過濾,這種形式下的索引稱作覆蓋索引,比如key(a,b),查詢時select a,b from tab where a = 1 and b > 2,本質原因:BTREE索引存儲了原表數據。

  聚簇索引也不是單獨的索引,前面簡要寫到,BTREE索引會把數據放在索引中,即索引的葉子頁中,包括主鍵,主鍵是跟表數據緊挨著放在一起的,因為表數據只有一份,一列鍵值要跟每一行數據都緊挨在一起,所以一張表只有一個聚簇索引,對於mysql來說,就是主鍵列,它是默認的。

  聚簇索引將表數據組織到了一起(參考前面主鍵索引簡略圖),插入時嚴重依賴主鍵順序,最好是連續自增,否則面臨頻繁頁分裂問題,移動許多數據。

  哈希索引

  簡要說下,類似於數據結構中簡單實現的HASH表(散列表)一樣,當我們在mysql中用哈希索引時,也是對索引列計算一個散列值(類似md5、sha1、crc32),然後對這個散列值以順序(默認升序)排列,同時記錄該散列值對應數據表中某行的指針,當然這只是簡略模擬圖

      

  比如對姓名列建立hash索引,生成hash值按順序排列,但是順序排列的hash值並不對應表中記錄,從地址指針可反應出來,而且,hash索引可能建立在兩列或者更多列上,取得是多列數據後的hash值,它不存儲表中數據。它先計算列數據的hash值,與索引中的hash值比較,找到了然後比對列數據是否相等,可能涉及其他列條件,然後返回數據。hash當然會有沖突,即碰撞,除非有很多沖突,一般hash索引效率很高,否則hash維護成本較高,因此哈希索引通常用在選擇性較高的列上面。哈希索引的結構決定了它的特點:

  1. hash索引只是hash值順序排列,跟表數據沒有關系,無法應用於order by;

  2. hash索引是對它的所有列計算哈希值,因此在查詢時,必須帶上所有列,比如有(a, b)哈希索引,查詢時必須 where a = 1 and b = 2,少任何一個不行;

  3. hash索引只能用於比較查詢 = 或 IN,其他范圍查詢無效,本質還是因不存儲表數據;

  4. 一旦出現碰撞,hash索引必須遍歷所有的hash值,將地址所指向數據一一比較,直到找到所有符合條件的行。

  填坑

  前面提到通過explain的key_len字段,可大致估計出用了哪些列,索引列的長度跟索引列的數據類型直接相關,一般,我們說int是4字節,bigint8字節,char是1字節,考慮到建表時要指定字符集,比如utf8,還跟選的字符集有關(==!),在utf8下邊,一個char是3字節,但是知道這些仍不能說key_len就是將用到的索引列的數據類型代表字節數一加不就完啦?事實總有點區別,測試方法比較機械(以下基於mysql 5.5.2)

  建表,加索引,int型

--測試表
  create table keyLenTest1(
    id int primary key auto_increment,
    typeKey int default 0 ,
    add_time timestamp not null default current_timestamp
  ) charset utf8
  --添加索引
  alter table keyLenTest1 add index idx_k(typeKey);

  可知int型索引默認長度為5,在4字節基礎上+1

  char型

 --改為char型,1個字符
  alter table keyLenTest1 modify typeKey char(1);

--改為char型,2個字符
  alter table keyLenTest1 modify typeKey char(2);

  可知,char型初始是4字節(3+1 bytes),後續按照3字節遞增

  varchar型

--改為varchar型,1個字符
  alter table keyLenTest1 modify typeKey varchar(1);

--改為varchar型,2個字符
  alter table keyLenTest1 modify typeKey varchar(2);

  可知,varchar型,1個字符時,key_len為6,以後以3字節遞增

  所以,如果一個語句用到了int、char、varchar,key_len如何計算以及用了哪些索引列應該很清楚了。

  如果想了解的更詳細點,explain各字段意義,索引的更多細節,除了explain,還有show profiles、慢查詢日志等(沒細看),推薦看高性能mysql,畢竟俺寫的太膚淺。

您可能感興趣的文章:

  • MySQL 索引分析和優化
  • Mysql limit 優化,百萬至千萬級快速分頁 復合索引的引用並應用於輕量級框架
  • MySQL索引背後的之使用策略及優化(高性能索引策略)
  • MySQL Order By索引優化方法
  • mysql優化之路----hash索引優化
  • 探究MySQL優化器對索引和JOIN順序的選擇

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved