1、,Optimzing mysql,I feel the need.the need for speed.,壮志凌云（top gun）,性能监控 10分性能优化 20分常见的架构 10分Tools and tips 10分,性能监控,操作系统级别的Mysql级别,操作系统,vmstat iostatfreetopsarmpstatuptime,netstatstrace,mysql,Show processlist Mysqlreport http:/ -uxxx p -i 1 -r extendedstatus|grep -v “| 0”Innotop http:/ global status
2、,show inondb status show session status;Mytop ExplainProfilingMysqldumpslow mysqlslasysbench,vmstat,基本的物理和虚拟内存的使用和一些基本的系统统计信息Vmstat S M Vmstat 5 5Swap颠簸现象Top ps axl,iostat,度量磁盘i/o,cpu和设备io使用Iostat -c iostat dx 5%iowait并发请求的数量 并发=（r/s+w/s）*(svctm/1000)(从头到尾服务请求时间),Cpu密集型机器,Io密集型,Swap idle,谁可能导致高的cpu
3、usagequery ，joins,every进程切换，锁表内存排序临时表加密算法谁可能导致高的disk usage临时表硬盘上排序 start with an idea ,then look for infromation support it,netstat,服务链接状态 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|
4、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,分析服务器,MysqlreportMysqladmin mysqladmin extended -r -i 10| grep v “| 0 ” -uroot pInnotop .Show global statusShow
5、 innodb status,分析查询,Show processlistFlush statusShow 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/mysqldgrep 3306 /etc/servicesps -eaf| grep UID|109
6、40lsof -i -P| grep 10942,性能优化,操作系统 mysql应用程序,操作系统,不要交换区。如果内存不足，增加更多的内存或配置你的系统使用较少内存。 flick:echo 0 /proc/sys/vm/swappiness innodb_flush_method=O_DIRECT不要使用NFS磁盘(会有NFS锁定的问题)。 增加系统和MySQL服务器的打开文件数量。(在safe_mysqld脚本中加入ulimit -n #)。 增加系统的进程和线程数量。 选择使用哪种文件系统。在Linux上的Reiserfs对于打开、读写都非常快。文件检查只需几秒种。,Mysql配置,My
7、siam 缓存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_rea
8、d%Innodb_buffer_pool_read_requests/Innodb_buffer_pool_readsshow 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_
9、hits Qcache_inserts) /Qcache_hits * 100%http:/ 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_c
10、onnections * per_connection initial + (max_connections * per_connection)/3),初始化： 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_
11、connection initial + (max_connections * per_connection)/3),数据类型选择,更小通常更好,慷慨并不明智简单就好 使用mysql内建的类型保存日期和时间，使用timestamp 保存，空间是datetime一半 使用整数保存ip15 bytes for char(15) vs. 4 bytes for the integer ip2long() 和 long2ip() inet_aton3 尽量避免null4 Char /varcha的选择 对于MyISAM而言，如果没有VARCHAR，TEXT等变长类型，那么每行数据所占用的空间都是定长的
12、（Fixed），俗称静态表，相对应的就是动态表。当执行一次查询时，MySQL可以通过索引文件找到所需内容的实际行号，此时，由于每行数据所占用的空间都是定长的（Fixed），所以可以通过查询到的实际行号直接定位到数据文件的具体位置， 对于InnoDB而言，数据行是没有所谓定长与否的概念的，这是由其结构所决定的：在InnoDB中，数据就位于Primary Key的B-Tree叶节点之上而除Primary Key之外的索引被称为Secondary Index，它们直接指向Primary Key。 用char来代替varchar，MyISAM是这样，InnoDB则相反5 使用enum 代替字符串类型
13、select internet + 0 from hotel_info group by internet;,索引,隔离列select * from tablename where id+1=5Select * 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
14、%GEXPLAIN SELECT * FROM film WHERE LEFT(title,2) = Tr G组合索引 前缀索引覆盖索引合并索引去除多余索引和重复索引 create table test (id int not null primary key, unique(id), index(id) ),合并索引,索引合并方法用于通过range扫描搜索行并将结果合成一个。合并会产 生并集、交集或者正在进行的扫描的交集的并集。在EXPLAIN输出中，该方法表现为type列内的index_merge。 在这种情况下，key列包含一列使用的索引，key_len包含这些索引的最长的关键元素,SE
15、LECT * 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 cWhere a = const order by b,c where a = const and b const order by,cOrder by a asc,b des
16、c,c desc Where g = const oder by b ,cWhere a = const order by cWhere a = const order by a ,d Where axx order by b,cWhere axx order by a,bWhere 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 fro
17、m products where actor= sean carrey and title like %apollo%) as t1 on (t1.prod_id = products.pro_id),逆范式化,适当的冗余分拆表,产品设计,产品设计-数据交互-mysqlAntoine de SaintExupery是法国作家兼飞机设计师，他曾经说过：“设计者确定其设计已经达到了完美的标准不是不能再增加任何东西，而是不能再减少任何东 西。” 分页的实现,http:/ explain select SQL_NO_CACHE * from page_test force index(idx_b_c)
18、 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 |+-+-+-+-+-+-+-+-+-+-+2mysql explain select S
19、QL_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 | P
20、RIMARY | | 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(id
21、x_b_id) where b=1 and id 10; select sql_no_cache (select count(*) from statistic_go) - count(*) from statistic_go where id =0; 建立计数器触发器对同一个表的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能够快
22、速缩小结果集的WHERE条件写在前面，如果有恒量条件，也尽量放在前面使用 UNION 来取代 IN 和 OR 定期执行optimize / analyze table往innoDB表导入数据时，先关闭autocommit模式，否则会实时刷新数据到磁盘对于频繁更改的MyISAM表,应尽量避免更新所有变长字段(VARCHAR、BLOB和TEXT)分表 分库汇总表 十大热门话题 create table hotel_infonew like hotel_info; rename table hotel_info to hotel_info_old ,hotel_infonew to hotel_in
23、fo放弃关系型数据库 key=value ，计数表,常见架构方案,Replication双master集群。,replication,1 数据分发 ,scale out，sacle up2 负载均衡 load balance3 备份，一般不会用作备份，一旦执行delete操作，replication也不会保留4 高可用 5 可以在不同的主从库上使用不同的存储引擎,原理,简单的讲就是master记录其变化到binlog，slave接收到变化后会记录到他的Relay log，slave通过重放relay log，然后就写进自己的log1)、Slave上面的IO进程连接上Master，并请求从指定日
24、志文件的指定位置（或者从最开始的日志）之后的日志内容；2)、Master接收到来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave 的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置；3)、Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的高速Master“我
25、需要从某个bin-log的哪 个位置开始往后的日志内容，请发给我”；4)、Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行,常见问题,延迟主从不同步命令复制 基于行复制,高可用,通过Heartbert2 让Mysql Replication 具有HA,双master,工具,InnotopMaatkit mk-table-checksummk-table-sync mk-parallel-dump mk-parallel-restoremk-findOptimize table 脚本
26、,Tools and tips,h命令 大批量数据的导入 load data infile /path/to/file into table tbl_name; alter table tbl_name disable keys; alter table tbl_name enable keys; 快速复制表结构： create table clone_tbl select * from tbl_name limit 0Gdb更改mysql配置 show variables like log_slave_updates; set global log_slave_updates=1; system gdb -p $(pidof mysqld) -ex set opt_log_slave_updates=1 -batch gdb -p $(pidof mysqld) -ex set max_connections=5000 -ex call resize_thr_alarm(5030) -batchtruncate table 不能复制到从库perl -ne m/(#s=+)s*(=.*|)/ q hotel_new2010-3-1117.sql,同一个团队，同一个梦想 Thanks!,
