mysql 优化 读写分离 主从复制

1：mysql所在服务器内核 优化 　　---------此优化可由系统运维人员完成

2：mysql配置参数优化（my.cnf）　--------　此优化需进行压力测试来进行参数调整

3：sql语句及表优化

　　①为查询缓存优化你的查询   有数据库实现   当某字段查询过多时候 mysql进行 缓存处理   

　　②explain sql    分析sql 语句 是否 通过主键  或者缓存 

　　③若返回结果只需要一条时候  使用limit 1   ，原理  短路原理  当mysql 发现你要的数据时 就不会继续的对比其他数据是否吻合

　　④建立索引   索引分 普通索引（Normal） --》普通字段使用    唯一索引 （Unique） ---》主键字段使用    全文检索（Full Text）--》text类型字段使用

　　　　误区 模糊查询走索引吗？   当被查询的字段 有索引 并且 参数 左边为固定值右边模糊值 此次查询将通过索引   示例 select * form user where username like "zhang%"

　　⑤join 查询时候  表与表之间关联字段 要求同一类型   若为varcher类型 则字符集要求也一致    select * from a left join b where a.id=b.id    ID都为索引 mysql会为join进行生sql优化 分布式数据库不建议使用

　　⑥ 千万不要 ORDER BY RAND()  随机选取数据返回  如有业务需求  则在业务处理层 进行随机数   

　　⑦mysql中 select *少用　　　应该需要什么就取什么字段数据　全部就加上全表字段

　　⑧每张表　一定创建　主键　　ｉｎｔ类型主键　优于varcher类型　　

　　⑨某些字段使用enum类型　mysql中enum类型采用tinyint类型　　如“性别”　，状态等　　而没有必要去使用varchar存储

　　⑩尽量保证　字段为ｎｏｔ　ｎｕｌｌ　　官方不建议使用

　　１１。让表字段固定长度　　当表中字段有“varchar”，“ｔｅｘｔ”　“ｂｌｏｂ”　时这张表就不是固定长度　，固定长度　mysql写入数据是按照偏移量进行操作　所以会更快

　　１２。垂直分割表　当一张表　中的字段超过２０个　建议分表　存放

　　１３。delete　与　insert　操作　会进行锁表　　批量型操作　可以放在访问量较小的情况下进行

　　１４　磁盘性能是程序　最大的性能瓶颈　　字段内的值越小　速度越快

　　１５　存储引擎　　

MyISAM是MySQL的默认存储引擎（5.5以前），基于传统的ISAM类型，支持B-Tree，全文索引，但不是事务安全的，而且不支持外键。不具有原子性。支持表锁。

　　　　MyISAM适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 　　　　SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB是事务型引擎，支持ACID事务（实现4种事务隔离级别）、回滚、崩溃恢复能力、行锁。以及提供与Oracle类型一致的不加锁读取方式。InnoDB存储它的表和索引在一个表空间中，表空间可以包含数个文件。

　　　　InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务

 

　　１６　永久链接　连接池　慎重设置　

 

－－－－－－－－－主从复制 与读写分离－－－－－－－－－－－－－－－－

实现原理  mysql 主表在进行更新操作时候  会把更新的内容 以二进制的方式 写入log中 从表中log中读取数据并更新 避免了数据更新锁表的状态 提高并发负载能力

注意嵌套sql语句 容易引发错误 

-----------虚拟机----------搭建服务器demo----------------------

Ip，192.168.1.8 为 mysql-proxy 服务器   192.168.1.6为mysql主数据库 进行写入操作   192.168.1.7 为从数据库 进行查询操作

在线安装 mysql   命令 yum  -y install mysql-server

启动数据库服务 service mysqld  start

设置mysql 登录密码  mysqladmin  -u  root password 'root'

登录mysql    mysql -u root -p 输入密码 后进入

设置外网IP登录  use mysql;  使用系统配置表

insert into user(host,user,password) values('192.168.1.5' ,'zhangsan','123456')

