mysql索引

實作效果：通俗的說索引是用來提高查詢效率，不需要通過掃描全部表記錄，而直接使用索引快速定位需要查詢的值。

需求的影響：論壇文章要求總量的統計，附加要求，實時更新

        功能上非常容易實作，執行select count * from 表名 的query就可以得到結果，如果我們采用不是MyLSAM存儲引擎，而是使用Innodb的存儲引擎，那麼存放文章的表有上千萬條記錄，執行這條需要很大的成本。

        沒有where的count使用MyLSAM要比InnoDB快的多，因為MyLSAM内置了一個計時器，count時他直接從計數器中讀，1而InnoDB必須掃描全表。在InnoDB上執行count是一般要伴随where，且where重要包含主鍵以外的索引列。

        這樣查詢不行，我們就專門為這個功能建一個表，就隻有一個字段，一條記錄，就存放這個統計量，每次有新的文章産生的時候，都将這個值增加1，我們每次都隻需要查詢這個表就可以得到結果，效率就能滿足要求。查詢效率肯定能夠滿足要求，開始如果文章産生快，高峰時期可以每秒上百個新增操作。因為鎖資源争用嚴重造成整體性能的大幅度下降。

        通過建立一個統計表，然後通過一個定時任務每隔一定時間段去更新一次裡面的統計值，既可以解決統計值查詢的效率問題，有保證不影響新發帖的效率。

     系統架構及實作的影響

            所有資料都不是适合在資料庫存放。

                二進制多媒體資料：主要包括圖檔，音頻，視訊和其他一些相關的二進制檔案，将二進制多媒體資料存放在資料庫中，資料庫空間隻有消耗嚴重，資料存儲消耗資料庫主機的CPU資源。

                超大文本資料

                    在5.0.3之前的mysql版本，VARCHAR類型的資料最長隻能存放255個位元組，如果需要存儲更長的資料到一個字段，必須使用TEXT類型（最大可存放64k）的字段，甚至是更大的LONGTEXT類型（最大4GB）。而text類型資料的處理性能要遠比VARCHAR類型資料的處理性能底下很多。從5.0.3版本，VARCHAR類型的最大長度被調整到64kb了，是以，超大文本資料存放的資料庫綜合那個不僅會帶來性能低下，還帶來空間占用浪費。

                對于web應用，活躍資料的資料量總是不會特别大，有些活躍資料更是很少變化，對于這裡資料，如果我們能将變化相對較少的部分活躍資料通過應用層的cache機制cache到記憶體中，對性能提升肯定是成數量級的，而且由于是活躍資料，對系統整體的性能影響也會很大。

                查詢語句對性能的影響

                  資料庫管理軟體中，最大的性能瓶頸是在于磁盤io，就是資料存取操作上面，對同一份資料，以不同方式找到其中的某一點内容的時候，所需的資料量可能會有天壤之别，消耗的資源也差別很多。

                explain來檢視執行計劃

                profiling來檢視實際執行計劃

                    通過執行show PROFILE指令擷取目前系統中儲存的多個query的profile的概要資訊。

            資料庫Schema設計對性能的影響

            硬體選擇對性能的影響

                資料庫主機是存取資料的地方，資料庫主機的io性能肯定是需要最優先考慮的一個因素，不管什麼類型的資料庫應用都适用。在主機中決定io性能不僅主要有磁盤和記憶體所決定，當然也包括各種io相關的闆卡

                其次，資料庫主機是存取資料的地方，詞語要相對集中很多，單台主機上所需要進行的計算量自然也就比較多，所有資料庫主機的CPU處理能力也不能忽視

                資料庫負責資料的存儲，與各應用茨城縣的互動中傳遞的資料比其他各類伺服器都要多，是以資料庫主機的網絡裝置的性能也可能會成為系統的瓶頸。

                資料庫應用系統的優化，實際上是一個需要多方面配合，多方面優化的才能産生根本性改善的事情。

                可以通過商業需求合理化，系統架構最優化，邏輯實作荊建華，硬體設施理性化。

                mysql性能優化之一-索引

                    mysql索引的好處：對于沒有索引的表，單表查詢可能幾十萬資料的瓶頸，而通常大型網站單日就會産生幾百萬的資料，沒有索引查詢會變得非常緩慢。

                索引實在存儲引擎中實作的，而不是在伺服器層中實作的。每種存儲的索引都不一定完全相同，并不是所有的存儲引擎都支援所有的索引類型。

                什麼是索引：

                    是幫助mysql高興擷取資料的資料結構，他的存在形式是檔案，縮影能夠幫助我們快速定位資料。

                為什麼使用索引：

                    索引可以讓mysql高效運作，可以提高mysql的插叙效率，資料限制

                好處：

                    提高查詢效率，快速定位資料

                索引産生的代價：

                    1，本身以檔案形式存放在硬碟，需要的時候才加載至記憶體，所有添加索引會增加磁盤的開銷；

                    2，寫資料：需要更新索引，歲資料庫是很多的開銷，見底表更新，添加和删除的速度。

                不建議使用索引的情況有哪些：

                    表記錄少

                    索引的選擇性較低。指不重複的索引值與表記錄數的比值，取值範圍0到1，值越大，選擇性越大

                索引的類型：

                    普通索引，基本的索引，沒有任何限制

                        create index index_name on tablename（column1）

                    唯一索引，與普通索引類似，不同的就是索引列的值必須唯一，但允許空值，空值值null，組合索引的組合列的值必須唯一

                        create uniaue table_name on tablename（column1）

                    主鍵索引，一種特殊的唯一索引，不允許有空值，一般在建表的時候同時建立主鍵索引

                        create table table_name（id int not unll，username varchar（16） not null，primay key（id））；

                    組合索引，建立單列索引，代表有3個單列索引，查詢時和上述的組合索引效率也會大不一樣，遠遠低于組合索引。

                    全文索引，隻用于mylsam表，對文本域進行索引，字段類型包括char，varchar，text，不過對于大容量的資料表，生成全文索引是一個非常消耗時間硬碟空間的做法

                        create fulltext index index_name on tablename（column）

                索引的資料結構，B-tree索引結構：

                如上圖，是一顆b+樹，這裡隻說一些重點，淺藍色的塊我們稱之為一個磁盤塊，可以看到每個磁盤塊包含幾個資料項（深藍色所示）和指針（黃色所示），如磁盤塊1包含資料項17和35，包含指針P1、P2、P3，P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊。真實的資料存在于葉子節點即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非葉子節點隻不存儲真實的資料，隻存儲指引搜尋方向的資料項和指針，如17、35并不真實存在于資料表中。

