MySQL优化详解

最新推荐文章于 2023-09-25 15:55:03 发布

养猫还是养狗？

最新推荐文章于 2023-09-25 15:55:03 发布

阅读量400

点赞数 1

分类专栏： mysql 学习笔记

本文链接：https://blog.csdn.net/weixin_43844810/article/details/90286335

版权

学习笔记同时被 2 个专栏收录

41 篇文章 0 订阅

订阅专栏

mysql

12 篇文章 0 订阅

订阅专栏

MySQL如何进行优化？

1.表的设计合理化(符合3NF)

2.添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]

3.SQL语句优化

4.分表技术(水平分割分表、垂直分割分库)

5.读写[写: update/delete/add]分离

6.存储过程 [模块化编程，可以提高速度]

7.对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]

8.mysql服务器硬件升级

9.定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

数据库三大范式

第一范式：1NF是对属性的原子性约束，要求属性(列)具有原子性，不可再分解；(只要是关系型数据库都满足1NF)

是否要满足原子性，主要看业务，比如数据库有一列是地址，如果是电商项目，有可能还会再分，什么省，什么市

第二范式：2NF是对记录的惟一性约束，表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现，主键不能包含业务逻辑。

第三范式：3NF是对字段冗余性的约束，它要求字段没有冗余，是确保每列都和主键列直接相关,而不是间接相关(另外非主键列必须直接依赖于主键，不能存在传递依赖，比如说外键用连表后，能通过所连接的表得到数据，就没必要新增一列).。

但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

垂直拆分

垂直拆分就是要把表按模块划分到不同数据库表中（当然原则还是不破坏第三范式），这种拆分在大型网站的演变过程中是很常见的。当一个网站还在很小的时候，只有小量的人来开发和维护，各模块和表都在一起，当网站不断丰富和壮大的时候，也会变成多个子系统来支撑，这时就有按模块和功能把表划分出来的需求。其实，相对于垂直切分更进一步的是服务化改造，说得简单就是要把原来强耦合的系统拆分成多个弱耦合的服务，通过服务间的调用来满足业务需求看，因此表拆出来后要通过服务的形式暴露出去，而不是直接调用不同模块的表，淘宝在架构不断演变过程，最重要的一环就是服务化改造，把用户、交易、店铺、宝贝这些核心的概念抽取成独立的服务，也非常有利于进行局部的优化和治理，保障核心模块的稳定性

垂直拆分用于分布式场景。

水平拆分

上面谈到垂直切分只是把表按模块划分到不同数据库，但没有解决单表大数据量的问题，而水平切分就是要把一个表按照某种规则把数据划分到不同表或数据库里。例如像计费系统，通过按时间来划分表就比较合适，因为系统都是处理某一时间段的数据。而像SaaS应用，通过按用户维度来划分数据比较合适，因为用户与用户之间的隔离的，一般不存在处理多个用户数据的情况，简单的按user_id范围来水平切分

通俗理解：水平拆分行，行数据拆分到不同表中，垂直拆分列，表数据拆分到不同表中

取模方式分表demo

创建表：

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;

pom:

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

server:

@Service
public class UserService {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public String regit(String name, String pwd) {
		// 1.先获取到 自定增长ID
		String idInsertSQL = "INSERT INTO uuid VALUES (NULL);";
		jdbcTemplate.update(idInsertSQL);
		Long insertId = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
		// 2.判断存储表名称
		String tableName = "user" + insertId % 3;
		// 3.注册数据
		String insertUserSql = "INSERT INTO " + tableName + " VALUES ('" + insertId + "','" + name + "','" + pwd
				+ "');";
		System.out.println("insertUserSql:" + insertUserSql);
		jdbcTemplate.update(insertUserSql);
		return "success";
	}

	public String get(Long id) {
		String tableName = "user" + id % 3;
		String sql = "select name from " + tableName + "  where id="+id;
		System.out.println("SQL:" + sql);
		String name = jdbcTemplate.queryForObject(sql, String.class);
		return name;
	}

}

controller:

@RestController
public class UserController {
	@Autowired
	private UserService userService;

	@RequestMapping("/regit")
	public String regit(String name, String pwd) {
		return userService.regit(name, pwd);
	}

	@RequestMapping("/get")
	public String get(Long id) {
		String name = userService.get(id);
		return name;
	}

}

property:

spring.datasource.url=jdbc:mysql://localhost:3306/test

spring.datasource.username=root

spring.datasource.password=root

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

分表缺点：1.分页查询 2.查询受限 3表结构发生改变，分表方式也需要改变

