【免费】mysql优化十大技巧_mysql优化资源-CSDN文库

mysql

需积分: 0 147 浏览量 2023-04-26 20:33:29 上传评论收藏 2.25MB PDF 举报

资源推荐

资源详情

资源评论

MySQL优化⼗⼤技巧优化⼗⼤技巧

主要分为以下四⼤⽅⾯：

设计：存储引擎，字段类型，范式与逆范式

功能：索引，缓存，分区分表。

架构：主从复制，读写分离，负载均衡。

合理SQL：测试，经验。

⼀、存储引擎

在创建表的时候我们使⽤sql语句，Create table tableName () engine=myisam|innodb;

这⾥就指明了存储引擎是myisam还是innodb。存储引擎是⼀种⽤来存储MySQL中对象（记录和索引）的⼀种特定的结构（⽂件结构），处

于MySQL服务器的最底层，直接存储数据。导致上层的操作，依赖于存储引擎的选择。地位如下图：

⽹络接⼝层：与客户端通信，⽐如传输数据等等。存储引擎层：存储数据的规则，⽅式。

本质：存储引擎就是特定的数据存储格式（⽅案）。

可以使⽤show engines命令来查看当前MySQL⽀持的存储引擎列表。

1、InnoDB存储引擎介绍

Mysql版本>=5.5 默认的存储引擎，MySQL推荐使⽤的存储引擎。⽀持事务，⾏级锁定，外键约束。事务安全型存储引擎。更加注重数据的

完整性和安全性。

（1）存储格式

数据，索引集中存储，存储于同⼀个表空间⽂件中。

数据：记录⾏。索引：⼀种检索机制，也需要⼀定的空间，就相当于⼀本字典的⽬录。

⽰例：创建⼀个test，新建⼀张student表，选择存储引擎为innodb, 然后打开的data下的test⽬录，发现有以下3个⽂件。

其中db.opt存放了数据库的配置信息，⽐如数据库的字符集还有编码格式。student.frm是表结构⽂件，仅存储了表的结构、元数据(meta)，

包括表结构定义信息等。不论是哪个表引擎都会有⼀个frm⽂件。student.ibd是表索引⽂件，包括了单独⼀个表的数据及索引内容。

如果往表⾥插⼊了新的数据，则在mysql的data⽬录下会⽣成ibdata1⽂件，这个⽂件是存储了所有innodb表的数据。

关于innodb引擎的详细介绍：

使⽤innodb引擎时，需要理解独⽴表空间、共享表空间。

独⽴表空间：每个表都会⽣成以独⽴的⽂件⽅式来存储，每个表都⼀个.frm的描述⽂件，还有⼀个.ibd⽂件。其中这个⽂件包括了单独⼀个表

的数据及索引内容，默认情况下它的存储在mysql指定的⽬录下。

独⽴表空间优缺点

优点：

每个表都有⾃⼰独⽴的表空间；每个表的数据和索引都会存储在各个独⽴的表空间中；可以实现单表在不同的数据进⾏迁移；表空间可以回

收（除了drop table操作，表空不能⾃⼰回收）；drop table 操作⾃动回收表空间，如果对统计分析或是⽇值表，删除⼤量数据后可以通过

：alter table tablename engin=innodb进⾏回缩不⽤的空间；对于使⽤inodb-plugin的innodb使⽤truncate table会使⽤空间收缩。；对于使

⽤独⽴表空间，不管怎么删除，表空间的碎⽚都不会太严重。

缺点：

单表增加过⼤，如超过100G。对于单表增长过⼤的问题，如果使⽤共享表空间可以把⽂件分开，但有同样有⼀个问题，如果访问的范围过

⼤同样会访问多个⽂件，⼀样会⽐较慢。对于独⽴表空间也有⼀个解决办法是：使⽤分区表，也可以把那个⼤的表空间移动到别的空间上然

后做⼀个连接。其实从性能上出发，当⼀个表超过100个G有可能响应也是较慢了，对于独⽴表空间还容易发现问题早做处理。

