Mysql优化专题

1. 优化目的

2. 数据

https://dev.mysql.com/doc/index-other.html
DVD租赁数据

3. 慢查询日志
http://www.cnblogs.com/kerrycode/p/5593204.html

show variables like ‘%log%’;
set global log_queries_not_using_indexs=on ;
show variables like ‘long_query_time’; 超过时间会慢查
set global slow_query_log=on; 开启慢查日志

查询后会出现慢查日志：如：

  select * from store;  

| store_id | manager_staff_id | address_id | last_update |
+———-+——————+————+———————+
| 1 | 1 | 1 | 2006-02-15 04:57:12 |
| 2 | 2 | 2 | 2006-02-15 04:57:12 |
+———-+——————+————+———————+

 show variables like "%slow%";

+———————+——————————————+
| Variable_name | Value |
+———————+——————————————+
| log_slow_queries | ON |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | E:\phpStudy\MySQL\data\whbingPC-slow.log |
+———————+——————————————+
查看慢查询日志的保存位置。
慢查日志的内容：

包括：
sql的主机信息；
sql的执行信息；
sql的执行时间；
sql的内容；

4. 慢查日志工具

工具1：mysqldumpslow

[root@master-1a mysql]# mysqldumpslow -h
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

[root@master-1a mysql]# mysqldumpslow -t 50 /var/lib/mysql/master-1a-slow.log

Reading mysql slow query log from /var/lib/mysql/master-1a-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  # Time: N-N-15T15:N:N.319335Z
  # User@Host: root[root] @ localhost []  Id:    N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  use sakila;
  SET timestamp=N;
  select * from actor limit N

Died at /usr/bin/mysqldumpslow line 161, <> chunk 1.

工具2：pt-query-digest

pt-query-digest使用方法介绍
https://www.36nu.com/post/228.html

直接分析慢查询文件,输出到文件

# pt-query-digest slow.log > slow_report.log

#pt-query-digest /var/lib/mysql/master-1a-slow.log > /whb/slow_report.log

5. 发现问题

6. explain命令查看执行计划

然后通过show warnings \G;查看数据库转化后的优化结果。

7. max( ) 和count( )的优化

http://blog.csdn.net/tanglei6636/article/details/52852340
http://blog.csdn.net/qq_28602957/article/details/52138947

explain select max(payment_date) from payment \G;

建立索引优化

create index idx_paydate on payment(payment_date);

8. 子查询优化

使用连接（JOIN）来代替子查询(Sub-Queries) 优化
http://blog.csdn.net/hongsejiaozhu/article/details/1876181
http://blog.csdn.net/zyz511919766/article/details/50470070

mysql distinct 用法详解及优化
http://blog.csdn.net/aeolus_pu/article/details/7818559

join重复的时候可以用select distinct t.id join…

MySQL 三种关联查询的方式: ON vs USING vs 传统风格
https://www.oschina.net/question/12_60726

9. group by优化

避免文件排序。

10. LIMIT优化

order by后的字段---->加索引
where + order by的字段---->加复合索引

11. 索引优化

如何判断离散度呢(联合索引谁放前边)？

mysql> select count(distinct customer_id),count(distinct staff_id) from payment;

+-----------------------------+--------------------------+
| count(distinct customer_id) | count(distinct staff_id) |
+-----------------------------+--------------------------+
|                         599 |                        2 |
+-----------------------------+--------------------------+
1 row in set (0.02 sec)

12. 索引维护

---

数据库表结构的优化

1. 选择合适的数据类型

1.选择可以存下数据的最小数据类型
2.选择简单的类型。int比varchar简单
3.尽可能使用not null定义字段
4.尽量少用text类型，非用不可时考虑分表

示例1

用int存储时间

由于int最大21亿，只能存储到2038年。

mysql中的时间戳转化函数：

unix_timestamp(‘xxxx-xx-xx xx:xx:xx’) 得到int
from_unixtime(int)

mysql> create table mytest1( 
          id int auto_increment not null,    
          timestr int, 
          primary key(id) 
       ); 

mysql> insert into mytest1(timestr) 

mysql> select timestr from mytest1;
+------------+
| timestr    |
+------------+
| 1500527593 |
+------------+

mysql> select from_unixtime(timestr) from mytest1;     
+------------------------+
| from_unixtime(timestr) |
+------------------------+
| 2017-07-20 13:13:13    |
+------------------------+

