mysql 简单优化总结---one

-------------mysql优化准备篇--------------

一.建表 

表名：emp（员工表）

empnoenamejobsaldept

二.插入海量数据，为测试做准备

定义下面的函数来随机生成字段值

定义一个新的命令结束符合 delimiter $$

随机生成字符串

create function rand_string(n INT)returns varchar(255)begin  declare chars_str varchar(100) default   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; declare return_str varchar(255) default ''; declare i int default 0; while i < n do    set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));   set i = i + 1;   end while;  return return_str;  end $$

随机生成部门编号

create function rand_num( )returns int(5)begin  declare i int default 0; set i = floor(10+rand()*500);return i;  end $$

向emp表中插入记录(海量的数据)

create procedure insert_emp(in start int(10),in max_num int(10))begindeclare i int default 0;  set autocommit = 0;   repeat set i = i + 1; insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());  until i = max_num end repeat;   commit; end $$</span>

将结束命令符号改回； delimiter ;  即可

下面只需调用insert_emp即可，1800000条记录，从100001号开始

call insert_emp(100001,1800000);

因为涉及到myisam 和 innodb 二种数据库引擎，所以建了二张emp表(数据库引擎不一样)进行对比，另一个表的建立过程不再说

至此准备工作搞定。。。。

-----------mysql优化--------------

数据库优化通常在以下几个方面：

第一部分：数据库的设计

在数据库设计方面，主要是遵循三范式。

第一范式（1NF）无重复的列：

 所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，不可再分解。(只要是关系型数据库都满足1NF) 

第二范式（2NF）属性完全依赖于主键[消除部分子函数依赖]

比如有这样一张表(学号，学生姓名、年龄、性别、课程、课程学分、系别、学科成绩，系办地址、系办电话)(红色字为主键)

则存在如下的依赖关系：

  (学号)→ (姓名, 年龄，性别，系别，系办地址、系办电话)      

    (课程名称) → (学分)          

  (学号，课程)→ (学科成绩)

 很明显有部分依赖，所以不符合第二范式。那么它会带来什么问题呢，让我们一起来分析下：

 数据冗余：如果一个学生选了N门课，那么学生信息就要重复了n-1次，如果一门课程被N个学生选修，那么课程的信息就要重复n-1次。

修改繁琐：如果一个学生或课程的信息发生改变，那么所有行的相应信息都要修改，比较繁琐(无用功)。

另外如果要加一个新的课程信息，由于还没有学生选课，没有学号关键字，课程信息无法录入。

删除问题：如果有些学生完成修课，则需要从数据库中删除学生信息，那么课程信息也会跟着删除，这就违背了我们的初衷。

解决方法就是将上面一个表改成下面三个

  (学号)→ (姓名, 年龄，性别，系别，系办地址、系办电话)      

  (课程名称) → (学分)          

  (学号，课程)→ (学科成绩)

第三范式（3NF）属性不依赖于其它非主属性[消除传递依赖]

举例说明：还是上面那张表，对于依赖关系(学号)→ (姓名, 年龄，性别，系别，系办地址、系办电话)        

仍存在下面的决定关系   (学号) → (所在学院)→(学院地点, 学院电话) 

即存在非关键字段"学院地点"、"学院电话"对关键字段"学号"的传递函数依赖。

因此不满足第三范式的要求了。(即存在非关键字段"系办地址"、"系办电话"对关键字段"学号"的传递函数依赖。 )

解决办法：分成二个表 学生：(学号, 姓名, 年龄, 性别，系别)；   系别：(系别, 系办地址、系办电话)。 

注：但是，没有冗余的数据库未必是最好的设计，有时为了提高运行效率，必需降低范式标准，适当保留数据冗余 。具体做法：在概念模型数据库设计时，遵循第三范式，降低范式标准的工作放到物理数据模型设计师考虑。降低范式就是增加字段，允许冗余。

mysql -u root -p -h localhost      然后输入

第二部分：sql语句优化

1.通过show status 命令 查看sql语句的执行情况(频率)

net  start mysql 启动mysql 服务(注：此命令后面不要加 ；否则不能执行)

启动mysql服务后，用客户端连接mysql服务，也可以直接在windows 命令行连接mysql服务，

mysql -u root -p -h localhost    之后输入mysql数据库的密码即可，上截图：

下面用show [session | global] status 命令来了解sql语句的执行频率。

session代表当前连接的统计结果，global代表自数据库上次启动以来的统计结果。

show status  like 'Com_XXX'; Com_XXX表示XXX语句执行的次数。

我们重点关注com_select,com_insert,com_update,com_delete,通过这几个数据我们可以很容易的知道数据库的应用是以插入更新为主还是查询为主(注：当然这都是建立在已经跑的系统上，我的这个只是演示)。

我们还可以通过以下一个参数了解数据库的进本情况

show status like  'connections'; 试图连接mysql的次数

show status like 'uptime';服务器工作的时间

show status like 'slow_queries'; 查看慢查询的次数(默认超过十秒一次会记录一次)

下面说下定位慢查询

2.定位慢查询

在默认情况下，mysql并不提供慢查询的日志记录，所以要在配置文件my.ini中[mysqld]节点下指定下面配置(我的mysql安装路径C:\Program Files\MySQL\MySQL Server 5.5\my.ini)，

#慢查询日志有关

#查询日志存放路径
log-slow-queries = C:\Program Files\MySQL\MySQL Server 5.5\log\slow-queries.log

#慢查询时间(2秒，超过就会记录在慢查询日志中)
long_query_time = 2

然后重新启动mysql，这样就可以用慢查询日志的功能了。

那么怎么用慢查询的日志定位慢查询呢？？

select * from emp where empno=456789;

然后打开慢查询日志文件，

我们可以看到这里面记录了慢查询的sql语句和查询所用时间等等一些信息。

另外可以通过这些语句查看慢查询的情况：

show status like 'slow_queries'; 查看慢查询的次数(默认超过十秒一次会记录一次)

show variables like 'long_query_time'; 查看慢查询的时间限制(默认为10秒)

set long_query_time = 2; 将慢查询的记录时间设置为2秒（在mysql5.21+后版本支持毫秒记录）

你会发现很简单我们就找到了慢查询的语句，那么接下来。。。

3.通过explain分析低效率的sql语句的执行情况

只需要在要分析的语句前面加上explain，会出现如下展示。

select_type:表示查询的类型，(简单的select,没使用union或子查询)

table: 返回结果集的表

type: 表示查询的方式(all: 全表扫描，效率比较低)

possible_keys: 可能用到的索引(为空说明没有索引)

key：实际用到索引

key_len: 索引字段的长度

ref: 显示那个字段或常数和key一起使用。

rows:扫描的行数

extra: 

如果是Only index ,这意味着信息只用到索引树中的信息检索出来的，这比扫描整个表要快。

如果是where used/using where, 就是使用上了where限制。

如果是impossible where 表示用不着where,一般就是没查出来啥。

如果此信息显示Using filesort或者Using temporary的话，说明很可能需要优化。

在使用order by关键字的时候，如果待排序的内容不能由所使用的索引直接完成排序的话，那么mysql有可能就要进行文件排序。

此时，可以进行的优化：
①修改逻辑，不在mysql中使用order by而是在应用中自己进行排序。
②使用mysql索引，将待排序的内容放到索引中，直接利用索引的排序。