自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

MySQL開(kāi)發(fā)實(shí)踐8問(wèn)，你能hold住幾個(gè)？

作者：Serdar Yegulalp
數(shù)據(jù)庫(kù) MySQL
最近研發(fā)的項(xiàng)目對(duì)DB依賴(lài)比較重，梳理了這段時(shí)間使用MySQL遇到的8個(gè)比較具有代表性的問(wèn)題，答案也比較偏自己的開(kāi)發(fā)實(shí)踐，沒(méi)有DBA專(zhuān)業(yè)和深入，有出入的請(qǐng)使勁拍磚!…

最近研發(fā)的項(xiàng)目對(duì)DB依賴(lài)比較重，梳理了這段時(shí)間使用MySQL遇到的8個(gè)比較具有代表性的問(wèn)題，答案也比較偏自己的開(kāi)發(fā)實(shí)踐，沒(méi)有DBA專(zhuān)業(yè)和深入，有出入的請(qǐng)使勁拍磚!…

  1. MySQL讀寫(xiě)性能是多少，有哪些性能相關(guān)的配置參數(shù)?
  2. MySQL負(fù)載高時(shí)，如何找到是由哪些SQL引起的?
  3. 如何針對(duì)具體的SQL做優(yōu)化?
  4. SQL層面已難以?xún)?yōu)化，請(qǐng)求量繼續(xù)增大時(shí)的應(yīng)對(duì)策略?
  5. MySQL如何做主從數(shù)據(jù)同步?
  6. 如何防止DB誤操作和做好容災(zāi)?
  7. 該選擇MySQL哪種存儲(chǔ)引擎，Innodb具有什么特性?
  8. MySQL內(nèi)部結(jié)構(gòu)有哪些層次?

1.MySQL讀寫(xiě)性能是多少，有哪些性能相關(guān)的重要參數(shù)?

這里做了幾個(gè)簡(jiǎn)單壓測(cè)實(shí)驗(yàn)

機(jī)器：8核CPU，8G內(nèi)存

表結(jié)構(gòu)(盡量模擬業(yè)務(wù))：12個(gè)字段(1個(gè)bigint(20)為自增primary key，5個(gè)int(11)，5個(gè)varchar(512)，1個(gè)timestamp)，InnoDB存儲(chǔ)引擎。

實(shí)驗(yàn)1(寫(xiě))：insert => 6000/s

前提：連接數(shù)100，每次insert單條記錄

分析：CPU跑了50%，這時(shí)磁盤(pán)為順序?qū)懀市阅茌^高

實(shí)驗(yàn)2(寫(xiě))：update(where條件***索引) => 200/s

前提：連接數(shù)100，10w條記錄，每次update單條記錄的4個(gè)字段(2個(gè)int(11)，2個(gè)varchar(512))

分析：CPU跑2%，瓶頸明顯在IO的隨機(jī)寫(xiě)

實(shí)驗(yàn)3(讀)：select(where條件***索引) => 5000/s

前提：連接數(shù)100，10w條記錄，每次select單條記錄的4個(gè)字段(2個(gè)int(11)，2個(gè)varchar(512))

分析：CPU跑6%，瓶頸在IO，和db的cache大小相關(guān)

實(shí)驗(yàn)4(讀)：select(where條件沒(méi)***索引) => 60/s

前提：連接數(shù)100，10w條記錄，每次select單條記錄的4個(gè)字段(2個(gè)int(11)，2個(gè)varchar(512))

分析：CPU跑到80%，每次select都需遍歷所有記錄，看來(lái)索引的效果非常明顯!

幾個(gè)重要的配置參數(shù)，可根據(jù)實(shí)際的機(jī)器和業(yè)務(wù)特點(diǎn)調(diào)整

max_connecttions：***連接數(shù)

table_cache：緩存打開(kāi)表的數(shù)量

key_buffer_size：索引緩存大小

query_cache_size：查詢(xún)緩存大小

sort_buffer_size：排序緩存大小(會(huì)將排序完的數(shù)據(jù)緩存起來(lái))

read_buffer_size：順序讀緩存大小

read_rnd_buffer_size：某種特定順序讀緩存大小(如order by子句的查詢(xún))

PS：查看配置方法：show variables like '%max_connecttions%';

2.MySQL負(fù)載高時(shí)，如何找到是由哪些SQL引起的?

方法：慢查詢(xún)?nèi)罩痉治?MySQLdumpslow)

