Optimzingmysql酷訊網(wǎng).ppt

,Optimzing mysql,I feel the needthe need for speed.,壯志凌云（top gun）,性能監(jiān)控 10分 性能優(yōu)化 20分 常見的架構 10分 Tools and tips 10分,性能監(jiān)控,操作系統(tǒng)級別的 Mysql級別,操作系統(tǒng),vmstat iostat free top sar mpstat uptime, netstat strace,mysql,Show processlist Mysqlreport /mysqlreportguide Mysqladmin -uxxx p -i 1 -r extendedstatus|grep -v “| 0” Innotop /blog/2006/07/02/innotop-mysql-innodb-monitor/ Show global status,show inondb status show session status; Mytop Explain Profiling Mysqldumpslow mysqlsla sysbench,vmstat,基本的物理和虛擬內(nèi)存的使用和一些基本的系統(tǒng)統(tǒng)計信息 Vmstat S M Vmstat 5 5 Swap顛簸現(xiàn)象 Top ps axl,iostat,度量磁盤i/o,cpu和設備io使用 Iostat -c iostat dx 5 %iowait 并發(fā)請求的數(shù)量 并發(fā)=（r/s+w/s）*(svctm/1000)(從頭到尾服務請求時間),Cpu密集型機器,Io密集型,Swap idle,誰可能導致高的cpu usage query ，joins,every 進程切換， 鎖表 內(nèi)存排序 臨時表 加密算法 誰可能導致高的disk usage 臨時表 硬盤上排序 start with an idea ,then look for infromation support it,netstat,服務鏈接狀態(tài) netstat -nat |awk print $6 | sort | uniq -c | sort -n 是否被dos：netstat -atun | awk print $5 | cut -d: -f1 | sed -e /$/d |sort | uniq -c | sort -n /bin/netstat -na|grep ESTABLISHED|awk print $5|awk -F: print $1|sort|uniq -c|sort -rn|grep -v -E 172.16|127.0|awk if ($2!=null & $110) print $1,$2,其它,Top： Ps： ps aux|awk if ($6 (1024*15)print $2|grep -v PID ,分析服務器,Mysqlreport Mysqladmin mysqladmin extended -r -i 10| grep v “| 0 ” -uroot p Innotop . Show global status Show innodb status,分析查詢,Show processlist Flush status Show session status like Select% Show session status like Handler% Show session status like Sort% Show session status like Create% Set profiling,分析mysql連接,netstat -ntp| grep :40327 netstat -ntp| grep 10940/mysqld grep 3306 /etc/services ps -eaf| grep UID|10940 lsof -i -P| grep 10942,性能優(yōu)化,操作系統(tǒng) mysql 應用程序,操作系統(tǒng),不要交換區(qū)。如果內(nèi)存不足，增加更多的內(nèi)存或配置你的系統(tǒng)使用較少內(nèi)存。 flick:echo 0 /proc/sys/vm/swappiness innodb_flush_method=O_DIRECT 不要使用NFS磁盤(會有NFS鎖定的問題)。 增加系統(tǒng)和MySQL服務器的打開文件數(shù)量。(在safe_mysqld腳本中加入ulimit -n #)。 增加系統(tǒng)的進程和線程數(shù)量。 選擇使用哪種文件系統(tǒng)。在Linux上的Reiserfs對于打開、讀寫都非常快。文件檢查只需幾秒種。,Mysql配置,Mysiam 緩存 key_buffer_size key_buffer_1.key_buffer_size=1G show variables like key_buffer_size; show global status like key_read%; key_cache_miss_rate Key_reads / Key_read_requests * 100% du sch find /mysqldatapath/ -name “*.MYI” Innodb 緩沖池 innodb_bufer_pool_size 80% show status like Innodb_buffer_pool_read% Innodb_buffer_pool_read_requests/Innodb_buffer_pool_reads show innodb statusG BUFFER POOL AND MEMORY Total memory allocated 4668764894;,查詢緩存,show global status like Qcache%; show variables like query_cache%; 查詢緩存利用率 = (query_cache_size Qcache_free_memory) / query_cache_size * 100% 查詢緩存命中率 = (Qcache_hits Qcache_inserts) / Qcache_hits * 100% /MySQL/,innodb,刷寫日志緩沖 Innodb_flush_log_at_trx_commit 0 1 2 打開并清寫日志和文件 innodb_flush_method,myisam,初始化： initial =key_buffer_size + query_cache_size 連接 per_connection=(sort_buffer_size +read_rnd_buffer_size + join_buffer_size + read_buffer_size + thead_stack ) 總和： initial + max_connections * per_connection initial + (max_connections * per_connection)/3),innodb,初始化： initial =innodb_buffer_pool_size + query_cache_size 連接 per_connection = (sort_buffer_size + join_buffer_size + thead_stack + binlog_cache_size + read_buffer_size + read_rnd_buffer_size) 總和： initial + max_connections * per_connection initial + (max_connections * per_connection)/3),數(shù)據(jù)類型選擇,更小通常更好,慷慨并不明智 簡單就好 使用mysql內(nèi)建的類型保存日期和時間，使用timestamp 保存，空間是datetime一半 使用整數(shù)保存ip 15 bytes for char(15) vs. 4 bytes for the integer ip2long() 和 long2ip() inet_aton 3 盡量避免null 4 Char /varcha的選擇 對于MyISAM而言，如果沒有VARCHAR，TEXT等變長類型，那么每行數(shù)據(jù)所占用的空間都是定長的（Fixed），俗稱靜態(tài)表，相對應的就是動態(tài)表。當執(zhí)行一次查詢時，MySQL可以通過索引文件找到所需內(nèi)容的實際行號，此時，由于每行數(shù)據(jù)所占用的空間都是定長的（Fixed），所以可以通過查詢到的實際行號直接定位到數(shù)據(jù)文件的具體位置， 對于InnoDB而言，數(shù)據(jù)行是沒有所謂定長與否的概念的，這是由其結構所決定的：在InnoDB中，數(shù)據(jù)就位于Primary Key的B-Tree葉節(jié)點之上而除Primary Key之外的索引被稱為Secondary Index，它們直接指向Primary Key。 用char來代替varchar，MyISAM是這樣，InnoDB則相反 5 使用enum 代替字符串類型 select internet + 0 from hotel_info group by internet;,索引,隔離列 select * from tablename where id+1=5 Select * where TO_DAYS(CURRENT_DATE) TO_DAYS(data_col) =date_sub(current_date,interval 10 day) Select * where data_col = date_sum(2010-04-12,interval 10 day) EXPLAIN SELECT * FROM film WHERE title LIKE Tr%G EXPLAIN SELECT * FROM film WHERE LEFT(title,2) = Tr G 組合索引 前綴索引 覆蓋索引 合并索引 去除多余索引和重復索引 create table test (id int not null primary key, unique(id), index(id) ),合并索引,索引合并方法用于通過range掃描搜索行并將結果合成一個。合并會產(chǎn) 生并集、交集或者正在進行的掃描的交集的并集。 在EXPLAIN輸出中，該方法表現(xiàn)為type列內(nèi)的index_merge。 在這種情況下，key列包含一列使用的索引，key_len包含這些索引的最長的關鍵元素,SELECT * FROM tbl_name WHERE key_part1 = 10 OR key_part2 = 20;,前綴索引,Key(a,b,c) Order by a ,order by a,b order by a,b,c order by a desc ,b desc,c desc Where a = const order by b,c,where a=const and b =const order by c Where a = const order by b,c where a = const and b const order by,c Order by a asc,b desc,c desc Where g = const oder by b ,c Where a = const order by c Where a = const order by a ,d Where axx order by b,c Where axx order by a,b Where a=const order by b desc ,a asc,覆蓋索引,Select * from products where actor=sean carrey and and title like %apollo% Select * from products join (select prod_id from products where actor= sean carrey and title like %apollo%) as t1 on (d_id = _id),逆范式化,適當?shù)娜哂?分拆表,產(chǎn)品設計,產(chǎn)品設計-數(shù)據(jù)交互-mysql Antoine de SaintExupery是法國作家兼飛機設計師，他曾經(jīng)說過：“設計者確定其設計已經(jīng)達到了完美的標準不是不能再增加任何東西，而是不能再減少任何東 西。” 分頁的實現(xiàn),/group/BigBangTheory/discussion?start=25,1 explain select SQL_NO_CACHE * from page_test force index(idx_b_c) where b=1 order by c desc limit 2000,10; +-+-+-+-+-+-+-+-+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+-+-+-+-+-+-+-+-+-+ | 1 | SIMPLE | page_test | ref | idx_b_c | idx_b_c | 4 | const | 2222 | Using where | +-+-+-+-+-+-+-+-+-+-+ 2 mysql explain select SQL_NO_CACHE * from page_test, (select SQL_NO_CACHE id from page_test force index(idx_b_c) where b=1 order by c desc limit 2000,10) temp where page_test.id=temp.id; +-+-+-+-+-+-+-+-+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+-+-+-+-+-+-+-+-+-+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 10 | | | 1 | PRIMARY | page_test | eq_ref | PRIMARY | PRIMARY | 8 | temp.id | 1 | | | 2 | DERIVED | page_test | ref | idx_b_c | idx_b_c | 4 | | 2222 | Using where; Using index | +-+-+-+-+-+-+-+-+-+-+ 3 explain select SQL_NO_CACHE * from page_test force index(idx_b_id) where b=1 and id187796 order by id desc limit 10; +-+-+-+-+-+-+-+-+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+-+-+-+-+-+-+-+-+-+ | 1 | SIMPLE | page_test | range | idx_b_id | idx_b_id | 12 | NULL | 190 | Using where | +-+-+-+-+-+-+-+-+-+-+ 5 select SQL_NO_CACHE * from page_test force index(idx_b_c) where b=1 order by c desc 870,10 select SQL_NO_CACHE * from page_test force index(idx_b_c) where b=1 order by c asc 9120,10 ./tuning-primer.sh all,優(yōu)化count(*),Mysiam: select sql_no_cache count(*) from statistic_go where id 10; select sql_no_cache (select count(*) from statistic_go) - count(*) from statistic_go where id =0; 建立計數(shù)器觸發(fā)器 對同一個表的select 和update update hotel_image inner join (select count(*) as cnt from hotel_image) as der set hotel_image.size = t;,其它一些,Group by 不進行排序，可以ordery by null 能夠快速縮小結果集的WHERE條件寫在前面，如果有恒量條件，也盡量放在前面 使用 UNION 來取代 IN 和 OR 定期執(zhí)行optimize / analyze table 往innoDB表導入數(shù)據(jù)時，先關閉autocommit模式，否則會實時刷新數(shù)據(jù)到磁盤 對于頻繁更改的MyISAM表,應盡量避免更新所有變長字段(VARCHAR、BLOB和TEXT) 分表 分庫 匯總表 十大熱門話題 create table hotel_infonew like hotel_info; rename table hotel_info to hotel_info_old ,hotel_infonew to hotel_info 放棄關系型數(shù)據(jù)庫 key=value ，計數(shù)表,常見架構方案,Replication 雙master 集群。,replication,1 數(shù)據(jù)分發(fā) ,scale out，sacle up 2 負載均衡 load balance 3 備份，一般不會用作備份，一旦執(zhí)行delete操作，replication也不會保留 4 高可用 5 可以在不同的主從庫上使用不同的存儲引擎,原理,簡單的講就是master記錄其變化到binlog，slave接收到變化后會記錄到他的Relay log，slave通過重放relay log，然后就寫進自己的log 1)、Slave上面的IO進程連接上Master，并請求從指定日志文件的指定位置（或者從最開始的日志）之后的日志內(nèi)容； 2)、Master接收到來自Slave的IO進程的請求后，通過負責復制的IO進程根據(jù)請求信息讀取制定日志指定位置之后的日志信息，返回給Slave 的IO進程。返回信息中除了日志所包含的信息之外，還包括本次返回的信息已經(jīng)到Master端的bin-log文件的名稱以及bin-log的位置； 3)、Slave的IO進程接收到信息后，將接收到的日志內(nèi)容依次添加到Slave端的relay-log文件的最末端，并將讀取到的Master端的 bin-log的文件名和位置記錄到master-info文件