MySQL 性能调优

数据库的调优是一个长远的话题,不是一朝一夕的事情。前期数据库的数据量较小并发小很多隐藏的问题发现不了。只有达到一定的规模之后,才会被慢慢的展现出来。所以说前期的数据库设计是多么的重要。

从宏观上来说,数据库的调优主要分为三个方面:硬件、网络和软件。硬件和网络是一个公司资金实力的展现,而软件方面则细分为数据库表结构(模式)的设计(范式、类型和存引擎)、SQL 语句与索引、配置文件参数设置、操作系统、文件系统、MYSQL 版本和体系结构的设计。

5.1 表结构的设计

在设计关系型数据库系统的时候,需要遵循的不同的规范要求被称为是范式。一般我们只需要满足第三范式就可以了。第三范式简单的来说就是不存在传递依赖关系。

ER 关系图,简称为实体关系图。他展示了实体、属性和之间的关联关系。但是在实际的数据库设计过程中,我们为了提高数据库查询的效率,适当的增加数据的冗余,打破了第三范式的要求。

5.2 字段类型的选择

选取数据存储的一个关键原则就是保小不保大,也就是说能使用占用空间较小的来存储那么就不使用大的。因为空间就是效率。特别是数据表关联时候,涉及到内存和磁盘 IO 等操作。

整数、字符串、时间类型

MYSQL 5.6 在关于在线 DDL 操作的一些说明:

MYSQL 5.6 关于添加索引、删除索引以及在一个会话中添加一个字段并不会导致影响其他会话的操作,也就是说这并不会导致锁表的现象(waiting for table metadata lock)。这个在 MYSQL 5.6 的版本中被称为是在线 DDL online DDL 操作。但是呢在这样的情况下就会发生锁表现象,比如:我当前在执行一个很大的查询操作,这个查询并没有结束,此时对数据表进行一个修改操作(Alter),此时你就会发生锁表情况。也就是说,在你进行数据表的 alter 操作的时候,你对数据表的增删该查并不会锁表,但是如果在这之前你对数据表进行了访问,那么就必须要等到该数据表访问结束之后,才可以进行 alter 操作。

行级锁的信息:

InnoDB 存储引擎是通过给索引上的索引项加锁来实现的,这也就意味着:只有通过索引条件来检索数据,InnoDB 才会使用行级锁,否则使用的还是表级别的锁。

测试用例说明:

有一个数据表 table,只有一个索引列 id,并且还是主键,此时开启两个会话事务,事务 1 执行了一个操作 select * from table,之后做了一个 update 的操作,更新了一个主键的属性值;此时开启事务 2,事务 2 一开始也是执行了与事务 1 同样的操作,此时事务 2 也执行修改事务 1 所指定的数据列,发现这个语句无法被执行,但是执行另外的一个主键所对应的属性,就可以被成功的执行。我们说在大并发的数据库访问下,大量的事务请求无法立即获取系统的锁资源而处于挂起的状态,此时会占用大量的系统互斥资源,造成严重的性能问题,此时需要合理的设置 innodb_lock_wait_timeout 锁等待超时的参数来解决,这个参数的值一般是 100。

行锁转表锁:

当你在对一个没有索引的数据表进行数据处理更新的时候,就会发现错误代码是 1205 的(lock wait timeout executed try restarting transaction),因为数据表是由行级锁转化为了表级别的锁的缘故。

两个事务都需要获取对方持有的排它锁才能够完成事务,这就是典型的死锁现象。当发生死锁的时候,InnoDB 存储引擎会自动的检索这个信息,他会让一个事务释放锁并回滚,另外的一个事务持有锁并继续完成事务。

InnoDB 的工作原理:就是把数据捞到内存中,被用户读写,这样就大大的增加了性能。因为内存数据的读取比磁盘要快很多。当数据全部被加载到内存中,这时的效率是最高的。可以充分的利用内存,减少磁盘的 IO 操作。每次数据库的版本的升级,在这个方面的改善也是最高的。MYSQL 有两种表空间管理模式:一种是共享表空间、一种是独立表空间。共享表空间的数据文件是 ibdata1,这个数据文件中存储的是元数据、数据、索引、插入合并缓冲、undo log 回滚日志,jb_logfile0,保存的是 redo log 重做日志。独立表空间则是把数据、索引、插入合并缓冲从 ibdata1 中分离出来,保存在了 .ibd 文件中,在 MYSQL 5.6 里面,通过配置文件也可以把 undo log 文件分离出来。这样做的目的是为了实现多块磁盘的 IO 操作。