所以，一般情况是先主表（存所有数据）存放所有数据，然后根据业务需求进行分表。

show status

使用show status使用show status查看MySQL服务器状态信息

常用命令

--mysql数据库启动了多少时间

show status like 'uptime';

show stauts like 'com_select' show stauts like 'com_insert' ...类推 update delete（显示数据库的查询，更新，添加，删除的次数）

show [session|global] status like .... 如果你不写 [session|global] 默认是session 会话，指取出当前窗口的执行，如果你想看所有(从mysql 启动到现在，则应该 global)

//显示到mysql数据库的连接数

show status like 'connections ';

//显示慢查询次数

show status like 'slow_queries';

慢查询

什么是慢查询

MySQL默认10秒内没有响应SQL结果,则为慢查询

可以去修改MySQL慢查询默认时间

如何修改慢查询

--查询慢查询时间

show variables like 'long_query_time';

--修改慢查询时间

set long_query_time=1; ---但是重启mysql之后，long_query_time依然是my.ini中的值

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

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

bin\mysqld.exe --safe-mode --slow-query-log [mysql5.5 可以在my.ini指定]（安全模式启动，数据库将操作写入日志，以备恢复）

bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]

先关闭mysql,再启动, 如果启用了慢查询日志，默认把这个文件放在

my.ini 文件中记录的位置

#Path to the database root

datadir=" C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

什么是索引

索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B-树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。

查询索引

desc 表名; 不能显示索引名称

show index from 表名

show keys from 表名

主键索引

主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。

唯一索引

这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。唯一性索引可以用以下几种方式创建：

创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表)；

修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表)；

创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) )；

创建表结构

create table ddd(id int primary key auto_increment , name varchar(32) unique);

注意

unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复，

但是不能存有重复的空字符串’’

普通索引

普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

数据库索引

是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用 B 树及其变种 B+ 树。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

1）B 树

B 树中每个节点包含了键值和键值对于的数据对象存放地址指针，所以成功搜索一个对象可以不用到达树的叶节点。

成功搜索包括节点内搜索和沿某一路径的搜索，成功搜索时间取决于关键码所在的层次以及节点内关键码的数量。

在 B 树中查找给定关键字的方法是：首先把根结点取来，在根结点所包含的关键字 K1,…,kj 查找给定的关键字（可用顺序查找或二分查找法），若找到等于给定值的关键字，则查找成功；否则，一定可以确定要查的关键字在某个 Ki 或 Ki+1 之间，于是取 Pi 所指的下一层索引节点块继续查找，直到找到，或指针 Pi 为空时查找失败。

2）B+ 树

B+ 树非叶节点中存放的关键码并不指示数据对象的地址指针，非也节点只是索引部分。所有的叶节点在同一层上，包含了全部关键码和相应数据对象的存放地址指针，且叶节点按关键码从小到大顺序链接。如果实际数据对象按加入的顺序存储而不是按关键码次数存储的话，叶节点的索引必须是稠密索引，若实际数据存储按关键码次序存放的话，叶节点索引时稀疏索引。

B+ 树有 2 个头指针，一个是树的根节点，一个是最小关键码的叶节点。

所以 B+ 树有两种搜索方法：

一种是按叶节点自己拉起的链表顺序搜索。

一种是从根节点开始搜索，和 B 树类似，不过如果非叶节点的关键码等于给定值，搜索并不停止，而是继续沿右指针，一直查到叶节点上的关键码。所以无论搜索是否成功，都将走完树的所有层。

B+ 树中，数据对象的插入和删除仅在叶节点上进行。

这两种处理索引的数据结构的不同之处：
a，B 树中同一键值不会出现多次，并且它有可能出现在叶结点，也有可能出现在非叶结点中。而 B+ 树的键一定会出现在叶结点中，并且有可能在非叶结点中也有可能重复出现，以维持 B+ 树的平衡。
b，因为 B 树键位置不定，且在整个树结构中只出现一次，虽然可以节省存储空间，但使得在插入、删除操作复杂度明显增加。B+ 树相比来说是一种较好的折中。
c，B 树的查询效率与键在树中的位置有关，最大时间复杂度与 B+ 树相同(在叶结点的时候)，最小时间复杂度为 1(在根结点的时候)。而 B+ 树的时候复杂度对某建成的树是固定的。可以扫描2的次方。

MySQL explain执行计划解读

MySql提供了EXPLAIN语法用来进行查询分析，在SQL语句前加一个"EXPLAIN"即可。比如我们要分析如下SQL语句：

explain select * from table where table.id = 1

运行上面的sql语句后你会看到，下面的表头信息：

EXPLAIN列的解释

