天天看點
傳回

MySQL全面優化思路-基礎内容

MySQL性能優化-優化思路

大概的優化思路分為以下幾個内容

PS: 優化是有風險的,如果你要優化就要變更。

  • 硬體層面優化
  • 系統層面優化
  • MySQL版本選擇優化
  • MySQL三層結構及參數優化
  • MySQL開發規範
  • MySQL的索引優化
  • MySQL的事務以及鎖優化
  • MySQL架構優化
  • MySQL安全優化

硬體層面優化

這個地方就略過了就是一些加大硬體配置的需求.

系統層面優化

  • id: 空閑狀态,如果數值越大,表示空閑狀态越多。如果可能達到0的情況下,表示目前CPU的核心處于滿負荷狀态。
  • us: 表示目前CPU核心數量的使用率。
  • sy: 表示CPU與核心互動的頻率,核心與CPU處理請求的占用,如果此參數高,表示核心很忙。
  • wa: CPU從記憶體中刷資料到硬碟中的占用,可能會出現I/O的問題。 
[root@mysql-master ~]# top
top - 15:05:11 up 35 days,  5:54,  2 users,  load average: 0.00, 0.01, 0.05
Tasks: 225 total,   2 running, 223 sleeping,   0 stopped,   0 zombie
%Cpu0  :  0.0 us,  0.0 sy,  0.0 ni,100.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu1  :  0.0 us,  0.0 sy,  0.0 ni,100.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu2  :  0.0 us,  0.0 sy,  0.0 ni,100.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu3  :  0.0 us,  0.0 sy,  0.0 ni,100.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu4  :  0.0 us,  0.0 sy,  0.0 ni,100.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu5  :  0.0 us,  0.0 sy,  0.0 ni,100.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu6  :  0.0 us,  0.0 sy,  0.0 ni,100.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
%Cpu7  :  0.0 us,  0.0 sy,  0.0 ni,100.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
KiB Mem : 24522416 total, 14931524 free,  3675344 used,  5915548 buff/cache
KiB Swap: 12386300 total, 12386300 free,        0 used. 20450988 avail Mem

複制

通過

top -Hp 10380

指定占用高的程序,可以看到具體是那些線程占用過高

假設

1893

線程占用過高,可以從資料庫中檢視

performance_schema

庫中具體的資訊

定位作業系統線程->從系統線程中定位資料庫線程 
*************************** 38. row ***************************
          THREAD_ID: 128014
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 127988
   PROCESSLIST_USER: ooooo
   PROCESSLIST_HOST: 192.168.0.1
     PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Sleep
   PROCESSLIST_TIME: 104
  PROCESSLIST_STATE: NULL
   PROCESSLIST_INFO: **
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: SSL/TLS
       THREAD_OS_ID: 16165
*************************** 39. row ***************************

複制

如果可能存在的是IO問題

  • 查詢

    MySQL

     中的

    sys

     庫中存在記錄

    IO

     的表
如果存在IO問題： 可以選擇用記憶體換取時間的方法.. 
mysql> use sys
mysql> show tables;
| x$io_by_thread_by_latency                     |
| x$io_global_by_file_by_bytes                  |
| x$io_global_by_file_by_latency                |
| x$io_global_by_wait_by_bytes                  |
| x$io_global_by_wait_by_latency                |

複制

MySQL版本選擇優化

在這裡…筆者非常推薦MySQL8.0x!!! 同樣的機器,8.0比5.7快2.5倍左右吧
  1. 選擇穩定版,選擇開源社群的穩定版和GA版本
  2. 選擇MySQL資料庫GA版本釋出後6-12個月的GA雙數版本
  3. 要選擇開發相容的MySQL版本

MySQL三層結構及參數優化

連接配接層優化

一切根據自己或者項目需要自由設定吧! 
max_connections = 1000
max_connect_errors = 999999
wait_timeout = 600
interactive_wait_timeout = 3600
net_read_timeout = 120
new_write_timeout = 120
max_allowed_packet = 500M

複制

Server層優化

一切根據自己或者項目需要自由設定吧! 
sort_buffer_size = 8M
sql_safe_updates = 1
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql-slow.log
log_queries_not_using_indexes = 10
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 16M
max_binlog_size = 500M
max_execution_time = 28800
log_timestamps = SYSTEM
init_connect = "set names utf8mb4"
binlog_format = ROW
event_scheduler = OFF
lock_wait_timeout =
sync_binlog = 1

複制

Engine層優化

一切根據自己或者項目需要自由設定吧! 
transaction-isolation = "READ-COMMITIED"
innodb_data_home_dir = /xxx
innodb_log_group_home_dir = /xxx
innodb_log_file_size = 2048M
innodb_log_files_in_group = 3
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1000
innodb_io_capacity_max = 4000
innodb_buffer_pool_size = 64G
innodb_buffer_pool_instances = 4
innodb_log_buffer_size = 64M
innodb_max_dirty_pages_pct = 85

