Mysql学习笔记(二)

一.如何实现mysql主从读写分离
1.通过程序实现读写分离(性能，效率最佳，推荐)
php和java都可以通过设置多个连接文件轻松的实现对数据库的读写分离，即当select时，就去
连接读库的连接文件，当update,insert,delete时就连接写库的连接文件。
2.通过软件实现读写分离
Mysql-proxy,Amoeba等代理软件也可以实现读写分离功能，但最常用好用的还是程序实现读写分离
3.开发dbproxy

二.mysql主从同步原理要点:

1.异步方式同步
2.逻辑同步模式，可采取多种模式进行同步，默认是通过SQL语句执行
3.主库通过记录binlog实现对从库的同步
4.主库一个线程，从库2个线程完成
5.从库关键文件master.info,relay-bin,relay-log
6.从库还想级联从库，要打开log_bin和log-slave-update参数

三.binlog的三种模式

1.statement Level模式
  每一条修改数据的sql语句都会记录到bin-log日志中，slave复制时执行相同的sql语句进行复制
  优点:减少bin-log日志量，节约IO，提高性能，延迟小。
  缺点:它记录的事执行语句，为了让这些语句在slave端正确执行，必须记录每条语句的一些上下文信息；在某些
  情况下，会导致函数和触发器同步出线问题
2.Row Level模式:日志中会以修改每一行数据的形式记录，然后再slave端再对相同的数据进行修改
  优点:日志内容非常清楚地记录下每一行数据修改的细节，不会出现某些特定情况下存储过程，函数，以及
trigger的调用和触发无法被正确复制的问题。
  缺点:记录的日志内容非常多，日志量非常大
3.Mixed模式
  它是前两种模式的结合，在Mixed模式下，Mysql会根据执行的每一条具体sql语句区分对待记录的日志形式

解析row格式的binlog日志:mysqlbinlog --base64-output=DECODE-ROWS -v mysql-bin.000007

事务:事务就是指逻辑上的一组SQL语句操作，组成这组操作的各个SQL语句要么全成功要么全失败

四.事务的四大特性

1.原子性(Atomicity)
  事务是一个不可分割的单位，事务中的所有SQL操作要么全发生，要么全都不发生
2.一致性(Consistency)
  事务发生前和发生后，数据的完整性必须保持一致
3.隔离性(Isolation)
  当并发访问数据库时，一个正在执行的事务对于其他的对话是不可见的，多个并发事务之间的数据时相互隔离的
4.持久性(Durability)
  一个事务一旦被提交，它对数据库的数据改变时永久性的。如果出了错误，事务也不允许撤销，只能通过"补偿性事务"

mysql数据库默认是开启事务的:
start transaction 开启事务 mysql5.1
begin; 开启事务 mysql5.5
rollback  回滚事务
commit  提交事务

五.MyISAM引擎特点

1.不支持事务
2.表级别锁定，打打降低了并发性能
3.读写互相堵塞:不仅会在写入的时候堵塞读取，MyISAM会在读取的时候堵塞写入
4.只会缓存索引:MyISAM可以通过key_buffer_size缓存索引，打打提高访问性能，但不能缓存数据
5.读取速度较快，占用资源相对少
6.不支持外键约束，相对粗颗粒

MyISAM生产场景:
  1.不需要事务支持的业务
  2.一般是读数据比较多的应用，读写都频繁的场景不适合
  3.读写并发访问相对较低的业务
  4.数据修改相对较少的业务(堵塞问题)
  5.中小型的网站部分业务会用

MyISAM引擎调优:
  1.设置合适的索引
  2.调整读写优先级
  3.启用延迟插入改善大批量写入性能(降低写入频率，尽可能多条数据一次性写入)
  4.尽量顺序操作让insert数据都写入到尾部，减少堵塞
  5.分解大的时间长的操作，降低单个操作的堵塞时间
  6.降低并发数，某些高并发场景通过应用进行排队队列机制
  7.充分利用缓存
 
六.InnoDB引擎特点:

 1. 支持事务:支持4个事务隔离级别
 2.行级锁定:通过索引实现，全表扫描仍然会是表锁
 3.读写堵塞与事务隔离级别相关
 4.具有高效的缓存特性:缓存索引和数据
 5.整个表和主键以cluster方式存储，组成一颗平衡树
 6.所有Secondary Index都会保存主键信息
 7.支持分区，表空间，类似Oracle数据库
 8.支持外键约束

InnoDB引擎适用的生产业务场景
 1.需要事务支持的业务
 2.行级锁对高并发有很好的适应能力，但需要确保查询是通过索引完成
 3.读写数据及更新数据较为频繁的场景，如BBS,SNS,微博，微信等
 4.数据一致性要求较高的业务,如充值转账等金融业务

InnoDB引擎调优
 1.主键尽可能小
 2.避免全表扫描，因为会使用表锁
 3.尽可能缓存所有索引和数据，提高响应速度，减少磁盘IO消耗
 4.在大批量小插入的时候，尽量自己控制事务而不要使用自动提交
 5.合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性。
 6.避免主键更新，这会带来大量的数据移动

七.数据库慢的排查方法:

1.show full processlist; #找出导致慢的SQL语句
2.开启慢查询日志
long_query_time =  1
log-slow-queries = /data/3306/slow.log
找到慢查询语句，创建联合索引解决问题

查看负载
[root@A1-lamp ~]# uptime
 16:29:35 up 13:46,  4 users,  load average: 0.00, 0.19, 0.34

