mysql优化
一、sql语句优化
定位执行效率低的sql语句:
1、通过慢查询日志, --log-slow-queries[=file_name]
2、由于慢查询日志只有在查询结束后才记录,所以在应用反映执行效率出现问题的时候可以通过show processlist命令查看当前mysql正在执行的线程,包括线程的状态,是否锁表等,可以实时的查看sql的执行情况,同时对一些锁表操作进行优化。
分析sql语句
1、通过explain命令,具体的就不说了。
2、通过show profile分析。
首先查看当前的mysql是否支持profile:select @@have_profiling;
查看profile状态,默认是关闭的,即0 select @@profiling; 进行开启:set profiling=1;
然后执行sql语句,执行完毕后,输入show profiles,通过查看Duration(默认单位为秒)可以查看当前sql总耗时多少时间 ,然后查看Query_id为多少,假如为2的话,show profile for query 2,就能查出来该语句执行过程中每个线程耗时时间
show profile for query 2默认显示的是所有线程的内存耗时信息,此外还支持相关参数,如block io、cpu等,命令:show profile cpu,block io for query 2;
二、表结构优化
1、优化表的存储类型,存储固定长度的信息,如身份证等,可用char来存储。
2、分区分表
垂直拆分:根据数据是否常用可进行拆分,缺点:需要管理冗余列,查询所有数据需要join操作。
水平拆分:前提是表数据量大,且数据有独立性等,缺点:查询时需要有多个表,查询所有数据的时候需要union操作,较为复杂。
3、逆规范化。常见的有增加冗余列、增加派生列等。
4、使用中间表提高统计查询速度。(中间表:复制原表的数据,能够针对部分特定需求进行定制)
Mysql的日志
binlog日志:记录了除了查询以外的增删改的语句,通过该日志的话可以对数据恢复及查找数据是否有进行更新。
有3种格式:STATEMENT(将每一条对数据造成修改的sql语句记录在日志中,但是在主从复制的时候,从库某些情况下日志复制会出错)、ROW(记录了每一行的变更记录,而非sql语句)、MIXED(默认,综合了以上2种的优点)
1、查看是否开启了binlog:show variable like 'log_bin'; ON表示开启
2、如果没有开启的话在my.cnf下的[mysqld]下添加信息:
log-bin=文件目录/mysql-bin.log
binlog_format=mixed binlog格式为mixed
max-binlog-size=512M binlog文件最大存储512M
expire-logs-days=14 binlog最多存储14天
3、如果进行了增删改操作后,找到binlog文件,查看binlog日志里面的内容:
mysqlbinlog --no-defaults --base64 -output=decode-rows -V --start-datetime='2019-04-11 00:00:00' --stop-datetime='2019-04-11 02:00:00' binlog文件名>1.sql
上面的语句mysqlbinlog --no-defaults这个是固定的,
--base64 -output=decode-rows -V表示输出base64位编码的sql语句,不加的话输出信息是看不懂的。
--start-datetime='2019-04-11 00:00:00' --stop-datetime='2019-04-11 02:00:00'表示sql执行的时间
>1.sql 表示将解析后的内容存放在1.sql这个文件中,会自动创建的
还有其他一些命令 具体的可以百度
Mysql主从复制:
主库执行读写操作,从库执行查询操作,降低数据库压力。
原理:主库在事务提交的时候会把数据变更(增删改查除了查询)记录在Binlog中,然后将Binlog日志刷新到磁盘中;主库推送Binlog的事件到从库的Relay log,然后从库根据Relay log进行数据变更操作,以此达到主从数据库一致。 属于异步的,存在一定的延迟。
主要通过3个线程来完成主从库之间的数据复制
主库:Binlog Dump线程
从库:io线程、sql线程
首先创建io线程连接主库,主库随后创建Binlog Dump线程读取数据变更的事件并发送到io线程,io线程获取到事件后更新到从库的Relay log日志中,然后从库的sql线程会去读取Relay log日志实现主从库的数据复制。
Binlog和Relay log一模一样,唯一的区别在于从库的sql线程执行完后,会自动删除当前的Relay log,避免占用过多磁盘空间。
缺点:主库宕机的话,需要人工干预从库设置为主机,不符合高可用性。