mysql数据库优化方法_mysql数据库优化的常见方法

mysql数据库优化的常见方法

1.表的设计要合理符合3NF

2.创建适当索引

3.对SQL语句优化----定位慢查询

4.使用分表技术(重点[水平分表,垂直分表]),分区技术(了解)

5.读写分离

6.创建适当存储过程,函数,触发器

7.对my.ini优化,优化配置

8.软件硬件升级

表的设计要合理符合3NF

1NF

指表的属性具有原子性,即表的列不能再分了

不能有重复的列

特点:只要是关系型数据库就满足1NF

关系型数据库:mysql,oracle,sql,server,infomix,db2,postgres

非关系型数据库:Nosql数据库:Redis,MongoDB

2NF

指我们的表中不能有完全重复的一条记录,一般情况下通过设置一个主键自增来搞定

3NF

指如果列的内容可以推导(显示推到|隐式推导)出来,那么我们就不要单独的用一列存放

例子

一个学生表 存放了id,名字,性别,邮箱,班级,科系 如果在下一行班级和科系重复,那么会造成冗余

应独立出一张班级表进行关联

反3NF

在通常情况下,我们的表的设计要严格的遵守3NF,但也有例外,反而会提高查询的效率

例子

相册表 id name

图片表 id name 浏览次数 所属相册

由于要求相册页面出现时显示浏览次数,所以不得不将所有的图片的浏览次数总和查询出来,但这样效率太低

在相册表加入总浏览次数,浏览图片时进行更新操作,增加一个触发器效率会提高

想知道怎么做快,实际测试打印出时间就可以了

创建适当索引

构建海量表,定位慢查询

构建一个海量表,而且每条数据不一样,要是用存储过程

1.创建数据库 CREATE DATABASE testdb;

2.创建部门表 CREATE TABLE dept(

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

dname VARCHAR(20) NOT NULL DEFAULT '',

loc VARCHAR(13) NOT NULL DEFAULT ''

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

3.创建雇员表 CREATE TABLE emp(

empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

ename VARCHAR(20) NOT NULL DEFAULT '',

job VARCHAR(9) NOT NULL DEFAULT '',

mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

hiredate DATE NOT NULL,

sal DECIMAL(7,2) NOT NULL,

comm ECIMAL(7,2) NOT NULL,

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

4.创建工资表 CREATE TABLE salgrade(

grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

losal DECIMAL(17,2) NOT NULL,

hisal DECIMAL(17,2) NOT NULL

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

5.改变命令结束符 delimiter $$

6.随机创建字符函数 CREATE FUNCTION rand_string(n INT)

RETURNS VARCHAR(255)

BEGIN

DECLARE CHARS_STR VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

DECLARE return_str VARCHAR(255) DEFAULT '';

DECLARE I INT DEFAULT 0;

while i

SET return_str=concat(return_str,substring(chars_str,floor(1+rand()*52),1));

SET i=i+1;

END while;

RETURN return_str;

end $$

7.随机创建部门编号 CREATE FUNCTION rand_num()

RETURNS INT(5)

BEGIN

DECLARE i INT DEFAULT 0;

SET i=floor(10+rand()*500);

RETURN i;

END $$

8.随机添加雇员 CREATE procedure insert_emp(in start int(10),in max_num int(10))

BEGIN

DECLARE 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 $$

9.调用添加雇员 call insert_emp(100001,8000000)$$

10.数据库数据文件类型

frm 创建表结构 MYD 表数据 MYI 表索引

11.向emp表添加索引后,查询的速度会提升,但我们不能在每个表都添加索引

定位慢查询

1.开启慢查询

1.cd wamp64\bin\mysql5.5.7\

2.mysqld.exe --safe--mod --slow-query-log

3.mysql的变量和查询

show variables like 'long_query_time'

2.记录慢查询

# Time: 2017-12-14T05:48:45.118935Z

# User@Host: root[root] @ localhost [::1] Id: 2

# Query_time: 1.410054 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 8000000

use testdb;

SET timestamp=1513230525;

SELECT * FROM emp WHERE job='AAA';

Query_time:查询时间

Lock_time:等待时间

3.如何分析一个sql语句的问题-explain工具

基本用法 explain sql\G;