MYSQL数据库优化技术

对Mysql的优化是一种综合性的优化，主要包括
1：表的设计合理化(符合 3NF)
2: 添加适当的索引(index) 普通索引，主键索引，唯一索引，全文索引，空间索引
3：分表技术(水平分割)，(垂直分割)
4：读写分离
5: 存储过程 [模块化编程，可以提高编程]
6:对Mysql配置优化(配置最大并发数,调整缓存大小)
7：Mysql服务器硬件升级
8：要定时的去清除一些不需要的数据，要定时进行碎片整理(MyISAM)

一：数据库表的设计
     什么样的表才是3NF(范式)
     前面有写到

关系型数据库(mysql/oracle/db2/sysbase/sql server)
非关系型数据库(特点是面向对象和集合的)

NoSQL数据库(介于关系和非关系之间的，比如mongoDB,特点是面向文档的)

--慢查询
sql语句本身的优化（定位慢查询）
1:首先了解mysql数据库的一些运行状态如何查询(比如想知道mysql运行的时间，一共执行了多少次相关语句，当前连接数)
show status
--例
 show status like 'uptime';
 show status like 'com_select' 
show status like 'com_insert' 类推

show [session|global] status like ...
如果不写，默认是会话级别。如果想看所有的，则应该写global。
show status like 'connections'
--显示慢查询
show status like 'slow_queries';
--如何定位慢查询 
   构建一个大表(400万)->存储过程构建
 在默认情况下，mysql认为，10秒种为一个慢查询
 --如何修改mysql的慢查询
 show variables like 'long_query_time';

 set long_query_time=1;

#创建表DEPT

CREATE TABLE dept( 
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0, 
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;


#创建表EMP雇员
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;


#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

#这里我创建了一个函数. 

#rand_string(n INT) rand_string 是函数名 (n INT) //该函数接收一个整数
delimiter

createfunction(nINT)returnsvarchar(255)begindeclarecharsstrvarchar(100)default′abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ′;declarereturnstrvarchar(255)default′′;declareiintdefault0;whilei<ndosetreturnstr=concat(returnstr,substring(charsstr,floor(1+rand()∗52),1));seti=i+1;endwhile;returnreturnstr;endcreatefunction(nINT)returnsvarchar(255)begindeclarecharsstrvarchar(100)default′abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ′;declarereturnstrvarchar(255)default″;declareiintdefault0;whilei<ndosetreturnstr=concat(returnstr,substring(charsstr,floor(1+rand()∗52),1));seti=i+1;endwhile;returnreturnstr;end

delimiter ;
select rand_string(6);


# 随机产生部门编号
delimiter

dropfunctionrandnumdropfunctionrandnum

#这里我们又自定了一个函数
create function rand_num( )
returns int(5)
begin 
 declare i int default 0;
 set i = floor(10+rand()*500);
return i;
  end

delimiter ; 
select rand_num();  
#****************************************** 
#向emp表中插入记录(海量的数据)  
delimiterdelimiter ; select rand_num();  #****************************************** #向emp表中插入记录(海量的数据)  delimiter

drop procedure insert_emp

#随即添加雇员[光标]  400w 
create procedure insert_emp(in start int(10),in max_num int(10)) 
begin 
declare i int default 0;  
 set autocommit = 0;   
 repeat 
 set i = i + 1; 
 insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num()); 
  until i = max_num 
 end repeat; 
   commit; 
 end#随即添加雇员[光标]  400w create procedure insert_emp(in start int(10),in max_num int(10)) begin declare i int default 0;   set autocommit = 0;    repeat  set i = i + 1;  insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());   until i = max_num  end repeat;    commit;  end

delimiter ;
#调用刚刚写好的函数, 1800000条记录,从100001号开始
call insert_emp(100001,40000);

--这时我们如果出现一条语句执行时间超过一秒，就会统计到
show status like 'slow_queries';
--如果把慢查询的sql记录到我们的日志中去，默认情况下，我们你的Mysql不会记录慢查询，需要在启动mysql时候，指定记录慢查询才可以

--先关闭mysql，再重新启动。(服务里启动)
--如果启用了慢查询日志，默认把这个文件放在my.ini文件中记录的位置(datadir=...)
--切换至bin\mysqld.exe --safe-mode --slow-query-log
--在测试即可。

--优化问题
  --通过 explain 语句可以分析，mysql如何执行你的sql语句。
  --索引
     --4种索引(主键索引，唯一索引，全文索引，普通索引)
     --1：主键索引
         --当一张表中，把某个列设为主键的时候，则该列为主键索引。如：
