溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹了MySql中sql怎么優化，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。
system > const > eq_ref > ref > range > index > all。
system：表僅有一行，基本用不到；
const：表最多一行數據配合，主鍵查詢時觸發較多；
eq_ref：對于每個來自于前面的表的行組合，從該表中讀取一行。這可能是最好的聯接類型，除了const類型；
ref：對于每個來自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀取；
range：只檢索給定范圍的行，使用一個索引來選擇行。當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關鍵字列時，可以使用range；
index：該聯接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引文件通常比數據文件小；
all：全表掃描；
實際sql優化中，最后達到ref或range級別。
Using index：只從索引樹中獲取信息，而不需要回表查詢；
Using where：WHERE子句用于限制哪一個行匹配下一個表或發送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where并且表聯接類型為ALL或index，查詢可能會有一些錯誤。需要回表查詢。
Using temporary：mysql常建一個臨時表來容納結果，典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時；
索引原理及explain用法請參照前一篇：MySQL索引原理，explain詳解
CREATE TABLE `student` ( `id` int(10) NOT NULL, `name` varchar(20) NOT NULL, `age` int(10) NOT NULL, `sex` int(11) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, `phone` varchar(100) DEFAULT NULL, `create_time` timestamp NULL DEFAULT NULL, `update_time` timestamp NULL DEFAULT NULL, `deleted` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `student_union_index` (`name`,`age`,`sex`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
備注：因為mysql優化器的緣故，與索引順序不一致，也會觸發索引，但實際項目中盡量順序一致。
where和order by一起使用時，不要跨索引列使用。
alter table student add index student_union_index(name,age,sex);
優化一點，但效果不是很好，因為type是index類型，extra中依然存在using where。
因為sql的編寫過程
select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...
解析過程
from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...
因此我懷疑是聯合索引建的順序問題，導致觸發索引的效果不好。are you sure？試一下就知道了。
alter table student add index student_union_index2(age,sex,name);
刪除舊的不用的索引：
drop index student_union_index on student
索引改名
ALTER TABLE student RENAME INDEX student_union_index2 TO student_union_index
更改索引順序之后，發現type級別發生了變化，由index變為了range。
range：只檢索給定范圍的行，使用一個索引來選擇行。
備注：in會導致索引失效，所以觸發using where，進而導致回表查詢。
ref：對于每個來自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀取；
index 提升為ref了，優化到此結束。
保持索引的定義和使用順序一致性；
索引需要逐步優化，不要總想著一口吃成胖子；
將含in的范圍查詢，放到where條件的最后，防止索引失效；
CREATE TABLE `student` ( `id` int(10) NOT NULL, `name` varchar(20) NOT NULL, `age` int(10) NOT NULL, `sex` int(11) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, `phone` varchar(100) DEFAULT NULL, `create_time` timestamp NULL DEFAULT NULL, `update_time` timestamp NULL DEFAULT NULL, `deleted` int(11) DEFAULT NULL, `teacher_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `teacher` ( `id` int(11) DEFAULT NULL, `name` varchar(100) DEFAULT NULL, `course` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
explain select s.name,t.name from student s left join teacher t on s.teacher_id = t.id where t.course = '數學'
上一篇介紹過，聯合查詢時，小表驅動大表。小表也稱為驅動表。其實就相當于雙重for循環，小表就是外循環，第二張表（大表）就是內循環。
雖然最終的循環結果都是一樣的，都是循環一樣的次數，但是對于雙重循環來說，一般建議將數據量小的循環放外層，數據量大的放內層，這是編程語言的優化原則。
再次代碼測試：
student數據：四條
teacher數據：三條
按照理論分析，teacher應該為驅動表。
sql語句應該改為：
explain select teacher.name,student.name from teacher left join student on teacher.id = student.id where teacher.course = '數學'
優化一般是需要索引的，那么此時，索引應該怎么加呢？往哪個表上加索引？
索引的基本理念是：索引要建在經常使用的字段上。
由on teacher.id = student.id可知，teacher表的id字段使用較為頻繁。
left join on，一般給左表加索引；因為是驅動表嘛。
alter table teacher add index teacher_index(id); alter table teacher add index teacher_course(course);
備注：如果extra中出現using join buffer，表明mysql底層覺得sql寫的太差了，mysql加了個緩存，進行優化了。
小表驅動大表
索引建立在經常查詢的字段上
sql優化，是一種概率層面的優化，是否實際使用了我們的優化，需要通過explain推測。
1、復合索引，不要跨列或無序使用（最佳左前綴）；
2、符合索引，盡量使用全索引匹配；
3、不要在索引上進行任何操作，例如對索引進行（計算、函數、類型轉換），索引失效；
4、復合索引不能使用不等于（!=或<>）或 is null（is not null），否則索引失效；
5、盡量使用覆蓋索引（using index）；
6、like盡量以常量開頭，不要以%開頭，否則索引失效；如果必須使用%name%進行查詢，可以使用覆蓋索引挽救，不用回表查詢時可以觸發索引；
7、盡量不要使用類型轉換，否則索引失效；
8、盡量不要使用or，否則索引失效；
select name,age from student exist/in (子查詢);
如果主查詢的數據集大，則使用in；
如果子查詢的數據集大，則使用exist；
using filesort有兩種算法：雙路排序、雙路排序（根據IO的次數）
MySQL4.1之前，默認使用雙路排序；雙路：掃描兩次磁盤（①從磁盤讀取排序字段，對排序字段進行排序；②獲取其它字段）。
MySQL4.1之后，默認使用單路排序；單路：只讀取一次（全部字段），在buffer中進行排序。但單路排序會有一定的隱患（不一定真的是只有一次IO，有可能多次IO）。
注意：單路排序會比雙路排序占用更多的buffer。
單路排序時，如果數據量較大，可以調大buffer的容量大小。
set max_length_for_sort_data = 1024;單位是字節byte。
如果max_length_for_sort_data值太低，MySQL底層會自動將單路切換到雙路。
太低指的是列的總大小超過了max_length_for_sort_data定義的字節數。
提高order by查詢的策略：
選擇使用單路或雙路，調整buffer的容量大小；
避免select * from student;（① MySQL底層需要對*進行翻譯，消耗性能；② *永遠不會觸發索引覆蓋 using index）；
符合索引不要跨列使用，避免using filesort；
保證全部的排序字段，排序的一致性（都是升序或降序）；
慢查詢日志就是MySQL提供的一種日志記錄，用于記錄MySQL響應時間超過閾值的SQL語句（long_query_time，默認10秒） ；
慢日志默認是關閉的，開發調優時打開，最終部署時關閉。
（1）檢查是否開啟了慢查詢日志：
show variables like '%slow_query_log%'
（2）臨時開啟：
set global slow_query_log = 1;
（3）重啟MySQL：
service mysql restart;
（4）永久開啟：
/etc/my.cnf中追加配置：
放到[mysqld]下：
slow_query_log=1 slow_query_log_file=/var/lib/mysql/localhost-slow.log
（1）查看默認閾值：
show variables like '%long_query_time%'
（2）臨時修改默認閾值：
set global long_query_time = 5;
（3）永久修改默認閾值：
/etc/my.cnf中追加配置：
放到[mysqld]下：
long_query_time = 5;
（4）MySQL中的sleep：
select sleep(5);
（5）查看執行時間超過閾值的sql：
show global status like '%slow_queries%';
慢查詢的sql被記錄在日志中，可以通過日志查看具體的慢sql。
cat /var/lib/mysql/localhost-slow.log
通mysqldumpslow工具查看慢sql，可以通過一些過濾條件，快速查出需要定位的慢sql。
mysqldumpslow --help
參數簡要介紹：
s：排序方式
r：逆序
l：鎖定時間
g：正則匹配模式
（1）返回記錄最多的3個SQL
mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log
（2）獲取訪問次數最多的3個SQL
mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log
（3）按時間排序，前10條包含left join查詢語句的SQL
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log
打開此功能：set profiling = on;
show profiles會記錄所有profileing打來之后，全部SQL查詢語句所花費的時間。
缺點是不夠精確，確定不了是執行哪部分所消耗的時間，比如CPU、IO。
show profile all for query 上一步查詢到的query_id。
show variables like '%general_log%'
開啟全局日志：
set global general_log = 1;
set global log_output = table;
讀寫：對同一個數據，多個讀操作可以同時進行，互不干擾。
寫鎖：如果當前寫操作沒有完畢，則無法進行其它的讀寫操作。
表鎖：一次性對一張表整體加鎖。
如MyISAM存儲引擎使用表鎖，開銷小、加鎖快、無死鎖；但鎖的范圍大，容易發生沖突、并發度低。
行鎖：一次性對一條數據加鎖。
如InnoDB存儲引擎使用的就是行鎖，開銷大、加鎖慢、容易出現死鎖；鎖的范圍較小，不易發生鎖沖突，并發度高（很小概率發生高并發問題：臟讀、幻讀、不可重復讀）
lock table 表1 read/write，表2 read/write，...
查看加鎖的表：
show open tables;
會話0： lock table student read; select * from student; --查，可以 delete from student where id = 1;--增刪改，不可以 select * from user; --查，不可以 delete from user where id = 1;--增刪改，不可以
如果某一個會話對A表加了read鎖，則該會話可以對A表進行讀操作、不能進行寫操作。即如果給A表加了讀鎖，則當前會話只能對A表進行讀操作，其它表都不能操作
會話1： select * from student; --查，可以 delete from student where id = 1;--增刪改，會“等待”會話0將鎖釋放 會話1： select * from user; --查，可以 delete from user where id = 1;--增刪改，可以
會話0給A表加了鎖，其它會話的操作①可以對其它表進行讀寫操作②對A表：讀可以，寫需要等待釋放鎖。
會話0： lock table student write;
當前會話可以對加了寫鎖的表，可以進行任何增刪改查操作；但是不能操作其它表；
其它會話：
對會話0中對加寫鎖的表，可以進行增刪改查的前提是：等待會話0釋放寫鎖。
MyISAM在執行查詢語句前，會自動給涉及的所有表加讀鎖，在執行增刪改前，會自動給涉及的表加寫鎖。
所以對MyISAM表進行操作，會有如下情況發生：
（1）對MyISAM表的讀操作（加讀鎖），不會阻塞其它會話（進程）對同一表的讀請求。但會阻塞對同一表的寫操作。只有當讀鎖釋放后，才會執行其它進程的寫操作。
（2）對MyISAM表的寫操作（加寫鎖），會阻塞其它會話（進程）對同一表的讀和寫操作，只有當寫鎖釋放后，才會執行其它進程的讀寫操作。
查看哪些表加了鎖：
show open tables;1代表被加了鎖
分析表鎖定的嚴重程度：
show status like 'table%';
Table_locks_immediate：可能獲取到的鎖數
Table_locks_waited：需要等待的表鎖數（該值越大，說明存在越大的鎖競爭）
一般建議：Table_locks_immediate/Table_locks_waited > 5000，建議采用InnoDB引擎，否則采用MyISAM引擎。
為了研究行鎖，暫時將自動commit關閉，set autocommit = 0;
show status like '%innodb_row_lock%';
Innodb_row_lock_current_waits：當前正在等待鎖的數量
Innodb_row_lock_time：等待總時長。從系統啟動到現在一共等待的時間
Innodb_row_lock_time_avg：平均等待時長。從系統啟動到現在一共等待的時間
Innodb_row_lock_time_max：最大等待時長。從系統啟動到現在一共等待的時間
Innodb_row_lock_waits：等待次數。從系統啟動到現在一共等待的時間
（1）查詢student
select id,name,age from student
（2）更新student
update student set age = 18 where id = 1
（3）加行鎖
通過select id,name,age from student for update;給查詢加行鎖。
依舊修改成功，原因是MySQL默認是自動提交的，因此需要暫時將自動commit關閉
set autocommit = 0;
（1）如果沒有索引，行鎖自動轉為表鎖。
（2）行鎖只能通過事務解鎖。
（3）InnoDB默認采用行鎖
優點：并發能力強，性能高，效率高
缺點：比表鎖性能損耗大
高并發用InnoDb，否則用MyISAM。
感謝你能夠認真閱讀完這篇文章，希望小編分享的“MySql中sql怎么優化”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
