MySQL优化（1）

MySQL如何优化

（1）将表的设计合理化
（2）添加适当的索引

• 普通索引
• 主键索引
• 唯一索引
• 全文索引

（3）sql语句进行优化
（4）分表技术

• 水平分割
• 垂直分割

（5）读写分离技术
（6）存储过程，模块化的编程可以提高速度，但是不是很常用
（7）对mysql的配置进行优化，配置其最大的并发数my.ini，调整缓存大小
（8）nysql的服务器硬件升级
（9）定时的去清除不需要的数据，定时进行碎片整理

数据库设计

首先我们现在这里讲解下3NF。数据库的三范式。

• 什么是数据库范式

为了建立冗余较小，数据结构合理的数据库，设计数据库的时候必须遵循一定的规则。在关系型数据库中这种规则就成为范式。范式是复合某一种设计要求的总结。

简单来说数据库的范式就是数据库设计的规则

• 数据库的三大范式

说起范式，肯定要提一句数据库的三大范式。

1. 第一范式：

1NF是对属性的原子性的约束，要求属性（列）具有原子性，不可拆分，从业务的角度上来说，当业务觉得字段不可拆分即是原子性的，不可进行拆分，只要是关系型数据库其实都是满足1NF的

2. 第二范式：

2NF是对记录的唯一性约束，表中的记录是唯一的，就满足2NF，通常我们是设计一个主键来实现的，但是主键不能包含业务逻辑（如同订单表，订单id与业务逻辑无关，id是id，订单号是订单号）

3. 第三范式：

3NF是对字段的冗余性的约束，他要求字段没有完全冗余。
举例说明：
当我们有学生表和班级表的时候，我们不应该在学生表存储班级的相关信息，而是应该用班级的id去查询班级的信息，冗余字段就是指，学生表出现了本可以通过班级id查询出来的班级表的字段，比如，是否是重点班，班级名称等信息。

注意一点：

没有冗余的数据库未必是最好的数据库，有的时候为了提高运行效率，就必须降低范式标准，适当的保留冗余的数据，具体的做法是：在概念设计的时候遵守第三范式，减低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。
举例说明：
有学生表和班级表，增加冗余字段的意思就是，将班级表部分的字段放到学生表中，这样子可以避免连表查询，提高了查询效率。

分表分库

分库分表主要是分为垂直拆分和水平拆分。

• 垂直拆分

有点类似微服务的设计理念，垂直拆分就是要把表按照模块划分到不同的数据库中（原则还是不破坏第三范式）。垂直切分更进一步的是服务化改造，就是将耦合的服务拆分成多个弱耦合的服务。

垂直拆分适用于分布式的场景

• 水平拆分

上面说到的垂直切分只是把表按照模块，功能划分到不同的数据库中，但是垂直切分依然没有解决单表大数据库量的问题。
水平切分就是解决单表大数据量的问题，水平切分就是把一个表按照某种规则把表的数据划分到不同的表或者数据库中。
例如：计费系统，通过时间划分表就比较合适；一般情况下，采用取模的方式进行拆分。

• 举例说明

1. 建立三张表（user0, user1, user2）
   （1）这三张表表示了我们将要对数据进行水平分割，按照某种规则进行拆分数据，将不同的规则的数据存到不同的表中。

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;

2. 建立一张中间表
   （1）该表是用来存储id的，因为我们的id需要先确定，确定过后将id取模。借此才能判断插入到哪一个表中。

CREATE TABLE uuid(
	id INT UNSIGNED PRIMARY KEY auto_increment
) ENGINE = myisam charset utf8;

（2）该表的id设置为自增，每次要获取的时候应该先insert null，然后再取最新的id

insert into uuid VALUES(null);
select * from uuid;

结果是：

3. 创建工程，进行试验
（1）新建springboot项目，导入pom文件

	<dependencies>
		<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>
	</dependencies>

（2）配置application.properties文件

server.port=8081


spring.datasource.url=jdbc:mysql://localhost:3307/test_rc
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

（3）service层

package com.xiyou.redis.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public class UserService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 插入一条数据到表中
     * @param name
     * @param pwd
     * @return
     */
    public String myInsert(String name, String pwd){
        // 先获取自增长的id，从uuid表中
        String sql = "insert into uuid values(null);";
        jdbcTemplate.update(sql);
        // 执行查询语句，得到新增的id
        // last_insert_id 自动返回最后一个insert或者update，询问自增长列设置的第一个发生变化的值，
        Long uuid = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
        // 判断待插入的表名，用取模的方式
        String tableName = "user" + uuid % 3;
        String insertSql = "insert into " + tableName + " VALUES ('" + uuid + "','" + name + "','" + pwd + "');";
        System.out.println(insertSql);
        jdbcTemplate.update(insertSql);
        return "success";
    }

    /**
     * 根据id查询数据
     * @param id
     * @return
     */
    public String get(Long id){
        // 根据id确定表名
        String tableName = "user" + id % 3;
        String sql = "select name from " + tableName + "  where id="+id;
        System.out.println(sql);
        String name = jdbcTemplate.queryForObject(sql, String.class);
        return name;
    }

}

（4）controller层

package com.xiyou.redis.controller;

import com.xiyou.redis.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @GetMapping("/insert/{name}/{pwd}")
    public String insert(@PathVariable String name, @PathVariable String pwd){
        return userService.myInsert(name, pwd);
    }

    @GetMapping("/get/{id}")
    public String get(@PathVariable Long id){
        return userService.get(id);
    }
}

（5）结果显示：

SQL优化

• 查看MySQL服务器的状态信息

我们通常使用show status命令来查看MySQL的服务器状态信息。
常用命令：

命令	含义
show status like ‘uptime’;	查看mysql数据库启动用了多长时间
show status like ‘com_select’	查看数据库的查询的次数，若想查看添加没更改删除的次数改为com_insert/com_update/com_delete等即可
show [session ! global ] status like…	这个命令主要介绍参数 session和global，我们默认使用session参数，指的是当前窗口的范围执行show status命令；如果你想看所有的执行，需要显示指明其为global
show status like ‘connections’	显示到mysql的数据库的连接数
show status like ‘slow_queries’	显示慢查询的次数

慢查询

• 什么是慢查询

MySQL默认10s内没有响应sql的结果，则为慢查询。我们也可以去修改慢查询的默认时间。

• 如何修改慢查询

（1）查询慢查询的时间

show variables like 'long_query_time';

（2）修改慢查询的时间

set long_query_time=1;

注意，这里可以修改慢查询的时间，单位是s，但是重启之后，long_query_time依然是my.ini中的值，该文件中默认是10s

• 如何将慢查询定位到日志中

在默认情况下，我们的mysql不会记录慢查询，需要在启动mysql的时候，指定记录慢查询才可以。

（1）指定日志文件位置：
打开my.ini文件，修改日志文件位置

#Path to the database root
datadir=" C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

或者可以直接启动的时候指定

bin\mysqld.exe –log-slow-queries=d:/abc.log 

（2）在当前mysql的安装路径上打开dos窗口，安全模式启动，将数据库的操作写入日志

bin\mysqld.exe --safe-mode  --slow-query-log