MySQL:資料庫入門篇5

1.存儲引擎

innodb與MyIASM存儲引擎的區別:
　　1.innodb 是mysql5.5版本以後的預設存儲引擎, 而MyISAM是5.5版本以前的預設存儲引擎.
　　2.innodb 支持事物,而MyISAM不支持事物
　　3.innodb 支持行級鎖.而MyIASM 它支持的是併發的表級鎖.
　　4.innodb 支持外鍵, 而MyIASM 不支持外鍵
　　5.innodb與MyIASM存儲引擎都採用B+TREE存儲數據, 但是innodb的索引與數據存儲在一個文件中,這種方式我們稱之為聚合索引.
　　　　而MyIASM則會單獨創建一個索引文件,也就是說,數據與索引是分離開的
　　6.在效率方面MyISAM比innodb高,但是在性能方面innodb要好一點.
2.索引
　　1.普通索引 加速查詢
　　創建:
　　create table t1(
　　id int not null,
　　name varchar(50),
　　index idx_id (id)
　　)
　　通過命令創建
　　CREATE index idx_name on t1(name);
　　查看索引
　　show index from t1;
　　刪除索引
　　drop index ide_id on t1;


　　2.唯一索引 加速查詢 和 唯一約束（可含一個null 值）
　　create table tb2(
　　id int not null auto_increment primary key,
　　name varchar(50) not null,
　　age int not null,
　　unique index idx_age (age)
　　)

　　create unique index idx_age on tb2(age);

　　3.主鍵索引 加速查詢 和 唯一約束（不可含null）

　　alter table tb3 add primary key(id);

　　alter table tb3 drop primary key;

　　4.組合索引
　　create unique index idx_age on tb2(age,name);

3. 聚合索引和輔助索引
總結二者區別:
　　相同的是：不管是聚集索引還是輔助索引，其內部都是B+樹的形式，即高度是平衡的，葉子結點存放著所有的數據。
　　不同的是：聚集索引葉子結點存放的是一整行的信息,而輔助索引葉子結點存放的是單個索引列信息.

4如何正確使用索引
#1. 範圍查詢(>、>=、<、<=、!= 、between...and)
　　#1. = 等號
　　select count(*) from userinfo where id = 1000 -- 執行索引,索引效率高

　　#2. > >= < <= between...and 區間查詢
　　select count(*) from userinfo where id <100; -- 執行索引,區間範圍越小,索引效率越高

　　select count(*) from userinfo where id >100; -- 執行索引,區間範圍越大,索引效率越低

　　select count(*) from userinfo where id between 10 and 500000; -- 執行索引,區間範圍越大,索引效率越低

　　#3. != 不等於
　　select count(*) from userinfo where id != 1000; -- 索引範圍大,索引效率低


#2.like '%xx%'
#為 name 欄位添加索引
create index idx_name on userinfo(name);

select count(*) from userinfo where name like '%xxxx%'; -- 全模糊查詢,索引效率低
select count(*) from userinfo where name like '%xxxx'; -- 以什麼結尾模糊查詢,索引效率低

#例外: 當like使用以什麼開頭會索引使用率高
select * from userinfo where name like 'xxxx%';

#3. or
select count(*) from userinfo where id = 12334 or email ='xxxx'; -- email不是索引欄位,索引此查詢全表掃描

#例外：當or條件中有未建立索引的列才失效，以下會走索引
select count(*) from userinfo where id = 12334 or name = 'alex3'; -- id 和 name 都為索引欄位時, or條件也會執行索引

#4.使用函數
select count(*) from userinfo where reverse(name) = '5xela'; -- name索引欄位,使用函數時,索引失效

#例外:索引欄位對應的值可以使用函數,我們可以改為一下形式
select count(*) from userinfo where name = reverse('5xela');

#5.類型不一致
#如果列是字元串類型，傳入條件是必須用引號引起來，不然...
select count(*) from userinfo where name = 454;

#類型一致
select count(*) from userinfo where name = '454';

#6.order by
#排序條件為索引，則select欄位必須也是索引欄位，否則無法命中
select email from userinfo ORDER BY name DESC; -- 無法命中索引

select name from userinfo ORDER BY name DESC; -- 命中索引

#特別的:如果對主鍵排序，則還是速度很快：
select id from userinfo order by id desc;

5.組合索引

組合索引: 是指對錶上的多個列組合起來做一個索引.

最左匹配原則: 從左往右依次使用生效，如果中間某個索引沒有使用，那麼斷點前面的索引部分起作用，斷點後面的索引沒有起作用；

select * from mytable where a=3 and b=5 and c=4;
　 #abc三個索引都在where條件裡面用到了，而且都發揮了作用

select * from mytable where c=4 and b=6 and a=3;
　　#這條語句列出來只想說明 mysql沒有那麼笨，where裡面的條件順序在查詢之前會被mysql自動優化，效果跟上一句一樣

select * from mytable where a=3 and c=7;
　　#a用到索引，b沒有用，所以c是沒有用到索引效果的

select * from mytable where a=3 and b>7 and c=3;
　　#a用到了，b也用到了，c沒有用到，這個地方b是範圍值，也算斷點，只不過自身用到了索引

select * from mytable where b=3 and c=4;
　　#因為a索引沒有使用，所以這裡 bc都沒有用上索引效果

select * from mytable where a>4 and b=7 and c=9;
　　#a用到了 b沒有使用，c沒有使用

select * from mytable where a=3 order by b;
　　#a用到了索引，b在結果排序中也用到了索引的效果

select * from mytable where a=3 order by c;
　　#a用到了索引，但是這個地方c沒有發揮排序效果，因為中間斷點了

select * from mytable where b=3 order by a;
　　#b沒有用到索引，排序中a也沒有發揮索引效果

6.註意事項
1. 避免使用select *
2. 其他資料庫中使用count(1)或count(列) 代替 count(*),而mysql資料庫中count(*)經過優化後,效率與前兩種基本一樣.
3. 創建表時儘量時 char 代替 varchar
4. 表的欄位順序固定長度的欄位優先
5. 組合索引代替多個單列索引（經常使用多個條件查詢時）
6. 使用連接（JOIN）來代替子查詢(Sub-Queries)
7. 不要有超過4個以上的表連接（JOIN）
8. 優先執行那些能夠大量減少結果的連接。
9. 連表時註意條件類型需一致
10.索引散列值不適合建索引，例：性別不適合

7.查詢計劃
預估查詢的結果,不太精準
type : 查詢計劃的連接類型, 有多個參數，先從最佳類型到最差類型介紹

　　性能： null > system/const > eq_ref > ref > ref_or_null > index_merge > range > index > all


8.慢日誌查詢
將mysql伺服器中影響資料庫性能的相關SQL語句記錄到日誌文件，
通過對這些特殊的SQL語句分析，改進以達到提高資料庫性能的目的。

#.查詢慢日誌配置信息 :
show variables like '%query%';
#.修改配置信息
set global slow_query_log = on;

# 顯示參數　　
show variables like '%log_queries_not_using_indexes';
# 開啟狀態
set global log_queries_not_using_indexes = on;


#查看慢日誌記錄的方式
show variables like '%log_output%';

#設置慢日誌在文件和表中同時記錄
set global log_output='FILE,TABLE';

#查詢時間超過10秒就會記錄到慢查詢日誌中
select sleep(3) FROM user ;

#查看表中的日誌
select * from mysql.slow_log;