mysql学习总结07 — 优化(存储层)-存储引擎与缓存

mysql学习总结07 — 优化(存储层)-存储引擎与缓存

Write By CS逍遥剑仙 我的主页: www.csxiaoyao.com GitHub: github.com/csxiaoyaojianxian Email: sunjianfeng@csxiaoyao.com QQ: 1724338257

1. mysql优化方向概述

mysql作为最流行的数据库,在开发过程中仍然有较多优化的空间,mysql的优化主要有4个方向:

  1. 存储层:数据表存储引擎选取、字段类型选取、查询缓存、3范式、数据碎片维护
  2. 设计层:索引、分区、分表
  3. 架构层:分布式部署(集群)(主从复制、读写分离)
  4. sql语句层:锁的使用、慢查询的定位、limit分页优化

2. 存储引擎选择

2.1 三种存储引擎特点概述

  1. Myisam:表锁,全文索引
  2. Innodb:行(记录)锁,事务(回滚),外键
  3. Memory:内存存储引擎,速度快、数据容易丢失

2.2 innodb

(1) 存储格式

表结构存储于* .frm文件中。

默认所有表的数据/索引存储在同一个表空间文件中。

可以通过配置将不同表的数据/索引单独存储在*.ibd中,方便管理。

# 创建表
mysql> create table t1(id int, name varchar(32))engine innodb charset utf8;
mysql> show variables like 'innodb_file_per_table%';
mysql> set global innodb_file_per_table=1;

注意:innodb不能直接通过文件的复制粘贴进行备份还原,备份还原需要使用mysqldump。

(2) 存储顺序: 主键顺序

数据按照主键顺序存储,写入顺序与存储顺序不同,因此速度比Myisam稍慢。

(3) 并发处理

擅长并发处理,支持行级锁和表级锁。

2.3 MyISAM

(1) 存储格式

mysql5.5以下默认存储引擎。

结构、数据、索引分别存储于frm、MYD、MYI文件中,支持直接通过文件复制粘贴进行备份还原。

(2) 存储顺序: 插入顺序

写入速度快

(3) 并发处理

不如innodb,只支持表级锁

(4) 压缩性

对于不频繁发生变化的数据,可以进行压缩,压缩后只读,写操作需要先解压

2.4 Memory

内存存储引擎,速度快、数据容易丢失,可以用作缓存。

2.5 innodb & myisam 的适用场景

myisam:写入快,适合写入、读取操作多的系统,如微博。表锁,全文索引。

innodb:适合业务逻辑强、修改操作多的系统,如商城、办公系统。行(记录)锁,事务(回滚),外键。

3. 查询缓存

3.1 使用方法

mysql服务器提供的用于缓存select语句结果的一种内部内存缓存系统。

mysql> show variables like 'query_cache%';
# query_cache_size: 缓存空间大小
# query_cache_type: 是否开启缓存
# 在 my.ini 中开启缓存,设置缓存空间为128M
query_cache_type=1
query_cache_size=134217728
# 重启mysql

3.2 缓存失效

数据表的数据发生变化(数据修改)或结构改变(字段增删),则会清空全部的缓存数据,即缓存失效。

3.3 不使用缓存情况

sql语句中有变化表达式(时间、随机数等),则不会使用缓存。

mysql> select name, now() from t2 where id=1234; # 不使用缓存
mysql> select * from t2 order by rand() limit 5; # 不使用缓存

3.4 生成多个缓存

生成缓存的sql语句对"空格"、"大小写"敏感,相同结果的sql语句,由于空格、大小写问题就会分别生成多个缓存。

3.5 禁用缓存

mysql> select sql_no_cache * from t2 where id=1234;

3.6 查看缓存空间的使用

mysql> show status like 'Qcache%';

Cache_free_memory 缓存中空闲内存总量

Qcache_hits 缓存命中次数

Qcache_inserts 缓存失效次数

Qcache_queries_in_cache 当前缓存的数量

4. 范式

范式主要分为四类范式,在开发过程中没有特殊情况数据表尽量要设计为第三范式。

第一范式:

(1) 表属性(列)具有原子性(不可分割)

(2) 表属性(列)不能重复

第二范式:

表中不能存在完全相同的两条记录,通常通过设置主键来实现。

第三范式:

表中不能存在冗余数据,列数据不能通过推导得到。

反三范式:

有时出于性能考虑,有意违反三范式,适度冗余,提高查询效率,例如存储浏览量。

5. 数据碎片与维护

长期数据操作过程中,索引和数据文件产生空洞碎片,会拖慢执行效率,需要修复,修复可以把数据文件重新整理,使之对齐。由于修复十分消耗资源,如果表的update、delete操作频繁,可以按周月修复。

# 方式1:
mysql> alter table engine innodb;
# 方式2:
mysql> optimize table <表名>;

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏开源优测

性能测试必备监控技能MySQL篇15

前言 性能测试过程中,数据库相关指标的监控是不可忽视的,在这里我们就MySQL的监控配置及重点涉及性能的一些参数进行说明。 在笔者的日常性能测试过程中,重点关注...

384120
来自专栏冷冷

MySQL二进制日志

MySQL 5.5 中对于二进制日志 (binlog) 有 3 种不同的格式可选:Mixed,Statement,Row,默认格式是 Statement。 总结...

21050
来自专栏MYSQL轻松学

MYSQL数据闪回方式

MYSQL官方截止目前还没有出来数据闪回特性,也许后续版本会出现。社区有一些开源工具可以使用,沿用的基本都是彭立勋最早提出的思路,利用binlog对SQL进行反...

46980
来自专栏沃趣科技

“mysqlbinlog”工具做binlog server靠谱吗?

玩过binlog server的同学都知道,它使用mysqlbinlog命令以daemon进程的方式模拟一个slave的IO线程与主库连接,可以很方便地即时同...

61880
来自专栏性能与架构

Mysql优化中Profiling的使用

要想优化一条Query,就须要清楚这条Query的性能瓶颈到底在哪里,是消耗的CPU计算太多,还是需要的IO操作太多?要想能够清楚地了解这些信息,可以通过Que...

31340
来自专栏一枝花算不算浪漫

[Linux]Linux下安装和配置solr/tomcat/IK分词器 详细实例二.

379100
来自专栏数据库

MySQL数据库性能优化之一

文章来自:博客 数据库属于 IO密集型的应用程序,其主要职责就是数据的管理及存储工作。而我们知道,从内存中读取一个数据库的时间是微秒级别,而从一块普通硬盘上读取...

624100
来自专栏杨建荣的学习笔记

MySQL中的事务和锁简单测试(r10笔记第46天)

一直以来,对于MySQL中的事务和锁的内容是浅尝辄止,没有花时间了解过,在一次看同事排查的故障中有个问题引起了我的兴趣,虽然过去了很久,但是现在简单总结一下还是...

35970
来自专栏北京马哥教育

为MySQL入门者整理的MySQL常用命令

作者:o0DarkNessYY0o 来源:http://blog.csdn.net/o0darknessyy0o/article/details/5208097...

32590
来自专栏杨建荣的学习笔记

MySQL中的MVCC(r12笔记第35天)

最近同事也问了我关于MySQL MVCC的一些问题,我觉得这个话题蛮有意思, 而之前似乎也没有总结过,就参考了一些资料,把一些内容摘录出来。 什么是MVCC 以...

38570

扫码关注云+社区

领取腾讯云代金券