Mysql查詢最佳化小結

標籤：order   刪除   字元集   mysql查詢   並且   維護   engine   hash演算法   部分   

轉自http://www.cnblogs.com/112ba/p/6220650.html

資料類型

簡單原則：更小更好，簡單就好，避免NULL
1)整型如int(10)括弧中的值與儲存大小無關
2)實數decimal比float與double佔用更大儲存空間且CPU不能直接對decimal進行計算
3)字串列最大長度<=255varchar使用一位元組儲存長度，否則使用2位元組。char(10)括弧中是字元數，而不是位元組數(位元組數與列字元集相關)。
4)使用MYSQL的內建類型儲存日期與時間，如datetime、timestamp

索引類型

在資料之外，資料庫系統還維護著滿足特定尋找演算法的資料結構，這些資料結構以某種方式引用(指向)資料，這樣就可以在這些資料結構上實現進階尋找演算法。這種資料結構，就是索引。

1) B-Tree索引
MyISAM、InnoDB使用B+Tree作為索引結構
MyISAM與InnoDB在實現上有所不同：MyISAM的主鍵與普通索引使用的結構一致，葉子節點儲存了指向資料記錄的地址。

InnoDB的主鍵採用叢集索引的方式(葉子節點
儲存完整的行資料)，而普通索引則與MyISAM
相似，但是葉子節點儲存的是主鍵的值。

NDB Cluster內部實際使用T-Tree結構實現索引

MYSQL中能使用B-Tree索引的查詢類型：
create table people (
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum(‘m’,‘f’) not null,
key (last_name,first_name,don)
) engine=myisam;
1. 匹配全名
where last_nam=’a’ and first_name=’b’ and dob=’1990-01-01′
2. 匹配最左首碼
where last_name=’a’ 或 where last_name=’a’ and first_name=’b’
3. 匹配列首碼
where last_name like ‘a%’
4. 匹配範圍值
where last_name >= ‘a’ and last_name<=’d’
5.精確匹配一部分並且匹配某個範圍中另一部分
where last_name=’a’ and first_name like ‘k%’
6. 只訪問索引的查詢
B-Tree支援只訪問索引的查詢，不訪問資料行(覆蓋
索引)
select first_name from people where last_name=’a’

MYSQL中B-Tree索引的一些局限：

1. 查詢不是最左首碼開始不能使用索引
where first_name=’a‘或where last_name like ‘%a’
2. 不能跳過索引中的列
where last_name=’a’ and dob=’1990-01-01′將只會所使用索引的第一列
3. 第一個範圍條件右邊的列不能使用索引
where last_name=’a’ and first_name like ‘a%’ and dob=’1990-01-01′ 因為like是範圍查詢，所以只會使用索引的前兩列

2) Hash索引

Hash索引是建立在hash表基礎上的，只對使用了索
引中每一列的精確查詢有用(Memory和NDB支援,
InnoBD支援自適應hash索引)

Hash查詢很快，但有些局限：
1. 索引中只儲存hash碼與行指標，不能覆蓋索引
2. 不能使用hash索引排序
3. 不支援部分索引列匹配
4. 只支援’=‘,’IN’,‘<=>’相等比較查詢,範圍查詢不能使用Hash索引
5.如果hash演算法的碰撞率很高會影響索引效能
6. 碰撞率很高的Hash索引刪除行代價很高

查詢最佳化

1. 隔離列
隔離列意味著條件中的列不是運算式也不位於函數中
2. 首碼索引和索引選擇性
索引選擇性=不重複索引值/表中所有行，越大越好
首碼索引:對於char、varchar、blob、text類型可以
索引開始的幾個字元，通過索引選擇性確定索引幾個
字元
KEY `bc` (`b`,`c`(5))
KEY `cd` (`c`(5),`d`(5))

3. 為排序使用索引

order by子句的順序與索引中列順序完全一致，並且所有列排序方向一樣才可以使用索引排序。如果聯結多表，只有order by子句中所有列引用的是第一個表才可以(查詢最佳化工具重寫查詢後)。

order by子句也要遵循索引最左首碼原則(前置列在where中精確匹配除外)。

例：

key t1 (day,in_id,con_id) 如下查詢可以使用索引(explain的Extra中不會看到using filesort)

4. group by使用索引

group by 使用索引規則與order by一致。

group by 預設會進行排序操作，如果不關心返回順序可以加上order by null來跳過排序。

distinct fd 如果fd列有索引，且where子句中使用了該索引，則也可以使用索引最佳化distinct

5. 最佳化min、max

select min(fd2) from tt1 where m=’2012-03-29′;

select fd2 from tt1 force index (fd2) where

m=’2012-03-29′ order by fd2 limit 1;

select max(fd2) from tt1 where m=’2012-03-29′;

select fd2 from tt1 force index (fd2) where

m=’2012-03-29′ order by fd2 desc limit 1;

6. 使用explain分析查詢

只能對SELECT查詢執行EXPLAIN分析。可以加

EXTENED關鍵字，然後使用show warnings查看查

詢最佳化器重寫後的SQL，如果對分區表可以加

PARTITIONS關鍵字。如：

EXPLAIN EXTENED SELECT …

Mysql查詢最佳化小結