高级Mysql（性能影响、存储引擎）

一：服务器硬件和操作系统对MySQL性能的影响：

1、什么是RAID

RAID是磁盘冗余队列的简称，（Redundant Arrays of Independent Disks）

简单来说RAID的作用就是可以把多个容量较小的磁盘组成一组容量更大的磁盘，并提供数据冗余来保证数据完整性的技术。

 

RAID 0是最早出现的RAID模式，也称之为数据条带。是组建磁盘阵列中最简单的一种形式，只需要2块以上的硬盘即可，成本低，可以提高整个磁盘的性能和吞吐量。RAID 0没有提供冗余或错误修复能力，但是实现成本是最低的。

RAID 1又称磁盘镜像，原理是把一个磁盘的数据镜像到另一个硬盘上，也就是说数据在写入一块磁盘的同时，会在另一块闲置的磁盘上生成镜像文件，在不影响性能情况下最大限度的保证系统的可靠性和可修复性。

常用的RAID组别（RAID 5、RAID10）

RAID 5 又称为分布式奇偶校验磁盘阵列，通过分布式奇偶校验块把数据分散到多个磁盘上，这样如果任何一个盘数据失效，都可以从奇偶校验块中重建。但是如果两块磁盘失效，则整个卷的数据都无法恢复。

RAID 10又称分布镜像，它是对磁盘先做RAID 1之后对两组RAID 1的磁盘再做RAID 0，所以对读写都有良好的性能，相对于RAID 5重建起来更简单，速度也更快。

 

 

2、固态存储

SSD和PCI-E SSD

相比机械磁盘固态磁盘有更好的随机读写性能。

相比机械磁盘固态磁盘能更好的支持并发。

相比机械磁盘固态磁盘更容易损坏。

固态硬盘的特点：1、使用SATA接口，可以替换传统磁盘而不需要任何改变。

                             2、SATA接口的SSD同样支持RAID技术。

 

固态存储PCIe卡的特点：1、无法使用SATA接口，需要独特的驱动和配置。

                                         2、价格相对于SSD要贵，但性能比SSD更好。

固态存储的使用场景：适用于存在大量随机I/O的场景。

                                    使用于解决单线程负载的I/O瓶颈。

 

3、网络存储SAN和NAS

SAN（Storage Area Network）和NAS（Network-Attached Storage）的两种外部文件存储设备加载到服务器上的方法。

SAN设备通过光纤连接到服务器，设备通过块接口访问，服务器可以做将其当硬盘使用。

NAS设备使用网络连接，通过基于文件的协议如NFS或SMB来访问。

 

4、服务器硬件对性能的影响：

CPU：

64位的cpu一定要工作在64位的系统下。

对于并发比较高的场景cpu的数量比频率重要。

对于cpu密集性场景和复杂sql则频率越高越好。

内存：

选择主板所能使用的最高频率的内存。

内存的大小对性能很重要，所以尽可能的打。

I/O子系统：

PCIe->SSD->RAID10->磁盘->SAN

5、操作系统对性能的影响：

MySQl适合的操作系统：

Windows、FreeBSD、Solaris、centOS

6、文件系统对性能的影响：

windows：fat、ntfs

linux：ext3、ext4、xfs

二：MySQL本身对自己的影响：

1、MySQL体系结构

select语句：如何从文件中获得我们所要查询的数据，这个具体的实现方式则是由下一层存储引擎层来实现的。

注意：存储引擎是针对于表的，而不是针对于库的（一个库中的不同表可以使用不同的存储引擎）

2、存储引擎之MyISAM

mysql5.5之前版本默认的存储引擎

mysql存储引擎表由MYD和MYI组成

frm表是记录表结构的

 

myisam的特性：

     并发性与锁级别（表级锁）

     表损坏修复

     check table tablename

     repair table tablename

     myisam表支持的索引类型：（支持全文索引）

     myisam表支持数据压缩：（命令行：myisampack）

限制：版本<mysql5.0时默认表大小为4G，如存储大表则要修改MAX_Rows和AVG_ROW_LENGTH

          版本>mysql5.0时默认支持为256TB。

适用场景：非事务型应用、只读类应用、空间类应用。

3、存储引擎之Innodb

mysql5.5之后版本默认存储引擎

innodb使用表空间进行数据存储：

innodb_file_per_table

on：独立表空间：tablename.ibd（为每一个表建立独立的表空间）

off：系统表空间：ibdataX

 

系统表空间和独立表空间要如何选择：

a、系统表空间无法简单的收缩文件大小

b、独立表空间可以通过optimize table 命令收缩系统文件

c、系统表空间会产生IO瓶颈

d、独立表空间可以同时向多个文件刷新数据

建议：对Innodb使用独立表空间（成了默认的配置）

 

把原来存在系统表空间中的表转移到独立表空间中的方法：

1、使用mysqldump导出所有数据库表数据

2、停止mysql服务，修改参数，并删除innodb相关文件

3、重启mysql服务，重建innodb系统表空间

4、重新导入数据

 

innodb的特性：

     innodb是一种事务性存储引擎

     完全支持事务的ACID特性

     Redo Log（重置日志）和Undo Log 回滚日志

     innodb支持行级锁

     行级锁可以最大程度的支持并发

     行级锁是由存储引擎层实现的

 

什么是锁？

     锁的作用是管理共享资源的并发访问

     锁用于实现事务的隔离性

锁的类型？

     共享锁（也称读锁）

     独占锁（也称写锁）

锁的粒度（锁最基本的单元）

    表级锁（开销小）

    行级锁（开销大）

 

阻塞和死锁

什么是阻塞？

什么是死锁？

 

innodb状态检查：show engine innodb status

innodb的适用场景：innodb适用于大多数OLTP应用

4、存储引擎之CSV

文件系统存储特点：

        数据以文本方式存储在文件中

       .CSV文件存储表内容

       .CSM文件存储表的元数据如表状态和数据量

       .frm文件存储表结构信息

功能特点：

        以CSV格式进行数据存储

        所有列必须都是不能为NULL的

        不支持索引（不适合大表，不适合在线处理）

        可以对数据文件直接编辑

适用场景：适合做为数据交换的中间表   （电子表格->CSV文件->MySQL数据目录）

5、存储引擎之Archive

文件系统存储特点：

       以zlib对表数据进行压缩，磁盘I/O更少。

      数据存储在ARZ为后缀的文件中

功能特点：

     只支持insert和select操作

     只允许在自增ID列上加索引

适用场景：日志和数据采集类应用（记录日志的表、记录数据采集的表）

6、存储引擎之Memory

文件系统存储特点：

     也称HEAP存储引擎，所以数据保存在内存中

功能特点：

     支持HASH索引和BTree索引（等值查找：HASH、范围查找：BTree）

     所有字段都为固定长度 varchar(10)=char(10)

     不支持BLOG和TEXT等大字段

     使用表级锁（行级锁比表级锁支持更大的并发）

     最大大小由max_heap_table_size参数决定的

     

     适用场景：用于查找或者是映射表，例如邮编和地区的对应表

                       用于保存数据分析中产生的中间表

                       用于缓存周期性聚合数据的结果表

7、存储引擎之Federated

功能特点：

       提供访问远程MySQL服务器上表的方法

       本地不存储数据，数据全部放到远程服务器上

      本地需要保存表结构和远程服务器的连接信息

如何使用：

      默认禁止，启用需要在启动时增加federated参数

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