MySQL优化（超完整版）（一）

一、 MySQL的优化

  前言：
    MySQL数据库的优化模块：
      - 数据库的设计—三大范式
      - 数据库的索引：唯一索引、主键索引、聚合索引、复合索引、默认索引
      - SQL优化
      - 分库分表
      - 读写分离：提升IO性能
      - 存储过程优化
      - 对MySQL配置进行优化（my.ini）
      - 定时清理碎片

1. 数据库的设计

（1） 什么是三大范式？

    为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。三大范式包括：
    - 1NF：属性的原子性，要求属性具有原子性，不可分解。
    - 2NF：对记录的唯一性，表中记录是唯一的（通常通过主键来实现）。
    - 3NF：是对字段冗余性的约束，要求字段没有冗余。

（2） 1NF

-- 案例
create table `user`(
id int,
name varchar(10),
address varchar(10)
);
insert into `user` values(1,’zs’,’上海市浦东新区’)；
此时这里就针对address这个字段，有了可分割性，可将将address分为：-市-区。
此时表的设计就不遵循1NF。

（3） 2NF

-- 案例

create table `emp`(
id int,
name varchar(10),
age int(10),
salary float(8,4)
这里id是员工的编号，每一个编号唯一确定一个员工，员工的充值和工位号也是通过这个id确定，此时用id来作为emp表的主键就不禁合理，因为主键一般是不做业务操作的，主键的作用就是唯一标识一行。
);

（4） 3NF

-- 案例
create table student(
stu_id int(10),
stu_name varchar(30),
class_id int(10),
class_name varchar(30)
);
Insert into student values(1,’zs’,1,’一班’);
Insert into student values(2,’ls’,1,’一班’);
Insert into student values(3,’ww’,1,’一班’);
Insert into student values(4,’wb’,1,’一班’);
这里我们发现，class_id和class_name字段大量的冗余，不遵循第3NF，这里我们需要将这张表拆分：student 表和 class表
create table student(
stu_id int(10),
class_id int(10),
stu_name varchar(30)
);
create table `class`(
class_id int(10),
class_name varchar(30)
);
然后对两张表设置外键关联。

2. 慢查询介绍及定位

（1） 定位慢查询

    慢查询的定义：MySQL规定，只要10s内，没有按照规则的时间返回结果，就是慢查询类型，然后MySQL会将这些语句存储到慢查询日志中。
可以通过命令查看

-- 使用show status查看MySQL服务器状态信息
mysql>show status

-- mysql 运行了多长时间
show status like 'uptime';

-- 当前窗口 数据库的查询次数
show status like 'com_select';

-- 当前窗口 数据库的插入次数
show  status like 'com_insert';

-- 当前窗口 数据库的更新次数
show  status like 'com_update';

-- 当前窗口，数据库的删除次数
show  status like 'com_delete';

-- 查看试图连接到MySQL(不管是否连接成功)的连接数
show status like 'connections';

-- 查看当前打开的连接的数量。
show status like 'threads_connected';

-- 显示慢查询数量 **********************
show status like 'slow_queries';

注意：这里默认的是session，表示的时当前会话，如果想查询全局的需要：

show global status like ‘’;

（2） 慢查询案例演示

①慢查询时间设置

--查询慢查询时间
show variables like 'long_query_time';
--修改慢查询时间（临时）
set long_query_time=1; ---但是重启mysql之后，long_query_time依然是my.ini中的值。

②建表、造数据

/*部门表*/
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;

/*员工表*/
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);

③批量生成100W条数据

--生成随机字符
create
 function rand_string(
  n int
 ) returns varchar(255) #该函数会返回一个字符串
 begin #chars_str定义一个变量 chars_str,类型是 varchar(100),默认值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set
return_str = concat( return_str, substring( chars_str, floor( 1 + rand()* 52 ), 1 ));
set
i = i + 1;
end while;
return return_str;
end

