MySQL 性能優(yōu)化

[TOC]

MySQL 工作原理

MySQL 體系結(jié)構(gòu)

1549913724163

管理工具

MySQL服務(wù)軟件安裝后提供的命令

連接池

檢查本機(jī)是否有資源處理當(dāng)前的連接請(qǐng)求 ( 空閑的線程 內(nèi)存 )

SQL 接口

把 SQL 命令傳遞給 mysql 服務(wù)的進(jìn)程處理.

分析器

檢查執(zhí)行的SQL命令是否有語(yǔ)法錯(cuò)誤,

優(yōu)化器

優(yōu)化執(zhí)行的sql命令,使其能以最節(jié)省系統(tǒng)資源的方式執(zhí)行

查詢緩存

查詢緩存的存儲(chǔ)空間是從系統(tǒng)的物理內(nèi)存里劃分出來(lái)的,用來(lái)存儲(chǔ)查詢過的查詢結(jié)果

存儲(chǔ)引擎

軟件自帶的功能程序,是用來(lái)處理表的處理器

文件系統(tǒng)

數(shù)據(jù)庫(kù)服務(wù)器存儲(chǔ)數(shù)據(jù)的磁盤

MySQL 執(zhí)行流程

1549913788017

MySQL性能調(diào)優(yōu)思路

提高 MySQL 系統(tǒng)的性能,響應(yīng)速度

1. 替換有問題的硬件 ( CPU/磁盤/內(nèi)存 等 )

2. 服務(wù)程序的運(yùn)行參數(shù)調(diào)整

3. 對(duì) SQL 查詢進(jìn)行優(yōu)化

運(yùn)行參數(shù)優(yōu)化

并發(fā)及連接控制

連接數(shù),連接超時(shí)

1549910455606

#查看有過的最大連接數(shù) 
mysql> show global status like "max_used_connections";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 2     |
+----------------------+-------+

#查看默認(rèn)的最大連接數(shù)
mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

命令行修改

set [ global ] 變量名=值

mysql> set global max_connections=501;
mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 501   |
+-----------------+-------+

永久修改

[mysqld]
max_connections=值

有過的最大連接數(shù)量/并發(fā)連接數(shù)=0.85

Max_used_connections / Max_used_connections=0.85

留百分之十五的空閑

緩存參數(shù)控制

緩沖區(qū),線程數(shù)量,開表數(shù)量

索引緩存

key_buffer_size = 8M

• 當(dāng) Key_reds / Key_read_requests 較低時(shí),可適當(dāng)加大此緩存值

mysql> show global status like "key_read%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 6     |
| Key_reads         | 3     |
+-------------------+-------+

mysql> show variables like "key_buffer_size";
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+

排序緩存

sort_buffer_size= 256K

• 增大此值可提高 ORDER 和 GROUP的速度

mysql> show variables like "sort_buffer_size";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+

表記錄讀取緩存

read_buffer_size

• 此表緩存值影響 SQL 查詢的響應(yīng)速度

mysql> show variables like "read_buffer_size";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+

查看可重用線程數(shù)

thread_cache_size = 9

mysql> show variables like "thread_cache_size";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 9     |
+-------------------+-------+]

查看當(dāng)前的線程重用狀態(tài)

mysql> show global status like "threads_%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 2     |
| Threads_created   | 2     |
| Threads_running   | 2     |
+-------------------+-------+

查看已打開,打開過多少個(gè)表

mysql> show global status like "open%tables";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 104   |
| Opened_tables | 111   |
+---------------+-------+

查看可緩存多少個(gè)打開的表

mysql> show variables like "table_open_cache";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 2000  |
+------------------+-------+

1549911531311

顯示查詢緩存的設(shè)置

一般生產(chǎn)環(huán)境不開查詢緩存,用專門的緩存中間件做查詢緩存,例如 memcache redis mogodb

query_cache_type = 0 | 1 | 2

mysql> show variables like "%query_cache%";
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

query_cache_wlock_invalidate off

當(dāng)對(duì)myisam存儲(chǔ)引擎的表,查詢的時(shí)候,若此時(shí)有客戶端對(duì)表執(zhí)行寫操作,Mysql服務(wù)不會(huì)從緩存里查找數(shù)據(jù)返回給客戶端,而是等寫操作完成后,重新從表里查找數(shù)據(jù)返回給客戶端.

顯示查詢緩存數(shù)據(jù)信息

mysql> show global status like "qcache%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 6       |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+

SQL 查詢優(yōu)化

程序員編寫的SQL 復(fù)雜導(dǎo)致處理速度慢.

MySQL 日志類型

優(yōu)化 SQL 查詢

• 記錄慢查詢

慢查詢?nèi)罩?只記錄超過超時(shí)時(shí)間顯示查詢結(jié)果的sql命令

#開啟慢查詢?nèi)罩?[mysqld]
slow-query-log
long-query-time=1
log_queries_not_using_indexes

#重啟后 睡眠10秒
mysql> select sleep(10);

#用工具統(tǒng)計(jì)滿日志信息
mysqldumpslow test1-slow.log  #mysqldumpslow  統(tǒng)計(jì)慢日志信息

數(shù)據(jù)架構(gòu)或者存儲(chǔ)問題導(dǎo)致的數(shù)據(jù)庫(kù)響應(yīng)緩慢,考慮調(diào)整架構(gòu)或者更換固態(tài)或分布式存儲(chǔ)解決.

幫助文檔:

mysql配置文件詳解

mysql 幫助手冊(cè)