一藻茂、MySQL優(yōu)化

MySQL優(yōu)化從哪些方面入手：

（1）存儲(chǔ)層（數(shù)據(jù)）

構(gòu)建良好的數(shù)據(jù)結(jié)構(gòu)督暂‰时迹可以大大的提升我們SQL語(yǔ)句的性能蛮位。

（2）設(shè)計(jì)層(單臺(tái)服務(wù)器)

表結(jié)構(gòu)的設(shè)計(jì)较沪，表的引擎的選擇，字段的索引的選擇失仁。

（3）架構(gòu)層(多臺(tái)服務(wù)器)

多臺(tái)服務(wù)器協(xié)同工作的時(shí)候的一種架構(gòu)尸曼！主從服務(wù)器，一主多從的實(shí)現(xiàn)萄焦！

主服務(wù)器控轿，負(fù)責(zé)我們的寫(xiě)操作。從服務(wù)器就可以實(shí)現(xiàn)我們的讀操作拂封！

（4）SQL語(yǔ)句層

寫(xiě)出一個(gè)符合我們業(yè)務(wù)的SQL語(yǔ)句是很重要茬射。但是你能寫(xiě)出一個(gè)繼符合業(yè)務(wù)的，又能在性能上有所要求的SQL就是更重要了 冒签。

二在抛、MyISAM與InnoDB的索引結(jié)構(gòu)

數(shù)據(jù)結(jié)構(gòu)：B+Tree

現(xiàn)在我們硬盤(pán)上的數(shù)據(jù)，基本上都是使用的這種數(shù)據(jù)結(jié)構(gòu)來(lái)進(jìn)行存儲(chǔ)數(shù)據(jù)的萧恕。

操作系統(tǒng)上面的文件也是使用這種B+Tree的數(shù)據(jù)結(jié)構(gòu)刚梭！

1、非聚簇（集）結(jié)構(gòu)

myisam主鍵索引：

主鍵索引有2個(gè)文件票唆，有一個(gè)是索引文件望浩，一個(gè)是數(shù)據(jù)文件。

通過(guò)索引查找數(shù)據(jù)的時(shí)候惰说，是通過(guò)找到索引下面掛載的數(shù)據(jù)編號(hào)，通過(guò)這個(gè)編號(hào)找到我們的數(shù)據(jù)區(qū)缘回，取出數(shù)據(jù)吆视。

myisam的普通索引與主鍵索引在硬盤(pán)上面的存儲(chǔ)方式，都是一樣的酥宴。

Mysql服務(wù)器實(shí)現(xiàn)的不同點(diǎn)是主鍵是唯一不能重復(fù)的啦吧，普通是可以重復(fù)的。

2拙寡、聚簇（集）結(jié)構(gòu)

innodb主鍵索引：

主鍵與數(shù)據(jù)是在一起的授滓，所有的Innodb引擎，表數(shù)據(jù)與表索引在一起，只有一個(gè)文件般堆。

索引的作用：

索引是排過(guò)序數(shù)據(jù)在孝，索引會(huì)讓我們?cè)诒闅v的時(shí)候，少遍歷很多數(shù)據(jù)淮摔，所以會(huì)加速我們的查詢私沮。

使用索引是SQL語(yǔ)句的where條件，必須是有索引的字段和橙！

innodb如果使用非主鍵進(jìn)行查詢的時(shí)候仔燕，會(huì)出現(xiàn)我們常說(shuō)的二次遍歷！

第一次是遍歷普通索引魔招，找到普通索引上面的主鍵編號(hào)晰搀；

第二次是遍歷主鍵索引，找到想要的數(shù)據(jù)办斑，返回查詢結(jié)果外恕。

索引覆蓋：查詢的時(shí)候使用到了索引，并且想要獲取的數(shù)據(jù)俄周，都是建立索引的字段吁讨，就會(huì)出現(xiàn)索引覆蓋現(xiàn)象。

innodb主鍵索引的重要性：innodb引擎的表峦朗，必須有主鍵建丧！

不創(chuàng)建主鍵索引時(shí)，它就會(huì)去找這個(gè)表里面某個(gè)字段來(lái)進(jìn)行主鍵索引創(chuàng)建波势。找不到合適的字段翎朱，innodb會(huì)自動(dòng)創(chuàng)建一個(gè)主鍵索引 。

無(wú)論如何Mysql服務(wù)器都會(huì)給innodb引擎的表創(chuàng)建主鍵尺铣！

我們?cè)趧?chuàng)建innodb引擎表的時(shí)候拴曲，必須給innodb創(chuàng)建主鍵。

一定要?jiǎng)?chuàng)建一個(gè)整型的主鍵凛忿，并且是自增長(zhǎng)的澈灼，主鍵不能用于業(yè)務(wù)處理。

自增長(zhǎng)的重要性：

innodb引擎的表店溢，插入操作的時(shí)候叁熔，一定要讓主鍵字段是順序插入的；如果非順序插入床牧，每次插入的時(shí)候荣回，都會(huì)進(jìn)行排序，數(shù)據(jù)就會(huì)整體移動(dòng)戈咳。移動(dòng)過(guò)程要消耗IO心软，數(shù)據(jù)量特別大時(shí)壕吹，占用的時(shí)間會(huì)特別的長(zhǎng)。

使用自增長(zhǎng)删铃，將不會(huì)有這些問(wèn)題耳贬！

創(chuàng)建表的時(shí)候，默認(rèn)選擇innodb就好泳姐，可以實(shí)現(xiàn)更多的業(yè)務(wù)需求效拭。

三、MySQL存儲(chǔ)引擎

1胖秒、什么是存儲(chǔ)引擎

存儲(chǔ)引擎就是存儲(chǔ)數(shù)據(jù)到硬件或者內(nèi)存中的一種技術(shù)缎患！

2、MyISAM與InnoDB引擎的區(qū)別

myisam是支持表鎖阎肝，innodb是支持行鎖（有條件的行鎖挤渔，必須有索引字段）

myisam是不支持事務(wù)，innodb是支持事務(wù)的

myisam是支持地理空間索引风题，innodb是在5.6.2版本之上才支持

myisam是支持壓縮的判导，innodb是不支持壓縮

myisam是支持全文索引的，innodb是不支持全文索引

3沛硅、存儲(chǔ)引擎插入數(shù)據(jù)的執(zhí)行順序

innodb的表會(huì)自動(dòng)的排序眼刃；myisam的表不會(huì)自動(dòng)排序。所以以后你在請(qǐng)求別人的接口的時(shí)候摇肌，一定要注意擂红，不要相信別人的排序。自己排一下围小。

4昵骤、MyISAM引擎的壓縮

（1）對(duì)myisam引擎表數(shù)據(jù)進(jìn)行壓縮

找到相應(yīng)的工具，linux為例肯适，在目錄/usr/local/bin/下变秦，壓縮工具：myisampack，解壓工具：myisamchk

（2）壓縮工具介紹：

使用：myisampack 表的絕對(duì)路徑(沒(méi)有表的后綴)

eg：/usr/local/mysql/bin/myisampack /usr/local/mysql/data/test/myisam

如果數(shù)據(jù)表太小框舔，則會(huì)提示不能壓縮蹦玫。

雖然可以壓縮成功，但是索引會(huì)損壞刘绣，所以在壓縮完成后應(yīng)該重建索引樱溉。

（3）重建索引

myisamchk -rq 表的絕對(duì)路徑(沒(méi)有表的后綴)

eg：/usr/local/mysql/bin/myisamchk -rq /usr/local/mysql/data/test/myisam

執(zhí)行結(jié)束后，索引會(huì)修復(fù)成功

（4）刷新數(shù)據(jù)回硬盤(pán)【重點(diǎn)】

進(jìn)入mysql中额港，執(zhí)行flush tables;即可

說(shuō)明：壓縮的表，只能是只讀的歧焦。不能修改移斩，不能更新肚医。

（5）解壓縮

myisamchk --unpack 表的絕對(duì)路徑（沒(méi)有表的后綴）

eg：/usr/local/mysql/bin/myisamchk --unpack /usr/local/mysql/data/test/myisam

完成后可以查看表數(shù)據(jù)：在linux下執(zhí)行ll -h /usr/local/mysql/data/test/

（6）必須刷新回硬盤(pán)：

在MySQL下執(zhí)行：flush tables;

5、MyISAM引擎與InnoDB引擎的備份與還原

（1）mysql服務(wù)器的備份與還原：

備份

備份表：mysqldump –u root –p 庫(kù) [表] > 備份的路徑

eg：/usr/local/mysql/bin/mysqldump -u root -p test innodb > /

備份庫(kù)：mysqldump -u root –p 庫(kù) > 備份的路徑

eg：/usr/local/mysql/bin/mysqldump -u root -p test > /tmp/test.sql

備份：帶庫(kù)名：mysqldump -uroot –p –B 庫(kù) > 備份的路徑

eg：/usr/local/mysql/bin/mysqldump -u root -p -B test > /tmp/testb.sql

還原

還原表：mysql –u root -p 庫(kù)名 < 備份sql的文件路徑

eg：/usr/local/mysql/bin/mysql -u root -p test < /tmp/innodb.sql

還原庫(kù)：mysql –uroot –p < 備份SQL的庫(kù)的路徑

備份文件里面是sql語(yǔ)句向瓷，是可以修改的肠套。但是我們不應(yīng)該修改它。備份就是數(shù)據(jù)的拷貝猖任，修改了之后你稚，一切都變味了。

6朱躺、其他存儲(chǔ)引擎介紹

（1）Memory

數(shù)據(jù)置于內(nèi)存的存儲(chǔ)引擎刁赖，擁有極高的插入，更新和查詢效率长搀。但是會(huì)占用和數(shù)據(jù)量成正比的內(nèi)存空間宇弛。并且其內(nèi)容會(huì)在MySQL重新啟動(dòng)時(shí)丟失 。

（2）Archive

歸檔存儲(chǔ)引擎源请，只支持?jǐn)?shù)據(jù)的查詢和寫(xiě)入枪芒。

經(jīng)常用于存儲(chǔ)日志等相關(guān)信息。

（3）merge

這個(gè)可以把很多myisam表連接在一起谁尸，我們查詢數(shù)據(jù)的時(shí)候舅踪，就感覺(jué)在一張里面表里面使用。myisam表使用量大的時(shí)候良蛮，merge引擎是經(jīng)常的出現(xiàn)抽碌。

查看MySQL引擎：show engines;

如果是merge，則會(huì)顯示mrg_myisam = merge

四背镇、列類型的選擇

1咬展、數(shù)值類型的選取

tinyint占的字節(jié)數(shù)；int占的字節(jié)數(shù)瞒斩；

有符號(hào)（-128破婆，127）與無(wú)符號(hào)（0，255）的范圍胸囱。

有一個(gè)字段祷舀，我們一定要判斷它所在的范圍杀餐，如果判斷的不準(zhǔn)洒疚，可以適當(dāng)放大一點(diǎn)，保存數(shù)據(jù)能夠全部存儲(chǔ)進(jìn)去噪珊。

選擇類型的時(shí)候谤职，盡量是選擇最小的類型饰豺，就能夠把字段所有的范圍包含進(jìn)去的。

示例：年齡（人）：0 - 200

選擇tinyint 無(wú)符號(hào)的

對(duì)ID選值的說(shuō)明：目前來(lái)說(shuō)ID默認(rèn)選擇int允蜈，無(wú)符號(hào)即可冤吨！

float 是取近似值蒿柳！

double 四舍五入；很多商店都是選擇的這種方式漩蟆。

單位很重要：?jiǎn)挝皇窃臅r(shí)候垒探，取2個(gè)小數(shù)點(diǎn)是沒(méi)有問(wèn)題的。

2怠李、字符串類型的選取

char(10) varchar(10) ：這個(gè)10是10個(gè)字符

char ：0 – 255個(gè)字符圾叼！無(wú)論是utf8還是gbk！

varcahr ：0-65535個(gè)字節(jié)捺癞！

utf8 ：一個(gè)漢字占3個(gè)字節(jié)夷蚊； gbk ：一個(gè)漢字占2個(gè)字節(jié)。

varchar 最大長(zhǎng)度是：

utf8：(65535 – 1 - 2) / 3 = 21844能夠存儲(chǔ)的字符

gbk： (65535 – 1 - 2) / 2 = 32766能夠存儲(chǔ)的字符

varchar可以使用的寬度是動(dòng)態(tài)變化的 翘簇。

blob的是存儲(chǔ)二進(jìn)制的：如果MySQL要存儲(chǔ)圖片撬码，選擇longblob即可。

創(chuàng)建char字段的表版保，存儲(chǔ)的字符數(shù)超過(guò)255時(shí)會(huì)報(bào)錯(cuò)呜笑，但是5.5版本的時(shí)候，超過(guò)的長(zhǎng)度會(huì)自動(dòng)截瘸估纭叫胁；5.7版本的時(shí)候，會(huì)報(bào)錯(cuò)汞幢。

至于是自動(dòng)截取還是報(bào)錯(cuò)驼鹅，都是可以配置的。

對(duì)于varchar對(duì)寬度的影響：

創(chuàng)建表時(shí)：CREATE TABLE varc(name varchar(21844),age int)ENGINE=MYISAM CHARSET=utf8;

這時(shí)因?yàn)槎嗉恿藗€(gè)age int這個(gè)字段森篷，就創(chuàng)建失敗了输钩。

