當(dāng)前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

Sql性能优化

發(fā)布時(shí)間：2025/7/14 编程问答 33 豆豆

生活随笔收集整理的這篇文章主要介紹了 Sql性能优化小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

Mysql數(shù)據(jù)庫優(yōu)化是多方面的，原則是減少系統(tǒng)的瓶頸，減少資源的占用，增加系統(tǒng)的反應(yīng)速度。例如，通過優(yōu)化文件系統(tǒng)，提高磁盤I\0的讀寫速度；通過優(yōu)化操作系統(tǒng)調(diào)度策略，提高M(jìn)ySQL在高負(fù)荷情況下的負(fù)載能力；優(yōu)化表結(jié)構(gòu)，索引、查詢語句等使查詢響應(yīng)更快。

在MySQL中，可以使用SHOW STATUS 語句查詢一些MySQL的性能參數(shù)

1 SHOW STATUS LIKE 'value'; 2 常用性能的參數(shù)如下： 3 Connections：連接mysql服務(wù)器的次數(shù) 4 Uptime：MySQL服務(wù)器上線的時(shí)間 5 SLow_queries：慢查詢的次數(shù) 6 Com_selet：查詢操作的次數(shù) 7 Com_insert：插入操作的次數(shù) 8 Com_update：修改操作的次數(shù) 9 Com_delete：刪除操作的次數(shù)

利用EXPLAGIN分析查詢語句/h2>

　　EXPLAGIN語句的基本語法

explain select * from tbl_order_goods\G

1、id：select的查詢序列號，值越大優(yōu)先級越高，越先被執(zhí)行

2、select_type：select語句的類型。有幾種取值

(1)SIMPLE 簡單查詢，不包括連接查詢和子查詢

(2)PRIMARY 主查詢或者是最外層查語句詢，不包括連接查詢和子查詢

(3)UNION和UNION RESULT ?UNION 表示連接查詢的第二個或后面的查詢語句，不依賴于外部查詢的結(jié)果集。UNION RESULT表示UNION查詢的結(jié)果集

(4)dependent union 表示連接查詢的第二個或后面的select語句，取決于外面的查詢

(5)subquery 表示子查詢中的第一個select語句，不依賴于外部查詢的結(jié)果集

(6)dependent subquery 表示子查詢中的第一個select語句，取決于外面查詢的

(7)derived 查詢類型用于from字句里有子查詢的情況。MySQL會遞歸執(zhí)行這些子查詢，把結(jié)果放在臨時(shí)表里。

4、type 表示表的連接類型

3 system 表只有一行 4 const 表最多只有一行匹配，通用用于主鍵或者唯一索引比較時(shí) 5 eq_ref 每次與之前的表合并行都只在該表讀取一行，這是除了system，const之外最好的一種， 6 特點(diǎn)是使用=，而且索引的所有部分都參與join且索引是主鍵或非空唯一鍵的索引 7 ref 如果每次只匹配少數(shù)行，那就是比較好的一種，使用=或<=>，可以是左覆蓋索引或非主鍵或非唯一鍵 8 fulltext 全文搜索 9 ref_or_null 與ref類似，但包括NULL 10 index_merge 表示出現(xiàn)了索引合并優(yōu)化(包括交集，并集以及交集之間的并集)，但不包括跨表和全文索引。 11 這個比較復(fù)雜，目前的理解是合并單表的范圍索引掃描（如果成本估算比普通的range要更優(yōu)的話） 12 unique_subquery 在in子查詢中，就是value in (select...)把形如“select unique_key_column”的子查詢替換。 13 PS：所以不一定in子句中使用子查詢就是低效的！ 14 index_subquery 同上，但把形如”select non_unique_key_column“的子查詢替換 15 range 常數(shù)值的范圍 16 index a.當(dāng)查詢是索引覆蓋的，即所有數(shù)據(jù)均可從索引樹獲取的時(shí)候（Extra中有Using Index）； 17 b.以索引順序從索引中查找數(shù)據(jù)行的全表掃描（無 Using Index）； 18 c.如果Extra中Using Index與Using Where同時(shí)出現(xiàn)的話，則是利用索引查找鍵值的意思； 19 d.如單獨(dú)出現(xiàn)，則是用讀索引來代替讀行，但不用于查找 20 all 全表掃描

5、possible_keys:可能使用到的索引，如果是NULL，則沒有相關(guān)的索引

6、key:經(jīng)過優(yōu)化器評估最終使用的索引

7、key_length:使用到的索引長度

8、ref:引用到的上一個表的列

9、rows:rows_examined，要得到最終記錄索要掃描經(jīng)過的記錄數(shù)

10、Extra:額外的信息說明

? ?10.1、Using index?
　　　mysql使用了覆蓋索引，避免訪問了鏢的數(shù)據(jù)行，效率不錯

　10.2、Using where?
　　表示服務(wù)器在存儲引擎收到行后將進(jìn)行過濾。?

