mysql本身对性能影响的因素存储引擎、数据库配置、数据库表结构及sql语句

mysql存储引擎对mysql性能的影响

mysql与众不同的就是这种插件式存储引擎了，这种架构将数据库查询处理及其他系统的任务以及数据的存储，提取相分离，这种数据存储和数据处理相分离的设计呢，可以让我们在使用时，根据存储引擎不同的性能和特征以及其他需要来选择数据存储的方式。首先要清楚mysql的体系结构，第一层 客户端， 这一层代表可以连接到mysql服务器的客户端，例如 php java api Odbc  jdbc ，跟大部分cs架构一样，这一层主要是，连接处理，授权认证，安全等一些功能，每个链接到mysql客户端都会在服务器进程中拥有一个线程，这个连接查询，只会在这个单独的线程中执行，也就是前面所说的哪个连接的查询只能用到一个cpu的核心，第二层是比较核心的，这一层包括了 查询缓存、查询解析、查询优化，以及内置函数，如常用的DDL ，DML全是在这一层定义的，总之，所有跨存储引擎的功能都是在这一层来实现的，因为这一层称之为mysql服务层，这一层中实现了与存储引擎无关的特性，什么是与存储引擎无关的特性，比如说select语句，如何从文件中获得我们所要查询的数据，这个具体的实现方式是由下一层存储引擎层来实现，这个语句对所有的存储引擎来说，所要实现的功能都是一样的，获取存储在文件中的数据，根据我们的过滤条件进行过滤，然后把数据显示出来，select语句的功能是在mysql服务层实现的，而如何从文件中获得我们要查询的数据，则是有下一层存储引擎层来实现的，第三层存储引擎层，区别其他数据库最大的地方，最常用的存储引擎，innodb、myisam、XtraDB、CSV、Memory。根据不同的特点选择不同的存储引擎，注意，存储引擎是针对于表的而不是针对于库的，（理论上一个库里面不同的表可以使用不同的存储引擎）

一、MyISAM是在mysql5.58之前的版本默认的存储引擎，MyISAM存储引擎将表存储在两个系统文件中，一个是数据文件以MYD结尾的和一个是索引文件以MYI结尾的，对于mysql所有的存储引擎都会有一个以frm结尾的文件，这个文件是记录表的结构的文件，而MYD和MYI是MyISAM存储引擎特有的。

MyISAM的特性，1、并发性及锁级别，MyISAMS使用的是表级锁而不是行级锁，这就意味着，对表中的数据修改时，要对整个表进行加锁，而对表中的数据进行读取的时候也需要对所有的表加共享锁，从这里可以看出使用MyISAM引擎的表读取和写入这两种操作是互斥的，MyISAM对读写混合的操作的并发性不是很好，如果是只读的操作，并发性还算可以接受，因为共享锁并不会阻塞共享锁。

2、从表损坏修复的来看，MyISAM支持由于意外关闭而损坏MyISAM表进行检查和修复操作，但这里指的并不是事务的恢复，因为myisam并不是支持事务的存储引擎，所以它也不可能有事务恢复的相关日志，所以对MyISAM表修复会造成数据丢失，使用check table tablename 对表进行检查，使用repair table tablename 对有问题的表进行修复。

在数据库中新建一个myIsam表 存储引擎为myIsam，create table myIsam(id int,c1 varchar(10))engine=myisam;。然后查看存储文件里面包含了myIsam.frm，myIsam.MYD，myIsam.MYI，myIsam.frm存放的是表的结构信息，MYD文件存储的是myIsam表的数据信息，MYI存放的是索引的信息，这时候我们使用check table myIsam;检查这个表，然后使用repair table myIsam; 修复表Msg_text 显示也是ok，如果发现有损坏的表，可以使用repair table tablename；来进行修复，mysql还提供了一个工具myisamchk 这个工具也可以对myisam表进行修复，但是需要注意如果用命令行来修复myisam表，要先把mysql服务先停掉，如果在mysql运行的时候对表进行修复，有可能对表进行大的损坏。

