mysql的安裝
bit
release 發(fā)布版
ga 穩(wěn)定發(fā)布版
第三方軟件包，安裝在/opt目錄下
var/ 變量文件的目錄
在linux下檢查安裝過(guò)的軟件法挨，rpm -qa|grep -i mysql
永遠(yuǎn)不要有中文，永遠(yuǎn)不要有空格
先安裝rpm -ivh
getconf LONG_BIT 查看操作系統(tǒng)位數(shù)
A temporary password is generated for root@localhost: !/Y/Cw#9IliG
mysql的數(shù)據(jù)目錄 /var/lib/mysql
修改默認(rèn)的配置文件/etc/my.cnf
修改mysql默認(rèn)字符集
mysql的二進(jìn)制日志文件:log-bin
frm 存放表結(jié)構(gòu)
myd 存放數(shù)據(jù)
myi 存放索引
mysql邏輯架構(gòu)
mysql引擎
myisam innodb
sql性能下降原因
執(zhí)行時(shí)間長(zhǎng)，等待時(shí)間長(zhǎng)
查詢語(yǔ)句爛，索引失效眷柔，
索引，單值索引原朝，符合索引驯嘱，
create index idx_user_name on user(name);
create index idx_user_nameEmail on user(name,email);
關(guān)聯(lián)查詢太多join
服務(wù)器調(diào)優(yōu)以及個(gè)各個(gè)參數(shù)的設(shè)置
常見(jiàn)的join查詢
sql的查詢順序
七種join查詢
select * from a inner join b on a.key=b.key
select * from a left join ab on a.key=b.key
select * from a right join b on a.key=b.key
[圖片上傳失敗...(image-e5e06-1612330399952)]
select * from a left join b on a.key=b.key where b.key=null
select * from a right join on b a.key=b.key where a.key=null
select * from a full outer join b on a.key=b.key
select * from a full outer join b on a.key=b.key where a.key=null or b.key=null
索引是什么
什么是索引：索引是幫助mysql高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。索引的本質(zhì)就是一種數(shù)據(jù)結(jié)構(gòu)竿拆，可以簡(jiǎn)單理解為排好序的快速查找數(shù)據(jù)結(jié)構(gòu)
在數(shù)據(jù)外，數(shù)據(jù)庫(kù)還維護(hù)著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)宾尚，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用(指向)數(shù)據(jù)丙笋，這樣就可以在這些數(shù)據(jù)結(jié)果上實(shí)現(xiàn)高級(jí)查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引
索引的優(yōu)勢(shì)與劣勢(shì)
索引的優(yōu)勢(shì)：（1）提高數(shù)據(jù)檢索的效率煌贴，降低數(shù)據(jù)庫(kù)的IO成本御板，（2）通過(guò)索引列對(duì)數(shù)據(jù)進(jìn)行排序，降低數(shù)據(jù)排序的成本牛郑，降低CPU的消耗
索引的劣勢(shì)：（1）實(shí)際上索引也是一張表怠肋，該表保存了主鍵與索引字段，并指向?qū)嶓w表記錄淹朋，所以索引列也要占用空間（2）雖然索引大大提高了查詢的速度笙各，同時(shí)卻會(huì)降低更新表的速度，如果對(duì)表進(jìn)行insert础芍，update和delete因?yàn)楦卤頃r(shí)杈抢，需要花時(shí)間研究建立最優(yōu)秀的索引，或優(yōu)化調(diào)整
索引的分類
單值索引：即一個(gè)索引只包涵單個(gè)列仑性，一個(gè)表可以有多個(gè)單列索引
唯一索引：索引列的值必須唯一惶楼，但允許有空值
復(fù)合索引：即一個(gè)索引包涵多個(gè)列
創(chuàng)建索引的語(yǔ)法：
create [unique] index indexname on tablename(columnname(length));
alter tablename add [unique] index [indexname] on columnname(length);
drop index[indexname] on tablename;
show index from tablename;
索引的數(shù)據(jù)結(jié)構(gòu)
BTree索引
Hash索引
full-text索引
R-Tree索引
需要建立索引的情況
1.主鍵自動(dòng)建立唯一索引
2.頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引
3.查詢中與其他表關(guān)聯(lián)的字段，外健索引
4.頻繁更新的字段不適合創(chuàng)建索引
5.where條件里用不到的字段不創(chuàng)建索引
6.查詢中排序的字段诊杆，排序字段若通過(guò)排序索引去訪問(wèn)將大大提高排序速度
7.查詢中統(tǒng)計(jì)或者分組的字段
不需要建立索引的情況
1.表記錄太少
2.經(jīng)常增刪改的數(shù)據(jù)
3,數(shù)據(jù)大量重復(fù)的字段
性能優(yōu)化
mysql常見(jiàn)的性能瓶頸
explain關(guān)鍵字
（1）查看表的執(zhí)行順序
（2）讀取數(shù)據(jù)的操作類型
（3）表之間的引用關(guān)系
（4）每張表有多少行被優(yōu)化器查詢
explain+sql語(yǔ)句
explain的表頭
explain之id
select 查詢的執(zhí)行順序歼捐，包涵一組數(shù)字，表示查詢中執(zhí)行的順序
值兩種情況：
值相同晨汹，執(zhí)行順序由上至下豹储，
值不同，id越大淘这，越優(yōu)先被執(zhí)行
select_type:查詢類型
-
取值情況：
simple:簡(jiǎn)單的查詢颂翼，查詢中不包含子查詢或者union查詢
primary：查詢中如果包涵復(fù)雜的查詢晃洒，最外層的查詢，最后加載的查詢
subquery：在where或者select中包涵的子查詢
derived：在臨時(shí)表中進(jìn)行查詢
union：若第二個(gè)select出現(xiàn)在union之后朦乏，則被標(biāo)記為union球及，若union包含在from子句的子查詢中，外層select將被標(biāo)記為derived
union result：從union表中獲取結(jié)果的select
table:涉及的表名
type：訪問(wèn)類型
（1）type取值類型
all：全表掃描檢索
index:全索引掃描
range:只檢索給定范圍的行呻疹，使用索引來(lái)選擇行吃引，key類顯示使用了那些行
ref:非唯一性掃描，通過(guò)索引找到滿足條件的所有行刽锤，可以找多多行結(jié)果镊尺，
eq_ref:唯一性索引掃描，對(duì)于每個(gè)索引鍵可以找到唯一的數(shù)據(jù)
const：查詢條件是主鍵或者非NULL的UNIQUE索引并思，表示通過(guò)索引能夠確定唯一一條數(shù)據(jù)
system:表只有一行記錄庐氮，等于系統(tǒng)表，這是const類型的特例宋彼，平時(shí)不會(huì)出現(xiàn)
訪問(wèn)類型從好到差依次為：system>const>eq_ref>ref>range>index>ALL
possible_keys:
可能應(yīng)用在這張表的索引
key
實(shí)際查詢使用的索引
key_len
表示索引中使用的字節(jié)數(shù)弄砍，長(zhǎng)度越短越好
但是與精確相矛盾，長(zhǎng)度越長(zhǎng)输涕，匹配結(jié)果越精確
ref
查詢中與其他表關(guān)聯(lián)的字段音婶，現(xiàn)實(shí)使用到的其他表中的索引字段
rows
估算找出所需要的數(shù)據(jù)需要讀取的行數(shù)
Extra 包含一些前面沒(méi)有的信息
-
類型信息
Using filesort: mysql會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取莱坎，mysql中無(wú)法利用索引完成的排序操作叫做文件排序
using temporary：新建了臨時(shí)表衣式，mysql在對(duì)查詢進(jìn)行過(guò)進(jìn)行排序時(shí)使用臨時(shí)表，常見(jiàn)與order by 或group by
using index：使用覆蓋索引檐什，避免訪問(wèn)表的數(shù)據(jù)碴卧，如果同時(shí)出現(xiàn)using where 說(shuō)明使用索引進(jìn)行數(shù)據(jù)的查找，沒(méi)出現(xiàn)using where說(shuō)明利用索引讀取數(shù)據(jù)乃正，沒(méi)有使用索引進(jìn)行數(shù)據(jù)的查找
using where：使用了where
using join buffer 使用了鏈接緩存
impossible where ：where 子句的查詢結(jié)果為false螟深，不能獲取元祖
distinct:優(yōu)化了distinct
表的讀取順序：
數(shù)據(jù)讀取操作的操作類型：
那些索引可以被引用
那些索引被實(shí)際的引用：
表之間的引用
每張表有多少行被優(yōu)化器
索引的單表優(yōu)化
索引兩表優(yōu)化
join左鏈接，右表建立索引
join右鏈接烫葬，左表建立索引
索引三表優(yōu)化
索引優(yōu)化界弧，避免索引失效
1.建立索引的順序與select之后字段出現(xiàn)的順序相同，即出現(xiàn)全局匹配
2.最佳做前綴匹配法則
3.不要在索引列上進(jìn)行任何操作搭综，自動(dòng)或手動(dòng)的類型轉(zhuǎn)換會(huì)產(chǎn)生索引失效
4.存儲(chǔ)引擎不能夠使用范圍條件右邊的索引垢箕，范圍條件之后的索引會(huì)失效
5.盡量使用覆蓋索引，減少select*
6.盡量不要使用!=,<>兑巾，會(huì)產(chǎn)生索引失效
7.is null条获，is not null 也無(wú)法使用索引
8.使用模糊查詢，通配符不能出現(xiàn)在開(kāi)頭蒋歌，like %會(huì)產(chǎn)生索引失效帅掘，使用覆蓋索引優(yōu)化兩邊都有%的情況
9.字符串不使用單引號(hào)會(huì)造成索引失效
10.少用or委煤，會(huì)產(chǎn)生索引失效
查詢截取分析
查詢優(yōu)化過(guò)程
1.觀察，至少跑一天修档，查看產(chǎn)生的慢sql情況
2.開(kāi)啟慢查詢?nèi)罩颈探剩O(shè)置閾值，將慢sql抓取出來(lái)
3.explian+慢sql分析
4,show profilie查詢SQL在mysql服務(wù)器里面的執(zhí)行細(xì)節(jié)和生命周期
5,DBA修改sql數(shù)據(jù)庫(kù)服務(wù)器的參數(shù)調(diào)優(yōu)
總結(jié)：慢查詢的開(kāi)啟并捕獲吱窝。expalin分析讥邻，show profile擦村sql的執(zhí)行細(xì)節(jié)，sql數(shù)據(jù)庫(kù) 的參數(shù)調(diào)優(yōu)
查詢與排序優(yōu)化
（1）小表驅(qū)動(dòng)大表：小的數(shù)據(jù)集驅(qū)動(dòng)大的數(shù)據(jù)集
用in與exist
select * from A where id in (select * from B);
select * from A where exists(select 1 from B where A.id = B.id ) 將主查詢的結(jié)果放入子查詢中驗(yàn)證
（2）order by關(guān)鍵字的排序優(yōu)化院峡，排序順序與索引順序相同
如果排序的字段不在索引上兴使，會(huì)產(chǎn)生fileSort，排序的方法有兩種：雙路排序與單路排序
雙路排序：先讀取排序的字段在內(nèi)存中排序照激，然后排好的順序在表中讀取數(shù)據(jù)
單路排序：從磁盤讀取所有需要的列发魄，在內(nèi)存中排序后輸出，但是在大數(shù)據(jù)量的情況下會(huì)多次讀取
如果多次讀取俩垃，需要擴(kuò)大sort_buffer和sort_length的大小
（3）group by實(shí)質(zhì)是先排序后分組励幼，也遵循索引的最左前綴匹配原則
無(wú)法使用索引列時(shí)，增大max_length_for_sort_data參數(shù)的設(shè)置吆寨，增加唉sort_buffer_size參數(shù)的設(shè)置
慢查詢?nèi)罩荆河糜谟涗沵ysql相應(yīng)時(shí)間超過(guò)閾值的語(yǔ)句
手動(dòng)開(kāi)啟：show variables like '%slow_query_log%';
set global slow_query_log=1;
set slow_query_log_path=/var/lib/mysql/slow.log
show variables like 'long_query_time%'
set global long_query_time=4
/var/lib/mysql/下保留慢查詢?nèi)罩?/p>
mysql慢日志的分析工具mysqIdumpslow
得到返回結(jié)果最多的10個(gè)SQL:mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
得到使用最頻繁的10個(gè)SQL:mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log
得到按時(shí)間排序最多的10個(gè)SQL:mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
得到返回結(jié)果最多,且查詢語(yǔ)句中包含left jion的10個(gè)SQL:mysqldumpslow -s r -t 10 -g "left jion" /var/lib/mysql/slow.log
批量數(shù)據(jù)腳本,函數(shù)與存儲(chǔ)過(guò)程
show profile:mysql用來(lái)分析當(dāng)前會(huì)話中語(yǔ)句執(zhí)行的資源消耗情況赏淌，可以用于sql的調(diào)優(yōu)的測(cè)量
開(kāi)啟profile：
1.查看是否支持 show variables like 'profiling';
2.開(kāi)啟profile set profiling=1;
3.運(yùn)行sql
4.查看結(jié)果：show profiles;
5.診斷sql
show profile cpu踩寇，block io ,for query 3;
危險(xiǎn)的status
全局查詢?nèi)罩?/h3>
命令 set globel general_log=1;
set globel log_output='TABLE';
數(shù)據(jù)庫(kù)鎖理論
鎖的分類
讀鎖：共享鎖啄清，多個(gè)讀操作可以同時(shí)進(jìn)行
寫鎖：排他鎖，當(dāng)前寫操作沒(méi)有完成前俺孙，他會(huì)阻斷其他操作
表鎖：（偏讀）鎖粒度大辣卒，發(fā)生鎖沖突的概率最高，并發(fā)度最低
-- 增加表鎖： lock table 表名 1read(write),表名2read(write),其他
-- 查看表上加過(guò)的鎖 show open tables;
-- 釋放鎖：unlock tables;
-- 表鎖分析
show open tables;
show status like 'table%';
-
鎖定一行：begin;
? select *from table where id=8 for update;
? commit;
行鎖分析：show status like 'innodb_row_lock%';
image
MySIAM
mylock1 （read） mylock2
select mylock1 可以 select mylock1 可以
insert mylock1 不可以 insert mylock1 阻塞
select mylock2 不可以
mylock1 （write） mylock2
select mylock1 不可以 select mylock1 阻塞
insert mylock1 可以 insert mylock1 阻塞
select mylock2 不可以
行鎖
innoDB
讀己之所寫睛榄，其他session讀取不到
寫鎖相互互斥荣茫，操作同一行會(huì)阻塞
索引失效，行鎖升級(jí)為表鎖
間隙鎖的危害
當(dāng)使用范圍條件而不是相等條件檢索數(shù)據(jù)场靴，innnoDB會(huì)給在這個(gè)范圍內(nèi)的索引項(xiàng)進(jìn)行加鎖啡莉，對(duì)于鍵值在這個(gè)范圍內(nèi)，但是不存在的記錄旨剥，也會(huì)加鎖咧欣，這就是間隙鎖（next_key鎖）
如何鎖定一行
begin;
select * from table_name where id =8 for update;
commit;
行鎖的分析
show status like 'innodb_row_lock%'
主從復(fù)制
復(fù)制的基本原理
salve會(huì)從master讀取binlog來(lái)進(jìn)行數(shù)據(jù)同步
三步驟+原理圖：
復(fù)制的基本原則：
每個(gè)slave只有一個(gè)master
每個(gè)slave只能有唯一的服務(wù)器ID
每個(gè)master可以有多個(gè)salve
主機(jī)修改my.cnf文件
1.配置主服務(wù)器唯一ID servier-id=1
2.開(kāi)啟二進(jìn)制日志 log-bin
3.啟用錯(cuò)誤日志 log-err
4.根目錄baseidr
5.臨時(shí)目錄
6.數(shù)據(jù)目錄
7.配置不需要復(fù)制的數(shù)據(jù)庫(kù) binlog-ignore-db
8.配置需要復(fù)制的庫(kù) binlog-do-db
從機(jī)修改my.cnf文件
1.修改從服務(wù)器ID
2.開(kāi)啟二進(jìn)制文件復(fù)制
3.主機(jī)建立賬戶并授權(quán)slave
mysql>GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'從機(jī)數(shù)據(jù)庫(kù)IP' IDENTIFED BY '123456';
4.主機(jī)刷新一下
mysql>flush privileges;
5.show查看主機(jī)的復(fù)制狀態(tài)斧抱，主要記錄File和Position的值
6.在linux上配置需要復(fù)制的主機(jī)
7.開(kāi)啟從機(jī) start slave
