mysql基本介紹和最佳化技巧

標籤：style   blog   http   os   io   使用   strong   ar   for   

一. mysql架構和基本介紹
1. 架構圖

更詳細：

2. 儲存引擎
MYISAM與INNODB對比：
MYISAM：mysql5.1及以前版本的預設儲存引擎。支援全文檢索索引，壓縮，表級鎖等，但不支援事務，行級鎖，崩潰後的資料恢複等
INNODB：mysql5.5及之後的預設儲存引擎。支援事務，行級鎖，資料恢複，mysql5.6 中的innodb(1.2)支援全文檢索索引。

如何選擇：innodb對於絕大多數的使用者都是最佳的選擇，除非某些儲存引擎能滿足特殊需求且使用者很瞭解這種儲存引擎。

二. 查詢效能的方法
1. 設定檔 my.cnf (linux), 
讀設定檔的一般順序：
     1)     /etc/my.cnf
     2)      DATADIR/my.cnf
     3)      ~/.my.cnf
可以通過命令獲知：
mysqld --verbose --help | grep -A 1 ‘Default options’
結果：
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

2. 環境變數
從my,cnf讀取環境變數值，
查看變數值方法： show variables like “”, (不清楚變數名時，用模糊比對%)
例如

設定(全域)變數方法：
set (global) 變數名=變數值
例如設定 開啟慢查詢日誌
set global slow_query_log=1;
設定後查看變數值
show variables like "%slow%”;

| slow_query_log            | ON 

變數列表：http://dev.mysql.com/doc/refman/5.6/en/mysqld-option-tables.html

分析sql語句時一些有用且預設不開啟的變數：
1） 慢查詢日誌: 開關 slow_query_log, 閾值：long_query_time (單位 秒)
2） sql剖析工具profile，開關：profiling，記錄數：profiling_history_size(最大為100)
3)   記錄每條sql語句，開關：general_log, log檔案位置：general_log_file
… …

3. 查看sql服務狀態
文法 SHOW [GLOBAL | SESSION] STATUS [LIKE ‘pattern‘ | WHERE expr]清除狀態 FLUSH STATUS;
清除表緩衝 
reset query cache ;
FLUSH TABLE [TABLE NAME]
    
用法舉例
FLUSH STATUS；
SELECT ...;
SHOW SESSION STATUS LIKE ‘Handler_read%’;  （SHOW SESSION STATUS LIKE ‘Handler_%’;）
EXPLAIN SELECT …;

具體參數可以參考：
http://lxneng.iteye.com/blog/451985
http://hi.baidu.com/thinkinginlamp/item/8d038333c6b0674a3075a1d3

4. 查詢剖析工具 show profiles
開啟 
set profiling=1;
set profiling_history_size=50  最大為100

用法：
show profiles;
show profile; //展示最後一條query時間消耗
show profile for query ID； // 替換ID值 從show profiles 表裡。
顯示更多內容
show profile cpu,block io for query 4;

5. 擷取查詢計劃的資訊 explain 
用法 explain  [sql查詢語句]
例如：

注意每列的含義

三. 資料類型最佳化
1. 選擇最佳化的資料類型原則
     1）盡量使用可以正確儲存資料的最小資料類型。例如： 只存0-200，用tinyint unsigned 更好。
     2）選擇簡單的資料類型。例如整形比字元操作代價更低，儲存時間用datetime而不是字串，用整形儲存ip。

2. 資料類型（主要說整形和字串）
     1）整型 
TINYINT 1 位元組 (-128，127) (0，255) 小整數值 
SMALLINT 2 位元組 (-32 768，32 767) (0，65 535) 大整數值 
MEDIUMINT 3 位元組 (-8 388 608，8 388 607) (0，16 777 215) 大整數值 
INT或INTEGER 4 位元組 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整數值 
BIGINT 8 位元組 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 極大整數值 

思考：tinyint(1)和tinyint(2)儲存空間相比較？

     2）字串
varchar:內容+長度，0-255位元組。
char:長度不足時，空格補足，0-255位元組。

如何選擇：
選擇用varchar：字串的最大長度比平均長度大很多，列更新少。但是一定要估算好長度（排序時）。

text和blob，大資料類型。

      3）浮點，時間，位 等

四. 高效能索引
1. 索引基礎
索引結果為B+樹。

B樹：
       

B-樹：


B+樹：


MYISAM 主索引（輔助索引結構與其相同）：

INNODB 主索引：

INNODB輔助索引：


索引優點：
     1） 索引大大減少了伺服器需要掃描的資料量；
     2） 索引可以協助伺服器避免排序和暫存資料表；
     3） 索引可以將隨機I/O變為順序I/O。

2. 高效能索引策略

1） 獨立的列
     將索引單獨放到比較符號的一側，否則無法利用索引。

2）首碼索引和索引選擇性

3）合適的索引順序

4）覆蓋索引
極大的提高效能。

5）使用索引掃描做排序

其他策略:
1）多條件過濾，盡量重複利用索引，(sex,country,age)有索引，現在有查詢條件 sex，country，region，age 或者sex,country,region,city,age需要再建索引嗎？
例如：(gender,name)  gender,gender name
select * from staff where name like “123”；
select * from staff where gender in (0,1) and name like “123"；
2） 避免多個範圍查詢
3）延遲關聯
select *  from table2 order by cnt,id limit 100000,10;

select *  from table2 join (select id from table2 order by cnt limit 100000,10) as x using(id) ;

五. 查詢效能最佳化
1） 查詢執行的基礎

2） 查詢最佳化工具的局限性
union限制
(select first_name,last_name from actor order by last_name) union (select first_name,last_name from customer order by last_name) order by last_name limit 20;

(select first_name,last_name from actor order by last_name limit 20) union (select first_name,last_name  from customer order by last_name limit 20) order by last_name limit 20 ;

在同一個表上查詢和更新
update foo as outer set cnt= (select count(*) from foo as inner where inner.type=outer.type);

update foo join (select type,count(*) as cnt from foo group by type) as der using(type) set foo.cnt=der.cnt;

3）最佳化特定關聯查詢
最佳化關聯：
explain select * from film inner join (film_actor,actor) on (film.film_id = film_actor.film_id and film_actor.actor_id = actor.actor_id); explain select straight_join * from film inner join (film_actor,actor) on (film.film_id = film_actor.film_id and film_actor.actor_id = actor.actor_id);

最佳化limit
最佳化sql_calc_found_rows
 select sql_calc_found_rows *  from table2 order by cnt,id limit 100000,10;
 select found_rows();

selelt count(*) from table ..

mysql基本介紹和最佳化技巧