--生成随机数
create FUNCTION rand_num()
RETURNS int(5)
BEGIN
 DECLARE i int default 0;
 set i =floor(10+RAND()*500);
 return i;
END

--编写存储过程，插入数据
delimiter // 
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0; 
#set autocommit =0 把autocommit设置成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 ;

-- 执行
call insert_emp (100001,10000000);

④设置MySQL记录慢查询日志
首先将MySQL服务关闭：

进入mysql/bin下执行一下命令：
[mysql5.5 可以在my.ini指定]（安全模式启动，数据库将操作写入日志，以备恢复）

$ mysqld.exe --safe-mode  --slow-query-log 

然后在my.ini配置文件中有这么一行：

在这个目录下，会生成相应的慢查询记录。
#5.7版本自动开启：

然后我们设置慢查询时间为：1s

set long_query_time=1;

用刚刚造出来的数据执行一个慢查询:

select * from emp where ename = 'aDNehz';

查看慢查询日志：

通过日志，我们就能定位到具体的是哪一条语句查询慢。

3. MySQL的索引

索引的概述

   索引用来快速的查询那些具有特定值的记录。所有的MySQL索引都是以B+树的形式保存的。如果没有索引，执行查询时MySQL必须从第一个记录开始，进行全表扫描，直至找到合适的记录。表里的记录越多，这个操作越耗时。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。

（1） 主键索引

   主键索引是一种唯一性索引，但是它必须指定“PRIMARY KEY”。主键一般在创建表的时候指定，并且一张表只能有一个主键。

#创建主键索引：
默认情况下，MySQL会为主键自动添加主键索引。
也可以后期添加主键：
Alter table table_name add primary key(field_name);
#删除主键：
Alter table table_name drop primary key; 

#查看索引
show index from table_name;
show keys from table_name;

（2） 全文索引

    全文索引一般用于查询文本或者长内容而建立。

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%';

-- 建立全文索引
alter table articles add  FULLTEXT index fulltext_article(body);
-- 正确使用方法
select * from articles where match (body) against('mysql');

使用全文索引的注意事项：
    - MySql自带的全文索引只能用于数据库引擎为MYISAM的数据表，如果是其他数据引擎，则全文索引不会生效.
    - MySQL不能对中文进行全文索引，只适用于英文.
    - 使用全文索引，只能用固定的语法：match(字段名)… against(关键字).
    - MySQL全文索引所能找到的默认最小长度为4个字符，并且如果查询的字符串包含停止词（常见字符），那么该停止词将会被忽略。

（3） 唯一索引

   这种索引的所有值都只能出现一次，即必须唯一。
默认的，在创建表时指定字段为唯一时，自动为其创建唯一索引。

#创建唯一索引：
CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表)；
ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表)；
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) )；
例：
create table ddd(id int primary key auto_increment , name varchar(32) unique);
create unique index uniq_index_name on ddd(name);
alter table ddd add unique uniq_index_name (name);

注意：unique字段可以为null，可以有多个null，但是如果是字符串的话只能有一个为‘’。

（4） 普通索引

    普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

创建方式：   create index 索引名 on 表 (列1,列名2);

-- 案例
-- 未建立索引时查询 1.45s
select * from emp where ename ='MFPkFv';
-- 创建普通索引
create index index_ename on emp (ename);
-- 查询 4ms
select * from emp where ename ='MFPkFv';

（5） 索引实现原理

   MySQL数据库的索引，是数据库管理中的一个排序的数据结构，以便于协助快速查询，更数据库表中数据。MySQL中的索引实现方式就是：B+树索引。

   上图展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在 O(log2n)的复杂度内获取到相应数据。