複制

全局鎖讀Global Read Lock (GRL）

加鎖方法：FTWRL,flush tables with read lock.

解鎖方法：unlock tables；

可能出現的場景

  • 記錄binlog日志->不讓所有事務送出
  • FTWRL->不讓新的修改進入
  • snapshot innodb-> 允許所有的DML語句,但是不允許DDL

屬于類型: MDL(matedatalock)層面鎖

影響情況: 加鎖的期間,阻塞所有事務的寫入,阻塞所有事務的commit,時間受到

lock_wait_timeout=315336000

全局讀鎖的排查方法 

MySQL [(none)]> USE performance_schema
MySQL [performance_schema]> 
# 5.6需要手動開啟
MySQL [performance_schema]> UPDATE setup_instruments SET ENABLED = "YES",TIMED = "YES" WHERE NAME='wait/lock/metadata/sql/mdl';
#  檢視是否有阻塞問題
MySQL [performance_schema]> SELECT * FROM metadata_locks;
mysql> SELECT OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE,LOCK_DURATION,LOCK_STATUS,OWNER_THREAD_ID,OWNER_EVENT_ID FROM performance_schema.metadata_locks;

複制

5.7版本全局讀鎖排查 

mysql> SHOW proceslist\G;
mysql> SELECT * FORM sys.schema_table_lock_waits;

複制

經典故障案例

  • 假設模拟一個大的查詢或者事物
  • 模拟備份時的TWRL,此時會發現指令阻塞
  • 發起正常查詢請求,發現查詢被阻塞
5.7版本的Xbackup/mysqldump備份資料庫出現鎖表狀态,所有的查詢不能正常進行. 
SELECT *,SLEEP(100) FORM `user` WHERE username = 'test1' for update;
flush tables with read lock;
SELECT * FROM icours.user where username = 'test' for update

複制

Table Lock(表級鎖)

  • 加鎖方式:

    lock table t1 read;

     所有會話隻讀,屬于MDL鎖。

    lock table write;

    目前會話可以可以RW,屬于MDL鎖.

    SELECT FOR UPDATE;

    SELECT FOR SHARE
  • 解鎖方式:

    unlock tables

     ;

檢測方式 

[mysqld]
performance-schema-instrument = 'wait/lock/metadata/sql/mdl=ON'

SELECT * FROM performance_schema.metadata_locks;
SELECT * FROM performance_schema.threads;

複制

MetaDataLock(中繼資料鎖)

  • 作用範圍: global、commit、tablespace、schema、table
  • 預設時間：

    lock_wait_timeout

     
mysql> select @@lock_wait_timeout;
+---------------------+
| @@lock_wait_timeout |
+---------------------+
|            31536000 |
+---------------------+
1 row in set (0.00 sec)

複制

檢測方式 

[mysqld]
performance-schema-instrument = 'wait/lock/metadata/sql/mdl=ON'

SELECT * FROM performance_schema.metadata_locks;

複制

// 找到阻塞的Id
OWNER_THREAD_ID = 12
mysql> SELECT * FROM threads where thread_id = '12'\G;
kill 12;

複制

AutoincLock(自增鎖)

  • 通過參數:

    innodb_autoinc_lock_mod = 0 | 1 | 2
  • 0 表鎖：每次插入都請求表鎖,效率低下
  • 1 mutex： 預計插入多少行,預申請自增序列.如果出現load或者insert select方式會退化為0。
  • 2 : 強制使用mutex的方式,并發插入會更高效！

Innodb Row Lock(行級鎖)

  • record lock、gap、next、lock 
MySQL [(none)]> SHOW STATUS LIKE 'innodb_row_lock';
MySQL [information_schema]> SELECT * FROM information_schema.innodb_trx;
MySQL [information_schema]> SELECT * FORM sys.schema_table_lock_waits;
MySQL [information_schema]> SELECT * FROM  performance_schema.threads;
MySQL [information_schema]> SELECT * FROM performance_schema.events_statements_current;
MySQL [information_schema]>

複制

優化方向

  1. 優化索引
  2. 減少事務的更新範圍
  3. RC級别
  4. 拆分語句 
// 假設 k1是輔助索引
update t1 set num=num+10 where k1<100;
// 改為
select id from t1 where k1<100;
update t1 set num=num+10 where id in (20,30,50)

複制

Dead Lock死鎖

dead lock 多個并發事務之間發生交叉依賴的時候,會出現死鎖. 
SHOW ENGINE innodb STATUS\G;
innodb_print——all_deadlocks =1 // 開啟記錄死鎖日志

複制

上一篇: Python 程式設計 | 連載 21 -序列化與加密子產品
下一篇: JavaScript 運算符基礎