3、MyISAM支持的索引，myisam支持全文索引，而且是在mysql5.7之前版本中，唯一原生就是支持全文索引的官方存储引擎，另外myisam表还支持，test，belog等字段前字符的前缀索引，如果myisam是一张很大的只读表的时候，也就是在表创建完，导入数据后，就不会在对表进行任何操作了，那么我们就可以对这样的表进行压缩操作，这样可以减少磁盘io。

4、所以MyISAM也是支持压缩的，如果对一张表压缩，可以使用myisampack命令来对表进行压缩表中数据，对表中的数据是独立进行压缩的，在读取单数据的时候呢，不必对整个表来进行解压，下面演示下，如何对一张表来进行压缩，myisampack -b -f myIsam.MYI，对压缩后的表只能进行读操作，不能进行写操作。

MyISAM存储引擎的限制，1、在mysql5.0 版本之前默认表的存储大小是4G，如果想要存储超过4g的数据的时候，要修改MAX_Rows 和AVG_ROW_LENGTH,这两个数相乘的大小就是表存储的最大的大小，大表修改这两个参数，等于对表进行重建，会话费一些时间，在Mysql5.0之后单表最大支持256TB，

MyISAM适用的场景:1、非事务型应用，myisam 本身是非事务存储引擎，是不支持事务的，关系型数据库是支持事务的，myisam是不支持事务的，2、myisam支持压缩，所以对只读类的报表，在mysql5.7之前myisam是唯一个支持空间函数的一个存储引擎。

二、在mysql5.58之后版本默认存储引擎是Innodb，代替了myisam，Innodb是事务存储引擎，Innodb是支持事务的，更适合处理大量的小事务，

Innodb与myisam另一个区别是，存储方式不同，Innodb有自己的表空间的概念，表空间数据是存储在表空间之中的，具体存放到哪个表空间由 innodb_file_per_table这个参数来决定， 如果这个参数为ON，则会为每个Innodb表建立一个独立的表空间，以tablename.ibd为扩展名的文件。如果这个参数为OFF则会把数据存放到系统共享表空间，也就是ibdataX 这个空间里，X代表一个数字，show variables like 'innodb_file_per_table'; 显示的是on，接下来新建一个表myinnodb，create table myinnodb(id int,c1 varchar(10)) engine='innodb'; 看这个表示如何存储的，可以看到有两个文件一个是myinnodb.frm、myinnodb.ibd，这两个文件，frm文件是记录表结构的，而idb文件是表实际存储的地方了。系统表空间和独立表空间如何选择，比较：系统表空间无法简单的收缩文件大小，造成大量空间的浪费，并且产生大量的磁盘碎片，从而降低系统的性能，如果使用独立表空间这个问题就很好解决了，可以通过optimize table命令收缩系统文件。系统表空间对IO，因为只有一个文件，所以多个表空间进行刷新的时候，实际上在文件系统上是顺序进行的，这样就会产生大量的IO瓶颈。而独立的表空间来说，每个表都有自己独立的表空间文件，所以在数据写入的时候，可以同时向多个文件刷新数据，所以对于频繁写入的操作，不适于系统表空间的存储，最好用独立表空间方式，建议，在Innodb使用独立表空间进行管理。

Innodb存储引擎的特性，1、Innodb是一种事务性存储引擎，完全支持事务的ACID

特性，也就是原子性，一致性、隔离性、持久性。Innodb是如何实现这几个特性的。

