Mysql优化专题
来源:互联网 发布:福州大学软件学院 编辑:程序博客网 时间:2024/06/07 05:26
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 -hUsage: mysqldumpslow [ OPTS... ] [ LOGS... ]
[root@master-1a mysql]# mysqldumpslow -t 50 /var/lib/mysql/master-1a-slow.logReading mysql slow query log from /var/lib/mysql/master-1a-slow.logCount: 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 NDied 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深入浅出》 301 08:00
张三 Struts 杨老师 java讲师 《Struts in Action》 302 13:30
这里通过(学生,课程)可以确定教师、教师职称,教材,教室和上课时间,所以可以把(学生,课程)作为主键。但是,教材并不完全依赖于(学生,课程),只拿出课程就可以确定教材,因为一个课程,一定指定了某个教材。这就叫不完全依赖,或者部分依赖。出现这种情况,就不满足第二范式。
修改后,选课表:
学生 课程 教师 教师职称 教室 上课时间
李四 Spring 张老师 java讲师 301 08:00
张三 Struts 杨老师 java讲师 302 13:30
课程表:
所以,第二范式可以说是消除部分依赖。第二范式可以减少插入异常,删除异常和修改异常。
第三范式(3NF)
定义:如果关系模式R是2NF,且所有非主属性对任何候选关键字都不存在传递依赖,则称关系R是属于第三范式。
简单的说,第三范式要满足以下的条件:首先要满足第二范式,其次非主属性之间不存在函数依赖。
由于满足了第二范式,表示每个非主属性都函数依赖于主键。如果非主属性之间存在了函数依赖,就会存在传递依赖,这样就不满足第三范式。
上例中修改后的选课表中,一个教师能确定一个教师职称。这样,教师依赖于(学生,课程),而教师职称又依赖于教师,这叫传递依赖。第三范式就是要消除传递依赖。
修改后,
选课表:
教师表:
这样,新教师的职称在没被选课的时候也有地方存了,没人选这个教师的课的时候教师的职称也不至于被删除,修改教师职称时只修改教师表就可以了。
简单的说,
第一范式就是原子性,字段不可再分割;第二范式就是完全依赖,没有部分依赖;第三范式就是没有传递依赖。
第三范式的优化
3.垂直拆分
解决表宽度问题
拆分后:
表1:
表2:
4. 水平拆分
- MySQL 优化综述专题
- Mysql优化专题
- MySQL数据库性能优化专题(转载,全部是URL)
- 「mysql优化专题」90%的程序员都二话不说收藏!本专题终极总结!(13)
- mysql专题
- 「mysql优化专题」这大概是一篇最好的mysql优化入门文章(1)
- 「mysql优化专题」这大概是一篇最好的mysql优化入门文章(1)
- MySQL 数据库性能优化之SQL优化(这是 MySQL数据库性能优化专题 系列的第四篇文章)
- MySQL 数据库性能优化之索引优化(这是 MySQL数据库性能优化专题 系列的第三篇文章)
- MySQL 数据库性能优化之表结构优化(这是 MySQL数据库性能优化专题 系列的第二篇文章)
- MySQL 数据库性能优化之缓存参数优化(这是 MySQL数据库性能优化专题 系列的第一篇文章)
- 「mysql优化专题」90%程序员都会忽略的增删改优化(2)
- 【转】【mysql的设计与优化专题】mysql的最佳索引攻略
- mysql 【mysql的设计与优化专题】表的垂直拆分和水平拆分
- 「mysql优化专题」高可用性、负载均衡的mysql集群解决方案(12)
- Fedora 16 ... 优化专题
- Twisted服务器优化专题
- 绘图性能优化专题
- linux tar命令简介
- (66)ArrayList练习:自定义对象存入ArrayList,去除重复元素。迭代器指针移动解释
- Git使用详细教程
- <TensorFlow Machine Learning Cookbook>学习笔记(1.2)
- maven的resources介绍
- Mysql优化专题
- 【洛谷1640】[SCOI2010]连续攻击游戏
- JVM类加载原理学习笔记
- 补题补题补题~~~~really big numbers
- 选择算法
- 编译内核提示mkimage command not found – U-Boot images will not be built
- Java垃圾收集学习笔记
- 华为机试:计算字符串的距离、24点游戏算法
- Http状态码