不同的存储引擎使用的索引：

    b-/+树索引的性能分析：先从 B-Tree 分析，根据 B-Tree 的定义，可知检索一次最多需要访问 h 个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次 I/O 就可以完全载入。为了达到这个目的，在实际实现 B-Tree 还需要使用如下技巧：
   每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个 node 只需一次 I/O。B-Tree 中一次检索最多需要 h-1 次 I/O（根节点常驻内存），渐进复杂度为 O(h)=O(logdN)。一般实际应用中，出度 d 是非常大的数字，通常超过 100，因此 h 非常小（通常不超过 3）。
   而红黑树这种结构，h 明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的 I/O 渐进复杂度也为 O(h)，效率明显比 B-Tree 差很多。
   综上所述，用 B-Tree 作为索引结构效率是非常高的。
    B+ 树非叶节点中存放的关键码并不指示数据对象的地址指针，非也节点只是索引部分。所有的叶节点在同一层上，包含了全部关键码和相应数据对象的存放地址指针，且叶节点按关键码从小到大顺序链接。如果实际数据对象按加入的顺序存储而不是按关键码次数存储的话，叶节点的索引必须是稠密索引，若实际数据存储按关键码次序存放的话，叶节点索引时稀疏索引。
   B+ 树有 2 个头指针，一个是树的根节点，一个是最小关键码的叶节点。
所以 B+ 树有两种搜索方法：
   一种是按叶节点自己拉起的链表顺序搜索。
   一种是从根节点开始搜索，和 B 树类似，不过如果非叶节点的关键码等于给定值，搜索并不停止，而是继续沿右指针，一直查到叶节点上的关键码。所以无论搜索是否成功，都将走完树的所有层。
B+ 树中，数据对象的插入和删除仅在叶节点上进行。

综上所述，两种排序的不同之处在于：
    - B 树中同一键值不会出现多次，并且它有可能出现在叶结点，也有可能出现在非叶结点中。而 B+ 树的键一定会出现在叶结点中，并且有可能在非叶结点中也有可能重复出现，以维持 B+ 树的平衡。
    - 因为 B 树键位置不定，且在整个树结构中只出现一次，虽然可以节省存储空间，但使得在插入、删除操作复杂度明显增加。B+ 树相比来说是一种较好的折中。
    - B 树的查询效率与键在树中的位置有关，最大时间复杂度与 B+ 树相同(在叶结点的时候)，最小时间复杂度为 1(在根结点的时候)。而 B+ 树的时候复杂度对某建成的树是固定的。可以扫描2的次方。

（6） 索引的优势与劣势

  优势：
    - 创建索引可以大大提高系统性能
    - 大大加快对数据的检索速度
    - 加速表和表之间的连接
    - 对使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
  劣势：
    - 建立索引耗时：创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
    - 占用一定的物理内存：索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
    - DML操作效率变低： 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

（7） 建立索引的时机

  应该建立索引的字段：
    - 查询作为查询条件字段应该创建索引
    - 经常用在连接的字段可以建立索引
    - 经常需要根据范围进行搜索的列上创建索引
    - 经常需要排序的列上创建索引
  不应建立索引的字段：
    - 那些在查询中很少使用或者参考的列不应该创建索引
    - 对于那些只有很少数据值的列也不应该增加索引，例如：性别、是否已婚等等
    - 对于那些定义为 text, image 和 bit 数据类型的列不应该增加索引
    - 修改性能远远大于检索性能时，不应该创建索引

（8） 索引使用的注意事项

① 联合索引的失效场景

-- 给dept新增数据：
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

-- 执行
call insert_dept(100,10);

-- 创建主键索引
alter table dept add primary key (deptno);
-- 创建一个联合索引
alter table dept add index my_ind (dname,loc); //  dname 左边的列,loc就是右边的列
在联合索引中，以dept为例：
-- 索引不失效
explain select * from dept where dname ='fuHagHPcRc';
-- 索引不失效
explain select * from dept where dname ='fuHagHPcRc' and loc = 'KtPSTRAc';
-- 索引失效
explain select * from dept where  loc = 'KtPSTRAc';

综上案例得出：创建的多列索引，如果不是使用第一部分，则不会创建索引。

