MySQL性能影响因素及优化方案

一.Myslq性能影响因素

1.数据库连接数

mysql数据库默认连接为100，虽然可以通过initialSize、minIdle、maxActive等进行调优，但是由于受硬件资源的限制连接数不可能无限制的增加，可能会出现最大连接数不能满足实际需求情况，这时会出现系统业务阻塞。

2.空间存储问题（表数据量大）

当单表数据量超过1000万条时普遍会出现读取性能方面的问题。从索引角度分析，未命中索引时数据库系统会进行全表扫描，数据量越大，扫描时间越长；而且B+TREE索引时存放在硬盘上的，数据量越大B+TREE层次越深，IO次数会越多

3.硬件资源限制

数据库服务器相对于普通的应用程序服务器相比，资源性对集中，单台主机上需进行的计算量会较大，cpu的处理能力非常重要；数据库是存储数据的地方，主机的磁盘和内存对IO性能会影响较大；数据库传递数据量也会较多，因此主机的网络设备性能也是系统的瓶颈之一，硬件资源还会直接影响数据库每秒查询数QPS和每秒事务数TPS。

4.查询语句

数据库管理软件中，最大的瓶颈在于磁盘的IO，即数据的存取操作上，对于同一份数据，以不同方式去查找相同内容时所需读取的数据量以及消耗的资源有着很大的区别，SQL语句的优劣对数据库系统性能会造成很大的影响。

二.数据库优化方案

1.SQL语句优化

(1)什么是SQL

SQL：结构化查询语言(Structured Query Language)，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。

(2)SQL语言的分类

DQL数据查询语言：SELECT <字段名表>；FROM <表或视图名>；WHERE <查询条件> DML数据操纵语言：INSERT；UPDATE；DELETE DDL数据定义语言：创建数据库中的各种对象（表，视图，索引等），隐形提交，不能回滚 DCL数据控制语言：授予或回收访问数据库的某种特权，并控制数据库操纵事务发生的时间及效果，对数据库实行监视等。GRANT，ROLLBACK，COMMIIT

(3)SQL语句的优化

SQL优化的一般步骤：

通过show status命令了解各种SQL的执行频率定位执行效率较低的SQL语句(尤其是select语句)通过explain或desc分析低效率的SQL语句的执行情况确定问题并采取相应的优化措施尽量不要使用not in 等

优化方法：

相同的查询要求SQL语句保持一致（包括格式和大小写区别），避免多次解析在where子句中尽量避免使用!=或<>操作符，否则引擎会放弃使用索引进行全表扫描尽量避免在 where 子句中对字段进行 null 值判断和使用or来连接条件避免在where子句中对字段进行表达式与函数或其他表达式的运算操作慎用not in ，对于连续的数值，能用between就不要用in，尽量用exists代替in使用update语句如果只需更改某些字段就不要update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。使用索引时如果是复合索引，使用索引中的第一个字段作为条件，且尽可能让字段顺序和索引顺序一致一个表的索引最好不要超过6个，过多的索引会降低insert和update的效率（insert和update可能会重建索引）只含数值信息的字段尽量不要设计成字符型，会降低查询和连接的性能，增加存储消耗（引擎处理查询和连接时会逐个比较每一个字符，数值只用比较一次）尽可能使用varchar/nvarchar代替char/nchar，变长字段存储空间小，可以节省储存空间查询时尽量避免使用select * from table,用具体字段代替 “*”，避免查询时调用过多的资源对于几百条数据；量的表的JOIN,先分页再JOIN，否则逻辑读会很高，性能很差避免频繁删除和创建临时表，以减少系统表资源的消耗新建临时表时如果插入的数据量很大，适应select into 代替 create table,避免造成大量log，以提高速度避免大事务操作，提高系统的并发能力

2.创建索引

合理的创建和使用索引可以大大的提高查询速度（索引是在存储引擎中实现的，而不是在服务器中实现的）

(1)什么是索引

索引是帮助高效获取数据的数据结构，类似书的目录，能快速定位数据，加快查询速度。分为普通索引，唯一索引，复合索引，全文索引等。索引本身以文件的形式存放在磁盘，需要时才加载至内存，添加索引会增加磁盘开销；写数据时需要更新原有索引，会降低表添加，更新，删除的速度。

(2)索引的优化

在where子句中出现的列，在join子句中出现的列创建索引考虑列中值的分布，索引的列的基数越大，索引的效果越好对字符串列进行索引，指定一个前缀长度（使用短索引），可节省大量索引空间，提升查询速度。不过度索引，只创建所需索引

3.缓存

数据库属于IO密集型的应用程序，优化IO,尽可能将磁盘IO转换成内存IO可以很好的优化数据库性能可以分别通过优化以下参数来对IO进行优化

(1)query_cache_size / query_cache_type （global）

query_cache_size设置用于缓存Result的内存大小，一般设置为256MB；query_cache_type （global）可设置为0/1/2;分别表示完全不使用query_cache;除显示要求不使用外其余所有select都使用query_cache和只有显示要求才使用query_cache 缺点：当表中的数据有任何变化时会导致所用引用了该表的select语句在query_cache中的缓存数据失效，不适应于数据变化频繁的情况

(2)binlog_cache_size （global）

用于开启binlog记录功能的环境，可短时间内临时储存binlog数据的内存区域。当数据库没有大事务，写入不是非常频繁时，设置为2MB-4MB比较合适；如果事务较大和写频繁可以适当将该参数调高 可通过binlog_cache_use 以及 binlog_cache_disk_use来分析是否有大量的binlog_cache由于内存大小不够而使用临时文件缓存来看设置的binlog_cache_size是否足够。

(3)key_buffer_size （global）

key_buffer_size 参数是默认MySQL 配置文件中设置最大的一个内存参数，用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果我们有足够的内存，这个缓存区域最好是能够存放下我们所有的 MyISAM 引擎表的所有索引，以尽可能提高性能。

(4)innodb_buffer_pool_size（global）

在使用innodb存储引擎时innodb_buffer_pool_size 参数是影响我们性能的最为关键的一个参数，用来设置用于缓存 InnoDB 索引及数据块的内存区域大小，类似于 MyISAM 存储引擎的 key_buffer_size 参数

(5)innodb_additional_mem_pool_size（global）

设置了InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小，所以当我们一个MySQL Instance中的数据库对象非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。

(6)innodb_log_buffer_size （global）

InnoDB 存储引擎的事务日志所使用的缓冲区,可以通过 innodb_log_buffer_size 参数设置其可以使用的最大内存空间。

4.读写分离

读写分离，基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库的写操作是比较耗时（10000条数据到要约3分钟），读只需要5s,通过读写分离可以提高数据查询效率

5.分库分表

单个表数据量越大（一个数据库数据量到1T-2T就是极限）， 单个数据库服务器压力过大，读写锁，读写速度遇到瓶颈（并发量几百），插入操作重新建立索引效率越低。 解决大数据量性能优化最有效的方案是采用读写分离和分库分表的方式将数据进行分布式存储。将存放在一台数据库服务器中的数据,按照特定的方式进行拆分,分散存放到多台数据库服务器中,以达到分散单台服务器负载的目的

(1) 水平分割

按照表中指定字段的分片规则,将表记录按行切分,分散存储到多个数据库中

(2)垂直分割

将单个数据库的多个表按业务类型分类,分散存储到不同的数据库