mysql的优化

一、三大范式

  a: 第一范式(确保每列保持原子性)
  b: 第二范式(确保表中的每列都和主键相关)
  c: 第三范式(确保每列都和主键列直接相关,而不是间接相关)

二、索引的分类

     1.普通索引 ：是最基本的索引，它没有任何限制
     2.唯一索引 ：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值
     3.主键索引 ：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。
     4.组合索引 ：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用
     5.全文索引

三、索引引擎类型：

四、InnoDB与Myisam的区别

        INNODB 存储: 对事务要求高，保存的数据都是重要数据，适合更新和插入等操作，我们建议使用INNODB,比如订单表，账号表
        Myisam 存储: 如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. ,比如 bbs 中的 发帖表，回复表.

        B树与B+树在引擎中的区别

子叶：KLFMNHIJ
非子叶（分支）:ABCDEG
除了A以外，其他子节点都是某个结点的孩子
堂兄弟指的是一层的结点：BCD是、EFGHIJ也是，KLMN也是
树的深度为4

        B树的无论是子叶还是非子叶都存储了键（key）和数据（data）
        B+树 非子叶只存储键（key)，但不存储数据

五、如何发现有问题的SQL

        1、检查慢查日志是否开启：
              show variables like 'slow_query_log'

        //开启慢查日志：
          show variables like 'slow_query_log'  

        //查看是否开启慢查询日志
        set global slow_query_log_file=' /var/lib/mysql/mysql-host-slow.log ' 

        //慢查询日志的位置
        set global log_queries_not_using_indexes=on;

        //开启慢查询日志
        set global long_query_time=1;  
        //大于1秒钟的数据记录到慢日志中，如果设置为默认0，则会有大量的信息存储在磁盘中，磁盘很容易满掉

        
        2、验证慢查询日志是否开启：
        Show databases;
        Use sakila;
        select * from store;
        select * from staff; 


        监听日志文件，看是否写入
        tail -50f /var/lib/mysql/mysql-host-slow.log

        3、MySQL慢查日志的存储格式
        如下图所示：

        说明：
      a、# Time: 180526  1:06:54 -------查询的执行时间
      b、# User@Host: root[root] @ localhost []  Id:     4 -------执行sql的主机信息
      c、# Query_time: 0.000401  Lock_time: 0.000105 Rows_sent: 2  Rows_examined: 2-------SQL的执行信息：
		Query_time：SQL的查询时间
		Lock_time：锁定时间
		Rows_sent：所发送的行数
		Rows_examined：锁扫描的行数
      d、SET timestamp=1527268014; -------SQL执行时间
      e、select * from staff; -------SQL的执行内容

        4、安装pt-query-digest工具
              1.1、快速安装（注：必须先要安装wget）
        wget https://www.percona.com/downloads/percona-toolkit/2.2.16/RPM/percona-toolkit-2.2.16-1.noarch.rpm && yum localinstall -y  percona-toolkit-2.2.16-1.noarch.rpm
              1.2、检查是否安装完成
              命令行中输入：pt-summary

	  显示如下图所示：说明安装成功！输入【[root@node03 mysql]# pt-query-digest --help】

              1.3、工具使用简介：
              pt-summary –help
              wget http://percona.com/get/pt-summary

                    a、查看服务器信息
                    命令：pt-summary

                    b、查看磁盘开销使用信息
                    命令：pt-diskstats
        
                    c、查看mysql数据库信息
                    命令：pt-mysql-summary --user=root --password=123456

                    d、分析慢查询日志
 命令：pt-query-digest /data/mysql/data/db-3-12-slow.log

                    e、查找mysql的从库和同步状态
 命令：pt-slave-find --host=localhost --user=root --password=123456

                    f、查看mysql的死锁信息
  pt-deadlock-logger --user=root --password=123456 localhost

                    g、从慢查询日志中分析索引使用情况
  pt-index-usage slow_20131009.log      

                    h、查找数据库表中重复的索引
  pt-duplicate-key-checker --host=localhost --user=root --password=123456

                    j、查看mysql表和文件的当前活动IO开销
  pt-ioprofile

                    k、pt-find查找mysql表和执行命令，示例如下

                          查找数据库里大于2G的表：
                          pt-find --user=root --password=123456 --tablesize +2G
                          
                          查找10天前创建，MyISAM引擎的表：
                          pt-find --user=root --password=123456 --ctime +10 --engine MyISAM

                          查看表和索引大小并排序
                          pt-find --user=root --password=123456 --printf "%T\t%D.%N\n" | sort -rn

                    g、pt-kill 杀掉符合标准的mysql进程
                          
                          显示查询时间大于60秒的查询
                          pt-kill --user=root --password=123456 --busy-time 60 --print
                          
                          kill掉大于60秒的查询
                           pt-kill --user=root --password=123456 --busy-time 60 --kill
                    
                    z、验证数据库复制的完整性
                          pt-table-checksum --user=root --password=123456

六、那些列上适合添加索引

        ①	肯定在where条件经常使用
        ②	该字段的内容不是唯一的几个值
        ③	字段内容不是频繁变化