2、Innodb支持行级锁，行级锁是在写操作时，我们所需要的资源更少，这样我们支持的并发就会更多，Innodb的行级锁是由存储引擎层实现的，什么是锁，锁是数据库系统区别于文件系统的重要特性，锁的作用主要是管理共享资源的并发访问，一个用户像邮件投递邮件的时候，另一个用户会阻塞，无法向相同的邮件末尾写入邮件，锁的另一个特性就是隔离性， 对于未提交的事物，锁定的数据，是无法被其他事务所查询到的。锁的常见分类，共享锁（也称读锁），独占锁（也称写锁），从名字中可以看出读锁是共享的，也就是说相互不会被阻塞的，多个线程可以在同一时间读取同一资源，而不相互干扰，写锁是独占的，也就是排他的，一个写锁会阻塞其他的写锁或读锁，这是处于数据完整性的考虑，只有这样才能保证，在给定的时间里，一个线程能执行写入，并防止其他用户读取正在写入的同一资源，也就是前面所说的实现了事物的隔离性，

写锁不兼容 写锁和读锁， 读锁兼容读锁，Innodb的来说读锁和写锁都是行锁，所谓兼容性是指同一行记录的兼容性情况，锁的粒度含义，就是如果加锁资源的最小单位，比如在行上加锁，最小单位就是行，这个锁就称为行级锁，如果锁的最小单位是页，我们就称为页级锁，同理如果锁的最小单位是表的话，这个锁就是表级锁，这种一种提高共享资源并发性的方式就是让锁定义的对象尽可能的小，最理想的方式就是对修改的数据进行精确的锁定，任何时候在给定的资源上锁定的数据越少，并发性就越高，只要相互之间不产生阻塞就可以，

mysql所支持的两种锁的粒度，表级锁和行级锁，

表级锁开销小，并发性低，表锁会在加锁时候锁定整张表，当用户对表进行写操作的时候，要先进行解锁，这时候就会阻塞其他用户对表的读写操作，只有没有写锁的时候，其他读取的用户才能获得读锁，读锁之前说的是不会相互堵塞的，表级锁通常是在mysql服务器层所实现的。

行级锁可以最大程度的支持并发处理，同时开销也比表级锁开销要大，所以它是并发性高，Innodb实现了行级锁，行级锁是在mysql存储引擎中实现的，而不在mysql服务器中实现，

锁的另外两个概念，阻塞和死锁，

阻塞是因为不同锁之间兼容性的关系，在有些时刻，一个事务的锁在需要等待另一个事务释放它所占用的资源，这就形成了阻塞，比如前面演示的表级锁，第一个连接，在Innodb表加了写锁，第二个连接在表上加读锁，就要等第一个连接写锁释放，这样第一个连接就阻塞了第二个连接，阻塞是为了确保事务可以并发且可以正常的运行，但如果一个系统出现大量的阻塞就说明系统出现了问题，也许是在一个被频繁更新的表上出现了慢查询，或是一个频繁使用的资源加上了写锁，阻塞过多的时候可以是数据库连接大量的堆积，从而占用大量的系统资源，使得系统性能整体下降。

死锁是两个或两个以上事务在执行过程中，相互占用了对方等待的资源而产生的异常，从定义中可以看到，处在阻塞中的多个事务，阻塞事务占用了被等待阻塞事务的资源，而死锁呢是多个事务，相互之间互相占用对方等待的资源，另外一个不同点是死锁数据库会自动发现，并且在多个死锁的事务中，找到一个资源占用最少的事务来进行回滚操作，这样就可以是其他事务正常运行了，再说死锁可以由系统自动处理，如果只有少量的死锁并不会对系统造成什么样的影响，只要在应用程序中发现死锁并进行处理就可以，但是如果一个系统频繁的出现大量的死锁，通常死锁可以在多个事务中按这个相同顺序，访问所需要资源来解决，也可以通过增加相关索引来解决。

Innodb引擎与其他引擎区别还提供了一个监控工具是，show engine innodb status，两次采样之间至少间隔30s，

使用场景：应用打多数在线处理的场景中，mysql5.7之后支持全文索引和空间函数

三、CSV存储引擎，就是csv文件作为mysql表来处理，存储格式就是普通的csv文件，如果把数据存储在myisam和Innodb中，存储的文件是不能直接查看的，这两种存储引擎都是以二进制文件存储的，而csv是以文本方式存储的，csv不支持索引的，查找的时候要进行全表扫描。