table
显示这一行的数据是关于哪张表的

type
这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

说明：不同连接类型的解释（按照效率高低的顺序排序）

system：表只有一行：system表。这是const连接类型的特殊情况。

const ：表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待。

eq_ref：在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用。

ref：这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。

range：这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况。

index：这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）。

ALL：这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。

possible_keys
显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key
实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len
使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref
显示索引的哪一列被使用了，如果可能的话，是一个常数

rows
MYSQL认为必须检查的用来返回请求数据的行数

Extra
关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

说明：extra列返回的描述的意义

Distinct ：一旦mysql找到了与行相联合匹配的行，就不再搜索了。

Not exists ：mysql优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。

Range checked for each Record（index map:#）：没有找到理想的索引，因此对从前面表中来的每一个行组合，mysql检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。

Using filesort ：看到这个的时候，查询就需要优化了。mysql需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。

Using index ：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。

Using temporary ：看到这个的时候，查询需要优化了。这里，mysql需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。

Where used ：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题。

索引的代价

占用磁盘空间

对DML(update、delete、insert)语句的效率影响

增删改会对索引影响，因为索引要重新整理。

存储引擎	允许的索引类型
myisam	btree
innodb	btree
memory/yeap	Hash,btree

那些列上适合添加索引

查询作为查询条件字段应该创建索引

唯一性太差的字段不适合单独创建索引,即使频繁

Select * from emp where sex=’男’

频繁更新字段，也不要定义索引。

不会出现在where语句的字段不要创建索引

总结:满处一下条件的字段，才应该创建索引

肯定在where条件经常使用
该字段的内容不是唯一的几个值
字段内容不是频繁变化

索引的注意事项

1.对于创建的联合索引，如果不是使用第一部分或者是全部使用，则不会使用到索引

2.模糊查询在like前面有百分号开头会失效。

3. 如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引, 我们建议大家尽量避免使用or 关键字

4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’), 也就是，如果列是字符串类型，就一定要用 ‘’ 把他包括起来.

5.如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

查询所用使用率

show status like ‘handler_read%’;

注意：
handler_read_key:这个值越高越好，越高表示使用索引查询到的次数。

handler_read_rnd_next:这个值越高，说明查询低效。

SQL优化技巧

使用group by 分组查询是，默认分组后，还会排序，可能会降低速度，

在group by 后面增加 order by null 就可以防止排序.

explain select * from emp group by deptno order by null;

有些情况下，可以使用连接来替代子查询。因为使用join，MySQL不需要在内存中创建临时表。

select * from dept, emp where dept.deptno=emp.deptno; [简单处理方式]

select * from dept left join emp on dept.deptno=emp.deptno; [左外连接，更ok!]

对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

最好不要给数据库留 NULL，尽可能的使用 NOT NULL 填充数据库.

备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用 NULL。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了，不管是否插入值（NULL 也包含在内），都是占用 100 个字符的空间的，如果是 varchar 这样的变长字段， null 不占用空间。

可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询：

select id from t where num = 0

MySQL数据引擎

使用的存储引擎 myisam / innodb/ memory

myisam 存储: 如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. ,比如 bbs 中的发帖表，回复表.

INNODB 存储: 对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.

MyISAM 和 INNODB的区别

1. 事务安全（MyISAM不支持事务，INNODB支持事务）

2. 查询和添加速度（MyISAM批量插入速度快）

3. 支持全文索引（MyISAM支持全文索引，INNODB不支持全文索引）

4. 锁机制（MyISAM时表锁，innodb是行锁）

5. 外键 MyISAM 不支持外键， INNODB支持外键. (在PHP开发中，通常不设置外键，通常是在程序中保证数据的一致)

Memory 存储，比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快. （如果mysql重启的话，数据就不存在了）

Myisam注意事项

如果你的数据库的存储引擎是myisam,请一定记住要定时进行碎片整理

举例说明:

create table test100(id int unsigned ,name varchar(32))engine=myisam;

insert into test100 values(1,’aaaaa’);

insert into test100 values(2,’bbbb’);

insert into test100 values(3,’ccccc’);

insert into test100 select id,name from test100;

我们应该定义对myisam进行整理

optimize table test100;

养猫还是养狗？

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL优化详解

MySQL如何进行优化？1.表的设计合理化(符合3NF)2.添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]3.SQL语句优化4.分表技术(水平分割分表、垂直分割分库)5.读写[写: update/delete/add]分离6.存储过程 [模块化编程，可以提高速度]7.对mysql配置优化 [配置最大并发数my.ini, 调整缓...
复制链接

扫一扫