1.數據庫優化的可以從如下幾個方面著手:
1.>sql語句和索引.
2.>數據庫表結構.
3.>系統配置.
4.>硬件
2.使用MySQL的慢查詢日誌對效率有問題的sql進行監控
1.>查看慢查詢日誌是否開啟:show variables like 'slow_query_log'
2.>根據查看出來的日誌存放路徑,開啟慢查詢日誌set global slow_query_log_file='/var/lib/mysql/mylinux-slow.log' (file後面的地址可以通過show variables like '%log%';獲取)
3.>開啟沒有用索引的查詢記錄在日誌中:set global log_queries_not_using_indexes=on;
4.>設置最長的查詢時間,如果超過該時間,則被記錄到日誌中.set global long_query_time=1;
3.SQL及索引優化.
3.1MySQL自帶的慢查詢日誌分析工具:
mysqldumpslow -h 可以查看相關參數.
mysqldumpslow -t 3 /var/lib/mysql/mylinux-slow.log | more 查看前3條性能比較差的sql.
3.2專業的慢查詢分析工具:pt-query-digest.
3.3使用該工具進行查看慢查詢日誌:pt-query-digest /var/lib/mysql/mylinux-slow.log | more 查看詳情.
4.通過慢查詢日誌發現有問題的SQL
5.SQL優化
5.1 max和count查詢的優化
explain select max(box_lunch_time) from cart_personal /G (豎著顯示) 如果查詢數據較多,比較慢的話,可以為box_lunch_time做索引,eg:
create index idxboxlunchtime on cart_personal(box_lunch_time);
count函數中值得注意的: count*會把空值也都計算進去,而count id則不會
5.2子查詢優化
當採用子查詢效率上不如連接查詢時,將子查詢改為連接查詢,但有一點需要值得注意,就是連接查詢不會屏蔽一對多時出現數據重複的情況,此時如果業務需要,可以採用distinct關鍵字來處理.
eg: select * from t where t.id in (select t1.tid from t1);
可以用: select t.id from t join t1 on t.tid =t1.tid;代替,當t1中的tid有兩條相同的數據時,那麼第一條查詢語句查詢出來的結果包只含1條數據,而第二條查詢語句能查出2條數據,此時可以改為:
select distinct t.id from t join t1 on t.tid =t1.tid; 這樣查詢結果就與第一條sql查詢結果一致了!
5.3group by 優化
優化前:
優化後:
5.4 limit查詢優化
優化前:
優化步驟一:
在order by操作中,儘量使用主鍵或者索引列來進行order by,這樣效率高.
優化步驟二:
如果數據量比較大時,Limit50000就要掃描500000次,IO比較大,因此需要進一步優化.值得注意的是,在這種情況下,id必須是連續的,順序增長的,如果Id不連續,需要添加一列,idindex,保證它是自增且連續的,為它添加索引,即可.
6.索引優化
6.1如何選擇合適的列建立索引?
1.在where從句,group by從句,order by從句 on從句中出現的列建立索引
2.索引字段越小越好
3.離散度大的列放到聯合索引的前面.(如何判斷離散度? 可以使用count語句,比如兩個字段staff_id和customer_id,可以使用select count distinct(staff_id), count distinct(customer_id) from payment; 查詢結果中對應的數字越大,說明離散度越大)
6.2索引優化sql的方法
1.重複索引
2.冗餘索引
3.如何查詢出重複索引和冗餘索引?
SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.COLUMN_NAME,
a.INDEX_NAME AS 'index1', b.INDEX_NAME AS 'index2'
FROM information_schema.STATISTICS a
JOIN information_schema.STATISTICS b
ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
AND a.COLUMN_NAME = b.COLUMN_NAME
WHERE a.SEQ_IN_INDEX = 1 AND a.INDEX_NAME <> b.INDEX_NAME
4.查找重複/冗餘索引的工具:pt-duplicate-key-checker 工具檢查重複及冗餘索引
下載地址：http://www.percona.com/redir/downloads/percona-toolkit/percona-toolkit-1.0.1.tar.gz
安裝方法：
先安裝依賴:yum install -y perl perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl-Digest-MD5 perl-ExtUtils-MakeMaker
然後進入解壓後的目錄 執行:
perl Makefile.PL；make；make install
使用:pt-duplicate-key-checker --host=localhost --user=root --password=123456 --database=ordering 進行查詢,根據查詢出的結果和工具給出的建議進一步優化索引.
4.索引維護
pt-index-usage --host=localhost --user=root --password=123456 /var/lib/mysql/mylinux-slow.log
刪除那些不經常使用的索引.
鏈接：https://www.jianshu.com/p/4623692d6567
來源：簡書
閱讀更多 思夢PHP 的文章
