1. 优化目的
2. 数据
https://dev.mysql.com/doc/index-other.html
DVD租赁数据
3. 慢查询日志
http://www.cnblogs.com/kerrycode/p/5593204.html
![这里写图片描述](https://img-blog.csdn.net/20170714161555692?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvYW5zd2VyMTAwYW5zd2Vy/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
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
![这里写图片描述](https://img-blog.csdn.net/20170715224515832?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvYW5zd2VyMTAwYW5zd2Vy/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
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命令查看执行计划
![这里写图片描述](https://img-blog.csdn.net/20170716170558992?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvYW5zd2VyMTAwYW5zd2Vy/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
然后通过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深入浅出》 301 08:00
张三 Struts 杨老师 java讲师 《Struts in Action》 302 13:30
这里通过(学生,课程)可以确定教师、教师职称,教材,教室和上课时间,所以可以把(学生,课程)作为主键。但是,教材并不完全依赖于(学生,课程),只拿出课程就可以确定教材,因为一个课程,一定指定了某个教材。这就叫不完全依赖,或者部分依赖。出现这种情况,就不满足第二范式。
修改后,选课表:
学生 课程 教师 教师职称 教室 上课时间
李四 Spring 张老师 java讲师 301 08:00
张三 Struts 杨老师 java讲师 302 13:30
课程表:
课程 | 教材 |
---|---|
Spring | 《Spring深入浅出》 |
Struts | 《Struts in Action》 |
所以,第二范式可以说是消除部分依赖。第二范式可以减少插入异常,删除异常和修改异常。
第三范式(3NF)
定义:如果关系模式R是2NF,且所有非主属性对任何候选关键字都不存在传递依赖,则称关系R是属于第三范式。
简单的说,第三范式要满足以下的条件:首先要满足第二范式,其次非主属性之间不存在函数依赖。
由于满足了第二范式,表示每个非主属性都函数依赖于主键。如果非主属性之间存在了函数依赖,就会存在传递依赖,这样就不满足第三范式。
上例中修改后的选课表中,一个教师能确定一个教师职称。这样,教师依赖于(学生,课程),而教师职称又依赖于教师,这叫传递依赖。第三范式就是要消除传递依赖。
修改后,
选课表:
学生 | 课程 | 教师 | 教室 | 上课时间 |
---|---|---|---|---|
李四 | Spring | 张老师 | 301 | 08:00 |
张三 | Struts | 杨老师 | 302 | 13:30 |
教师表:
教师 | 职称 |
---|---|
张老师 | java讲师 |
杨老师 | java讲师 |
这样,新教师的职称在没被选课的时候也有地方存了,没人选这个教师的课的时候教师的职称也不至于被删除,修改教师职称时只修改教师表就可以了。
简单的说,
第一范式就是原子性,字段不可再分割;
第二范式就是完全依赖,没有部分依赖;
第三范式就是没有传递依赖。
第三范式的优化
3.垂直拆分
解决表宽度问题
拆分后:
表1:
表2:
4. 水平拆分