影响mysql数据库性能的因素

影响mysql数据库性能的因素

一、影响性能的几个方面

1. 服务器硬件。（CPU、内存、磁盘I/O）

2. 服务器系统（系统参数优化）。

3. 数据库存储引擎的选择。 MyISAM： 不支持事务，表级锁。InnoDB: 支持事务，支持行级锁，事务ACID。

4. 数据库参数配置

5. 数据库结构设计和sql语句

1.SQL和索引优化

1.1SQL

1.1.1优化SQL语句的一般步骤：

①通过show status 命令了解各种SQL的执行效率，

1

show  status

显示当前连接所有统计参数的值。Com_xxx表示每个xxx语句执行的次数，通常需要注意的是下面几个参数：Com_select/Com_insert/Com_update/Com_delete。eg:

1

show status like 'Com_%';

②定位执行效率较低的SQL语句·通过show processlist命令实时查看当前SQL的执行情况；·通过慢查询日志（结束以后记录）定位出现的问题。

③通过explain 或 desc分析低效SQL的执行计划select_type(simple/primary/union/subquery)/table/type/possible_keys/key/key_len/rows/extra

④通过show profile 分析SQLshow profile 能帮我们了解时间都耗费到哪里去了。MySQL从5.0.37版本开始增加了show profile和show profiles语句的支持，通过secect @have_profiling命令能够看到当前MySQL是否支持profile，通过show profiles我们能够更清楚了解SQL执行的过程，通过show profile for query我们能看到执行过程中线程的每个状态和消耗的时间。

⑤通过trace分析优化器如何选择执行计划MySQL5.6提供了对SQL的跟踪trace,能帮我们了解为什么优化器选择执行A计划而不是B计划，进一步理解优化器的行为。

⑥确定问题并采取相应的优化措施

1.1.2两个简单实用的优化方法

①定期分析和检查表

1

analyze table tbl_name;

2

check table tbl_name;

②定期优化表

1

optimize table tbl_name;

1.1.3常用SQL的优化

①优化insert语句·如果从同一客户端插入很多行，应该尽量使用多个值表一次性插入；·如果从不同客户端插入很多行，可以使用insert delayed语句先把数据放在内存的队列中，并不真正写入磁盘，比每条语句分别插入快得多；·当从一个文本文件装载一个表时，使用load data infile，这通常比使用很多insert 语句快20倍；·如果在MyISAM表中进行批量插入，可以通过增加bulk_insert_buffer_size变量值的方法来提高速度。

②优化 order by语句MySQL中有两种排序方式，第一种通过有序索引顺序扫描直接返回有效数据，不需要额外的排序，操作效率较高；第二种对返回的数据进行排序，也就是常说的Filesort排序，所有不是通过索引直接返回排序结果的排序都是filesort排序。优化目标：尽量通过索引直接返回有序数据，减少额外的排序。通过创建合适的索引能减少filesort出现，但是某些情况下，条件限制不能让filesort消失，那就需要想办法加快filesort的操作。filesort有两种排序算法，一种是一次扫描算法（较快），二种是两次扫描算法。适当加大系统变量max_length_for_sort_data的值，能够让MySQL选择更优化的filesort排序算法；适当加大sort_buffer_size排序区，尽量让排序在内存中完成，而不是通过创建临时表放在文件中进行。尽量只使用必要的字段，select具体的字段名称，而不是select * 选择所有字段，这样可以减少排序区的使用，提高SQL性能。

③优化group by 语句MySQL默认对所有group by col1,col2...的字段进行排序，可以指定order by null禁止排序。

④优化嵌套查询MySQL5.5及以下版本，子查询的效率不如连接查询（join），因为MySQL不需要在内存中创建临时表来完成这个在逻辑上需要两个步骤的查询工作。

⑤优化or 查询对于含有or的查询子句，如果要利用索引，则or之间的每个条件列都必须使用索引；如果没有索引，可以考虑增加索引。MySQL在处理含有or的查询时，实际上对or的各个字段分别查询后的结果进行了union操作。

⑥优化分页查询·第一种 在索引上完成排序分页操作，然后根据主键关联回原表查询所需要的其他列的内容；·第二种 在排序字段不会出现重复值的情况下，新增一个参数记录上次查询的最后一条记录，将limit m,n转化成limit n.