--->flush privileges  刷新数据库权限 

利用通配符实现任意外网机器访问

update  user set host='%'       进行权限刷新后就可以外部访问了

测试 外部访问  mysql -u root -p -h192.168.1.6  

设置 192.168.1.6为主数据库    分别编辑数据库配置文件  vi /etc/my.cnf

在首行 插入server-id=1   logb-bin=mysql-bin  并保存退出  重启mysql 

再次登录mysql  进行状态查询，出现我们配置的server-id  说明配置成功

SHOW VARIABLES LIKE 'server_id'; 

查看 master数据的状态 show master status;

接下来 配置从服务器  192.168.1.7

修改

vi /etc/my.cnf

在首行 插入server-id=2   logb-bin=mysql-bin  并保存退出  重启mysql  再次查询

配置主从依赖 再 从服务器上输入 

change master to 
master_host='192.168.1.6',　　　　#表示主服务器地址
master_user='root',
master_password='root',
master_log_file='mysql-bin.000001',　　#日志文件名
master_log_pos=106;　　　　　　　　　　#日志路径

---启动从服务器的同步线程 mysql> start slave;并查看状态 mysql> show slave status\G

当Slave_IO_Runningmysql与Slave_SQL_Running 的值都为YES，表示状态正常

然后在主数据库进行测试  顺利配置完成

注意事项  1，server-id 以及 log-file的文件位置 2，防火墙 端口号3306  3，mysql的外部机器访问权限

-------------------配置代理 服务器 192.168.1.8---------------------------------------

检测运行库文件是否齐全  yum -y install gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig*libevent* glib* readline* 

若文件不齐则自动在线安装   安装完成后 再此执行  出现 nothing to do  表示文件安装完毕

检查linux 系统内核版本

去mysql官方网站下载对应的 mysql-proxy 安装文件 mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz

版本 不对应 容易引发 rpm 管理混乱  造成意外错误

将文件安装 到 /opt/目录下   安装后名称过长  进行改名为 mysql-proxy   查看mysql-proxy的目录结构

 查找mysql-proxy读写分离的脚本文件

 

将此文件 复制一份 到 自定义目录下  作为我们操作的脚本  这里 我放在了 /mysql-proxy/scripts文件夹

---mysql的帮助文档查看

修改rw-spliting.lua的默认配置   这里测试改成1

创建mysql-proxy 的配置文件

[mysql-proxy]
user=root 　　　　　　　　　　　　　#运行mysql-proxy用户
admin-username=lin3615 　　　  　#主从mysql共有的用户
admin-password=123456 　　　　　　#用户的密码
proxy-address=192.168.1.8:4040 #mysql-proxy运行ip和端口，不加端口，默认4040
proxy-read-only-backend-addresses=192.168.1.7 #指定后端从slave读取数据
proxy-backend-addresses=192.168.1.6 #指定后端主master写入数据
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定读写分离配置文件位置
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua #指定管理脚本
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log #日志位置
log-level=info #定义log日志级别，由高到低分别有(error|warning|info|message|debug)
daemon=true    #以守护进程方式运行
keepalive=true #mysql-proxy崩溃时，尝试重启

 

修改 文件权限  chmod 660 /etc/mysql-porxy.cnf

启动mysql-proxy --》[root@192 bin]# ./mysql-proxy --defaults-file=/opt/mysql-proxy/bin/mysql-proxy.conf

查看日志

启动时候由于会占用编辑界面 此时 再次启动会报错

 

netstat -tupln |grep mysql    查看mysql 占用的网络端口

为了验证是否配置成功  关闭从服务器 slave    从服务器中数据没有被更新

疑问？？ 关闭了同步   insert 的数据 没有 进入 从数据库中  但是通过 mysql-proxy 代理服务器 查询  确能查询到 新增数据 ？ 

推测 代理服务器有验证 消息同步  如果没有同步 则会从主数据库查询