create table aaa(id int unsigned primary key auto_increment,
 name varchar(32) not null default '') charset=utf8;
 --这时,id列就是主键索引
 --如果创建表时，未指定主键索引，也可以在创建表后添加，指令如下
 alter table 表名 add primary key (列名)
 --例
  create table bbb(
        id int ,
name varchar(20) not null default '')charset utf8;
       alter table bbb add primary key (id);
     --1.2 普通索引
               --create index 索引名on 表(列)
          --普通索引的创建，先创建表，然后再创建普通索引
 create table ccc
 (
    id int unsigned,
    name varchar(32)
 );
      --1.3 全文索引
          --全文索引主要是针对文字或者文本的检索，比如文章，全文索引只能针对MyISAM有用。
  create table articles(
     id int unsigned auto_increment not null primary key,
     title varchar(200),
     body text,
     fulltext(title,body) 
  )engine=myisam charset=utf8;
 
INSERT INTO articles (title,body) VALUES
    ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ('How To Use MySQL Well','After you went through a ...'),
    ('Optimizing MySQL','In this tutorial we will show ...'),
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ('MySQL vs. YourSQL','In the following database comparison ...'),
    ('MySQL Security','When configured properly, MySQL ...');
 
  --如何使用全文索引
     select *from articles where body like '%mysql%'; [不会使用全文索引]。
     --证明：
       explain  select *from articles where body like '%mysql%';
    --正确用法
        select *from articles where match(title,body) against('database');
     
      --1.4 唯一索引
         --当表的某列被指定为unique，这列就是一个唯一索引了
--方式一： create table ddd(
id int primary key auto_increment,
name varchar(32) not null unique
)
       --这时，name列就是唯一索引了
                                                                   
       --name 列是否可以为null。 可以为空。
        --方式二：
   --在创建表后，再去创建唯一索引
   create table eee (id int primary key auto_increment,
   name varchar(32)
   );
   create unique index 索引名 on表名 (字段);

      --2：查询索引
           desc　表名　［该方法的缺点是：不能够显示索引名］
  show index from 表名;
           show keys from 表名；
       --3:删除索引
     
           --alter table 表名 drop index 索引名;（唯一索引也是这样删除）
  --如果删除的是主键索引
            --alter table 表名 drop primary key; 
       --4:修改索引
       -- 先删除，后创建
        
    --创建索引的注意事项
      --索引的代价：
         1：占用磁盘空间
         2：对DML语句的操作有影响,变慢(比如：删除一条记录之后，要重建二叉树)。
在哪些列上适合添加索引？
     (1)：较为频繁的作为查询条件字段应该创建索引
                select *from emp where empno=1
     (2):唯一性太差的字段不适合创建索引，即使频繁作为查询条件
 select *from emp where sex='男';
              (3):更新非常频繁的字段也不适合创建索引。
                   select *from emp where logincount=1;


              (4): 不会出现在where子句中字段不该创建索引
        
    总结：满足一下条件的字段，才可以创建索引
     a:肯定在where条件中经常使用。
     b:该字段的内容不是唯一的几个值。
     c:字段本身不是经常(频繁)变化的。
  --使用索引的注意事项


   在dept表中增加几个部门；


   测试： 如果我们的表中有复合索引，此时，我们注意到：
      1：对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。
      
      explain 结果中的字段含义
        id:查询序列号
select_type :查询类型
type：扫描的方式，all表示全表扫描
possible keys: 可能使用到的索引
key : 实际使用的索引
rows:执行该sql语句扫描了多少行，可能得到多少记录
Extra:代表sql语句的额外信息(比如排序信息)

        explain 可以帮助我们在不真正执行某个sql语句时，就告诉我们Mysql将怎么样执行，这样利于我们去分析sql指令

    --例
        先建立复合索引
alter table dept add index myind (dname,loc);
      explain  select *from dept where loc='tmgSVaqC';   不会使用索引
      explain  select *from dept where dname='oJozPbOOC'; 会使用索引


     2:对于使用like的查询，查询如果是 ’%aaa‘ 不会使用到索引 ’aaa%‘会使用到索引。
      比如：
         explain  select *from dept where dname like 'oJozPbOOC'; 使用了索引
         explain  select *from dept where dname like '%oJozPbOOC';不会使用索引(因为使用了%或者_)
         explain  select *from dept where dname like 'oJozPbOOC%';使用了索引
    即用like 查询时，关键字的前面，不能使用%或者_这样的字符，如果一定要前面有变化的值，考虑使用全文索引
    
      3:如果条件中有or，即使其中有条件带索引，也不会使用，换言之，就是要求使用到的所有字段都必须建索引
         select *from dept where dname='aaa' or loc='ddd'\G; 不会使用索引
         select *from dept where dname='aaa' or empno=890\G; 会使用索引
      4：如果列类型是字符串，那一定要在条件中使用引号引起来，否则不会使用索引。
      
      5: 如果mysql 发现全表扫描比索引还快，则不会使用索引。
    
    如何查看索引使用的情况：