② 模糊查询时like,索引不会失效，但是如果like中有‘%xxx%’，则索引失效，但是%放在后面索引不会失效，例“xxx%”，但是%放在前面会失效，例：“%xxx”。
③ 如果条件中有or，及时其中有带索引字段，也不会使用索引
④ 如果类型为字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引
⑤ 如果MySQL的全表扫描比使用索引快，则不使用索引。

#查看索引的使用率：
show status like 'handler_read%';

handler_read_key：这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next：这个值越高，说明查询低效。

4. SQL语句优化技巧

（1） group by 的默认排序

    使用group by 分组查询是，默认分组后，还会排序，可能会降低速度，在group by 后面增加 order by null 就可以防止排序。

-- 案例
-- 8.188s
select * from emp  group by deptno;

 -- 6.200s
select * from emp  group by deptno order by null;

这是因为在group by后默认使用排序：
explain select * from emp  group by deptno;

（2） 有些情况下，使用连接来代替子查询

原因：使用join，MySQL不需要在内存中创建临时表。

-- 7.924s
select * from dept, emp where dept.deptno=emp.deptno;

-- 8.8s
select * from dept left join emp on dept.deptno=emp.deptno;

（3） 查询优化时，尽量避免全表扫描

（4） 尽量避免在where 子句后面对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描（解决，对null值进行default 设置）

（5） 在对where子句进行判断时，尽量不要使用xx=，例：>=、<=,比如>=18 就写成>17

（6） 能用between 就不要用in

二、 补充内容

1. MySQL的存储引擎

（1） MySQL引擎介绍

    MySQL使用的存储引擎有三种：myisam / innodb/ memory。
    Myisam存储：如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. ,比如 bbs 中的 发帖表，回复表。
    innodb存储：对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表。
    Memory：基于内存存储，类似于Redis。

（2） myisam引擎与innodb引擎的区别

   - 事物安全（MyISAM不支持事务，INNODB支持事务）
   - 查询和添加速度（MyISAM批量插入速度快）
   - 支持全文索引（MyISAM支持全文索引，INNODB不支持全文索引）
   - 锁机制（MyISAM时表锁，innodb是行锁）
   - 外键机制（MyISAM 不支持外键， INNODB支持外键）
Ps：Memory 存储，比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快。

（3） myisam使用的注意事项

   如果我们在建表的时候指定的是myisam引擎，那么在我们删除表数据时，默认只是逻辑删除，而真正的物理存储的数据文件是不会删除的。
   接下来由下图，为大家讲解一下MySQL数据存放的方式：
   在my.ini文件中有：datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data 这个配置就是本地系统存放MySQL数据文件的目录。

进入具体的数据库：（test）

这其中：
   .frm结尾的表示表的结构文件
   .MYD结尾的表示数据文件
   .MYI结尾的表示索引文件
#然后我们演示如果使用的是myisam引擎，数据如何删除：

-- 建表，并指定引擎为myisam
create table test100(id int unsigned ,name varchar (22)) engine=myisam;
-- 插入数据
insert into test100 values(1,'aaaaa');
insert into test100 values(2,'bbbb');
insert into test100 values(3,'ccccc');
-- 批量增加数据
insert into test100 select id,name from test100;
-- 删除部分数据
delete from test100 where id =2;

此时我们查看test100.MYD 发现这个文件的大小根本没有变化。

--这时我们必须清除碎片：
optimize table test100;  

之后test100.MYD大小就会改变了，数据真正意义上的删除了。

2. MySQL的定时备份

编写备份脚本，然后通过crontab 定时执行。

-- 语法：
mysqldump –u -账号 –密码 数据库 [表名1 表名2..]  > 文件路径
--例：
mysqldump -u -root root test > d:\temp.sql
--载入数据,在MySQL
mysql>source /path/temp.sql

小编在下篇文章给大家MySQL如何分库分表，MySQL负载均衡版的读写分离。

博文地址：https://blog.51cto.com/14048416/2432938

绝对没有水货！！！！！！！！！