MySQL优化

数据库如何进行优化

　　数据库设计规范（三大范式）
　　添加索引（普通索引，唯一索引，全文索引等等）
　　分库分表（垂直分割和水平分割）
　　读写分离
　　存储过程
　　配置MySQL连接参数
　　SQL语句优化
　　清理碎片化
　　mysql服务器优化

数据库设计规范（三大范式）

　　第一范式：保证每一个列的原子性，不可再分
　　第二范式: 在满足第一范式基础之上，保证表中数据的唯一，每一个列都和主键相关，一张表只描述一件事情
　　第三范式：在满足第二范式基础之上，保证表中每一个列都和主键直接相关，而不是间接相关
　　在实际开发场景当中，根据业务需求来定，没有必要非得严格按照三大范式

分库分表：mycat
　　什么时候分库：垂直分割，专库专用
　　分布式项目，每一个模块是一个工程，进行分库，一个数据库对应一个模块
　　优点：
　　　　拆分之后业务更加清晰，拆分规则更加明确
　　　　系统之间整合或者扩展更加方便
　　　　数据维护简单
　　缺点：
　　　　如果遇到关联，维护起来比较麻烦
　　　　受各种业务限制
　　什么时候分表：单张表数据量过大的情况下，进行水平分割，（取模算法）
　　优点：
　　　　每一张表不存在大量的数据
　　　　对应用透明
　　　　按照合理的查新规则进行拆分，避免夸库
　　　　提高了系统的负载能力
　　缺点：
　　　　拆分规则不好确定
　　　　对事务一致性难以解决
　　　　分页不好做

定位慢查询

CREATE TABLE user0(
id INT UNSIGNED PRIMARY KEY ,
NAME VARCHAR(32) NOT NULL DEFAULT '',
pwd  VARCHAR(32) NOT NULL DEFAULT '')
ENGINE=MYISAM CHARSET utf8;

CREATE TABLE user1(
id INT UNSIGNED PRIMARY KEY ,
NAME VARCHAR(32) NOT NULL DEFAULT '',
pwd  VARCHAR(32) NOT NULL DEFAULT '')
ENGINE=MYISAM CHARSET utf8;

CREATE TABLE user2(
id INT UNSIGNED PRIMARY KEY ,
NAME VARCHAR(32) NOT NULL DEFAULT '',
pwd  VARCHAR(32) NOT NULL DEFAULT '')
ENGINE=MYISAM CHARSET utf8;
CREATE TABLE UUID(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT)ENGINE=MYISAM CHARSET utf8;







SHOW VARIABLES LIKE 'long_query_time';


SHOW STATUS LIKE 'slow_queries';


SET long_query_time=1


/*部门表*/
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 ;
/*员工表*/
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 DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
)ENGINE=MYISAM DEFAULT CHARSET=utf8 ;




/*薪水*/
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;



DELIMITER $$
CREATE FUNCTION rand_string(n INT) 
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN 
#chars_str定义一个变量 chars_str,类型是 varchar(100),默认值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 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 $$




DELIMITER $$
CREATE FUNCTION rand_num()
RETURNS INT(5)
BEGIN
 DECLARE i INT DEFAULT 0;
 SET i =FLOOR(10+RAND()*500);
 RETURN i;
END $$


DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0; 
#set autocommit =0 把autocommit设置成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 $$

CALL insert_emp (101002,40000000);  

SELECT * FROM emp

 数据库如何进行优化

　　数据库设计规范（三大范式）
　　添加索引（普通索引，唯一索引，全文索引等等）
　　分库分表（垂直分割和水平分割）
　　读写分离
　　存储过程
　　配置MySQL连接参数
　　SQL语句优化
　　清理碎片化
　　mysql服务器优化

数据库设计规范（三大范式）

 

　　第一范式：保证每一个列的原子性，不可再分
　　第二范式: 在满足第一范式基础之上，保证表中数据的唯一，每一个列都和主键相关，一张表只描述一件事情
　　第三范式：在满足第二范式基础之上，保证表中每一个列都和主键直接相关，而不是间接相关
　　在实际开发场景当中，根据业务需求来定，没有必要非得严格按照三大范式

分库分表：mycat
　　什么时候分库：垂直分割，专库专用
　　分布式项目，每一个模块是一个工程，进行分库，一个数据库对应一个模块
　　优点：
　　　　拆分之后业务更加清晰，拆分规则更加明确
　　　　系统之间整合或者扩展更加方便
　　　　数据维护简单
　　缺点：
　　　　如果遇到关联，维护起来比较麻烦
　　　　受各种业务限制
　　什么时候分表：单张表数据量过大的情况下，进行水平分割，（取模算法）
　　优点：
　　　　每一张表不存在大量的数据
　　　　对应用透明
　　　　按照合理的查新规则进行拆分，避免夸库
　　　　提高了系统的负载能力
　　缺点：
　　　　拆分规则不好确定
　　　　对事务一致性难以解决
　　　　分页不好做

定位慢查询