事务的实现:

MYSQL 数据库在进行事务处理的时候使用的是日志先行的方式来保证事务可以快速和持久的运行,也就是说在写数据之前,先写日志。也就是说在开始一个事务的时候,会记录这个事务的 LSN 日志序列号,在执行事务的时候,会往 innodb_log_buffer 日志缓冲区中插入事务日志 redo log,当事务提交的时候,就会把缓冲区中的数据刷新到磁盘,这个动作是由参数 innodb_flush_log_at_trx_commit 来控制的。

Innodb_flush_log_at_trx_commit=0,表示每个事务提交的时候,每隔一秒把事务日志缓冲区中的数据写入到日志文件中,以及把日志文件中的数据刷新到磁盘上,他的性能是最好的,但是安全性是最差的,因为会丢失一秒的数据。

Innodb_flush_log_at_trx_commit=1,表示每个事务提交的时候,会把事务日志从缓冲区写入到日志文件中,并且刷新日志文件中的数据到磁盘上。

Innodb_flush_log_at_trx_commit=2,表示每个事务提交时,会把事务日志数据从缓冲区写入到日志文件中,每隔一秒刷新一次日志文件,但是并不一定刷新到磁盘上,而是取决于操作系统的调度。

可以使用命令:show innodb status\G; 来查看当前刷新事务日志的情况

Log sequence number:当前的 LSN 日志序列号

Log flushed up to:当前刷新到事务日志的 LSN 日志序列号

Last checkpoint at:当前刷新到磁盘的 LSN 日志序列号

除了记录事务日志文件之外,数据库还会记录一定量的撤销日志(undo log),undo log 和 redo log 正好相反,在对数据进行修改的时候,因为某些原因失败了,那么就可以利用这些撤销日志将数据库回滚到修改前的样子。redo log 是存放在 ib_logfile0 里面的而 undo log 则是被存放在 ibdata1 中,在 MYSQL 5.6 版本中可以被单独分离出来。

我们说在 MYSQL 数据库中,事务默认是自动提交的。

在事务提交以后,首先刷新到 binlog,然后再刷新到 redo log 里。如果在刷新到 binlog 的时候发生数据库的宕机,那么 MYSQL 数据库在下次启动的时候,由于 redo log 里面没有记录该事务就会回滚,但是二进制日志已经记录了该事务信息,不能被回滚。如果此时 slave 复制 master 上的数据,这就会导致主从数据的不一致性,那么 MYSQL 如何实现数据的一致性呢?我们说是 XA 事务保证了 MYSQL 数据库在刷新到 binlog 和 redo log 的一致性。因此也就是参数:innodb_support_xa=1。

事务的隔离级别:Repeatable Read

比如有两个线程,各自开启一个事务,此时事务 1 查询数据表中所有的数据,事务 2 也查询数据表所有的数据。此时事务 2 更新了表中的一条记录,此时再查询数据表中的数据,发现数据表中的数据是最新被修改的数据。此时事务 2 提交。此时事务 1 再次查询数据表中的所有的数据,发现读取的数据还是之前的数据,此时事务 1 提交之后再次查询数据表中的所有的数据,发现得到的数据是数据表中最新的数据,也就是被修改过之后的数据。

间隙锁:

间隙锁是为了防止幻读的存在,是在 Repeatable Read 事务隔离级别下,对当前数据进行条件、范围检索的时候,对范围内不存在的数据进行加锁。他对于高并发的业务数据有着很大的影响。

比如:当前有两个 Session,其中 session 1 进行一个操作(select * from table where column<10 lock in share mode),此时 session 2 插入一条记录,这条记录的 id<10,就会发生 1205 错误,lock wait timeout exceeded;try restart transaction,而此时插入数据大于 10 的发现没有什么问题。

SQL 优化和合理的利用索引:

在应用系统的初期,由于数据库的数据量较小,所以对于查询的 SQL 语句的编写等方面体会不出 SQL 语句的各种写法在性能方面的优略性。但是随着数据库数据量的增加,此时数据库的响应就成为了系统需要密切关注的一个方面,系统优化的一个重要方面就是 SQL 语句的优化,对于海量的数据而言,劣质的 SQL 语句跟优质的 SQL 语句之间的差别是上百倍。

如何定位执行很慢的 SQL 语句

数据库上线之后,会遇到很多的问题。比如数据库的查询等等,这是数据库 DBA 的日常工作。那么如何去定位数据库的慢查询 SQL,并根据数据库的日志得到相应的优化思路呢。

慢查询带来的数据库性能的直接影响就是系统的宝贵的 IO 资源,对于 MYSQL 数据库来说,IO 系统出现瓶颈会导致数据库连接数增大、锁表等等,特别是数据库的高并发下。开启慢查询记录功能带来的好处就是可以通过分析慢查询 SQL 来优化 SQL 语句。从而解决因为慢查询导致的问题。

开启慢查询日志的参数配置:

Slow_query_log = 1

Slow_query_log_file = mysql.slow

Long_query_time = 2

使用 mysqldumpslow 来分析耗时很长的 SQL 语句:

SQL 优化案例分析

和设计数据库一样,优化执行 SQL 语句可以提高应用程序的性能,如果不遵循一些基本的原则,那么即使你的数据库结构设计的怎么合理,数据库的配置如何好,也不会得到用户满意的效果。对于 SQL 查询,首先需要明确的是你要完成的目标,并努力使查询的效率更高,以最少的时间来检索数据。大多数的查询可以通过多种方式来实现,所以选择哪一种实现方式很重要。

下面我们讲述一下 SQL 语句优化的原则和方法:

NOT IN 子查询的优化

将一些子查询转化为连接查询(left join where)

Select * from table1 where id not in(select id from table2)

Select * from table1 left join table2 on table1.id=table2.id where table2.id is null

通过子查询来删除已查询的数据

Delete from table where id in(select id from table)

此时数据库会报错 1093:you can’t specify target table for update in from clause;

模式匹配的查询 LIKE 的优化:

对于 MYSQL 数据库来说,类似 LIKE ’%XXX%’ 这样的查询是不会用到索引的,但是我们可以通过索引覆盖的方式来实现或者是提高数据库的查询效率

因为 MYSQL 的索引覆盖是消耗资源最少的数据查询,减少了系统的磁盘 IO,因为直接访问叶子节点就可以查询到数据。

LIMIT 的分页查询:

例如实际中的一个例子:

Select * from table order by id limit ,100;

对于上述的一个查询,从 id = 1 到 id 为 这样的数据扫描定位需要花费很长的时间,相当于一个全表扫描了。他的性能是非常差的。我们可以做如下的一个优化:

Select * from table where id >= order by id limit 10;

然而据我所知,这样的查询没有任何意义,无法满足业务的需求,看到很多人说他们可以优化。要是再碰到有人这么说就当个笑话听听就行了。

COUNT 的数据统计:

我们说 count(*) 在 MYSQL 数据库里面对于 InnoDB 来说是速度较慢的一种查询,特别是数据库的数据量较大的时候。

COUNT (辅助索引) 和 count(*) 的比较:

我们说聚集索引是将主键和数据保存在一起了,而辅助索引则是并不保存数据,而是一个指向对应数据块的指针。

Count(distinct)的优化:

我们说当遇到这方面的数据统计的时候,首先需要做的就是根据 distinct 查询出去重数据,然后再进行数据统计。

我们说 MYSQL 的 InnoDB 在使用 COUNT 做数据统计的时候,往往会锁表,注意并不是行级别的锁,而是表级别的锁。

OR 条件的优化:

Select * from table where id = 11 or age = 11

Select * from table whete id = 11 union select * from table where age = 11

分析上面的两种查询,发现下面的查询使用到了索引,上面的第一中查询没有使用到索引。

ON DUMPLICATE KEY UPDATE 子句

MYSQL 数据库中有一种非常高效的主键冲突判断机制,就是当发生主键冲突的时候,执行对应的主键 update 操作

不必要的排序操作

不必要的 SELCT 嵌套操作

不必要的连接操作