b+樹的查找過程

                如圖所示，如果要查找資料項29，那麼首先會把磁盤塊1由磁盤加載到記憶體，此時發生一次IO，在記憶體中用二分查找确定29在17和35之間，鎖定磁盤塊1的P2指針，記憶體時間因為非常短（相比磁盤的IO）可以忽略不計，通過磁盤塊1的P2指針的磁盤位址把磁盤塊3由磁盤加載到記憶體，發生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到記憶體，發生第三次IO，同時記憶體中做二分查找找到29，結束查詢，總計三次IO。真實的情況是，3層的b+樹可以表示上百萬的資料，如果上百萬的資料查找隻需要三次IO，性能提高将是巨大的，如果沒有索引，每個資料項都要發生一次IO，那麼總共需要百萬次的IO，顯然成本非常非常高。

               有什麼要求：隻有某些時候的like才需建立索引，因為在以通配符%和_開頭做查詢時，mysql不會使用索引。

                不要在列上進行運算

                将在每個行上進行運算，将導緻索引失效而進行全表掃描

                選擇索引列：

                    a，使用索引的主要有兩種類型的列：在where子句中出現的列，在join子句中出現的列

                    b，考慮列綜合那個值的分布，如果對字元串列進行索引，應該指定一個字首長度，可節省大量索引空間，提升查詢速度。

                    c，使用短索引，可節省大量索引空間，提升查詢速度。

                    d，利用最左字首

                    e，不要過度索引，值保持所需的索引，每個額外的索引都要占用額外的磁盤空間，并降低寫操作的性能，在修改表的内容時，索引必須進行更新，有時可能需要重構，是以，索引越多，所花的時間越長。

                 mysql性能優化-慢查詢分析，優化索引，優化配置

                    性能瓶頸定位

                        show指令

                        慢查詢日志

                        explain分析查詢

                        profiling分析查詢

                    索引即查詢優化

                    配置優化

                        最常見的兩個瓶頸是cpu和i/o的瓶頸，cpu在飽和的時候一般發生子資料裝入記憶體或從磁盤上讀取資料時候，磁盤i/o瓶頸發生在裝入資料遠大雨記憶體容量的時候，如果應用分布在網絡上，查詢量相當大的時候那麼瓶頸就會出現在網絡上，可以用mpstat，iostat，sar，vmstat來檢視系統的性能狀态。

                    查詢與索引優化分析

                            優化mysql時，需要分析資料庫。有慢查詢日志，explain分析查詢，profiling分析以及show指令查詢系統狀态即系統變量，通過定位分析性能的瓶頸，才能更好的優化資料庫系統的性能。

                    show指令檢視mysql狀态即變量，找到系統的瓶頸。

                        檢視mysql伺服器配置資訊mysql > show variables;

                        檢視mysql伺服器運作的各自狀态值mysq > show global status;

                        mysqladmin variables -u username -ptanhong 顯示系統變量

                        mysqladmin extended-status -u username -ptanhong 顯示狀态資訊

                     慢查詢日志開啟。

                        配置檔案my.cnf中的{mysqld}一行下面加入3個配置參數，并重新開機mysql服務

                            show query log = 1    1：開啟 0：關閉

                            show_query_log_file = /usr/local/mysql/data/slow-query.log    慢查詢日志存放地點

                           long_query_time = 1    表示查詢超過1秒才記錄

                使用mysqldumpslow指令可以非常明确的得到各種我們需要的查詢語句，對mysql查詢語句的監控，分析，優化是mysql優化非常重要的，開啟慢查詢日志後，日志記錄操作，在一定程度上會占用cpu資源影響mysql的性能，但是可以階段性開啟來定位性能瓶頸

                explain分析查詢，可以模拟優化器執行sql查詢語句，進而知道mysq是如何醋栗sql語句的，可以分析你的查詢語句或是表結構的性能瓶頸

                    explain select * from test1.tb1 where stuname='admin'\G;

                        id:1 

               select_type:SIMPLE

                     table:tb1            顯示是哪個表

                partitions:NULL           

                      type:ALL            插叙使用了何種類型，重要字段。

             possible_keys:NULL           顯示可能應用在表中的索引

                       key:NULL           實際使用的索引

                   key_len:NULL           使用的索引的長度

                       ref:NULL           顯示索引哪一列被使用

                      rows:19986          mysql認為必須檢索傳回請求資料的行數

                  filtered:10.00          

                     Extra:Using where    關于mysql模拟優化器執行sql語句來看是沒有使用索引查詢的，而是全表掃描

                1 row in set,1 warning(0.00 sec)

                profiling分析查詢

                    通過慢日志查詢可以知道哪些sql語句執行效率低下，通過explain可以得知sql語句的具體執行情況，索引使用等，可以結合show指令檢視執行狀态，如果決定explain的資訊不夠詳細，可以通過profiling指令得到更準确的sql執行消耗資源的資訊。

                    profiling預設是關閉的，通過set profiling=1開啟，執行需要測試的sql語句。

本文轉自   宏強   51CTO部落格，原文連結:http://blog.51cto.com/tanhong/1905236