Анализ и оптимизация влияния базы данных MySQL на производительность

MySQL SQL контрольная работа
Анализ и оптимизация влияния базы данных MySQL на производительность

Влияние на производительность базы данных MySQL

1. Аппаратные ограничения сервера

2. Операционная система, используемая сервером

3. Настройки параметров сервера отличаются

4. Выбор механизма хранения базы данных

5. Конфигурация параметров базы данных отличается

6. (выделено) Проектирование структуры базы данных и оператора SQL

1).Конфигурация и настройки сервера (процессор и доступный объем памяти)

 1.网络和I/O资源 
 2.cpu的主频和核心的数量的选择
 (对于密集型的应用应该优先考虑主频高的cpu)
 (对于并发量大的应用优先考虑的多核的cpu)
 3.磁盘的配置和选择
 (使用传统的机械硬盘:
	特点：读写较慢、存储空间大、最常见、使用最多、价格低;
	工作过程：移动磁头到磁盘表面上的正确位置;
			 等待磁盘的旋转，使得所得所需的数据在磁头之下;
			 等待磁盘旋转过去，所有所需的数据都被磁头读出
	选择因素：存储容量、传输速度、访问时间、主轴转速、物理尺寸)
 (使用RAID增强传统的机器硬盘的性能：
	特点：利用小的磁盘组成大的磁盘并提供数据的冗余保证数据的完整性的技术
	数据库中所使用的RAID的级别：
		RAID0级别、RAID1级别、RAID5级别[分布式奇偶校验磁盘阵列]、RAID10[分片的镜像(数据库最好的方式)]
	RAID级别选择：如下图)
 (使用固态存储的SSD和PCI-E卡:
	特点：相对于机械盘固态磁盘有更好的随机读写性能;
		 相对于机械固态磁盘能更好的支持并发;
		 相对于机械固态磁盘更容易损坏
	SSD:使用SATA接口可以替换传统的磁盘而不需要任何的改变[受到接口的速度的限制];
		SATA接口的SSD同样支持RAID技术
	PCI-E卡(Fusion-IO卡)：无法使用在SATA接口[需要使用独特的驱动和配置];
						 价格贵,使用了cpu的资源和内存
	使用的场景：适用于存在大量的随机I/O的场景;
			  适用于解决单线程负载的I/O瓶颈)
 (使用网络存储NAS和SAN：
	SAN[光纤接入服务器]:大量顺序读写操作、读写I/O、缓存、I/O合并、随机读写慢（不如本地的RAID）
	NAS设备使用网络连接，基于文件的协议如NFS或者SMB来访问
	适合场景：数据库的备份、)

使用RAID增强传统的机器硬盘的性能->RAID0级别
使用RAID增强传统的机器硬盘的性能->RAID1级别
使用RAID增强传统的机器硬盘的性能->RAID5级别

Сравнение различных уровней REAID:

Меры предосторожности:

 1.64位数据库的版本使用32位的服务器的版本
 2.内存的主频的选择主板所能支持的最大内存的频率
Суммировать:
	对于cpu：
		1.64位的cpu一定能够要工作在64位的系统下
		2.对于并发比较高的场景cpu的数量比频率重要
		3.对于cpu密集型的场景和复杂SQL则频率越高越好
	对于内存：
		1.选择主板所能使用的最高频率的内存
		2.内存的大小对性能很重要，所以尽可能的大
	I/O子系统：
		1.PCIe -> SSD -> RAID10 -> 磁盘 -> SAN

2) Влияние операционной системы на производительность

Windows、FreeBSD、Solaris、Linux
centos的参数优化的设置：
	(1)内核相关的参数（/etc/sysctl.conf）
		net.core.somaxconn = 65535
		net.core.netdev_max_backlog = 65535
		net.ipv4.tcp_max_syn_backlog = 65535
		
		net.ipv4.tcp_fin_timeout = 10
		net.ipv4.tcp_tw_reuse = 1
		net.ipv4.tcp_tw_recycle = 1

		net.core.wmem_defaullt = 87380
		net.core.wmem_max = 16777216
		net.core.rmem_defaullt = 87380
		net.core.rmem_max = 16777216

		net.ipv4.tcp_keepalive_time = 120
		net.ipv4.tcp_keepalive_intvl = 30
		net.ipv4.tcp_keepalive_probes = 3
		
		kernel.shmmax = 4294967295
		vm.swappiness = 0
	(2)增加资源限制（/etc/security/limit.conf）
		* soft nofile 65535
		* hard nofile 65535
			* 表示对所有的用户有效
			soft 指的是当前系统的生效的设置
			hard 表明系统中所能设定的最大值
			nofile 表示所限制的资源是打开文件的最大数目
			65535 就是限制的数量
	(3).磁盘调度策略(/sys/block/devname/queue/scheduler)
		noop(电梯式调度策略）、deadline（截止时间调度策略）、anticipatory(预料I/O调度策略)
		cat /sys/block/sda/queue/scheduler
		noop anticipatory deadline [cfq]
			
			echo deadline > /sys/block/sda/queue/scheduler

3) Система базы данных MySQL

MySQl的数据库的体系

4) Механизм хранения базы данных MySQL.