? 10.3、Using temporary?
　　Mysql對查詢結(jié)果進(jìn)行排序的時(shí)候使用了一張臨時(shí)表。

? 10.3、

　　Using filesort ?mysql會對數(shù)據(jù)使用一個外部的索引排序

　　當(dāng)出現(xiàn)Using temporary 和Using filesort時(shí)，需要對查詢語句進(jìn)行優(yōu)化操作。

如何使用索引查詢

1、使用like關(guān)鍵字查詢語句

在使用like關(guān)鍵字進(jìn)行查詢的查詢語句中，如果匹配字符串的第一個字符為"%"，索引不會起作用。只有"%"不再第一個位置，索引才會起作用

2、使用多列索引查詢語句

一個索引可以包括16個字段。對于多列索引，只有查詢條件中使用了這些字段中第一個字段時(shí)，索引才會被使用

3、使用or關(guān)鍵字的查詢語句

查詢條件中只有or關(guān)鍵字，且or前后的兩個條件中的列都是索引時(shí)，查詢中才使用索引。否則，查詢將不使用索引。

優(yōu)化insert語句

第一種：

insert into tbl_shop_order (id,name)values(1,'小雞燉蘑菇')要比insert into tbl_shop_order (id,name)value(1,'小雞燉蘑菇')速度快。

當(dāng)一個文本文件載入一個表時(shí)，使用load data infile加載數(shù)據(jù)往往比使用很多insert語句效率至少提高20倍。

例：數(shù)據(jù)庫結(jié)構(gòu)

1 CREATE TABLE `t0` ( 2 `id` bigint(20) unsigned NOT NULL auto_increment, 3 `name` char(20) NOT NULL, 4 `age` tinyint(3) unsigned NOT NULL, 5 `description` text NOT NULL, 6 PRIMARY KEY (`id`), 7 UNIQUE KEY `idx_name` (`name`),
8 update_time? timestamp not null 9 ) ENGINE=MyISAM DEFAULT CHARSET=utf8

s.txt文件內(nèi)容

1 "我愛你","20","相貌平常，經(jīng)常耍流氓！哈哈" 2 "李奎","21","相貌平常，經(jīng)常耍流氓！哈哈" 3 "王二米","20","相貌平常，經(jīng)常耍流氓！哈哈" 4 "老三","24","很強(qiáng)" 5 "老四","34","XXXXX" 6 "老五","52","***%*￥*￥*￥*￥" 7 "小貓","45","中間省略。。。" 8 "小狗","12","就會叫" 9 "小妹","21","PP的很" 10 "小壞蛋","52","表里不一" 11 "上帝他爺","96","非常英俊" 12 "MM來了","10","。。。" 13 "歌頌黨","20","社會主義好" 14 "人民好","20","的確是好" 15 "老高","10","學(xué)習(xí)很好" 16 "斜三","60","眼睛斜了" 17 "中華之子","100","威武的不行了" 18 "大米","63","我愛吃" 19 "蘋果","15","好吃" View Code

執(zhí)行sql語句

load data infile 'G:/s.txt' ignore into table t0 character set gbk fields terminated by ',' enclosed by '"' lines terminated by '\n'(`name`,`age`,`description`)set update_time=current_timestamp;

第二種：對于myisam類型的表，可以通過insert into delayed語句提升執(zhí)行速度。insert delayed into是客戶端提交數(shù)據(jù)給mysql服務(wù)器，mysql服務(wù)器返回OK狀態(tài)碼給客戶端，實(shí)際上，數(shù)據(jù)還沒有被插入到表，而是存儲在內(nèi)存里面等待排隊(duì)，當(dāng)mysql服務(wù)器空閑時(shí)在執(zhí)行插入。優(yōu)點(diǎn)是速度快，缺點(diǎn)是，系統(tǒng)崩潰，數(shù)據(jù)會丟失

第三種：鎖表，加快插入數(shù)據(jù)速度

1 lock tables tbl_shop_order write; 2 insert into tbl_shop_order (id,name)values(1,'小雞燉蘑菇'); 3 unlock tables;

如果不加鎖定表，每一次insert完成之后，索引緩沖區(qū)都會被寫到磁盤上，加入鎖定后索引緩沖區(qū)僅被寫到磁盤上1次

優(yōu)化order by語句

1、order by id/order by id索引優(yōu)化(這應(yīng)該是常識，大家都懂,id加索引)

select * from tbl_shop_order where id=10 select * from tbl_shop_order order by id

2、order by id?+limit 組合的索引優(yōu)化

select * from tbl_shop_order where name='天王蓋地虎' order by id

只對id添加索引，效率不是很高，更加高效的方法就是建一個聯(lián)合索引(name,id)

3、不要對where和order by 的選項(xiàng)使用表達(dá)式或者函數(shù)

4、order by 的字段混合使用asc和desc(估計(jì)沒人這么干過)

select * from tbl_shop_order order by id desc,name asc;此方法不應(yīng)該使用索引

5、where字句使用的字段和order by的字段不一致

select * from tbl_shop_orde where id=1 order by name;此方法不應(yīng)該使用索引

