MySQL调优
1、数据库设计三范式
2、数据库分表
3、怎么定位慢查询?
一、MySQL优化方案
1、数据库设计要合理基本要遵循三范式
2、添加索引(普通索引、主键索引、唯一索引、全文索引)
3、分表分库技术(取模分表、水平分割、垂直分割)
4、读写分离
5、存储过程
6、配置mysql最大连接数 my.ini
7、mysql服务器升级
8、随时清理碎片化
9、SQL语句调优
三、数据库三大范式
什么是数据库三大范式?
1、原子约束:表示每列不可再分
2、保证唯一、主键。使用主键
3、不要有冗(rong)余数据
幂等性 = 不会重复
四、数据库的分库分表
什么时候分库?
在电商项目将一个项目进行拆分、拆分成多个小项目,每个的项目有自己
单独的数据库。互不影响。这叫垂直分割
如:会员数据库、订单数据库、支付数据库。
什么时候分表?
水平分割 分表、根据业务需求
如:存放日志(按每年存放) 根据年份进行分表
腾讯QQ号(根据位数) 缺点:表数据不均匀
水平分割(取模算法):
五、实现水平分割(取模算法)案例
好处:很均匀的分配每张表
案例代码:使用的springBoot来搭建的
1、UserService.java
package com.leeue.service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import com.fasterxml.jackson.core.sym.Name;
/**
*
* @classDesc: 功能描述:(用户表 )
* @author:<a href="leeue@foxmail.com">李月</a>
* @Version:v1.0
* @createTime:2018年8月7日 上午11:18:58
*/
@Service
public class UserService {
@Autowired
private JdbcTemplate jdbcTemplate;
public String regist(String name, String pwd) {
//1.生成userId
String insertUUidSql = "insert into uuid values(null)";
//执行这个sql语句,从id表中获取id
jdbcTemplate.update(insertUUidSql);
Long userId = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
//2.存放具体那张表中
String tableName = "user"+userId%3;
//3.插入具体的某个表中
String insertSql = "insert into "+tableName+" values("+userId+","+name+","+pwd+")";
System.out.println("sql:"+insertSql);
jdbcTemplate.update(insertSql);
return "success";
}
public String get(Long userId) {
//1、存放在具体那张表中
String tableName = "user"+userId%3;
String selectUserSql = "select name from "+tableName+" where id="+userId;
System.out.println("sql:"+selectUserSql);
String name = jdbcTemplate.queryForObject(selectUserSql, String.class);
return name;
}
}
2、UserController.java
package com.leeue.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.leeue.service.UserService;
@RestController
public class UserController {
@Autowired
private UserService UserService;
@RequestMapping("/regist")
public String regist(String name, String pwd) {
return UserService.regist(name, pwd);
}
@RequestMapping("/get")
public String get(Long userId) {
return UserService.get(userId);
}
}
3、pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.leeue</groupId>
<artifactId>20_mysql</artifactId>
<version>0.0.1-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.3.3.RELEASE</version>
</parent>
<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>
</project>
运行测试结果:取模算法分表
插入
查找
分表之后有什么缺点?
1、分页查询不行
2、查询非常受限制
3、
取模算法:如果表发生的改变就很难
阿里云的rds:自带分表分库
六、SQL优化如何定位慢查询
1、什么是慢查询?
答:mysql默认慢查询是10秒。
慢查询都会有日志来存放的
定位慢查询:
定位慢查询
主要是自己本地没有实现成功,目测问题没有解决,可能是安装问题。
使用的sql语句如下:
SHOW STATUS -- 查看mysql服务器状态
SHOW STATUS LIKE 'uptime' -- 查看mysql数据库启动了多长时间
SHOW STATUS LIKE 'connections' -- 查看mysql连接数
--
SHOW STATUS LIKE 'slow_queries' -- 查询慢查询的次数
SHOW VARIABLES LIKE 'long_query_time'; -- 查询慢查询设置时间
SET long_query_time=1; -- 修改慢查询时间 但是重启数据库后,慢查询时间还是 10秒