慢查詢(xún)?nèi)罩纠樱煽吹矫總€(gè)慢查詢(xún)SQL的耗時(shí)：

  1. # User@Host: edu_online[edu_online] @ [10.139.10.167] 
  2.  
  3. # Query_time: 1.958000 Lock_time: 0.000021 Rows_sent: 254786 Rows_examined: 254786 
  4.  
  5. SET timestamp=1410883292; 
  6.  
  7. select * from t_online_group_records;  

日志顯示該查詢(xún)用了1.958秒，返回254786行記錄，一共遍歷了254786行記錄。及具體的時(shí)間戳和SQL語(yǔ)句。

使用MySQLdumpslow進(jìn)行慢查詢(xún)?nèi)罩痉治?/p>

MySQLdumpslow -s t -t 5 slow_log_20140819.txt

輸出查詢(xún)耗時(shí)最多的Top5條SQL語(yǔ)句

-s：排序方法，t表示按時(shí)間 (此外，c為按次數(shù)，r為按返回記錄數(shù)等)

-t：去Top多少條，-t 5表示取前5條

執(zhí)行完分析結(jié)果如下：

  1. Count: 1076100 Time=0.09s (99065s) Lock=0.00s (76s) Rows=408.9 (440058825), edu_online[edu_online]@28hosts 
  2.  
  3. select * from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > N 
  4.  
  5. Count: 1076099 Time=0.05s (52340s) Lock=0.00s (91s) Rows=62.6 (67324907), edu_online[edu_online]@28hosts 
  6.  
  7. select * from t_online_course where UNIX_TIMESTAMP(c_updatetime) > N 
  8.  
  9. Count: 63889 Time=0.78s (49607s) Lock=0.00s (3s) Rows=0.0 (18), edu_online[edu_online]@[10x.213.1xx.1xx] 
  10.  
  11. select f_uin from t_online_student_contact where f_modify_time > N 
  12.  
  13. Count: 1076097 Time=0.02s (16903s) Lock=0.00s (72s) Rows=52.2 (56187090), edu_online[edu_online]@28hosts 
  14.  
  15. select * from t_online_video_info where UNIX_TIMESTAMP(v_update_time) > N 
  16.  
  17. Count: 330046 Time=0.02s (6822s) Lock=0.00s (45s) Rows=0.0 (2302), edu_online[edu_online]@4hosts 
  18.  
  19. select uin,cid,is_canceled,unix_timestamp(end_time) as endtime,unix_timestamp(update_time) as updatetime 
  20.  
  21. from t_kick_log where unix_timestamp(update_time) > N  

以第1條為例，表示這類(lèi)SQL(N可以取很多值，這里MySQLdumpslow會(huì)歸并起來(lái))在8月19號(hào)的慢查詢(xún)?nèi)罩緝?nèi)出現(xiàn)了1076100次，總耗時(shí)99065秒，總返回440058825行記錄，有28個(gè)客戶(hù)端IP用到。

通過(guò)慢查詢(xún)?nèi)罩痉治?，就可以找到最耗時(shí)的SQL，然后進(jìn)行具體的SQL分析了

慢查詢(xún)相關(guān)的配置參數(shù)

log_slow_queries：是否打開(kāi)慢查詢(xún)?nèi)罩?，得先確保=ON后面才有得分析

long_query_time：查詢(xún)時(shí)間大于多少秒的SQL被當(dāng)做是慢查詢(xún)，一般設(shè)為1S

log_queries_not_using_indexes：是否將沒(méi)有使用索引的記錄寫(xiě)入慢查詢(xún)?nèi)罩?/p>

slow_query_log_file：慢查詢(xún)?nèi)罩敬娣怕窂?/p>

3.如何針對(duì)具體的SQL做優(yōu)化?

使用Explain分析SQL語(yǔ)句執(zhí)行計(jì)劃

  1. MySQL> explain select * from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > 123456789; 
  2.  
  3. +----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+ 
  4.  
  5. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
  6.  
  7. +----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+ 
  8.  
  9. | 1 | SIMPLE | t_online_group_records | ALL | NULL | NULL | NULL | NULL | 47 | Using where | 
  10.  
  11. +----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+ 
  12.  
  13. 1 row in set (0.00 sec)  

如上面例子所示，重點(diǎn)關(guān)注下type，rows和Extra：