(1).Mysql之存储引擎MyISAM
	组成的结构：表为MYD和MYI、frm的文件组成
	特性：并发性和锁级别
		 MyISAM表支持索引类型
		 MyISAM表支持数据的压缩(命令行：myisampack)
		 	myisampack -b -f myIsam.MYI;
			压缩后的表不能进行写操作，只能进行读操作
	修复:对数据库中的表进行检查并修复:
		check table mytable;
		repair table mytable;

		myisamchk工具,修复时数据库服务必须停止
	限制：使用MySQL5.0之前时默认表的大小4G(存储大表修改MAX_Rows和AVG_ROW_LENGTH)
		 使用MySQL5.0之后的版本默认支持256TB
	适用的场景:非事务型的应用
			  只读类的应用
			  空间类的应用(GPS的数据)
(2).Mysql之存储引擎InnoDB
	mysql5.5.8之后版本默认使用的存储引擎
	组成结构：通过设置innodb_file_per_table参数存储的位置不同
				ON:独立表空间：tablename.ibd
				OFF:系统表空间:ibdataX
	建议：对于mysql中建议使用InnoDB的独立表空间
	特性：事务性存储引擎
		 完全支持事务的存储引擎
		 Redo log(存储已经提交的事务)和Undo log(存储未提交的事务)

		 InnoDB支持行级别锁
		 最大程序的支持并发
		 行级别的锁是由存储引擎层实现的
	锁：共享锁(读锁)、独占锁(写锁)
	 	表级锁、行级锁
		阻塞：确保事务并发的正常的执行
		死锁：两个或者两个以上的事务执行过程中相互等待对方的资源而产生的一种异常
	InnoDB状态检查：
		show engine innodb status;	
	适用场景：InooDB适用于大多数OLTP应用
(3).Mysql之存储引擎CSV
	特点：数据以文本的方式存储在文件中
		.CSV文件存储表的内容
		.CSM文件存储表的元数据如表的状态和数据量
		.frm文件存储表的结构的信息
		以CSV格式进行数据的存储
		所有的列必须不能为NULL的
		不支持索引(不适合大表，不适合在线处理)
		可以对数据文件直接进行编辑
	适用的场景：适合作为数据交换的中间表
			  mysql数据目录->csv文件->其他web程序
			  excel电子表格 -> csv文件 -> mysql数据目录
(4).Mysql之存储引擎Archive
	特点：以zlib对表数据进行压缩，磁盘I/O更少
		 数据存储在ARZ为后缀的文件中

		 只支持insert和select操作
		 只支持在自增的ID列上加索引
	适用场景：
		 日志和数据采集类的应用
(4).Mysql之存储引擎Memory
	特点：数据只保存在内存中
		 Memory存储引擎的I/O效率特别高
		 支持HASH索引和BTree索引
		 所有的字段为固定长度
		 不支持BLOG和TEXT等大字段
		
		 Memory存储引擎使用表级锁
		 表中存储数据的最大值由max_heap_table_size参数决定
	适用场景：用于查找或者映射表，例如邮编和地区
			 用于保存数据分析产生的中间表
			 用于缓存周期性聚合数据的结果表

5).Параметры сервера базы данных MySQL

(1).Mysql配置参数作用域
	全局参数 
		set global 参数名=参数值；
		set @@global.参数名：=参数值；
	会话参数
		set[session] 参数名=参数值；
		set @@session.参数名：=参数值；
(2).内存配置相关的参数
		确定可以使用的内存的上限
		确定MySQL的每个连接使用的内存
			sort_buffer_size join_buffer_size
			read_buffer_size read_rnd_buffer_size
		确定需要为操作系统保留多少内存
		如何为缓存池分配内存
			Innodb_buffer_pool_size
			总内存-（每个线程锁需要的内存*连接数）- 系统的保留内存
			key_buffer_size
(3).I/O相关配置参数
		InnoDb存储引擎的I/O参数设置：
		Innodb_log_file_size
		Innodb_log_file_in_group
		Innodb_log_buffer_size
		Innodb_flush_log_at_trx_commit
		
		Innodb_flush_method = O_DIRECT
		Innodb_file_per_table = 1
		Innodb_doublewrite = 1
	  MySIAM存储引擎的I/O参数设置：
		delay_key_write
			OFF:每次操作后刷新键缓冲中的脏块到磁盘
			ON:只对在键表时指定了delay_key_write选项的表使用延迟刷新
			ALL:对所有MYSIAM表都使用延迟键写入
(4).安全相关配置参数
		expire_logs_days 指定自动清理binlog的天数
		max_allowed_packet 控制MySQL可以接受的包的大小（32M）
		skip_name_resolve 禁用DNS查找

		sysdate_is_now 确保sysdate()返回确定性的日期
		read_only 禁止非super权限的用户写权限
		skip_slave_start 禁止Slave自动恢复
		sql_mode 设置MySQL所使用的SQL模式
			strict_trans_tables
			no_engine_subtitutoion
			no_zero_date
			no_zero_in_date
			only_full_group_by
(5).其他相关配置参数
		sync_binlog = 1控制MySQL如何向磁盘刷新binlog
		tmp_table_size和max_heap_table_size 控制内存临时表的大小(设置一致)
		max_connections = 2000 控制允许的最大连接数

6) Проектирование структуры базы данных MySQL и оптимизация SQL.

(1).过分的反范式化为表的建立太多的列
(2).过分的范式化造成太多的表关联
(3).在OLTP环境中使用不恰当的分区表
(4).使用外键保证数据的完整性

Последовательность оптимизации производительности

  • Проектирование структуры базы данных и оптимизация операторов SQL
  • Выбор механизма хранения базы данных и настройка параметров
  • Выбор и оптимизация системы
  • аппаратное обновление
Категории