四、如何选择正确的存储引擎

1、首先我们要考虑应用是否需要事务支持，2、在线热备方案 Innodb，不要使用混合引擎。

Mysql服务器配置对mysql的性能的影响

Mysql获取配置信息路径

命令行参数

mysqld_safe --datadir=/data/sql_data

配置文件

mysqld --help --verbose|grep -A 

mysql配置参数的作用域

全局参数

set global 参数名=参数值； 

回话参数

set [session] 参数名=参数值；  

 内存配置相关参数

确定可以使用的内存的上限，

确定mysql的每个连接使用的内存,mysql对一些缓存对每个连接的内存是单独分配的，所以使用的连接数越大，占用的内存也就会越多，

sort_buffer_size 排序缓存区的尺寸，这个参数定义了mysql每个线程使用的排序缓存区的大小，mysql不是在连接初始化的时候后就给每个缓冲区分配内存，而是在有查询或排序操作时候，才会给每个缓冲区分配内存，然而在mysql出现查询排序的时候，mysql会立即分配这个参数设置的大小内存给缓冲区，而不管该排序是否需要这么大的内存，有的时候会把这个值设置很大，增加查询效率，比如100M，如果有100个连接同时进行排序，那么mysql，就会分配出10g左右的内存，所以很容易造成服务器内存溢出，所以配置这个的时候要注意。

join_buffer_size连接缓冲区的尺寸，这个参数定义了mysql每个线程使用的连接缓冲区的大小，如果一个查询中关联了多张表，就会为每个关联 分配一个 连接缓冲，所有每个查询可能会有多个连接缓冲，所以这个参数也不能设置太大

read_buffer_size ，当对myisam表进行全表扫描时，所分配的读缓冲区的大小， mysql只会在有查询需要的时候才会对该缓存分配内存，同样也会一次性分配该参数的值，这个参数值是4k的倍数

read_rnd_buffer_size,索引缓冲区的大小，mysql也是只有在查询的时候，才会给该缓冲区分配内存，会分配内存需要的大小，不会分配指定的的大小。

以上四个参数是为了每个线程所分配内存的

如何为缓存池分配内存

Innodb_buffer_pool_size 定义了Innodb所使用的缓冲池的大小，这个参数对Innodb存储引擎性能是影响很大的，Innodb不仅要缓存索引还要缓存数据，Innodb缓存池还帮助写入，这个帮助数据一起顺序的写入到磁盘，性能影响最大，所以必须分配足够的内存，公式=总内存-（每个线程所需要的内存*连接数）-系统保留内存，官方手册中，参数配置值要是服务器内存75%以上，

key_buffer_size 这个参数定义了，这个缓冲池存储引擎用于myisam，只会缓冲索引，数据则是依赖操作系统的缓存，

I/O相关配置参数

这部分参数决定了mysql如何同步缓存池中的数据到磁盘上，已修改数据持久化的保存，这些操作对性能的影响非常的大 ，如果需要修改的数据立刻写入到磁盘上，那是很昂贵的操作。

Innodb I/O相关配置

Innodb_log_file_size控制了单个事务日志的大小，

Innodb_log_file_in_group控制了事务日志文件的个数，

事务日志文件总大小=Innodb_log_files_in_group*Innodb_log_file_size

事务日志文件是循环使用的，

数据库结构设计和SQL优化

数据库设计对性能的影响，

过分的反范式化为表建立太多的列，根据范式化适当对表进行拆分，如果一个比表由有太多的列会对性能造成很大的影响，

过分的范式化造成太多的表关联，最多关联的表的限制，最多只能关联61个表，

使用外键保证数据的完整性

总结

性能优化顺序

1、数据库结构设计和SQL语句，包括表结构优化、索引优化、sql语句优化

2、数据库存储引擎选择和参数配置优化，包括缓冲池和内存配置上

3、系统优化

4、硬件升级