type：使用類(lèi)別，有無(wú)使用到索引。結(jié)果值從好到壞：… > range(使用到索引) > index > ALL(全表掃描)，一般查詢(xún)應(yīng)達(dá)到range級(jí)別

rows：SQL執(zhí)行檢查的記錄數(shù)

Extra：SQL執(zhí)行的附加信息，如”Using index”表示查詢(xún)只用到索引列，不需要去讀表等

使用Profiles分析SQL語(yǔ)句執(zhí)行時(shí)間和消耗資源

  1. MySQL> set profiling=1; (啟動(dòng)profiles，默認(rèn)是沒(méi)開(kāi)啟的) 
  2.  
  3. MySQL> select count(1) from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > 123456789; (執(zhí)行要分析的SQL語(yǔ)句) 
  4.  
  5. MySQL> show profiles; 
  6.  
  7. +----------+------------+----------------------------------------------------------------------------------------------+ 
  8.  
  9. | Query_ID | Duration   | Query                                                                                        | 
  10.  
  11. +----------+------------+----------------------------------------------------------------------------------------------+ 
  12.  
  13. |        1 | 0.00043250 | select count(1) from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > 123456789 | 
  14.  
  15. +----------+------------+----------------------------------------------------------------------------------------------+ 
  16.  
  17. 1 row in set (0.00 sec) 
  18.  
  19. MySQL> show profile cpu,block io for query 1; (可看出SQL在各個(gè)環(huán)節(jié)的耗時(shí)和資源消耗) 
  20.  
  21. +----------------------+----------+----------+------------+--------------+---------------+ 
  22.  
  23. | Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | 
  24.  
  25. +----------------------+----------+----------+------------+--------------+---------------+ 
  26.  
  27. ... 
  28.  
  29. | optimizing           | 0.000016 | 0.000000 |   0.000000 |            0 |             0 | 
  30.  
  31. | statistics           | 0.000020 | 0.000000 |   0.000000 |            0 |             0 | 
  32.  
  33. | preparing            | 0.000017 | 0.000000 |   0.000000 |            0 |             0 | 
  34.  
  35. | executing            | 0.000011 | 0.000000 |   0.000000 |            0 |             0 | 
  36.  
  37. | Sending data         | 0.000076 | 0.000000 |   0.000000 |            0 |             0 | 
  38.  
  39. ...  

SQL優(yōu)化的技巧 (只提一些業(yè)務(wù)常遇到的問(wèn)題)

最關(guān)鍵：索引，避免全表掃描。

對(duì)接觸的項(xiàng)目進(jìn)行慢查詢(xún)分析，發(fā)現(xiàn)***0的基本都是忘了加索引或者索引使用不當(dāng)，如索引字段上加函數(shù)導(dǎo)致索引失效等(如where UNIX_TIMESTAMP(gre_updatetime)>123456789)

  1. +----------+------------+---------------------------------------+ 
  2.  
  3. | Query_ID | Duration | Query | 
  4.  
  5. +----------+------------+---------------------------------------+ 
  6.  
  7. | 1 | 0.00024700 | select * from mytable where id=100 | 
  8.  
  9. | 2 | 0.27912900 | select * from mytable where id+1=101 | 
  10.  
  11. +----------+------------+---------------------------------------+  

另外很多同學(xué)在拉取全表數(shù)據(jù)時(shí)，喜歡用select xx from xx limit 5000,1000這種形式批量拉取，其實(shí)這個(gè)SQL每次都是全表掃描，建議添加1個(gè)自增id做索引，將SQL改為select xx from xx where id>5000 and id;

  1. +----------+------------+-----------------------------------------------------+ 
  2.  
  3. | Query_ID | Duration | Query | 
  4.  
  5. +----------+------------+-----------------------------------------------------+ 
  6.  
  7. | 1 | 0.00415400 | select * from mytable where id>=90000 and id91000 | 
  8.  
  9. | 2 | 0.10078100 | select * from mytable limit 90000,1000 | 
  10.  
  11. +----------+------------+-----------------------------------------------------+  

合理用好索引，應(yīng)該可解決大部分SQL問(wèn)題。當(dāng)然索引也非越多越好，過(guò)多的索引會(huì)影響寫(xiě)操作性能

只select出需要的字段，避免select

  1. +----------+------------+-----------------------------------------------------+ 
  2.  
  3. | Query_ID | Duration | Query | 
  4.  
  5. +----------+------------+-----------------------------------------------------+ 
  6.  
  7. | 1 | 0.02948800 | select count(1) from ( select id from mytable ) a | 
  8.  
  9. | 2 | 1.34369100 | select count(1) from ( select * from mytable ) a | 
  10.  
  11. +----------+------------+-----------------------------------------------------+  

