Mysql优化汇总系列
1.MySQL版本：
5.x:5.0-5.1:早期產(chǎn)品的延續(xù)，升級(jí)維護(hù)5.4 - 5.x : MySQL整合了三方公司的新存儲(chǔ)引擎 （推薦5.5）安裝：rpm -ivh rpm軟件名
如果安裝時(shí) 與某個(gè)軟件 xxx沖突，則需要將沖突的軟件卸載掉：yun -y remove xxx安裝時(shí) 有日志提示我們可以修改密碼：/usr/bin/mysqladmin -u root password 'new-password'注意： 如果提示“GPG keys...”安裝失敗，解決方案：rpm -ivh rpm軟件名 --force --nodoeps驗(yàn)證：
mysqladmin --version啟動(dòng)mysql應(yīng)用： service mysql start關(guān)閉： service mysql stop重啟： service mysql restart在計(jì)算機(jī)reboot后 登陸MySQL : mysql可能會(huì)報(bào)錯(cuò)： "/var/lib/mysql/mysql.sock不存在" --原因：是Mysql服務(wù)沒有啟動(dòng)解決 ： 啟動(dòng)服務(wù)： 1.每次使用前 手動(dòng)啟動(dòng)服務(wù) /etc/init.d/mysql start2.開機(jī)自啟 chkconfig mysql on , chkconfig mysql off 檢查開機(jī)是否自動(dòng)啟動(dòng)： ntsysv 給mysql 的超級(jí)管理員root 增加密碼：/usr/bin/mysqladmin -u root password root登陸：mysql -u root -p數(shù)據(jù)庫(kù)存放目錄：
ps -ef|grep mysql 可以看到：數(shù)據(jù)庫(kù)目錄： datadir=/var/lib/mysql pid文件目錄： --pid-file=/var/lib/mysql/bigdata01.pidMySQL核心目錄：/var/lib/mysql :mysql 安裝目錄/usr/share/mysql: 配置文件/usr/bin：命令目錄（mysqladmin、mysqldump等）/etc/init.d/mysql啟停腳本MySQL配置文件
my-huge.cnf 高端服務(wù)器 1-2G內(nèi)存my-large.cnf 中等規(guī)模my-medium.cnf 一般my-small.cnf 較小但是，以上配置文件mysql默認(rèn)不能識(shí)別，默認(rèn)只能識(shí)別 /etc/my.cnf采用 my-huge.cnf ：cp /usr/share/mysql/my-huge.cnf /etc/my.cnf注意：mysql5.5默認(rèn)配置文件/etc/my.cnf；Mysql5.6 默認(rèn)配置文件/etc/mysql-default.cnf默認(rèn)端口3306mysql字符編碼：sql : show variables like '%char%' ;可以發(fā)現(xiàn)部分編碼是 latin,需要統(tǒng)一設(shè)置為utf-8設(shè)置編碼：vi /etc/my.cnf:[mysql]default-character-set=utf8[client]default-character-set=utf8[mysqld]character_set_server=utf8character_set_client=utf8collation_server=utf8_general_ci重啟Mysql:
service mysql restart編碼：
sql : show variables like '%char%' ; 注意事項(xiàng)：修改編碼 只對(duì)“之后”創(chuàng)建的數(shù)據(jù)庫(kù)生效，因此 我們建議 在mysql安裝完畢后，第一時(shí)間 統(tǒng)一編碼。mysql清屏
ctrl+L , system clear2.原理
MYSQL邏輯分層 ：連接層 服務(wù)層 引擎層 存儲(chǔ)層
InnoDB(默認(rèn)) ：事務(wù)優(yōu)先 （適合高并發(fā)操作；行鎖）
MyISAM ：性能優(yōu)先 （表鎖）
查詢數(shù)據(jù)庫(kù)引擎：
支持哪些引擎？ show engines ; 查看當(dāng)前使用的引擎 show variables like '%storage_engine%' ;指定數(shù)據(jù)庫(kù)對(duì)象的引擎：create table tb(id int(4) auto_increment ,name varchar(5),dept varchar(5) ,primary key(id) )ENGINE=MyISAM AUTO_INCREMENT=1DEFAULT CHARSET=utf8 ;3.SQL優(yōu)化
原因：性能低、執(zhí)行時(shí)間太長(zhǎng)、等待時(shí)間太長(zhǎng)、SQL語句欠佳（連接查詢）、索引失效、服務(wù)器參數(shù)設(shè)置不合理（緩沖、線程數(shù)）
a.SQL ：
編寫過程：
select dinstinct …from …join …on …where …group by …h(huán)aving …order by …limit …
解析過程：
from … on… join …where …group by …h(huán)aving …select dinstinct …order by limit …
b.SQL優(yōu)化， 主要就是 在優(yōu)化索引
索引： 相當(dāng)于書的目錄
索引： index是幫助MYSQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。索引是數(shù)據(jù)結(jié)構(gòu)（樹：B樹(默認(rèn))、Hash樹…）
索引的弊端：
1.索引本身很大， 可以存放在內(nèi)存/硬盤（通常為 硬盤）
2.索引不是所有情況均適用： a.少量數(shù)據(jù) b.頻繁更新的字段 c.很少使用的字段
3.索引會(huì)降低增刪改的效率（增刪改 查）
索引的優(yōu)勢(shì)：
1提高查詢效率（降低IO使用率）
2.降低CPU使用率 （…order by age desc,因?yàn)?B樹索引 本身就是一個(gè) 好排序的結(jié)構(gòu)，因此在排序時(shí) 可以直接使用）
參照：https://www.cnblogs.com/annsshadow/p/5037667.html（步步深入：MySQL架構(gòu)總覽->查詢執(zhí)行流程->SQL解析順序）
4.索引
4.1分類：
主鍵索引： 不能重復(fù)。id 不能是null
唯一索引： 不能重復(fù)。id 可以是null
單值索引： 單列， age ;一個(gè)表可以多個(gè)單值索引,name。
復(fù)合索引： 多個(gè)列構(gòu)成的索引 （相當(dāng)于 二級(jí)目錄 ： z: zhao） (name,age) (a,b,c,d,…,n)
BTree檢索原理：
4.2創(chuàng)建索引：
方式一：create 索引類型 索引名 on 表(字段)
單值： create index dept_index on tb(dept); 唯一： create unique index name_index on tb(name) ; 復(fù)合索引 create index dept_name_index on tb(dept,name);方式二：alter table 表名 索引類型 索引名（字段）
單值： alter table tb add index dept_index(dept) ; 唯一： alter table tb add unique index name_index(name); 復(fù)合索引 alter table tb add index dept_name_index(dept,name);注意： 如果一個(gè)字段是primary key，則改字段默認(rèn)就是 主鍵索引
刪除索引：
drop index 索引名 on 表名 ; drop index name_index on tb ;查詢索引：
show index from 表名 ; show index from 表名 \G5.SQL性能問題
a.分析SQL的執(zhí)行計(jì)劃 : explain ，可以模擬SQL優(yōu)化器執(zhí)行SQL語句，從而讓開發(fā)人員 知道自己編寫的SQL狀況
b.MySQL查詢優(yōu)化其會(huì)干擾我們的優(yōu)化
優(yōu)化方法，官網(wǎng)：https://dev.mysql.com/doc/refman/5.5/en/optimization.html
5.1查詢執(zhí)行計(jì)劃：
explain +SQL語句 explain select * from tb ;id : 編號(hào)
select_type ：查詢類型
table ：表
type ：類型
possible_keys ：預(yù)測(cè)用到的索引
key ：實(shí)際使用的索引
key_len ：實(shí)際使用索引的長(zhǎng)度
ref :表之間的引用
rows ：通過索引查詢到的數(shù)據(jù)量
Extra :額外的信息
測(cè)試：
1.準(zhǔn)備數(shù)據(jù)：
create table course
(
cid int(3),
cname varchar(20),
tid int(3)
);
create table teacher
(
tid int(3),
tname varchar(20),
tcid int(3)
);
查詢課程編號(hào)為2 或 教師證編號(hào)為3 的老師信息
explain +sql:
(1)id:
id值相同，從上往下 順序執(zhí)行。 t3-tc3-c4
tc3--c4-t6表的執(zhí)行順序 因數(shù)量的個(gè)數(shù)改變而改變的原因： 笛卡兒積
a b c4 3 2 = 2*3=6 * 4 =243*4=12* 2 =24數(shù)據(jù)小的表 優(yōu)先查詢；
id值不同：id值越大越優(yōu)先查詢 (本質(zhì)：在嵌套子查詢時(shí)，先查內(nèi)層 再查外層)
查詢教授SQL課程的老師的描述（desc）
explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid and t.tcid = tc.tcid and c.cname = 'sql' ;將以上 多表查詢 轉(zhuǎn)為子查詢形式：
explain select tc.tcdesc from teacherCard tc where tc.tcid = (select t.tcid from teacher t where t.tid = (select c.tid from course c where c.cname = 'sql') );子查詢+多表：
explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc where t.tcid= tc.tcid and t.tid = (select c.tid from course c where cname = 'sql') ;id值有相同，又有不同： id值越大越優(yōu)先；id值相同，從上往下 順序執(zhí)行
(2)select_type:查詢類型
PRIMARY:包含子查詢SQL中的 主查詢 （最外層）
SUBQUERY：包含子查詢SQL中的 子查詢 （非最外層）
simple:簡(jiǎn)單查詢（不包含子查詢、union）
derived:衍生查詢(使用到了臨時(shí)表)
union: 上例
union result : 告知開發(fā)人員，那些表之間存在union查詢
(3)type:索引類型、類型
越來越差：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system>const>eq_ref>ref>range>index>all ，要對(duì)type進(jìn)行優(yōu)化的前提：有索引
其中：system,const只是理想情況；實(shí)際能達(dá)到 ref>range
1.system（忽略）:
只有一條數(shù)據(jù)的系統(tǒng)表 ；或 衍生表只有一條數(shù)據(jù)的主查詢
2.const:
僅僅能查到一條數(shù)據(jù)的SQL ,用于Primary key 或unique索引 （類型 與索引類型有關(guān)）
3.eq_ref:
唯一性索引：對(duì)于每個(gè)索引鍵的查詢，返回匹配唯一行數(shù)據(jù)（有且只有1個(gè)，不能多 、不能0）
4.ref：
非唯一性索引，對(duì)于每個(gè)索引鍵的查詢，返回匹配的所有行（0，多）
5.range：
檢索指定范圍的行 ,where后面是一個(gè)范圍查詢(between ,> < >=, 特殊:in有時(shí)候會(huì)失效 ，從而轉(zhuǎn)為 無索引all)
6.index：
查詢?nèi)克饕袛?shù)據(jù)
7.all：
查詢?nèi)勘碇械臄?shù)據(jù)
system/const: 結(jié)果只有一條數(shù)據(jù)
eq_ref:結(jié)果多條；但是每條數(shù)據(jù)是唯一的 ；
ref：結(jié)果多條；但是每條數(shù)據(jù)是是0或多條 ；
（4）possible_keys ：可能用到的索引，是一種預(yù)測(cè)，不準(zhǔn)。
alter table course add index cname_index (cname);explain select t.tname ,tc.tcdesc from teacher t,teacherCard tc where t.tcid= tc.tcid and t.tid = (select c.tid from course c where cname = 'sql') ;如果 possible_key/key是NULL，則說明沒用索引
explain select tc.tcdesc from teacherCard tc,course c,teacher t where c.tid = t.tid and t.tcid = tc.tcid and c.cname = 'sql' ;（5） key ：實(shí)際使用到的索引
（6）key_len ：索引的長(zhǎng)度 ;
作用：用于判斷復(fù)合索引是否被完全使用 （a,b,c）。
create table test_kl (name char(20) not null default '' ); alter table test_kl add index index_name(name) ; explain select * from test_kl where name ='' ; -- key_len :60在utf8：1個(gè)字符站3個(gè)字節(jié)
alter table test_kl add column name1 char(20) ; --name1可以為nullalter table test_kl add index index_name1(name1) ; explain select * from test_kl where name1 ='' ;–如果索引字段可以為Null,則會(huì)使用1個(gè)字節(jié)用于標(biāo)識(shí)。
drop index index_name on test_kl ; drop index index_name1 on test_kl ;增加一個(gè)復(fù)合索引 alter table test_kl add index name_name1_index (name,name1) ; explain select * from test_kl where name1 = '' ; --121 explain select * from test_kl where name = '' ; --60varchar(20) alter table test_kl add column name2 varchar(20) ; --可以為Null alter table test_kl add index name2_index (name2) ;explain select * from test_kl where name2 = '' ; --63 20*3=60 + 1(null) +2(用2個(gè)字節(jié) 標(biāo)識(shí)可變長(zhǎng)度) =63utf8:1個(gè)字符3個(gè)字節(jié)
gbk:1個(gè)字符2個(gè)字節(jié)
latin:1個(gè)字符1個(gè)字節(jié)
(7) ref : 注意與type中的ref值區(qū)分。
作用： 指明當(dāng)前表所 參照的 字段。
select ....where a.c = b.x ;(其中b.x可以是常量，const)alter table course add index tid_index (tid) ;explain select * from course c,teacher t where c.tid = t.tid and t.tname ='tw' ;(8)rows: 被索引優(yōu)化查詢的 數(shù)據(jù)個(gè)數(shù) (實(shí)際通過索引而查詢到的 數(shù)據(jù)個(gè)數(shù))
explain select * from course c,teacher t where c.tid = t.tid and t.tname = 'tz' ;（9）Extra：
(i).using filesort ： 性能消耗大；需要“額外”的一次排序（查詢） 。常見于 order by 語句中。
排序：先查詢
10個(gè)人 根據(jù)年齡排序。create table test02 (a1 char(3),a2 char(3),a3 char(3),index idx_a1(a1),index idx_a2(a2),index idx_a3(a3) );explain select * from test02 where a1 ='' order by a1 ;a1:姓名 a2：年齡explain select * from test02 where a1 ='' order by a2 ; --using filesort小結(jié)： 對(duì)于單索引， 如果排序和查找是同一個(gè)字段，則不會(huì)出現(xiàn)using filesort；如果排序和查找不是同一個(gè)字段，則會(huì)出現(xiàn)using filesort；
避免： where哪些字段，就order by那些字段2
復(fù)合索引：不能跨列（最佳左前綴）
drop index idx_a1 on test02; drop index idx_a2 on test02; drop index idx_a3 on test02;alter table test02 add index idx_a1_a2_a3 (a1,a2,a3) ; explain select *from test02 where a1='' order by a3 ; --using filesort explain select *from test02 where a2='' order by a3 ; --using filesort explain select *from test02 where a1='' order by a2 ; explain select *from test02 where a2='' order by a1 ; --using filesort小結(jié)： 避免： where和order by 按照復(fù)合索引的順序使用，不要跨列或無序使用。
(ii). using temporary: 性能損耗大 ，用到了臨時(shí)表。一般出現(xiàn)在group by 語句中。
explain select a1 from test02 where a1 in ('1','2','3') group by a1 ;explain select a1 from test02 where a1 in ('1','2','3') group by a2 ; --using temporary避免： 查詢那些列，就根據(jù)那些列 group by .
(iii). using index : 性能提升; 索引覆蓋（覆蓋索引）。原因：不讀取原文件，只從索引文件中獲取數(shù)據(jù) （不需要回表查詢）
只要使用到的列 全部都在索引中，就是索引覆蓋using index
例如：test02表中有一個(gè)復(fù)合索引(a1,a2,a3)
explain select a1,a2 from test02 where a1=’’ or a2= ‘’ ; --using index
如果用到了索引覆蓋(using index時(shí))，會(huì)對(duì) possible_keys和key造成影響：
a.如果沒有where，則索引只出現(xiàn)在key中；
b.如果有where，則索引 出現(xiàn)在key和possible_keys中。
(iii).using where （需要回表查詢）
假設(shè)age是索引列
但查詢語句select age,name from …where age =…,此語句中必須回原表查Name，因此會(huì)顯示using where.
(iv). impossible where ： where子句永遠(yuǎn)為false
explain select * from test02 where a1='x' and a1='y' ;6.優(yōu)化案例
單表優(yōu)化、兩表優(yōu)化、三表優(yōu)化
（1）單表優(yōu)化
優(yōu)化： 加索引
alter table book add index idx_bta (bid,typeid,authorid);索引一旦進(jìn)行 升級(jí)優(yōu)化，需要將之前廢棄的索引刪掉，防止干擾。
drop index idx_bta on book;根據(jù)SQL實(shí)際解析的順序，調(diào)整索引的順序：
alter table book add index idx_tab (typeid,authorid,bid); --雖然可以回表查詢bid，但是將bid放到索引中 可以提升使用using index ;再次優(yōu)化（之前是index級(jí)別）： 思路。因?yàn)榉秶樵僫n有時(shí)會(huì)實(shí)現(xiàn)，因此交換 索引的順序，將typeid in(2,3) 放到最后。
drop index idx_tab on book; alter table book add index idx_atb (authorid,typeid,bid); explain select bid from book where authorid=1 and typeid in(2,3) order by typeid desc ;–小結(jié)： a.最佳做前綴，保持索引的定義和使用的順序一致性 b.索引需要逐步優(yōu)化 c.將含In的范圍查詢 放到where條件的最后，防止失效。
本例中同時(shí)出現(xiàn)了Using where（需要回原表）; Using index（不需要回原表）：原因，where authorid=1 and typeid in(2,3)中authorid在索引(authorid,typeid,bid)中，因此不需要回原表（直接在索引表中能查到）；而typeid雖然也在索引(authorid,typeid,bid)中，但是含in的范圍查詢已經(jīng)使該typeid索引失效，因此相當(dāng)于沒有typeid這個(gè)索引，所以需要回原表（using where）；
例如以下沒有了In，則不會(huì)出現(xiàn)using where
explain select bid from book where authorid=1 and typeid =3 order by typeid desc ;
還可以通過key_len證明In可以使索引失效。
（2）兩表優(yōu)化
左連接：
explain select *from teacher2 t left outer join course2 c on t.cid=c.cid where c.cname='java';索引往哪張表加？ -小表驅(qū)動(dòng)大表
-索引建立經(jīng)常使用的字段上 （本題 t.cid=c.cid可知，t.cid字段使用頻繁，因此給該字段加索引） [一般情況對(duì)于左外連接，給左表加索引；右外連接，給右表加索引]
小表：10
大表：300
where 小表.x 10 = 大表.y 300; --循環(huán)了幾次？10
–以上2個(gè)FOR循環(huán)，最終都會(huì)循環(huán)3000次；但是 對(duì)于雙層循環(huán)來說：一般建議 將數(shù)據(jù)小的循環(huán) 放外層；數(shù)據(jù)大的循環(huán)放內(nèi)存。
總結(jié)：–當(dāng)編寫 …on t.cid=c.cid 時(shí)，將數(shù)據(jù)量小的表 放左邊（假設(shè)此時(shí)t表數(shù)據(jù)量小）
alter table teacher2 add index index_teacher2_cid(cid) ; alter table course2 add index index_course2_cname(cname);Using join buffer:extra中的一個(gè)選項(xiàng)，作用：Mysql引擎使用了 連接緩存。（3）三張表優(yōu)化A B C
a.小表驅(qū)動(dòng)大表
b.索引建立在經(jīng)常查詢的字段上
–總結(jié)：
i.如果 (a,b,c,d)復(fù)合索引 和使用的順序全部一致(且不跨列使用)，則復(fù)合索引全部使用。如果部分一致(且不跨列使用)，則使用部分索引。
ii.where和order by 拼起來，不要跨列使用
using temporary:需要額外再多使用一張表. 一般出現(xiàn)在group by語句中；已經(jīng)有表了，但不適用，必須再來一張表。解析過程：
from … on… join …where …group by …h(huán)aving …select dinstinct …order by limit …
a.
explain select * from test03 where a2=2 and a4=4 group by a2,a4 ;–沒有using temporary
b.
explain select * from test03 where a2=2 and a4=4 group by a3 ;
7.避免索引失效的一些原則
（1）復(fù)合索引
a.復(fù)合索引，不要跨列或無序使用（最佳左前綴） (a,b,c)
b.復(fù)合索引，盡量使用全索引匹配(a,b,c)
（2）不要在索引上進(jìn)行任何操作（計(jì)算、函數(shù)、類型轉(zhuǎn)換），否則索引失效
select …where A.x = … ; --假設(shè)A.x是索引
不要：select …where A.x*3 = …
（3）復(fù)合索引不能使用不等于（!= <>）或is null (is not null)，否則自身以及右側(cè)所有全部失效。
復(fù)合索引中如果有>，則自身和右側(cè)索引全部失效。
– SQL優(yōu)化，是一種概率層面的優(yōu)化。至于是否實(shí)際使用了我們的優(yōu)化，需要通過explain進(jìn)行推測(cè)。
explain select * from book where authorid != 1 and typeid =2 ; explain select * from book where authorid != 1 and typeid !=2 ;體驗(yàn)概率情況(< > =)： 原因是服務(wù)層中有SQL優(yōu)化器，可能會(huì)影響我們的優(yōu)化。
drop index idx_typeid on book; drop index idx_authroid on book; alter table book add index idx_book_at (authorid,typeid); explain select * from book where authorid = 1 and typeid =2 ;--復(fù)合索引at全部使用 explain select * from book where authorid > 1 and typeid =2 ; --復(fù)合索引中如果有>，則自身和右側(cè)索引全部失效。 explain select * from book where authorid = 1 and typeid >2 ;--復(fù)合索引at全部使用----明顯的概率問題--- explain select * from book where authorid < 1 and typeid =2 ;--復(fù)合索引at只用到了1個(gè)索引 explain select * from book where authorid < 4 and typeid =2 ;--復(fù)合索引全部失效–我們學(xué)習(xí)索引優(yōu)化 ，是一個(gè)大部分情況適用的結(jié)論，但由于SQL優(yōu)化器等原因 該結(jié)論不是100%正確。
–一般而言， 范圍查詢（> < in），之后的索引失效。
（4）補(bǔ)救。盡量使用索引覆蓋（using index）
（a,b,c）
(5) like盡量以“常量”開頭，不要以’%'開頭，否則索引失效
select * from xx where name like '%x%' ; --name索引失效explain select * from teacher where tname like '%x%'; --tname索引失效explain select * from teacher where tname like 'x%';explain select tname from teacher where tname like '%x%'; --如果必須使用like '%x%'進(jìn)行模糊查詢，可以使用索引覆蓋 挽救一部分。（6）盡量不要使用類型轉(zhuǎn)換（顯示、隱式），否則索引失效
explain select * from teacher where tname = 'abc' ; explain select * from teacher where tname = 123 ;//程序底層將 123 -> '123'，即進(jìn)行了類型轉(zhuǎn)換，因此索引失效（7）盡量不要使用or，否則索引失效
explain select * from teacher where tname ='' or tcid >1 ; --將or左側(cè)的tname 失效。8.一些其他的優(yōu)化方法
（1）exist和in
select …from table where exist (子查詢) ;
select …from table where 字段 in (子查詢) ;
exist語法： 將主查詢的結(jié)果，放到子查需結(jié)果中進(jìn)行條件校驗(yàn)（看子查詢是否有數(shù)據(jù)，如果有數(shù)據(jù) 則校驗(yàn)成功） ，
如果 復(fù)合校驗(yàn)，則保留數(shù)據(jù)；
in語法：
select ..from table where tid in (1,3,5) ;（2）order by 優(yōu)化
using filesort 有兩種算法：雙路排序、單路排序 （根據(jù)IO的次數(shù)）
MySQL4.1之前 默認(rèn)使用 雙路排序；
雙路：掃描2次磁盤
–IO較消耗性能
MySQL4.1之后 默認(rèn)使用 單路排序 ：
只讀取一次（全部字段），在buffer中進(jìn)行排序。但種單路排序 會(huì)有一定的隱患 （不一定真的是“單路|1次IO”，有可能多次IO）。
原因：如果數(shù)據(jù)量特別大，則無法 將所有字段的數(shù)據(jù) 一次性讀取完畢，因此 會(huì)進(jìn)行“分片讀取、多次讀取”。
單路排序在使用時(shí)，如果數(shù)據(jù)大，可以考慮調(diào)大buffer的容量大小： set max_length_for_sort_data = 1024 單位byte
注意： 單路排序 比雙路排序 會(huì)占用更多的buffer。
如果max_length_for_sort_data值太低，則mysql會(huì)自動(dòng)從 單路->雙路 （太低：需要排序的列的總大小超過了max_length_for_sort_data定義的字節(jié)數(shù)）提高order by查詢的策略：
a.選擇使用單路、雙路 ；調(diào)整buffer的容量大小；
b.避免select * …
c.復(fù)合索引 不要跨列使用 ，避免using filesort
d.保證全部的排序字段 排序的一致性（都是升序 或 降序）
9.SQL排查
慢查詢?nèi)罩? MySQL提供的一種日志記錄，用于記錄MySQL種響應(yīng)時(shí)間超過閥值的SQL語句 （long_query_time，默認(rèn)10秒）
慢查詢?nèi)罩灸J(rèn)是關(guān)閉的；
建議：開發(fā)調(diào)優(yōu)是 打開，而 最終部署時(shí)關(guān)閉。
檢查是否開啟了 慢查詢?nèi)罩?：
show variables like '%slow_query_log%' ;臨時(shí)開啟：
set global slow_query_log = 1 ; --在內(nèi)存種開啟 exit service mysql restart永久開啟：
/etc/my.cnf 中追加配置： vi /etc/my.cnf [mysqld] slow_query_log=1 slow_query_log_file=/var/lib/mysql/localhost-slow.log慢查詢閥值：
show variables like '%long_query_time%' ;臨時(shí)設(shè)置閥值：
set global long_query_time = 5 ; --設(shè)置完畢后，重新登陸后起效 （不需要重啟服務(wù)）永久設(shè)置閥值：
/etc/my.cnf 中追加配置： vi /etc/my.cnf [mysqld] long_query_time=3select sleep(4);
select sleep(5);
select sleep(3);
select sleep(3);
–查詢超過閥值的SQL：
(1)慢查詢的sql被記錄在了日志中，因此可以通過日志 查看具體的慢SQL。
cat /var/lib/mysql/localhost-slow.log(2)通過mysqldumpslow工具查看慢SQL,可以通過一些過濾條件 快速查找出需要定位的慢SQL
mysqldumpslow --helps：排序方式r:逆序l:鎖定時(shí)間g:正則匹配模式 --獲取返回記錄最多的3個(gè)SQLmysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log--獲取訪問次數(shù)最多的3個(gè)SQLmysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log--按照時(shí)間排序，前10條包含left join查詢語句的SQLmysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log語法：mysqldumpslow 各種參數(shù) 慢查詢?nèi)罩镜奈募?10.分析海量數(shù)據(jù)
a.模擬海量數(shù)據(jù)
存儲(chǔ)過程（無return）/存儲(chǔ)函數(shù)（有return）
通過存儲(chǔ)函數(shù) 插入海量數(shù)據(jù)：
創(chuàng)建存儲(chǔ)函數(shù)：
–如果報(bào)錯(cuò)：You have an error in your SQL syntax，說明SQL語句語法有錯(cuò)，需要修改SQL語句；
如果報(bào)錯(cuò)This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
是因?yàn)?存儲(chǔ)過程/存儲(chǔ)函數(shù)在創(chuàng)建時(shí) 與之前的 開啟慢查詢?nèi)罩緵_突了
解決沖突：
–產(chǎn)生隨機(jī)整數(shù)
create function ran_num() returns int(5)begindeclare i int default 0;set i =floor( rand()*100 ) ;return i ; end $–通過存儲(chǔ)過程插入海量數(shù)據(jù)：emp表中 ， 10000, 100000
create procedure insert_emp( in eid_start int(10),in data_times int(10))begin declare i int default 0;set autocommit = 0 ;repeatinsert into emp values(eid_start + i, randstring(5) ,'other' ,ran_num()) ;set i=i+1 ;until i=data_timesend repeat ;commit ; end $–通過存儲(chǔ)過程插入海量數(shù)據(jù)：dept表中
create procedure insert_dept(in dno_start int(10) ,in data_times int(10))begindeclare i int default 0;set autocommit = 0 ;repeatinsert into dept values(dno_start+i ,randstring(6),randstring(8)) ;set i=i+1 ;until i=data_timesend repeat ;commit ;end$–插入數(shù)據(jù)
delimiter ; call insert_emp(1000,800000) ; call insert_dept(10,30) ;b.分析海量數(shù)據(jù):
（1）profiles
(2)–精確分析:sql診斷
show profile all for query 上一步查詢的的Query_Idshow profile cpu,block io for query 上一步查詢的的Query_Id(3)全局查詢?nèi)罩?：
記錄開啟之后的 全部SQL語句。 （這次全局的記錄操作 僅僅在調(diào)優(yōu)、開發(fā)過程中打開即可，在最終的部署實(shí)施時(shí) 一定關(guān)閉）
–執(zhí)行的所有SQL記錄在表中
set global general_log = 1 ;--開啟全局日志 set global log_output='table' ; --設(shè)置 將全部的SQL 記錄在表中–執(zhí)行的所有SQL記錄在文件中
set global log_output='file' ; set global general_log = on ; set global general_log_file='/tmp/general.log' ;開啟后，會(huì)記錄所有SQL ： 會(huì)被記錄 mysql.general_log表中。
select * from mysql.general_log ;11.鎖機(jī)制 ：解決因資源共享 而造成的并發(fā)問題。
示例：買最后一件衣服X
A: X 買 ： X加鎖 ->試衣服...下單..付款..打包 ->X解鎖 B: X 買：發(fā)現(xiàn)X已被加鎖，等待X解鎖， X已售空分類：
操作類型：
a.讀鎖（共享鎖）： 對(duì)同一個(gè)數(shù)據(jù)（衣服），多個(gè)讀操作可以同時(shí)進(jìn)行，互不干擾。
b.寫鎖（互斥鎖）： 如果當(dāng)前寫操作沒有完畢（買衣服的一系列操作），則無法進(jìn)行其他的讀操作、寫操作
操作范圍：
a.表鎖 ：一次性對(duì)一張表整體加鎖。如MyISAM存儲(chǔ)引擎使用表鎖，開銷小、加鎖快；無死鎖；但鎖的范圍大，容易發(fā)生鎖沖突、并發(fā)度低。
b.行鎖 ：一次性對(duì)一條數(shù)據(jù)加鎖。如InnoDB存儲(chǔ)引擎使用行鎖，開銷大，加鎖慢；容易出現(xiàn)死鎖；鎖的范圍較小，不易發(fā)生鎖沖突，并發(fā)度高（很小概率 發(fā)生高并發(fā)問題：臟讀、幻讀、不可重復(fù)度、丟失更新等問題）。
c.頁鎖
示例：
（1）表鎖 ： --自增操作 MYSQL/SQLSERVER 支持；oracle需要借助于序列來實(shí)現(xiàn)自增
create table tablelock ( id int primary key auto_increment , name varchar(20) )engine myisam;insert into tablelock(name) values('a1'); insert into tablelock(name) values('a2'); insert into tablelock(name) values('a3'); insert into tablelock(name) values('a4'); insert into tablelock(name) values('a5'); commit;增加鎖：
locak table 表1 read/write ,表2 read/write ,...查看加鎖的表：
show open tables ;會(huì)話：session :每一個(gè)訪問數(shù)據(jù)的dos命令行、數(shù)據(jù)庫(kù)客戶端工具 都是一個(gè)會(huì)話
===加讀鎖：會(huì)話0：lock table tablelock read ;select * from tablelock; --讀（查），可以delete from tablelock where id =1 ; --寫（增刪改），不可以select * from emp ; --讀，不可以delete from emp where eid = 1; --寫，不可以結(jié)論1：
–如果某一個(gè)會(huì)話 對(duì)A表加了read鎖，則 該會(huì)話 可以對(duì)A表進(jìn)行讀操作、不能進(jìn)行寫操作；
且 該會(huì)話不能對(duì)其他表進(jìn)行讀、寫操作。
–即如果給A表加了讀鎖，則當(dāng)前會(huì)話只能對(duì)A表進(jìn)行讀操作。
MySQL表級(jí)鎖的鎖模式：
MyISAM在執(zhí)行查詢語句（SELECT）前，會(huì)自動(dòng)給涉及的所有表加讀鎖，
在執(zhí)行更新操作（DML）前，會(huì)自動(dòng)給涉及的表加寫鎖。
所以對(duì)MyISAM表進(jìn)行操作，會(huì)有以下情況：
a、對(duì)MyISAM表的讀操作（加讀鎖），不會(huì)阻塞其他進(jìn)程（會(huì)話）對(duì)同一表的讀請(qǐng)求，
但會(huì)阻塞對(duì)同一表的寫請(qǐng)求。只有當(dāng)讀鎖釋放后，才會(huì)執(zhí)行其它進(jìn)程的寫操作。
b、對(duì)MyISAM表的寫操作（加寫鎖），會(huì)阻塞其他進(jìn)程（會(huì)話）對(duì)同一表的讀和寫操作，
只有當(dāng)寫鎖釋放后，才會(huì)執(zhí)行其它進(jìn)程的讀寫操作。
分析表鎖定：
查看哪些表加了鎖：
分析表鎖定的嚴(yán)重程度：
show status like 'table%' ; Table_locks_immediate :即可能獲取到的鎖數(shù) Table_locks_waited：需要等待的表鎖數(shù)(如果該值越大，說明存在越大的鎖競(jìng)爭(zhēng))一般建議：
Table_locks_immediate/Table_locks_waited > 5000， 建議采用InnoDB引擎，否則MyISAM引擎（2）行鎖（InnoDB）
create table linelock(
id int(5) primary key auto_increment,
name varchar(20)
)engine=innodb ;
insert into linelock(name) values(‘1’) ;
insert into linelock(name) values(‘2’) ;
insert into linelock(name) values(‘3’) ;
insert into linelock(name) values(‘4’) ;
insert into linelock(name) values(‘5’) ;
–mysql默認(rèn)自動(dòng)commit; oracle默認(rèn)不會(huì)自動(dòng)commit ;
為了研究行鎖，暫時(shí)將自動(dòng)commit關(guān)閉; set autocommit =0 ; 以后需要通過commit
會(huì)話0： 寫操作insert into linelock values( 'a6') ;會(huì)話1： 寫操作 同樣的數(shù)據(jù)update linelock set name='ax' where id = 6;對(duì)行鎖情況：
1.如果會(huì)話x對(duì)某條數(shù)據(jù)a進(jìn)行 DML操作（研究時(shí)：關(guān)閉了自動(dòng)commit的情況下），則其他會(huì)話必須等待會(huì)話x結(jié)束事務(wù)(commit/rollback)后 才能對(duì)數(shù)據(jù)a進(jìn)行操作。
2.表鎖 是通過unlock tables，也可以通過事務(wù)解鎖 ; 行鎖 是通過事務(wù)解鎖。
行鎖的注意事項(xiàng)：
a.如果沒有索引，則行鎖會(huì)轉(zhuǎn)為表鎖
b.行鎖的一種特殊情況：
間隙鎖：值在范圍內(nèi)，但卻不存在
–此時(shí)linelock表中 沒有id=7的數(shù)據(jù)
間隙：Mysql會(huì)自動(dòng)給 間隙 加索 ->間隙鎖。即 本題 會(huì)自動(dòng)給id=7的數(shù)據(jù)加 間隙鎖（行鎖）。
行鎖：如果有where，則實(shí)際加索的范圍 就是where后面的范圍（不是實(shí)際的值）
開啟和關(guān)閉
1.查看是否開啟間隙鎖：
2.關(guān)閉間隙鎖(gap lock)方法:
在my.cnf里面的[mysqld]添加
3.重啟MySQL后生效.
如果僅僅是查詢數(shù)據(jù)，能否加鎖？ 可以 for update 研究學(xué)習(xí)時(shí)，將自動(dòng)提交關(guān)閉：set autocommit =0 ;start transaction ;begin ;select * from linelock where id =2 for update ;通過for update對(duì)query語句進(jìn)行加鎖。行鎖總結(jié)：
InnoDB默認(rèn)采用行鎖；
缺點(diǎn)： 比表鎖性能損耗大。
優(yōu)點(diǎn)：并發(fā)能力強(qiáng)，效率高。
因此建議，高并發(fā)用InnoDB，否則用MyISAM。
行鎖分析：
show status like '%innodb_row_lock%' ; Innodb_row_lock_current_waits :當(dāng)前正在等待鎖的數(shù)量 Innodb_row_lock_time：等待總時(shí)長(zhǎng)。從系統(tǒng)啟到現(xiàn)在 一共等待的時(shí)間 Innodb_row_lock_time_avg ：平均等待時(shí)長(zhǎng)。從系統(tǒng)啟到現(xiàn)在平均等待的時(shí)間 Innodb_row_lock_time_max ：最大等待時(shí)長(zhǎng)。從系統(tǒng)啟到現(xiàn)在最大一次等待的時(shí)間 Innodb_row_lock_waits ： 等待次數(shù)。從系統(tǒng)啟到現(xiàn)在一共等待的次數(shù)12.主從復(fù)制 （集群在數(shù)據(jù)庫(kù)的一種實(shí)現(xiàn)）
windows:mysql 主
linux:mysql從
安裝windows版mysql:
如果之前計(jì)算機(jī)中安裝過Mysql，要重新再安裝 則需要：先卸載 再安裝先卸載：通過電腦自帶卸載工具卸載Mysql (電腦管家也可以)刪除一個(gè)mysql緩存文件C:\ProgramData\MySQL刪除注冊(cè)表regedit中所有mysql相關(guān)配置--重啟計(jì)算機(jī)安裝MYSQL：安裝時(shí)，如果出現(xiàn)未響應(yīng)： 則重新打開D:\MySQL\MySQL Server 5.5\bin\MySQLInstanceConfig.exe圖形化客戶端： SQLyog, Navicat如果要遠(yuǎn)程連接數(shù)據(jù)庫(kù)，則需要授權(quán)遠(yuǎn)程訪問。 授權(quán)遠(yuǎn)程訪問 :(A->B,則再B計(jì)算機(jī)的Mysql中執(zhí)行以下命令)GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;FLUSH PRIVILEGES;如果仍然報(bào)錯(cuò)：可能是防火墻沒關(guān)閉 ： 在B關(guān)閉防火墻 service iptables stop實(shí)現(xiàn)主從同步（主從復(fù)制）：
1.master將改變的數(shù) 記錄在本地的 二進(jìn)制日志中（binary log） ；該過程 稱之為：二進(jìn)制日志件事
2.slave將master的binary log拷貝到自己的 relay log（中繼日志文件）中
3.中繼日志事件，將數(shù)據(jù)讀取到自己的數(shù)據(jù)庫(kù)之中
MYSQL主從復(fù)制 是異步的，串行化的， 有延遲
配置：
windows(mysql: my.ini)
linux(mysql: my.cnf)
準(zhǔn)備工作：
配置前，為了無誤，先將權(quán)限(遠(yuǎn)程訪問)、防火墻等處理：
關(guān)閉windows/linux防火墻： windows：右鍵“網(wǎng)絡(luò)” ,
linux: service iptables stop
Mysql允許遠(yuǎn)程連接(windowos/linux)：
GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘root’ WITH GRANT OPTION;
FLUSH PRIVILEGES;
1.主機(jī)（以下代碼和操作 全部在主機(jī)windows中操作）：
my.ini
[mysqld] #id server-id=1 #二進(jìn)制日志文件（注意是/ 不是\） log-bin="D:/MySQL/MySQL Server 5.5/data/mysql-bin" #錯(cuò)誤記錄文件 log-error="D:/MySQL/MySQL Server 5.5/data/mysql-error" #主從同步時(shí) 忽略的數(shù)據(jù)庫(kù) binlog-ignore-db=mysql #(可選)指定主從同步時(shí)，同步哪些數(shù)據(jù)庫(kù) binlog-do-db=testwindows中的數(shù)據(jù)庫(kù) 授權(quán)哪臺(tái)計(jì)算機(jī)中的數(shù)據(jù)庫(kù) 是自己的從數(shù)據(jù)庫(kù)：
GRANT REPLICATION slave,reload,super ON *.* TO 'root'@'192.168.2.%' IDENTIFIED BY 'root';flush privileges ;查看主數(shù)據(jù)庫(kù)的狀態(tài)（每次在左主從同步前，需要觀察 主機(jī)狀態(tài)的最新值）
show master status; （mysql-bin.000001、 107）2.從機(jī)（以下代碼和操作 全部在從機(jī)linux中操作）：
my.cnf
[mysqld] server-id=2 log-bin=mysql-bin replicate-do-db=testlinux中的數(shù)據(jù) 授權(quán)哪臺(tái)計(jì)算機(jī)中的數(shù)控 是自己的主計(jì)算機(jī)
CHANGE MASTER TO MASTER_HOST = '192.168.2.2', MASTER_USER = 'root', MASTER_PASSWORD = 'root', MASTER_PORT = 3306, master_log_file='mysql-bin.000001', master_log_pos=107;如果報(bào)錯(cuò)：This operation cannot be performed with a running slave; run STOP SLAVE first 解決：STOP SLAVE ;再次執(zhí)行上條授權(quán)語句3.開啟主從同步：
從機(jī)linux:
start slave ;檢驗(yàn) show slave status \G
主要觀察： Slave_IO_Running和 Slave_SQL_Running，確保二者都是yes；
如果不都是yes，則看下方的 Last_IO_Error。
本次 通過 Last_IO_Error發(fā)現(xiàn)錯(cuò)誤的原因是 主從使用了相同的server-id， 檢查:在主從中分別查看serverid: show variables like ‘server_id’ ;
可以發(fā)現(xiàn)，在Linux中的my.cnf中設(shè)置了server-id=2，但實(shí)際執(zhí)行時(shí) 確實(shí)server-id=1，原因：可能是 linux版Mysql的一個(gè)bug，也可能是 windows和Linux版本不一致造成的兼容性問題。
解決改bug： set global server_id =2 ;
4.演示：
主windows =>從
windows:
將表，插入數(shù)據(jù)
觀察從數(shù)據(jù)庫(kù)中該表的數(shù)據(jù)
總結(jié)
以上是生活随笔為你收集整理的Mysql优化汇总系列的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 亚马逊跨境跟卖快速出单的技巧
- 下一篇： 用脑过度了头痛了两天