語句優(yōu)化

#(1) mysql 執(zhí)行流程

客戶端:

發(fā)送鏈接請求,然后發(fā)送sql語句

服務端:1.連接層: 提供和客戶端鏈接的服務

show processlist;查看所有登錄到mysql的用戶2.服務器:

(1)提供各種用戶使用的接口(增刪改查)

(2)提供sql優(yōu)化器(mysql query optimizer)

(發(fā)現(xiàn)sql語句執(zhí)行效率非常慢,會經(jīng)過優(yōu)化器優(yōu)化,然后把優(yōu)化的結果進行執(zhí)行)3.存儲引擎:

把得到的數(shù)據(jù)進行保存,

innodb : 支持事務處理,支持行鎖,支持高并發(fā)

myisam : 支持表鎖,不支持并發(fā).

把數(shù)據(jù)存儲在文件或者內(nèi)存當中"""create table ceshi_table(

id int primary key auto_increment,

name varchar(255)

)engine = myisam auto_increment=2 charset = utf8;"""

4.文件和日志

產(chǎn)生日志文件 binlog 二進制文件#(2) sql 卡頓原因

硬盤讀寫數(shù)據(jù),io延遲高,sql語句性能低,索引失效,導致sql執(zhí)行時間長

編寫過程:

select ..from.. join on where .. group by .. having .. order by limit

解析過程:from.. join on where group by having select distinct order by limit ..#(3) 索引

#索引(index)概念:

是一個樹狀的數(shù)據(jù)結構,即(B樹結構,分支節(jié)點>2)

相當于字典的目錄,功效是加快查詢速度;

常用樹: B樹(balance-tree) , 二叉樹,紅黑樹,hash樹#樹節(jié)點:

根節(jié)點(最頂級節(jié)點)

分支節(jié)點(父節(jié)點,子節(jié)點)

葉子節(jié)點(最后一層存儲數(shù)據(jù)的節(jié)點)

樹的高度(樹的層級,理想是三層)

[b+] : 在相鄰的葉子節(jié)點上,加入了雙向鏈表(指針),當前葉子節(jié)點不但保存當前值,還保存了下一個葉子節(jié)點的地址[小范圍數(shù)據(jù),查詢速度很快]

[b*] : 在相戀的分支節(jié)點上,加入了雙向鏈表(指針),當前分支節(jié)點不但保存當前值,還保存了下一個分支節(jié)點的地址[在大范圍里,找數(shù)據(jù)速度加快]#(4) innodb 和 myisam 的索引結構

(1) 聚集索引(聚簇索引) [innodb存儲引擎的特點,myisam沒有]

如果有主鍵,自動以主鍵作為聚集索引列(字段)

如果沒有主鍵,選擇唯一鍵

都沒有,自動生成隱藏聚集索引,該字段是6個字節(jié),類型為長整型;

分支節(jié)點是存儲下一層節(jié)點的最小值,用來劃分范圍,追求的矮胖的數(shù)據(jù)結構

在數(shù)據(jù)量變大的時候,盡量在樹層級高度不變的情況下,橫向發(fā)展,好處:可以減少io次數(shù),提升查詢效率

真實的數(shù)據(jù),直接在葉子節(jié)點上存儲,所以速度快.

(2) 輔助索引(非聚簇索引,二級索引,普通索引)

對這一列的數(shù)據(jù)先排序,劃分區(qū)間,把索引值分布到葉子節(jié)點上

輔助索引存儲的是加了索引的字段值和對應映射的主鍵id(primary key=>pk),沒有存儲真實的數(shù)據(jù)

通過找出這個主鍵id,再去聚集索引樹狀結構中查詢真實數(shù)據(jù);

輔助索引輔助聚集索引找數(shù)據(jù)的,輔助索引葉子節(jié)點重復值過多,會導致回表的次數(shù)增多,隨機產(chǎn)生的io減慢查詢效率

如果想要解決重復問題,使用聯(lián)合索引,更加精確找出對應唯一的那個id.

(3) 兩者區(qū)別:

myisam 和 innodb 使用的索引數(shù)據(jù)結構都是B+樹,但是在葉子節(jié)點上存儲的數(shù)據(jù)不同

innodb的文件結構中只有.frm 和 .ibd , 直接把數(shù)據(jù)存在了葉子節(jié)點上

myisam的文件結構中有.frm .myd .myi , 葉子節(jié)點上存儲的索引列,通過索引列映射對應的地址,在去通過這個地址找到實際的數(shù)據(jù)

innodb 一個表只有一個聚集索引,和多個輔助索引,排序速度更快

myisam 只能有多個輔助索引,沒有聚集索引

(4) 性能優(yōu)化:

利用索引查詢時,速度很快,相反,增刪改速度會變慢,會改變樹狀結構;

追求:讓每一個分支節(jié)點存儲的數(shù)據(jù)盡量小,減少樹狀結構縱向值高度上的增加#### part2 : 索引#1.常用索引種類:

普通索引(index)-提高查詢的效率

唯一索引:-主鍵索引 primary key : 在創(chuàng)建主鍵約束的同時,創(chuàng)建索引(不為空,唯一)-唯一索引 unique : 在創(chuàng)建唯一約束的同時,創(chuàng)建索引(允許為空,唯一)