把多加的字段的空間留出來(lái)，就可以創(chuàng)建成功了：CREATE TABLE varc(name varchar(21842),age int)ENGINE=MYISAM CHARSET=utf8;

3仲智、時(shí)間類型的選取

時(shí)間戳

PHP：time();

MYSQL：unix_timestamp();

如果存儲(chǔ)時(shí)間的時(shí)候买乃，不顯示要求的時(shí)候，就使用時(shí)間戳存儲(chǔ)钓辆，選擇int就好 剪验。

4、枚舉類型與集合類型

set與enum底層是使用整型來(lái)實(shí)現(xiàn)的前联。只是我們傳輸?shù)臅r(shí)候功戚，是傳的相應(yīng)的值過(guò)來(lái)。但是這種設(shè)置不夠靈活似嗤。

tinyint實(shí)現(xiàn)：1 = > man 2 => woman 3 => ...

php配置文件： array(1=>man, 2=>woman)

php代碼使用的時(shí)候啸臀，直接拿來(lái)替換就可以。

5烁落、IP與整型數(shù)據(jù)的轉(zhuǎn)換

mysql ：inet_aton 乘粒；IP地址轉(zhuǎn)成整型

select inet_aton('192.168.111.110');此時(shí)這個(gè)IP會(huì)轉(zhuǎn)成3232264046

mysql ：inet_ntoa 席揽；整型轉(zhuǎn)成IP地址

select inet_ntoa(3232264046);此時(shí)這個(gè)整型會(huì)轉(zhuǎn)成192.168.111.110這個(gè)IP

php：ip2long ；ip轉(zhuǎn)成整型

echo ip2long('192.168.111.110');會(huì)打印出-1062703250

php ：long2ip 谓厘；整型轉(zhuǎn)成IP

echo long2ip(-1062703250);會(huì)打印出192.168.111.110

使用php轉(zhuǎn)成的整型，就要使用PHP的函數(shù)轉(zhuǎn)成IP地址寸谜。

不同的語(yǔ)言轉(zhuǎn)出來(lái)的值可以不一樣竟稳。所有一定要使用什么語(yǔ)言轉(zhuǎn)的，就要使用什么轉(zhuǎn)回去熊痴。

六他爸、MySQL中的執(zhí)行計(jì)劃

1、什么是MySQL執(zhí)行計(jì)劃

就是把SQL語(yǔ)句的執(zhí)行效果顯示出來(lái)果善。

2诊笤、執(zhí)行計(jì)劃的作用

讓我們清楚的知道SQL語(yǔ)句干了什么，滿足不滿足我們的理想巾陕！

3讨跟、執(zhí)行計(jì)劃的基本語(yǔ)法

explain 查詢SQL語(yǔ)句

eg：explain select * from innodb limit 1;

4、explain詳解

上節(jié)的查詢語(yǔ)句會(huì)查詢出的信息鄙煤，解釋如下：

type ：const | ref | range | index | all

? 小 大

? const ：基本上使用等于的時(shí)候才會(huì)出現(xiàn)這個(gè)值

? ref ：小范圍結(jié)果的時(shí)候晾匠，就會(huì)這個(gè)值

? range ：大范圍結(jié)果的時(shí)候，就會(huì)這個(gè)值

? index ：整個(gè)索引查詢梯刚，遍歷索引

? all ：全表查詢凉馆，使用索引了。

Extra：

Using filesort ：文件排序亡资，這個(gè)就很慢了澜共。可以優(yōu)化

Using temporary ：使用了臨時(shí)表锥腻，這個(gè)就可以優(yōu)化

Using index ：使用了索引覆蓋

Using where ：使用了數(shù)據(jù)過(guò)濾

索引長(zhǎng)度：創(chuàng)建表的時(shí)候嗦董，只是通知數(shù)據(jù)庫(kù)幫助創(chuàng)建了索引。索引是怎么創(chuàng)建的旷太，如何創(chuàng)建的展懈，都是mysql服務(wù)器自己做的事情。所有這個(gè)長(zhǎng)度供璧，你只需要拿來(lái)對(duì)性能優(yōu)化的時(shí)候存崖，對(duì)比即可。

5睡毒、數(shù)據(jù)庫(kù)中索引的設(shè)計(jì)

（1）什么是索引

存儲(chǔ)引擎以一種數(shù)據(jù)結(jié)構(gòu)進(jìn)行存儲(chǔ)的數(shù)據(jù)来惧。

這種數(shù)據(jù)結(jié)構(gòu)的數(shù)據(jù)讓我們查詢數(shù)據(jù)時(shí)非常的高效。

（2）非常重要的索引類型

① 主鍵索引（primary key）

刪除主鍵：alter table table_name drop primary key

eg：alter table innodb drop primary key;但是此時(shí)會(huì)報(bào)錯(cuò)演顾，因?yàn)橹麈I是自動(dòng)增長(zhǎng)的供搀，因此不能刪除隅居。

刪除自動(dòng)增長(zhǎng)：alter table table_name modify id int(11) unsigned not null;

eg：alter table innodb modify id int(11) unsigned not null;此時(shí)自動(dòng)增長(zhǎng)就刪除了，再進(jìn)行刪除主鍵就能執(zhí)行成功葛虐。

添加主鍵：alter table table_name add primary key (id)

eg：alter table innodb add primary key (id);

② 唯一索引（unique key）

主鍵：一張表里面只能有一個(gè)胎源；主鍵值是不能夠重復(fù)的∮炱辏可以使用自動(dòng)增長(zhǎng)涕蚤。

唯一：一張表里面可以有多個(gè)；主鍵值是不能夠重復(fù)的的诵⊥蛘ぃ可以使用自動(dòng)增長(zhǎng)。

添加唯一索引：alter table table_name add unique key 取個(gè)索引名(字段) ;

eg：alter table innodb add unique key myname(name);

刪除索引：alter table table_name drop key 索引名;

eg：alter table innodb drop myname;

③ 普通索引（key）

普通：普通索引在值是可以重復(fù)的西疤，一張表可以創(chuàng)建多個(gè)烦粒，沒(méi)有限制的。

添加普通索引：alter table table_name add key 取個(gè)名稱(字段);

eg：alter table innodb add key yourage(age);

刪除普通索引：alter table table_name drop key 索引名稱;

eg：alter table innodb drop key yourage;

重點(diǎn)說(shuō)明：普通索引才是使用的非常廣泛代赁，而且經(jīng)常需要我們操作的扰她。

④ 復(fù)合（聯(lián)合）索引

創(chuàng)建索引的，以多個(gè)字段共同創(chuàng)建就是復(fù)合索引芭碍。復(fù)合索引的類型根據(jù)創(chuàng)建的關(guān)鍵字來(lái)決定 义黎。這些關(guān)鍵字，就是上面的三種索引豁跑。所以你創(chuàng)建的是哪一個(gè)索引廉涕，就有哪一種索引的特性。

復(fù)合索引：很多時(shí)候艇拍，都是普通形式存在的狐蜕。

添加：alter table table_name add key 取個(gè)索引名(字段1,字段2,……) ;

eg：alter table innodb add key name_age(name, age);

刪除符合索引：alter table table_name drop key 索引名;

eg：alter table innodb drop key name_age;

⑤ 總結(jié)

添加索引的時(shí)候：

主鍵索引：表里面唯一的，值不能重復(fù)

? alter table table_name add primary key (id)

唯一索引：表里面可以有多個(gè)卸夕，值不能重復(fù)

? alter table table_name add unique key 索引名(字段)

普通索引：表里面可以有多個(gè)层释，值可以重復(fù)

? alter table table_name add key 索引名(字段)

普通復(fù)合索引：

? alter table table_name add key 索引名(字段,字段,……)

刪除索引的時(shí)候：

主鍵索引：

? alter table table_name drop primary key

唯一索引|普通索引|普通復(fù)合索引

? alter table table_name drop key 索引名

復(fù)合索引 === 聯(lián)合索引， 這2個(gè)名稱是一個(gè)意思快集！

（3）索引字段的使用原則

1）等于匹配

驗(yàn)證只有使用id的時(shí)候贡羔，才會(huì)使用到主鍵索引

explain select * from innodb where name = 'xiao1'\G

上面的語(yǔ)句中，name字段是沒(méi)有索引的个初，索引沒(méi)有辦法使用到索引乖寒。

explain select * from innodb where id = 1\G

使用索引字段的時(shí)候，使用到了索引院溺，并且范圍還是很小楣嘁。

能不能使用索引，根據(jù)where后面的條件的來(lái)決定的。

2）大于逐虚，小于匹配

說(shuō)明：在實(shí)際工作聋溜，大于小于盡量不使用，能夠使用in替代的叭爱，就使用in代替撮躁。

大于：explain select * from innodb where id > 0\G

小于：explain select * from innodb where id < 0\G

大于小于在目前看來(lái)都使用了索引；但是如果數(shù)據(jù)量變化了买雾，這個(gè)結(jié)果會(huì)改變的馒胆。

3）最左原則

like查詢：explain select * from innodb where name like 'xiao1%'\G

結(jié)果：數(shù)據(jù)會(huì)影響索引的使用。

如果將%寫(xiě)在左邊：explain select * from innodb where name like %xiao1'\G

結(jié)果：沒(méi)有使用索引凝果。

說(shuō)明：在使用索引的時(shí)候，值的左邊一定要保持是定值睦尽。定值就是不會(huì)改變的器净。

4）列獨(dú)立

explain select * from innodb where id + 1 = 2\G此時(shí)，這個(gè)id + 1不是一個(gè)定值当凡，因此不能使用索引山害。

修改一下：explain select * from innodb where id = 2 - 1\G，字段id這邊不要有其它值時(shí)沿量，就會(huì)使用到索引

5）復(fù)合索引

創(chuàng)建復(fù)合索引：alter table innodb add key name_fu(name,age,sex);

使用索引進(jìn)行查詢：explain select * from innodb where name = 'xiao1' and age = 18 and sex = 1\G

此時(shí)使用到了索引浪慌，此時(shí)索引的長(zhǎng)度key_len: 64

只使用name和age索引查詢：explain select * from innodb where name = 'xiao1' and age = 18\G

此時(shí)的索引的長(zhǎng)度key_len: 63

只使用name字段索引查詢：explain select * from innodb where name = 'xiao1'\G

此時(shí)的索引的長(zhǎng)度key_len: 62

創(chuàng)建復(fù)合索引的時(shí)候：name字段的索引長(zhǎng)度是62；age與sex分別是1

只使用name和sex字段索引查詢：explain select * from innodb where name = 'xiao1' and sex = 1\G

說(shuō)明：只使用了name字段長(zhǎng)度的索引朴则。sex是沒(méi)有被使用的权纤。

只使用age和sex字段索引查詢：explain select * from innodb where age= 18 and sex = 1\G

此時(shí)沒(méi)有使用到索引。

只使用age字段的索引查詢：explain select * from innodb where age= 18\G

只使用sex字段的索引查詢：explain select * from innodb where sex= 1\G

此時(shí)這兩種查詢也都沒(méi)有使用到索引乌妒。

總結(jié)：復(fù)合索引在創(chuàng)建的時(shí)候的順序汹想，影響了使用的順序。

創(chuàng)建的時(shí)候是什么順序撤蚊，使用的時(shí)候就是什么順序古掏。

創(chuàng)建的時(shí)候是最左邊是name 然后是age ; 最后是sex；所以你使用的時(shí)候侦啸，要保持最左原則槽唾。從左邊開(kāi)始使用。

sql發(fā)送給mysql服務(wù)器的時(shí)候光涂，mysql服務(wù)器會(huì)解析庞萍，然后在優(yōu)化。優(yōu)化的時(shí)候忘闻，就會(huì)重新拼接sql語(yǔ)句挂绰。

6）OR表達(dá)式：or最好不要使用，性能特別低。

7）mysql對(duì)索引的自動(dòng)判斷介紹

mysql服務(wù)器才是管理索引的使用者葵蒂。我們只能告訴mysql服務(wù)器交播，去創(chuàng)建一個(gè)什么樣的索引。你在使用索引的時(shí)候践付，一切的規(guī)則都是按MYSQL服務(wù)器秦士，內(nèi)部的規(guī)則去運(yùn)行的。所以你只需要記住這些規(guī)則就可以了永高。通過(guò)explain看出來(lái)效果隧土。選擇出最佳的就可以了 。

比如：當(dāng)你的字段有索引的時(shí)候命爬。但是你查詢的數(shù)據(jù)曹傀，有很多的時(shí)候。MYSQL服務(wù)器就會(huì)自動(dòng)的判斷饲宛。使用索引與直接在硬盤(pán)上面查數(shù)據(jù)皆愉，那個(gè)更快。經(jīng)過(guò)它自己的判斷 艇抠。會(huì)決定最終使用那一個(gè)幕庐。如果它認(rèn)為直接掃描硬盤(pán)更快。就會(huì)不經(jīng)過(guò)索引家淤。直接使用全表掃描异剥。

4、適合使用索引的應(yīng)用場(chǎng)景

（1）索引的選擇性：

字段里面不重復(fù)的值的總數(shù) / 字段值的總數(shù) = 0 - 1

這個(gè)值應(yīng)該大于0.4制作索引的效果就可以了絮重。

統(tǒng)計(jì)總數(shù)：

count：select count(age) from innodb;

顯示統(tǒng)計(jì)字段里不重復(fù)的值：select distinct sex from innodb;

統(tǒng)計(jì)不重復(fù)的總數(shù)：select count(distinct sex) from innodb;

