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

一、数据库优化包括的方面
数据库优化是一种综合性的技术，并不是通过某一种方式让数据库效率提高很多，而是通过多方面的提高，从而使得数据库性能提高。
主要包括：
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语句。

反三范式：

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