优化MySQL

于 2020-02-19 13:46:20 发布
阅读量111 收藏
点赞数
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/qq_43868329/article/details/104391322

优化MySQL

1.三大范式

第一范式：保证每一列的原子性，不可再分

第二范式：在第一范式的前提下，保证一张表只描叙一件事情，每个列都和主键相关，一张表只描叙一件事情

第三范式：在第二范式的前提下，保证每个列都和主建直接相关

2.分库分表：mycat
分布式项目，每一个模块是一个工程，进行分库，一个数据库对应一个模块
优点：
1.拆分之后业务更加清晰，拆分规则更加明确
2.系统之间整合或者扩展更加方便
3.数据维护简单
缺点：
1.如果遇到关联，维护起来比较麻烦
2.受各种业务限制
分表
在单张表数据过多的情况下进行水平分割（取模算法）,简单说就是一个表的用户数据太多，在创建几个相同的表把数据平摊下去
创建表

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;
复制代码

创建springboot工程
导入依赖

//toString
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.4.1.RELEASE</version>
  </parent>
 <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

创建LevelService

@Service
public class LevelService {
    @Resource
    private JdbcTemplate jdbcTemplate;

    //用户注册的方法
    public String register(String name,String password){
        //向UUID表中插入数据
        jdbcTemplate.update("insert  into uuid values (null)");
        //取模算法
        //获取插入数据的id
        Integer id = jdbcTemplate.queryForObject("select last_insert_id()", Integer.class);
        //计算表
        String tableName="user"+id%3;
        //向分表中插入数据
        jdbcTemplate.update("insert into "+tableName+" values ('"+id+"','"+name+"','"+password+"')");
        return "success";
    }

    public String getUser(Integer id){
        String tableName="user"+id%3;
        return jdbcTemplate.queryForObject(("select name from "+tableName),String.class);
    }

}

创建LevelController


@RestController
public class LevelController {
    @Resource
    private LevelService levelService;


    @RequestMapping("/register")
    public String register(String name,String password){
        return levelService.register(name,password);
    }

    @RequestMapping("/get")
    public String get(Integer id){
        return levelService.getUser(id);
    }
}

创建application.yml

spring:
       datasource:
            driver-class-name: com.mysql.jdbc.Driver
            url: jdbc:mysql://localhost:3306/levelef
            username: root
            password: root

运行结果
在这里插入图片描述
3.定位慢查询

		定位到查询慢的SQL语句，MySQL默认认为慢查询时间为1s
		##查询慢查询时间,mysql默认1s
		SHOW VARIABLES LIKE 'long_query_time';
		##查询慢查询的次数
		SHOW STATUS LIKE 'slow_queries';
		##怎么定位慢查询语句，启动慢查询日志
		##1.准备慢查询时间，修改慢查询时间尽量1s      set long_query_time=1;   修改my.ini文件（C:\ProgramData\MySQL\MySQL Server 5.7）
		##2.开启慢查询日志：mysql5.7版本默认就是开启，所以在此更改时间和日志路径即可
			##慢查询生成日志时间
			#slow-query-log=1
			#日志文件
			#slow_query_log_file="D:\\show-query.log"
			##慢查询时间
			#long_query_time=1
		##3.更改完毕后重启服务器
		##4.准备数据
		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 (100001,40000000);

什么是慢查询
　　mysql记录下查询超过指定时间的语句，被称为“慢查询”；
　　启动慢查询日志
1.查询是否把索引的SQL记录到慢查询日志中

SHOW VARIABLES LIKE 'log_queries_%'

在这里插入图片描述
查询到的数据如果为OFF，表示没有数据

SET GLOBAL log_queries_not_using_indexes=ON;　

查看慢查询时间long_query_time，

SHOW VARIABLES LIKE 'long_query_time';

在这里插入图片描述
默认查询时间为10s
将查询时间改成1秒

SET GLOBAL long_query_time=1;

查看是否开启慢查询日志

SET GLOBAL slow_query_log=ON;

如果为OFF就是没有开启，ON表示开启

在这里插入图片描述
查看日志记录位置

SHOW VARIABLES LIKE 'slow_query_log_file';

修改日志路径

SET GLOBAL slow_query_log_file='E:\MySql\日志'

案例慢查询

/*部门表*/
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;

#测试数据leveof
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

　　　　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 (100001,40000000);

执行慢查询

SELECT * FROM emp WHERE empno=100003

在这里插入图片描述

TWOFOUR_
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值