运维必会之MySQL篇 (7)

mysql> explain select SQL_NO_CACHE *from student where Sname='nfsnobody'\G

 
3、对需要建索引的条件列建立索引

  生产场景，大表高峰期不能建立索引，例如：300玩记录。

4、分析慢查询SQL的工具mysqlsla（每天早很发邮件给核心开发、运维、CTO）
5、按天切割慢查询日志，如果并发太大可以按小时，去重分析后发给大家。

   a．mv然后flush进程。
   b．cp复制，然后利用>清空。
   c．定时任务 mv /data/3306/slow.log/opt/$(date +%F)_slow.log
   d．mysqladmin –uroot–pnfsnobody –S /data/3306/mysql.sock flush-logs

6、日常优化

   dba、总监、开发、CTO联合解决。

索引优化

1）白名单机制 – 百度，项目开发，DBA暗语，减少上线后的慢SQL数量；

抓出慢SQL    配置my.cnf    long_query_time = 2                    ### 超过2秒，记录到Slow Log里。    log_queries_not_using_indexes           ### 没有走索引的语句，记录到Slow Log里。    log-slow-queries = /data/3306/slow.log     ### Slow Log 文件。    min_examined_row_limit                ### 记录结果集大于指定值的SQL语句，5.6-5.7都可以使用    按天轮询：slow-log

2）慢查询日志分析工具 -----mysqlsla 或 pt-query-digest（推荐）；
3）每天晚上0点定时分析慢查询，发到核心开发，DBA分析，及高级运维，CTO的邮箱里；

  DBA分析给出优化建议 à 核心开发确认更改 à DBA线上操作处理；

4）定期使用pt-duplicate-key-checker检查并删除重复的索引；

  定期使用pt-index-usage工具检查并删除使用频率很低的索引；

5）使用pt-online-schema-change来完成大表的ONLINE DDL 需求；
6）有时候mysql会使用错误的索引，对于这种情况使用USE INDEX;
7）使用explain及set profile 优化SQL语句；
8）打的复杂的SQL语句拆分成多个小的SQL语句；

 子查询，JOIN连表查询，某个表4000万条记录；

9）数据库是存储数据的地方，但是不是计算数据的地方；

 对于数据计算、应用处理、都要拿到前端应用解决、禁止在数据库上处理；

10）使用连接（JOIN）来代替子查询；
11）避免在整个表上使用count(*)，他可能锁住整张表；
12）多表连接查询时，把结果集小的表作为驱动表；
13）多表联接并且有排序时，排序字段必须是驱动表里的，负责排序列无法用到索引；
14）尽量去掉”IN”  ”OR”“<>”
15）类似分页功能的SQL，建议先用主键关联，然后返回结果集，效率会高很多；
 

第四章 MySQL日志

error log 错误日志

开启方法：

vim /etc/my.cnf [mysqld_safe] log-error=http://www.likecs.com/data/3306/mysql_nfsnobody3306.err

在库内查看错误日志：

show variables like"%err%";

访问日志

开启方法：

vim /etc/my.cnf [mysqld] general_log = on general_log_file =http://www.likecs.com/data/3306/data/MySQL_nfsnobody.log

临时生效：

mysql> set globalgeneral_log_file = "/data/3306/data/MySQL_nfsnobody.log"; mysql> show variables like'general_log%'; mysql> set global general_log =on;

注意：在生产环境中访问日志，不开。

慢查询日志

开启方法：

vim /etc/my.cnf long_query_time = 2                    ### 超过2秒，记录到Slow Log里。 log_queries_not_using_indexes           ### 没有走索引的语句，记录到Slow Log里。 log-slow-queries =http://www.likecs.com/data/3306/slow.log     ### Slow Log 文件。 min_examined_row_limit                ### 记录结果集大于指定值的SQL语句，5.6-5.7都可以使用

 

二进制日志

介绍：
二进制日志（binary log）：记录数据库里的数据被修改
（insert、update、delete、create、drop、alter）的相关语句，用户主从复制及增量恢复；
调整：
bin-log文件切割条件：

a、数据库重启，自动切割新文件；
b、mysqldump –F/mysqladmin flush-logs；
c、文件达到1.1G，自动切割；
d、认为配置及调整；

删除：
a、设置参数自动删除

vim /etc/my.cnf expire_logs_days = 7  #<==删除7天前的日志