Mysql数据库的优化技术：对mysql优化时一个综合性的技术，主要包括
1、 表的设计合理化(符合3NF)
2、 添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]
3、 分表技术(水平分割、垂直分割)
4、 读写分离
5、 存储过程[模块化编程，可以提高速度]
6、 对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]
7、 mysql服务器硬件升级
8、 定时的去清除不需要的数据,定时进行碎片整理(MyISAM)
以MySQL5.5以上版本为例！
以下参数是我手打的，不可复制粘贴！！因为参数可能存在拼写错误！
仅供参考！！！
1、新参数：
（1）充分利用CPU多核的处理能力：
Innodb_read_io_threads =
Innodb_write_io_threads =
根据读写性能惊喜分配，两个参数值相加等于逻辑CPU总数。
允许值为1~64，不支持动态更改，需要把该参数加入my.cnf里。
（2）刷新脏页数量和合并插入数量：
Innodb_io_capacity =
根据硬盘性能修改，支持动态修改。
硬盘类型 | 值 |
单盘SAS/SATA | 200 |
SAS*12RAID 10 | 2000 |
SSD | 5000 |
Fusion_IO | 50，000 |
（3）自适应刷新脏页：
Innodb_adaptive_flushing =
默认开启，可动态更新。
（4）缓冲池：
Innodb_buffer_pool
1.sublist of new blocks 热数据
2.sublist of old blocks 冷数据
硬盘 ---read---> old ---移动---> new
控制冷数据的数量。
Innodb_old_blocks_pct =
当扫描大表时：old~pct <= 5
当扫描小表时：new~pct >= 37
控制数据停留在热数据区域的时间（微秒）。
Innodb_old_blocks_time =
查看配置状态：
Show engine innodb status\G;
扫描全表时non-young s/s小，则应该增大old~time。
不扫描全表时 young s/s 小，则应该增大old~pct 或减少old~time。
（5）删除缓冲和清除缓冲：
Innodb_change_buffering = all （默认为all）
（6）清除程序进度：
Innodb_purge_threads = 0 （默认为0）
0为不使用单独线程，1为使用单独线程。
不支持动态修改，使用默认即可。
（7）使用多个buffer pool 实例：
Innodb_buffer_pool_size =
数值设得越高，read时i/o就越少。
专用数据服务器上该值设置为：物理内存的80%。
Buffer_pool缓冲池复制管理着：
free list（初始化空闲页）
Flush list （缓冲池产生的脏页）
LRU （内存中不用数据）
Innodb_buffer_pool_instances = 1~64
管理buffer_pool实例个数。
当innodb_buffer_pool_size > 1G 时，buffer_pool 多实例才有效。
（8）自适应哈希索引：
哈希索引是查询最快的索引。
高并发情况下，启用哈希会造成RW-latch争用，进而堵塞进程。
查询innodb状态发现很多waits，应该关闭哈希。
哈希的比例为50%时，不用关闭。
（9）内存分配程序：
Innodb_use_sys_malloc = (默认为1)
可使用TCMalloc工具，在高并发情况下提高性能，降低负载。
（10）默认innodb线程并发数：
（11）预读算法：
线性预读 -------|
|----->提高I/O
随机预读 -------|
Innodb_read_ahead_threshold = （默认=56）
当查看InnoDB状态时：
Evicted without access 很多时，则增大该参数值。
（12）异步I/O：
Innodb_use_native_aio =
利用libaio库可使用该参数启动异步I/O，默认开启。
用cat /proc/slabinfo | grep kio 查看异步I/O是否正常工作。
Kiocb != 0 则工作。
（13）组提交：
组提交工作模式只能在sync_binlog = 0 且 innodb_support_xa = 0的情况下工作。
目的是保证redo log 事务日志与bin log 日志的顺序一致。
（14）多个回滚段：
使用多个回旋段来提升性能和扩展性。
极大增加并发事务处理数量。
（15）自旋锁spin lock 软训间隔：
自旋锁：保护共享资源而提出的一种锁机制。
Innodb_spin_wait_delay = (默认为6)
控制轮训间隔，防止自旋锁循环过快。
（16）创建压缩数据页：
数据页的压缩数据文件体积变小，减少I/O，提升吞吐量，提高CPU利用率。
对读多写少的应用来说最为有效。
必须采用Barracuda文件格式且独立表空间。
（17）InnoDB更新元数据的统计功能：
Innodb_stats_on_metadata = （默认ON，推荐关闭）
（18）中继日志relay-log：
Relay_log_recovery = (默认为0 ，推荐1)
（19）Innodb严格模式：
Innodb_strict_mode = (默认为OFF，推荐1/ON)
采用了数据压缩功能后，最好是开启该功能。
（20）更改独立表空间：
Innodb_file_per_table = (默认OFF，推荐1)
（21）更改innodb锁超时时间：
Innodb_lock_wait_time = (默认50)
2、半同步复制
异步复制 -----------> 最佳性能
半同步复制 --------> 数据完整、一致性，增删改速度慢。
半同步复制模式必须在主从同时开启。
waster参数：
Rpl_semi_sync_master_enabled = ON (开启)
Rpl_semi_sync_master_timeout = 10 000
Rpl_semi_master_wait_no_slave = ON
Rpl_semi_sync_master_trace_level = 32
slave参数：
Rpl_semi_sync_slave_enabled = ON
Rpl_semi_sync_slave_trace_level =32
在主或从上查看复制状态：show status like “%semi%”;
从服务器开启半同步：start slave;
从服务器停止半同步：stop slave io_thread;
提交事务：commit;
开启事务：begin;
3、故障诊断
影响MySQL InnoDB引擎性能最主要因素 ： I/O
性能分析工具：vmstat、sar、iosat、netstat、free、ps、top、mps sat、dstat、collectl、Tsar。
最常用RAID：RAID 10
性能指标：
CPU性能指标：%us（应用程序）、%sy（内核）、%wa（最佳）、%id（空闲）
内存性能指标：swap（交换空间）
硬盘性能指标：I/O等待、队列平均长度（2-3最佳）、平均等待、每秒传输、每秒读写块/字节、
推荐工具：dstat
功能：
-c, –cpu 显示CPU情况
-C 0,3,total include cpu0,cpu3 and total
-d, –disk 显示磁盘情况
-D total,hda include hda andtotal
-g, –page enable page stats
-i, –int enable interrupt stats
-I 5,eth2 include int5 and interrupt used by eth2
-l, –load enable load stats
-m, –mem 显示内存情况
-n, –net 显示网络情况
-N eth1,total 可以指定网络接口
-p, –proc enable process stats
-s, –swap 显示swap情况
-S swap1,total 可以指定多个swap
-t, –time enable time counter
-y, –sys enable system stats
–ipc 报告IPC消息队列和信号量的使用情况
–lock enable lock stats
–raw enable raw stats
–tcp enable tcp stats
–udp enable udp stats
–unix enable unix stats
-M stat1,stat2 enable external stats
–mods stat1,stat2
-a, –all 使用-cdngy 缺省的就是这样显示
-f, –full 使用 -C, -D, -I, -N and -S 显示
-v, –vmstat 使用-pmgdsc -D 显示
–integer show integer values
–nocolor disable colors (implies –noupdate)
–noheaders 只显示一次表头以后就不显示了,使用重定向写入文件时很有用
–noupdate disable intermediate updates
–output file 写入到CVS文件中
dstat -cdlmnpsy 在1024×768的屏幕上正好全部显示出来
高压情况下、重启完MySQL、手工加载热数据到缓冲池预热。
V5.5：
Select count(*) from user;
Select count(*) from Buddy;
Select count(*) from password;
V5.6:
Innodb_buffer_pool_dump_at_shutdown = 1
Innodb_buffer_pool_dump_now = 1
Innodb_buffer_pool_load_at_startup = 1
Innodb_buffer_pool_load_now_now = 1
主库子查询引起宕机：
解决办法：把子查询改为表链接（join）方式。
二进制日志有两个作用：1.恢复数据 2.实现主从复制
配置文件： log-bin
主机名/mysql.bin.001：
保存用户对数据库内容和结构的修改情况，不会记录select。
主机名/mysql.bin.index：
文件中包含一份全体日志文件清单
找出最频繁的表：
通过bin-log日志来分析业务增长量，哪个表操作频繁。
谨慎设置：Binlog_format = STATEMENT、ROW、MIXED
STATEMENT：记录sql语句
ROW：记录实际更变
MIXED：除了6种情况外，其余都是STATEMENT格式。
1.使用UUID()、2.用户函数、3.insert，delayed语句、4.临时表、5.自增字段更新6.使用用户定义函数。
如果用ROW造成日志量大、则设置binlog_row_image = minimal加以解决。
恢复slave上的某几张表：
a1、b1、c1、三张表数据根master上的不一样。
恢复方法:
1.停止slave复制：
>stop slave；
2.从master导出了三张表和binlog的POS点：
#MySQLdump -uroot -p密码 --single-transaction --master-data=2yourdb a1 b1 c1 > ./a1_b1_c1.sql
3.查看a1_b1_c1.sql,找出binlog和POS点：
#more a1_b1_c1.sql
Master_LOG_FILE=’MySQL-bin.xxxx’,master_LOG_POS=xxxxx
4.把a1_b1_c1.sql复制到slave上，做chenge master to指向：
>start slave until master_LOG_FILE=’xxxx’,master_LOG_POS=xxxx;
5.同步报错直接跳过：
stop slave;set global sql_slave_skip_counter=1;
start slave;
6.在slave上导入a1_b1_c1.sql：
#MySQL -uroot -p密码 yourdb < ./a1_b1_c1.sql
7.导入完成，开启同步：
>start slave;
彻底清除从库(slave)数据（V >= 5.5.20）：
>reset slave all;
>show slave status;
4、InnoDB与MyISAM对比
InnoDB是事务型引擎：支持回滚、具有崩溃恢复、多版本并发控制、
工作原理：把数据提取到内存中，减少I/O。
应用：使用在需要执行大量读写操作的服务器上，如电子商城、等等。
MyISAM：管理方便，效率优于InnoDB。
应用：小型应用服务器、需要大量读性能的服务器。
事务隔离级别：
5、慢日志
在my.cnf中加入：
Slow_query_log = 1
Slow_query_log_file = mysql.slow
Long_query_time = 2 （超过两秒的记录）
截取一段时间的慢日志：
Sed -n ‘/# Time: YYMMDD HH:mm:SS/,/end/p’mysqlslow > slow.log
用mysqldumpslow取出耗时最长的sql语句：
Mysqldumpslow -s -t -tlo slow.log
尽量避免使用having，而使用where替换之。
显示慢查询次数：
Show status like ‘connection’;
show globalstatus like '%slow%';
慢查询日志实例：
6、my.cnf配置文件调优
（1）per_thread_buffers 优化：
为每个接到MySQL的用户进程分配内存。
参数：
1.每个线程分配的缓冲区大小，用于表的顺序扫描。
read_buffer_size = 128-256 KB
2.每个线程分配的缓冲区大小，用于随机读取。
read_rnd_buffer_size = 128-256 KB
3.增加每个线程分配的缓冲区大小，用于order 、group时。
sort_buffer_size = 128-256 KB
4.每个线程的堆栈大小。
thread_stack = 256 KB
5.增加每个线程分配的缓冲区大小，用于join连接操作时。
join_buffer_size = 128-256 KB
6.无大事务情况下1-2MB即可。
binlog_cache_size = 1-2 MB
7.最大连接数。
max_connections = 512-1000
（2）global_buffers 优化：
用于在内存中缓存从数据文件中检索出来的数据块。
可提高查询和更新的性能。
参数：
1.innodb_buffer_pool_size = 内存的60%~70%
2.存储字典信息和其他内部数据结构，表越大分配越多。
innodb_additional_mem_pool_size = 16 MB
3.事务日志使用的缓冲区。
innodb_log_buffer_size = 16-24 MB
4.缓存MyISAM引擎索引。
key_buffer_size = 64 MB
5.缓存查询语句和结果集，写少 = 1，写多 = 0 。
query_cache_size =
注意：（1）+（2）=的值 < 物理内存，否则高并发情况下造成内存溢出。
7、MySQL5.6同步复制的新特性
同步复制是自动的，mysql会通过内部机制GTID自动找点同步。
可以做到多个库之间的多线程复制。
GTID：全局事务标识符，由UUID+事务ID组成。
开启GTID时，slave做同步复制时，无须POS点，直接chenge master to master_auto_posittion = 1 即可。
使用GTID就不能使用binlog和pos方式。
8、备份与恢复：
备份方式：冷备份、热备份、逻辑备份
冷备份：数据库处于关闭状态，保证数据库的完整性。
热备份：数据库处于运行状态，依赖于数据库日志文件。
逻辑备份：使用命令从数据库中提取数据，写到一个文件上，内容为纯文本的sql语句。
（1）冷备份
冷备份用于非核心业务，业务一般允许中断，速度快，回复简单，直接复制物理文件。
备份和恢复步骤：
关闭服务进程-->复制data和日志目录-->复制到另一块硬盘里-->用复制目录替换原有目录-->启动服务
（2）逻辑备份
逻辑备份用于数据量很小时，数据导出。
备份和恢复步骤：命令（备份）-->命令（恢复）
（恢复命令1.MySQL -uroot -p <all.Sql 2.>source all.sql）
新工具：mydumper（高性能，多线程）
（3）热备份
不关闭服务进行复制数据物理文件。
工具：xtrabackup（1.xtrabackup 2.innobackupex(更完善)）
Innobackupex备份参数：innobackupex [options]
参数 | 介绍 |
--apply-log | 准备一个备份在BACKUP-DIR通过事务日志文件名为“xtrabackup_logfile”位于同一个目录中。此外,创建新的事务日志。InnoDB配置从文件读取“backup-my.cnf”。 |
--compress | 这个选项指示xtrabackup压缩InnoDB数据文件的备份副本。它是直接传递到xtrabackup子进程。尝试xtrabackup——帮助的更多细节。 |
--compress-threads | 这个选项指定的工作线程数量将用于并行压缩。它是直接传递到xtrabackup子进程。尝试xtrabackup——帮助的更多细节。 |
--copy-back | 复制所有文件在以前备份的备份目录到原来的位置。 |
--databases=LIST | 这个选项指定的数据库列表innobackupex应该备份。接受一个字符串参数的选项。“databasename1列表的形式。table_name1]databasename2[。table_name2]。”。如果没有指定这个选项,所有数据库包含MyISAM和InnoDB表将备份。请确保,InnoDB数据库和表的数据库包含了所有,这所有的InnoDB。纳也备份文件。名单很长,这可以在一个文件中指定,可以指定文件的完整路径的列表。(见选项-文件。) |
--defaults-file=[MY.CNF] | 这个选项指定文件读MySQL的默认选项。接受一个字符串参数的选项。这也是直接传递给xtrabackup——defaults-file选项。有关详细信息,请参阅xtrabackup文档。 |
--export | 这个选项是直接传递给xtrabackup——导出选项。它使出口个人表导入到另一个服务器。有关详细信息,请参阅xtrabackup文档。 |
--extra-lsndir=DIRECTORY | 这个选项指定的目录保存一个额外的“xtrabackup_checkpoints”文件的副本。接受一个字符串参数的选项。它是直接传递给xtrabackup——extra-lsndir选项。有关详细信息,请参阅xtrabackup文档。 |
--force-tar | 这个选项部队使用tar创建流时备份,而不是tar4ibd,这是缺省设置。 |
--help | 这个选项显示帮助屏幕并退出。 |
--host=HOST | 此选项指定了主机使用时使用TCP / IP连接到数据库服务器。接受一个字符串参数的选项。它是传递到mysql子进程没有变更。有关详细信息,请参阅mysql——help。 |
--ibbackup=IBBACKUP-BINARY | 这个选项指定应该使用哪个xtrabackup二进制。接受一个字符串参数的选项。IBBACKUP-BINARY应该XtraBackup运行使用的命令。选项可以有用如果xtrabackup二进制不在你的搜索路径或工作目录。如果没有指定这个选项,自动innobackupex试图确定使用二进制。默认情况下,“xtrabackup”使用的命令。然而,当指定选项——复制回来,“xtrabackup_51”使用的命令。当指定选项——运用原木,使用二进制文件中,他的名字叫“xtrabackup_binary”备份目录,如果文件存在。 |
--include=REGEXP | 这个选项是一个正则表达式匹配表名在数据库名。表的格式。它是直接传递给xtrabackup——表选项。有关详细信息,请参阅xtrabackup文档。 |
--incremental | 这个选项告诉xtrabackup创建一个增量备份,而不是一个满的。这是传递到xtrabackup子进程。当指定这个选项,要么——incremental-lsn或incremental-basedir也可以。如果没有选项,选项——incremental-basedir传递给xtrabackup默认情况下,设置为第一个时间戳备份目录备份基础目录。 |
--incremental-basedir=DIRECTORY | 这个选项指定包含完整备份的目录增量备份的基础数据集。接受一个字符串参数的选项。它是使用——增量选项。 |
--incremental-dir=DIRECTORY | 这个选项指定增量备份的目录将结合新建一个完整备份的完整备份。接受一个字符串参数的选项。它是使用——增量选项。 |
--incremental-lsn | 这个选项指定日志序列号(LSN)使用增量备份。接受一个字符串参数的选项。它是使用——增量选项。它是用来代替指定——incremental-basedir。创建的数据库MySQL和Percona服务器5.0系列版本,指定两个32位整数的LSN高:低格式。5.1中创建数据库和后,指定LSN作为一个64位整数。 |
--no-lock | 使用这个选项来禁用表锁定“读锁平表”。InnoDB使用它只有在你所有的表,你不关心的二进制日志备份的位置。 |
--no-timestamp | 这个选项可以防止创建一个带时间戳的子目录BACKUP-ROOT-DIR给定的命令行。指定时,备份在BACKUP-ROOT-DIR代替。 |
--parallel=NUMBER-OF-THREADS | 此选项指定了xtrabackup子进程线程的数量应该使用并发备份文件。接受一个整数参数的选项。它是直接传递给xtrabackup——平行选项。有关详细信息,请参阅xtrabackup文档。 |
--password=WORD | 这个选项指定连接到数据库时使用的密码。它接受一个字符串参数。它是传递到mysql子进程没有变更。有关详细信息,请参阅mysql——help。 |
--port=PORT | 这个选项指定要使用的端口时使用TCP / IP连接到数据库服务器。接受一个字符串参数的选项。这是传递到mysql子进程。它是传递到mysql子进程没有变更。有关详细信息,请参阅mysql——help。 |
--redo-only | 这个选项是直接传递给xtrabackup——apply-log-only选项。这就迫使xtrabackup跳过“回滚”阶段,只做一个“重做”。这是必要的,如果备份将增量更改应用到后来。有关详细信息,请参阅xtrabackup文档。 |
--remote-host=HOSTNAME | 不建议使用这个选项,将被删除在Percona XtraBackup 2.1。在Percona XtraBackup 2.0和以后,您应该使用流式备份。这个选项指定的远程主机备份文件将被创建,通过使用ssh连接。接受一个字符串参数的选项。 |
--safe-slave-backup | 停止奴隶SQL线程,等到开始备份Slave_open_temp_tables在“显示状态”是零。如果没有打开临时表,备份将,否则SQL线程将启动和停止,直到没有开放的临时表。备份将会失败如果Slave_open_temp_tables不成为零——safe-slave-backup-timeout秒之后。奴隶SQL线程将备份完成时重新启动。 |
--safe-slave-backup-timeout | 多少秒——safe-slave-backup应该等待Slave_open_temp_tables变成零。300(默认) |
--scpopt=SCP-OPTIONS | 这个选项指定的命令行选项通过scp选项——remost-host时指定。接受一个字符串参数的选项。如果没有指定的选项,默认选项“- cp - c arcfour”。 |
--sshopt=SSH-OPTIONS | 这个选项指定的命令行选项通过ssh选项——remost-host时指定。接受一个字符串参数的选项。 |
--slave-info | 这个选项是有用的时候从服务器备份复制。它输出的二进制日志位置和主服务器的名称。它还将这些信息写入“xtrabackup_slave_info”文件“修改主”命令。新奴隶这个主人可以开始建立的一个从属服务器备份和发行的“修改主”命令二进制日志位置保存在“xtrabackup_slave_info”文件。 |
--socket=SOCKET | 这个选项指定套接字连接到本地数据库服务器时使用的UNIX域套接字。接受一个字符串参数的选项。它是传递到mysql子进程没有变更。有关详细信息,请参阅mysql——help。 |
--stream=[tar|. . .] | 这个选项指定流的格式来做备份。接受一个字符串参数的选项。备份将STDOUT在指定的格式完成。目前,唯一支持的格式是焦油和xbstream。这个选项是直接传递给xtrabackup——流选项。 |
--tables-file=FILE | 此选项指定了文件,其中有一个数据库表单的名称列表。接受一个字符串参数的选项。表,每行一个。选择是直接传递给xtrabackup——文件选项。 |
--throttle=IOS | 此选项指定了一个I / O操作的数量(对阅读+写作)每秒。它接受一个整数参数。它是直接传递给xtrabackup——节流选项。 |
--tmpdir=DIRECTORY | 此选项指定了一个临时文件将存储的位置。接受一个字符串参数的选项。时应该使用——远程主机或指定。对于这些选项,事务日志将首先被存储到一个临时文件中,在流媒体或复制到远程主机。这个选项指定临时文件将存储的位置。如果没有specifed选项,默认的值是使用tmpdir从服务器读取配置。 |
--use-memory=B | 这个选项接受一个字符串参数,用于指定的字节的内存数量xtrabackup用于崩溃恢复,同时准备一个备份。倍数提供支持单位(例如1 mb,1 gb)。它只使用的选项——运用原木。它是直接传递给xtrabackup——使用内存选项。有关详细信息,请参阅xtrabackup文档。 |
--user=NAME | 这个选项指定使用的MySQL用户名连接到服务器时,如果这不是当前用户。接受一个字符串参数的选项。它是传递到mysql子进程没有变更。有关详细信息,请参阅mysql——help。 |
--version | 这个选项显示了xtrabackup版本和版权声明,然后退出。 |
全量恢复：停止数据库-->删除老数据和日志-->恢复日志-->恢复数据-->修改权限-->启动数据库
（可以备份到远程服务器）
（恢复解压时用 -i 参数：tar -ixvf 文件名）
增量备份：先全量备份-->增量备份-->进入备份目录-->查看哪份是全量，哪份是增量
增量恢复：恢复增量事务日志-->恢复全量日志-->恢复数据-->修改权限-->启动数据库
9、批量管理服务器
开源工具：Pssh
先写一个批量部署ssh私钥认证的脚本。Batch_sshkey.sh
脚本会调用ip.txt文件作为ssh私钥认证。
Ip.txt格式：
Ip：密码
Ip：密码
.........
再写一个吧认证文件复制到客户端里的脚本。Remote_operate.sh
#！/bin/bash
If [ ! -d /root/.ssh ];then
Mkdir /root/.ssh
if
Cp /tmp/authorized_keys /root/.ssh/
先执行batch_sshkey.sh 在执行remote_operate.sh
Pssh安装：#yum -y install *python*
#tar zxvf pssh-*
#cd pssh-*
#python setup.py install
批量执行命令：pssh -h other.txt -l root -i 命令
10、性能监控
Cacti：基于php、Mysql、snmp的网络流量监测图像分析工具，通过snmpget来获取数据。（www.cacti.net）
基于lamp环境。
需要安装：gcc*、apache、php-plugin、mysql、php、snmp、rrdtool、cacti、percona-mysql-monitor插件、
详情：
11、服务监控
Nagios：开源的免费网络监视工具、基于lamp环境
监控端安装：nagios-*
Nagios-plugins-*
Nrpe-*
被监控端安装：nagios-plugins-*
Nrpr-*
详情：
12、MySQL监控脚本
13、高可用集群管理
高可用集群：减少服务中断时间为目的的服务器集群技术。
High availability cluster（HA cluster）
高可用集群软件：实现故障检查和业务切换自动化。
双机热备：只有两个节点的高可用集群。
心跳检测：
服务器之间定期进行相互的健康状态检查。
通过网络进行。
私网心跳和作为备份方式的公网心跳探测。
4种流行架构：
1.keepalived + mysql Replication
2.MMM + mysql Replication
3.Heartbeat + DRBD + mysql
4.RHCS高可用集群套件
