Sovia' Blog
Mysql之常用引擎对比与参数调优
LiuSovia 化神
  2024-11-21 19:09:48   2022-12-20 20:38:52    1.8k 字

MySQL常用存储引擎之MyISAM

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
特性：
    1、并发性与锁级别
    2、表损坏修复
        check table tablename
        repair table tablename
    3、MyISAM表支持的索引类型
        ①、全文索引
        ②、前缀索引
    4、MyISAM表支持数据压缩
        myisampack
限制：
    版本 < MySQL5.0时默认表大小为4G
    如存储达标则要修改MAX_Rows和AVG_ROW_LENGTH
    版本 > MySQL5.0时默认支持为256TB

适用场景：
    1、非事务形应用
    2、只读类应用
    3、空间类应用

MySQL常用存储引擎之Innodb

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Innodb存储引擎的特征
    1、Innodb是一种事务性存储引擎
    2、完全支持事务的ACID特性
    3、Redo Log 和 Undo Log
    4、Innodb支持行级锁

Innodb使用表空间进行 数据存储
    为每个表独立创建一个表空间存储
        innodb_file_per_table
            ON:独立表空间:tablename.ibd
            OFF:系统表空间:ibdataX（X是个数字，从1开始的数字）
    
系统表空间和独立表空间要如何选择
    比较：
        系统表空间无法捡的收缩文件大小
        独立表空格键可以通过optimize table命令收缩系统文件
        系统表空间会产生IO瓶颈
        独立表空间可以同时向多个文件刷新数据
    
表转移的步骤
    步骤：
        1、使用mysqldump到处所有数据库表数据
        2、停止MySQL服务，修改参数，并删除Innodb相关文件
        3、重启MySQL服务，重建Innodb系统表空间
        4、重新导入数据

MySQL常见的存储引擎之CSV

1
2
3
4
5
6
7
8
9
10
11
文件系统存储特点
    1、数据以文本方式存储在文本中
    2、.csv文件存储表内容
    3、.csm文件存储表的元数据如表状态和数据量
    4、.frm文件存储表结构信息
    5、以csv格式进行存储
    6、所有列必须都是不能为Null的
    7、不支持索引

    适用场景：
        适用作为数据交换的中间表(电子表格->csv文件->MySQL数据库目录)

MySQL常用存储引擎之Archive

1
2
3
4
5
6
7
8
9
10
文件系统存储特点
    1、以zlib对表数据进行压缩,磁盘I/O更少
    2、数据存储在ARZ为后缀的文件中

Archive存储引擎的特点
    1、只支持insert和select操作
    2、只允许在自增的ID列上加索引
    
    适用场景：
        日志和数据采集类应用

MySQL常用存储引擎之Memory

1
2
3
4
5
6
7
8
9
10
11
12
13
14
文件系统存储特点
    1、也成HEAP存储引擎，所以数据保存在内存中

功能特点：
    1、支持HASH索引和Btree索引
    2、所有字段都有固定长度varchar(10)=char(10)
    3、不支持BLOG和TEXT等大字段
    4、Memory存储引擎使用表级锁
    5、最大大小由max_heap_table_size参数决定

    适用场景：
        1、用于查找或者是映射表，例如邮编和地区的对应表
        2、用于保存数据分心中产生的中间表
        3、用于缓存周期性聚合数据的结果表

MySQL常用存储引擎之Federated

1
2
3
4
5
6
7
8
9
10
11
特点：
    1、提供了访问远程MySQL服务器上表的方法
    2、本地不存储数据，数据全部放到远程服务器上
    3、本地需要保存表结构和远程服务器的连接信息

如何使用
    默认静止，启用需要在启动时增加federated参数
    mysql://user_name[:password]@host_name[:port]/db_name/table_name

    适用场景：
        偶尔的统计分析及手工查询

如何选择正确的存储引擎

常用引擎MyISAM与InnoDb对比

image

1
2
3
4
5
参考条件
    1、是否要支持事务
    2、定期备份
    3、崩溃恢复
    4、存储引擎的特有特性

什么影响了性能

1
2
3
4
5
数据库设计对性能的影响
    1、过分的反范式化为表建立太多的列
    2、过分的范式化造成太多的表关联（关联的表尽可能的控制在10个之内）
    3、在OLTP环境中使用不恰当的分区表
    4、使用外键保证数据的完整性

总结

1
2
3
4
5
性能优化的顺序
    1、数据库结构设计和SQL语句
    2、数据库存储引擎的选择参数配置
    3、系统选择及优化
    4、硬件升级