計(jì)算：select count(distinct sex) / count(sex) from innodb;

這個(gè)值匹配度太低了冤寿，所以不適合做索引。所有性別是不能做索引青伤。做索引就太浪費(fèi)資源疚沐。

（2）數(shù)據(jù)的量：

數(shù)據(jù)表的數(shù)據(jù)量，在1000以上的就可以制作索引 潮模。重復(fù)值太多的字段不適合做索引亮蛔。

5、SQL語(yǔ)句優(yōu)化

myisam表在使用統(tǒng)計(jì)的時(shí)候擎厢，直接使用count(*)就好了究流。因?yàn)槲覀僲yisam表的最后面有一個(gè)隱藏的值就是我們表的數(shù)量。你使用count(*)其實(shí)就是把這個(gè)值給取出來(lái)动遭。

innodb表在使用統(tǒng)計(jì)的時(shí)候芬探，直接使用count(字段名)就好了，各種說(shuō)明都說(shuō)的不適合使用*厘惦。但是當(dāng)測(cè)試數(shù)據(jù)量在200W的時(shí)候偷仿，使用*比字段名要快一些哩簿！

在使用group by時(shí)，它會(huì)自動(dòng)排序酝静，可以使用order by null取消排序

非常重要的性能說(shuō)明：大量數(shù)據(jù)的時(shí)候节榜，分頁(yè)的實(shí)現(xiàn)！ limit N, 10;

比如：select * from myisam limit 300000, 10;此時(shí)查詢出結(jié)果說(shuō)消耗的時(shí)間很長(zhǎng)

優(yōu)化：select * from myisam where id > 300000 limit 10;此時(shí)消耗的是就就很少了别智。

所以：id：請(qǐng)一定不要更新或者刪除它宗苍。

七、兩種特殊的索引結(jié)構(gòu)

1薄榛、全文索引（MyISAM）

其它索引：之前章節(jié)介紹的4種索引讳窟，都是把列的值作為索引。

全文索引：是把列里面的值進(jìn)行分詞敞恋，然后把每一個(gè)分詞作為索引丽啡。

文章：標(biāo)題，作者硬猫，內(nèi)容补箍，發(fā)布時(shí)間

內(nèi)容從幾千到幾萬(wàn)字不等，存儲(chǔ)內(nèi)容需要使用大文本字段浦徊。

搜索內(nèi)容里面含有某一個(gè)詞的文章，曾經(jīng)學(xué)習(xí)的搜索方式唯有使用like ‘%詞%’進(jìn)行匹配天梧。

改善搜索的查詢速度盔性，需要對(duì)文章內(nèi)容里面的每一個(gè)詞，進(jìn)行索引呢岗。讓查詢的時(shí)候可以使用到索引冕香，提升查詢的性能。

myisam引擎支持的全文索引后豫，就可以完成這樣的功能悉尾。該引擎支持英文，不支持中文挫酿！

英文分詞：空格進(jìn)行分詞构眯。

添加全文索引：alter table table_name add fulltext 取個(gè)名稱(字段);

刪除全文索引：alter table table_name drop index 取的名稱;

之前介紹的3種索引與全文索引刪除時(shí)都可以使用index或者key

搜索：select * from table_name where match('字段') against('搜索詞') ;

（1）創(chuàng)建全文索引表

create table `fulltext`(
    id int(11) unsigned not null auto_increment comment 'id',
    content text comment 'hello world',
    primary key(id)
)engine=myisam charset=utf8;

（2）創(chuàng)建全文索引

在插入一些數(shù)據(jù)后，創(chuàng)建全文索引：

alter table `fulltext` add fulltext fullcon(content);

mysql里面早龟，關(guān)鍵詞惫霸。一定要加反引號(hào)！

（3）進(jìn)行搜索

搜索詞：you葱弟，

select * from `fulltext` where match(content) against('you');
eg:select * from `fulltext` where match(content) against('you');
查看表結(jié)果
show create table `fulltext`\G
如果顯示FULLTEXT KEY `fullcon` (`content`)壹店，則說(shuō)明全文索引是添加成功的

（4）進(jìn)行搜索

搜索詞：Dream

select * from `fulltext` where match(content) against('Dream');
再搜索：
select * from `fulltext` where match(content) against('relationship');
總結(jié)：寫(xiě)文章的時(shí)候，you與is在英文文章中芝加，是經(jīng)常出現(xiàn)的硅卢，所以就沒(méi)有搜索的必要。所以像這類的詞，全文索引會(huì)過(guò)濾掉将塑，這些詞是全文索引定義的脉顿。
能夠使用搜索的詞，只有那些能夠區(qū)分出內(nèi)容的詞抬旺，才能被搜索到弊予。

（5）刪除全文索引

alter table `fulltext` drop index 索引名;
eg：alter table `fulltext` drop index fullcon;

（6）總結(jié)

搜索不到的詞，每篇文章中出現(xiàn)的機(jī)會(huì)都很多开财，搜索的時(shí)候就沒(méi)有意義汉柒。分詞就過(guò)濾掉了這些詞，不對(duì)這些詞建立索引责鳍。過(guò)濾詞是全文索引定義的碾褂，可以不必在意這些。

中文文章內(nèi)容搜索是可以實(shí)現(xiàn)的历葛，需要使用中間件（第三方工具）正塌，來(lái)幫助mysql完成功能。

2恤溶、前綴索引

其它索引：之前介紹的4種索引乓诽，都是把列的值作為索引。

前綴索引：是把列前面的一部份值作為索引咒程。

比如：張三的張鸠天；李四的李；王五的王

建立索引的數(shù)據(jù)是字段的前面一部分帐姻，查詢返回值是需要一個(gè)完整的字段值稠集。查詢返回值是前綴索引時(shí)，為了返回一個(gè)完整的字段值饥瓷，就必須要去數(shù)據(jù)區(qū)找到值剥纷。

前綴索引制作的要求：都是長(zhǎng)字段。

字段值前面的一部分呢铆，能夠很好的代表整個(gè)字段的時(shí)候晦鞋，才有必要制作前綴索引。

索引的選擇性

計(jì)算公式：字段不重復(fù)值的總數(shù) / 字段值的總數(shù) = 0 - 1

? select count(distinct 字段值) / count(字段) = 0 -1

前綴索引的選擇性

計(jì)算公式：字段前面的幾個(gè)值不重復(fù)的總數(shù) / 字段值的總數(shù) = 0 - 1

     select count(distinct left(字段值, number)) / 字段值的總數(shù) = 0 - 1 

number的確定：

     前綴索引的選擇性結(jié)果  === 索引的選擇性結(jié)果 

left的示例：

select left('abcd', 2);打印出ab

select left('abcd', 3);打印出abc

添加前綴索引：前綴索引很多時(shí)候都有重復(fù)的值棺克，使用普通索引即可鳖宾！

alter table table_name add key 取個(gè)索引名(字段(number))

number ：表示取字段值的前面多少個(gè)！是由前綴索引的選擇性計(jì)算出來(lái)的值逆航！

（1）創(chuàng)建一個(gè)表

create table prefix(
    id int(11) unsigned not null auto_increment comment 'id',
    name var varchar(20) not null comment 'name',
    passwd char(32) not null comment 'passwd',
    primary key(id)
)engine=innodb charset=utf8;

（2）準(zhǔn)備腳本

$db = mysqli_connect('127.0.0.1', 'root', '123456', 'test');
mysqli_query($db, "set names utf8");
    
$sql = "insert into prefix (`name`, `passwd`) select concat('xiao', convert(round(rand() * 20000), char)) as name, 
            md5(
                substring('qwerop[]\|{PLKJH}asdfghjkl;:zxc567vbnm,./<>?`~1234890-=!@tyui#$%^&*()_+……%QWERTYUIOGFDSAZXCVBNM<>?:;[]F86892652jdmjwolbvxzm2', 
                    ceiling( rand() * 128), 
                    ceiling( rand() * 50 + rand() * 30 - 1)
                )
            ) as passwd";

for($i = 0; $i < 4444; $i++){
    mysqli_query($db, $sql);
}

（3）運(yùn)行腳本

將這個(gè)準(zhǔn)備好的腳本放到linux站點(diǎn)目錄下鼎文，并用瀏覽器訪問(wèn)該頁(yè)面。此時(shí)瀏覽器刷新圖標(biāo)會(huì)一直在轉(zhuǎn)因俐，等到不轉(zhuǎn)了表示請(qǐng)求成功了拇惋。

轉(zhuǎn)的時(shí)候蹬音，等的很著急鸥鹉；你就會(huì)再次刷新頁(yè)面蓖康。這個(gè)時(shí)候會(huì)出現(xiàn)什么情況呢肿孵？

刷新頁(yè)面，就是再次發(fā)送請(qǐng)求胡嘿。以當(dāng)前頁(yè)面來(lái)說(shuō)這個(gè)問(wèn)題蛉艾。剛才我們刷新了二次，第一次沒(méi)有等待結(jié)果返回衷敌，就再次刷新了勿侯。我們的數(shù)據(jù)結(jié)果是執(zhí)行了完整的二次。

當(dāng)你第一次刷新的時(shí)候缴罗，瀏覽器已經(jīng)把請(qǐng)求發(fā)送給了服務(wù)器助琐。服務(wù)器接收到任務(wù)，已經(jīng)開(kāi)始執(zhí)行面氓。你再次刷新的時(shí)候兵钮，瀏覽器發(fā)送了第二個(gè)任務(wù)給服務(wù)器，服務(wù)器接收到任務(wù)舌界，然后開(kāi)始執(zhí)行掘譬。

服務(wù)器把第一次任務(wù)執(zhí)行完成，準(zhǔn)備返回的時(shí)候呻拌，找不到回家的路了葱轩，就沒(méi)有辦法返回。第二次任務(wù)執(zhí)行完成柏锄，準(zhǔn)備返回的時(shí)候酿箭，回家的路還在复亏，因此就直接返回給瀏覽器了趾娃。

將該P(yáng)HP文件可以在php解析器直接執(zhí)行一下：

/working/php7/bin/php /站點(diǎn)目錄/www/MySQL.php

在查看一下數(shù)據(jù)庫(kù)：select count(*) from prefix;。此時(shí)結(jié)果多了一個(gè)4444缔御，證明運(yùn)行成功了抬闷。

PHP程序在執(zhí)行PHP文件的時(shí)候，連接數(shù)據(jù)的地址寫(xiě)的是：127.0.0.1耕突。所以你的保證php連接的地址能夠正常被訪問(wèn)到笤成。127.0.0.1表示的是本地，所以php與mysql應(yīng)該在同一臺(tái)服務(wù)器上面眷茁。

（4）索引的選擇性

select count(distinct passwd) / count(*) from prefix;

（5）前綴索引的選擇性

select count(distinct left(passwd, 6)) / count(*) from prefix;

當(dāng)取的字段前面6個(gè)字符的時(shí)候炕泳，計(jì)算出來(lái)的結(jié)果，就與索引選擇性的結(jié)果是一樣的上祈。

（6）添加前綴索引

alter table prefix add key 取個(gè)名(passwd(6));

eg：alter table prefix add key prepass(passwd(6));

（7）確定搜索的值

select * from prefix limit 1;

查詢出的結(jié)果培遵，確定passwd的值為4e1d9fdd24906a3256a0c2bafa3f532f

（8）使用值進(jìn)行搜索

select * from prefix where passwd='4e1d9fdd24906a3256a0c2bafa3f532f';

此時(shí)使用了索引浙芙，且索引的長(zhǎng)度為：key_len : 18

（9）刪除前綴索引

alter table prefix drop key 索引名;

eg：alter table prefix drop key prepass;

（10）創(chuàng)建普通索引

alter table prefix add key passwd(passwd);

（11）使用普通索引進(jìn)行搜索

explain select * from prefix where passwd = "4e1d9fdd24906a3256a0c2bafa3f532f";

此時(shí)的索引的長(zhǎng)度：key_len: 96

（12）結(jié)果總結(jié)

使用前綴索引的長(zhǎng)度是：18

使用普通索引的長(zhǎng)度是：96

前綴索引是普通索引的 5 倍速！

前綴索引制作的字段籽腕，一般情況是比較長(zhǎng)的才使用它嗡呼，工作中很少有這樣的字段出現(xiàn)！

八皇耗、MySQL中的其他功能

1南窗、慢日志查詢

慢[日志]查詢：把sql語(yǔ)句執(zhí)行慢的，都記錄到日志文件里面郎楼。

定時(shí)檢查日志文件內(nèi)容万伤，把sql語(yǔ)句執(zhí)行慢進(jìn)行優(yōu)化！

慢[日志]查詢中的“慢”指的是：人為設(shè)定的時(shí)間箭启，超過(guò)了就是慢壕翩。

（1）慢[日志]查詢配置

show variables like 'slow_query%';

顯示出的結(jié)果：

slow_query_log OFF：代表開(kāi)關(guān)，OFF的時(shí)候是關(guān)閉的傅寡；ON的時(shí)候是開(kāi)啟的放妈。

slow_query_log_file /usr/local/mysql/data/localhost-slow.log；慢日志的存儲(chǔ)位置荐操，請(qǐng)不要修改它芜抒，因?yàn)樾薷木陀袡?quán)限的問(wèn)題！

（2）慢[日志]查詢時(shí)間配置

show variables like 'long_query%';

查詢出的結(jié)果：

long_query_time 10.000000托启；中10.000000的小數(shù)點(diǎn)后面代表單位是秒

