MYSQL性能优化总结

简介：mysql性能优化可以从各个方面进行，每个地方提升一点整体性能就会提升很多。

优化有以下几个方面：

一：数据库,表设计

数据库：

1.数据库命名：一般和项目名同名，不是必须要求，但应该是程序员应有的素质。

2.数据库编码：要选择utf8mb4而不使用utf8。因为真正的utf8编码是4字节的mysql的utf8存在历史遗留问题是3字节的，但他有个替代品utf8mb4 这个是4字节的，也是真正的我们需要的utf8编码。

表设计：

表的命名：一般我们表名有会有前缀，项目名_表名，或者 业务名_表名。。。这个每个公司要求不同，但应该都会有前缀。

大小写：mysql 在 Windows 下是不区分大小写的，但 Linux 默认是区分大小写的。因此，数据库名， 表名，字段名，最好不要乱用大小写。

表的存储引擎：存储引擎常见的有InnoDB，MyISAM。。。不同的存储引擎拥有不同的特性，合理的利用每种存储引擎的特点去存储数据。MyISAM不支持事务，是表级锁，但是查询速度快，InnoDB支持事务，行锁。我们常用应该就是InnoDB引擎。

字段：

1.字段类型的选择：不同类型的字段的索引方式和存储不同，从而性能也会不同。选择正确的字段类型很重要。比如，年龄字段我们可以选择一个字节的tinyint,范围是0-255，足够用了，而不要选择4字节的int，或者integer。

　　正常情况下能用tinyint不用int，能用int不用char，能用char不用varchar。不过不同的存储引擎对char和varchar的使用原则不同。innodb：建议使用varchar，大部分表都是使用innodb，varchar的使用频率会更高。

　　金额类型的如果有小数一定不要用float，double，要使用定点数decimal！

　　日期类型的选择：有date，year，time，timestamp，datetime。常用的有timestamp，datetime。timestamp是4字节的 如果使用时区就用它，datetime是8字节的，时间范围更大。

　　不推荐使用字符串存储日期，一般来说日期类型比字符串类型占用的空间小，日期时间类型在进行查找过滤是可以利用日期进行对比，这比字符串对比高效，日期时间类型有丰富的处理函数，可以方便的对日期类型进行日期的计算。

　　存储时间戳这个问题用int 还是bigint还是varchar2？我觉得bigint合适点因为他可以和java的long直接对接。

2.字段长度的选择：数据最终要写到磁盘上,字段的长度会影响磁盘的I/O操作, 所以合理的字段长度也能提升数据库的性能。比如，性别字段长度给个1就可以，没必要给个255。

3.主键的选择：几乎所有的表都应该有主键。主键的生成方式很多，数据库可以自增，也可以用框架生成。多数情况下主键都是没有什么特殊意义的，和业务没什么关系的。

4.常用字段命名：涉及到逻辑删除，禁用之类字段的时候，我们命名一般都是 is_delete,is_able这种，做到见名知意。

5.数据的完整性：数据的完整性强烈建议通过后台程序来保证，不要用外键约束。

6.字段的数量和内容：无用的字段最好删除，字段的数量不易多过。字段的内容不要过多，当一个字段需要用text这种类型的时候。可以考虑单独存到另一张表。

二：SQL优化

sql优化是mysql性能优化中很重要的一个方法。大多数我们首先想到的就是优化sql。毕竟不是每个程序员sql都写的非常溜。

 1.使用索引一般比不适用索引查询快很多。explain命令是查看该sql是否使用索引的。是一个很重要的命令。

explain命令查询出的列表字段解释：

　　id: SELECT识别符。这是SELECT的查询序列号。
　　select_type: 查询类型 。有以下类型：
　　　　simple: 简单表即不适用表连接或者子查询。
　　　　primary: 主查询，即外层的查询。
　　　　subquery: 子查询内层第一个SELECT，结果不依赖于外部查询。
　　　　dependent subquery: 子查询内层第一个。
　　　　select: 依赖于外部查询。
　　　　union: UNION语句中第二个SELECT开始后面所有SELECT。
　　　　union result union 中合并结果。
　　　　DERIVED
　　table：查询的表名。
　　type：扫描的方式，有以下方式：
　　　　all : 全表扫描。
　　　　index: 扫描所有索引。
　　　　range: 索引范围扫描,常见于< <=、>、>=、between。
　　　　const: 表最多有一个匹配行, 常见于根据主键或唯一索引进行查询。
　　　　system: 表仅有一行(=系统表)。这是const联接类型的一个特例。
　　　　ref
　　possible_keys： 该查询可以利用的索引，可能同一个查询有多个索引可以使用，如果没有任何索引显示null。
　　key：实际使用到的索引，从Possible_key中所选择使用索引，当有多个索引时，mysql会挑出一个最优的索引来使用。
　　key_len： 被选中使用索引的索引长度。
　　ref：
　　　　多表连接时的外键字段
　　　　const
　　rows: 估算出结果集行数，该sql语句扫描了多少行，可能得到的结果,MySQL认为它执行查询时必须检查的行数。
　　Extra： 额外重要的信息
　　　　no tables: Query语句中使用FROM DUAL 或不含任何FROM子句。
　　　　using filesort : 使用文件排序，最好能避免这种情况。
　　　　Using temporary: 某些操作必须使用临时表，常见 GROUP BY ; ORDER BY。
　　　　Using where: 不用读取表中所有信息，仅通过索引就可以获取所需数据。
　　　　Using join buffer (Block Nested Loop)
　　　　Using index condition
　　　　Using sort_union(索引名)

 2.什么时候走索引和不走索引？