⑦使用SQL提示就是在SQL语句中加入一些认为提示，让MySQL按照特定方案执行，以达到优化操作的目的。·use index 指定MySQL参考的索引而忽略别的索引·ignore index 让MySQL忽略某个或某些索引·force index 强制MySQL使用某个特定的索引

⑧其他·使用REGEXP，比如代替like.·使用rand()提取随机行·表的字段尽量不使用自增长变量，在高并发的情况下可能会对MySQL的效率有较大影响。

1.2索引优化

MySQL的索引在存储引擎层实现，而不是在服务器层。可以通过show status like 'Handler_read%'命令来查看索引使用情况。

1.2.1MySQL 中索引的存储类型

MySQL 中索引的存储类型目前有四种（B-Tree、Hash、空间索引R-Tree、全文索引Full-text），具体和表的存储引擎相关；MyISAM 和 InnoDB 存储引擎都支持 B-Tree 和全文索引（Full-text，InnoDB 5.6 +）；MyISAM还支持空间索引（R-Tree）；Memory/Heap存储引擎可以支持 HASH和 B-Tree 索引，不过只有Memory/Heap支持 Hash索引。

1.2.2MySQL如何使用索引

（1）MySQL使用索引的典型情景①匹配全值（match the full value）②匹配值的范围（match a range of values）③匹配最左前缀（match a leftmost prefix）最左匹配原则是MySQL中B-Tree索引使用的首要原则。④只查询索引（index only query）当然where子句中要满足最左匹配原则⑤匹配列前缀（match a column prefix）使用复合索引的第一列的开头一部分⑥复合索引中，一部分匹配精确内容 and 其他部分匹配一个范围（match one part exactly and match a range on another part）⑦列名是索引，那么column_name is null就会使用索引,比如where column_name is null（2）MySQL不使用索引的典型情景①like "%query"不使用B-Tree索引，但like "query%"会使用B-Tree索引。②数据类型出现隐式转换的时候也不会使用索引。尤其当列类型是字符串时，一定记得在where条件中把字符串常量值用引号引起来，比如where last_name = '1';③使用复合索引时，查询条件不包含索引的最左边部分④用or 分割的条件，如果其中一个列中没有索引，则涉及的另一个索引也不会被用到。⑤如果MySQL估计使用索引比全表扫描更慢，则不使用索引。

2.优化数据库对象：表结构、字段类型、存储引擎

2.1表结构

2.1.1三范式和反三范式

2.1.2分区、分表（对表进行水平或者垂直拆分）

2.2字段选择合适的数据类型

procedure analyse() 可以对当前应用的表进行分析，对数据表中列的数据类型提出优化建议。

2.3选择合适的存储引擎

MySQL5.5及之后版本默认的存储引擎：InnoDB。

2.3.1 InnoDB使用表空间进行数据存储。

show variables like 'innodb_file_per_table

如果innodb_file_per_table 为 ON 将建立独立的表空间，文件为tablename.ibd；如果innodb_file_per_table 为 OFF 将数据存储到系统的共享表空间，文件为ibdataX（X为从1开始的整数）；

.frm ：是服务器层面产生的文件，类似服务器层的数据字典，记录表结构。

2.3.2 (MySQL5.5默认)系统表空间与(MySQL5.6及以后默认)独立表空间

1.1 系统表空间无法简单的收缩文件大小，造成空间浪费，并会产生大量的磁盘碎片。1.2 独立表空间可以通过optimeze table 收缩系统文件，不需要重启服务器也不会影响对表的正常访问。2.1 如果对多个表进行刷新时，实际上是顺序进行的，会产生IO瓶颈。2.2 独立表空间可以同时向多个文件刷新数据。

强烈建立对Innodb 使用独立表空间，优化什么的更方便，可控。

1.1.3 系统表空间的表转移到独立表空间中的方法

1、使用mysqldump 导出所有数据库数据（存储过程、触发器、计划任务一起都要导出 ）可以在从服务器上操作。2、停止MYsql 服务器，修改参数（my.cnf加入innodb_file_per_table），并删除Inoodb相关文件（可以重建Data目录）。3、重启MYSQL，并重建Innodb系统表空间4、 重新导入数据