（3）開(kāi)啟慢[日志]查詢

set global slow_query_log = 1;

此時(shí)在進(jìn)行第（1）步的操作宅倒，可以看到slow_query_log的值是ON，這就代表開(kāi)啟了屯耸。

這些內(nèi)容拐迁，是mysql服務(wù)器管理人員設(shè)定的。一般情況都是默認(rèn)設(shè)定在配置文件里面的疗绣。我們現(xiàn)在使用的臨時(shí)有效的线召，重啟就沒(méi)有效果了。

（4）設(shè)定慢[日志]查詢時(shí)間

set long_query_time = 1.111111;

此時(shí)經(jīng)過(guò)第（2）步操作多矮，可以看到long_query_time的值變?yōu)?code>1.100000

這個(gè)就是設(shè)定的慢缓淹，SQL查詢時(shí)間，超過(guò)這個(gè)的時(shí)候塔逃，就會(huì)記錄到慢日志里面去讯壶。

（5）查看慢[日志]查詢?nèi)罩疚募恢?/h3>

日志文件在/usr/local/mysql/data/目錄下的localhost-slow.log文件。

學(xué)習(xí)一個(gè)全新的查看方式：

參數(shù) ： -f 是打開(kāi)文件湾盗，占用當(dāng)前窗口伏蚊，只要文件有新內(nèi)容從尾部插入，就立即展示出來(lái)格粪。

eg：tail -f /usr/local/mysql/data/localhost-slow.log

此時(shí)打開(kāi)之后躏吊，當(dāng)前的窗口就會(huì)被占用了肺孵。

這種打開(kāi)方式在工作中是經(jīng)常使用的。

（6）總結(jié)

慢[日志]查詢：開(kāi)啟的時(shí)候颜阐，可以把sql語(yǔ)句執(zhí)行慢的記錄下來(lái)平窘。可以針對(duì)性的對(duì)sql語(yǔ)句優(yōu)化凳怨。

-f參數(shù)非常的重要瑰艘。工作中，集群服務(wù)器出現(xiàn)問(wèn)題肤舞。訪問(wèn)的時(shí)候紫新，有時(shí)候出bug，有時(shí)候沒(méi)有出bug 李剖。

此時(shí)芒率，使用-f打開(kāi)全部服務(wù)器日志文件。請(qǐng)求服務(wù)器內(nèi)容時(shí)沒(méi)有出現(xiàn)bug篙顺，可以立即查看哪臺(tái)服務(wù)器日志文件有新消息偶芍，可以立即排除這臺(tái)服務(wù)器了。

2德玫、SQL語(yǔ)句緩存

執(zhí)行sql語(yǔ)句的時(shí)候匪蟀，直接把執(zhí)行的結(jié)果，存儲(chǔ)在服務(wù)器上面宰僧。下次再一次執(zhí)行相同的SQL語(yǔ)句時(shí)材彪，立即返回緩存結(jié)果。

（1）確定服務(wù)器是否支持

show variables like '%query_cache%';

得到的結(jié)果的參數(shù)解釋：

have_query_cache ：值是yes的時(shí)候琴儿，就是支持sql語(yǔ)句緩存 段化。

query_cache_limit ：每條sql語(yǔ)句，可以占用（最大）的空間大小是1M造成。

query_cache_min_res_unit ：最少占用的空間大小4K 显熏。

query_cache_size ：緩存大小 。（5.5版本時(shí)谜疤，是沒(méi)有設(shè)定的佃延，要自己設(shè)定现诀！ ）

query_cache_type ： ON是開(kāi)啟夷磕，OFF是關(guān)閉 。（5.5版本時(shí)仔沿，默認(rèn)是開(kāi)啟坐桩！ ）

（2）查看緩存狀態(tài)

show status like '%qcache%';

得到參數(shù)解釋：

Qcache_free_memory ：可以使用的空間，即剩余空間

Qcache_hits ：命中封锉，使用了多少次緩存

Qcache_inserts ：插入的緩存數(shù)量

Qcache_lowmem_prunes ：內(nèi)存不夠绵跷，放棄緩存的數(shù)量

Qcache_not_cached ：不緩存的數(shù)量

Qcache_queries_in_cache ：已經(jīng)緩存的數(shù)量

（3）開(kāi)啟緩存

set global query_cache_type = 1;

如果是5.7版本的緩存開(kāi)啟膘螟，請(qǐng)進(jìn)入配置文件：vim /etc/my.cnf

[mysqld]
prot    = 3306
user    = mysql
datadir = /usr/local/mysql/data/
socket  = /tmp/mysql/sock
query_cache_type = 1   #將此行寫(xiě)在這個(gè)段落的這個(gè)位置

修改完配置文件后記得重啟MySQL服務(wù)器！

（4）設(shè)定緩存的大小

set global query_cache_size = 1024 * 1024 * 80;

執(zhí)行完成后可以在show variables like '%query_cache%';中查詢出query_cache_size的值碾局。