盡量早做過(guò)濾，使Join或者Union等后續(xù)操作的數(shù)據(jù)量盡量小

把能在邏輯層算的提到邏輯層來(lái)處理，如一些數(shù)據(jù)排序、時(shí)間函數(shù)計(jì)算等

…….

PS：關(guān)于SQL優(yōu)化，已經(jīng)有足夠多文章了，所以就不講太全面了，只重點(diǎn)說(shuō)自己1個(gè)感受：索引!基本都是因?yàn)樗饕?

4.SQL層面已難以?xún)?yōu)化，請(qǐng)求量繼續(xù)增大時(shí)的應(yīng)對(duì)策略?

下面是我能想到的幾個(gè)方法，每個(gè)方法又都是一篇大文章了，這里就不展開(kāi)

分庫(kù)分表

使用集群(master-slave)，讀寫(xiě)分離

增加業(yè)務(wù)的cache層

使用連接池

5.MySQL如何做主從數(shù)據(jù)同步?

復(fù)制機(jī)制(Replication)

master通過(guò)復(fù)制機(jī)制，將master的寫(xiě)操作通過(guò)binlog傳到slave生成中繼日志(relaylog)，slave再將中繼日志redo，使得主庫(kù)和從庫(kù)的數(shù)據(jù)保持同步

復(fù)制相關(guān)的3個(gè)MySQL線程

  1. slave上的I/O線程：向master請(qǐng)求數(shù)據(jù)
  2. master上的Binlog Dump線程：讀取binlog事件并把數(shù)據(jù)發(fā)送給slave的I/O線程
  3. slave上的SQL線程：讀取中繼日志并執(zhí)行，更新數(shù)據(jù)庫(kù)

屬于slave主動(dòng)請(qǐng)求拉取的模式

實(shí)際使用可能遇到的問(wèn)題

