瞭解MySQL-索引與優化

markzy5201190發表於2013-05-18
MySql索引優化
瞭解MySQL-索引與優化

索引樹對查詢的速度有著關鍵的影響，如下情況：
資料庫中一個表有10^6條記錄，DBMS的頁面大小為4k，並儲存100條記錄，如沒有索引，
查詢將對整個表進行掃描，最壞情況下，如果所有資料頁不在記憶體中，需要讀取10^4個
頁面，如這10^4個頁面在磁碟上隨機分佈，需要進行10^4次IO，假設磁碟每次IO時間為
10MS(不計資料傳輸時間)，則總共需要100s(但實際上要好很多的)。如對其建立B-Tree
索引，則需要進行log100(10^6)=3次頁面讀取，最壞情況耗時30ms。很多時候，如應用
程式進行SQL查詢速度很慢時，考慮是否可以建索引；

**索引與優化
1.索引資料型別
1）資料型別要小，小對磁碟，記憶體和cpu快取需要更少空間，處理起來更快；
2）整型資料比字元，處理開銷小，因字串的比較複雜；
3）列用NOT NULL 替代 NULL，除非想儲存NULL，因它們使得索引，索引統計資訊以及比
較運算更加複雜，含有空值的列進行查詢優化很難;

2.選擇識別符號
1）整型，更快處理，用於設定：auto_increment
2)字串，避免用字串為識別符號，消耗更多空間，處理很慢，且字串隨機的，所以在
索引中的位置也是隨機的，會導致頁面分裂，隨機訪問磁碟，聚集索引分裂(對於使用聚集
索引儲存引擎)

3.索引入門
如對多列進行索引(組合索引)，列的順序非常重要，MySQL僅能對索引最左邊的字首進行有
效查詢，如：
組合索引it1c1c2(c1,c2),查詢select * from t1 where c1=1 and c2=2能使用該索引；查
詢語句select * from t1 where c1 = 1 也可以使用該索引，但是,查詢select * from t1
where c2 = 2 不能使用該索引，因沒有組合索引的引導列c1,要想使用c2列進行查詢，必須
出現c1等於某值.

4.索引的型別
索引是在儲存引擎中實現的，而不是在伺服器層實現的，所以,每種儲存引擎的索引都不一定
完全相同，並不是所有的儲存引擎都支援所有索引型別;
1)二級索引:key(last_name,first_name,dob) 大致結構描述：
索引儲存的值按索引列中的順序排列，
-------------
| last_name |
-------------
| first_name |
-------------
| dob        |
-------------
2) hash 索引
MySQL中，只有Memory顯示支援hash索引，其支援非唯一hash索引，如多個值有相同的hash code，
索引把它們的行指標用連結串列儲存到同一個hash表項中;
key using hash(fname)
假設索引使用hash函式f(),如下：
f('Argen')=2323
f('Baron')=7437
f('Peter')=8784
f('Vadim')=2458
索引結構如下：
slot        value
2323        Pointer to row 1
2458        Pointer to row 4
7437        Pointer to row 2
8784        Pointer to row 3

slot是有序的，但是在記錄不是有序的，當你執行
select * from testhash where fname = 'Peter' ;
MySQL會計算'Peter'的hash值，然後通過它來查詢索引的行指標,因為f('Peter')=8784,MySQL
會在索引中查詢8784，得到指向記錄3的指標;
因索引自己僅儲存短的值，所以，索引非常緊湊，hash值不取決於列的資料型別，一個tinyint列的索引
與一個長字串列的索引一樣大.

hash索引一些限制:
1)索引僅包含hash code和記錄指標，所以，MySQL不能通過使用索引避免讀取記錄，但訪問記憶體中的記錄很
快速，不會對效能造成太大影響.
2)不能用hash索引排序
3）hash索引不支援鍵的部分匹配，因通過整個索引值來計算hash值
4）Hash索引只支援等值比較，如=,in()和<=>，對 where price>100不能加速查詢;

5.高效能索引策略
聚集索引(clustered indexes)
聚集索引保證關鍵字的值相近的元組儲存的物理位置相同(所以字串型別不用於建立聚集索引，特別
是隨機字串，會使得系統進行大量的移動操作)，且一個表只能有一個聚集索引，由儲存引擎實現索引，
所以，並不是所有引擎支援聚集索引，只有InnoDB和solidDB

注意：葉子頁面包含完整的元組，而內節點頁面僅包含索引的列(索引的列位整型)，InnoDB對主鍵建立聚集
索引，如不指定主鍵，InnoDB會用一個具有唯一且非空值的索引來代替.如果不存在這樣的索引，InnoDB會定
義一個影藏的主鍵，而後對其建立聚集索引。

InnoDB和Myisam資料佈局的比較：如下表:
create table layout_test(
col1 int not null,
col2 int not null,
primary key(col1),
key(col2)
)engine=Innodb charset=utf8;

