MySql优化参考

MySql

MySQL 是一个开源的关系型数据库管理系统，是最流行的关系型数据库管理系统之一，被广泛应用于互联网公司及中小企业。优化好mySql可以为企业减少很多成本。

首先对于任何新开发的系统都应当对做集成基准测试(性能测试)，以确定每一模块的性能瓶颈，并根据测试情况进行优化或制定应急策略。

集成测试工具：

ab 可以测试HTTP服务器每秒最多可以处理多少请求。

http_load 可以通过一个输入文件提供多个URL，http_load在这些URL中随机选择进行测试。

JMeter 可以加载其他应用并测试性能。

如果确定是由于数据库使用不当而导致的性能问题，可以通过以下方式排查：

一、表设计

1、使你的数据尽可能小

在优化中最基本的优化之一是使表在磁盘上占据的空间尽可能小。因为磁盘写入较快，并且在查询执行过程中小表的内容被处理时占用较少的主存储器。如果在更小的列上做索引，索引也将占据较少的资源。可以使用下面的方法使表的性能更好并且使存储空间最小：

(1) 尽可能地使用最有效(最小)的数据类型。MySQL有很多节省磁盘空间和内存的专业化类型。

(2) 尽可能使用较小的整数类型使表更小。例如，MEDIUMINT经常比INT好一些，因为MEDIUMINT列使用的空间要少25%。

(3) 如果可能，声明列为NOT  NULL。它使任何事情更快而且每列可以节省一位。注意如果在应用程序中确实需要NULL，应该毫无疑问使用它，只是避免 默认地在所有列上有它。

(4) 每张表的主索引应该尽可能短。这使一行的识别容易而有效。

(5) 只创建你确实需要的索引。索引对检索有好处，但是当你需要快速存储东西时就变得糟糕。如果主要通过搜索列的组合来存取一个表，对它们做一个索引。第一个索引部分应该是最常用的列。如果从表中选择时总是使用许多列，应该首先以更多的副本使用列以获得更好的索引压缩。

(6) 如果很可能一个索引在头几个字符上有唯一的前缀，仅仅索引该前缀比较好。MySQL支持对一个字符列的最左边部分创建一个索引。更短的索引会更快，不仅因为它们占较少的磁盘空间，而且因为它们将在索引缓存中提供更多的访问，因此磁盘搜索更少。

如果单表数据量控制在万以内，几乎不会有性能问题当然在实际业务场景中，单表控制在万以内是不现实的，特别在电商业务场景，业务数据何止上百亿，除了基础参数表，很少有表数据是在万以内。所以在设计中，mySql的单表容量是控制在百万左右，百万以内的单表加上合适的索引和sql语句优化就可以很快的查询出想要的结果，这里则可以通过水平拆分的方法将数据拆到多张表中从而控制单表的数据量不至于太多。对于数据量大的表应当进行适当的分区。

2、访问频率高的表保证字段数尽可能的少

业务表字段一般都很多，曾经看到过一张表有一百多个字段，这种设计太糟糕了。这里应该把表根据数据使用频率垂直拆分，将不需要频繁使用的字段拆分到其他附属表中，当需要使用附属字段时再检索出来，这样可以减少大多数扫描场景扫描出的数据变小，从而提高查询效率。

3、主要按expr1，expr2，... 顺序检索行的表，使用ALTER  TABLE ... ORDER BY expr1, expr2, ...。对表大量更改后使用该选项，可以获得更好的性能。

4、对于经常访问的不重要数据(如为没有在Web 浏览器中启用cookie的用户最后显示的标语的相关信息)使用内存表。在许多Web应用程序环境中也可以使用用户会话来处理可变状态数据。

5、在不同表中具有相同信息的列应该被声明为相同类型的并有相同的名字。尝试使名字简单化。例如，在customer表中使用name而不是customer_name。为了使名字能移植到其它SQL服务器，应该使名字短于18个字符。

二、索引设计

1、使用 EXPLAIN获取SELECT查询情况及索引使用情况

EXPLAIN语句可以用作DESCRIBE的一个同义词，或获得关于MySQL如何执行SELECT语句的信息，语法：

EXPLAIN  tbl_name

或：

EXPLAIN  [EXTENDED] SELECT select_options

在SELECT语句前放上关键词EXPLAIN，MySQL将解释它如何处理SELECT，并提供有关表如何联接和联接的次序。借助于EXPLAIN，可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。还可以知道优化器是否以一个最佳次序联接表。

2、创建适当的索引

在查询中，索引的存在可以让查询器快速定位到需要的数据，不使用索引，MySQL必须从第1条记录开始读完整个表直到找出相关的行。但这并不意味着索引越多越好，因为索引也是占空间的，而且索引太多会影响INSERT和UPDATE语句效率，因为每次INSERT和UPDATE修需要新增或更新索引数据。如果确实需要多个列索引可以使用组合索引，并且组合索引在组合查询场景效率更高。

比如你执行下面的SELECT语句：

mysql>SELECT * FROM  tbl_name WHERE col1=val1 AND col2=val2;

如果col1和col2上存在一个多列索引，可以直接取出相应行。如果col1和col2上存在单列索引，优化器将通过决定哪个索引将找到更少的行来找出更具限制性的索引并且使用该列索引取行。

如果表有一个多列索引，优化器可以使用最左面的索引前缀来找出行。例如，如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)和(col1,col2,col3)上的搜索进行了索引。

有时即使有可用索引，MySQL也不会使用索引。当优化器发现使用索引将需要MySQL访问表中的大部分行时。(在这种情况下，表扫描可能会更快些，因为需要的搜索要少）。然而，如果此类查询使用LIMIT只搜索部分行，MySQL就会使用索引，因为它可以更快地找到几行并在结果中返回。

3、索引创建注意点

(1) 不要在有NULL值的列创建索引

(2) 相同值很多的列不适合创建单列BTree索引

(3) 当SELECT使用到组合索引时，尽可能保证字段顺序与组合索引顺序一致，当然优化器会帮你检查

(4) 对于列索引不要使用左'%'like

(5) ORDER BY 的列最好建索引

(6) 所有GROUP  BY列引用最好建组合索引，这样可以避免group by时创建临时表。

三、优化查询sql

对sql优化其实就是让查询sql尽可能的走合适索引，避免全表扫描。

以下情况会造成全表扫描：

1、查询数据量很小的表

2、一次查询出了表中大部分数据

3、未在查询条件中创建索引

4、对索引列使用了like'%val1'

5、对索引列使用了函数或者。

6、使用or 作为查询条件。

7、使用 NULL作为查询条件。

8、使用in或not in 时

9、使用！=或时

查询很小的表全表扫面是没有问题的，对于需要查询大量数据的场景可以使用分页查询拼接结果。其他情况可以尽量避免，例如or可以用union all代替 ，对于NULL条件可以在建表时给所有字段都加上默认值从而避免有NULL值的字段。使用in或not in的地方可以改成exsits 或not exists。使用！=或的地方可以使用 =,between等替代。

SQL 语句应该尽可能简单：一条 SQL只能在一个 CPU 运算，大语句应当拆成小语句，减少锁表时间，一条大 SQL可能会堵死整个库。

SQL语句中不要使用SELECT * ，应当查询出需要的字段。

四、系统参数调优

常用调优参数：

back_log：back_log 值可以指出在 MySQL 暂时停止回复新请求之前的短时间内多少个请求可以被存在堆栈中。

wait_timeout：数据库连接闲置时间，超过设置的时间闲置连接会被回收。默认的 8 小时。

max_user_connection：最大连接数，默认为 0 无上限。

thread_concurrency：并发线程数，默认为 CPU 核数。

skip_name_resolve：是否禁止对外部连接进行 DNS 解析。消除 DNS 解析时间，但需要所有远程主机用 IP 访问。

key_buffer_size：索引块的缓存大小，增加会提升索引处理速度，对 MyISAM 表性能影响最大。

innodb_buffer_pool_size：缓存数据块和索引块大小，对 InnoDB 表性能影响最大。

通过查询 show status like 'Innodb_buffer_pool_read%'，保证 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests 越高越好。

innodb_additional_mem_pool_size：InnoDB 存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小。

当数据库对象非常多的时候，适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率，当过小的时候，MySQL 会记录 Warning 信息到数据库的错误日志中，这时就需要调整这个参数大小。

innodb_log_buffer_size：InnoDB 存储引擎的事务日志所使用的缓冲区，一般来说不建议超过 32MB。

query_cache_size：查询缓存大小，缓存 MySQL 中的 ResultSet，也就是一条 SQL 语句执行的结果集，所以仅仅只能针对 Select 语句。

当某个表的数据有任何变化，都会导致所有引用了该表的 Select 语句在 Query Cache 中的缓存数据失效。

根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整，一般不建议太大，256MB 可能已经差不多了，大型的配置型静态数据可适当调大。可以通过命令 show status like 'Qcache_%' 查看目前系统 Query Cache 使用大小。

read_buffer_size：MySQL 读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL 会为它分配一段内存缓冲区。

如果对表的顺序扫描请求非常频繁，可以通过增加该变量值以及内存缓冲区大小提高其性能。

sort_buffer_size：排序使用的缓冲大小。如果想要增加 ORDER BY 的速度，首先看是否可以让 MySQL 使用索引而不是额外的排序阶段。如果不能，可以尝试增加 sort_buffer_size 变量的大小。

read_rnd_buffer_size：随机读缓冲大小。当按任意顺序读取行时(例如按照排序顺序)，将分配一个随机读缓存区。

进行排序查询时，MySQL 会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。

record_buffer：表记录缓冲区。每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。

thread_cache_size：线程缓存大小。保存当前没有与连接关联但是准备为后面新的连接服务的线程，可以快速响应连接的线程请求而无需创建新的。