數(shù)據(jù)非強(qiáng)一致：CDB默認(rèn)為異步復(fù)制，master和slave的數(shù)據(jù)會(huì)有一定延遲(稱(chēng)為主從同步距離，一般 主從同步距離變大：可能是DB寫(xiě)入壓力大，也可能是slave機(jī)器負(fù)載高，網(wǎng)絡(luò)波動(dòng)等原因，具體問(wèn)題具體分析

相關(guān)監(jiān)控命令

show processlist：查看MySQL進(jìn)程信息，包括3個(gè)同步線程的當(dāng)前狀態(tài)

show master status ：查看master配置及當(dāng)前復(fù)制信息

show slave status：查看slave配置及當(dāng)前復(fù)制信息

6.如何防止DB誤操作和做好容災(zāi)?

業(yè)務(wù)側(cè)應(yīng)做到的幾點(diǎn)：

重要DB數(shù)據(jù)的手工修改操作，操作前需做到2點(diǎn)：1 先在測(cè)試環(huán)境操作 2 備份數(shù)據(jù)

根據(jù)業(yè)務(wù)重要性做定時(shí)備份，考慮系統(tǒng)可承受的恢復(fù)時(shí)間

進(jìn)行容災(zāi)演練，感覺(jué)很必要

MySQL備份和恢復(fù)操作

1.備份：使用MySQLdump導(dǎo)出數(shù)據(jù)

MySQLdump -u 用戶(hù)名 -p 數(shù)據(jù)庫(kù)名 [表名] > 導(dǎo)出的文件名

MySQLdump -uxxx -p xxx mytable > mytable.20140921.bak.sql

2.恢復(fù)：導(dǎo)入備份數(shù)據(jù)

MySQL -uxxx -p xxxx

3.恢復(fù)：導(dǎo)入備份數(shù)據(jù)之后發(fā)送的寫(xiě)操作。先使用MySQLbinlog導(dǎo)出這部分寫(xiě)操作SQL(基于時(shí)間點(diǎn)或位置)

如導(dǎo)出2014-09-21 09:59:59之后的binlog：

  1. MySQLbinlog --database="test" --start-date="2014-09-21 09:59:59" /var/lib/MySQL/mybinlog.000001 > binlog.data.sql 

如導(dǎo)出起始id為123456之后的binlog：

  1. MySQLbinlog --database="test" --start-position="123456" /var/lib/MySQL/mybinlog.000001 > binlog.data.sql 

***把要恢復(fù)的binlog導(dǎo)入db

  1. MySQL -uxxxx -p xxxx 

7.該選擇MySQL哪種存儲(chǔ)引擎，Innodb具有什么特性?

存儲(chǔ)引擎簡(jiǎn)介

插件式存儲(chǔ)引擎是MySQL的重要特性，MySQL支持多種存儲(chǔ)引擎以滿足用戶(hù)的多種應(yīng)用場(chǎng)景

存儲(chǔ)引擎解決的問(wèn)題：如何組織MySQL數(shù)據(jù)在介質(zhì)中高效地讀取，需考慮存儲(chǔ)機(jī)制、索引設(shè)計(jì)、并發(fā)讀寫(xiě)的鎖機(jī)制等

MySQL5.0支持的存儲(chǔ)引擎有MyISAM、InnoDB、Memory、Merge等

**MyISAM和InnoDB的區(qū)別(只說(shuō)重點(diǎn)了)

1.InnoDB

    MySQL5.5之后及CDB的默認(rèn)引擎。

  • 支持行鎖：并發(fā)性能好
  • 支持事務(wù)：故InnoDB稱(chēng)為事務(wù)性存儲(chǔ)引擎，支持ACID，提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全
  • 支持外鍵：當(dāng)前唯一支持外鍵的引擎

2.MyISAM

    MySQL5.5之前默認(rèn)引擎

  • 支持表鎖：插入+查詢(xún)速度快，更新+刪除速度慢
  • 不支持事務(wù)

使用show engines可查看當(dāng)前MySQL支持的存儲(chǔ)引擎詳情

 

8.MySQL內(nèi)部結(jié)構(gòu)有哪些層次?

非專(zhuān)業(yè)DBA，這里只簡(jiǎn)單貼個(gè)結(jié)構(gòu)圖說(shuō)明下。MySQL是開(kāi)源系統(tǒng)，其設(shè)計(jì)思路和源代碼都出自大牛之手，有空可以學(xué)習(xí)下。

 

 

  1. Connectors：連接器。接收不同語(yǔ)言的Client交互
  2. Management Serveices & Utilities：系統(tǒng)管理和控制工具
  3. Connection Pool: 連接池。管理用戶(hù)連接
  4. SQL Interface: SQL接口。接受用戶(hù)的SQL命令，并且返回用戶(hù)需要查詢(xún)的結(jié)果
  5. Parser: 解析器。驗(yàn)證和解析SQL語(yǔ)句成內(nèi)部數(shù)據(jù)結(jié)構(gòu)
  6. Optimizer: 查詢(xún)優(yōu)化器。為查詢(xún)語(yǔ)句選擇合適的執(zhí)行路徑
  7. Cache和Buffer：查詢(xún)緩存。緩存查詢(xún)的結(jié)果，有***即可直接返回
  8. Engine：存儲(chǔ)引擎。MySQL數(shù)據(jù)***組織并存儲(chǔ)成具體文件 
責(zé)任編輯：龐桂玉 來(lái)源： 數(shù)據(jù)庫(kù)開(kāi)發(fā)
MySQL開(kāi)發(fā)實(shí)踐
相關(guān)推薦

2024-07-04 11:06:47

2011-12-30 10:28:15

Win 8操作系統(tǒng)難題

2022-04-01 12:40:13

MySQL數(shù)據(jù)庫(kù)

2011-09-20 17:38:17

2023-01-28 08:24:28

MySQL索引B+樹(shù)

2023-04-26 07:40:34

MySQL索引類(lèi)型存儲(chǔ)

2011-11-04 16:01:01

2020-09-30 18:19:27

RedisJava面試

2012-07-24 14:00:30

2019-12-30 10:10:32

前端工程師Web 微前端

2011-09-12 14:54:55

神舟筆記本

2023-05-15 10:02:00

分庫(kù)分表數(shù)據(jù)

2011-09-24 12:26:41

2011-09-22 09:38:27

CIO云計(jì)算

2022-03-21 14:09:19

面試C語(yǔ)言代碼

2017-03-08 14:35:22

存儲(chǔ)云計(jì)算華云網(wǎng)際

2019-07-09 17:39:12

存儲(chǔ)

2022-01-24 14:08:16

Redis面試命令

2011-08-28 16:26:20

清華同方筆記本

2011-12-01 13:20:58

云服務(wù)器天地超云
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)