八.mysql优化企业案例

数据库中像 LIKE '%杜冷丁%' 这样的语句特别多，导致数据库负载特别高，
%杜冷丁%这样的双%%的SQL语句创建索引是不起作用的，因此对于数据库本身优化是不行的
优化方案思路:
1.从业务上实现用户登录后再搜索，这样减少搜索次数，从而减轻数据库服务的压力
2.如果有大量频繁的搜索，一般是由爬虫在爬你的网站，分析WEB日志封掉IP(AWSTATS)
3.配置多个主从同步，程序上实现读写分离(最好让LIKE‘%杜冷丁%’这样的查询去从库查，减轻主库读写压力)
4.在数据库前端加上memcached缓存服务器
5.LIKE'%杜冷丁%'的语句，可以通过搜索服务Sphinx实现搜索

6.可以利用c,ruby开发程序，实现每日读库计算搜索索引，保存在服务器上提供搜索，然后每5分钟做一次增量，

这是大公司针对站内搜索采取的比较好的方案

九.索引

索引的原理:索引时建立了针对数据内容的排序结果的指针。就像一本书的目录
索引的设计原则:
  1.索引列一般为where子句中的列或者连接子句中的列
  2.尽量不对重复值多的列做索引，如性别
  3.尽可能使用短索引:尽量对字符列索引尽量指定最小长度
   create index cityname on city(city(5));
  4.符合索引前缀特性，索引的顺序很重要
   key(a,b) ... where b=5 不会用索引
   key（a,b,c）
   可以做索引的组合:key(a),key(a,b),key(a,b,c)
   下列组合无法走索引:key(b),key(b,c),key(a,c),key(b),key(c)
   创建复合索引应将最常用做限制条件列放在最左边，依次递减
  
   5.避免出现无用索引(很少使用或者不用)
   6.InnoDB:尽量指定主键，最常用较短数据类型唯一列做主键
   7.尽量使用定长字符类型做索引,如char,不用varchar
 
索引是一把双刃剑，避免过度使用索引:
   1.索引的建立对提高检索能力很有用，但是数据库维护它很费资源
   2.对性别列索引
      只有两个值，建索引不仅没优势，还会影响插入、更新速度
   3.索引会占用磁盘空间，降低写操作。执行计划要考虑各个索引
   4.索引不是越多越好
   5.行数比较少的表可以不建立索引(100行以内)
创建索引的方法
1.create index index_name on table_name(列名)；
2.alter table table_name add index index_name(列名);

删除索引:
drop index index_name on table_name;

mysql索引优化步骤
1.查看负载很高uptime
2.找出慢查询语句
show full processlist;
3.看看查询语句的执行过程，是否有索引，
explain 慢查询语句

4.在查询列创建索引
create index index_name on table_name(列名);

5.查看优化后语句的执行效率
explain 慢查询语句

创建索引很消耗性能，因此不要在业务高峰期建立索引

十.mysql备份之mysqldump

利用explain查看sql语句的执行计划
防止误操作:alias mysql='mysql -U'
长期可追加到/etc/profile

mysqldump -uroot -phaha123 -d oldboy > oldboy.sql #备份表结构

-t 备份表数据
-A 备份所有数据库
备份所有数据库mysqldump -uroot -phaha123 -A -B --events|gzip >/opt/all.sql
-F 切割二进制日志
mysqldump -uroot -phaha123 -A -B -F --events |gzip > /opt/all.sql

mysqldump 的关键参数说明
1.-B 指定多个库，增加建库语句和切换库语句
2.--compact 去掉注释，适合调试输出，生产不用
3.-A 备注所有库
4.-F 刷新binlog日志
5.--master-data 增加binglog日志文件名及对应的位置点
6.-x,--lock-all-tables
7.-l --lock-tables 只读锁表
8.-d 只备份表结构
9.-t 只备份数据
10.--single-transaction 适合innodb事务数据库备份

for myisam：
mysqldump --user=root --all-databases --flush-privileges --lock-all-tables \
--master-data=1 --flush-logs --triggers --routines --events \
--hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql
for innodb：
mysqldump --user=root --all-databases --flush-privileges --single-transaction \
--master-data=1 --flush-logs --triggers --routines --events \
--hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql

myisam：mysqldump -uroot -phaha123 -A -B --master-data=1 -x |gzip > /opt/all.sql.gz
innodb mysqldump -uroot -phaha123 -A -B --master-data=1 --single-transaction|gzip > /opt/all.sql

system ls /root
命令前加system 可以执行shell命令
查看数据库参数和状态:
show processlist; #查看正在执行的完整的SQL语句，**查看慢查询语句的命令

show variables like ''; #查看数据库的参数信息

show global status; #查看整个数据库运行状态信息，很重要，要分析并做好监控

set global key_buffer_size =  32777218; 直接设置数据库参数
show status #查看当前会话的数据库状态信息
mysqlbinlog 解析mysqlbinlog日志
bin_log 日志就是记录mysql数据库增删改的操作

mysqlbinlog -d 数据库名 mysqlbinlog >/opt/test.sql
指定起始和结束点:mysqlbinlog mysqlbinlog-0001 --start-position=start_num --end-position=end_num -r test.sql
指定开始和结束时间：mysqlbinlog --start-datetime='2017-12-11 16:67:00' --stop-datetime='2017-12-11 23:00:00' -r test.sql