MySQL性能优化

一、 为什么要进行数据库优化

　　1、 避免网站页面出现访问错误

　　　　  由于数据库连接timeout产生页面5xx错误

　　　　由于慢查询造成页面无法加载

　　　　由于阻塞造成数据无法提交

　　2、 增加数据库的稳定性

　　　　  很多数据库问题都是由于低效的查询引起的

　　3、 优化用户体验

　　　　流畅页面的访问速度

　　　　良好的网站功能体验

二、mysql数据库优化

　　可以从哪几个方面进行数据库的优化？如下图所示：　　

　　 

　　A、 SQL及索引优化

　　　　根据需求写出良好的SQL，并创建有效的索引，实现某一种需求可以多种写法，这时候我们就要选择一种效率最高的写法。这个时候就要了解sql优化

　　B、 数据库表结构优化

　　　　根据数据库的范式，设计表结构，表结构设计的好直接关系到写SQL语句。

　　C、 系统配置优化

　　　　大多数运行在Linux机器上，如tcp连接数的限制、打开文件数的限制、安全性的限制，因此我们要对这些配置进行相应的优化。

　　D、 硬件配置优化

　　　　选择适合数据库服务的cpu，更快的IO，更高的内存；cpu并不是越多越好，某些数据库版本有最大的限制，ＩＯ操作并不是减少阻塞。

　　注：通过上图可以看出，该金字塔中，优化的成本从下而上逐渐增高，而优化的效果会逐渐降低。

三、数据库如何进行优化

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

　　2、添加索引（普通索引，唯一索引，全文索引等等）

　　3、 分库分表（垂直分割和水平分割）

　　4、 读写分离

　　5、 存储过程

　　6、 配置MySQL连接参数

　　7、 SQL语句优化

　　8、 清理碎片化

　　9、 mysql服务器优化

四、数据库三大范式

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

 　　第二范式: 在满足第一范式基础之上，保证表中数据的唯一，每一个列都和主键相关，一张表只描述一件事情

　　 第三范式：在满足第二范式基础之上，保证表中每一个列都和主键直接相关，而不是间接相关

　　   注：在实际开发场景当中，根据业务需求来定，没有必要非得严格按照三大范式

五、数据库表的垂直拆分

　　1、垂直拆分定义

　　        所谓的垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。

　　2、垂直拆分原则

　　　  通常垂直拆分可以按以下原则进行：

　　　　①把不常用的字段表单独存放到一个表中。

　　　　②把大字段独立存放到一个表中。

　　　　③ 把经常一起使用的字段放到一起。

六、数据库表的水平拆分

　　1、为什么水平拆分

　　　　表的水平拆分是为了解决单表数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的

　　2、水平不拆分原因

　　　　如果单表的数据量达到上亿条，那么这时候我们尽管加了完美的索引，查询效率低，写入的效率也相应的降低。

　　3、如何将数据平均分为N份

　　　　通常水平拆分的方法为：

　　　　　　①对customer_id进行hash运算，如果要拆分为5个表则使用mod（customer_id，5）取出0-4个值。

　　　　　　②针对不动的hashid把数据存储到不同的表中。

　　 4、水平拆分面临的挑战

　　　　①夸分区表进行数据查询

　　　　前端业务统计：

　　　　　　业务上给不同的用户返回不同的业务信息，对分区表没有大的挑战。

　　　  ②统计及后台报表操作

　　　　　　但是对后台进行报表统计时，数据量比较大，后台统计时效性比较低，后台就用汇总表，将前后台的表拆分开。

七、取模案例

　　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;

　　3、数据库展示

　　　　　

　　4、目录展示

　　　　

　　5、导入依赖

　　　　

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

　　6、application.yml 配置文件

　　　　

 　　7、LevelService

package com.zn.service;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;

@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);
    }

}

　　8、LevelController

package com.zn.controller;

import com.zn.service.LevelService;
import com.zn.service.LevelService;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;

@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);
    }
}

　　9、启动类

package com.zn;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Start {
    public static void main(String[] args) {
        SpringApplication.run(Start.class,args);
    }
}

　　10、测试