　　1.in操作是走索引的，但能少用in就少用。要考虑in里面的数量尽量不要太大。

　　2.范围查找是走索引的，比如age字段上有索引，则age>10会走索引。

　　3.模糊查询只有做前缀走索引。比如name字段建立的索引， name like '明%' 会走索引，'%明%' 和 '%明' 是不会走索引的。

　　4.反向条件不走索引,如； !=， <>， NOT IN ，IS NOT NULL 是不走索引的。

　　5.对条件计算也不会走索引，比如 length(name)>=5，create_time>now() 是不走索引的。

　　6.查询条件的数据类型不对也不走索引，比如 age 是tinyint类型的，查询条件加了引号；age>'10' 就不会走索引。

　　7.or 只有两边都有索引才会走索引，否则是不走索引的。

　　8.用union 少用or，union 两边的sql语句有索引的走索引，没索引的全表扫描。  

　　9.union all 不去重复，union去重并且使用了临时表。能不使用临时表就不用。

　　10.复合索引遵循最左原则，所以如果直接使用右边的字段是不走索引的。

　　11.mysql排序方式有两种；一种是通过索引顺序扫描直接返回有序数据。另一种是通过对返回数据进行排序即order by。order by排序方向要索引的排序方向一致！

　　12.覆盖索引是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。 当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。

　　13.limit分页优化很重要也是常见的需要优化的地方。思路一：在索引上完成排序分页的操作，然后根据主键关联回原表，再查询原来所需要的其他列。这种思路是使用覆盖索引尽快定位出需要的记录的id，覆盖索引效率高些。思路二：limit m,n 转换为 n。

　　14.最好不要使用select *，需要什么字段就去取什么字段。 

三：分表

如果一个表的每条记录的内容很大，那么就需要更多的IO操作，如果字段值比较大，而使用频率相对比较低，可以将大字段移到另一张表中，当查询不查大字段的时候，这样就减少了I/O操作。
如果一个表的数据量很少，那么查询就很快；如果表的数据量非常非常大，那么查询就变的比较慢；也就是表的数据量影响这查询的性能。
表中的数据本来就有独立性，例如分别记录各个地区的数据或者不同时期的数据，特别是有些数据常用，而另外一些数据不常用。
大表对DDL操作有一定的影响，如创建索引，添加字段 。修改表结构需要长时间锁表，会造成长时间的主从延迟，影响正常的数据操作。

当产品运营很多年时数据量就会变的很大，如QQ的用户表，不知道QQ有多少用户，而且一个人可能有多个QQ号，如订单表，比如淘宝的订单数据有多少，都是惊人的数据量。单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。
分表方式：垂直分割和水平分割。

垂直分割：一般用于拆分大字段和访问频率低的字段，分离冷热数据。

垂直拆分的优点： 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。 
垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂。

水平拆分：是指数据表行的拆分，表的行数超过500万行或者单表容量超过10GB时，查询就会变慢，这时可以把一张的表的数据拆成多张表来存放。水平分表尽可能使每张表的数据量相当，比较均匀。

水平拆分可以支持非常大的数据量。需要注意的一点是:分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水品拆分最好分库。

水平拆分能够支持非常大的数据量存储，应用端改造也少，但分片事务难以解决，跨界点Join性能较差，逻辑复杂。

水平拆分会给应用增加复杂度，它通常在查询是需要多个表名，查询所有数据需要union操作。在许多数据库应用中，这种复杂性会超过它带来的优点，因为只要索引关键字不大，则在索引用于查询时，表中增加2-3倍数据量，查询时也就增加读一个索引层的磁盘次数，所以水平拆分要考虑数据量的增长速度，根据实际情况决定是否需要对表进行水平拆分。

水平分割标准很重要；例如：

用户表可以根据用户的手机号段进行分割如user183、user150、user153等，每个号段就是一张表。
用户表也可以根据用户的id进行分割，加入分3张表user0,user1,user2，如果用户的id%3=0就查询user0表，如果用户的id%3=1就查询user1表。
对于订单表可以按照订单的时间进行分表。

数据库分片方案：

　　1.客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。

　　2.中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat、360的Atlas、网易的DDB等等都是这种架构的实现。

四：主从复制,读写分离

