①②③④⑤⑥⑦⑧⑨

查詢(xún)執(zhí)行基礎(chǔ)知識(shí)

mysql執(zhí)行查詢(xún)過(guò)程

① 客戶端將查詢(xún)發(fā)送到服務(wù)器

② 服務(wù)器檢查查詢(xún)緩存 如果找到了就從緩存返回結(jié)果 否則進(jìn)行下一步

③ 服務(wù)器解析,預(yù)處理和優(yōu)化查詢(xún),生成執(zhí)行計(jì)劃

④ 執(zhí)行引擎調(diào)用存儲(chǔ)引擎api執(zhí)行查詢(xún)

⑤ 服務(wù)器將結(jié)果發(fā)送回客戶端

mysql客戶端/服務(wù)器協(xié)議

該協(xié)議是半雙工通信,可以發(fā)送或接收數(shù)據(jù),但是不能同時(shí)發(fā)送和接收決定了mysql的溝通簡(jiǎn)單又快捷

缺點(diǎn):無(wú)法進(jìn)行流程控制,一旦一方發(fā)送消息,另一方在發(fā)送回復(fù)之前必須提取完整的消息,就像

拋球游戲，任意時(shí)間,只有某一方有球,而且有球在手上,否則就不能把球拋出去(發(fā)送消息)

mysql客戶端發(fā)送/服務(wù)器響應(yīng)

可以設(shè)定max_packet_size這個(gè)參數(shù)控制客戶端發(fā)送的數(shù)據(jù)包(一旦發(fā)送數(shù)據(jù)包,唯一做的就是等待結(jié)果)

服務(wù)器發(fā)送的響應(yīng)由多個(gè)數(shù)據(jù)包組成, 客戶端必須完整接收結(jié)果，即使只需要幾行數(shù)據(jù),也得等到全部接收 然后丟掉，或者強(qiáng)制斷開(kāi)連接

(這兩個(gè)方法好挫,所以我們使用limit子句呀！！)

也可以理解,客戶端從服務(wù)器 "拉" 數(shù)據(jù) ,實(shí)際是服務(wù)器產(chǎn)生數(shù)據(jù) "推"到客戶端, 客戶端不能說(shuō)不要 是必須全部裝著 !!

常用的Mysql類(lèi)庫(kù) 其實(shí)是從客戶端提取數(shù)據(jù) 緩存到array(內(nèi)存)中，然后進(jìn)行 foreach 處理

但是對(duì)于龐大的結(jié)果集裝載在內(nèi)存中需要很長(zhǎng)時(shí)間 如果不緩存 使用較少的內(nèi)存并且可以盡快工作 但是應(yīng)用程序和類(lèi)庫(kù)交互時(shí)候

服務(wù)器端的鎖和資源都是被鎖定的

查詢(xún)狀態(tài)

每個(gè)mysql連接都是mysql服務(wù)器的一個(gè)線程 任意一個(gè)給定的時(shí)間都有一個(gè)狀態(tài)來(lái)標(biāo)識(shí)正在發(fā)生的事情

使用 show full processlist 命令查看

mysql中一共有12個(gè)狀態(tài)

休眠 查詢(xún) 鎖定 分析和統(tǒng)計(jì) 拷貝到磁盤(pán)上的臨時(shí)表 排序結(jié)果 發(fā)送數(shù)據(jù)

通過(guò)這些狀態(tài) 知道 "球在誰(shuí)手上"

查詢(xún)緩存

解析一個(gè)查詢(xún) 如果開(kāi)啟了緩存 mysql會(huì)檢查查詢(xún)緩存 發(fā)現(xiàn)緩存匹配 返回緩存之前 檢查查詢(xún)的權(quán)限

優(yōu)化數(shù)據(jù)訪問(wèn)

查詢(xún)性能低下最基本的原因是訪問(wèn)了太多的數(shù)據(jù)

分析兩方面：

