建站
咨詢
售后
建站咨詢
新聞中心
最近研發(fā)的項目對DB依賴比較重，梳理了這段時間使用MySQL遇到的8個比較具有代表性的問題，答案也比較偏自己的開發(fā)實踐，沒有DBA專業(yè)和深入，有出入的請使勁拍磚!…
成都創(chuàng)新互聯(lián)主要從事成都網(wǎng)站建設(shè)、網(wǎng)站制作、網(wǎng)頁設(shè)計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務。立足成都服務上猶,十余年網(wǎng)站建設(shè)經(jīng)驗,價格優(yōu)惠、服務專業(yè),歡迎來電咨詢建站服務:18980820575
- MySQL讀寫性能是多少，有哪些性能相關(guān)的配置參數(shù)?
- MySQL負載高時，如何找到是由哪些SQL引起的?
- 如何針對具體的SQL做優(yōu)化?
- SQL層面已難以優(yōu)化，請求量繼續(xù)增大時的應對策略?
- MySQL如何做主從數(shù)據(jù)同步?
- 如何防止DB誤操作和做好容災?
- 該選擇MySQL哪種存儲引擎，Innodb具有什么特性?
- MySQL內(nèi)部結(jié)構(gòu)有哪些層次?
1.MySQL讀寫性能是多少，有哪些性能相關(guān)的重要參數(shù)?
這里做了幾個簡單壓測實驗
機器：8核CPU，8G內(nèi)存
表結(jié)構(gòu)(盡量模擬業(yè)務)：12個字段(1個bigint(20)為自增primary key，5個int(11)，5個varchar(512)，1個timestamp)，InnoDB存儲引擎。
實驗1(寫)：insert => 6000/s
前提：連接數(shù)100，每次insert單條記錄
分析：CPU跑了50%，這時磁盤為順序?qū)?，故性能較高
實驗2(寫)：update(where條件***索引) => 200/s
前提：連接數(shù)100，10w條記錄，每次update單條記錄的4個字段(2個int(11)，2個varchar(512))
分析：CPU跑2%，瓶頸明顯在IO的隨機寫
實驗3(讀)：select(where條件***索引) => 5000/s
前提：連接數(shù)100，10w條記錄，每次select單條記錄的4個字段(2個int(11)，2個varchar(512))
分析：CPU跑6%，瓶頸在IO，和db的cache大小相關(guān)
實驗4(讀)：select(where條件沒***索引) => 60/s
前提：連接數(shù)100，10w條記錄，每次select單條記錄的4個字段(2個int(11)，2個varchar(512))
分析：CPU跑到80%，每次select都需遍歷所有記錄，看來索引的效果非常明顯!
幾個重要的配置參數(shù)，可根據(jù)實際的機器和業(yè)務特點調(diào)整
max_connecttions：***連接數(shù)
table_cache：緩存打開表的數(shù)量
key_buffer_size：索引緩存大小
query_cache_size：查詢緩存大小
sort_buffer_size：排序緩存大小(會將排序完的數(shù)據(jù)緩存起來)
read_buffer_size：順序讀緩存大小
read_rnd_buffer_size：某種特定順序讀緩存大小(如order by子句的查詢)
PS：查看配置方法：show variables like '%max_connecttions%';
2.MySQL負載高時，如何找到是由哪些SQL引起的?
方法：慢查詢?nèi)罩痉治?MySQLdumpslow)
慢查詢?nèi)罩纠?，可看到每個慢查詢SQL的耗時：
- # User@Host: edu_online[edu_online] @ [10.139.10.167]
- # Query_time: 1.958000 Lock_time: 0.000021 Rows_sent: 254786 Rows_examined: 254786
- SET timestamp=1410883292;
- select * from t_online_group_records;
日志顯示該查詢用了1.958秒，返回254786行記錄，一共遍歷了254786行記錄。及具體的時間戳和SQL語句。
使用MySQLdumpslow進行慢查詢?nèi)罩痉治?/p>
MySQLdumpslow -s t -t 5 slow_log_20140819.txt
輸出查詢耗時最多的Top5條SQL語句
-s：排序方法，t表示按時間 (此外，c為按次數(shù)，r為按返回記錄數(shù)等)
-t：去Top多少條，-t 5表示取前5條
執(zhí)行完分析結(jié)果如下：
- Count: 1076100 Time=0.09s (99065s) Lock=0.00s (76s) Rows=408.9 (440058825), edu_online[edu_online]@28hosts
- select * from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > N
- Count: 1076099 Time=0.05s (52340s) Lock=0.00s (91s) Rows=62.6 (67324907), edu_online[edu_online]@28hosts
- select * from t_online_course where UNIX_TIMESTAMP(c_updatetime) > N
- Count: 63889 Time=0.78s (49607s) Lock=0.00s (3s) Rows=0.0 (18), edu_online[edu_online]@[10x.213.1xx.1xx]
- select f_uin from t_online_student_contact where f_modify_time > N
- Count: 1076097 Time=0.02s (16903s) Lock=0.00s (72s) Rows=52.2 (56187090), edu_online[edu_online]@28hosts
- select * from t_online_video_info where UNIX_TIMESTAMP(v_update_time) > N
- Count: 330046 Time=0.02s (6822s) Lock=0.00s (45s) Rows=0.0 (2302), edu_online[edu_online]@4hosts
- select uin,cid,is_canceled,unix_timestamp(end_time) as endtime,unix_timestamp(update_time) as updatetime
- from t_kick_log where unix_timestamp(update_time) > N
以第1條為例，表示這類SQL(N可以取很多值，這里MySQLdumpslow會歸并起來)在8月19號的慢查詢?nèi)罩緝?nèi)出現(xiàn)了1076100次，總耗時99065秒，總返回440058825行記錄，有28個客戶端IP用到。
通過慢查詢?nèi)罩痉治?，就可以找到最耗時的SQL，然后進行具體的SQL分析了
慢查詢相關(guān)的配置參數(shù)
log_slow_queries：是否打開慢查詢?nèi)罩?，得先確保=ON后面才有得分析
long_query_time：查詢時間大于多少秒的SQL被當做是慢查詢，一般設(shè)為1S
log_queries_not_using_indexes：是否將沒有使用索引的記錄寫入慢查詢?nèi)罩?/p>
slow_query_log_file：慢查詢?nèi)罩敬娣怕窂?/p>
3.如何針對具體的SQL做優(yōu)化?
使用Explain分析SQL語句執(zhí)行計劃
- MySQL> explain select * from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > 123456789;
- +----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+
- | 1 | SIMPLE | t_online_group_records | ALL | NULL | NULL | NULL | NULL | 47 | Using where |
- +----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+
- 1 row in set (0.00 sec)
如上面例子所示，重點關(guān)注下type，rows和Extra：
type：使用類別，有無使用到索引。結(jié)果值從好到壞：… > range(使用到索引) > index > ALL(全表掃描)，一般查詢應達到range級別
rows：SQL執(zhí)行檢查的記錄數(shù)
Extra：SQL執(zhí)行的附加信息，如”Using index”表示查詢只用到索引列，不需要去讀表等
使用Profiles分析SQL語句執(zhí)行時間和消耗資源
- MySQL> set profiling=1; (啟動profiles，默認是沒開啟的)
- MySQL> select count(1) from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > 123456789; (執(zhí)行要分析的SQL語句)
- MySQL> show profiles;
- +----------+------------+----------------------------------------------------------------------------------------------+
- | Query_ID | Duration | Query |
- +----------+------------+----------------------------------------------------------------------------------------------+
- | 1 | 0.00043250 | select count(1) from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > 123456789 |
- +----------+------------+----------------------------------------------------------------------------------------------+
- 1 row in set (0.00 sec)
- MySQL> show profile cpu,block io for query 1; (可看出SQL在各個環(huán)節(jié)的耗時和資源消耗)
- +----------------------+----------+----------+------------+--------------+---------------+
- | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
- +----------------------+----------+----------+------------+--------------+---------------+
- ...
- | optimizing | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
- | statistics | 0.000020 | 0.000000 | 0.000000 | 0 | 0 |
- | preparing | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
- | executing | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
- | Sending data | 0.000076 | 0.000000 | 0.000000 | 0 | 0 |
- ...
SQL優(yōu)化的技巧 (只提一些業(yè)務常遇到的問題)
最關(guān)鍵：索引，避免全表掃描。
對接觸的項目進行慢查詢分析，發(fā)現(xiàn)***0的基本都是忘了加索引或者索引使用不當，如索引字段上加函數(shù)導致索引失效等(如where UNIX_TIMESTAMP(gre_updatetime)>123456789)
- +----------+------------+---------------------------------------+
- | Query_ID | Duration | Query |
- +----------+------------+---------------------------------------+
- | 1 | 0.00024700 | select * from mytable where id=100 |
- | 2 | 0.27912900 | select * from mytable where id+1=101 |
- +----------+------------+---------------------------------------+
另外很多同學在拉取全表數(shù)據(jù)時，喜歡用select xx from xx limit 5000,1000這種形式批量拉取，其實這個SQL每次都是全表掃描，建議添加1個自增id做索引，將SQL改為select xx from xx where id>5000 and id;
- +----------+------------+-----------------------------------------------------+
- | Query_ID | Duration | Query |
- +----------+------------+-----------------------------------------------------+
- | 1 | 0.00415400 | select * from mytable where id>=90000 and id91000 |
- | 2 | 0.10078100 | select * from mytable limit 90000,1000 |
- +----------+------------+-----------------------------------------------------+
合理用好索引，應該可解決大部分SQL問題。當然索引也非越多越好，過多的索引會影響寫操作性能
只select出需要的字段，避免select
- +----------+------------+-----------------------------------------------------+
- | Query_ID | Duration | Query |
- +----------+------------+-----------------------------------------------------+
- | 1 | 0.02948800 | select count(1) from ( select id from mytable ) a |
- | 2 | 1.34369100 | select count(1) from ( select * from mytable ) a |
- +----------+------------+-----------------------------------------------------+
盡量早做過濾，使Join或者Union等后續(xù)操作的數(shù)據(jù)量盡量小
把能在邏輯層算的提到邏輯層來處理，如一些數(shù)據(jù)排序、時間函數(shù)計算等
…….
PS：關(guān)于SQL優(yōu)化，已經(jīng)有足夠多文章了，所以就不講太全面了，只重點說自己1個感受：索引!基本都是因為索引!
4.SQL層面已難以優(yōu)化，請求量繼續(xù)增大時的應對策略?
下面是我能想到的幾個方法，每個方法又都是一篇大文章了，這里就不展開
分庫分表
使用集群(master-slave)，讀寫分離
增加業(yè)務的cache層
使用連接池
5.MySQL如何做主從數(shù)據(jù)同步?
復制機制(Replication)
master通過復制機制，將master的寫操作通過binlog傳到slave生成中繼日志(relaylog)，slave再將中繼日志redo，使得主庫和從庫的數(shù)據(jù)保持同步
復制相關(guān)的3個MySQL線程
- slave上的I/O線程：向master請求數(shù)據(jù)
- master上的Binlog Dump線程：讀取binlog事件并把數(shù)據(jù)發(fā)送給slave的I/O線程
- slave上的SQL線程：讀取中繼日志并執(zhí)行，更新數(shù)據(jù)庫
屬于slave主動請求拉取的模式
實際使用可能遇到的問題
數(shù)據(jù)非強一致：CDB默認為異步復制，master和slave的數(shù)據(jù)會有一定延遲(稱為主從同步距離，一般 主從同步距離變大：可能是DB寫入壓力大，也可能是slave機器負載高，網(wǎng)絡(luò)波動等原因，具體問題具體分析
相關(guān)監(jiān)控命令
show processlist：查看MySQL進程信息，包括3個同步線程的當前狀態(tài)
show master status ：查看master配置及當前復制信息
show slave status：查看slave配置及當前復制信息
6.如何防止DB誤操作和做好容災?
業(yè)務側(cè)應做到的幾點：
重要DB數(shù)據(jù)的手工修改操作，操作前需做到2點：1 先在測試環(huán)境操作 2 備份數(shù)據(jù)
根據(jù)業(yè)務重要性做定時備份，考慮系統(tǒng)可承受的恢復時間
進行容災演練，感覺很必要
MySQL備份和恢復操作
1.備份：使用MySQLdump導出數(shù)據(jù)
MySQLdump -u 用戶名 -p 數(shù)據(jù)庫名 [表名] > 導出的文件名
MySQLdump -uxxx -p xxx mytable > mytable.20140921.bak.sql
2.恢復：導入備份數(shù)據(jù)
MySQL -uxxx -p xxxx
3.恢復：導入備份數(shù)據(jù)之后發(fā)送的寫操作。先使用MySQLbinlog導出這部分寫操作SQL(基于時間點或位置)
如導出2014-09-21 09:59:59之后的binlog：
- MySQLbinlog --database="test" --start-date="2014-09-21 09:59:59" /var/lib/MySQL/mybinlog.000001 > binlog.data.sql
如導出起始id為123456之后的binlog：
- MySQLbinlog --database="test" --start-position="123456" /var/lib/MySQL/mybinlog.000001 > binlog.data.sql
***把要恢復的binlog導入db
- MySQL -uxxxx -p xxxx
7.該選擇MySQL哪種存儲引擎，Innodb具有什么特性?
存儲引擎簡介
插件式存儲引擎是MySQL的重要特性，MySQL支持多種存儲引擎以滿足用戶的多種應用場景
存儲引擎解決的問題：如何組織MySQL數(shù)據(jù)在介質(zhì)中高效地讀取，需考慮存儲機制、索引設(shè)計、并發(fā)讀寫的鎖機制等
MySQL5.0支持的存儲引擎有MyISAM、InnoDB、Memory、Merge等
**MyISAM和InnoDB的區(qū)別(只說重點了)
1.InnoDB
MySQL5.5之后及CDB的默認引擎。
- 支持行鎖：并發(fā)性能好
- 支持事務：故InnoDB稱為事務性存儲引擎，支持ACID，提供了具有提交、回滾和崩潰恢復能力的事務安全
- 支持外鍵：當前唯一支持外鍵的引擎
2.MyISAM
MySQL5.5之前默認引擎
- 支持表鎖：插入+查詢速度快，更新+刪除速度慢
- 不支持事務
使用show engines可查看當前MySQL支持的存儲引擎詳情
8.MySQL內(nèi)部結(jié)構(gòu)有哪些層次?
非專業(yè)DBA，這里只簡單貼個結(jié)構(gòu)圖說明下。MySQL是開源系統(tǒng)，其設(shè)計思路和源代碼都出自大牛之手，有空可以學習下。
- Connectors：連接器。接收不同語言的Client交互
- Management Serveices & Utilities：系統(tǒng)管理和控制工具
- Connection Pool: 連接池。管理用戶連接
- SQL Interface: SQL接口。接受用戶的SQL命令，并且返回用戶需要查詢的結(jié)果
- Parser: 解析器。驗證和解析SQL語句成內(nèi)部數(shù)據(jù)結(jié)構(gòu)
- Optimizer: 查詢優(yōu)化器。為查詢語句選擇合適的執(zhí)行路徑
- Cache和Buffer：查詢緩存。緩存查詢的結(jié)果，有***即可直接返回
- Engine：存儲引擎。MySQL數(shù)據(jù)***組織并存儲成具體文件
網(wǎng)站欄目：MySQL開發(fā)實踐8問，你能hold住幾個？
本文來源：http://m.5511xx.com/article/djiesih.html