或者 Alter table 同样可以的转移，但是无法回收系统表空间中占用的空间。

2.4 InnoDB存储引擎的特性

2.4.1 特性一：

1. Innodb 是一种事务性存储引擎。

2. 完全支持事务的ACID特性。

3. 支持事务所需要的两个特殊日志类型：Redo Log 和Undo Log

Redo Log：实现事务的持久性(已提交的事务)，顺序读写：包括innodb_log_buffer_size：日志缓冲区大小innodb_log_files_in_group:ib_logfile0 和 ib_logfile1

Undo Log：未提交的事务，独立于表空间，需要随机访问，可以存储在高性能io设备上。

Undo日志记录某数据被修改前的值，可以用来在事务失败时进行rollback；Redo日志记录某数据块被修改后的值，可以用来恢复未写入data file的已成功事务更新的数据。

2.4.2 特性二：

1. InnoDB支持行级锁

2. 行级锁可以最大程度地支持并发

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

将table_name加表级锁命令：lock table table_name write; 写锁会阻塞其它用户对该表的‘读写’操作，直到写锁被释放：unlock tables；

1. 锁的开销越大，粒度越小，并发度越高。

2. 表级锁通常是在服务器层实现的。

3. 行级锁是存储引擎层实现的。innodb的锁机制，服务器层是不知道的

阻塞和死锁：

（1）阻塞是由于资源不足引起的排队等待现象。（2）死锁是由于两个对象在拥有一份资源的情况下申请另一份资源，而另一份资源恰好又是这两对象正持有的，导致两对象无法完成操作，且所持资源无法释放。

2.5 如何选择正确的存储引擎

参考条件：

1. 事务

2. 备份(Innobd免费在线备份)

3. 崩溃恢复

4. 存储引擎的特有特性

3.优化MySQL server

3.1MySQL内存管理和优化

3.2InnoDB log机制及优化

3.3调整跟并发相关的MySQL参数

（1）max_connections（2）back_log（3）table_open_cache（4）thread_cache_size（5）innodb_lock_wait_timeout

4.磁盘I/O优化

4.1使用磁盘阵列（RAID）

4.2使用Linux虚拟文件卷模拟RAID

4.3符号连接（Symbolic Links）分布I/O

利用操作系统的符号连接（Symbolic Links）将不同的数据库、表或索引指向不同的物理磁盘，从而达到分布磁盘I/O的目的。

4.4禁止操作系统更新文件的atime属性

4.5用裸设备（Raw Device）存放InnoDB的共享表空间

4.6调整I/O调度算法

4.7RAID卡电池的充放电引起的性能波动

4.8NUMA架构优化

非一致存储访问结构（Non-Uniform Memory Access, NUMA）

5.应用优化

5.1使用连接池

5.2减少对MySQL的访问

①理清应用逻辑，能一次取出的数据不用两次；②使用查询缓存MySQL的查询缓存（MySQL query cache）是4.1版本之后新增的功能，作用是存储select的查询文本和相应结果。如果随后收到一个相同的查询，服务器会从查询缓存中重新得到查询结果，而不再需要解析和执行查询。查询缓存适用于更新不频繁的表，当表更改（包括表结构和数据）后，查询缓存会被清空。③在应用端增加cache层

④负载均衡负载均衡（Load Balance）是实际应用中使用非常普遍的一种优化方法，它的机制就是利用某种均衡算法，将固定的负载量分布到不同的服务器上，以此来减轻单台服务器的负载，达到优化的目的。负载均衡可以用在系统中的各个层面中，从前台的 Web 服务器到中间层的应用服务器，最后到数据层的数据库服务器，都可以使用。·利用 MySQL 复制分流查询和更新操作利用 MySQL 的主从复制可以有效地分流更新操作和查询操作，具体的实现是一个主服务器承担更新操作，而多台从服务器承担查询操作，主从之间通过复制实现数据的同步。通过复制来分流查询和更新是减少主数据库负载的一个常用方法，但是这种办法也存在一些问题，最主要的问题是当主数据库上更新频繁或者网络出现问题的时候，主从之间的数据可能存在比较大的延迟更新，从而造成查询结果和主数据库上有所差异。因此在设计应用的时候需要有所考虑。