mysql数据库优化之表的设计和慢查询定位

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql数据库优化之表的设计和慢查询定位

jkxqj 2015-08-21 17:30:00 浏览1145
展开阅读全文
一、数据库优化包括的方面
数据库优化是一种综合性的技术,并不是通过某一种方式让数据库效率提高很多,而是通过多方面的提高,从而使得数据库性能提高。
主要包括:
1、表的设计合理化(3范式)
2、给表添加合适的索引,如何使用索引
3、分表技术(水平分割、垂直分割)
4、定时清除数据垃圾,定时碎片整理
5、多用存储过程和触发器
6、对mysql配置进行优化
7、读写分离
8、mysql服务器硬件升级。


二、数据库的设计
步骤:
1、收集信息:与该系统有关人员进行交流,充分了解数据库需要完成的任务
 
2、标识实体:具体存在的对象,名词。
比如:用户、帖子、回帖、板块

3、标识属性


4、标识关系:
  1)一对一:两个表的主键是公共字段
  2)一对多:主键和非主键建关系
  3)多对一:非主键和主键建关系
  4)多对多:非主键和非主键建关系
5、将E-R图转成表
  1)将实体转成表,将属性转成字段
  2)如果找不到一个合适的字段做主键,我们添加一个自动增长列做主键。
  3)建立正确的关系


三、数据规范化


表设计出来以后,并不是最合理的结构,我们需要对表进行规范化(我们通过3范式来对表进行规范化)
先满足第一范式--再满足第二范式--再满足第三范式


1NF: 确保每列的原子性
     第一范式用来规范化所有的字段,所有的字段都不可再分

注意:比如地址这个字段,如果不分类汇总、不排序,仅仅起一个字符串的作用,这时我们不拆(反3范式)


2NF: 非键字段必须依赖于主键字段

如果一个关系满足1NF,并且除了主键以外其他列都依赖于该主键,则满足第二范式(2NF)


3NF: 消除传递依赖

在非主键字段中,如果一个字段可以推导另一个字段,这就叫传递依赖。


四、规范化和性能的关系

1、为了满足某种商业目标,数据库性能比规范化数据库更重要

通过对给定的表中添加额外的字段,以大量减少需要从中搜索信息所需要的时间。

通过在给定的表中插入计算列(比如成绩总分),以方便查询

2、进行规范化的同时需要考虑数据库的性能


五、定位慢查询

找出查询速度较慢的SQL语句。

增、删、改、查

其中查询占90%,增删改共占10%

Mysql数据库一些关于状态的查询

用"show status"           

指令                                                                  说明

show status                                                   查询mysql数据库的一些运行状态

show status like 'com_insert';                    显示执行了多少次插入操作

show status like 'com_update';                 显示执行了多少次更新操作

show status like 'com_delete';                    显示执行了多少次删除操作

show status like 'com_select';                    显示执行了多少次查询操作

show status like 'uptime';                             显示mysql启动了多长时间,如果时间很长了,数据库表的存储引擎有的是myisam,这时候注意要碎片整理。


show [session | global ] status like...,如果不写,默认是会话级(session),想要查询从mysql启动一直到现在,用global。


显示慢查询

show variables like 'long_query_time'         显示慢查询的时间,默认是10秒

set long_query_time=0.5                               把慢查询的时间设置为0.5秒,以便于测试

show status like 'slow_queries'         显示慢查询的时间,默认是10秒


定位慢查询(开启慢查询日志)

一旦开启慢查询日志以后,日志文件的位置在my.ini文件中去查找,默认情况下mysql不会记录慢查询


如何开启慢查询:

1、关闭当前mysql服务:net stop mysql

2、通过安全模式启动就会写日志。

3、关闭安全模式启动的mysql

4、把慢查询的时间设置为0.5秒,set long_query_time=0.5       

5、select * from table where xx='00' ;

6、到慢查询日志中去找对应的SQL语句。


反三范式:

范式越高。数据冗余就越少,但是有时就效率就越低下,为了提高运行效率,可以适当的让数据冗余。









网友评论

登录后评论
0/500
评论