聯(lián)合索引:-primary key() : 聯(lián)合主鍵索引-unique() : 聯(lián)合唯一索引-index() : 聯(lián)合普通索引#2.常用索引的應用場景

編號int,

姓名varchar(255),

身份證號char(18),

電話char(11),

住址varchar(255),

備注信息: text,

姓:char(10)

名:char(10)

編號int, 主鍵

姓名varchar,可以使用普通索引

身份證號char, 唯一索引unique

電話char,唯一索引

備注信息:text 全文索引 , 可使用fulltext(全文索引) 多數(shù)情況下使用第三方軟件Sphinx來運行

姓和名 ,通過來說一起查詢,可以使用聯(lián)合索引#3.常用的索引數(shù)據(jù)結構 hash樹 與 B-Tree

hash類型的索引: 數(shù)據(jù)在內(nèi)存中,通過鍵來獲取值,查詢單條數(shù)據(jù)最快,一個范圍的數(shù)據(jù)慢

B-Tree類型的索引: b+數(shù)(理想層級三級),三層B樹,理論上存放的數(shù)據(jù)量可以支撐百萬條數(shù)據(jù);#不同的存儲引擎支持的索引種類

innodb : 支持事務,行級鎖, 支持 B-Tree ,fulltext ,不支持hash類型索引結構

myisam : 支持表級鎖,不支持事務 支持 B-Tree , fulltext ,不支持hash類型索引結構

memory : 不支持事務,支持表級鎖 支持 B-Tree ,hash類型 ,不支持fulltext索引結構#4.建立索引#(1) 方法1,建表的時候,創(chuàng)建索引 index 索引名(索引字段)

create table t1(

id int primary key,

name char(100),

index index_name(name)

);#(2) 方法2.建表之后,創(chuàng)建索引 create index 索引名 on 表名(索引字段)

create table t2(

id int primary key,

name char(100)

);

create index index_name on t2(name);#(3) 方法3.建表之后,創(chuàng)建索引 alter table 表名 add index 索引名(索引字段)

create table t3(

id int primary key,

email char(100)

);

alter table t3 add index index_email(email)#(4) 刪除索引

drop index index_email on t3#5.正確使用索引

alter table s1 add index index_id(id) /create index index_id on s1(id)

select count(*) from s1 where id = 5;#發(fā)現(xiàn)加了索引和不加索引時間差距較大#注意加了索引之后,ibd文件會表達

#(1) 把頻繁作為搜索條件的字段作為索引,查單條數(shù)據(jù),如果查詢的是一個范圍內(nèi)的數(shù)據(jù),不能命中索引#范圍小的數(shù)據(jù) 表達范圍的符號: id > < >= <= != like "xboy" between and in...

#(2) 選一個字段作為索引,這個列(字段)必須是區(qū)分度較高的字段

"""這個字段對應的值,如果出現(xiàn)了大量重復,在通過輔助索引查詢的時候,會出現(xiàn)大量的隨機id,增加聚集索引中的查詢量,影響速度"""

"""區(qū)分度高的字段,推薦加上索引 ,必須系統(tǒng)會自動給primary key 和 unique 兩個約束自動加索引"""create index index_name on s1(name);

select count(*) from s1 where name = "xxxx"select count(*) from s1 where name = "xboyww" #不推薦使用區(qū)分度不高的字段加索引

#(3) 條件中,不能讓索引字段參與計算,不能命中索引

select * from s1 where id = 1000select* from s1 where id*3 = 3000

#(4) 條件中含有and , sql語句會經(jīng)過優(yōu)化器優(yōu)化.#1.如果有and 相連,找到第一個有索引的并且樹的高度最矮的字段進行優(yōu)化

select count(*) from s1 where email = "xboyww1000@oldboy"select count(*) from s1 where email = "xboyww1000@oldboy" and id = 1000select count(*) from s1 where name = "xboyww" and email = "xboyww1000@oldboy" and id = 1000

#2.如果有or相連,沒有優(yōu)化,所有語句從左到右執(zhí)行,索引會失去意義

select count(*) from s1 where id = 1000 or email = "xboyww1000@oldboy";#(5) 聯(lián)合索引 最左前綴原則 index(字段1,字段2 .... )

drop index index_id on s1;

drop index index_name on s1;

create index union_index on s1(first_name,last_name);

select count(*) from s1 where first_name="王6" and last_name="文6" #命中索引

select count(*) from s1 where last_name="文6" and first_name="王6" #命中索引#select count(*) from s1 where last_name="文6"; 不會命中索引,被標記的first_name不存在

select count(*) from s1 where first_name="王6" and gender = "man";

select count(*) from s1 where first_name="王6" and gender = "man" and name = "xboyw11w";#最左前綴原則: 被標記的MUL這個字段存在,就命中索引

first_name + name + gender +... (該字段存在即可)#(6) 其他

使用了函數(shù)不能命中索引

select count(*) from s1 where reverse(first_name) = "文2";

類型不匹配不能命中索引

select count(*) from s1 where first_name = 90;

總結

以上是生活随笔為你收集整理的MySQL繁忙度查询_mysql 慢查询优化的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

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