正确使用索引（sql优化），limit分页优化，执行计划，慢日志查询
查看表相關(guān)命令
- 查看表結(jié)構(gòu)? ?desc 表名
- 查看生成表的SQL
? ?show?create?table?表名
- 查看索引
? ?show?index?from??表名
?
使用索引和不使用索引
由于索引是專門用于加速搜索而生，所以加上索引之后，查詢效率會快到飛起來。# 有索引
mysql> select * from tb1 where name = 'zhangqiye'; +-----+-------------+---------------------+----------------------------------+---------------------+ | nid | name | email | radom | ctime | +-----+-------------+---------------------+----------------------------------+---------------------+ | 889 | zhangqiye | zhangqiye@live.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 | +-----+-------------+---------------------+----------------------------------+---------------------+ 1 row in set (0.00 sec) ? # 無索引 mysql> select * from tb1 where email = 'zhangqiye@live.com'; +-----+-------------+---------------------+----------------------------------+---------------------+ | nid | name | email | radom | ctime | +-----+-------------+---------------------+----------------------------------+---------------------+ | 889 | zhangqiye | wupeiqi888@live.com | 5312269e76a16a90b8a8301d5314204b | 2016-08-03 09:33:35 | +-----+-------------+---------------------+----------------------------------+---------------------+ 1 row in set (1.23 sec)?
正確使用索引
數(shù)據(jù)庫表中添加索引后確實會讓查詢速度起飛，但前提必須是正確的使用索引來查詢，如果以錯誤的方式使用，則即使建立索引也會不奏效。
即使建立索引，索引也不會生效的情況：
- like '%xx'? ?select?*?from?tb1?where?name?like?'%cn';
- 使用函數(shù)
? ?select?*?from?tb1?where?reverse(name) =?'zhangqiye';
- or
? ?select?*?from?tb1?where?nid =?1?or?email =?'seven@live.com';
? ?特別的：當or條件中有未建立索引的列才失效，以下會走索引
? ? ? ? ? ?select?*?from?tb1?where?nid =?1?or?name?=?'seven';
? ? ? ? ? ?select?*?from?tb1?where?nid =?1?or?email =?'seven@live.com'?and?name?=?'zhangqiye'
- 類型不一致
? ?如果列是字符串類型，傳入條件是必須用引號引起來，不然...
? ?select?*?from?tb1?where?name?=?999;
- !=
? ?select?*?from?tb1?where?name?!=?'zhangqiye'
? ?特別的：如果是主鍵，則還是會走索引
? ? ? ?select?*?from?tb1?where?nid !=?123
- >
? ?select?*?from?tb1?where?name?>?'zhangqiye'
? ?特別的：如果是主鍵或索引是整數(shù)類型，則還是會走索引
? ? ? ?select?*?from?tb1?where?nid >?123
? ? ? ?select?*?from?tb1?where?num?>?123
-?order?by
? ?select?email?from?tb1?order?by?name?desc;
? ?當根據(jù)索引排序時候，選擇的映射如果不是索引，則不走索引
? ?特別的：如果對主鍵排序，則還是走索引：
? ? ? ?select?*?from?tb1?order?by?nid?desc;
- 組合索引最左前綴
? ?如果組合索引為：(name,email)
? ?name and email ? ? ??-- 使用索引
? ?name ? ? ? ? ? ? ? ??-- 使用索引
? ?email ? ? ? ? ? ? ? ?-- 不使用索引
?
其他注意事項
-?避免使用select *-?count(1)或count(列) 代替 count(*)
-?創(chuàng)建表時盡量時 char 代替 varchar
-?表的字段順序固定長度的字段優(yōu)先
-?組合索引代替多個單列索引（經(jīng)常使用多個條件查詢時）
-?盡量使用短索引
-?使用連接（JOIN）來代替子查詢(Sub-Queries)
-?連表時注意條件類型需一致
-?索引散列值（重復(fù)少）不適合建索引，例：性別不適合
?
limit分頁
無論是否有索引，limit分頁是一個值得關(guān)注的問題
mysql大數(shù)據(jù)量使用limit分頁，隨著頁碼的增大，查詢效率越低下。
?
直接用limit?start,?count分頁語句
select?*?from?product?limit?start,?count
當起始頁較小時，查詢沒有性能問題，我們分別看下從10，?100，?1000，?10000開始分頁的執(zhí)行時間（每頁取20條），?如下：
select?*?from?product?limit?100,?20???0.016秒
select?*?from?product?limit?1000,?20???0.047秒
select?*?from?product?limit?10000,?20???0.094秒
我們已經(jīng)看出隨著起始記錄的增加，時間也隨著增大，?這說明分頁語句limit跟起始頁碼是有很大關(guān)系的，那么我們把起始記錄改為40w看下（也就是記錄的一般左右） ? ? ?select?*?from?product?limit?400000,?20?? 3.229秒
再看我們?nèi)∽詈笠豁撚涗浀臅r間
select?*?from?product?limit?866613,?20?? 37.44秒
難怪搜索引擎抓取我們頁面的時候經(jīng)常會報超時，像這種分頁最大的頁碼頁顯然這種時
間是無法忍受的。
從中我們也能總結(jié)出兩件事情：
1）limit語句的查詢時間與起始記錄的位置成正比
2）mysql的limit語句是很方便，但是對記錄很多的表并不適合直接使用。
?
對limit分頁問題的性能優(yōu)化方法
利用表的覆蓋索引來加速分頁查詢
我們都知道，利用了索引查詢的語句中如果只包含了那個索引列（覆蓋索引），那么這種情況會查詢很快。
因為利用索引查找有優(yōu)化算法，且數(shù)據(jù)就在查詢索引上面，不用再去找相關(guān)的數(shù)據(jù)地址了，這樣節(jié)省了很多時間。另外Mysql中也有相關(guān)的索引緩存，在并發(fā)高的時候利用緩存就效果更好了。
在我們的例子中，我們知道id字段是主鍵，自然就包含了默認的主鍵索引。現(xiàn)在讓我們看看利用覆蓋索引的查詢效果如何：
這次我們之間查詢最后一頁的數(shù)據(jù)（利用覆蓋索引，只包含id列），如下：
select?id?from?product?limit?866613,?20 0.2秒
相對于查詢了所有列的37.44秒，提升了大概100多倍的速度
那么如果我們也要查詢所有列，有兩種方法，一種是id>=的形式，另一種就是利用join，看下實際情況：
SELECT?*?FROM?product?WHERE?ID?>?=(select?id?from?product?limit?866613,?1)?limit?20
查詢時間為0.2秒，簡直是一個質(zhì)的飛躍啊，哈哈
另一種寫法SELECT?*?FROM?product?a?JOIN?(select?id?from?product?limit?866613,?20)?b?ON?a.ID?=?b.id查詢時間也很短，贊！
其實兩者用的都是一個原理嘛，所以效果也差不多
執(zhí)行計劃
explain + 查詢SQL - 用于顯示SQL執(zhí)行信息參數(shù)，根據(jù)參考信息可以進行SQL優(yōu)化
如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as B; +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | NULL | | 2 | DERIVED | tb1 | range | PRIMARY | PRIMARY | 8 | NULL | 9 | Using where | +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+?
結(jié)果字段說明
id：查詢順序標識
select_type
查詢類型
SIMPLE ? ? ? ? ? ? ? ? 簡單查詢
PRIMARY ? ? ? ? ? ? ?最外層查詢
SUBQUERY ? ? ? ? ?映射為子查詢
DERIVED ? ? ? ? ? ? ?子查詢
UNION ? ? ? ? ? ? ? ? ? 聯(lián)合
UNION RESULT ? ?使用聯(lián)合的結(jié)果
?
table：正在訪問的表名
type
查詢時的訪問方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
ALL （必須進行優(yōu)化） ? ? ? ? ? ?
全表掃描，對于數(shù)據(jù)表從頭到尾找一遍
select * from tb1;
特別的：如果有l(wèi)imit限制，則找到之后就不在繼續(xù)向下掃描
select * from tb1 where email = 'seven@live.com'
select * from tb1 where email = 'seven@live.com' limit 1;
雖然上述兩個語句都會進行全表掃描，第二句使用了limit，則找到一個后就不再繼續(xù)掃描。
?
INDEX ? ? ? ? ??
全索引掃描，對索引從頭到尾找一遍
select nid from tb1;
?
RANGE ? ? ? ? ?
對索引列進行范圍查找
select * ?from tb1 where name < 'zhagsan';
between and
in
> ? >= ?< ? <= ?操作
注意：!= 和 > 符號
?
INDEX_MERGE ? ??
合并索引，使用多個單列索引搜索
select * ?from tb1 where name = zhangsan' or nid in (11,22,33);
?
REF ? ? ? ? ? ?
根據(jù)索引查找一個或多個值
select * ?from tb1 where name = 'seven';
?
EQ_REF ? ? ? ? ?
連接時使用primary key 或 unique類型
select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
?
CONST ? ? ? ? ??
常量
表最多有一個匹配行,因為僅有一行,在這行的列值可被優(yōu)化器剩余部分認為是常數(shù),const表很快,因為它們只讀取一次。
select nid from tb1 where nid = 2 ;
?
SYSTEM ? ? ? ? ?
系統(tǒng)
表僅有一行(=系統(tǒng)表)。這是const聯(lián)接類型的一個特例。
select * from (select nid from tb1 where nid = 1) as A;
?
possible_keys：可能使用的索引
key?：真實使用的
key_len：MySQL中使用索引字節(jié)長度
rows
mysql估計為了找到所需的行而要讀取的行數(shù) ------ 只是預(yù)估值
?
慢日志查詢
MySQL的慢查詢?nèi)罩臼荕ySQL提供的一種日志記錄，它用來記錄在MySQL中響應(yīng)時間超過閥值的語句，具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢?nèi)罩局小ong_query_time的默認值為10，意思是運行10S以上的語句。默認情況下，Mysql數(shù)據(jù)庫并不啟動慢查詢?nèi)罩?#xff0c;需要我們手動來設(shè)置這個參數(shù)，當然，如果不是調(diào)優(yōu)需要的話，一般不建議啟動該參數(shù)，因為開啟慢查詢?nèi)罩緯蚨嗷蛏賻硪欢ǖ男阅苡绊憽Ｂ樵內(nèi)罩局С謱⑷罩居涗泴懭胛募?#xff0c;也支持將日志記錄寫入數(shù)據(jù)庫表。
?
MySQL 慢查詢的相關(guān)參數(shù)解釋
slow_query_log ? ?：是否開啟慢查詢?nèi)罩?#xff0c;1表示開啟，0表示關(guān)閉。log-slow-queries ?：舊版（5.6以下版本）MySQL數(shù)據(jù)庫慢查詢?nèi)罩敬鎯β窂健？梢圆辉O(shè)置該參數(shù)，系統(tǒng)則會默認給一個缺省的文件host_name-slow.log
slow-query-log-file：新版（5.6及以上版本）MySQL數(shù)據(jù)庫慢查詢?nèi)罩敬鎯β窂健？梢圆辉O(shè)置該參數(shù)，系統(tǒng)則會默認給一個缺省的文件host_name-slow.log
long_query_time ：慢查詢閾值，當查詢時間多于設(shè)定的閾值時，記錄日志。
log_queries_not_using_indexes：未使用索引的查詢也被記錄到慢查詢?nèi)罩局?#xff08;可選項）。
log_output：日志存儲方式。log_output='FILE'表示將日志存入文件，默認值是'FILE'。log_output='TABLE'表示將日志存入數(shù)據(jù)庫，
這樣日志信息就會被寫入到mysql.slow_log表中。MySQL數(shù)據(jù)<br>庫支持同時兩種日志存儲方式，配置的時候以逗號隔開即可，如：log_output='FILE,TABLE'。
日志記錄到系統(tǒng)的專用日志表中，要比記錄到文件耗費更多的系統(tǒng)資源，因此對于需要啟用慢查詢?nèi)罩?#xff0c;又需<br>要能夠獲得更高的系統(tǒng)性能，那么建議優(yōu)先記錄到文件。
?
開啟慢查詢
找到 MySQL 的配置文件 ，my.cnf （Windows?為 my.ini ），在 [mysqld]下增加下面幾行：
slow_query_log?=?1?? ? ? ? ? ? ? ? ? ? ? ? ?long_query_time?=?2?? ? ? ? ? ? ? ? ? ? ? ? ??
slow_query_log_file?= /usr/slow.log ? ? ??
log_queries_not_using_indexes?=?1
重新啟動MySQL服務(wù)
?
MySQL 配置文件的位置
Windows：Windows 的配置文件為 my.ini，一般在 MySQL 的安裝目錄下或者 c:\Windows 下。
Linux：Linux 的配置文件為 my.cnf ，一般在 /etc 下。
?
?
識別圖中二維碼,領(lǐng)取python全套視頻
轉(zhuǎn)載于:https://www.cnblogs.com/IT-Scavenger/p/9192506.html
總結(jié)
以上是生活随笔為你收集整理的正确使用索引（sql优化），limit分页优化，执行计划，慢日志查询的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： [UE4]函数分组
- 下一篇： Django 生成数据库表时的报错Typ