CREATE TABLE user0(
id INT UNSIGNED PRIMARY KEY ,
NAME VARCHAR(32) NOT NULL DEFAULT '',
pwd  VARCHAR(32) NOT NULL DEFAULT '')
ENGINE=MYISAM CHARSET utf8;

CREATE TABLE user1(
id INT UNSIGNED PRIMARY KEY ,
NAME VARCHAR(32) NOT NULL DEFAULT '',
pwd  VARCHAR(32) NOT NULL DEFAULT '')
ENGINE=MYISAM CHARSET utf8;

CREATE TABLE user2(
id INT UNSIGNED PRIMARY KEY ,
NAME VARCHAR(32) NOT NULL DEFAULT '',
pwd  VARCHAR(32) NOT NULL DEFAULT '')
ENGINE=MYISAM CHARSET utf8;
CREATE TABLE UUID(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT)ENGINE=MYISAM CHARSET utf8;







SHOW VARIABLES LIKE 'long_query_time';


SHOW STATUS LIKE 'slow_queries';


SET long_query_time=1


/*部门表*/
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 ;
/*员工表*/
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 DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
)ENGINE=MYISAM DEFAULT CHARSET=utf8 ;




/*薪水*/
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;



DELIMITER $$
CREATE FUNCTION rand_string(n INT) 
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN 
#chars_str定义一个变量 chars_str,类型是 varchar(100),默认值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 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 $$




DELIMITER $$
CREATE FUNCTION rand_num()
RETURNS INT(5)
BEGIN
 DECLARE i INT DEFAULT 0;
 SET i =FLOOR(10+RAND()*500);
 RETURN i;
END $$


DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0; 
#set autocommit =0 把autocommit设置成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 $$

CALL insert_emp (101002,40000000);  

SELECT * FROM emp

数据库如何进行优化

　　数据库设计规范（三大范式）
　　添加索引（普通索引，唯一索引，全文索引等等）
　　分库分表（垂直分割和水平分割）
　　读写分离
　　存储过程
　　配置MySQL连接参数
　　SQL语句优化
　　清理碎片化
　　mysql服务器优化

数据库设计规范（三大范式）

 

　　第一范式：保证每一个列的原子性，不可再分
　　第二范式: 在满足第一范式基础之上，保证表中数据的唯一，每一个列都和主键相关，一张表只描述一件事情
　　第三范式：在满足第二范式基础之上，保证表中每一个列都和主键直接相关，而不是间接相关
　　在实际开发场景当中，根据业务需求来定，没有必要非得严格按照三大范式

分库分表：mycat
　　什么时候分库：垂直分割，专库专用
　　分布式项目，每一个模块是一个工程，进行分库，一个数据库对应一个模块
　　优点：
　　　　拆分之后业务更加清晰，拆分规则更加明确
　　　　系统之间整合或者扩展更加方便
　　　　数据维护简单
　　缺点：
　　　　如果遇到关联，维护起来比较麻烦
　　　　受各种业务限制
　　什么时候分表：单张表数据量过大的情况下，进行水平分割，（取模算法）
　　优点：
　　　　每一张表不存在大量的数据
　　　　对应用透明
　　　　按照合理的查新规则进行拆分，避免夸库
　　　　提高了系统的负载能力
　　缺点：
　　　　拆分规则不好确定
　　　　对事务一致性难以解决
　　　　分页不好做

定位慢查询

CREATE TABLE user0(
id INT UNSIGNED PRIMARY KEY ,
NAME VARCHAR(32) NOT NULL DEFAULT '',
pwd  VARCHAR(32) NOT NULL DEFAULT '')
ENGINE=MYISAM CHARSET utf8;

CREATE TABLE user1(
id INT UNSIGNED PRIMARY KEY ,
NAME VARCHAR(32) NOT NULL DEFAULT '',
pwd  VARCHAR(32) NOT NULL DEFAULT '')
ENGINE=MYISAM CHARSET utf8;

CREATE TABLE user2(
id INT UNSIGNED PRIMARY KEY ,
NAME VARCHAR(32) NOT NULL DEFAULT '',
pwd  VARCHAR(32) NOT NULL DEFAULT '')
ENGINE=MYISAM CHARSET utf8;
CREATE TABLE UUID(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT)ENGINE=MYISAM CHARSET utf8;







SHOW VARIABLES LIKE 'long_query_time';


SHOW STATUS LIKE 'slow_queries';


SET long_query_time=1


/*部门表*/
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 ;
/*员工表*/
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 DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
)ENGINE=MYISAM DEFAULT CHARSET=utf8 ;




/*薪水*/
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;



DELIMITER $$
CREATE FUNCTION rand_string(n INT) 
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN 
#chars_str定义一个变量 chars_str,类型是 varchar(100),默认值'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 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 $$




DELIMITER $$
CREATE FUNCTION rand_num()
RETURNS INT(5)
BEGIN
 DECLARE i INT DEFAULT 0;
 SET i =FLOOR(10+RAND()*500);
 RETURN i;
END $$


DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0; 
#set autocommit =0 把autocommit设置成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 $$

CALL insert_emp (101002,40000000);  

SELECT * FROM emp