共享表空间：某⼀个数据库所有的表数据，索引⽂件全部都放在⼀个⽂件中，默认这个共享表空间的⽂件路径在data⽬录下，默认的⽂件名

为 ibdata1,初始化为10M。

共享表空间优缺点

优点：可以将表空间分成多个⽂件存放在各个磁盘上（表空间⽂件⼤⼩不受表⼤⼩的限制，如⼀个表可以分布在不同的⽂件上），数据和⽂

件放在⼀起⽅便管理。

缺点：所有的数据和索引存放到⼀个⽂件中，将来会是⼀个很⼤的⽂件，虽然可以把⼀个⼤⽂件分成多个⼩⽂件，但是多个表及索引在表空

间中混合存储，这样对⼀个表做了⼤量删除操作后表空间将有⼤量的空隙，特别是对统计分析、⽇值这类应⽤最不适合⽤共享表空间。

如何开启独⽴表空间？

查看是否开启独产表空间：

mysql> show variables like '%per_table';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | OFF |

+-----------------------+-------+

设置开启：

在my.cnf⽂件中[mysqld] 节点下添加innodb_file_per_table=1

或者通过命令：set global innodb_file_per_table=1;

注：

innodb_file_per_table值来进⾏修改即可，但是对于之前使⽤过的共享表空间则不会影响，除⾮⼿动的去进⾏修改或者是

innodb_file_per_table=1 为使⽤独占表空间

innodb_file_per_table=0 为使⽤共享表空间

修改独占空表空间的数据存储位置

innodb_data_home_dir = "C:\mysql\data\"

innodb_log_group_home_dir = "C:\mysql\data\"

innodb_data_file_path=ibdata1:10M:autoextend

innodb_file_per_table=1

参数说明:

这个设置配置⼀个可扩展⼤⼩的尺⼨为10MB的单独⽂件，名为ibdata1。没有给出⽂件的位置，所以默认的是在MySQL的数据⽬录内。【对

数据来进⾏初始化的设置】

innodb_data_home_dir 代表为数据库⽂件所存放的⽬录

innodb_log_group_home_dir 为⽇志存放⽬录

innodb_file_per_table 是否使⽤共享以及独占表空间来

以上的⼏个参数必须在⼀起加⼊。

对于参数⼀些注意的地⽅

InnoDB不创建⽬录，所以在启动服务器之前请确认”所配置的路径⽬录”的确存在。这对你配置的任何⽇志⽂件⽬录来说也是真实的。使⽤

Unix或DOS的mkdir命令来创建任何必需的⽬录。

通过把innodb_data_home_dir的值原原本本地部署到数据⽂件名，并在需要的地⽅添加斜杠或反斜杠，InnoDB为每个数据⽂件形成⽬录路

径。

如果innodb_data_home_dir选项根本没有在my.cnf中提到，默认值是“dot”⽬录 ./，这意思是MySQL数据⽬录。

（2）数据按照主键顺序存储

插⼊时做排序⼯作，效率低。

（3）特定功能

事务、外键约束：都是为了维护数据的完整性。

并发性处理：

innodb擅长处理并发的。因为它使⽤了⾏级锁定，只该⾏锁了，其它⾏没有锁。

⾏级锁定：row-level locking，实现了⾏级锁定，在⼀定情况下，可以选择⾏级锁来提升并发性。也⽀持表级锁定，Innodb会⾃带锁，不需

要我们⾃⼰设置。

多版本并发控制, MVCC，效果达到⽆阻塞读操作。

（4）总结：innodb擅长事务、数据的完整性及⾼并发处理，不擅长快速插⼊（插⼊前要排序，消耗时间）和检索。

2.MyISAM存储引擎介绍

MySQL<= 5.5 MySQL默认的存储引擎。

ISAM：Indexed Sequential Access Method(索引顺序)的缩写，是⼀种⽂件系统。

擅长与处理，⾼速读与写。