6、對不同的關(guān)鍵字使用order by 排序

select * from tbl_shop_orde order by id,name;此方法不應(yīng)該使用索引

優(yōu)化group by語句

使用grouo by語句時(shí)，默認(rèn)情況，mysql會對符合的結(jié)果自動排序。通過掃描整個表并創(chuàng)建一個新的臨時(shí)表，表中美工組的所有行應(yīng)為連續(xù)，然后使用該臨時(shí)表來找到組并應(yīng)用累計(jì)行數(shù)。

group by 優(yōu)先于order by
group by是分組查詢，一般與聚會函數(shù)配合使用

通過使用 order by null禁止排序，從而可以節(jié)省耗損

explain select wm_order_platform,count(*) as c from tbl_shop_order group by wm_order_id order by null;

優(yōu)化嵌套查詢

1、使用用join代替子查詢（避免了創(chuàng)建臨時(shí)表）

2、兩張表關(guān)聯(lián)時(shí)，大表關(guān)聯(lián)小表，這樣速度更快

優(yōu)化or 條件

使用or條件語句，如果要使用索引，則or直接的每個條件都必須使用到索引，如果沒有，考慮添加索引。

explain select * from tbl_shop_order where id=1 or wm_order_id=2;

優(yōu)化插入記錄的速度

插入記錄時(shí)，影響插入速度的主要是索引，唯一性教研，一次插入記錄條數(shù)等。

1、禁用索引

對于非空表，插入記錄時(shí)，mysql會根據(jù)表的索引隊(duì)插入的記錄建立索引，如果要插入大力的數(shù)據(jù)，建立索引會降低插入記錄的速度。可以在插入記錄之前禁用索引，插入完成之后在開啟索引

alter table tbl_shop_order DISABLE KEYS;(tbl_shop_order 表名)

開啟索引

alter table tbl_shop_order ENABLE KEYS;

2、禁用唯一性檢查

SET UNIQUE_CHECK=0;（禁用）
load ...
SET UNIQUE_CHECK=1;（開啟）

3、批量插入

insert into tbl_shop_order (id,name)values(1,'小雞燉蘑菇'),values(1,'小雞燉蘑菇'),values(1,'小雞燉蘑菇'),values(1,'小雞燉蘑菇');

4、使用load命令批量導(dǎo)入

以下是針對myisam存儲引擎的表

alter table t0 disable keys; load .... alter table t0 enablekeys;

以下是針對innoDB存儲引擎的表

1、禁用唯一性檢查

set unique_checks=0 load .... set unique_checks=1

2、禁用外鍵檢查

set foreign_key_checks=0 load .... set foreign_key_checks=1

3、禁止自動提交

set autocommit=0 load .... set autocommit=1

優(yōu)化數(shù)據(jù)庫結(jié)構(gòu)

1、將字段很多的表分解成多個表

2、增加中間表（對于經(jīng)常聯(lián)合查詢的表，可以建立中間表以提高查詢效率）

3、增加冗余字段（設(shè)計(jì)數(shù)據(jù)庫時(shí)應(yīng)盡量遵循范式理論的規(guī)約，盡可能減少冗余字段。但是適當(dāng)?shù)脑黾尤哂嘧侄?#xff0c;可以提高查詢速度）

分析表

分析表主要是分析關(guān)鍵字的分布

analyze table t0;

table：表名?

op：執(zhí)行的操作 .analyze表示進(jìn)行分析操作

msg_type：信息類型。通常為狀態(tài)（status）,信息（info）,注意（note）,警告（warning）和錯誤（error）

msg_text：顯示信息

analyz table分析表的過程中，數(shù)據(jù)庫系統(tǒng)會自動對表加一個只讀鎖。分析期間只能讀，不能改、增加。analyz table可以分析mysiam和innoDB

檢查表

檢查表主要是檢查表是否存在錯誤

check table 可以堅(jiān)持mysiam和innodb類型的表是否存在錯誤。對與myisam類型的表check table語句會更新關(guān)鍵字統(tǒng)計(jì)數(shù)據(jù)。而且check table可以堅(jiān)持師徒是否有錯誤

check table t0

優(yōu)化表

優(yōu)化表主要是消除刪除或者更新造成的空間浪費(fèi)

optilmize table語句來優(yōu)化表

optilmize table只能優(yōu)化表中的varchar,blob或text類型的字段

optilmize table語句可以消除刪除和更新造成的文件碎片。optilmize table在執(zhí)行過程中也會給表加上只讀鎖

本文乃《MySQL技術(shù)精粹->架構(gòu)、高級特性、性能優(yōu)化與集群實(shí)戰(zhàn)》讀書筆記！

轉(zhuǎn)載于:https://www.cnblogs.com/xb88/p/8505331.html

《新程序員》：云原生和全面數(shù)字化實(shí)踐50位技術(shù)專家共同創(chuàng)作，文字、視頻、音頻交互閱讀

總結(jié)

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

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

SQL
性能