主鍵的值位於1---10k之間，且按隨機順序插入,而後optimize table進行優化，col2隨機賦予1---100之間
的值，會存在重複的值；
1）myisam的資料佈局
myisam按照插入順序在磁碟上儲存資料，如下：
row number  col1 col2
        0    99    8
        1    12    56
        2    300    63
        ....
        ....
        9997 18 8
        9998 4700 13
        9999 3 93
注意：左邊行號(row number),從0開始，因元組的大小固定，所以myisam可很容易從表的開始位置
找到某一位元組的位置；

primary key的索引結構描述：
Internal nodes

leaf nodes in col1 orders

注意：myisam不支援聚集索引，索引中每個葉子節點僅僅包含行號(row number),且葉子節點按照col1(主鍵)的
順序儲存；

看看col2的索引結構：
Internal nodes

leaf nodes in col2(索引) orders

綜述:在myisam中，primary key 和其他索引沒什麼區別，primary key僅僅只是一個叫做primary的唯一，
非空的索引而已;

2)InnoDB資料佈局
按照聚集索引的形式儲存資料，它的資料佈局有很大不同，儲存表的結構描述：
primary key columns(col1)
transaction Id
rollback pointer
Non-PK columns(col2)

注:聚集索引中的每個葉子節點包含primary key的值，事務Id和回滾指標(rollback pointer)--用於
事務和mvcc，和餘下的列(如col2)

相對於Myisam，二級索引與聚集索引很大不同,InnoDb的二級索引的葉子包含primary key的值，而不
是行指標(row pointers),這減小了移動資料或資料頁面分裂時維護二級索引的開銷,InnoDB不需要更新
新索引的行指標.

當mysql不能使用索引進行排序時，它會利用自己的排序演算法在記憶體(sort buffer)中對資料進行排序，
如記憶體裝不下，它會將磁碟上的資料進行分塊，再對各個資料塊進行排序，而後將各個塊合併成有順的
結果集.對filesort，MySQL有2種排序演算法:
1)2遍掃描法:
先將須要排序的欄位和可以直接定位到相關行資料的指標資訊取出，然後在設定的記憶體(通過引數sort_buffer_size)
中進行排序，完成排序之後再次通過行指標資訊取出所需的columns.

6.索引與加鎖
索引對innodb非常重要，因可以讓查詢鎖更少的元組，MySQL5.0中，innodb直到事務提交時才會解鎖，有2個原因：
1）innodb行鎖開銷非常高效，記憶體開銷也較少，
2）不需要的元組加鎖，會增加鎖的開銷，減低併發性；

Innodb僅對需要訪問的元組加鎖，而索引能夠減少Innodb訪問的元組數，但是，只有在儲存引擎層過濾掉那些不需要
的資料才能達到目的。一旦索引不容許innodb那樣做，MySQL伺服器只能對innodb返回的資料進行where操作，此時，已經
無法避免對那些元組加鎖，innodb已經鎖住那些元組，伺服器無法解鎖.

如：
create table actor(
    actor_id int unsigned NOT NULL AUTO_INCREMENT,
    name      varchar(16) NOT NULL DEFAULT '',
    password        varchar(16) NOT NULL DEFAULT '',
    PRIMARY KEY(actor_id),
    KEY     (name)
) ENGINE=InnoDB

insert into actor(name,password) values('cat01','1234567');
insert into actor(name,password) values('cat02','1234567');
insert into actor(name,password) values('ddddd','1234567');
insert into actor(name,password) values('aaaaa','1234567');



SET AUTOCOMMIT=0;
BEGIN;
SELECT actor_id FROM actor WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE;

該查詢僅僅返回2---3的資料，實際已經對1---3的資料加上排它鎖了。InnoDB鎖住元組1是因為MySQL的查詢計劃
僅使用索引進行範圍查詢（而沒有進行過濾操作，WHERE中第二個條件已經無法使用索引了）

EXPLAIN SELECT actor_id FROM test.actor

    -> WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE \G

*************************** 1. row ***************************

           id: 1

 select_type: SIMPLE

        table: actor

         type: index

possible_keys: PRIMARY

          key: PRIMARY

      key_len: 4

          ref: NULL

         rows: 4

        Extra: Using where; Using index

1 row in set (0.00 sec)

表明儲存引擎從索引的起始處開始，獲取所有的行，直到actor_id<4為假，伺服器無法告訴InnoDB去掉元組 1

為了證明row 1已經被鎖住，我們另外建一個連線，執行如下操作：
SET AUTOCOMMIT=0;
BEGIN;
SELECT actor_id FROM actor WHERE actor_id = 1 FOR UPDATE;

該查詢會被掛起，直到第一個連線的事務提交釋放鎖時，才會執行（這種行為對於基於語句的複製(statement-based replication)
是必要的）。如上所示，當使用索引時，InnoDB會鎖住它不需要的元組。更糟糕的是，如果查詢不能使用索引，MySQL會進行全表掃
描，並鎖住每一個元組，不管是否真正需要。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26855487/viewspace-761424/，如需轉載，請註明出處，否則將追究法律責任。

相關文章