（1）存储⽅式

数据和索引分别存储于不同的⽂件中。

解压后，变成了原来的25600KB

刷新表的状态：flush table myisam_2

c.并发性：

仅仅⽀持表级锁定，不⽀持⾼并发。

⽀持并发插⼊。写操作中的插⼊操作，不会阻塞读操作（其他操作）

（4）关于Innodb 和myisam的取舍：

Innodb ：数据完整性，并发性处理，擅长更新，删除。

myisam：⾼速查询及插⼊。擅长插⼊和查询。

具体举例：

那么对于微博项⽬来看，选择哪⼀个存储引擎呢？

a.微博主要是插⼊微博和查询微博列表，较为适合MyISAM；

b.微博在更新微博和删除微博，要少的多，较为适合MyISAM；

c.对数据完整性的需求并没有那么强烈，⽐如⽤户删除微博，关联的转播和评论并不要求都做相应的⾏为，较为适合MyISAM；

那么对于记账财务系统，选择哪⼀款存储引擎呢？

a.财务系统除了读取和插⼊，经常要进⾏数据的修改和删除，较为适合InnoDB；

b.在进⾏财务变更的时候，如果失败需要回滚必须⽤到事务，较为适合InnoDB；

c.每个⽤户的财务数据完整性和同步性⾮常重要，需要外键⽀持，否则财务将会混乱，较为适合InnoDB。

3.其他存储引擎

（1）Archive：存档型，仅提供插⼊和查询操作。⾮常⾼效阻塞的插⼊和查询。

（2）Memory：内存型，数据存储于内存中，存储引擎。缓存型存储引擎。

（3）插件式存储引擎：⽤C和C++开发的存储引擎。

4.锁的概念：当客户端操作表（记录）时，为了保证操作的隔离性（多个客户端操作不能互相影响），通过加锁来处理。

操作⽅⾯：

读锁：读操作时增加的锁，也叫共享锁，S-lock。特征是阻塞其他客户端的写操作，不阻塞读操作。（并发读）

写锁：写操作时增加的锁，也叫独占锁或排他锁，X-lock。特征是阻塞其他客户端的读，写操作。

锁定粒度（范围）：

⾏级：提升并发性，锁本⾝开销⼤

表级：不利于并发性，锁本⾝开销⼩。

⼆、字段类型选择

字段类型应该要满⾜需求，尽量要满⾜以下需求。

尽可能⼩（占⽤存储空间少）、尽可能定长（占⽤存储空间固定）、尽可能使⽤整数。

剩余26页未读，继续阅读

评论收藏

内容反馈

计算机小陈

粉丝: 10
资源: 65

mysql优化十大技巧

MySQL性能优化 SQL优化方法技巧

mysql 性能优化

MySQL数据库十大优化技巧

优化SQL Server性能的十大技巧(dell出品).pdf

十大DB2优化技巧

UPS电源的十大优化技巧

mysql优化技巧

MySQL调优讨论：原理 优化 技巧

2023最新mysql的sql语句优化方法技巧面试题总结.docx

MYSQL优化－一篇很好的优化文章

初学者宝典－SEO优化学习《三大禁忌6个细节，十大技巧》（超级实用、易懂）网络推广宝典

数据库优化让数据库飞起来 十大DB2优化技巧

mysql优化笔记+资料

mysql优化配置大全

mysql优化小技巧之去除重复项实现方法分析【百万级数据】

MySQL优化技巧.rar

Mysql 优化技巧总结（自己整理）

大型门户网站核心技术-Mysql优化

php之mysql优化

MySql优化.rar

mysql优化！

我的mysql 优化日记

MySQL优化技巧大全.pdf

mysql 优化工具.rar

tongweb7.0，windows和linux安装包

东方通TongWeb Linux/windows安装包(最新版)

nginx-1.19.3_nginx-http-flv-module.rar

最新资源

MySQL调优讨论：原理优化技巧

数据库优化让数据库飞起来十大DB2优化技巧