數(shù)據(jù)庫(kù)稼稿，后端開(kāi)發(fā)者必學(xué)册招，而且現(xiàn)在以MySQL居多抖单。這個(gè)系列將羅列自己以前的筆記以及深入MySQL一些高級(jí)用法萎攒。打算先講很多人關(guān)注的使用方式（增刪改查以及其優(yōu)化）遇八，然后就講數(shù)據(jù)庫(kù)和表的操作（很多我們學(xué)習(xí)忽略的地方），接著就是引擎還有更高級(jí)的查詢等等耍休。OK刃永，不多說(shuō)，Let's go!
本系列會(huì)逐步更新羊精，從基本知識(shí)總結(jié)斯够，到sql優(yōu)化，索引設(shè)計(jì)優(yōu)化喧锦，存儲(chǔ)過(guò)程使用優(yōu)化......并且到后面會(huì)給出一些方案設(shè)計(jì)读规，比如集群，主從......
敬請(qǐng)期待Ｈ忌佟束亏！
本人能力有限，有寫(xiě)得不對(duì)的阵具，麻煩在下面指出碍遍，十分感謝！
本文章結(jié)構(gòu)：（一）操作數(shù)據(jù)操作語(yǔ)句優(yōu)化認(rèn)識(shí)祝沸；（二）INSERT語(yǔ)句矮烹；（三）DELETE語(yǔ)句；（四）UPDATE語(yǔ)句罩锐；（五）REPLACE語(yǔ)句奉狈；（六）庫(kù)與表基本操作
一、操作數(shù)據(jù)語(yǔ)句優(yōu)化認(rèn)識(shí)：
通常情況下涩惑，當(dāng)訪問(wèn)某張表的時(shí)候仁期，讀取者首先必須獲取該表的鎖，如果有寫(xiě)入操作到達(dá)竭恬，那么寫(xiě)入者一直等待讀取者完成操作（查詢開(kāi)始之后就不能中斷跛蛋，因此允許讀取者完成操作）。當(dāng)讀取者完成對(duì)表的操作的時(shí)候痊硕，鎖就會(huì)被解除赊级。如果寫(xiě)入者正在等待的時(shí)候，另一個(gè)讀取操作到達(dá)了岔绸，該讀取操作也會(huì)被阻塞（block）理逊，因?yàn)槟J(rèn)的調(diào)度策略是寫(xiě)入者優(yōu)先于讀取者橡伞。當(dāng)?shù)谝粋€(gè)讀取者完成操作并解放鎖后，寫(xiě)入者開(kāi)始操作晋被，并且直到該寫(xiě)入者完成操作兑徘，第二個(gè)讀取者才開(kāi)始操作。
通過(guò)LOCK TABLES和UNLOCK TABLES語(yǔ)句可以顯式地獲取或釋放鎖羡洛，但是在通常情況下挂脑，服務(wù)器的鎖管理器會(huì)自動(dòng)地在需要的時(shí)候獲取鎖，在不再需要的時(shí)候釋放鎖翘县。獲取的鎖的類型依賴于客戶端是寫(xiě)入還是讀取操作最域。
對(duì)某張表進(jìn)行寫(xiě)入操作的客戶端必須擁有獨(dú)占的（排他的）訪問(wèn)權(quán)的鎖。操作在進(jìn)行的過(guò)程中锈麸，該數(shù)據(jù)表處于不一致的（inconsistent）狀態(tài)镀脂，因?yàn)閿?shù)據(jù)記錄在刪除、添加或修改的時(shí)候忘伞，數(shù)據(jù)表上的索引也可能需要更新以相互匹配薄翅。允許兩個(gè)客戶端同時(shí)寫(xiě)入一張數(shù)據(jù)表是不利的，因?yàn)檫@樣的操作會(huì)很快使數(shù)據(jù)表中的信息成為一堆無(wú)用的垃圾氓奈。同時(shí)允許客戶端讀取變化之中的數(shù)據(jù)表也不正確翘魄，因?yàn)檎谧x取的位置中的數(shù)據(jù)可能正在變化（修改），讀取的結(jié)果可能并不是真實(shí)的舀奶。因此對(duì)某張表執(zhí)行讀取操作的客戶端也必須獲取一個(gè)鎖暑竟，防止在讀取的過(guò)程中，其它的客戶端寫(xiě)入或改變表育勺。但是這個(gè)鎖不需要獨(dú)占的訪問(wèn)權(quán)但荤。因?yàn)樽x取操作不會(huì)改變數(shù)據(jù)，因此沒(méi)有理由讓某個(gè)讀取者阻止其它的讀取者訪問(wèn)這張表涧至。故讀取鎖可允許其它的客戶端在同一時(shí)刻讀取這張表腹躁。
雖然通過(guò)鎖機(jī)制，可以實(shí)現(xiàn)多線程同時(shí)對(duì)某個(gè)表進(jìn)行操作南蓬，但當(dāng)某個(gè)線程作更新操作時(shí)纺非，首先要獲得獨(dú)占的訪問(wèn)權(quán)。在更新的過(guò)程中赘方，所有其它想要訪問(wèn)這個(gè)表的線程必須要等到其更新完成為止烧颖。此時(shí)就會(huì)導(dǎo)致鎖競(jìng)爭(zhēng)的問(wèn)題，從而導(dǎo)致用戶等待時(shí)間的延長(zhǎng)窄陡。
因此：要提高M(jìn)ySQL的更新/插入效率倒信，應(yīng)首先考慮降低鎖的競(jìng)爭(zhēng)黄橘，減少寫(xiě)操作的等待時(shí)間绍昂。 （本系列在后面才討論表設(shè)計(jì)的優(yōu)化）
使用前說(shuō)明：偷點(diǎn)懶蛉腌，用之前寫(xiě)出來(lái)的數(shù)據(jù)庫(kù)-INNODB存儲(chǔ)引擎溶锭。（請(qǐng)導(dǎo)入數(shù)據(jù)庫(kù)文件）。數(shù)據(jù)庫(kù)說(shuō)明乘综。數(shù)據(jù)庫(kù)文件憎账。
CREATE TABLE `score` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`change_type` VARCHAR(255) NULL DEFAULT NULL,
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`score` INT(11) NULL DEFAULT NULL,
`user_id` BIGINT(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci';
//外鍵在以后再聊
CREATE TABLE `gag` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`gag_time` DATE NULL DEFAULT NULL,
`user_id` BIGINT(20) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci';
二、INSERT語(yǔ)句：
INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), ...]
注意：
如果要插入的值列表包含所有字段并且順序一致卡辰，則可以省略字段列表胞皱。
可同時(shí)插入多條數(shù)據(jù)記錄！
REPLACE 與 INSERT 完全一樣九妈，可互換反砌。
例子：
INSERT INTO score (change_type,score,user_id) VALUES ('吃飯',10,1);
優(yōu)化：
（1）當(dāng)我們需要批量插入數(shù)據(jù)的時(shí)候，這樣的語(yǔ)句卻會(huì)出現(xiàn)性能問(wèn)題萌朱。例如說(shuō)宴树，如果有需要插入100000條數(shù)據(jù)，那么就需要有100000條insert語(yǔ)句晶疼，每一句都需要提交到關(guān)系引擎那里去解析酒贬，優(yōu)化，然后才能夠到達(dá)存儲(chǔ)引擎做真的插入工作翠霍。上述所說(shuō)的同時(shí)插入多條就是一種優(yōu)化锭吨。（經(jīng)測(cè)試，大概10條同時(shí)插入是最高效的）
例子：
INSERT INTO score (change_type,score,user_id) VALUES ('吃飯',10,1),('喝茶',10,1),('喝茶',10,1);
（2）將進(jìn)程/線程數(shù)控制在2倍于CPU數(shù)目相對(duì)合適
（3）采用順序主鍵策略（例如自增主鍵寒匙，或者修改業(yè)務(wù)邏輯零如，讓插入的記錄盡可能順序主鍵）
（4）考慮使用replace 語(yǔ)句代替insert語(yǔ)句。（REPLACE語(yǔ)句請(qǐng)參考下文锄弱，有詳細(xì)講述）
三考蕾、DELETE語(yǔ)句：
DELETE FROM 表名[ 刪除條件子句]
沒(méi)有條件子句，則會(huì)刪除全部
例子：
DELETE FROM gag; 刪除全部
DELETE FROM gag WHERE id=1;
補(bǔ)充：Mysql中truncate table和delete語(yǔ)句都可以刪除表里面所有數(shù)據(jù)棵癣，但是在一些情況下有些不同！
例子：
truncate table gag;
（1）truncate table速度要更快一些夺衍，但truncate刪除后不記錄mysql日志狈谊，不可以恢復(fù)數(shù)據(jù)。
（2）如果沒(méi)有外鍵關(guān)聯(lián)沟沙，innodb執(zhí)行truncate是先drop table(原始表),再創(chuàng)建一個(gè)跟原始表一樣空表,速度要遠(yuǎn)遠(yuǎn)快于delete逐條刪除行記錄河劝。
（3）如果使用innodb_file_per_table參數(shù)，truncate table 能重新利用釋放的硬盤(pán)空間,在InnoDB Plugin中矛紫，truncate table為自動(dòng)回收赎瞎，如果不是用InnoDB Plugin,那么需要使用optimize table來(lái)優(yōu)化表，釋放空間颊咬。
truncate table刪除表后务甥，optimize table尤其重要牡辽，特別是大數(shù)據(jù)數(shù)據(jù)庫(kù)，表空間可以得到釋放敞临！
（4）表有外鍵關(guān)聯(lián)态辛，truncate table刪除表數(shù)據(jù)為逐行刪除，如果外鍵指定級(jí)聯(lián)刪除(delete cascade)挺尿，關(guān)聯(lián)的子表也會(huì)會(huì)被刪除所有表數(shù)據(jù)奏黑。如果外鍵未指定級(jí)聯(lián)(cascde),truncate table逐行刪除數(shù)據(jù)，如果是父行關(guān)聯(lián)子表行數(shù)據(jù)编矾，將會(huì)報(bào)錯(cuò)熟史。
（5）auto_increment計(jì)數(shù)器在truncate table后會(huì)重置為0.與是否有外鍵關(guān)聯(lián)沒(méi)有關(guān)系。
注意：
一個(gè)大的 DELETE 或 INSERT 操作窄俏，要非常小心蹂匹，因?yàn)檫@兩個(gè)操作是會(huì)鎖表的，表一鎖住裆操，其他操作就進(jìn)不來(lái)了怒详。因此，我們要交給DBA去拆分踪区，重整數(shù)據(jù)庫(kù)策略昆烁，比如限制處理1000條。
MySQL官方手 冊(cè)得知?jiǎng)h除數(shù)據(jù)的速度和創(chuàng)建的索引數(shù)量是成正比的缎岗。所以在超大型數(shù)據(jù)庫(kù)中静尼，刪除時(shí)處理好索引關(guān)系非常重要。推薦的折中方法：在刪除數(shù)據(jù)之前刪除這那幾個(gè)索引传泊，然后刪除其中無(wú)用 數(shù)據(jù)鼠渺，刪除完成后重新創(chuàng)建索引。
四眷细、UPDATE語(yǔ)句：
UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新條件]
例子：
UPDATE score SET change_type='洗澡' WHERE id=2;
優(yōu)化：更新多條記錄（往后會(huì)結(jié)合MyBatics寫(xiě)個(gè)實(shí)例）
Update score
SET change_type = CASE id
WHEN 1 THEN 'value1'
WHEN 2 THEN 'value2'
WHEN 3 THEN 'value3'
END
WHERE id IN (1,2,3)
更新多條記錄的多個(gè)值
Update score
SET change_type = CASE id
WHEN 1 THEN 'value1'
WHEN 2 THEN 'value2'
WHEN 3 THEN 'value3'
END ,
score = CASE id
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
END
WHERE id IN (1,2,3)
(1). 盡量不要修改主鍵字段拦盹。
(2). 當(dāng)修改VARCHAR型字段時(shí)，盡量使用相同長(zhǎng)度內(nèi)容的值代替溪椎。
(3). 盡量最小化對(duì)于含有UPDATE觸發(fā)器的表的UPDATE操作普舆。
(4). 避免UPDATE將要復(fù)制到其他數(shù)據(jù)庫(kù)的列。
(5). 避免UPDATE建有很多索引的列校读。
(6). 避免UPDATE在WHERE子句條件中的列沼侣。
五、REPLACE語(yǔ)句：
根據(jù)應(yīng)用情況可以使用replace 語(yǔ)句代替insert/update語(yǔ)句歉秫。例如：如果一個(gè)表在一個(gè)字段上建立了唯一索引蛾洛，當(dāng)向這個(gè)表中使用已經(jīng)存在的鍵值插入一條記錄，將會(huì)拋出一個(gè)主鍵沖突的錯(cuò)誤雁芙。如果我們想用新記錄的值來(lái)覆蓋原來(lái)的記錄值時(shí)轧膘，就可以使用REPLACE語(yǔ)句钞螟。
使用REPLACE插入記錄時(shí)，如果記錄不重復(fù)（或往表里插新記錄）扶供，REPLACE功能與INSERT一樣筛圆，如果存在重復(fù)記錄，REPLACE就使用新記錄的值來(lái)替換原來(lái)的記錄值椿浓。使用REPLACE的最大好處就是可以將DELETE和INSERT合二為一太援，形成一個(gè)原子操作。這樣就可以不必考慮同時(shí)使用DELETE和INSERT時(shí)添加事務(wù)等復(fù)雜操作了扳碍。
在使用REPLACE時(shí)提岔，表中必須有唯一有一個(gè)PRIMARY KEY或UNIQUE索引，否則笋敞，使用一個(gè)REPLACE語(yǔ)句沒(méi)有意義碱蒙。
用法：
（1）同INSERT
//含義一：與普通INSERT一樣功能
REPLACE INTO score (change_type,score,user_id) VALUES ('吃飯',10,1),('喝茶',10,1),('喝茶',10,1);
//含義二：找到第一條記錄，用后面的值進(jìn)行替換
REPLACE INTO score (id,change_type,score,user_id) VALUES (1,'吃飯',10,1)
此語(yǔ)句的作用是向表table中插入3條記錄夯巷。如果主鍵id為1或2不存在就相當(dāng)于插入語(yǔ)句：
INSERTINTO score (change_type,score,user_id) VALUES ('吃飯',10,1),('喝茶',10,1),('喝茶',10,1);
如果存在相同的值則不會(huì)插入數(shù)據(jù)赛惩。
（2）replace(object, search, replace)，把object中出現(xiàn)search的全部替換為replace趁餐。
//用法一：并不是修改數(shù)據(jù)喷兼，而只是單純做局部替換數(shù)據(jù)返還而已。
SELECT REPLACE('喝茶','茶','喝')
//結(jié)果： 喝喝
用法二：修改表數(shù)據(jù)啦后雷，對(duì)應(yīng)下面就是季惯，根據(jù)change_type字段找到做任務(wù)的數(shù)據(jù)，用bb來(lái)替換
UPDATE score SET change_type=REPLACE(change_type,'做任務(wù)','bb')
在此臀突，做下對(duì)比：UPDATE和REPLACE的區(qū)別：
1）UPDATE在沒(méi)有匹配記錄時(shí)什么都不做勉抓，而REPLACE在有重復(fù)記錄時(shí)更新，在沒(méi)有重復(fù)記錄時(shí)插入候学。
2）UPDATE可以選擇性地更新記錄的一部分字段藕筋。而REPLACE在發(fā)現(xiàn)有重復(fù)記錄時(shí)就將這條記錄徹底刪除，再插入新的記錄梳码。也就是說(shuō)隐圾，將所有的字段都更新了。
其實(shí)REPLACE更像INSERT與DELETE的結(jié)合边翁。
六翎承、庫(kù)與表基本操作：
（一）庫(kù)的基本操作：
（1）查看所有數(shù)據(jù)庫(kù)以及使用數(shù)據(jù)庫(kù)：
show databases;
use table;
（2）查看當(dāng)前數(shù)據(jù)庫(kù)
select database();
（3）顯示當(dāng)前時(shí)間硕盹、用戶名符匾、數(shù)據(jù)庫(kù)版本
select now(), user(), version();
（4）創(chuàng)建庫(kù)
create database[ if not exists] 數(shù)據(jù)庫(kù)名 數(shù)據(jù)庫(kù)選項(xiàng)
數(shù)據(jù)庫(kù)選項(xiàng)：
CHARACTER SET charset_name
COLLATE collation_name
（5）查看當(dāng)前庫(kù)信息
show create database 數(shù)據(jù)庫(kù)名
（6）修改庫(kù)的選項(xiàng)信息
alter database 庫(kù)名 選項(xiàng)信息
（7）刪除庫(kù)
drop database[ if exists] 數(shù)據(jù)庫(kù)名
同時(shí)刪除該數(shù)據(jù)庫(kù)相關(guān)的目錄及其目錄內(nèi)容
（二）表的基本操作：
（1）創(chuàng)建表：
create [temporary] table[ if not exists] [庫(kù)名.]表名 ( 表的結(jié)構(gòu)定義 )[ 表選項(xiàng)]
每個(gè)字段必須有數(shù)據(jù)類型
最后一個(gè)字段后不能有逗號(hào)
temporary 臨時(shí)表，會(huì)話結(jié)束時(shí)表自動(dòng)消失
對(duì)于字段的定義：
字段名 數(shù)據(jù)類型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
（2）表的選項(xiàng)：
[1]刪除列
ALTER TABLE 【表名字】 DROP 【列名稱】
[2]增加列
ALTER TABLE 【表名字】 ADD 【列名稱】 INT NOT NULL COMMENT '注釋說(shuō)明'
[3]修改列的類型信息
ALTER TABLE 【表名字】 CHANGE 【列名稱】【新列名稱（這里可以用和原來(lái)列同名即可）】 BIGINT NOT NULL COMMENT '注釋說(shuō)明'
[4]重命名列
ALTER TABLE 【表名字】 CHANGE 【列名稱】【新列名稱】 BIGINT NOT NULL COMMENT '注釋說(shuō)明'
[5]重命名表
ALTER TABLE 【表名字】 RENAME 【表新名字】
[6]刪除表中主鍵
Alter TABLE 【表名字】 drop primary key
[7]添加主鍵
ALTER TABLE sj_resource_charges ADD CONSTRAINT PK_SJ_RESOURCE_CHARGES PRIMARY KEY (resid,resfromid)
[8]添加索引
ALTER TABLE sj_resource_charges add index INDEX_NAME (name);
[9]添加唯一限制條件索引
ALTER TABLE sj_resource_charges add unique emp_name2(cardnumber);
[10]刪除索引
Alter table tablename drop index emp_name;
（3）查看所有表
SHOW TABLES瘩例；
（4）查看表機(jī)構(gòu)：
SHOW CREATE TABLE 表名 （信息更詳細(xì)）
DESC 表名 / DESCRIBE 表名 / EXPLAIN 表名 / SHOW COLUMNS FROM 表名 [LIKE 'PATTERN']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
（5）修改表：對(duì)表進(jìn)行重命名
RENAME TABLE 原表名 TO 新表名
RENAME TABLE 原表名 TO 庫(kù)名.表名 （可將表移動(dòng)到另一個(gè)數(shù)據(jù)庫(kù)）
-- RENAME可以交換兩個(gè)表名
（6）刪除表
DROP TABLE[ IF EXISTS] 表名
（7）清空表數(shù)據(jù)
TRUNCATE [TABLE] 表名
（8）復(fù)制表結(jié)構(gòu)
CREATE TABLE 表名 [AS] SELECT * FROM 要復(fù)制的表名
好了啊胶，MySQL優(yōu)化筆記（一）--庫(kù)與表基本操作以及數(shù)據(jù)增刪改講完了甸各，這是MySQL學(xué)習(xí)以及優(yōu)化筆記之一，現(xiàn)在羅列給大家焰坪，這是積累的必經(jīng)一步趣倾，我會(huì)繼續(xù)出這個(gè)系列文章，分享經(jīng)驗(yàn)給大家某饰。歡迎在下面指出錯(cuò)誤儒恋，共同學(xué)習(xí)！黔漂！你的點(diǎn)贊是對(duì)我最好的支持＝刖 ！