設(shè)定好后荆残，每次執(zhí)行一次sql語(yǔ)句，都會(huì)在show variables like '%query_cache%';記錄中Qcache_inserts和Qcache_queries_in_cache`的值加1净当。

（5）重復(fù)執(zhí)行sql語(yǔ)句

重復(fù)執(zhí)行一條sql時(shí)内斯，會(huì)在show variables like '%query_cache%';中的Qcache_hits值加1， 重復(fù)執(zhí)行的像啼，所以增加了命中俘闯。

（6）修改sql語(yǔ)句

執(zhí)行的sql語(yǔ)句中只要任何地方有變化，都會(huì)增加緩存的值忽冻，哪怕之前寫(xiě)的是where真朗，之后是WHERE，這樣也會(huì)增加緩存數(shù)僧诚。多寫(xiě)一個(gè)空格也會(huì)增加遮婶。

總結(jié)：意味著SQL的緩存，是區(qū)分大小寫(xiě)的湖笨。

緩存是必須SQL一模一樣的蹭睡，才會(huì)認(rèn)定是同一條SQL。不然就會(huì)是新的緩存赶么。

因?yàn)槲覀兊腟QL的查詢結(jié)果進(jìn)行緩存的時(shí)候肩豁，存儲(chǔ)的數(shù)據(jù)格式是key=>value。這個(gè)key就是SQL語(yǔ)句HASH之后的值辫呻；value就是SQL的查詢結(jié)果清钥。sql的HASH結(jié)果，只要SQL有任何一點(diǎn)的變化放闺，這個(gè)HASH結(jié)果祟昭，都會(huì)不一樣的 。

（7）修改數(shù)據(jù)或者增加數(shù)據(jù)

更新一條sql語(yǔ)句時(shí)怖侦，執(zhí)行完成完成時(shí)篡悟，再查看緩存狀態(tài)，可以看到Qchche_queries_in_cache的值變少了匾寝。

說(shuō)明：當(dāng)表的SQL語(yǔ)句搬葬，被緩存的時(shí)候。然后這個(gè)表有任何的修改（更新艳悔，插入急凰，表結(jié)構(gòu)）都會(huì)清空這個(gè)表的緩存。

（8）強(qiáng)制不緩存

select sql_no_cache * from table_name猜年，其中sql_no_cache：告訴服務(wù)器不緩存抡锈，查詢結(jié)果疾忍。

在執(zhí)行完成后，緩存狀態(tài)里的Qcache_not_cached的值增加了床三。

（9）清空緩存

reset query cache;

（10）總結(jié)

sql緩存使用的地方特別少一罩，現(xiàn)在很多數(shù)據(jù)都要更新，或者添加數(shù)據(jù)撇簿。交互的內(nèi)容越來(lái)越多擒抛。

數(shù)據(jù)經(jīng)常發(fā)生變化的時(shí)候，不適合使用sql緩存补疑。

開(kāi)啟sql緩存歧沪，每次查詢請(qǐng)求，都會(huì)把sql語(yǔ)句作hash取值操作莲组。去緩存中查詢有沒(méi)有緩存诊胞。數(shù)據(jù)經(jīng)常變化，緩存常被清空锹杈，查詢的命中就低撵孤。整個(gè)去緩存的過(guò)程，就經(jīng)常變成無(wú)意義的操作竭望，浪費(fèi)了服務(wù)器資源邪码！

九、MySQL分區(qū)與分表

當(dāng)表數(shù)據(jù)達(dá)到一定的數(shù)量級(jí)時(shí)咬清。SQL語(yǔ)句的查詢會(huì)非常的慢闭专。 解決這個(gè)問(wèn)題，就要把數(shù)據(jù)拆分到不同的表里面去旧烧。這個(gè)時(shí)候影钉，就要使用分區(qū)或者是分表的技術(shù)。

1掘剪、分區(qū)與分表的區(qū)別

分區(qū)：mysql服務(wù)器幫助實(shí)現(xiàn)的數(shù)據(jù)分表功能平委，就是分區(qū)。

? mysql把數(shù)據(jù)存儲(chǔ)在拆分的不同表里面夺谁，對(duì)用戶是無(wú)感知的廉赔，還是像一張表一樣操作。

分表：是程序員匾鸥，自己實(shí)現(xiàn)的拆表蜡塌。把相同的數(shù)據(jù)，存儲(chǔ)到不同的表里面扫腺。就是分表岗照。

? 要分表的非常好村象，需要一定的算法功底笆环。

2攒至、分區(qū)的類型

類型說(shuō)明：

key ：指定id作為hash值，來(lái)進(jìn)行劃分躁劣。內(nèi)部會(huì)有一定算法實(shí)現(xiàn)的迫吐。 key可以不是整型 。

hash ：指定整型作為hash值账忘，來(lái)進(jìn)行劃分志膀。

range ：指定整型，作為范圍鳖擒，來(lái)進(jìn)行劃分溉浙。

list：指定整型，作為固定的區(qū)域蒋荚，來(lái)進(jìn)行劃分戳稽。

3、key分區(qū)操作

（1）創(chuàng)建表

create table goods_key(
    id int  unsigned not null auto_increment comment '主鍵id',
    name  varchar(32) not null comment '名稱',
    price  decimal(10,2) not null default 0 comment '價(jià)格',
    pubdate datetime not null comment '出廠時(shí)間',
    primary key (id)
)engine=myisam charset=utf8
partition by key (id) partitions 4;

執(zhí)行完成后在查看表文件時(shí)期升，可以看到文件goods_key.par為分區(qū)結(jié)構(gòu)惊奇。

（2）合并分區(qū)

alter table table_name coalesce partition 2;

當(dāng)執(zhí)行表操作的時(shí)候，一定要關(guān)注數(shù)據(jù)播赁。

表文件已經(jīng)只有2個(gè)了颂郎，但是數(shù)據(jù)還是存在的，這就證明了容为，我們的表數(shù)據(jù)已經(jīng)移動(dòng)了乓序。合并的時(shí)候，表數(shù)據(jù)會(huì)移動(dòng)坎背，移動(dòng)數(shù)據(jù)竭缝，就會(huì)有IO的操作。所以大家在數(shù)據(jù)量大的時(shí)候沼瘫，一定一定不要隨便的操作合并抬纸。在夜深人靜的時(shí)沒(méi)什么人訪問(wèn)網(wǎng)站的時(shí)候進(jìn)行這種操作。

（3）增加分區(qū)

alter table table_name add partition partitions 2;

說(shuō)明：新增加的2個(gè)表文件都有數(shù)據(jù)的耿戚，就證明添加的時(shí)候湿故，數(shù)據(jù)也會(huì)被移動(dòng)的。會(huì)重新的進(jìn)行數(shù)據(jù)的分區(qū)操作膜蛔。這樣的話坛猪，數(shù)據(jù)就會(huì)占用IO資源。謹(jǐn)慎操作皂股。

（4）移除分區(qū)

alter table table_name remove partitioning;

移除分區(qū)后墅茉，數(shù)據(jù)還在，但是分區(qū)的表文件都被移除了，只剩下原來(lái)普通的模式了就斤。

（5）總結(jié)

添加與合并：都會(huì)進(jìn)行數(shù)據(jù)的重新移動(dòng)悍募，這個(gè)時(shí)候，就會(huì)占用IO資源洋机。所以操作的時(shí)候坠宴，一定要謹(jǐn)慎操作。

移除的時(shí)候绷旗，會(huì)把分區(qū)刪除掉喜鼓。也會(huì)進(jìn)行數(shù)據(jù)的移動(dòng)的操作。

這三個(gè)操作衔肢，數(shù)據(jù)還會(huì)存在庄岖。

4、hash分區(qū)操作

（1）創(chuàng)建表

create table goods_hash(
    id int  unsigned not null auto_increment comment '主鍵id',
    name  varchar(32) not null comment '名稱',
    price  decimal(10,2) not null default 0 comment '價(jià)格',
    pubdate datetime not null comment '出廠時(shí)間',
    primary key (id,pubdate)
)engine=myisam charset=utf8
partition by hash (month(pubdate)) partitions 4;

（2）合并分區(qū)

alter table table_name coalesce partition 2;

說(shuō)明：合并的時(shí)候角骤，數(shù)據(jù)會(huì)進(jìn)行移動(dòng)顿锰。也就是io操作。合并操作启搂，并不會(huì)影響數(shù)據(jù)庫(kù)里面的數(shù)據(jù) 硼控。

（3）添加分區(qū)

alter table table_name add partition partitions 2;

說(shuō)明：新分區(qū)的文件里面也是包含了數(shù)據(jù)的。也就是說(shuō)當(dāng)有新分區(qū)的時(shí)候胳赌。數(shù)據(jù)會(huì)重新進(jìn)行分區(qū)操作牢撼。會(huì)占用IO資源。

（4）移除分區(qū)

alter table table_name remove partitioning;

（5）總結(jié)

合并與添加都會(huì)進(jìn)行數(shù)據(jù)重新分區(qū)的操作疑苫。這種操作會(huì)占用IO資源熏版。數(shù)據(jù)量大了就會(huì)非常的危險(xiǎn)。所以操作的時(shí)候捍掺，一定要注意撼短。

移除的時(shí)候也會(huì)進(jìn)行數(shù)據(jù)移動(dòng)操作。

上面三個(gè)操作挺勿，都會(huì)保證數(shù)據(jù)的正常曲横。

小結(jié)：

key | hash 分區(qū)：

添加分區(qū)：

? alter table table_name add partition partitions number（添加表的個(gè)數(shù)）

合并分區(qū)：

? alter table table_name coalesce partition number（合并表的個(gè)數(shù)）

移除分區(qū)：

? alter table table_name remove partitioning;

使用分區(qū)的字段，必須是主鍵不瓶！

分區(qū)的時(shí)候禾嫉，字段的選擇？

一般情況下蚊丐，要盡量讓分區(qū)的表熙参，每一張表的數(shù)量盡可能的保持一致！100W數(shù)據(jù)的表麦备，分成2張表孽椰，盡量保持是50W一張表昭娩！

在表結(jié)構(gòu)里面，什么樣的字段黍匾，能夠滿足這樣的需求栏渺，你就可以去選擇這個(gè)字段，來(lái)制作分區(qū)表膀捷！

5迈嘹、range分區(qū)操作

（1）創(chuàng)建表

create table goods_range(
    id int unsigned not null auto_increment comment '主鍵id',
    name  varchar(32) not null comment '名稱',
    price  decimal(10,2) not null default 0 comment '價(jià)格',
    pubdate datetime not null comment '出廠時(shí)間',
    primary key (id,pubdate)
)engine=myisam charset=utf8
partition by range (year(pubdate))(
    partition year60 values less than (1970),       //注意削彬，從這行開(kāi)始往下必須從小到大
    partition year70 values less than (1980),
    partition year80 values less than (1990),
    partition year90 values less than (2000)
);

創(chuàng)建完成后全庸，再進(jìn)行插入數(shù)據(jù)。insert into goods_range values (1,'htc', 3451.3,'1975-02-14 12:30:12');插入總共幾條條類似這種的語(yǔ)句融痛。

此時(shí)如果插入一個(gè)比分區(qū)范圍更大的值壶笼。比如insert into goods_range values (6,'nokia', 3451.3,'2018-07-24 12:30:12');此時(shí)會(huì)報(bào)錯(cuò)，因?yàn)?018不在分區(qū)數(shù)據(jù)里面雁刷。

重點(diǎn)說(shuō)明：我們的這個(gè)范圍分區(qū)設(shè)置的時(shí)候覆劈，一定要把所有可能的值，全部包含沛励。這樣插入數(shù)據(jù)的時(shí)候责语，才不會(huì)報(bào)錯(cuò)。

（2）設(shè)置一個(gè)最大的分區(qū)

包含所有的可能性：alter table table_name add partition(partition 分區(qū)名稱 values less than MAXVALUE);

eg：alter table goods_range add partition(partition yearmax values less than MAXVALUE);

再查看數(shù)據(jù)時(shí)目派，數(shù)據(jù)都存在坤候。

再重新插入insert into goods_range values (6,'nokia', 3451.3,'2018-07-24 12:30:12');此時(shí)就可以成功插入了。

注意：添加分區(qū)的時(shí)候企蹭，只能添加越來(lái)越大的白筹。

（3）刪除分區(qū)

alter table table_name drop partition 分區(qū)名稱 ;

eg：alter table goods_range drop partition year 70;

強(qiáng)調(diào)：刪除分區(qū)的時(shí)候，數(shù)據(jù)也會(huì)一并被刪除谅摄，所以請(qǐng)注意了徒河。

（4）移除分區(qū)

alter table table_name remove partitioning;

eg：alter table goods_range remove partitioning;

此時(shí)，分區(qū)文件已經(jīng)被刪除了送漠；只有普通的樣子了顽照。

（5）總結(jié)

刪除分區(qū)，會(huì)直接刪除掉數(shù)據(jù)。所以請(qǐng)操作之前，三思欺缘。

添加分區(qū)识虚，只能添加越來(lái)越大的分區(qū)。

移除分區(qū)蓬戚，數(shù)據(jù)是還存在的，就會(huì)存在IO資源的使用。

合并分區(qū)也是有的 何乎。

6句惯、list分區(qū)操作

（1）創(chuàng)建分區(qū)

create table goods_list(
    id int unsigned not null auto_increment comment '主鍵id',
    name  varchar(32) not null comment '名稱',
    price  decimal(10,2) not null default 0 comment '價(jià)格',
    pubdate datetime not null comment '出廠時(shí)間',
    primary key (id,pubdate)
)engine=myisam charset=utf8
partition by list (month(pubdate))(
    partition spring values in (3,4,5),
    partition summer values in (6,7,8),
    partition autumn values in (9,10,11),
    partition winter values in (12,1,2)
);

再進(jìn)行插入數(shù)據(jù)。

（2）刪除分區(qū)

alter table table_name drop partition 分區(qū)名稱;

eg：alter table goods_list droppartition winter;

刪除分區(qū)的時(shí)候支救，數(shù)據(jù)會(huì)一并被刪除的抢野。

（3）修復(fù)分區(qū)

修復(fù)一下剛剛刪除的"winter"分區(qū)，其實(shí)就是重新添加一下

alter table table_name add partition(partition winter values in (12,1,2));

此時(shí)各墨，雖然有重新創(chuàng)建了分區(qū)指孤，但是在刪除了數(shù)據(jù)，刪除了分區(qū)的話贬堵，數(shù)據(jù)是不會(huì)回來(lái)的恃轩，現(xiàn)在只是相同的名稱的分區(qū)，已經(jīng)和曾經(jīng)的分區(qū)不一樣了黎做。

（4）移除分區(qū)

alter table table_name remove partitioning;

此時(shí)叉跛，表文件恢復(fù)到了沒(méi)有分區(qū)時(shí)的樣子了。

（5）總結(jié)

刪除分區(qū)：就是把分區(qū)表刪除蒸殿，表刪除了筷厘，里面的數(shù)據(jù)也就一起刪除了。

添加分區(qū)：添加的分區(qū)是一個(gè)全新的宏所，里面是沒(méi)有任何數(shù)據(jù)的酥艳。

移除分區(qū)，會(huì)進(jìn)行數(shù)據(jù)的移動(dòng)爬骤，會(huì)占用IO資源充石。

小結(jié)：

range 與 list 分區(qū)

添加分區(qū)：

alter table table_name add partition (

? partition 分區(qū)名稱 values less than (整型)

? 或者

? partition 分區(qū)名稱 values in (整型,整型,整型)

)

刪除分區(qū)：

? alter table table_name drop partition 分區(qū)名

移除分區(qū)：

? alter table table_name remove partitioning;

注意：移除分區(qū)是會(huì)進(jìn)行數(shù)據(jù)的移動(dòng)的；所以請(qǐng)注意盖腕，一定要在夜深人靜的時(shí)候操作赫冬。

刪除分區(qū)與添加分區(qū)，都不會(huì)進(jìn)行數(shù)據(jù)操作溃列，所以請(qǐng)操作的時(shí)候三思而行劲厌！

range與list也有合并分區(qū)；合并的時(shí)候听隐，都是會(huì)進(jìn)行數(shù)據(jù)移動(dòng)的补鼻！

7、水平分表

數(shù)據(jù)有很多的時(shí)候雅任，存儲(chǔ)在多張表里面风范；這些表的表結(jié)構(gòu)肯定是一模一樣的。

表結(jié)構(gòu)一模一樣的表沪么，在存儲(chǔ)數(shù)據(jù)的時(shí)候硼婿，我們就可以理解成水平分表！

數(shù)據(jù)有1000W的時(shí)候禽车，我們進(jìn)行水平分表寇漫；分成2張表刊殉；

策略一：前500W數(shù)據(jù)，分在表1里面州胳；后500W數(shù)據(jù)记焊，分在表2里面；

策略二：id是單數(shù)的栓撞，分在表1里面遍膜；id是雙數(shù)的，分在表2里面瓤湘。

完成策略一：首先1000W的數(shù)據(jù)瓢颅；新增數(shù)據(jù)。

? 讀取曾經(jīng)的數(shù)據(jù)岭粤，跑前500W在表1里面惜索；跑后500W在表2里面特笋；

? 新增數(shù)據(jù)：需要一直維護(hù)一個(gè)id值剃浇，通過(guò)id值去判斷，我們的數(shù)據(jù)應(yīng)該存儲(chǔ)在那張表里面了猎物。假如1001W虎囚，這個(gè)時(shí)候就存儲(chǔ)在表3里面；到達(dá)1501W蔫磨，這個(gè)時(shí)候就存儲(chǔ)在表4里面淘讥。

查詢數(shù)據(jù)的時(shí)候：

? 查詢數(shù)據(jù)的，必須給數(shù)據(jù)維護(hù)好id值堤如，查詢的時(shí)候蒲列，盡量使用id查詢，通過(guò)id值搀罢，就能區(qū)分它在哪一個(gè)表里面了蝗岖！

? merge引擎；它可以把myisam引擎的多張表連接起來(lái)榔至，你查詢的時(shí)候抵赢，直接查詢這張表，它就會(huì)把所有的myisam引擎的表唧取，都遍歷一次铅鲤，這樣就獲得了數(shù)據(jù)。

? 非myisam引擎的表枫弟，就要使用中件間（第三方工具）實(shí)現(xiàn)同merge引擎一樣的效果邢享！

完成策略二：首先1000W的數(shù)據(jù)；新增數(shù)據(jù)淡诗。

? 讀取曾經(jīng)的數(shù)據(jù)骇塘，跑數(shù)據(jù)id掸犬，id是單數(shù)的就存儲(chǔ)在表1里面；跑數(shù)據(jù)id绪爸，id是雙數(shù)的就存儲(chǔ)在表2里面湾碎。

? 新增數(shù)據(jù)：需要一直維護(hù)一個(gè)id值，通過(guò)id值去判斷奠货， 只要id是單數(shù)就存儲(chǔ)在表1里面介褥；id是雙數(shù)就存儲(chǔ)在表2里面。

面試车萃铮考：有一個(gè)用戶信息表柔滔，數(shù)據(jù)量有點(diǎn)大了，我們要進(jìn)行分表萍虽，應(yīng)該怎么劃分?

? id 睛廊，姓名，年齡杉编，性別超全，登錄時(shí)間，籍貫邓馒，time

用戶信息表：

? 1）新用戶注冊(cè)的時(shí)候嘶朱，注冊(cè)信息

? 2）用戶登錄的時(shí)候，登錄獲得信息

用戶信息表光酣，經(jīng)常都是回答使用用戶名進(jìn)行劃分的疏遏。

? 用戶名是一個(gè)字符串，可以通過(guò)hash轉(zhuǎn)成整型救军，整型在進(jìn)行取余就可以了财异。

要拿的值，一定是盡量是唯一的唱遭，不要重復(fù)的戳寸，不可修改的。

完成一下策略二：

分表的數(shù)據(jù)先定義好：2張表胆萧；

分表的算法要定義好：id進(jìn)行取余庆揩；

通過(guò)算法，就會(huì)發(fā)現(xiàn)表名可以設(shè)計(jì)的有點(diǎn)意思：

id = 3跌穗；

3 % 2 = 1订晌；

id = 4；

4 % 2 = 0蚌吸；

它的值锈拨，只有0或者是1；

確定一個(gè)表前綴羹唠，然后加上這個(gè)取余的結(jié)果奕枢，就可以組裝成表名娄昆！

表前綴是:goods_ ; 取余結(jié)果：0 ;組裝：goods_0（表名）

（1）創(chuàng)建好2個(gè)表

create table goods_0(
    id int unsigned not null auto_increment comment '主鍵id',
    name  varchar(32) not null comment '名稱',
    price  decimal(10,2) not null default 0 comment '價(jià)格',
    pubdate datetime not null default '0000-00-00 00:00:00'  comment '出廠時(shí)間',
    primary key (id)
)engine=myisam charset=utf8;

create table goods_1(
    id int unsigned not null auto_increment comment '主鍵id',
    name  varchar(32) not null comment '名稱',
    price  decimal(10,2) not null default 0 comment '價(jià)格',
    pubdate datetime not null default '0000-00-00 00:00:00'  comment '出廠時(shí)間',
    primary key (id)
)engine=myisam charset=utf8;

（2）創(chuàng)建維護(hù)id的表

這個(gè)id一定要是自增長(zhǎng)的！

create table goods_id_incr(
    id int unsigned not null auto_increment comment '主鍵id',
    primary key (id)
)engine=myisam charset=utf8;

插入數(shù)據(jù)：insert into goods_id_incr values (null);

（3）代碼實(shí)現(xiàn)

//連接數(shù)據(jù)庫(kù)
$db = mysqli_connect('127.0.0.1', 'root', '123456', 'test');
//表前綴
$table_pre = 'goods_';
//表的數(shù)量
$table_num = 2;
//有數(shù)據(jù)來(lái)了
$data = ['name' => 'chuizi', 'price' => 3299, 'pubdate' => '2018-05-15 19:00:00'];
//數(shù)量時(shí)沒(méi)有ID的缝彬，要給它準(zhǔn)備一個(gè)id值
$i_sql = "insert into goods_id_incr values (null)";
mysqli_query($db, $i_sql);
//獲得生成的id值
$auto_id = mysqli_insert_id($id);
//取余計(jì)算表明
$table_last = $auto_id % $table_num;
//拼接表名
$table_name = $table_pre . $table_last;
//寫(xiě)入數(shù)據(jù)的sql
$sql = "insert into $table_name values ('{$auto_id}', '{$data['name']}', '{$data['price']}', '{$data['pubdate']}')";
mysqli_query($db, $sql);

（4）發(fā)現(xiàn)一個(gè)問(wèn)題：goods_id_incr

查詢一下good_id_incr表萌焰，得知有5條數(shù)據(jù)，那么以后會(huì)有多少條呢谷浅？

? 這些數(shù)據(jù)扒俯，只是在組裝id的時(shí)候，才會(huì)有用一疯。之后都將沒(méi)有作用撼玄。所以我們應(yīng)該清空它！

delete from goods_id_incr; ：清空表數(shù)據(jù)墩邀，不清空自動(dòng)增長(zhǎng)掌猛！

truncate goods_id_incr : 清空表數(shù)據(jù)，并且把自動(dòng)增長(zhǎng)清空眉睹，所以不能使用它荔茬。

8、垂直分表

表里面有很多字段辣往，有一些是常用的兔院，有一些是不常用的殖卑。

把常用的分成一張表站削，把不常用的分成一張表。

假如：用戶表

id name age sex phone email wechat address content .....

不常用的：

id fid address content .....

補(bǔ)充說(shuō)明：垂直分表的真實(shí)內(nèi)涵

? 我們的數(shù)據(jù)是存儲(chǔ)到硬盤(pán)里面的孵稽，那么一行數(shù)據(jù)的內(nèi)容都是存儲(chǔ)在一起的许起。而IO一次性讀取的數(shù)據(jù)，也是整塊讀取的菩鲜。當(dāng)io讀取一次的時(shí)候园细，里面有很多數(shù)據(jù)，是不需要的接校。這次讀取就浪費(fèi)太多資源猛频。

? 進(jìn)行垂直分表。那一行數(shù)據(jù)里面蛛勉，都是需要的÷寡埃現(xiàn)在IO一次性讀取出來(lái)的內(nèi)容，都是需要的诽凌。

十毡熏、鎖

1、什么是鎖

就是對(duì)數(shù)據(jù)的訪問(wèn)控制的一種技術(shù)侣诵。

2痢法、mysql里面的鎖的幾種形式

共享鎖（讀鎖）：加了共享鎖的狱窘，大家都可以讀它。

排它鎖（寫(xiě)鎖）：加了排它鎖的财搁，只有加鎖的這個(gè)人可以去讀寫(xiě)它蘸炸。

鎖的范圍：

? 表鎖：就是把一張表給鎖住，就是表鎖尖奔。myisam引擎幻馁，實(shí)現(xiàn)的就是表鎖。表鎖的顆粒大越锈，加鎖快仗嗦，并發(fā)訪問(wèn)低。

? 行鎖：就是把一張表里面的一行鎖住甘凭，就是行鎖稀拐。innodb引擎，實(shí)現(xiàn)的就是行鎖丹弱，顆粒小德撬，加鎖慢，并發(fā)訪問(wèn)高躲胳。

加鎖的時(shí)機(jī)：

? 悲觀鎖：當(dāng)我想使用數(shù)據(jù)的時(shí)候蜓洪，我就認(rèn)為別人有可能會(huì)使用，我就趕緊加鎖坯苹。

? 樂(lè)觀鎖：當(dāng)我想使用數(shù)據(jù)的時(shí)候隆檀，認(rèn)為別人不會(huì)使用。那我就修改的時(shí)候才加鎖粹湃。

當(dāng)我們使用樂(lè)觀鎖的時(shí)候恐仑，想要去修改它。這個(gè)時(shí)候为鳄，我們要讀取數(shù)據(jù)出來(lái)裳仆。改變這些數(shù)據(jù)。

改變之后孤钦，就要更新歧斟。但是在更新之前檢查一下version號(hào)是否一致。一致就更新偏形。不一致静袖，就先重新獲取一下數(shù)據(jù)。然后修改數(shù)據(jù)在更新壳猜。

鎖沖突：

當(dāng)前用戶進(jìn)行加鎖的時(shí)候勾徽，其它用戶加鎖會(huì)出現(xiàn)鎖等待的情況。

并發(fā)訪問(wèn)的情況：

A ：對(duì)ID是1的加鎖，執(zhí)行訪問(wèn)ID是2的喘帚，出現(xiàn)鎖等待

B ：對(duì)ID是2的加鎖畅姊，執(zhí)行訪問(wèn)ID是1的，出現(xiàn)鎖等待

這個(gè)就是鎖沖突吹由。

3若未、表鎖myisam引擎（表鎖）

讀鎖：

? 開(kāi)始加鎖：lock tables table_name read

? 解鎖：unlock tables

寫(xiě)鎖：

? 開(kāi)始加鎖：lock tables table_name write

? 解鎖：unlock tables

開(kāi)始實(shí)踐：準(zhǔn)備2個(gè)用戶！

說(shuō)明：用戶是以session來(lái)區(qū)分的倾鲫，不是以用戶名來(lái)分區(qū)的粗合。

讀鎖：

? 加鎖：lock tables myisam read;

? 當(dāng)前用戶：讀操作！

? 當(dāng)前用戶再操作其它表時(shí)乌昔，會(huì)報(bào)錯(cuò)隙疚。你給誰(shuí)加了鎖，你就要操作哪張表磕道，直到解鎖供屉。

? 其他用戶：讀操作時(shí)是可以進(jìn)行讀的。

? 但是進(jìn)行寫(xiě)操作時(shí)溺蕉，會(huì)出現(xiàn)鎖等待伶丐。

? 當(dāng)前用戶：寫(xiě)操作。此時(shí)會(huì)報(bào)錯(cuò)疯特，寫(xiě)操作是不可以執(zhí)行的哗魂，讀的鎖，不能寫(xiě)漓雅。

? 解鎖：unlock tables;

總結(jié)：myisam表的讀鎖录别，滿足所有用戶可讀；所有用戶不可寫(xiě)故硅。

寫(xiě)鎖：

? 加鎖：lock tables myisam write;

? 當(dāng)前用戶進(jìn)行讀操作時(shí)可以進(jìn)行庶灿，但是其他用戶在此時(shí)進(jìn)行讀、寫(xiě)操作時(shí)會(huì)進(jìn)行鎖等待吃衅。

? 說(shuō)明：其他用戶是不能再寫(xiě)鎖的時(shí)候操作讀與寫(xiě)的。

? 當(dāng)前用戶：寫(xiě)操作時(shí)可以執(zhí)行腾誉。

? 解鎖：unlock tables;

總結(jié)：寫(xiě)鎖是當(dāng)前用戶可以讀與寫(xiě)的徘层。其他用戶是不可以讀不可以寫(xiě)的。

4利职、行鎖innodb引擎（行鎖）

事務(wù)（Transaction）及其ACID屬性：

? 原子性（Atomicity）：事務(wù)是一個(gè)原子操作單元趣效，其對(duì)數(shù)據(jù)的修改，要么全都執(zhí)行猪贪，要么全都不執(zhí)行跷敬。

? 一致性（Consistent）：事務(wù)開(kāi)始和完成，數(shù)據(jù)都必須保持一致?tīng)顟B(tài)热押。這意味著所有相關(guān)的數(shù)據(jù)規(guī)則都必須應(yīng)用于事務(wù)的修改西傀，以保持?jǐn)?shù)據(jù)的完整性斤寇；事務(wù)結(jié)束時(shí)，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)也都必須是正確的拥褂。

? 隔離性（Isolation）：數(shù)據(jù)庫(kù)系統(tǒng)提供一定的隔離機(jī)制娘锁，保證事務(wù)在不受外部并發(fā)操作影響的“獨(dú)立”環(huán)境執(zhí)行。這意味著事務(wù)處理過(guò)程中的中間狀態(tài)對(duì)外部是不可見(jiàn)的饺鹃，反之亦然莫秆。

? 持久性（Durable）：事務(wù)完成之后，它對(duì)于數(shù)據(jù)的修改是永久性的悔详，即使出現(xiàn)系統(tǒng)故障也能夠保持镊屎。

事務(wù)級(jí)別有所不同：4個(gè)級(jí)別！

事務(wù)里面操作：

開(kāi)始事務(wù)：

? begin：

結(jié)束事務(wù)：

? commit：

讀鎖：sql + lock in share mode

寫(xiě)鎖：sql + for update

當(dāng)前用戶加個(gè)讀鎖

開(kāi)始事務(wù)：begin;

當(dāng)前用戶：讀鎖select * from innodb where id = 1 lock in share mode'

其他用戶：讀鎖select * from innodb where id = 1 lock in share mode;

其他用戶：寫(xiě)操作update innodb set name = '6666' where id = 1;此時(shí)出現(xiàn)了鎖等待茄螃。

其他用戶：寫(xiě)操作非加鎖的行update innodb set name where id = 2;杯道，執(zhí)行成功，當(dāng)前用戶確實(shí)是加的行鎖责蝠。

當(dāng)前用戶：寫(xiě)操作：update innodb set name = '5555' where id = 1;

在innodb引擎的時(shí)候党巾，在加了讀鎖的時(shí)候，依賴能夠?qū)懖僮鞒晒λ健Ｒ驗(yàn)楫?dāng)你寫(xiě)操作的時(shí)候齿拂，自動(dòng)就轉(zhuǎn)成了寫(xiě)鎖了。

寫(xiě)鎖是排它的肴敛。所以使用其他用戶署海，查詢一下：select * from innodb where id = 1 lock in share mode;，此時(shí)出現(xiàn)鎖等待医男。

提交一下事務(wù)：commit;

總結(jié)：當(dāng)Innodb引擎加讀鎖的時(shí)候砸狞，是共享鎖。大家都可以讀镀梭。其他用戶不能寫(xiě)刀森。如果當(dāng)前用戶寫(xiě)數(shù)據(jù)了。鎖就會(huì)修改成寫(xiě)鎖报账，具有排它性研底。

當(dāng)前用戶加個(gè)寫(xiě)鎖

開(kāi)始事務(wù)：begin;

當(dāng)前用戶可以正常讀操作，但是其他用戶進(jìn)行讀操作和寫(xiě)操作時(shí)出現(xiàn)鎖等待透罢。

當(dāng)前用戶可以正常寫(xiě)操作榜晦。

提交事務(wù)：commit;

總結(jié)：寫(xiě)鎖是當(dāng)前用戶可以寫(xiě)可以讀；其它用戶是排它性的羽圃，不能讀不能寫(xiě)乾胶。

事務(wù)：就是拷貝了一份，單獨(dú)的拿出來(lái)操作。在事務(wù)沒(méi)有提交的時(shí)候识窿，事務(wù)與源數(shù)據(jù)斩郎，是拷貝關(guān)系。

innodb總結(jié)：innodb是有條件的行鎖腕扶，當(dāng)使用索引的字段值的時(shí)候孽拷，才能加上行鎖，非索引字段加的是表鎖半抱。

表鎖是mysql服務(wù)器實(shí)現(xiàn)的脓恕，不是innodb引擎實(shí)現(xiàn)的。所有當(dāng)有表鎖沖突的時(shí)候窿侈，我們的innodb引擎沒(méi)有辦法解決這個(gè)問(wèn)題炼幔。

行鎖沖突的時(shí)候，innodb引擎會(huì)自動(dòng)幫助我們解決這個(gè)問(wèn)題史简！

查看表結(jié)構(gòu)：有一個(gè)復(fù)合索引KEY name_fu (name, age, sex)

刪除復(fù)合索引：alter table innodb drop key name_fu;

開(kāi)始事務(wù)：begin;

當(dāng)前用戶：寫(xiě)鎖乃秀。select * from innodb where name = '8888' for update;

name字段現(xiàn)在沒(méi)有索引，所以加的是表鎖

可以測(cè)試一下：其他用戶進(jìn)行讀操作圆兵，非name=8888的字段查詢跺讯，select * from innodb where name = 'xiao15' lock in share mode;，查詢非id=1的出現(xiàn)了鎖等待殉农，就證明了加了表鎖刀脏。

提交事務(wù)：commit;

給name字段創(chuàng)造一個(gè)普通索引：alter table innodb add key name(name);

開(kāi)始事務(wù)：begin;

當(dāng)前用戶：寫(xiě)鎖。select * from innodb where id = 1 for update;

說(shuō)明：現(xiàn)在的name字段是有普通索引的超凳，所以加的是行鎖愈污。

再測(cè)試一下：其他用戶進(jìn)行非加鎖行操作：select * from innodb where name = 'xiao15' lock in share mode;

數(shù)據(jù)是可以查出。

說(shuō)明：已經(jīng)證明了轮傍，非加鎖行暂雹，都可以正常操作。

其他用戶：對(duì)加鎖行操作 時(shí)创夜，出現(xiàn)了鎖等待

總結(jié)：只有加鎖的行杭跪，其他用戶操作的時(shí)候，才出現(xiàn)了鎖等待挥下。就證明了揍魂。innodb的表鎖，是在使用的時(shí)候棚瘟，沒(méi)有索引的情況下產(chǎn)生的。

提交事務(wù)：commit;

當(dāng)使用范圍的時(shí)候喜最。只要范圍的都會(huì)被加鎖偎蘸！

id > 10 ; 鎖不是絕對(duì)的。這個(gè)和事務(wù)的級(jí)別有關(guān)系。

5迷雪、php實(shí)現(xiàn)鎖

需求：把訪問(wèn)人數(shù)限书，統(tǒng)計(jì)到我們表里面！

（1）創(chuàng)建一張表章咧，記錄人數(shù)

create table lock_num(
    id tinyint(1) unsigned not null comment 'id',
    num int unsigned not null
)engine=myisam charset=utf8;

num字段就是把我們有多少人訪問(wèn)倦西，把統(tǒng)計(jì)的結(jié)果，存儲(chǔ)在這里赁严！

（2）提前數(shù)據(jù)預(yù)熱

insert into lock_num values(1,0);

很多時(shí)候扰柠，我們都需要把數(shù)據(jù)提前預(yù)熱；因?yàn)槲覀兊脑L問(wèn)一但來(lái)了疼约，這個(gè)時(shí)候卤档，我們沒(méi)有準(zhǔn)備好數(shù)據(jù)，就只能讓mysql服務(wù)器硬抗程剥，抗不住的劝枣。

（3）代碼實(shí)現(xiàn)

//文件名：lock.php
//配置mysql服務(wù)器
$db = mysqli_connect('127.0.0.1', 'root', '123456', 'test');
mysqli_query($db, "set names utf8");
//等待一個(gè)人的到來(lái)
$person = 1;
//把曾經(jīng)的訪問(wèn)數(shù)據(jù)取出來(lái)
$s_sql = "select num from lock_num where id = 1";
$res = mysqli_qyery($db, $s_sql);
$data = mysqli_fetch_assoc($res);
$num = $data['num']; //曾經(jīng)的訪問(wèn)總?cè)藬?shù)
//曾經(jīng)的人與現(xiàn)在訪問(wèn)人相加
$num = $num + $person;
//更新到數(shù)據(jù)庫(kù)里面
//update自帶寫(xiě)鎖碟狞，select自帶讀鎖皇型，但是它們的作用范圍都是一行語(yǔ)句
//所以有多行語(yǔ)句，或者要處理sql語(yǔ)句的記過(guò)躯嫉，再操作就需要手動(dòng)的加鎖
$u_sql = "update lock_num set num = $num where id = 1";
mysqli_query($db, $u_sql);

（4）模擬多人并發(fā)訪問(wèn)

在linux中執(zhí)行/working/httpd-2.4/bin/ab -n 500 -c 50 http://127.0.0.1/lock.php

執(zhí)行完后查看數(shù)據(jù)庫(kù)搂擦，發(fā)現(xiàn)：并發(fā)問(wèn)題稳诚，當(dāng)同時(shí)有多個(gè)人要操作同一個(gè)地方的數(shù)據(jù)的時(shí)候。如果不加鎖盾饮，直接操作采桃。有的就會(huì)被抹掉。

解決這個(gè)問(wèn)題丘损，就只能加鎖普办！

（5）實(shí)現(xiàn)加鎖功能

讓請(qǐng)求實(shí)現(xiàn)我們的效果：

//文件名：lock.php
//配置mysql服務(wù)器
$db = mysqli_connect('127.0.0.1', 'root', '123456', 'test');
mysqli_query($db, "set names utf8");
//等待一個(gè)人的到來(lái)
$person = 1;
//加好寫(xiě)鎖
mysqli_query($db, 'lock tables lock_num write');
//把曾經(jīng)的訪問(wèn)數(shù)據(jù)取出來(lái)
$s_sql = "select num from lock_num where id = 1";
$res = mysqli_qyery($db, $s_sql);
$data = mysqli_fetch_assoc($res);
$num = $data['num']; //曾經(jīng)的訪問(wèn)總?cè)藬?shù)
//曾經(jīng)的人與現(xiàn)在訪問(wèn)人相加
$num = $num + $person;
//更新到數(shù)據(jù)庫(kù)里面
//update自帶寫(xiě)鎖，select自帶讀鎖徘钥，但是它們的作用范圍都是一行語(yǔ)句
//所以有多行語(yǔ)句衔蹲，或者要處理sql語(yǔ)句的記過(guò)，再操作就需要手動(dòng)的加鎖
$u_sql = "update lock_num set num = $num where id = 1";
mysqli_query($db, $u_sql);
//操作完成呈础，解鎖
mysqli_query($db, 'unlock tables');

再用ab來(lái)測(cè)試一下：/working/httpd-2.4/bin/ab -n 500 -c 50 http://127.0.0.1/lock.php

結(jié)果：加了寫(xiě)鎖的舆驶，都是串行的。速度就會(huì)變慢的而钞。為了數(shù)據(jù)的完整性沙廉，變慢也必須這樣做。

6臼节、文件鎖

手冊(cè)內(nèi)容：

參數(shù)：

handle
文件系統(tǒng)指針撬陵，是典型地由 fopen() 創(chuàng)建的 resource(資源)珊皿。
operation
operation 可以是以下值之一：
? LOCK_SH取得共享鎖定（讀取的程序）。
? LOCK_EX 取得獨(dú)占鎖定（寫(xiě)入的程序巨税。
? LOCK_UN 釋放鎖定（無(wú)論共享或獨(dú)占）蟋定。

? 如果不希望 flock() 在鎖定時(shí)堵塞，則是 LOCK_NB（Windows 上還不支持）草添。

//文件名：lock.php
//配置mysql服務(wù)器
$db = mysqli_connect('127.0.0.1', 'root', '123456', 'test');
mysqli_query($db, "set names utf8");
//等待一個(gè)人的到來(lái)
$person = 1;
//加好寫(xiě)鎖
$fp = fopen('./lock.txt', 'w');
if(flock($fp, LOCK_EX))
{
    //把曾經(jīng)的訪問(wèn)數(shù)據(jù)取出來(lái)
    $s_sql = "select num from lock_num where id = 1";
    $res = mysqli_qyery($db, $s_sql);
    $data = mysqli_fetch_assoc($res);
    $num = $data['num']; //曾經(jīng)的訪問(wèn)總?cè)藬?shù)
    //曾經(jīng)的人與現(xiàn)在訪問(wèn)人相加
    $num = $num + $person;
    //更新到數(shù)據(jù)庫(kù)里面
    //update自帶寫(xiě)鎖驶兜，select自帶讀鎖，但是它們的作用范圍都是一行語(yǔ)句
    //所以有多行語(yǔ)句远寸，或者要處理sql語(yǔ)句的記過(guò)抄淑，再操作就需要手動(dòng)的加鎖
    $u_sql = "update lock_num set num = $num where id = 1";
    mysqli_query($db, $u_sql);
    flock($fp, LOCK_UN);
}
fclose($fp);

再用ab來(lái)測(cè)試一下：/working/httpd-2.4/bin/ab -n 500 -c 50 http://127.0.0.1/lock.php

確實(shí)添加進(jìn)數(shù)據(jù)了。

再查看項(xiàng)目目錄：確實(shí)出現(xiàn)了lock.txt這個(gè)文件

文件鎖只能在單臺(tái)服務(wù)器使用而晒。

mysql鎖可以在多臺(tái)服務(wù)器使用蝇狼。

十一、主從服務(wù)器

1倡怎、什么是主從服務(wù)器

主從服務(wù)器：一臺(tái)服務(wù)器設(shè)置成為主服務(wù)器迅耘，一臺(tái)服務(wù)器設(shè)置成為從服務(wù)器。

mysql服務(wù)器的抗壓能力有限监署。

假設(shè)一臺(tái)服務(wù)器最多能夠接受的并發(fā)請(qǐng)求數(shù)是500個(gè)颤专。當(dāng)并發(fā)超過(guò)500的時(shí)候，很顯然一臺(tái)服務(wù)器就不能正常工作了钠乏，就需要更多的服務(wù)器來(lái)支持栖秕。

保證服務(wù)器之間的數(shù)據(jù)及時(shí)同步問(wèn)題，需要使用主從的技術(shù)解決方案實(shí)現(xiàn)數(shù)據(jù)同步晓避。

眾多業(yè)務(wù)之中簇捍，查詢請(qǐng)求為主，修改與寫(xiě)請(qǐng)求相對(duì)來(lái)說(shuō)非常少俏拱。

應(yīng)對(duì)項(xiàng)目中的實(shí)際情況暑塑。設(shè)置一臺(tái)服務(wù)器專門接受修改與寫(xiě)的請(qǐng)求，這臺(tái)服務(wù)器就會(huì)被設(shè)置成主服務(wù)器锅必；設(shè)置一臺(tái)服務(wù)器專門接受查詢的請(qǐng)求事格，這臺(tái)服務(wù)器就會(huì)被設(shè)置成從服務(wù)器。

主服務(wù)器的數(shù)據(jù)變更時(shí)搞隐，快速的同步到從服務(wù)器驹愚，保證主從服務(wù)器的數(shù)據(jù)是相同的。這樣的架構(gòu)就是主從架構(gòu)劣纲，同時(shí)也實(shí)現(xiàn)了讀寫(xiě)分離逢捺。

2、主從服務(wù)器的實(shí)現(xiàn)原理

主服務(wù)器：

1）開(kāi)啟二進(jìn)制日志癞季，讓修改與寫(xiě)操作的SQL語(yǔ)句蒸甜，記錄到bin日志（二進(jìn)制日志）棠耕。

2）設(shè)置集群的唯一id余佛。

3）創(chuàng)建一個(gè)從服務(wù)器登錄主服務(wù)器的賬戶柠新。

從服務(wù)器：

1）設(shè)置集群的唯一id。

2）使用主服務(wù)器提供的賬戶與其它信息連接上主服務(wù)器辉巡。

3）從服務(wù)器的I/O進(jìn)程恨憎，去主服務(wù)器的bin日志里面讀取內(nèi)容，然后保存到從服務(wù)器的中繼日志里面郊楣。

4）從服務(wù)器的SQL進(jìn)程憔恳，去中繼日志里面讀取內(nèi)容，把讀取到的 SQL語(yǔ)句在服務(wù)器上面執(zhí)行一次净蚤。

注意：主從服務(wù)器開(kāi)啟同步的時(shí)候钥组，一定要讓主從服務(wù)器在同步之前，所有數(shù)據(jù)保持一致今瀑。

例如：主服務(wù)器的寫(xiě)SQL語(yǔ)句程梦，同步保存到從服務(wù)器。從服務(wù)器的SQL進(jìn)程執(zhí)行該SQL語(yǔ)句橘荠，發(fā)現(xiàn)沒(méi)有這張表屿附，就會(huì)報(bào)錯(cuò)，同步操作就會(huì)異常中斷哥童！

注意事項(xiàng)：

1）主從服務(wù)器的網(wǎng)絡(luò)是能夠連通的

2）主服務(wù)器開(kāi)啟端口允許訪問(wèn)或者關(guān)閉防火墻

3挺份、MySQL服務(wù)器添加（授權(quán)）賬號(hào)

添加與刪除賬戶

啟用新的賬戶，并且設(shè)定密碼與權(quán)限贮懈！

grant 權(quán)限 on 庫(kù).表 to 賬號(hào)@IP地址 identified by 密碼;

權(quán)限：select ：查詢權(quán)限匀泊；all ：所有權(quán)限

庫(kù)： 指定某一個(gè)庫(kù)，使用*代表所有的庫(kù)

表： 指定某一個(gè)表朵你，使用*代表所有的表

賬號(hào)：設(shè)置數(shù)據(jù)庫(kù)里面沒(méi)有的賬戶名

IP地址：限制賬戶在指定IP地址上面各聘，才能使用賬戶登錄系統(tǒng)

? 192.168.182. 53 ：只能是擁有這個(gè)IP地址的電腦，才能使用它對(duì)應(yīng)的賬戶登錄系統(tǒng) 撬呢。

? % :代表所有的IP地址伦吠，有的版本不包含127.0.0.1

刪除賬戶：drop user '用戶名'@'ip地址';

如果刪除賬戶之前該賬戶已經(jīng)登錄MySQL了，在刪除之后如果沒(méi)有登出就還是可以繼續(xù)使用魂拦，一旦登出就不能再使用了毛仪。

bin-log日志

開(kāi)啟bin-log日志功能

找到MySQL的配置文件：/etc/my.cnf

打開(kāi)它并修改內(nèi)容：

[mysqld]
log-bin         = filename  #這個(gè)是文件名
server-id       = 1         #一定要設(shè)置一個(gè)serverid
port            = 3306
user            = mysql
datadir         = /usr/local/mysql/data #bin日志是保存在這個(gè)目錄下面，不能有重名
socket          = /tmp/mysql.sock
query_cache_type = 1

修改完后重啟MySQL芯勘，每一次重啟都會(huì)生成一個(gè)新的bin日志文件

和bin-log日志相關(guān)的一些函數(shù)

命令生成新的日志文件

在MySQL下輸入：flush logs;

說(shuō)明：每一個(gè)bin日志文件箱靴，都會(huì)因?yàn)殚L(zhǎng)時(shí)間存儲(chǔ)，文件足夠大了荷愕。文件大了衡怀，就應(yīng)該生成一個(gè)新的文件棍矛，把曾經(jīng)的舊文件，備份起來(lái)抛杨，存儲(chǔ)好够委。以備以后使用 。

刪除全部日志文件怖现，生成新日志文件

reset master

使用這個(gè)命令茁帽。是刪除原來(lái)的所有二進(jìn)制日志文件。生成一個(gè)全新的二進(jìn)制日志文件屈嗤。

查看日志狀態(tài)

show master status;

4潘拨、可以使用log-bin日志完成數(shù)據(jù)恢復(fù)

bin日志里面，記錄的都是sql語(yǔ)句饶号。如果數(shù)據(jù)丟失铁追，再把里面的sql語(yǔ)句拿出來(lái)執(zhí)行，就實(shí)現(xiàn)數(shù)據(jù)恢復(fù)茫船。

說(shuō)明：開(kāi)啟二進(jìn)制日志之后琅束，修改或者寫(xiě)的SQL語(yǔ)句，才會(huì)記錄到bin日志里面透硝。曾經(jīng)的修改或者寫(xiě)的SQL語(yǔ)句狰闪，都已經(jīng)是過(guò)眼云煙！

產(chǎn)生二進(jìn)制日志數(shù)據(jù)

執(zhí)行修改或者寫(xiě)的SQL語(yǔ)句

寫(xiě)SQL：insert into innodb values (null, 'xiao12', 18, 1, 'fjsd');

更新SQL：update innodb set name = '123456654321' where id = 1;

查看二進(jìn)制日志內(nèi)容：工具應(yīng)用

找到查看工具：/usr/local/mysql/bin/目錄下的mysqlbinlog文件濒生。

使用一下：/usr/local/mysql/bin/mysqlbinlog --base64-output=decode-rows -v /usr/local/mysql/data/bin-log.000001

查看二進(jìn)制日志內(nèi)容：命令應(yīng)用

show binlog events;用于查看當(dāng)前正在使用的二進(jìn)制日志文件埋泵。

查看指定的二進(jìn)制文件：show binlog events in '二進(jìn)制文件文件名';

這個(gè)是5.7版本升級(jí)之后的效果，5.5版本的效果不是這個(gè)樣子罪治！5.7版本的查看這些就已經(jīng)非常的難了丽声。

數(shù)據(jù)消失應(yīng)采取的措施

誤操作：刪除表。

恢復(fù)手段：使用innodb備份文件：/tmp/innodb.sql觉义，可以直接恢復(fù)雁社。

查看二進(jìn)制日志與show binlog events確定位置

時(shí)間恢復(fù) | 起始位置與結(jié)束位置來(lái)進(jìn)行恢復(fù)！

時(shí)間的問(wèn)題：1秒可以插入10條數(shù)據(jù)晒骇；數(shù)據(jù)掉了5條霉撵，想恢復(fù)只是這5條。以時(shí)間來(lái)進(jìn)行恢復(fù)洪囤，就會(huì)把10條數(shù)據(jù)都恢復(fù)徒坡。有重復(fù)的5條數(shù)據(jù)，就會(huì)報(bào)錯(cuò)瘤缩。

恢復(fù)工具：/usr/local/mysql/bin/mysqlbinlog --help

--start-datetime ：時(shí)間

--start-position ：開(kāi)始位置

確定以位置來(lái)進(jìn)行恢復(fù)喇完！

--stop-position ：結(jié)束位置

mysqlbinlog –start-position=開(kāi)始位置 --stop-position=結(jié)束位置 二進(jìn)制日志文件路徑 | mysql –u root –p [庫(kù)名]

恢復(fù)表數(shù)據(jù)

第一步：還原一下：/usr/local/mysql/bin/mysql/ -u root -p test < /tmp/innodb/sql

第二步：使用二進(jìn)制日志進(jìn)行恢復(fù)

在日志中可以找到update開(kāi)始位置和結(jié)束的位置。

按照我的日志顯示是開(kāi)始位置：219剥啤；結(jié)束位置：762

進(jìn)行恢復(fù)：/usr/local/mysql/bin/mysqlbinlog --start-position=219 --stop-position=762 /usr/local/mysql/data/filename.000001 | /usr/local/mysql/bin/mysql -u root -p -test

十二锦溪、主從復(fù)制的配置

1不脯、配置主服務(wù)器

設(shè)定主從服務(wù)器：一定是主服務(wù)器版本低于或者相同于從服務(wù)器。最佳的情況是主從服務(wù)器的版本一致刻诊。

說(shuō)明：linux上面的mysql是5.7版本的防楷；win上面的mysql是5.5版本的。

確定主服務(wù)器是win上面的坏逢。

（1）開(kāi)啟二進(jìn)制日志

打開(kāi)在windows上mysql.ini配置文件域帐，設(shè)置集群的唯一id：

[mysqld]
log-bin = bin-log
server-id = 8       #集群里面，不要有重復(fù)的
......

重啟服務(wù)器是整。

（2）創(chuàng)建從服務(wù)器登錄主服務(wù)器的授權(quán)賬戶

權(quán)限：replication slave

grant replication slave on *.* to 'slave'@'%' identified by '123456';

關(guān)閉防火墻。

2民假、配置從服務(wù)器

（1）設(shè)置集群的唯一id

在linux中打開(kāi)mysql配置文件：vim /etc/my.cnf

[mysqld]
log-bin = bin-log
server-id = 1       #集群里面浮入，不要有重復(fù)的
......

保存重啟mysql服務(wù)器

（2）連接主服務(wù)器設(shè)置

需要主服務(wù)器提供如下信息：

change master to

master_host=’192.168.182.52’

master_port=3306

master_user=’slave’

master_password=’123456’

master_log_file=’log-bin.000001’ #這個(gè)的值是主服務(wù)器的二進(jìn)制文件的文件名

master_log_pos=250 #這個(gè)值是主服務(wù)器的二進(jìn)制文件最新的位置

重點(diǎn)說(shuō)明：開(kāi)始進(jìn)行主服務(wù)器與從服務(wù)器的數(shù)據(jù)同步！把所有的主服務(wù)器的內(nèi)容都復(fù)制到從服務(wù)器上面羊异。

因?yàn)閿?shù)據(jù)的同步事秀，就是在從服務(wù)器上面再次執(zhí)行SQL語(yǔ)句，沒(méi)有庫(kù)沒(méi)有表野舶，就會(huì)執(zhí)行報(bào)錯(cuò)易迹。

假裝同步：

配置從服務(wù)器：

change master to master_host='192.168.182.52',
master_port=3306,
master_user='slave',
master_password='123456',
master_log_file='log-bin.000001',
master_log_pos=250;

3、從服務(wù)器管理

查看從服務(wù)器狀態(tài)：show salve status\G;

啟動(dòng)從服務(wù)器：start slave;

停止從服務(wù)器：stop slave;

清空從服務(wù)器配置：reset slave all ;

在查看從服務(wù)器狀態(tài)時(shí)平道，Slave_IO_Running和Slave_SQL_Running的值都是yes時(shí)睹欲，才是啟動(dòng)成功

出現(xiàn)錯(cuò)誤信息時(shí)，記錄在Last_Errno和Last_Error中

從服務(wù)器已經(jīng)停止了在主服務(wù)器上面把拿數(shù)據(jù)過(guò)來(lái)一屋，此時(shí)不會(huì)再同步了 窘疮。

每一次從服務(wù)器去主服務(wù)器取數(shù)據(jù)的時(shí)候，都會(huì)記錄最后一次取數(shù)據(jù)的位置冀墨。然后下一次去取的時(shí)候闸衫，就是從這個(gè)位置開(kāi)始向下取的。

從服務(wù)器的修改與寫(xiě)入操作诽嘉，都不會(huì)記錄到主服務(wù)器上面的蔚出。所以修改或者寫(xiě)操作，必須由主服務(wù)器完成虫腋。

4骄酗、讀寫(xiě)分離的配置

PHP代碼層面實(shí)現(xiàn)：

主服務(wù)器：192.168.182.52;

從服務(wù)器：192.168.182.53;

$db1 = mysqli_connect(); 主服務(wù)器

$db2 = mysqli_connect(); 從服務(wù)器

$sql = “select” 這條sql是你寫(xiě)的。你知道它是查詢岔乔，請(qǐng)使用$db2操作mysqli_query($db2, $sql);

$sql = “insert” 這條sql是你寫(xiě)的酥筝。你知道它是寫(xiě)的，請(qǐng)使用$db1操作mysqli_query($db1,\ $sql);

十三雏门、定時(shí)任務(wù)

查看程序所在路徑：whereis vim

也可以使用find嘿歌，但是這種方式搜文件名比較慢掸掏。

date指令：

%M是分鐘數(shù)；%S是秒數(shù)：date +%M----%S顯示出的結(jié)果41----55

可以使用轉(zhuǎn)義符：\

date +\%M----\%S

創(chuàng)建一個(gè)文件：名稱由當(dāng)前的分與秒組成

touch ./file-$(date +\%M-\%S).php創(chuàng)建后的文件名：file-44-09.php

$()：在這里面可以寫(xiě)Linux的命令宙帝，會(huì)解析LINUX的命令丧凤。

1、什么是定時(shí)任務(wù)

指定時(shí)間自動(dòng)運(yùn)行的程序步脓，就是定時(shí)任務(wù)愿待。

2、為什么需要定時(shí)任務(wù)

很多工作需要定時(shí)定點(diǎn)完成靴患，這個(gè)時(shí)候就需要定時(shí)任務(wù)仍侥！

例：按天分析用戶的訪問(wèn)數(shù)據(jù)。

? 用戶的一天時(shí)間是0 – 23 點(diǎn)鸳君，當(dāng)一天結(jié)束之后农渊，項(xiàng)目立即需要對(duì)用戶訪問(wèn)數(shù)據(jù)進(jìn)行分析，然后處理入庫(kù)或颊。

? 提早把數(shù)據(jù)分析砸紊，處理入庫(kù)的代碼寫(xiě)好，測(cè)試通過(guò)囱挑；設(shè)定一個(gè)定時(shí)任務(wù)醉顽，0晨執(zhí)行該代碼，就可以完成數(shù)據(jù)處理平挑。

定時(shí)任務(wù)充斥著整個(gè)項(xiàng)目游添。完整的一個(gè)項(xiàng)目，有許多的定時(shí)任務(wù)弹惦。他們?cè)谥付ǖ臅r(shí)間否淤，運(yùn)行這些程序，完成項(xiàng)目中各種各樣的需求棠隐。

3石抡、定時(shí)任務(wù)語(yǔ)法介紹

語(yǔ)法：

時(shí)間參數(shù) 執(zhí)行命令（絕對(duì)路徑） 命令參數(shù) [ &> /dev/null ]

語(yǔ)法分解：

/dev/null ：這個(gè)設(shè)備叫數(shù)據(jù)黑洞，無(wú)論寫(xiě)入多少內(nèi)容助泽，直接吞掉啰扛，無(wú)法恢復(fù)。

&> ：成功或者失敗的消息嗡贺，全部寫(xiě)入設(shè)備 /dev/null

命令參數(shù) ：根據(jù)執(zhí)行命令決定參數(shù)內(nèi)容

執(zhí)行命令 ：根據(jù)需求隐解，選擇合適的命令

時(shí)間參數(shù) ：

? 分 時(shí) 天 月 星期 ：對(duì)應(yīng)的5個(gè)位置，必須有值诫睬！

? * * * * * ：重點(diǎn)【*】使用【每】來(lái)代表煞茫。

1 * * * * ：每小時(shí)的第1分鐘，執(zhí)行命令

2 * 1 * * ：每月第1天每小時(shí)的第2分鐘，執(zhí)行命令

1 * * * 1 ：每周一的每月每天每小時(shí)第1分鐘续徽，執(zhí)行命令

*/2 * * * * ：每小時(shí)的每2分鐘蚓曼，執(zhí)行命令

*/4 * * * 2 ：每周二的每小時(shí)的每4分鐘，執(zhí)行命令

1 */3 * * * ：每天的每三個(gè)小時(shí)的第1分鐘钦扭，執(zhí)行命令

1 1-5 * * * ：每天的第1小時(shí)到第5小時(shí)的第1分鐘纫版，執(zhí)行命令

1 1 * * 1,3 ：星期一和星期三的每天第1小時(shí)第1分鐘，執(zhí)行命令

1 1,4 * * * ：每天的第1小時(shí)和第4小時(shí)的第1分鐘客情，執(zhí)行命令

4其弊、定時(shí)任務(wù)命令介紹

crontab 選項(xiàng)參數(shù)

-l : 查看已經(jīng)設(shè)定好的定時(shí)任務(wù)

-e: 編輯定時(shí)任務(wù)：使用方式和vim的操作一致

? 添加一個(gè)定時(shí)任務(wù)：添加定時(shí)任務(wù)語(yǔ)法即可！

? 刪除一個(gè)定時(shí)任務(wù)：刪除該行任務(wù)內(nèi)容即可膀斋！

-r： 清除所有的定時(shí)任務(wù)

確定一個(gè)定時(shí)任務(wù)：

? 每2分鐘創(chuàng)建一個(gè)文件梭伐，并且文件名包含當(dāng)前分與秒！

crontab -e

*/2 * * * * /bin/touch /root/file-$(date +\%M-\%S).txt &> /dev/null

等待2分鐘后可以查看結(jié)果概页。

清空創(chuàng)建任務(wù)

crontab -r

crontab -l