① 查明應(yīng)用程序是否獲取超過(guò)需要的數(shù)據(jù) 通常意味著訪問(wèn)了過(guò)多的行或列

② 查明mysql服務(wù)器是否分析了超過(guò)需要的行

向服務(wù)器請(qǐng)求了不需要的數(shù)據(jù)

一般請(qǐng)求不需要的數(shù)據(jù) 再丟掉他們 造成服務(wù)器額外的負(fù)擔(dān) 增加網(wǎng)絡(luò)開(kāi)銷(xiāo) 消耗了內(nèi)存和cpu

典型的錯(cuò)誤:

① 提取超過(guò)需要的行 => 添加 limit 10 控制獲取行數(shù)

② 多表聯(lián)接提取所有列 => select fruit.* from fruit left join fruit_juice where

.....

③ 提取所有的列 => select id,name... from fruit ... (有時(shí)提取超過(guò)需要的數(shù)據(jù)便于復(fù)用)

mysql檢查了太多數(shù)據(jù)

簡(jiǎn)單的開(kāi)銷(xiāo)指標(biāo)：執(zhí)行時(shí)間 、 檢查的行數(shù) 、返回的行數(shù)

以上三個(gè)指標(biāo)寫(xiě)入了慢查詢(xún)?nèi)罩?可以使用 mysqlsla工具進(jìn)行日志分析

① 執(zhí)行時(shí)間：執(zhí)行時(shí)間只是參考 不可一概而論 因?yàn)閳?zhí)行時(shí)間 和服務(wù)器當(dāng)時(shí)負(fù)載有關(guān)

② 檢查和返回的行：理想情況下返回的行和檢查的行一樣，但是顯示基本不可能 比如聯(lián)接查詢(xún)

③ 檢查的行和訪問(wèn)類(lèi)型： 使用 explain sql語(yǔ)句 觀察 type 列

typ列：(訪問(wèn)速度依次遞增)

① 全表掃描(full table scan)

② 索引掃描(index scan)

③ 范圍掃描(range scan)

④ 唯一索引查找(unique index lookup)

⑤ 常量(constant)

可見(jiàn) type 列為 index 即 sql 語(yǔ)句 基于 索引掃描

rows 列 為 12731 即 掃描了 12731 行

extra列為 using index 即 使用索引過(guò)濾不需要的行

mysql會(huì)在3種情況下使用where子句 從最好到最壞依次是：

① 對(duì)索引查找應(yīng)用where子句來(lái)消除不匹配的行 這發(fā)生在存儲(chǔ)層

② 使用覆蓋索引(extra 列 "using index") 避免訪問(wèn)行 從索引取得數(shù)據(jù)過(guò)濾不匹配的行 這發(fā)生在服務(wù)層不需要從表中讀取行

③ 從表中檢索出數(shù)據(jù) 過(guò)濾不匹配的行(extra:using where)

如果發(fā)現(xiàn)訪問(wèn)數(shù)據(jù)行數(shù)很大,嘗試以下措施：

① 使用覆蓋索引 ,存儲(chǔ)了數(shù)據(jù) 存儲(chǔ)引擎不會(huì)讀取完整的行

② 更改架構(gòu)使用匯總表

③ 重寫(xiě)復(fù)雜的查詢(xún) 讓mysql優(yōu)化器優(yōu)化執(zhí)行它

重構(gòu)查詢(xún)的方式

優(yōu)化有問(wèn)題的查詢(xún) 其實(shí)也可以找到替代方案 提供更高的效率

復(fù)雜查詢(xún)和多個(gè)查詢(xún)

mysql一般服務(wù)器可以每秒50000個(gè)查詢(xún)

常規(guī)情況下，使用盡可能少的查詢(xún) 有時(shí)候分解查詢(xún)得到更高的效率

縮短查詢(xún)

分治法,查詢(xún)本質(zhì)上不變,每次執(zhí)行一小部分,以減少受影響的行數(shù)

