mysql数据库如何优化？谁能给出点具体的解决方案？

1、explain：解释sql的执行计划，后边的sql不执行

2、explain partitions ：用于查看存在分区的表的执行计划

3、explain extended：待验证

4、show warnings:

5、show create table:查看表的详细的创建语句，便于用户对表进行优化

6、show indexes :产看表的所有索引，show indexes from table_name，同样也可以从information_schemastatistics表中获得同样的信息。cardinality列很重要，表示数据量。

7、show tables status: 查看数据库表的底层大小以及表结构，同样可以从information_schematables表中获得底层表的信息。

8、show [global|session]status:可以查看mysql服务器当前内部状态信息。可以帮助却行mysql服务器的负载的各种指标。默认是session。同information_schemaglobal_status和information_schemasession_status

9、show [global|session] variables ：查看当前mysql系统变量的值，其中一些值能影响到sql语句的执行方式。同information_schemaglobal_variables和information_schemasession_variables;

10、information_schema:包含的表的数量和mysql的版本有关系。

修改mysql配置文件，优化缓存大小和连接数连接方式，优化sql语句 ,记得mysql好像是有工具可以查看最占用资源的sql语句，找到他，优化他。

安装好mysql后，配制文件应该在/usr/local/mysql/share/mysql目录中，配制文件有几个，有my-hugecnf my-mediumcnf my-largecnf my-smallcnf,不同的流量的网站和不同配制的服务器环境，当然需要有不同的配制文件了。

一般的情况下，my-mediumcnf这个配制文件就能满足我们的大多需要；一般我们会把配置文件拷贝到/etc/mycnf 只需要修改这个配置文件就可以了，使用mysqladmin variables extended-status _u root _p 可以看到目前的参数，有3个配置参数是最重要的，即key_buffer_size,query_cache_size,table_cache。

key_buffer_size只对MyISAM表起作用，

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。一般我们设为16M,实际上稍微大一点的站点　这个数字是远远不够的，通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。比例 key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。 或者如果你装了phpmyadmin 可以通过服务器运行状态看到,笔者推荐用phpmyadmin管理mysql，以下的状态值都是本人通过phpmyadmin获得的实例分析:

这个服务器已经运行了20天

key_buffer_size _ 128M

key_read_requests _ 650759289

key_reads - 79112

比例接近1:8000 健康状况非常好

Linux 进程通过 C 标准库中的内存分配函数 malloc 向系统申请内存，但是到真正与内核交互之间，其实还隔了一层，即内存分配管理器(memory allocator)。常见的内存分配器包括：ptmalloc(Glibc)、tcmalloc(Google)、jemalloc(FreeBSD)。MySQL 默认使用的是 glibc 的 ptmalloc 作为内存分配器。

内存分配器采用的是内存池的管理方式，处在用户程序层和内核层之间，它响应用户的分配请求，向操作系统申请内存，然后将其返回给用户程序。

为了保持高效的分配，分配器通常会预先向操作系统申请一块内存，当用户程序申请和释放内存的时候，分配器会将这些内存管理起来，并通过一些算法策略来判断是否将其返回给操作系统。这样做的最大好处就是可以避免用户程序频繁的调用系统来进行内存分配，使用户程序在内存使用上更加高效快捷。

关于 ptmalloc 的内存分配原理，个人也不是非常了解，这里就不班门弄斧了，有兴趣的同学可以去看下华庭的《glibc 内存管理 ptmalloc 源代码分析》。

关于如何选择这三种内存分配器，网上资料大多都是推荐摒弃 glibc 原生的 ptmalloc，而改用 jemalloc 或者 tcmalloc 作为默认分配器。因为 ptmalloc 的主要问题其实是内存浪费、内存碎片、以及加锁导致的性能问题，而 jemalloc 与 tcmalloc 对于内存碎片、多线程处理优化的更好。

目前 jemalloc 应用于 Firefox、FaceBook 等，并且是 MariaDB、Redis、Tengine 默认推荐的内存分配器，而 tcmalloc 则应用于 WebKit、Chrome 等。

一个数据库服务器高iowait的优化案例

1开发反馈某一测试环境sql运行缓慢，而在其他测试环境该sql运行很快。两个环境其配置相同，均只部署了mysql服务器。

2执行top命令发现sql运行缓慢的机器上磁盘iowait较sql运行较快的机器高出很多。推测这是导致sql运行缓慢的主因，因为该sql是要读取表，表较大，且要扫描的行数较多。

3到底是什么导致机器iowait高呢，执行iotop发现消耗io的进程主要是mysql，而且主要是mysql上的读操作。

4想必是有其他高频运行的查询语句不停从某大表中查询数据，且查询时可能使用不到索引，要扫描的表行数较多，从而导致高频io操作，致使其他需io操作的sql运行缓慢。

5究竟是什么sql引起的呢？开启了general log，发现收集到的语句太多，且不能很好定位到高开销的sql。

6开启slow log,long_query_time置为1，来捕获慢查询，同时使用pt-ioprofile用来追踪mysql数据文件中哪些文件上的io消耗比较多。

7综合slow log(可使用pt-query-digest进行聚合)和pt-ioprofile的结果发现确实是两条典型的需要扫描全表的且对应的表非常大的sql频繁执行导致了磁盘的高io。

8那么剩下的问题便是优化表或者查询了。最简单直接的是通过建合适的索引来提升查询性能，减少表扫描行数，需要继续榨取性能的话就是优化sql的写法，调整表结构，调整参数配置来解决了。

9先从收益最大的方法入手，先评估sql语句，根据语句中的条件查看各个字段的数据分布情况，通过explain等评估在字段上创建索引或多列联合索引的合理性，并创建合适的索引。

10最后发现建好索引后原来需要扫全表的语句通过索引可有效减少扫描行数，继而io操作减少了，服务器的iowait讲题，原来反馈的运行较慢的sql运行速度得以提升，但还是不够理想。

11最后通过在该慢语句对应的表建索引，并修正where条件中使用错误的值类型极大的提升了sql语句运行速度，且服务器整体IO消耗大大降低。

12可通过pt-query-digest聚合优化后mysql server产生的slow log以及使用pt-ioprofile分析优化后mysql数据文件io占用情况，可了解到优化前后的差异情况。

在mysql安装目录下，比如：D:ProgramFilesMySQLMySQLServer51

里面有几个配置文件，只要修改名字成为myini即可，比如：

my-hugeini巨型服务器

my-largeini大型

my-mediumini中型

my-smallini小型

备份原来的，并重命名，重新启动即可。其中，[mysqld]这一节是mysql服务器的配置信息。

Linux上MySQL优化提升性能，可以优化关闭NUMA特性如下：

这些其实都源于CPU最新的技术：节能模式。操作系统和CPU硬件配合，系统不繁忙的时候，为了节约电能和降低温度，它会将CPU降频。

为了保证MySQL能够充分利用CPU的资源，建议设置CPU为最大性能模式。这个设置可以在BIOS和操作系统中设置，当然，在BIOS中设置该选项更好，更彻底。

然后我们看看内存方面，我们有哪些可以优化的。

i)

我们先看看numa

非一致存储访问结构

(NUMA

：

Non-Uniform

Memory

Access)

也是最新的内存管理技术。它和对称多处理器结构

(SMP

：

Symmetric

Multi-Processor)

是对应的。

我们可以直观的看到：SMP访问内存的都是代价都是一样的;但是在NUMA架构下，本地内存的访问和非

本地内存的访问代价是不一样的。对应的根据这个特性，操作系统上，我们可以设置进程的内存分配方式。目前支持的方式包括：

--interleave=nodes

--membind=nodes

--cpunodebind=nodes

--physcpubind=cpus

--localalloc

--preferred=node

简而言之，就是说，你可以指定内存在本地分配，在某几个CPU节点分配或者轮询分配。除非

是设置为--interleave=nodes轮询分配方式，即内存可以在任意NUMA节点上分配这种方式以外。其他的方式就算其他NUMA节点上还有内

存剩余，Linux也不会把剩余的内存分配给这个进程，而是采用SWAP的方式来获得内存。

所以最简单的方法，还是关闭掉这个特性。

关闭特性的方法，分别有：可以从BIOS，操作系统，启动进程时临时关闭这个特性。

a)

由于各种BIOS类型的区别，如何关闭NUMA千差万别，我们这里就不具体展示怎么设置了。

b)

在操作系统中关闭，可以直接在/etc/grubconf的kernel行最后添加numa=off，如下所示：

kernel

/vmlinuz-2632-220el6x86_64

ro

root=/dev/mapper/VolGroup-root

rd_NO_LUKSUTF-8

rd_LVM_LV=VolGroup/root

rd_NO_MD

quiet

SYSFONT=latarcyrheb-sun16

rhgb

crashkernel=auto

rd_LVM_LV=VolGroup/swap

rhgb

crashkernel=auto

quiet

KEYBOARDTYPE=pc

KEYTABLE=us

rd_NO_DM

numa=off

另外可以设置

vmzone_reclaim_mode=0尽量回收内存。

c)

启动MySQL的时候，关闭NUMA特性：

numactl

--interleave=all

mysqld

当然，最好的方式是在BIOS中关闭。

ii)

我们再看看vmswappiness。

vmswappiness是操作系统控制物理内存交换出去的策略。它允许的值是一个百分比的值，最小为0，最大运行100，该值默认为60。vmswappiness设置为0表示尽量少swap，100表示尽量将inactive的内存页交换出去。

具体的说：当内存基本用满的时候，系统会根据这个参数来判断是把内存中很少用到的inactive

内存交换出去，还是释放数据的cache。

1当我们请求mysql服务器的时候,MySQL前端会有一个监听,请求到了之后,服务器得到相关的SQL语句,执行之前(虚线部分为执行),还会做权限的判断

2通过权限之后,SQL就到MySQL内部,他会在查询缓存中,看该SQL有没有执行过,如果有查询过,则把缓存结果返回,说明在MySQL内部,也有一个查询缓存但是这个查询缓存,默认是不开启的,这个查询缓存,和我们的Hibernate，Mybatis的查询缓存是一样的,因为查询缓存要求SQL和参数都要一样,所以这个命中率是非常低的（没什么卵用的意思）。

3如果我们没有开启查询缓存,或者缓存中没有找到对应的结果,那么就到了解析器,解析器主要对SQL语法进行解析

4解析结束后就变成一颗解析树,这个解析树其实在Hibernate里面也是有的,大家回忆一下,在以前做过Hibernate项目的时候,是不是有个一个antlrjar。这个就是专门做语法解析的工具因为在Hibernate里面有HQL，它就是通过这个工具转换成SQL的,我们编程语言之所以有很多规范、语法,其实就是为了便于这个解析器解析,这个学过编译原理的应该知道

5得到解析树之后,不能马上执行,这还需要对这棵树进行预处理,也就是说,这棵树,我没有经过任何优化的树,预处理器会这这棵树进行一些预处理,比如常量放在什么地方,如果有计算的东西,把计算的结果算出来等等

6预处理完毕之后,此时得到一棵比较规范的树,这棵树就是要拿去马上做执行的树,比起之前的那棵树,这棵得到了一些优化

7查询优化器，是MySQL里面最关键的东西,我们写任何一条SQL,比如SELECT FROM USER WHERE USERNAME = toby AND PASSWORD = 1,它会怎么去执行它是先执行username = toby还是password = 1每一条SQL的执行顺序查询优化器就是根据MySQL对数据统计表的一些信息,比如索引,比如表一共有多少数据,MySQL都是有缓存起来的,在真正执行SQL之前,他会根据自己的这些数据,进行一个综合的判定,判断这一次在多种执行方式里面,到底选哪一种执行方式,可能运行的最快这一步是MySQL性能中,最关键的核心点,也是我们的优化原则我们平时所讲的优化SQL,其实说白了,就是想让查询优化器,按照我们的想法,帮我们选择最优的执行方案,因为我们比MySQL更懂我们的数据MySQL看数据,仅仅只是自己收集到的信息,这些信息可能是不准确的,MySQL根据这些信息选了一个它自认为最优的方案,但是这个方案可能和我们想象的不一样

8这里的查询执行计划,也就是MySQL查询中的执行计划,比如要先执行username = toby还是password = 1

9这个执行计划会传给查询执行引擎,执行引擎选择存储引擎来执行这一份传过来的计划,到磁盘中的文件中去查询,这个时候重点来了,影响这个查询性能最根本的原因是什么就是硬盘的机械运动,也就是我们平时熟悉的IO,所以一条查询语句是快还是慢,就是根据这个时间的IO来确定的那怎么执行IO又是什么来确定的就是传过来的这一份执行计划(优化就是制定一个我们认为最快的执行方案,最节省IO,和执行最快)

10如果开了查询缓存,则返回结果给客户端,并且查询缓存也放一份。