Mysql開發實踐8問，你能hold住幾個？，mysqlhold

Mysql開發實踐8問，你能hold住幾個？，mysqlhold
最近項目對DB依賴比較重，梳理了這段時間使用Mysql遇到的8個比較具有代表性的問題，答案也比較偏開發實踐，沒有DBA專業和深入，有出入的請使勁拍磚！...

1、Mysql讀寫效能是多少，有哪些效能相關的配置參數？
2、Mysql負載高時，如何找到是由哪些SQL引起的？
3、如何針對具體的SQL做最佳化？
4、SQL層面已難以最佳化，請求量繼續增大時的應對策略？
5、Mysql如何做主從資料同步？
6、如何防止DB誤操作和做好容災？
7、該選擇Mysql哪種儲存引擎，Innodb具有什麼特性？
8、Mysql內部結構有哪些層次？

======================================================

1、Mysql讀寫效能是多少，有哪些效能相關的重要參數？

* 這裡做了幾個簡單壓測實驗
機器：8核CPU，8G記憶體
表結構(盡量類比業務)：12個欄位(1個bigint(20)為自增primary key，5個int(11)，5個varchar(512)，1個timestamp)
實驗1(寫)：insert => 6000/s
前提：串連數100，每次insert單條記錄
分析：CPU跑了50%，這時磁碟為順序寫，故效能很高
實驗2(寫)：update(where條件命中索引) => 200/s 
前提：串連數100，10w條記錄，每次update單條記錄的4個欄位(2個int(11)，2個varchar(512))
分析：CPU跑2%，瓶頸明顯在IO的隨機寫
實驗3(讀)：select(where條件命中索引) => 5000/s
前提：串連數100，10w條記錄，每次select單條記錄的4個欄位(2個int(11)，2個varchar(512))
分析：CPU跑6%，瓶頸在IO，和db的cache大小相關
實驗4(讀)：select(where條件沒命中索引) => 60/s
前提：串連數100，10w條記錄，每次select單條記錄的4個欄位(2個int(11)，2個varchar(512))
分析：CPU跑到80%，每次select都需遍曆所有記錄，看來索引的效果非常明顯！

* 幾個重要的配置參數，可根據實際的機器和業務特點調整
max_connecttions：最大串連數
table_cache：緩衝開啟表的數量
key_buffer_size：索引緩衝大小
query_cache_size：查詢快取大小
sort_buffer_size：排序緩衝大小(會將排序完的資料緩衝起來)
read_buffer_size：順序讀緩衝大小
read_rnd_buffer_size：某種特定順序讀緩衝大小(如order by子句的查詢)
PS：查看配置方法：show variables like '%max_connecttions%';

======================================================

2、Mysql負載高時，如何找到是由哪些SQL引起的？

* 方法：慢查詢日誌分析(mysqldumpslow)

* 慢查詢日誌例子，可看到每個慢查詢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進行慢查詢日誌分析
mysqldumpslow -s t -t 5 slow_log_20140819.txt 
輸出查詢耗時最多的Top5條SQL語句
-s：排序方法，t表示按時間 (此外，c為按次數，r為按返回記錄數等)
-t：去Top多少條，-t 5表示取前5條
執行完分析結果如下：
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]@[10.213.170.137]
  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號的慢查詢日誌內出現了1076100次，總耗時99065秒，總返回440058825行記錄，有28個用戶端IP用到。
通過慢查詢日誌分析，就可以找到最耗時的SQL，然後進行具體的SQL分析了

* 慢查詢相關的配置參數
log_slow_queries：是否開啟慢查詢日誌，得先確保=ON後面才有得分析
long_query_time：查詢時間大於多少秒的SQL被當做是慢查詢，一般設為1S
log_queries_not_using_indexes：是否將沒有使用索引的記錄寫入慢查詢日誌
slow_query_log_file：慢查詢日誌存放路徑

======================================================

3、如何針對具體的SQL做最佳化？

* 使用Explain分析SQL語句執行計畫
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)
如上面例子所示，重點關注下type，rows和Extra：
type：使用類別，有無使用到索引。結果值從好到壞：... > range(使用到索引) > index > ALL(全表掃描)，一般查詢應達到range層級
rows：SQL執行檢查的記錄數
Extra：SQL執行的附加資訊，如"Using index"表示查詢只用到索引列，不需要去讀表等

