最近在學(xué)MySQL惫恼，這篇是第一篇祈纯，希望自己能夠好好記筆記，方便以后的復(fù)習(xí)筛婉。
MYSQL邏輯分層
當(dāng)我們執(zhí)行一條命令時(shí)，比如
select * from student
數(shù)據(jù)庫中的操作順序如下：
連接層 -> 服務(wù)層 ->引擎層 ->存儲層
- 連接層：只負(fù)責(zé)查詢語句和數(shù)據(jù)庫的連接
- 服務(wù)層：提供用戶使用接口，例如select枫甲、delete...同時(shí)還可以對sql語句進(jìn)行優(yōu)化
- 引擎層：數(shù)據(jù)的存儲方式（常用的有InnoDB(事務(wù)優(yōu)先)想幻、MyISAM（性能優(yōu)先））
- 存儲層：最終執(zhí)行后的數(shù)據(jù)在存儲層
查看當(dāng)前使用了那些引擎
指定數(shù)據(jù)庫對象的引擎：
當(dāng)有時(shí)候想要性能優(yōu)先的時(shí)候举畸，可以改掉默認(rèn)的引擎
//修改默認(rèn)的存儲引擎抄沮，可以在創(chuàng)建的時(shí)候指定
create table tb( id int(4) auto_increment, name varchar(5), dept varchar(5),primary key(id))ENGINE=MyISAM DEFAULT ChARSET=utf8;
SQL優(yōu)化
為什么要優(yōu)化：等待時(shí)間太長，執(zhí)行時(shí)間太長率挣，索引失效椒功，服務(wù)器參數(shù)不合理（緩存區(qū)參數(shù)）
SQL:
編寫過程：
select ... from ... join .... on where group by ... having ... order by limit
解析過程：
可能跟編寫的過程不一樣
from...on...join ... where ...group by... having...select ...
b.SQL優(yōu)化
主要是優(yōu)化索引动漾，索引相當(dāng)于書的目錄旱眯，是高效查詢數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)（樹：B(默認(rèn))/Hash樹...）
我們可以看到删豺，右邊是一個(gè)加了索引的樹（當(dāng)然這里是一個(gè)平衡二叉樹呀页，真實(shí)的環(huán)境是B樹）叫搁，加了索引樹之后查詢年齡只需要3次，不加就要5次音诫，在千萬級別的數(shù)據(jù)庫中雪位，這樣可以節(jié)省大量的搜索時(shí)間竭钝。
- 弊端：
1 索引本身很大，可以存放在內(nèi)存/硬盤
2 少量數(shù)據(jù)雹洗，不需要索引
3 頻繁更新香罐，每一次都要
4 很少使用的字段，不需要加
5 雖然可以增加檢索效率时肿，但是降低了增刪改的效率 - 優(yōu)勢：
1 提高查詢效率（降低IO使用率）
2 降低CPU使用率：比如DESC庇茫，索引本身就排好序，不需要再排序了
BTree原理
數(shù)據(jù)都在葉節(jié)點(diǎn)里面螃成，非葉子節(jié)點(diǎn)的數(shù)據(jù)只是為了分割數(shù)據(jù)
索引
-分類
1 單值索引：單列的值旦签，一個(gè)表可以有多個(gè)單值索引
2 唯一索引：不能重復(fù)宁炫，一般用id罩阵，值不能重復(fù)
3 復(fù)合索引：多個(gè)列構(gòu)成，相當(dāng)于二級目錄（篩選兩次威创，沒有必要全部列都用）
創(chuàng)建索引
方式一
create 索引類型 索引名 on 表（字段）
//根據(jù)部門創(chuàng)建一個(gè)單值索引
create index dept_index on tb(dept);
//給name加上唯一索引，假設(shè)name是唯一的
create unique index name_index on tb(name);
//復(fù)合索引
create index dept_name_index on tb(dept, name);
方式二
alter table 表名 索引類型 索引名 （字段）
//根據(jù)部門創(chuàng)建一個(gè)單值索引
alter table tb add index dept_index(dept);
//給name加上唯一索引，假設(shè)name是唯一的
alter table tb add unique index name_index(dept);
//復(fù)合索引
alter table tb add index dept_name_index(dept,name);
DDL:create alter drop都是事務(wù)性語句蛉威，自動提交 不需要commit择示；
注意
- 主鍵索引不能為null废恋，唯一索引可以是null
- primary就是默認(rèn)的主鍵索引
刪除索引
drop index name_index on tb;
查詢索引
show index from table
SQL性能分析
- sql執(zhí)行計(jì)劃：（explain 可以模擬sql優(yōu)化器執(zhí)行sql語句蚓哩，讓開發(fā)人員知道自己的sql執(zhí)行狀況）
- 查詢優(yōu)化可能會干擾我們的優(yōu)化
- 查詢執(zhí)行計(jì)劃
id(編號) | select_type（查詢類型） | table（表） | partitions | type（類型） | possible_keys（預(yù)測索引） | key | key_len | ref | rows | filtered | Extra
create table course
(
cid int(3),
cname varchar(20),
tid int(3)
);
create table teacher
(
tid int(3),
tname varchar(20),
tcid int(3)
);
create table tearcherCard
(
tcid int(3),
tcdesc varchar(200)
);
//查詢課程編號為2或教師證編號為 3的老師信息
explain select t.* from teacher t, course c, tearcherCard tc where t.tid=c.tid and tc.tcid=t.tcid and (c.cid=2 or tc.tcid=3);
數(shù)據(jù)的順序：t3-tc3-c4
數(shù)據(jù)的順序：tc3-t6-c4
-為什么會有表的差異（笛卡爾積）
中間結(jié)果數(shù)據(jù)會變：數(shù)據(jù)小的表優(yōu)先查詢
id值不同的時(shí)候寂拆，越大越優(yōu)先：查詢sql課程的老師的描述
explain select tc.tcdesc from teacher t, course c, tearcherCard tc where t.tid=c.tid and tc.tcid=t.tcid and c.cname='sql';
//多表連接寫成子查詢形式(在嵌套子查詢時(shí)先查最內(nèi)存在查最外層)
explain select tc.tcdesc from tearcherCard tc where tc.tcid=(
select t.tcid from teacher t where t.tid=(select c.tid from course c where c.cname='sql')
);
- id(編號) ：有相同和不同尝江，越大越優(yōu)先，id值相同從上往下順序執(zhí)行（explain語句中）
- select_type（查詢類型）：包含子查詢sql中的子查詢彼妻，PRIMARY、SUBQUERY炮温、SIMPLE（最簡單的担巩，沒有子查詢拳话，union）坚俗，derived（衍生查詢）：from中只有一張表，在from子查詢中（之前查詢過來的）
- type（類型）：索引類型system>const>eq_ref>ref>range>index>all
常用
ref
ref:不需要唯一性索引拟糕，數(shù)量是0或多
range
range:檢索一定范圍內(nèi)的數(shù)據(jù)辱挥，where里面的范圍（between in(有時(shí)失效) > <=）
index:查詢數(shù)據(jù)晤碘，查詢?nèi)克饕锩娴臄?shù)據(jù)全部索引
all：查詢所有數(shù)據(jù)全部數(shù)據(jù)
- possible_keys（預(yù)測索引）：預(yù)測用的索引
- key：實(shí)際的索引
- key_len：索引的長度，判斷復(fù)合索引是否完全被使用
create table text(
name char(20) not null default ''
);
alter table text add column name1 char(20);
--加入復(fù)合索引蹬竖，現(xiàn)根據(jù)name查劈榨，查不到旱函，再根據(jù)另外一個(gè)name1查
alter table text add index index_name_name1(name,name1);
-
ref：指明當(dāng)前表參照的字段
引用，一定要加上索引 rows：被索引優(yōu)化查詢的數(shù)據(jù)個(gè)數(shù)
Extra：
1 using filesort--當(dāng)前sql語句需要額外的一次排序
復(fù)合索引染簇，不能跨列过蹂，最佳左前綴
create table A(
a1 int,
a2 int,
a3 int,
index idx_a1_a2_a3(a1,a2,a3),
);
查找的時(shí)候只能是select a1 order by a2...
不能是a2...a3或者a1...a3之類的，因?yàn)槎伎缌械某秸@樣會出現(xiàn)using filesort情況
2 using temporary性能損耗比較大藕夫，用到了臨時(shí)表，常見于group by語句中
根據(jù)a1查找梭姓，但是根據(jù)a2分組。避免：查詢那些列就根據(jù)那個(gè)group by烘挫，本來游標(biāo)了窟扑，但是還需要另外一張表查找
3 using index
會對possiblekey和key有影響墙懂，如果沒有where，索引只出現(xiàn)在key中扮念，否則possiblekey和key里面都有损搬。
出現(xiàn)了using index表示性能提升了，索引覆蓋。
原因：不讀取源文件巧勤，只從索引文件中獲取數(shù)據(jù)
4 using where
需要回表查詢
索引里面沒有的，需要回到原表查詢
select age ,name from .. where age=...
此語句必須要回到表格查詢name颅悉，所以用where
5 possible where
永遠(yuǎn)不可能的
... where a1='x' and a2='y'