Mysql配置参数介绍

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
MySQL获取配置信息路径
    1、命令行参数
        mysqld_safe --datadir=/data/sql_data
    2、配置文件
        查看配置文件的命令：
        [root@localhost ~]# mysqld --help --verbose | egrep -A 1 'Default options'
        配置文件的有效路径
            /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

MySQL配置参数的作用域
    1、全局参数
        set global 参数名=参数值;
        set @@global.参数名:=参数值;
    2、会话参数 
        set [session] 参数名=参数值;
        set @@session.参数名:=参数值;

内存配置相关参数
    1、确定可以使用的内存的上限
    2、确定MySQL的每个连接使用的内存
        sort_buffer_size
        join_buffer_size
        read_buffer_size
        read_rnd_buffer_size
    3、确定需要为操作系统保留多少内存
    4、如何为缓存池分配内存
        Innodb_buffer_pool_size
            注：设置缓存池的大小的考量标准为:总内存-(每个编程所以需要的内存*连接数)-系统保留内存
        key_buffer_size
        select sum(index_length) from information_schema.tables where engines='myisam'
    
I/O相关配置参数
    Innodo I/O相关配置
    Innodb_log_file_size 单个事务日志的大小
    Innodb_log_files_in_group 控制文件日子的个数
    事务日志总大小 = Innodb_log_files_in_group * Innodb_log_file_size
    Innodb_log_buffer_size = (32M or 128M)
    Innodb_flush_log_at_trx_commint
        0:每秒进行一次log写入cache,并flush log到磁盘
        1[默认]:在每次事务提交执行log写入cache，并flush log到磁盘
        2[建议]:每次事务提交，执行log数据写入到cache中，每秒执行一次flush log到磁盘
    Innodb_flush_method=O_DIRECT
    Innodb_file_per_table = 1
    Innodb_doublewrite = 1

MyISAM I/O相关配置
    delay_key_write
        OFF:每次写操作后刷新键缓冲中的脏块到磁盘
        ON:只对在键表时指定了delay_key_write选项的表使用延迟刷新
        ALL:对所有的MyISAM表都使用延迟建写入
        
安全相关配置参数
    expire_logs_days 指定自动清理binlog的天数
    max_allowed_packet 控制MySQL可以连接的包大小，建议设置为32M，如果使用了主从复制，参数应该设置成一致的
    skip_name_resolve 禁用DNS查找
    sysdate_is_now 确保sysdate()返回确保性日期
    read_only 禁止非super权限的用户写操作  注：建议在主从复制中的从库开启此功能。以确保不能修改从库中的操作，只能从主库同步过来
    skip_slave_start 禁用Salve自动恢复(从库中的设置使用)
    sql_mode 设置MySQL所使用的SQL模式 （谨慎操作，可能会造成MySQL无法执行）
        ① strict_trans_tables 给定的数据如果不能插入到数据库中，对事务引擎会终端操作，对非事务引擎是没有影响的
        ② no_engine_subitiution 在create table中指定engines的时候，如果引擎不可用，不会使用默认引擎建立表
        ③ no_zero_date 不能再表中插入0年0月0日的日期
        ④ no_zero_in_date 不接受一部分的为0的日期
        ⑤ noly_full_group_by 
    
其他常用的配置参数
    sync_binlog 控制MySQL如何向磁盘刷新binlog
    tmp_table_size 和 max_heap_table_size 控制内存临时表大小(不宜设置的太大，以避免内存的溢出)
    max_connections 控制允许的最大连接数(默认为100，有点小，根据自己的业务适当的调整大小)

End

来自huiy_小溪

 评论
评论插件加载失败
正在加载评论插件
  1. MySQL常用存储引擎之MyISAM
  2. MySQL常用存储引擎之Innodb
  3. MySQL常见的存储引擎之CSV
  4. MySQL常用存储引擎之Archive
  5. MySQL常用存储引擎之Memory
  6. MySQL常用存储引擎之Federated
  7. 如何选择正确的存储引擎
  8. 什么影响了性能
  9. 总结
  10. Mysql配置参数介绍
  1. MySQL常用存储引擎之MyISAM
  2. MySQL常用存储引擎之Innodb
  3. MySQL常见的存储引擎之CSV
  4. MySQL常用存储引擎之Archive
  5. MySQL常用存储引擎之Memory
  6. MySQL常用存储引擎之Federated
  7. 如何选择正确的存储引擎
  8. 什么影响了性能
  9. 总结
  10. Mysql配置参数介绍