比如清理陳舊的數(shù)據(jù) 每次清理1000條

delete from message where create < date_sub(now(),inteval 3 month)

limit 1000

防止長(zhǎng)時(shí)間鎖住很多行的數(shù)據(jù)

分解聯(lián)接

把一個(gè)多表聯(lián)接分解成多個(gè)單個(gè)查詢(xún) 然后在應(yīng)用程序?qū)崿F(xiàn)聯(lián)接操作

select * from teacher

join school on teacher.id = school.id

join course on teacher.id = course.id

where course.name= 'english'

使用一下語(yǔ)句代替

select * from course where name = 'english'

select * from teacher where course_id = 1024

select * from school where teacher_id in (111,222,333)

第一眼看上去比較浪費(fèi),因?yàn)樵黾恿瞬樵?xún)數(shù)量,但是有重大的性能優(yōu)勢(shì)

① 緩存效率高,應(yīng)用程序直接緩存了表 類(lèi)似第一個(gè)查詢(xún)直接跳過(guò)

② 對(duì)于myisam表來(lái)說(shuō) 每個(gè)表一個(gè)查詢(xún)有效利用表鎖 查詢(xún)鎖住表的時(shí)間縮短

③ 應(yīng)用程端進(jìn)行聯(lián)接更方便擴(kuò)展數(shù)據(jù)庫(kù)

④ 使用in() 避免聯(lián)表查詢(xún)id排序的耗費(fèi)

⑤ 減少多余行的訪問(wèn) , 意味著每行數(shù)據(jù)只訪問(wèn)一次 避免聯(lián)接查詢(xún)的非正則化的架構(gòu)帶來(lái)的反復(fù)訪問(wèn)同一行的弊端

分解聯(lián)接應(yīng)用場(chǎng)景：

① 可以緩存早期查詢(xún)的大量的數(shù)據(jù)

② 使用了多個(gè)myisam表(mysiam表鎖 并發(fā)時(shí)候 一條sql鎖住多個(gè)表 所以要分解)

③ 數(shù)據(jù)分布在不同的服務(wù)器上

④ 對(duì)于大表使用in() 替換聯(lián)接

⑤ 一個(gè)聯(lián)接引用了同一個(gè)表很多次

提取隨機(jī)行

select * from area order by rand() limit 5;

分組查詢(xún)

select cname,pname,count(pname) from user by (cname pname with rollup )

外鍵

只有Innodb引擎支持外鍵，myisam可以添加外鍵但是沒(méi)有效果

主表添加主鍵id 從表添加外鍵id引用主表的id

表student

create table `student` (

`id` int(11) not null auto_increment,

`name` varchar(255) default null,

primary key (`id`)

) engine=innodb auto_increment=7 default charset=utf8

表student_extend

create table `student_extend` (

`student_id` int(11) default null,

`age` smallint(5) default null,

key `student_id` (`student_id`),

constraint `student_index` foreign key (`student_id`)

references `student` (`id`) on delete cascade on update no action

) engine=innodb default charset=utf8

為student_extend添加外鍵 外鍵指向 student 表中的id 列 在delete時(shí)觸發(fā)外鍵

表student數(shù)據(jù)

表student_extend數(shù)據(jù)

刪除表student一條數(shù)據(jù) 則 外鍵表就會(huì)觸發(fā)外鍵 刪除對(duì)應(yīng)數(shù)據(jù)

delete from student where id = 2;

優(yōu)化聯(lián)合查詢(xún)

select * from A limit 10 union all select * from B limit 10

優(yōu)化max() min()

其中 name 沒(méi)有索引

select min(id) from fruit where name = "banana"

==>

select id from fruit use index(PRIMARY) where name = 'banana' limit 1

對(duì)一個(gè)表同時(shí)進(jìn)行select 和 update

總結(jié)

以上是生活随笔為你收集整理的mysql 架构优化_Mysql 架构及优化之-查询性能优化的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。