show status like 'Handler_read%';   
             这个值越高，说明索引使用率越高
  Handler_read_key越高 ，索引使用率高
  Hnadler_rnd_nex越高越不好。
          
#**************************************************************
#  向dept表中插入记录
delimiter

dropprocedureinsertdeptdropprocedureinsertdept

create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0; 
 set autocommit = 0;  
 repeat
 set i = i + 1;
 insert into dept values ((start+i) ,rand_string(10),rand_string(8));
  until i = max_num
 end repeat;
   commit;
 end

delimiter ; 
call insert_dept(100,10);  
#------------------------------------------------ 
#向salgrade 表插入数据 
delimiterdelimiter ; call insert_dept(100,10);  #------------------------------------------------ #向salgrade 表插入数据 delimiter

drop procedure insert_salgrade

createprocedureinsertsalgrade(instartint(10),inmaxnumint(10))begindeclareiintdefault0;setautocommit=0;ALTERTABLEempDISABLEKEYS;repeatseti=i+1;insertintosalgradevalues((start+i),(start+i),(start+i));untili=maxnumendrepeat;commit;endcreateprocedureinsertsalgrade(instartint(10),inmaxnumint(10))begindeclareiintdefault0;setautocommit=0;ALTERTABLEempDISABLEKEYS;repeatseti=i+1;insertintosalgradevalues((start+i),(start+i),(start+i));untili=maxnumendrepeat;commit;end

delimiter ;
#测试不需要了
#call insert_salgrade(10000,1000000);

#----------------------------------------------


SQL语句的小技巧
  1：在使用group by 分组查询时，默认分组后， 还会排序，可能会降低速度。
 可以在 gorup by 后增加order by null 可以加快速度。
  2：有些情况下，可以使用连接来替代子查询。因为使用join，Mysql不需要在内存中创建临时表
   select *from dept,emp where dept.deptno=emp.deptno; [简单处理连接]
   select *from dept left join emp on dept.depono=emp.deptno;


  3：选择合适的搜索引擎
   --Myisam存储：如果表对事务要求不高,同时是以查询和添加为主的，我们考虑使用Myisam存储引擎。
                 支持全文索引(InnDB不支持) ,表锁
 
   --InnoDB: 对事务要求高，保存的数据都是重要数据，建议InnoDB
               支持事务，支持外键（Myisam不支持）   行锁
   
   --Memory: 数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory引擎。
   
   4:如果你的数据库的存储引擎为myisam,则要定期进行碎片整理。
       
             optimize table 表名;


-- 数据库的定时备份
   --手动备份数据库(表的)方法
     --cmd控制台
       mysqldump -u root -p 5678 数据库的名字[表名 1 表名 2..] >文件路径
        比如：把temp数据库备份到d:temp.bak
       mysqldump -u root -p 5678 temp >d:\temp.bak
        如果希望备份的是数据库的某几张表
       mysqldump -u root -p 5678 temp dept >d:\temp.dept.bak


       --若要恢复数据，则：
              source  d:\temp.dept.bak
      --定时完成备份
          1:把备份数据库的指令，写入到bat文件，然后通过任务管理器(linux下用crontab)去定时调用文件。
  写mytask.bat文件
   C:\myenv\mysql5.5.27\bin/mysqldump -u root -p5678 temp dept >d:\temp.dept.bak


--表的分割技术
   --水平分割，垂直分离
--增量备份
--定义：mysql数据库会以二进制的形式，自动把用户对mysql数据库的操作，记录到文件，当用户希望
恢复的时候可以使用备份文件，进行恢复。
--增量备份会记录(dml语句，创建表的语句, 不会记录select)
--记录的是操作语句本身，操作的时间和位置。
--实际案例，如何进行增量备份，和恢复。
步骤：
 （1）配置my.ini 或者my.conf，启用二进制备份。 
   找到mysqld
    port=3306
    添加：log-bin=d:/binlog/mylog(此名字可以任意)
  (2)重新启动mysql 
      这时会得到文件，mylog.index(索引文件)：记录有哪些增量备份文件
                      mylog.000001:存放用户对数据库操作的文件


  可以使用mysqlbinlog(mysql下的bin目录)这个程序来查看备份文件的内容
  进入到cmd控制台
  cmd>mysqlbinlog 文件路径 | mysql -uroot -p;