示例2

bigint存储ip地址

转换函数：

inet_aton(ip) ip转换成整形
inet_ntoa(bigint) 转换成ip

mysql> create table mytest2(
    -> id int auto_increment not null,
    -> ipaddress bigint,
    -> primary key(id)
    -> );

mysql> insert into mytest2(ipaddress) values(inet_aton('192.168.0.1'));

mysql> select ipaddress from mytest2;
+------------+
| ipaddress  |
+------------+
| 3232235521 |
+------------+

mysql> select inet_ntoa(ipaddress) from mytest2;
+----------------------+
| inet_ntoa(ipaddress) |
+----------------------+
| 192.168.0.1          |
+----------------------+

2. 范式优化

范式：Normal Form

第一范式(1NF)

定义：如果关系模式R的每个关系r的属性都是不可分的数据项，那么就称R是第一范式的模式。
简单的说，每一个属性都是原子项，不可分割。

1NF是关系模式应具备的最起码的条件，如果数据库设计不能满足第一范式，就不称为关系型数据库。关系数据库设计研究的关系规范化是在1NF之上进行的。
例如(学生信息表)：

学生编号 姓名 性别 联系方式
20080901 张三 男 email:zs@126.com,phone:88886666
20080902 李四 女 email:ls@126.com,phone:66668888

以上的表就不符合，第一范式：联系方式字段可以再分，所以变更为正确的是：

学生编号 姓名 性别 电子邮件 电话
20080901 张三 男 zs@126.com 88886666
20080902 李四 女 ls@126.com 66668888

第二范式（2NF）

定义：如果关系模式R是1NF，且每个非主属性完全函数依赖于候选键，那么就称R是第二范式。
简单的说，第二范式要满足以下的条件：首先要满足第一范式，其次每个非主属性要完全函数依赖与候选键，或者是主键。也就是说，每个非主属性是由整个主键函数决定的，而不能由主键的一部分来决定。
例如(学生选课表):

学生 课程 教师 教师职称 教材 教室 上课时间
李四 Spring 张老师 java讲师 《Spring深入浅出》 ３０１ 08:00
张三 Struts 杨老师 java讲师 《Struts in Action》 ３０２ 13:30

这里通过（学生，课程）可以确定教师、教师职称，教材，教室和上课时间，所以可以把（学生，课程）作为主键。但是，教材并不完全依赖于（学生，课程），只拿出课程就可以确定教材，因为一个课程，一定指定了某个教材。这就叫不完全依赖，或者部分依赖。出现这种情况，就不满足第二范式。
修改后，选课表：

学生 课程 教师 教师职称 教室 上课时间
李四 Spring 张老师 java讲师 ３０１ 08:00
张三 Struts 杨老师 java讲师 ３０２ 13:30

课程表：

课程	教材
Spring	《Spring深入浅出》
Struts	《Struts in Action》

所以，第二范式可以说是消除部分依赖。第二范式可以减少插入异常，删除异常和修改异常。

第三范式（3NF）

定义：如果关系模式R是2NF，且所有非主属性对任何候选关键字都不存在传递依赖，则称关系R是属于第三范式。

简单的说，第三范式要满足以下的条件：首先要满足第二范式，其次非主属性之间不存在函数依赖。
由于满足了第二范式，表示每个非主属性都函数依赖于主键。如果非主属性之间存在了函数依赖，就会存在传递依赖，这样就不满足第三范式。

上例中修改后的选课表中，一个教师能确定一个教师职称。这样，教师依赖于（学生，课程），而教师职称又依赖于教师，这叫传递依赖。第三范式就是要消除传递依赖。
修改后，

选课表：

学生	课程	教师	教室	上课时间
李四	Spring	张老师	３０１	08:00
张三	Struts	杨老师	３０２	13:30

教师表：

教师	职称
张老师	java讲师
杨老师	java讲师

这样，新教师的职称在没被选课的时候也有地方存了，没人选这个教师的课的时候教师的职称也不至于被删除，修改教师职称时只修改教师表就可以了。
简单的说，

第一范式就是原子性，字段不可再分割；
第二范式就是完全依赖，没有部分依赖；
第三范式就是没有传递依赖。

第三范式的优化

3.垂直拆分

解决表宽度问题

拆分后：
表1：

表2：

4. 水平拆分