1. InnoDB支持事務, MyISAM不支持;
2. InnoDB支持外鍵, 而MyISAM不支持;
3. InnoDB是聚集索引,使用B+Tree作為索引結構,資料檔案是和(主鍵)索引綁在一起的
MyISAM是非聚集索引, 也是使用B+Tree作為索引結構, 索引和資料檔案是分離的, 索引保存的是資料檔案的指標, 主鍵索引和輔助索引是獨立的
InnoDB的B+樹主鍵索引的葉子節點就是資料檔案, 輔助索引的葉子節點是主鍵的值; 而MyISAM的B+樹主鍵索引和輔助索引的葉子節點都是資料檔案的地址指標
4. InnoDB支持表、行(默認)級鎖, 而MyISAM支持表級鎖
InnoDB的行鎖是實作在索引上的, 而不是鎖在物理行記錄上. 潛臺詞是, 如果訪問沒有命中索引, 也無法使用行鎖, 將要退化為表鎖
8、InnoDB表必須有主鍵(用戶沒有指定的話會自己找或生產一個主鍵), 而Myisam可以沒有
9、Innodb存盤檔案有 .frm. ibd, 而Myisam是 .frm .MYD .MYI
Innodb：frm是表定義檔案，ibd是資料檔案
Myisam：frm是表定義檔案，myd是資料檔案，myi是索引檔案
索引是幫助MySQL高效獲取資料的排好序的資料結構
B-Tree
葉節點具有相同的深度,葉節點的指標為空
所有索引元素不重復
節點中的資料索引從左到右遞增排列
B+Tree(B-Tree變種)
非葉子節點不存盤data,只存盤索引(冗余),可以放更多的索引
葉子節點包含所有索引欄位
葉子節點用指標連接,提高區間訪問的性能
MyISAM索引檔案和資料檔案是分離的(非聚集)
InnoDB索引實作(聚集)
表資料檔案本身就是按B+Tree組織的一個索引結構檔案
聚集索引-葉節點包含了完整的資料記錄
聚集索引
mysql的innodb主鍵索引,如果沒有主鍵索引就是唯一索引
InnoDB聚合索引: 索引欄位在一起存盤到key,按照索引排序排列
innodb聯合索引示例(索引最左前綴原理)
sql執行計劃 explan + sql
id ID可以如果相同認為是同一組,從上往下執行,在所有組中id越大,優先級越高,越先執行
select_type 查詢型別
1)SIMPLE 簡單查詢,不包括子查詢或UNION
2)PRIMARY 查詢中包含任何復雜的子部分,最外層查詢被標記為
3)SUBQUERY 在select或where里包含了子查詢
4)DERIVED 在from串列中包含了子查詢被標記為DERIVED(衍生),mysql會遞回執行這些子查詢,把結果放在臨時表
5) UNION 若在第二個select出現在union后,會標記為UNION.若union包含在from子句的查詢中,外層會標記為DERIVED
6)UNION RESULT 從UNION中獲取select
table 這一行資料顯示的表,type是null會直接走索引,不會走表,效率最好
type 從最好到最差的順序system > const > eq_ref > ref > range > index > ALL 一般來說最少達到range,最好能達到ref
possible_keys 顯示可能應用在這張表中的索引,一個或多個,查詢涉及到的欄位若存在索引,則也列出來,但不一定被查詢實際用到
key 實際使用的索引
ken_len 索引欄位的最大可能長度,并非實際長度,key_len長度越短越好
ref 表示索引的哪一列被使用,也可能是常量
rows 根據表統計資訊和索引選用的情況,大致估算出找到所需記錄的讀取行數
Extra 其他的資訊
1)Using index: 使用覆寫索引
2)Using where: 使用 where 陳述句來處理結果，查詢的列未被索引覆寫
3)Using index condition: 查詢的列不完全被索引覆寫, where條件中是一個前導列的范
圍
4)Using temporary: mysql需要創建一張臨時表來處理查詢. 出現這種情況一般是要進行
優化的, 首先是想到用索引來優化
5)Using filesort: 將用外部排序而不是索引排序,資料較小時從記憶體排序,否則需要在磁盤
完成排序. 這種情況下一般也是要考慮使用索引來優化的
6)Select tables optimized away: 使用某些聚合函式(比如 max、min)來訪問存在索引
的某個欄位是
索引失效的情況
1.(復合索引)全值匹配我最愛
2.最佳左前綴法則(帶頭大哥不能死,中間兄弟不能斷)
3.不在索引列上做任何操作(計算,函式,(自動or手動)型別轉換),會導致索引失效而轉向全表掃描
4.存盤引擎不能使用索引中范圍條件右邊的列
5.盡量使用覆寫索引(只訪問索引的查詢(索引列和查詢列一致)),減少select*
6.mysql在使用不等于(! =或者<>)的時候無法使用索引會導致全表掃描
7.is null,is not nul 也無法使用索引
8.like以通配符開頭(“%abc.…)mysql索引失效會變成全表掃描的操作
9.字串不加單引號索引失效
10.少用or,用它來連接時會索引失效
全值匹配我最愛(聚合索引)，最左前綴要遵守；
帶頭大哥不能死，中間兄弟不能斷；
索引列上少計算，范圍之后全失效；
Like百分寫最右，覆寫索引不寫星；
不等空值還有or，索引失效要少用；
VAR引號不可丟，SQL高級也不難！
解決like‘%字串%’時索引不被使用,使用覆寫索引,即建的索引和查詢的欄位個數順序最好完全一致
sql優化小表驅動大表,非要大表驅動小表用exists
mysql支撐Index和FileSort兩種方式排序排序,Index效率高,FileSort效率低
mysql慢查詢是否開啟 > show variables like 'slow_query%';
開啟慢查詢> set global slow_query_log='ON';
設定慢查詢時間> set global long_query_time=1;
查看設定后的引數(重新建連或新開回話查看) > show variables like 'long_query_time';
mysql范圍查找要是范圍過大有可能不走索引,同時會出現根據效率考慮走不走索引
trace工具
> set session optimizer_trace="enabled=on",end_markers_in_json=on; -- 開啟trace(以json展示)
> select * from employees where name > 'a' order by position;
> SELECT * FROM information_schema.OPTIMIZER_TRACE;
查看可能走索引的成本已經索引行數,來判斷具體走的索引
優化總結:
1. MySQL支持兩種方式的排序filesort和index,Using index是指MySQL掃描索引本身完成排序.index
效率高,filesort效率低
2. order by滿足兩種情況會使用Using index
1) order by陳述句使用索引最左前列
2) 使用where子句與order by子句條件列組合滿足索引最左前列
3. 盡量在索引列上完成排序, 遵循索引建立(索引創建的順序)時的最左前綴法則
4. 如果order by的條件不在索引列上, 就會產生Using filesort
5. 能用覆寫索引盡量用覆寫索引
6. group by與order by很類似, 其實質是先排序后分組, 遵照索引創建順序的最左前綴法則.對于group
by的優化如果不需要排序的可以加上order by null禁止排序. 注意: where高于having, 能寫在where中
的限定條件就不要去having限定了
filesort檔案排序方式
單路排序: 是一次性取出滿足條件行的所有欄位,然后在sort buffer中進行排序;用trace工具可
以看到sort_mode資訊里顯示< sort_key, additional_fields >或者< sort_key,packed_additional_fields >
雙路排序(又叫回表排序模式): 是首先根據相應的條件取出相應的排序欄位和可以直接定位行
資料的行 ID,然后在 sort buffer 中進行排序,排序完后需要再次取回其它需要的欄位；用trace工具
可以看到sort_mode資訊里顯示< sort_key, rowid >
sql分頁優化
讓查詢盡可能的少,比如覆寫索參考回表關聯查詢
select * from employees e inner join (select id from employees order by name limit 90000,5) ed
on e.id = ed.id;
mysql表關聯的兩種方式
1. 嵌套回圈連接(Nested-Loop Join(NLJ)演算法 鏈接欄位是索引
一次一行回圈地從第一張表(稱為驅動表,一般是小表)中讀取行, 在這行資料中取到關聯欄位, 根據關聯欄位在另一張表(被驅動表,一般是大表)里取出滿足條件的行, 然后取出兩張表的結果合集
2. 基于塊的嵌套回圈連接 Block Nested-Loop Join(BNL)演算法 鏈接欄位不是索引
把驅動表的資料讀入到 join_buffer 中, 然后掃描被驅動表, 把被驅動表每一行取出來跟 join_buffer 中的資料做對比
對于Join關聯sql的優化
1.關聯欄位加索引, 讓mysql做join操作時盡量選擇嵌套回圈連接(NLJ)演算法
2.小標驅動大表, 寫多表連接sql時如果明確知道哪張表是小表可以用straight_join寫法固定連接驅動方式, 省去mysql優化器自己判斷的時間
straight_join相當于join類似, 但能讓左邊的表來驅動右邊的表, 能改表優化器對于聯表查詢的執
行順序. 比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表制定mysql選著 t2 表作為驅動表，
straight_join只適用于inner join, 并不適用于left join, right join. (因為left join，right join已經代表指
定了表的執行順序)
in和exsits優化
原則：小表驅動大表，即小的資料集驅動大的資料集
in：當B表的資料集小于A表的資料集時，in優于exists
select * from A where id in (select id from B)
exists：當A表的資料集小于B表的資料集時，exists優于in
將主查詢A的資料,放到子查詢B中做條件驗證,根據驗證結果(true或false)來決定主查詢的資料是否保留
select * from A where exists (select 1 from B where B.id = A.id)
count count不計算null值
select count(1) from employess; 遍歷二級索引樹,不遍歷索引樹的值
select count(id) from employess; mysql5.7之后走的輔助索引
select count(name) from employess; name不為空
select count(*) from employess; 走輔助索引
count(1) > count(name) == count(*) > count(id)
mysql鎖
手動增加表鎖 > lock table 表名稱 read(write),表名稱2 read(write);
查看表上加過的鎖 > show open tables;
洗掉表鎖 > unlock tables;
讀鎖會阻塞寫, 但是不會阻塞讀; 而寫鎖則會把讀和寫都阻塞
行鎖支持事物: 原子性(Atomicity),一致性(Consistent),隔離性(Isolation),持久性(Durable)
并發事務處理帶來的問題
更新丟失, 臟讀(讀其他未提交事物的資料), 不可重讀(修改資料), 幻讀(新增資料)
mysql事物級別默認 "不可重復讀"
常看當前資料庫的事務隔離級別: show variables like 'tx_isolation';
設定事務隔離級別：set tx_isolation='REPEATABLE-READ';
可串行化
間隙鎖
InnoDB的行鎖是針對索引加的鎖, 不是針對記錄加的鎖. 并且該索引不能失效, 否則都會從行鎖升級為表鎖
mysql MVVC 為了性能和處理大資料基于快照版本
select * from account(創建了查詢快照, 記錄執行sql這一刻最大的已提交事務id(快照點已提交最大事務id)) 快斬訓于insert,update,delete
sql
-- 新建資料庫 create database `dbname` default character set utf8mb4 collate utf8mb4_unicode_ci;
create database `dbname` default character set utf8 collate utf8_general_ci;
-- 新建資料庫并授權: grant all privileges on `dbname`.* to 'userName'@'%' identified by 'password';
-- 重繪服務 flush privileges;
-- 創建mysql觸發器沒有權限(log_bin_trust_function_creators 1),root登陸到對應資料庫
set global log_bin_trust_function_creators = 1;
-- 新建資料庫 create database [dbname] default character set utf8 collate utf8_general_ci;
-- 新建資料庫并授權: grant all privileges on 'dbname'.* to 'userName'@'%' identified by 'password';
-- 創建用戶 create user 'userName'@'%' identified by 'password';
-- 用戶授權資料庫 grant all privileges on [dbname].* to 'userName';
-- 或 grant select,insert,update,delete,create,drop on [dbname].* to 'userName';
-- 取消用戶所有資料庫(表)的所有權限 revoke all on *.* from userName;
-- 洗掉用戶 delete from mysql.user where user='userName';
-- 洗掉資料庫 drop database [dbname];
-- 重繪服務 flush privileges;
-- 洗掉賬戶 drop user hustjhcg@localhost;
-- 重繪服務 flush privileges;
-- 修改密碼 set password for root=password('123456');
-- 切換資料庫 use mysql;
-- 查詢資料庫賬號和權限 select host,user from user;
//如果為null則改為0
IFNULL( tsmcs.sign_count, 0 ) signCount,
( SELECT count( DISTINCT somo.erp_cust_id )FROM tb_sup_order_main_original somo WHERE somo.supplier_id = sb.supplier_id ) AS custNum,
格式化金額:
四舍五入:CONVERT(sum(od.purchase_num * od.member_price), DECIMAL(10,2))
千分位:else FORMAT( sod.member_price*sod.purchase_num,2) end as totalPrice,
-- 修改表的創建時間和更新時間欄位
alter table t_users add create_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間';
alter table t_users add update_at timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新時間';
-- 添加聯合索引 alter table tb_sup_cust add index INDEX_DANW_BRANCHIDY(索引名) (cust_name,branch_id);
-- 添加唯一索引 alter table tb_sup_orderaudit_totalprice add unique (supplier_id);
-- 創建臨時表并把已有的表資料添加到臨時表
CREATE TABLE tem_t_users SELECT uid,username,password FROM t_users;
-- 查出一張表的欄位插入臨時表
insert into tem_t_users (`uid`,`username`,`password`) values (2,'a',(select password from t_users));
或
insert into tem_t_users (`uid`,`username`,`password`) (select uid,username,password from t_users);
-- 根據查詢臨時表更改已有的表
update out_user as a inner join out_user_copy1 as b on a.tu_id = b.tu_id set a.time_update = a.time_create;
explan + sql
id ID可以如果相同認為是同一組,從上往下執行,在所有組中id越大,優先級越高,越先執行
select_type 查詢型別
1)SIMPLE 簡單查詢,不包括子查詢或UNION
2)PRIMARY 查詢中包含任何復雜的子部分,最外層查詢被標記為
3)SUBQUERY 在select或where里包含了子查詢
4)DERIVED 在from串列中包含了子查詢被標記為DERIVED(衍生),mysql會遞回執行這些子查詢,把結果放在臨時表
5) UNION 若在第二個select出現在union后,會標記為UNION.若union包含在from子句的查詢中,外層會標記為DERIVED
6)UNION RESULT 從UNION中獲取select
table 這一行資料顯示的表
type 從最好到最差的順序system > const > eq_ref > ref > range > index > ALL 一般來說最少達到range,最好能達到ref
possible_keys 顯示可能應用在這張表中的索引,一個或多個,查詢涉及到的欄位若存在索引,則也列出來,但不一定被查詢實際用到
key 實際使用的索引
ken_len 索引欄位的最大可能長度,并非實際長度,key_len長度越短越好
ref 表示索引的哪一列被使用,也可能是常量
rows 根據表統計資訊和索引選用的情況,大致估算出找到所需記錄的讀取行數
Extra 其他的資訊
索引失效的情況
1.(復合索引)全值匹配我最愛
2.最佳左前綴法則(帶頭大哥不能死,中間兄弟不能斷)
3.不在索引列上做任何操作(計算,函式,(自動or手動)型別轉換),會導致索引失效而轉向全表掃描
4.存盤引擎不能使用索引中范圍條件右邊的列
5.盡量使用覆寫索引(只訪問索引的查詢(索引列和查詢列一致)),減少select*
6.mysql在使用不等于(! =或者<>)的時候無法使用索引會導致全表掃描
7.is null,is not nul 也無法使用索引
8.like以通配符開頭(“%abc.…)mysql索引失效會變成全表掃描的操作
9.字串不加單引號索引失效
10.少用or,用它來連接時會索引失效
全值匹配我最愛，最左前綴要遵守；
帶頭大哥不能死，中間兄弟不能斷；
索引列上少計算，范圍之后全失效；
Like百分寫最右，覆寫索引不寫星；
不等空值還有or，索引失效要少用；
VAR引號不可丟，SQL高級也不難！
解決like‘%字串%’時索引不被使用,使用覆寫索引,即建的索引和查詢的欄位個數順序最
好完全一致
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/6043.html
標籤：MySQL
上一篇：MySQL學習筆記（18）：SQL優化
下一篇：DQL：查詢表中的記錄