使用 WHERE 子句来代替 HAVING 子句

我们说 having 子句是对查询的结果进行数据过滤的,这样就需要对数据进行排序,总计等操作。

合理的设置索引:

适当的索引技术对于数据库的设计来说是至关重要的,但是遗憾的是索引也是需要花费较大的开销的,每次对数据表的操作(DELETE UPDATE 和 INSERT)都会需要花费时间去更新索引,增加了对数据表的操作。此外索引还增加了数据库的规模。只有当某一个数据列被使用到了 WHERE 子句中的时候,才能享受到索引带来的性能的提升。如果不能使用到索引技术,那么他就没有任何的价值了。而且还会较多的时间去维护。

单列索引还是多列索引

字段使用函数将不会使用到索引

致命的无引号导致数据库数据查询全表扫描

当扫描的数据记录量达到全表结果集的 30% 的时候,将不会使用到索引,而是全表扫描的方式来实现的。

考虑不为某些数据列添加索引

ORDER BY 和 GROUP BY 的优化

MYSQL 5.6 InnoDB 引擎支持全文索引

MYSQL 5.6 支持 Multi_Range Read 索引优化

当对一个较大的数据表的辅助索引进行基于范围的数据查询的时候,会导致产生大量的随机 IO 操作,在 5.6 中优化器会首先扫描索引,然后收集到每一行的主键值,并对主键进行排序,然后实现基于主键的顺序 IO 访问此时就实现了随机 IO 到顺序 IO 的转换

MYSQL 5.6 的 Index merge 合并索引

也就是说在 MYSQL 的 5.6 版本中一个查询可以用到不止一个索引的效率。

MYSQL 5.6 支持 Index Condition Pushdown 索引优化

MYSQL 的配置文件的优化

在 MYSQL 数据库性能调优中,首先需要考虑的是模式的设计,这一点非常重要,一个糟糕的数据模式的设计即使是在一个性能高的服务器上运行,也是会很差的。与模式的设计类似,数据库的查询也会影响 MYSQL 数据库的性能。应当避免低效的 SQL 语句的查询。最后才是参数配置文件的优化。因为 MYSQL 数据库默认的参数配置很差,是不能在正式的环境中运行的,因此就需要对系统的参数配置文件进行优化。

Per_thread_buffers:

可以简单的理解为:为每一个连接到 MYSQL 数据库的用户进程分配的内存。主要包含如下的几个参数:

Read_buffer_size:这个参数用于数据表的顺序扫描,表示每个线程分配到的缓冲区的大小。比如:在进行全表扫描的时候,MYSQL 会按照数据的存储顺序依次读取数据块。然后将他们存储在 read_buffer_size 中,这个参数的默认大小是 128K,可以配置的范围是 128K-256K 之间。

Read_rnd_buffer_size:这个参数用于数据表的随机读取,表示每个线程分配的缓冲区的大小,比如做一次非索引字段的排序操作的时候。就可以利用这个缓冲区来存取数据。默认是 256K。

Sort_buffer_size:在数据表进行 order by 和 group by 排序分组操作的时候,由于排序的字段没有索引,会出现 using filesort,为了提高性能,可以用此参数增加每个线程分配的缓冲区大小。默认是 2M。一般出现 using filesort 的时候,就表示索引设置不合理。

Thread_stack:该参数表示每个线程的堆栈大小默认是 192K,可以设置为 256K。

Join_buffer_size:在进行数据表的连接查询操作的时候,如果你连接的字段没有索引的时候,就会出现using join buffer。可以增加这个参数的配置调整缓冲区的大小。

Binlog_cache_size:一般来说如果数据库没有大的事务的时候,将其设置为 1-2M,如果存在大的事务,那么可以增加这个参数。

Max_connections:该参数用来设置最大连接数,默认是 100.一般设置为 300-1000 即可。

对于上述的这几个参数作如下的说明:per_thread_buffers

(read_buffer_size+read_rnd_buffer_size+sort_buffer_size+thread_stack+join_buffer_size+binlog_cache_size)*max_connections

Global_buffers优化:

参数 Global_buffers 用于在内存中缓存从数据文件中检索出来的数据块。主要的参数如下:

Innodb_buffer_pool_size:这个参数是 InnoDB 存储引擎的核心参数,默认是 128M,一般是操作系统物理内存的 70% 左右。

Innodb_additional_men_pool_size:该参数用来存储数据字典和其他内部数据结构,数据表越多,需要在这里分配的内存越多。如果 InnoDB 用光了这个池内的内存,InnoDB 开始从操作系统分配内存,并且往 MYSQL 的错误日志文件中写入警告信息。默认是 8M,该参数可以被修改,建议设置为 16M。

Innodb_log_buffer_size:事务日志使用的缓冲区。InnoDB 在写事务日志的时候,为了提高性能,写将信息写入 innodb_log_buffer 中,当满足 innodb_flush_log_trx_commit 参数设置的相应条件的时候,再将日志写入到文件中。该参数建议设置为 16M。

Query_cache_size:缓存 select 语句和结果集大小的参数。

Global_buffers 的内存计算公式:

Innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+query_cache_size

Per_thread_buffers+global_buffers 的和不能大于总的物理内存的大小,否则高并发的情况下会造成内存溢出。

Query Cache 在不同环境下的使用:

如果你的环境中,写操作很少,读操作频繁。那么可以适当的打开 query_cache_type = 1,会对系统的性能有所提升。

如果你的环境写操作很多,那么就不合适了。因为每当数据表的内容被修改之后,Query Cache 缓冲区的结果集就要随之被刷新,频繁的数据刷新反而会降低系统的性能。需要设置的参数包含(query_cache_size 和 query_cache_type),在 MYSQL 5.6 版本中 InnoDB 数据页的大小是 16K 和 8K,这个需要自己配置(参数是 innodb_page_size)。

做一个简单的配置文件的配置信息:

Port = 3306

Socket = /tmp/mysql.sock

Server-id = 1

Port = 3306

Basedir = /usr/local/mysql

Datadir = /usr/local/mysql/data

Socket = /tmp/mysql.sock

Default-storage-engine = InnoDB

Character-set-server = utf8

Wait_timeout = 100

Connect_timeout = 20

Interactive_timeout = 100

Event_scheduler = ON

###############******binlog********############

Log-bin = /usr/local/mysql/mysql-bin

Binlog_format = row

Max_binlog_size = 128M

Binlog_cache_size = 2M

Expire-logs-days = 5

##############********slow log*******#############

Slow_query_log = 1

Slow-query_log_file = /usr/local/mysql/mysql.slow

Long-query-time = 2

##############*********error log******#############

Log-error = /usr/local/mysql/error.log

#############*****per_thread_buffers*******#############

Max_connections = 1024

Max_user_connections = 1000

Max_connect_errors = 1000

Max_allowed_packet = 128M

Sort_buffer_size = 512K

Read_buffer_size = 512K

Read_rnd_buffer_size = 512K

Join_buffer_size = 512K

Tmp_table_size = 64M

Max_heap_table_size = 64M

Query_cache_type = 0

Query_cache_size = 0

Thread_concurrency = 32

Thread_stack = 256K

###########******InnoDB*********############

Innodb_data_home_dir = /usr/local/mysql/data

Innodb_log_group_home_dir = /usr/local/mysql/logs

Innodb_data_file_path = ibdata1:2G:autoextend

Innodb_buffer_pool_size = 50G

Innodb_buffer_pool_instances = 8

Innodb_additional_mem_pool_size = 16M

Innodb_log_file_size = 1024M

Innodb_log_buffer_size = 64M

Innodb_log_files_in_group = 3

Innodb_flush_log_trx_commit = 2

Innodb_lock_wait_timeout = 10

Innodb_sync_spin_loops = 40

Innodb_max_dirty_pages_pct = 90

Innodb_support_xa = 1

Innodb_thread_concurrency = 0

Innodb_file_io_threads = 4

Innodb_flush_method = O_DIRECT

Innodb_file_per_table = 1

Innodb_read_io_threads = 16

Innodb_write_io_threads = 16

Innodb_io_capacity = 2000

Innodb_file_format = Barracuda

Innodb_purage_threads = 1

Innodb_old_blocks_pct = 75

Innodb_change_buffering = all

Transaction_isolation = READ-COMMITTED

谨慎的使用分区表功能: