mysql 数据库优化

数据库类型

 
     一般的关系型数组库包括哪些呢？

     php: mysql
     java:oracle
    .net:sql server
     银行：informix 、db2(关注一下老猛了)

     非关系型数据库
 
    （Nosql、redis(重点学习下)、MongoDB）





mysql 3NF


1NF
  1、原子性
 
    （1）、mysql中的列不能再分
    （2）、mysql列不能重复
 
     一般的关系型数据库天生就满足第一NF

 



 2NF

   表中不能有完全重复的一条记录（行）,一般我们用一个主键来搞定，而且主键是自增的


 3NF
   如果列的内容可以推导（显示推导 eg:班级  ）

 
 什么时候可以反3NF式
      违反3NF能提高查询速度
   eg：点击图片 浏览次数加1




 构建海量表 定位慢查询

     为了查看优化效果我们构建海量表（800000000）而且每条数据不一样，这是我们需要使用存储过程完成任务。
    
   1、构建海量表步骤
      （1）、创建一个测试数据库
           create database testdb
             set  names utf8
      （2）、创建表
           随意创建 一般用Myiasm引擎
      （3）、定义一个新的命令结束符号。防止创建存储过程冲突
             delimiter $$
        以前   select * from user;
        现在   select * from user$$

add" where 1


    查询 emp 表 empno字段 会是1.98秒

    此时我们开始为其建立索引

    alter table emp add primary key (empno);

   建立索引的同时如果我们的数据库引擎是myiasm 我们的索引文件：MYI文件将会变大（一般增加索引的文件大小是改表数据大小的3分之1）

   我们再次查询的时候时间将会是0.00秒
    


  如何定位定位慢查询（slow query）
  在默认情况下mysql是不会记录慢查询的，所以我们在测试的时候，可以指定
  mysql记录慢查询。
   [mysqld]


  log-slow-queries="D:/phpstudy/MySQL/log/mysql-slow.log"

  long_query_time = 1

  log-queries-not-using-indexes


  log-slow-queries: 代表MYSQL慢查询的日志存储目录, 此目录文件一定要有写权限；


  Windows下需要写绝对路径，如：log-slow-queries="C:/Program Files/MySQL/MySQL   Server 5.5/log/mysql-slow.log"

 long_query_time: 最长执行时间. (如图, MSYQL将记录下所有执行时间超过2条的SQL语句,  此处为测试时间, 时间不应太小最好在5-10秒之内, 当然可以根据自己的标准而定);

 
log-queries-not-using-indexes    ：没有使用到索引的查询也将被记录在日志中





  mysql 的变量和查询

   在进入mysql show variables;
   
   show variables like 'long_query_time';

  设置查询时间为1s

   set long_query_time=1;


 







  我们可以在mysql.ini中查看数据存放的位置同样也可以定义位置；

  一般是在data文件中
 
  如果我们使用的mysql引擎是myisam 时 data文件中会产生3个文件
    1、frm(表结构)

    2、MYD（记录数据的）

    3、MYI（记录索引的）


 分析sql语句的工具 explain

  用法：explain sql语句
  eg: explain  select * from emp where ename='khkhg';
 
 
索引失效：

 1、 对加索引的字段使用group by

 2、 对一张表中创建了复合索引

     eg:alter table 表名 add index ('uname','pwd');

     此时查询uname会使用索引，但是查询pwd 不会使用索引 因为：uname是最左边的列，因此发现最左边的索引'uname' 就使用索引，否则不使用索引

 3、 如果条件中有Or字段两边都是索引就可以使用索引，如果只有一个字段有索引，那么索引使用不成功


索引详解：
  1、索引创建
      
       主键索引：1、创建表的时候添加主键索引
                   
         eg:create table t1 (id int primary key auto_increment);
                    
                 2、创建表完成之后在添加主键索引

                 eg:create table t3 (id int ,name char(11) not null default '');

                    alter table t3 add primary key(id,name);

        特点  1、一个表最多只能有一个主键
              2、一个主键可以指向多列（复合主键）
              3、主键索引的效率最高，一般给id
              4、既不能为空也不能重复

       

        唯一索引：

        1、创建表时候可以指定某列或某几列为唯一索引；

        eg:create table t4(id int primary key,name char(20) not null default '',email varchar(64)unique);

                2、表创建好后指定某列或某几列为唯一索引；(必须指定索引名)

                eg:create table t6(id int primary key,name char(20) not null default '',email varchar(64));

                   create unique index uni_email on t6 (email);

        特点：
         1、一张表中可以有多个唯一索引
             2、唯一索引不能重复但可以为null，可以有多个 但是‘ ’只能有一个
             3、当某列数据没有重复时才会使用唯一索引 (速度仅次于主键索引)




        普通索引：
                一般普通索引是把表创建好之后在进行创建
                1、alter table t7 add index(name);

                2、create index ind_name on t7(name);

        特点：1、一张表中可以有多个普通索引，一个普通索引也可以指向多列
              2、列内数据可以重复
              3、效率相对较低
      
    注意：   eg:alter table 表名 add index ('uname','pwd');

                 此时查询uname会使用索引，但是查询pwd 不会使用索引




        全文索引：
                主要针对文章、汉字、英文的检索，可以快速度额检索到文章的某个关键字

          
         1.使用Mysql全文检索fulltext的先决条件

          表的类型必须是MyISAM

      建立全文检索的字段类型必须是char,varchar,text

        创建全文索引：   
          alter table  表名 add fulltext(字段);
          
          
              
         如果我们使用 like 查询 全文索引将会失效

         使用以下的查询才可以成功

         select * from 表名 where match（字段名）against（'查询的单词'）;        

       
        特点：1、mysql 默认的全文索引，只针对MYisam存储引擎
              2、mysql 默认的全文索引，只支持英文
              3、停止词的概念：对于特别普通的字母，不会建立索引（eg：比如查询字母a）
              4、匹配度：全文索引是按照一定概率来进行匹配的

      如何使用mysql 中文索引           

                
  2、索引的查询
      desc 表名
      show keys from 表名\G
      shwo index from 表名\G
      show indexes from  表名\G

      
 

  2、索引的删除

      1、drop index 索引名 on 表名
 
      2、alter table 表名 drop  index 索引名

  3、索引的修改

      先删除 再添加
        
  4、索引原理

      添加索引为什么会变快？

       加索引之后就会使用2叉数树机制（节点）通过节点能快速的找到位置
       一般覆盖的范围就是 log2N次方
         eg:2的100次方就是好大的一个数
       同样insert into （添加索引数据）会使节点发生改变为了维护节点，所以dml操作（update,delete,insert into ）速度会变慢
     
 
 5、索引的注意事项

     1、经常查询并且修改不频繁的字段应加上索引
     2、唯一性较差的字段不应加索引 eg:性别
     3、经常变化的字段不适合加上索引 eg：状态修改
     4、不会出现在where子句中的字段不应加索引




6、如何正确的使用索引

 
 1、 对一张表中创建了复合索引
     eg:alter table 表名 add index ('uname','pwd');

     此时查询uname会使用索引，但是查询pwd 不会使用索引 因为：uname是最左边的列，因此发现最左边的索引'uname' 就使用索引，否则不使用索引
   

 2、（全文索引除外）对于使用like 查询，查询如果是‘%aa%’或“%aa”、“_aa”都不会使用索引；使用like "aa%" 会用到索引
     
     简单来说：在like语句中，如果“中有_或者%就使用不到索引，如果在中间或者最后又_或者%可以使用到索引”
     
     全文索引使用任何like查询都不会使用到索引


 3、如果条件中有Or字段两边都是索引就可以使用索引，如果只有一个字段有索引，那么索引使用不成功，可以考虑增加索引

 4、如果字段类型是字符串那么不使用“”号就不会使用索引哪怕内容是数字


7、sql语句优化
   1、group by 语句优化
   group by 字段 会默认进行排序 如果为了避免消耗可以使用 group by 字段 order by null   
   eg：explain select * from  t7 where uname='afafa' group by id\G;
   eg：explain select * from  t7 where uname='afafa' group by id order by null;


   2、有些情况可以使用连接来代替子查询
    因为使用join ,mysql 不需要再内存中创建临时表
    

8、索引使用的查询情况（索引命中率）

  show status like 'handler_read%';

   handler_read_key:这个值越高越好，越高表示使用索引查询到的次数

   handler_read_md_next:这个值越高。说明查询低效

 
（了解）mysql管理员在导入大批量数据的时候可以：
    MYISAM:
     alter table  table_name disable keys;
      loading data//inset 语句;
     alter table  table_name enable keys;
    对于INNodb:
      1、将要导入的数据按照主键拍讯
      2、set unique_checks=0，关闭唯一性校验。
      3、set autocommit=0,关闭自动提交
   


 9、选择合适的存储引擎
   原则：
      
   1、MYISAM:默认的mysql存储引擎，如果是以读操作和插入操作为主，只有很少的更新和删除，并且对事务的完整性没有很高的要求。访问的速度快（eg:论坛帖子、新闻、商品表）
 
   2、Innodb:提供了具有提交、回滚和崩溃回复能的事务安全，是对比MYISAM,写的处理效率差一些并且会占用更多的磁盘空间（如果对安全性要求高，则使用innodb）[账户、积分、余额]

  3、 memory/help[一些访问频繁，变化频繁，有没有必要入库的数据，比如用户的在线状态]

   特别说明：memeory引擎的使用：

    1、数据本身在内存中，因此操作速度很快

    2、当mysql重启后，数据丢失，但表结构还在


  10、选择合适的数据类型


    1、要求精准度高的数据，要用decimal(10,2)不用float(10,2)  decimal更精确
   
    2、对存储引擎是myisam表要定时碎片整理
         
     eg：当我们在某表中有大量数据时，我们delete 数据后，我们发现磁盘空间没有回收，因此我们需要定时的进行碎片整理。
     
     命令如下：optimize table 表名;

    
     // fdisk -l

    //  find ./+1000nm


    3、时间日期：一般我们用转换时间戳使用date("Y-m-d H:i:s",time());但是php转换时间的区间是2的31次方-1:2147483647


       $time = date("Y-m-d H:i:s",2147483647);
       echo $time;
       2038-01-19 11:14:07
       再多一秒就会返回1970年
   
       所以我们这里可以使用
      //如果要用到2038年以后
      $d = new DateTime(@21474836480);
      $d->setTimezone(new DateTimeZone('PRC'));
      echo $d->format("Y-m-d H:i:s");
       结果：2650-07-06 16:21:20
     
     
      将大的年月日转换为时间戳
     $d = new DateTime("2650-07-06 16:21:20");
       echo $d->format('U');
      结果：21474836480


  11、分表技术
      
      当一个表很大很大时，我们可以考虑添加索引，当添加索引也解决不了问题时，我们可以使用分表技术或分区技术搞定
    
       思想：当一个表大的时候我们就把他搞成小表


     1、水平分割表
        
        我们使用QQ登录表来测试

         (1)、建立三张qq登录表
        create table qq_member0 (id bigint unsigned primary key ,name varchar(64) not null default '',
         email varchar(64) not null unique,pwd char(32) not null default '')engine=myisam charset=utf8;
      
          create table qq_member1 like qq_member0;
          
          create table qq_member2 like qq_member0;
            
          
        （2）、创建一个id表
       create table uu (id int unsigned primary key auto_increment,name char(11) not null default '');

          注册：首先我们先添加uu表
         insert into uu values(null);
         $id = mysql_insert_id();
      $tableName = "qq_member".$id%TABLENUM;
         之后我们在将信息存入qq_member表中
         
        $sql1 = "insert into {$tableName} values($id,'$name','$email','$pwd')";
     if(mysql_query($sql1)){
        echo "添加成功！插入的表是{$tableName}";
        }


        如果需要使用email登录我们需要对大表登录或再处理

    2、垂直分表
   

     所谓垂直分割就是把表的某个大字段（而且很少查询）,单独的取出放入到另外的一个表，并通过id相关联



   12、分区技术
   
      概念：分区允许根据指定的规则，跨文件系统分配单个表的多个部分，表的不同部分在不同的位置被存储为单独的表，MySQL从5.1.3开始支持Partition
    
    

   13、数据库my.ini参数配置
   
       1、 最重要的参数就是内存，我们主要用的innodb引擎，所以下面两个参数可以调的            很大
     
             innodb_additional_mem_pool_siz = 64M
             innodb_buffer_pool_size = 1G
    
        2、对于myisam,需要调整key_buffer_size
           当然调整参数环视要看状态，用show status语句可以看到当前状态，以决定改调哪些参数
         
        3、在my.ini修改端口3306，默认存储引擎和最大连接数
          max_connetions 100=>调整到2500
         query_cache_size 100m