Mysql优化之基础回顾篇
mysql查詢指令執(zhí)行順序
where->group-by>having->order by->limit
更新時(shí)間2016年7月24日 20:47:28
階段一
進(jìn)入數(shù)據(jù)庫???? mysql –h服務(wù)器名稱–u用戶名稱 –p密碼;
? ? ? ? ?[示例]mysql -hlocalhost -uroot -p123456;
? ? ?? ? ? ? ?顯示數(shù)據(jù)庫????
? ? ? ? ?? ? ? ? ?[示例]show databases;
? ? ? ?? ? ? ? ? ?->進(jìn)入某個(gè)數(shù)據(jù)庫??? use? 數(shù)據(jù)庫名;
? ? ?? ? ? ? ?? ?[示例]use shop;
? ? ? ? ??? ? ? ? ? ? ? ? ?->查看數(shù)據(jù)庫里面的表單列表
? ? ? ? ?? ? ?? ? ? ? ?? ?[示例]show tables;
列類型的概念
數(shù)值型
? ??整型 tinyint ? ? smallint ? ?mediumint ? ?int ? ? bigint
? ? unsigned?
? ? ? ? ? 無符號(hào)，因?yàn)橛?jì)算機(jī)記錄數(shù)字是以補(bǔ)碼(詳見《計(jì)算機(jī)組成原理》)的形式保存的
? ??所以當(dāng)聲明了無符號(hào)的時(shí)候，該數(shù)字記錄的范圍可以多一位了
? ? 比如,聲明一個(gè)tinyint類型，它可記錄一個(gè)字節(jié),即，可以記錄八位二進(jìn)制數(shù)據(jù)
? ? 在未聲明無符號(hào)的時(shí)候，它會(huì)使用第一位作為符號(hào)，范圍就在-128~127之間
? ??聲明無符號(hào)位的時(shí)候，的范圍就變?yōu)榱?~255
? ? zerofill?
? ? ? ? ?零填充,M寬度
? ? 浮點(diǎn)型/定點(diǎn)型
? ? ? ? ?M為總位數(shù),d代表小數(shù)位個(gè)數(shù)
? ? ? ? ?float(M,d)與decimal(M,d)
? ? ? ? ?無符號(hào)的時(shí)候decimal比float更精確，常用商城價(jià)格
字符型
? ? char(M)與varchar(M)的區(qū)別
? ? ? ? ?char實(shí)際占了M個(gè)字節(jié)(定長(zhǎng)),存儲(chǔ)的不夠M則向右側(cè)補(bǔ)空格,取出時(shí)取出右側(cè)空格，限制 M<=255
? ? ? ? ? varchar有1~2個(gè)字節(jié)來標(biāo)記真實(shí)的長(zhǎng)度(變長(zhǎng)),限制 M<=65535
日期
? ? 常用TIMESTAMP current_timestamp直接記錄時(shí)間
? ? 在開發(fā)中常用int類型來時(shí)間戳秒數(shù)，方便計(jì)算，顯示時(shí)間的時(shí)候也方便格式化為不同的顯示樣式
階段二
where
//取出account_log>4的數(shù)據(jù)?四個(gè)運(yùn)算符? > = < !=
SELECT??* ?FROM?account_log?where?log_id?>4;
//取出4到8直接的數(shù)據(jù)
SELECT??* ?FROM?account_log?where?log_id?between?4?and?8;
（注意這里可以用??wherelog_id>=4 and log_id <=8來同樣表達(dá)）
//取出4和8中的數(shù)據(jù)
SELECT??* ?FROM?account_log?where?log_id?in (4,8);
//邏輯詞匯三個(gè) not and or，下面以not的用法為例子
SELECT??* ?FROM?account_log?where?log_id not?in (4,8);
group by
通常與統(tǒng)計(jì)函數(shù)avg(),sum(),count(),min(),max()一起使用
【注意】當(dāng)使用group的時(shí)候，count(*)函數(shù)得出的數(shù)是指的每個(gè)分組內(nèi)的成員個(gè)數(shù)，
比如select count(*) as times from table1 group by id，
比如? id=1的個(gè)數(shù)就是times的值
把每一列都當(dāng)作一個(gè)變量看,運(yùn)算結(jié)果通過as作為別名輸出
//查出一個(gè)用戶所購(gòu)買的所有東西的總價(jià)格
SELECT??* sum( money * num) as total?FROM?account_log?group by user_id;
where與having的區(qū)別
where查表中的原始數(shù)據(jù)有作用，having對(duì)表中沒有的但對(duì)查詢邏輯中有結(jié)果有效
//查詢總成績(jī)大于380的學(xué)生名字,與其對(duì)應(yīng)的總分
SELECT name, (Chinese+Math+English)as score
FROM student
HAVING score>380
ORDER BYscore DESC;
//查詢用戶中，消費(fèi)超過1000元的人中消費(fèi)最低的兩個(gè)人
SELECTuser_od,sum(user_money) as total
FROMaccount_log
Group BYuser_id
HAVINGtotal>1000
ORDER BY user_idASC
LIMIT 2;
?
//計(jì)算掛科兩門及以上的學(xué)生的平均分,從高到低每頁展示20個(gè)人的信息 column,name,score
SELECT avg(score) as avg_score,sum(score<60) as times
FROMstudent_score
GROUP BY name
HAVINGtimes>=2
ORDER BYavg_score DESC
LIMIT 20;
?
【注意】count()里面只取的是行數(shù)，給什么條件都沒用，
sum()?如果里面是判斷類型，結(jié)果就為真值的總個(gè)數(shù)；
如果是普通運(yùn)算，結(jié)果就為運(yùn)算的結(jié)果
?
order by
它可以多字段排序 orderby 字段1,字段2 asc
[示例]order by cat_idasc,shop_price desc;
//通過goods表建立臨時(shí)表g2并把goods表中的數(shù)據(jù)傳給g2,傳入前，
//①將goods表的數(shù)據(jù)通過cat_id與shop_price排序（但在平時(shí)的操作中，
//②我們不用臨時(shí)表，我們用子查詢(查詢結(jié)果當(dāng)作表)）
CREATE TABLE g2 LIKE goods;
INSERT INTO g2 SELECT * FROM goods ORDER BY cat_id asc,shop_price desc;
?
Where ?From ?Exists子查詢
?>Where是把內(nèi)部的查詢結(jié)果，給外部查詢使用
? ? ??[常用場(chǎng)景=>查詢最大商品、查詢最貴商品][最新,即自增id為最大值的時(shí)候]
SELECT name?FROM goods?
WHERE name in(
? ??? ??SELECT name?
? ??? ??FROM goods?
? ??? ??ORDER BY user_id
);
>From查詢結(jié)果當(dāng)成一個(gè)臨時(shí)表(表必須取一個(gè)別名)，再讓外部進(jìn)行一次sql查詢使用
????? [常用場(chǎng)景=>查詢每個(gè)欄目下的最新/最貴商品]
SELECT name?FROM(
?? ??? ??SELECT name?
? ??? ??FROM goods?
? ??? ??ORDER BY user_id
) as temp_table;
>Exists 把外層的查詢結(jié)果拿到內(nèi)層,看內(nèi)部的查詢結(jié)果是否成立
????? [常用場(chǎng)景=>查詢有商品的欄目]
SELECT?*?FROM?comment?
WHERE?EXISTS(?
? ??? ??SELECT?*?FROM?comment_inner?
? ??? ??WHERE?comment_inner.article_id=comment.article_id?
);
//計(jì)算掛科兩門及以上的學(xué)生的平均分 column,name,score
思路：分三次。第一次查詢
①??? 篩選符合條件的人=>這時(shí)候會(huì)有一個(gè)人的名字多次出現(xiàn)的情形【WHERE子查詢】
SELECT name,count(*) as result
FROM stu
WHERE score <60
GROUP BY name having result>1;
②??? 合并重復(fù)出現(xiàn)的名字【普通查詢】
SELECT name
FROM temp
GROUP BY name;
③??? 計(jì)算這些人的平均成績(jī)【FROM子查詢】
SELECT name,avg(score) as avg_score
FROM stu where name in②中的結(jié)果;
最終拼寫出語句
SELECT name,avg(score) as avg_score
FROM stu where name in
? ? (SELECT name
? ??FROM (
? ??? ??SELECT name,count(*) as result
? ??? ??FROM stu
?? ?? ??WHERE score <60
? ?? ???GROUP BY name having result>1
? ?? ???)as temp
GROUP BY name
);
更新時(shí)間2016年7月26日23:26:48
階段三
union
>把2次或者多次查詢結(jié)果合成一張表，要求其列數(shù)要一致
可以來源于多張表，可以進(jìn)行多次sql語句查詢，取出的列名可以不一致，此時(shí)以第一個(gè)sql的列名為準(zhǔn)
?
1)如果不同的語句中取出的行，有完全相同(每個(gè)列的值都相同),
那么相同的行將會(huì)合并(去重復(fù))
SELECTS * FROM ta
UNION
SELECT * FROM tb;
2)如果不去掉重復(fù)項(xiàng)，可以通過加all來搞定
SELECTS * FROM ta
UNION ALL
SELECT * FROM tb;
3)如果語句中有oder by,limit(在子句中order by配合limit才有意義，否則語法分析器，會(huì)在分析語法的時(shí)候，把order by去掉)
則需要將每個(gè)查詢子句都包起來(但平日多用在總的結(jié)果后 排序)
//取第三個(gè)欄目前三高、第四個(gè)欄目前兩高的商品，用union實(shí)現(xiàn)
(SELECT goods_id,cat_id,goods_name,shop_price
FROM goods
WHERE cat_id = 3
ORDER BY?shop_price desc
LIMIT 3)
UNION
(SELECT goods_id,cat_id,goods_name,shop_price
FROM goods
WHERE cat_id =4
ORDER BY shop_price desc
LIMIT 2);
join
1) 左連接【這里以tb為基準(zhǔn) [意指它每項(xiàng)得應(yīng)完全對(duì)齊] ：交集部分先拿出來。另外，若自己有沒對(duì)齊的行，則把這些行的數(shù)據(jù)也拿出來。因?yàn)闆]對(duì)齊，所以這幾行只有自身的數(shù)據(jù)，其他數(shù)據(jù)為NULL】
SELECT tb.level,tb.assignment,ta.name
FROM tb
LEFT JOIN ta
ON ta.level=tb.level
2) 右連接【這里以ta為基準(zhǔn)，邏輯過程與左聯(lián)接同理】
SELECT tb.level,tb.assignment,ta.name
FROM tb
RIGHT JOIN ta
ON ta.level=tb.level
3)內(nèi)連接【查詢左右表都有的數(shù)據(jù)，即左右鏈接的交集】
SELECT tb.level,tb.assignment,ta.name
FROM tb
INNER JOIN ta
ON ta.level=tb.level
//查出左右連接的并集，可以用UNION?
//查出下面2016-08-08之后的所有比賽，并以形如下面的方式輸出：
? ??? ??? ??? ??重慶力帆 ?0:0?上海申花 2016-08-09
SELECT a.matchResult,a.matchTime ,b.teamName as hName,c.teamName as gName
FROM `match` as a
LEFT JOIN `team` as b
On b.teamID=a.hostTeamID
LEFT JOIN `team` as c
On c.teamID=a.guestTeamID
WHERE a.matchTime>'2016-08-08'
更新時(shí)間2016年7月28日 10:27:44
view
? >視圖是由查詢結(jié)果形成的一張?zhí)摂M表
視圖的用處
>簡(jiǎn)化查詢
>把表的權(quán)限封閉，但開放相應(yīng)的視圖權(quán)限，視圖里只開放部分?jǐn)?shù)據(jù)
>大數(shù)據(jù)分表可以用到.
視圖的創(chuàng)建
create view 視圖名 as select語句
視圖的刪除
drop view 視圖名
視圖的修改
Alter view as select xxx;
視圖與表的關(guān)系
視圖是表的查詢結(jié)果,自然表的數(shù)據(jù)改變了，會(huì)影響視圖的結(jié)果
視圖改變了呢？
1:視圖增刪改也會(huì)影響表的嗎
? ? >視圖的數(shù)據(jù)與表的數(shù)據(jù) 一一對(duì)應(yīng)時(shí)，可以修改
2:視圖總能增刪改嗎
? ? >對(duì)于視圖的insert還應(yīng)該注意，視圖必須包含表中沒有默認(rèn)值的列
大數(shù)據(jù)分表查詢
當(dāng)表超過200萬行的時(shí)候，查詢速度就會(huì)變慢，這時(shí)候可以通過分表查詢的方法
? ? [示例] 現(xiàn)在我把表通過 模4 的方法，把表分成4張視圖
CREATE view g1 as select * from goods where goods_id % 4=0;
CREATE view g2 as select * from goods where goods_id % 4=1;
CREATE view g3 as select * from goods where goods_id % 4=2;
CREATE view g4 as select * from goods where goods_id % 4=3
以php查詢?yōu)槔?/span>
$tableFlag=$_GET['id']%4;
$tablename="g".$tableFlag;
$result=$pdo->("select * from $tablename");
此外，我們可以把多張表通過union合成一張新的視圖
CREATE newTable as select * from t1 unionselect * from t2 ....;
視圖的Algorithm
algorithm = merge / Temptable?/ undefined
Merge當(dāng)引用視圖的時(shí)候，視圖語句與定義視圖的語句合并只是為了形成一條select語句
Temptable 當(dāng)引用視圖時(shí)，根據(jù)視圖的創(chuàng)建語句建立一個(gè)臨時(shí)表多用于做子查詢的結(jié)果，出現(xiàn)非常頻繁的時(shí)候
Undefined未定義，讓系統(tǒng)幫你選
而temptable是根據(jù)創(chuàng)建語句瞬間創(chuàng)建一張臨時(shí)表,
然后查詢視圖的語句從該臨時(shí)表查數(shù)據(jù)
create algorithm=Temptable?view g2 as?
select goods_id,cat_id,goods_name,shop_price from goods order by cat_idasc,shop_price desc;
查詢語句為
select * from g2 group by cat_id; //最后執(zhí)行的這個(gè)查詢語句
然后取出結(jié)果，并放在臨時(shí)表上
字符集與校對(duì)集
字符集
>數(shù)據(jù)庫默認(rèn)字符集>表默認(rèn)字符集
>列字符集
>如果某一個(gè)級(jí)別沒有指定字符集，則繼承上一級(jí)
>最上一級(jí)是服務(wù)器，它是一定有字符集定義的(因?yàn)橐?fù)責(zé)頁面的輸出)，
1.告訴服務(wù)器，我給你發(fā)送的數(shù)據(jù)是什么編碼 Character_set_client
? ? ? ? ?[示例]set character_set_client=utf8;
2.告訴轉(zhuǎn)換器，轉(zhuǎn)換成什么編碼？Character_set_connection
? ? ? ? ?[示例]set character_set_connection=utf8;
3.查詢的結(jié)果是什么編碼？Character_set_results
? ? ? ? ?[示例]set character_set_results=utf8;
若三者統(tǒng)一編碼，則可寫為set names 字符集名
? ? ? ? ?[示例]set names utf8;
校對(duì)集
>值字符集的排序規(guī)則[一個(gè)字符集可以有多種排序規(guī)則，如圖所示]
? ??>常用字符集為utf8_general_ci
聲明校對(duì)規(guī)則
? ? ? ? ?[示例]create table(xxxxx) Charset utf8 collate?utf8_general_ci;
更新時(shí)間2016年7月29日 01:55:46
階段四
觸發(fā)器
四要素
監(jiān)視地點(diǎn) 監(jiān)視事件 觸發(fā)事件 觸發(fā)事件
MySQL包含對(duì)觸發(fā)器的支持。觸發(fā)器是一種與表操作有關(guān)的數(shù)據(jù)庫對(duì)象，當(dāng)觸發(fā)器所在表上出現(xiàn)指定事件時(shí)，將調(diào)用該對(duì)象，即表的操作事件觸發(fā)表上的觸發(fā)器的執(zhí)行。
創(chuàng)建觸發(fā)器
在MySQL中，創(chuàng)建觸發(fā)器語法如下：
CREATE TRIGGER trigger_name
trigger_time trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
其中：
trigger_name：標(biāo)識(shí)觸發(fā)器名稱，用戶自行指定；
trigger_time：標(biāo)識(shí)觸發(fā)時(shí)機(jī)，取值為 BEFORE 或 AFTER；
trigger_event：標(biāo)識(shí)觸發(fā)事件，取值為 INSERT、UPDATE 或 DELETE；
tbl_name：標(biāo)識(shí)建立觸發(fā)器的表名，即在哪張表上建立觸發(fā)器；
trigger_stmt：觸發(fā)器程序體，可以是一句SQL語句，或者用 BEGIN 和 END 包含的多條語句。
由此可見，可以建立6種觸發(fā)器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。
另外有一個(gè)限制是不能同時(shí)在一個(gè)表上建立2個(gè)相同類型的觸發(fā)器，因此在一個(gè)表上最多建立6個(gè)觸發(fā)器。
trigger_event 詳解
MySQL 除了對(duì) INSERT、UPDATE、DELETE 基本操作進(jìn)行定義外，還定義了 LOAD DATA 和 REPLACE 語句，這兩種語句也能引起上述6中類型的觸發(fā)器的觸發(fā)。
LOAD DATA 語句用于將一個(gè)文件裝入到一個(gè)數(shù)據(jù)表中，相當(dāng)與一系列的 INSERT 操作。
REPLACE 語句一般來說和 INSERT 語句很像，只是在表中有 primary key 或 unique 索引時(shí)，如果插入的數(shù)據(jù)和原來 primary key 或 unique 索引一致時(shí)，會(huì)先刪除原來的數(shù)據(jù)，然后增加一條新數(shù)據(jù)，也就是說，一條 REPLACE 語句有時(shí)候等價(jià)于一條。
INSERT 語句，有時(shí)候等價(jià)于一條 DELETE 語句加上一條 INSERT 語句。
INSERT 型觸發(fā)器：插入某一行時(shí)激活觸發(fā)器，可能通過 INSERT、LOAD DATA、REPLACE 語句觸發(fā)；
UPDATE 型觸發(fā)器：更改某一行時(shí)激活觸發(fā)器，可能通過 UPDATE 語句觸發(fā)；
DELETE 型觸發(fā)器：刪除某一行時(shí)激活觸發(fā)器，可能通過 DELETE、REPLACE 語句觸發(fā)。
BEGIN … END 詳解
在MySQL中，BEGIN … END 語句的語法為：
BEGIN
[statement_list]
END
其中，statement_list代表一個(gè)或多個(gè)語句的列表，列表內(nèi)的每條語句都必須用分號(hào)（;）來結(jié)尾。
而在MySQL中，分號(hào)是語句結(jié)束的標(biāo)識(shí)符，遇到分號(hào)表示該段語句已經(jīng)結(jié)束，MySQL可以開始執(zhí)行了。因此，解釋器遇到statement_list 中的分號(hào)后就開始執(zhí)行，然后會(huì)報(bào)出錯(cuò)誤，因?yàn)闆]有找到和 BEGIN 匹配的 END。
這時(shí)就會(huì)用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思），它是一條命令，不需要語句結(jié)束標(biāo)識(shí)，語法為：
DELIMITER new_delemiter
new_delemiter 可以設(shè)為1個(gè)或多個(gè)長(zhǎng)度的符號(hào)，默認(rèn)的是分號(hào)（;），我們可以把它修改為其他符號(hào)，如$：
DELIMITER $
在這之后的語句，以分號(hào)結(jié)束，解釋器不會(huì)有什么反應(yīng)，只有遇到了$，才認(rèn)為是語句結(jié)束。注意，使用完之后，我們還應(yīng)該記得把它給修改回來。
一個(gè)完整的創(chuàng)建觸發(fā)器示例
假設(shè)系統(tǒng)中有兩個(gè)表：
班級(jí)表 class(班級(jí)號(hào) classID, 班內(nèi)學(xué)生數(shù) stuCount)
學(xué)生表 student(學(xué)號(hào) stuID, 所屬班級(jí)號(hào) classID)
要?jiǎng)?chuàng)建觸發(fā)器來使班級(jí)表中的班內(nèi)學(xué)生數(shù)隨著學(xué)生的添加自動(dòng)更新，代碼如下：
DELIMITER $
create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;
end$
DELIMITER ;
變量詳解
MySQL 中使用 DECLARE 來定義一局部變量，該變量只能在 BEGIN … END 復(fù)合語句中使用，并且應(yīng)該定義在復(fù)合語句的開頭，
即其它語句之前，語法如下：
DECLARE var_name[,...] type [DEFAULT value]
其中：
var_name 為變量名稱，同 SQL 語句一樣，變量名不區(qū)分大小寫；type 為 MySQL 支持的任何數(shù)據(jù)類型；可以同時(shí)定義多個(gè)同類型的變量，用逗號(hào)隔開；變量初始值為 NULL，如果需要，可以使用 DEFAULT 子句提供默認(rèn)值，值可以被指定為一個(gè)表達(dá)式。
對(duì)變量賦值采用 SET 語句，語法為：
SET var_name = expr [,var_name = expr] ...
Before 與 After的區(qū)別
After ? ??是先完成數(shù)據(jù)的增刪改，再觸發(fā)，觸發(fā)的語句晚于監(jiān)視的增刪改，無法影響前面的增刪改動(dòng)作
Before 是先完成觸發(fā)，再增刪改
修改一次觸發(fā)器，就得把之前的觸發(fā)器給刪了，不然原來的觸發(fā)器還會(huì)繼續(xù)生效
delimiter $
create trigger t1
before insert on indent
for each row
begin
if new. buy_num > 5 then
??? set new.buy_num= 5;
end if;
update indent_details set num = num – buy_num where id =new.indent_id;
end$
delimiter ;
NEW 與 OLD 詳解
上述示例中使用了NEW關(guān)鍵字，和 MS SQL Server 中的 INSERTED 和 DELETED 類似，MySQL 中定義了 NEW 和 OLD，用來表示
觸發(fā)器的所在表中，觸發(fā)了觸發(fā)器的那一行數(shù)據(jù)。
具體地：
在 INSERT 型觸發(fā)器中，NEW 用來表示將要（BEFORE）或已經(jīng)（AFTER）插入的新數(shù)據(jù)；
在 UPDATE 型觸發(fā)器中，OLD 用來表示將要或已經(jīng)被修改的原數(shù)據(jù)，NEW 用來表示將要或已經(jīng)修改為的新數(shù)據(jù)；
在 DELETE 型觸發(fā)器中，OLD 用來表示將要或已經(jīng)被刪除的原數(shù)據(jù)；
使用方法：NEW.columnName （columnName 為相應(yīng)數(shù)據(jù)表某一列名）
另外，OLD 是只讀的，而 NEW 則可以在觸發(fā)器中使用 SET 賦值，這樣不會(huì)再次觸發(fā)觸發(fā)器，造成循環(huán)調(diào)用（如每插入一個(gè)學(xué)生前，都在其學(xué)號(hào)前加“2013”）。
查看觸發(fā)器
和查看數(shù)據(jù)庫（show databases;）查看表格（show tables;）一樣，查看觸發(fā)器的語法如下：
SHOW TRIGGERS [FROM schema_name];
其中，schema_name即 Schema 的名稱，在 MySQL 中 Schema 和 Database 是一樣的，也就是說，可以指定數(shù)據(jù)庫名，這樣就
不必先“USE database_name;”了。
刪除觸發(fā)器
和刪除數(shù)據(jù)庫、刪除表格一樣，刪除觸發(fā)器的語法如下：
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
觸發(fā)器的執(zhí)行順序
我們建立的數(shù)據(jù)庫一般都是 InnoDB 數(shù)據(jù)庫，其上建立的表是事務(wù)性表，也就是事務(wù)安全的。這時(shí)，若SQL語句或觸發(fā)器執(zhí)行失敗，MySQL 會(huì)回滾事務(wù)，有：
①如果 BEFORE 觸發(fā)器執(zhí)行失敗，SQL 無法正確執(zhí)行。
②SQL 執(zhí)行失敗時(shí)，AFTER 型觸發(fā)器不會(huì)觸發(fā)。
③AFTER類型的觸發(fā)器執(zhí)行失敗，SQL 會(huì)回滾。
?
預(yù)計(jì)更新時(shí)間2016年7月29日晚
事務(wù)
>對(duì)一組操作，要么全部執(zhí)行，要么全部取消，只要沒完成此次事務(wù)，其結(jié)果不會(huì)被看到ACID特性
原子性：數(shù)據(jù)庫事務(wù)不可再分的原則一致性：要么一起執(zhí)行，要么一起取消
隔離型：每個(gè)事務(wù)對(duì)其他事務(wù)是不可見的
持久性：當(dāng)事務(wù)完成后，其影響會(huì)被保留，不能撤銷
用法
start transaction;
sql語句1;
sql語句2;
...
若成功，則提交commit;
若失敗，則回滾rollback;
? ? ?[示例] ?HLZ給Leo轉(zhuǎn)賬,并成功start transaction;
update bank ?set ?money= money -100 where name='HLZ';
update bank ?set ?money= money+100 ?where name='Leo';
commit;
索引
——這部分是簡(jiǎn)單引入，下一篇為優(yōu)化的正文，我將進(jìn)一步談?wù)劯鞣N索引 索引是針對(duì)數(shù)據(jù)所建立的目錄 優(yōu)點(diǎn)：可以加快查詢速度 缺點(diǎn)：降低了增刪改的速度 原理：類似新華字典，這里就以新華字典為例把。 ? ? ? ? ? ?新華字典根據(jù) 拼音或者筆畫 可以搜索到對(duì)應(yīng)的字的位置 ? ? ? ? ? ?在對(duì)應(yīng)字的位置也有對(duì)應(yīng)的拼音與筆畫 ? ? ? 引入索引之前我們先對(duì)比一下MyIsam與InnoDB存儲(chǔ)引擎的各自特點(diǎn)， ? ? ? ?因?yàn)橐粫?huì)兒我們會(huì)用到各自的優(yōu)點(diǎn)，如圖所示現(xiàn)在我們來稍稍談?wù)剛€(gè)別索引的內(nèi)部算法 ? (為方便書寫對(duì)數(shù)表達(dá)式，我在這里用 ?lb ?表示以2為底的對(duì)數(shù)) 設(shè)有N條隨機(jī)記錄，如果不用索引，平均要用查找 ? N/2? ? 次。 由《數(shù)據(jù)結(jié)構(gòu)》可知
如果用btree(二叉樹)索引 ? ? ? ? ??lb(N) ? ?次
如果用hash(哈希)散列索引 ? ? ? 1?? ? ? ?次
一般有索引的數(shù)據(jù)都比較大，所以，換服務(wù)器的時(shí)候，記得先把索引去掉，導(dǎo)入數(shù)據(jù)庫后再統(tǒng)一設(shè)置索引
索引類型 普通索引：index 只是加快了查詢速度 唯一索引：unique index 行上的值不能重復(fù)，一個(gè)表中，可以有多個(gè) 主鍵索引：primary key 不能重復(fù)，這具有唯一索引的功能，但主鍵索引一個(gè)表中只能有一個(gè) 全文索引：fulltext index 不能重復(fù)
這里引入模糊查詢與索引的關(guān)系(模糊查詢有點(diǎn)像正則表達(dá)式查詢，具體是怎樣的，本篇文章就不講了，請(qǐng)自行百度) 模糊查詢的時(shí)候 "%輸入的內(nèi)容%"不能使用索引, ? "輸入的內(nèi)容%"可以用到索引 索引創(chuàng)建原則 1.不要過度索引 2.在where條件最頻繁的列上加 3.盡量索引散列值，過于集中的值索引意義不大
查看一張表上的所有索引
Show index from 表名
建立索引
Alter table 表名 add index / unique / fulltext 索引名 (列名) ? ? [示例] alter table indent add unique ?name (name) Alter table 表名 add primary key (列名) ? ? ##這里不加索引名是因?yàn)橹麈I只有一個(gè) 刪除索引 Alter table 表名 drop index 索引名 Alter table 表名 drop primary key
全文索引 >用法 ? ?Match(全文索引名) against ('輸入的內(nèi)容'); >停止詞 ? ?全文索引不針對(duì)非常頻繁的詞做索引,例如this,is,you,my等等 >全文索引與中文搜索 ? ?全文索引在默認(rèn)情況下，有中文無法做到索引，因?yàn)橛⑽挠锌崭窕蛘邩?biāo)點(diǎn)符號(hào)來拆成單詞，進(jìn)而對(duì)單詞進(jìn)行索引整理 但是對(duì)中文語句，進(jìn)行分詞太難了，mysql無法對(duì)中文語句進(jìn)行分詞，如果非得實(shí)現(xiàn)，得用中文詞庫處理。(用sphinx處理，以后的文章我會(huì)說說怎么用它)
什么情況下使用索引
表的主關(guān)鍵字 表的字段唯一約束 直接條件查詢的字段 查詢中與其它表關(guān)聯(lián)的字段 查詢中排序的字段 查詢中統(tǒng)計(jì)或分組統(tǒng)計(jì)的字段
什么情況下應(yīng)不建或少建索引
表記錄太少
經(jīng)常插入、刪除、修改的表
存儲(chǔ)過程
>類似函數(shù)，進(jìn)行封裝、調(diào)用。(相當(dāng)于sql編程) >要點(diǎn)：封裝sql、參數(shù)、控制結(jié)構(gòu)、循環(huán)查看現(xiàn)有的存儲(chǔ)過程 Show procedure status
刪除存儲(chǔ)過程 Drop procedure status
調(diào)用存儲(chǔ)過程 Call 存儲(chǔ)過程的名字();
mysql里面只能用set來賦值 ? ? [示例] delimiter $ create procedure test1(n smallint) begin ? ? declare i int; ? ? declare s int; ? ? set i ?= 1; ? ? set s = 0; ? ? while i <= n do ? ? ? ? set s= s + i; ? ? ? ? set i = i ?+ 1;
? ?end while; ? ?select s; end$ delimiter ;
其中 ??select s; ? ?是為顯示計(jì)算結(jié)果
與函數(shù)的區(qū)別 ①定義時(shí)候的方法 若是定義函數(shù) create function xxx(); ②存儲(chǔ)過程沒有返回值，但是函數(shù)可以有
——下篇將正式開始講述優(yōu)化方面的東西
總結(jié)
以上是生活随笔為你收集整理的Mysql优化之基础回顾篇的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 心得体悟帖---18、时间
- 下一篇： CURL模拟登录