* 使用Profiles分析SQL語句執行時間和消耗資源
mysql> set profiling=1; (啟動profiles，預設是沒開啟的)
mysql> select count(1) from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > 123456789; (執行要分析的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在各個環節的耗時和資源消耗)
+----------------------+----------+----------+------------+--------------+---------------+
| 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最佳化的技巧 (只提一些業務常遇到的問題)
1、最關鍵：索引，避免全表掃描。
對接觸的項目進行慢查詢分析，發現TOP10的基本都是忘了加索引或者索引使用不當，如索引欄位上加函數導致索引失效等(如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  |
+----------+------------+---------------------------------------+
另外很多同學在拉取全表資料時，喜歡用select xx from xx limit 5000,1000這種形式批量拉取，其實這個SQL每次都是全表掃描，建議添加1個自增id做索引，將SQL改為select xx from xx where id>5000 and id<6000;
+----------+------------+-----------------------------------------------------+
| Query_ID | Duration   | Query                                               |
+----------+------------+-----------------------------------------------------+
|        1 | 0.00415400 | select * from mytable where id>=90000 and id<=91000 |
|        2 | 0.10078100 | select * from mytable limit 90000,1000              |
+----------+------------+-----------------------------------------------------+
合理用好索引，應該可解決大部分SQL問題。當然索引也非越多越好，過多的索引會影響寫操作效能
2、只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    |
+----------+------------+-----------------------------------------------------+
3、盡量早做過濾，使Join或者Union等後續操作的資料量盡量小
4、把能在邏輯層算的提到邏輯層來處理，如一些資料排序、時間Function Compute等
5、......

PS：關於SQL最佳化，KM已經有足夠多文章了，所以就不講太全面了，只重點說自己1個感受：索引！基本都是因為索引！

======================================================

4、SQL層面已難以最佳化，請求量繼續增大時的應對策略？

* 下面是我能想到的幾個方法，每個方法又都是一篇大文章了，這裡就不展開
分庫分表
使用叢集(master-slave)，讀寫分離
增加業務的cache層
使用串連池

======================================================

5、Mysql如何做主從資料同步？

* 複製機制（Replication）
master通過複製機制，將master的寫操作通過binlog傳到slave產生中繼日誌(relaylog)，slave再將中繼日誌redo，使得主庫和從庫的資料保持同步

* 複製相關的3個Mysql線程
1、slave上的I/O線程：向master請求資料
2、master上的Binlog Dump線程：讀取binlog事件並把資料發送給slave的I/O線程
3、slave上的SQL線程：讀取中繼日誌並執行，更新資料庫
屬於slave主動請求拉取的模式

* 實際使用可能遇到的問題
資料非強一致：預設為非同步複製，master和slave的資料會有一定延遲(稱為主從同步距離，一般<1s)
主從同步距離變大：可能是DB寫入壓力大，也可能是slave機器負載高，網路波動等原因，具體問題具體分析

* 相關監控命令
show processlist ：查看Mysql進程資訊，包括3個同步線程的目前狀態
show master status ：查看master配置及當前複製資訊
show slave status ：查看slave配置及當前複製資訊

======================================================

6、如何防止DB誤操作和做好容災？

* 業務側應做到的幾點：
重要DB資料的手工修改操作，操作前需做到2點：1 先在測試環境操作 2 備份資料
根據業務重要性做定時備份，考慮系統可承受的恢復
進行容災演練，感覺很必要

* Mysql備份和恢複操作
1、備份：使用mysqldump匯出資料
mysqldump -u 使用者名稱 -p 資料庫名 [表名] > 匯出的檔案名稱
mysqldump -uroot -p test mytable > mytable.20140921.bak.sql
2、恢複：匯入備份資料
mysql -uroot -p test < mytable.20140921.bak.sql
3、恢複：匯入備份資料之後發送的寫操作。先使用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 -uroot -p test < binlog.data.sql

======================================================

7、該選擇Mysql哪種儲存引擎，Innodb具有什麼特性？

* 儲存引擎簡介
外掛程式式儲存引擎是Mysql的重要特性，Mysql支援多種儲存引擎以滿足使用者的多種應用情境
儲存引擎解決的問題：如何組織mysql資料在介質中高效地讀取，需考慮儲存機制、索引設計、並發讀寫的鎖機制等
Mysql5.0支援的儲存引擎有MyISAM、InnoDB、Memory、Merge等

* MyISAM和InnoDB的區別(只說重點了)
1、InnoDB
Mysql5.5之後預設引擎。
1 支援行鎖：並發效能好
2 支援事務：故InnoDB稱為事務性儲存引擎，支援ACID，提供了具有提交、復原和崩潰恢複能力的事務安全
3 支援外鍵：當前唯一支援外鍵的引擎
2、MyISAM
Mysql5.5之前預設引擎。
1 支援表鎖：插入+查詢速度快，更新+刪除速度慢
2 不支援事務

* 使用show engines可查看當前Mysql支援的儲存引擎詳情


======================================================

8、Mysql內部結構有哪些層次？

* 非專業DBA，這裡只簡單貼個結構圖說明下。Mysql是開源系統，其設計思路和原始碼都出自大牛之手，有空可以學習下。

1 Connectors：連接器。接收不同語言的Client互動
2 Management Serveices & Utilities：系統管理和控制工具
3 Connection Pool: 串連池。系統管理使用者串連
4 SQL Interface: SQL介面。接受使用者的SQL命令，並且返回使用者需要查詢的結果
5 Parser: 解析器。驗證和解析SQL語句成內部資料結構
6 Optimizer: 查詢最佳化工具。為查詢語句選擇合適的執行路徑
7 Cache和Buffer：查詢快取。緩衝查詢的結果，有命中即可直接返回
8 Engine ：儲存引擎。Mysql資料最後組織並儲存成具體檔案

《PHP+MySQL+Dreamweaver 8動態網站開發從基礎到實踐》新聞發布系統後台管理密碼？

你試一下，使用者名稱和密碼都是admin，你那那個login.php的代碼傳上來讓我幫你找找?
 
php+mysql動態網站開發從基礎到實踐 薑林美 電子版 誰有？

這是什麼書，都沒聽過，php的話，建議看細說php，和php與mysql權威指南，肯定比你這個好