如果数据库压力很大，一台服务器支撑不了，那么可以使用mysql的主从复制实现多台服务器同步，将数据库的压力分散到多台数据库服务器上。将增删改交给master执行，查询交给slaver查询。

一个主服务器承担更新操作，多台服务器承担查询操作，主从之间通过复制实现数据同步。多台服务器一方面用来保证可用性，另一方面可以创建不同的索引来满足不同的查询。

五：增加缓存

 可以采用缓存的方式来为数据库减压。变相提高数据库性能。

六：大事务处理

对于比较大的事物的处理，尽可能的把select这种操作放到事物之外执行。

七：数据库参数配置

mysql是一个高度定制化的数据库系统，提供了很多配置参数，一般都需要根据应用程序的特性和硬件情况对mysql做配置优化，windows配置文件为my.ini，linux为my.cnf。

常用优化配置变量：

query_cache_type : 如果全部使用innodb存储引擎，建议为0，如果使用MyISAM 存储引擎，建议为2，同时在SQL语句中显式控制是否是哟你gquery cache。

query_cache_size: 根据 命中率（Qcache_hits/(Qcache_hits+Qcache_inserts)*100)）进行调整，一般不建议太大，256MB可能已经差不多了，大型的配置型静态数据可适当调大。

binlog_cache_size: 一般环境2MB～4MB是一个合适的选择，事务较大且写入频繁的数据库环境可以适当调大，但不建议超过32MB。

key_buffer_size: key_buffer_size 参数用来设置用于缓存 MyISAM存储引擎中索引文件的内存区域大小。如果我们有足够的内存，这个缓存区域最好是能够存放下我们所有的 MyISAM 引擎表的所有索引，以尽可能提高性能。此外，当我们在使用MyISAM 存储的时候有一个及其重要的点需要注意，由于 MyISAM 引擎的特性限制了他仅仅只会缓存索引块到内存中，而不会缓存表数据库块。所以，我们的 SQL 一定要尽可能让过滤条件都在索引中，以便让缓存帮助我们提高查询效率。如果不使用MyISAM存储引擎，16MB足以，用来缓存一些系统表信息等。如果使用 MyISAM存储引擎，在内存允许的情况下，尽可能将所有索引放入内存，简单来说就是“越大越好”。

bulk_insert_buffer_size: 如果经常性的需要使用批量插入的特殊语句（上面有说明）来插入数据，可以适当调大该参数至16MB～32MB，不建议继续增大，某人8MB。

innodb_buffer_pool_size: 用来设置用于缓存 InnoDB 索引及数据块的内存区域大小，类似于 MyISAM 存储引擎的 key_buffer_size 参数。如果不使用InnoDB存储引擎，可以不用调整这个参数，如果需要使用，在内存允许的情况下，尽可能将所有的InnoDB数据文件存放如内存中，同样将但来说也是“越大越好” 。
innodb_additional_mem_pool_size: 一般的数据库建议调整到8MB～16MB，如果表特别多，可以调整到32MB，可以根据error log中的信息判断是否需要增大。

innodb_log_buffer_size: InnoDB 存储引擎的事务日志所使用的缓冲区。默认是1MB，系的如频繁的系统可适当增大至4MB～8MB。当然如上面介绍所说，这个参数实际上还和另外的flush参数相关。一般来说不建议超过32MB。

innodb_max_dirty_pages_pct: 根据以往的经验，重启恢复的数据如果要超过1GB的话，启动速度会比较慢，几乎难以接受，所以建议不大于 1GB/innodb_buffer_pool_size(GB)*100 这个值。当然，如果你能够忍受启动时间比较长，而且希望尽量减少内存至磁盘的flush，可以将这个值调整到90，但不建议超过90。

开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。这里的查询条件包括查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息。因此任何两个查询在任何字符上的不同都会导致缓存不命中。此外，如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、Mysql库中的系统表，其查询结果也不会被缓存。缓存建立之后，Mysql的查询缓存系统会跟踪查询中涉及的每张表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。 因此，开启缓存查询要谨慎，尤其对于写密集的应用来说更是如此。如果开启，要注意合理控制缓存空间大小，一般来说其大小设置为几十MB比较合适。此外，还可以通过sql_cache和sql_no_cache来控制某个查询语句是否需要缓存：SELECT SQL_NO_CACHE * FROM t_user WHERE name = ‘MsencSir’;

八：应用程序优化

由于数据库服务器本身的性能局限，就必须对前台应用进行一些优化，减少数据库的压力。

　　1，使用数据库连接池。

　　2，减少对mysql的访问； 

　　　　1.能够一次连接就能提取所有结果的，就不用两次连接。例如批量插入、批量更新。
　　　　2.多表连接的性能很低，特别是连接的表比较多的时候，可以通过分为多个sql查询，然后使用应用程序组装成想要的数据格式，这是解决多表连接查询性能低的一个很重要的手段。
　　　　3.数据库缓存，例如mybatis默认开启缓存的。
　　　　4.增加缓存层，如redis，memcache。