Mysql 效能調優 二 2

Mysql 效能調優 二  2

---

oracle 資料庫遷移到 mysql資料庫
     很複雜的sql語句必須重新編寫成相對簡單的sql
     效能會下降
原因：
nest loopjoin
hash join
sort mergejoin

hash 的效能原因高於巢狀sql的for的排序


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
鎖：

排它鎖：當檢視修改資料時，事務會為所以來的資料資源請求排它鎖，一旦授予，事務將一直持有排它鎖，直至事務完成。
這種鎖模式之所以稱為排它鎖，是因為相對於相同的資料資源，如果有其他事務已經獲得了該資源的任何型別的鎖，就不能再獲得該資源的排它鎖；
如果有其他事務已經獲得該資源的排它鎖，就不能再獲得該資源的任何型別的鎖。

共享鎖：當檢視讀取資料時，事務預設會為所依賴的資料資源請求共享鎖，讀操作一完成，就立即釋放資源上的共享鎖。
這種鎖模式之所以成為共享鎖，是因為多個事務可以同事持有同一資料資源上的共享鎖。

事務之間鎖的相互制約關係：如果資料正在由一個事務進行修改，其他事務就既不能修改該資料，也不能讀取（至少預設不能）該資料，直到第一個資料完成。
吐過資料正在由另一個事務讀取，其他事務就不能修改該資料（至少預設不能）。


1.共享鎖只用於表級，排他鎖用於行級。
2.加了共享鎖的物件，可以繼續加共享鎖，不能再加排他鎖。加了排他鎖後，不能再加任何鎖。
3.比如一個DML操作，就要對受影響的行加排他鎖，這樣就不允許再加別的鎖，也就是說別的會話不能修改這些行。
同時為了避免在做這個DML操作的時候，有別的會話執行DDL，修改表的定義，所以要在表上加共享鎖，這樣就阻止了DDL的操作。
4.當執行DDL操作時，就需要在全表上加排他鎖。

表鎖
行鎖

系統引數：
lock_wait_timeout
max_write_lock_count
innodb_table_locks

狀態引數：
Table_locks_waited


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

[server]
[mysqld]


[client]
[mysql]
[mysqladmin]


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

慢查詢

系統引數：
slow_query_log
slow_query_log_file

query_cache_type
query_cache_limit
query_cache_size
long_query_time


狀態引數：
Last_query_cost


開啟慢查詢：
1.
直接去my.conf中檢視。
my.conf中的配置(放在[mysqld]下的下方加入)

[mysqld]
slow_query_log_file =  /var/lib/mysql/test1-slow.log
long_query_time = 1  #單位是秒
log-queries-not-using-indexes


使用sql語句來修改：不能按照my.conf中的項來修改的。
修改透過
show VARIABLES like "%slow%"

語句列出來的變數，執行如下sql：
set global slow_query_log = ON;
set global long_query_time=0.1; #設定大於0.1s的sql語句記錄下來


2.  慢查詢函式：
mysql> select sleep(12);
+-----------+
| sleep(12) |
+-----------+
|         0 |
+-----------+
1 row in set (12.00 sec)

mysql>

3. 檢視日誌
[root@test1 ~]# cat /var/lib/mysql/test1-slow.log
/usr/sbin/mysqld, Version: 5.6.22-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 141217 13:13:58
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 12.000427  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1418793238;
select sleep(12);
[root@test1 ~]#



php編寫的慢查詢分析指令碼：