目标

· 了解什么是优化

· 掌握优化查询的方法

· 掌握优化数据库结构的方法

· 掌握优化MySQL服务器的方法

什么是优化？

· 合理安排资源、调整系统参数使MySQL运行更快、更节省资源。

· 优化是多方面的，包括查询、更新、服务器等。

· 原则：减少系统瓶颈，减少资源占用，增加系统的反应速度。

数据库性能参数

· 使用SHOW STATUS语句查看MySQL数据库的性能参数

· SHOW STATUS LIKE 'value'

· 常用的参数：

· Slow_queries 慢查询次数

· Com_(CRUD) 操作的次数

· Uptime 上线时间

查询优化

EXPLAIN

在MySQL中可以使用EXPLAIN查看SQL执行计划，用法：EXPLAIN SELECT * FROM tb_item

id

SELECT识别符。这是SELECT查询序列号。这个不重要。

select_type

表示SELECT语句的类型。

有以下几种值：

1、 SIMPLE表示简单查询，其中不包含连接查询和子查询。

2、 PRIMARY表示主查询，或者是最外面的查询语句。

3、 UNION表示连接查询的第2个或后面的查询语句。

4、 DEPENDENT UNIONUNION中的第二个或后面的SELECT语句，取决于外面的查询。

5、 UNION RESULT连接查询的结果。

6、 SUBQUERY子查询中的第1个SELECT语句。

7、 DEPENDENT SUBQUERY子查询中的第1个SELECT语句，取决于外面的查询。

8、 DERIVEDSELECT(FROM 子句的子查询)。

table

表示查询的表。

type（重要）

表示表的连接类型。

以下的连接类型的顺序是从最佳类型到最差类型：

1、 system表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计。

2、 const数据表最多只有一个匹配行，因为只匹配一行数据，所以很快，常用于PRIMARY KEY或者UNIQUE索引的查询，可理解为const是最优化的。
3、 eq_refmysql手册是这样说的:"对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY"。eq_ref可以用于使用=比较带索引的列。

4、 ref查询条件索引既不是UNIQUE也不是PRIMARY KEY的情况。ref可用于=或<或>操作符的带索引的列。

5、 ref_or_null该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。

上面这五种情况都是很理想的索引使用情况。

6、 index_merge该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

7、 unique_subquery该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。

8、 index_subquery该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)

9、 range只检索给定范围的行,使用一个索引来选择行。

10、 index该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。

11、 ALL对于每个来自于先前的表的行组合,进行完整的表扫描。（性能最差）

possible_keys

指出MySQL能使用哪个索引在该表中找到行。

如果该列为NULL，说明没有使用索引，可以对该列创建索引来提高性能。

key

显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。

可以强制使用索引或者忽略索引：


key_len

显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。

注意：key_len是确定了MySQL将实际使用的索引长度。

ref

显示使用哪个列或常数与key一起从表中选择行。

rows

显示MySQL认为它执行查询时必须检查的行数。

Extra

该列包含MySQL解决查询的详细信息

· Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。

· Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。

· range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。

· Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。

· Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。

· Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。

· Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。

· Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。

· Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

使用索引查询需要注意

索引可以提供查询的速度，但并不是使用了带有索引的字段查询都会生效，有些情况下是不生效的，需要注意！

使用LIKE关键字的查询

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为"%"，索引不起作用。只有"%"不在第一个位置，索引才会生效。

使用联合索引的查询

MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于联合索引，只有查询条件中使用了这些字段中第一个字段时，索引才会生效。

使用OR关键字的查询

查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效。

子查询优化

MySQL从4.1版本开始支持子查询，使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。

子查询虽然很灵活，但是执行效率并不高。

执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响。

优化：

可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询快。

数据库结构优化

一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。

需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

将字段很多的表分解成多个表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。

通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

增加冗余字段

设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。

表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

注意：

冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

插入数据的优化

插入数据时，影响插入速度的主要是索引、唯一性校验、一次插入的数据条数等。

插入数据的优化，不同的存储引擎优化手段不一样，在MySQL中常用的存储引擎有，MyISAM和InnoDB，两者的区别：

http://www.cnblogs.com/panfeng412/archive/2011/08/16/2140364.html


MyISAM

禁用索引

对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入数据速度。

为了解决这个问题，可以在批量插入数据之前禁用索引，数据插入完成后再开启索引。

禁用索引的语句：

ALTER TABLE table_name DISABLE KEYS

开启索引语句：

ALTER TABLE table_name ENABLE KEYS

对于空表批量插入数据，则不需要进行操作，因为MyISAM引擎的表是在导入数据后才建立索引。

禁用唯一性检查

唯一性校验会降低插入记录的速度，可以在插入记录之前禁用唯一性检查，插入数据完成后再开启。

禁用唯一性检查的语句：SET UNIQUE_CHECKS = 0;

开启唯一性检查的语句：SET UNIQUE_CHECKS = 1;

批量插入数据

插入数据时，可以使用一条INSERT语句插入一条数据，也可以插入多条数据。


第二种方式的插入速度比第一种方式快。

使用LOAD DATA INFILE

当需要批量导入数据时，使用LOAD DATA INFILE语句比INSERT语句插入速度快很多。

InnoDB

禁用唯一性检查

用法和MyISAM一样。

禁用外键检查

插入数据之前执行禁止对外键的检查，数据插入完成后再恢复，可以提供插入速度。

禁用：SET foreign_key_checks = 0;

开启：SET foreign_key_checks = 1;

禁止自动提交

插入数据之前执行禁止事务的自动提交，数据插入完成后再恢复，可以提高插入速度。

禁用：SET autocommit = 0;

开启：SET autocommit = 1;

服务器优化

优化服务器硬件

服务器的硬件性能直接决定着MySQL数据库的性能，硬件的性能瓶颈，直接决定MySQL数据库的运行速度和效率。

需要从以下几个方面考虑：

1、 配置较大的内存。足够大的内存，是提高MySQL数据库性能的方法之一。内存的IO比硬盘快的多，可以增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘的IO。

2、 配置高速磁盘，比如SSD。

3、 合理分配磁盘IO，把磁盘IO分散到多个设备上，以减少资源的竞争，提高并行操作能力。

4、 配置多核处理器，MySQL是多线程的数据库，多处理器可以提高同时执行多个线程的能力。

优化MySQL的参数

通过优化MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。

MySQL的配置参数都在my.conf或者my.ini文件的[mysqld]组中，常用的参数如下：






关于系统优化,假设这么个场景,用户反映系统太卡（其实就是高并发）,那么我们怎么优化?

  • 如果请求过多,判定web服务器的压力过大,增加前端的web服务器,做负载均衡

  • 如果请求静态界面不卡了,但是动态数据还是卡,说明MySQL处理的请求太多了,在应用层增加缓存.

  • 数据库层其实是最脆弱的一层，一般在应用设计时在上游就需要把请求拦截掉，数据库层只承担“能力范围内”的访问请求，所以，我们通过在服务层引入队列和缓存，让最底层的数据库高枕无忧。但是如果请求激增,还是有大量的查询压力到MySQL,这个时候就要想办法解决MySQL的瓶颈了

总结起来就是,系统优化的第一步,是绝对轮不到MySQL优化我们之所以要做MySQL的集群,一般都是在做好了应用级别的缓存,请求还是太多的情况下考虑的问题。

MySQL的执行流程

  1. 当请求一个mysql服务器的时候，mysql前端会有一个监听，请求到了以后服务器相关的SQL语句执行之前还会做权限的判断
  2. 通过权限之后，SQL就到MySQL内部，会查询缓冲，看该SQL有没有执行，如果有有查询过，则返回缓存结果。查询缓存默认是不开启的，和Hibernate，Mybatis的查询缓存是一样的。因为查询缓存要求SQL和参数都一样的，所以这个命中率是非常低的，基本没什么用。
  3. 如果没有开启缓存查询，或者缓存中没有找到对应的结果，那么就到了解析器，解析器主要对SQL语法进行解析
  4. 解析结束后变成一颗解析树，在Hibernate里面也有。Hibernate项目里antlr.jar。专门做语法解析的工具，因为在Hibernate里面有HQL，它就是通过这个工具转换成SQL的。
  5. 得到解析树后，还需要对树进行预处理。也就是预处理器对该树进行优化，比如常量放在什么地方，如果有计算的东西，把计算结果算出来等等
  6. 预处理完毕后，此时得到一颗比较规范的树。
  7. 查询优化器。每一条SQL执行顺序查询优化器就是根据MySQL对数据统计表的一些信息，比如索引，缓存，在执行SQL之前，它会根据这些数据进行一个综合判断，判断哪种运行的最快。
我们的优化SQL原则：想让查询优化器,按照我们的想法,帮我们选择最优的执行方案,因为我们比MySQL更懂我们的数据.MySQL看数据,仅仅只是自己收集到的信息,这些信息可能是不准确的,MySQL根据这些信息选了一个它自认为最优的方案,但是这个方案可能和我们想象的不一样.


表锁：

通常情况下，当访问某张表的时候，读取者首先必须获取该表的锁，如果有写入操作到达，那么写入者一直等待读取者完成操作（查询开始之后就不能中断，因此允许读取者完成操作）。当读取者完成对表的操作的时候，锁就会被解除。如果写入者正在等待的时候，另一个读取操作到达了，该读取操作也会被阻塞（block），因为默认的调度策略是写入者优先于读取者。当第一个读取者完成操作并解放锁后，写入者开始操作，并且直到该写入者完成操作，第二个读取者才开始操作。因此：要提高MySQL的更新/插入效率，应首先考虑降低锁的竞争，减少写操作的等待时间。

操作数据语句优化

一、INSERT

INSERT [INTO] 表名 [(字段列表)] VALUES (值列表)[, (值列表), …]
如果要插入的值列表包含所有字段并且顺序一致，则可以省略字段列表。可同时插入多条数据记录！

优化前：INSERT INTO SCORE (change_type,score,user_id) VALUES ('食饭'，10，1)；
优化策略：当插入大量的数据，比如100000条数据，需要10000条insert语句，每一条语句都需要提交到关系引擎那里去解析，优化，然后才能到达存储引擎做真正的插入工作，一般同时10条为最高效的。
优化后：INSERT INTO score (change_type,score,user_id) VALUES ('食饭'，10，1),('饮茶'，10，1)

此外：还有以下优化方式

  • 将线程/进程数控制在2倍于CPU数目相对合适
  • 采用顺序逐渐策略（自增主键、修改业务逻辑，让插入的记录尽可能顺序主键）
  • 考虑使用replace语句代替insert

二、DELETE

DELETE FROM 表名[ 删除条件子句]（没有条件子句，则会删除全部）

Mysql中的truncate table和delete语句都可以删除表里面所有数据，但是在一些情况下有些不同！
  1. truncate table删除速度更快，但删除之后不记录mysql日志，不可以恢复数据。
  2. 如果没有外键关联。innodb执行truncate是先drop table(原始表),再创建一个跟原始表一样空表,速度要远远快于delete逐条删除行记录。
  3. 表有外键关联，truncate table删除表数据为逐行删除，如果外键指定级联删除(delete cascade)，关联的子表也会会被删除所有表数据。如果外键未指定级联(cascde),truncate table逐行删除数据，如果是父行关联子表行数据，将会报错。
  4. 如果使用innodb_file_per_table参数，truncate table 能重新利用释放的硬盘空间,在InnoDB Plugin中，truncate table为自动回收，如果不是用InnoDB Plugin,那么需要使用optimize table来优化表，释放空间。
truncate table删除表后，optimize table尤其重要，特别是大数据数据库，表空间可以得到释放！

一个大的 DELETE 或 INSERT 操作，要非常小心，因为这两个操作是会锁表的，表一锁住，其他操作就进不来了。因此，我们要交给DBA去拆分，重整数据库策略，比如限制处理1000条。

另外，扩展下删除和索引的联系（关于索引优化，后面的查询优化也会讲解），由于索引需要额外的维护成本；因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟），然后删除其中无用数据，此过程需要不到两分钟，删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。


三、UPDATE

UPDATE 表名 SET 字段名=新值[, 字段名=新值] [更新条件]
优化前：UPDATE score SET change_type='洗澡' WHERE id=2;
优化后：

  1. 尽量不要修改主键字段
  2. 当修改VARCHAR型字段时，尽量使用相同字段内容的值替代
  3. 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。
  4. 避免UPDAATE将要复制到其他数据库的列
  5. 避免UPDATE建有很多索引的列
  6. 避免UPDATE在WHERE子句条件中的列

四、REPLACE

根据应用情况可以使用replace 语句代替insert/update语句。例如：如果一个表在一个字段上建立了唯一索引，当向这个表中使用已经存在的键值插入一条记录，将会抛出一个主键冲突的错误。如果我们想用新记录的值来覆盖原来的记录值时，就可以使用REPLACE语句。
使用REPLACE插入记录时，如果记录不重复（或往表里插新记录），REPLACE功能与INSERT一样，如果存在重复记录，REPLACE就使用新记录的值来替换原来的记录值。使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一，形成一个原子操作。这样就可以不必考虑同时使用DELETE和INSERT时添加事务等复杂操作了。
在使用REPLACE时，表中必须有唯一有一个PRIMARY KEY或UNIQUE索引，否则，使用一个REPLACE语句没有意义。

用法：

  1. 同INSERT REPLACE INTO score (change_type,score,user_id) VALUES ('吃饭',10,1),('喝茶',10,1),('喝茶',10,1);
  2. replace(object, search, replace)，把object中出现search的全部替换为replace。并不是修改数据，而只是单纯做局部替换数据返还而已。SELECT REPLACE('喝茶','茶','喝')

    修改表数据啦，对应下面就是，根据change_type字段找到做任务的数据，用bb来替换

    UPDATE score SET change_type=REPLACE(change_type,'做任务','bb')

UPDATE和REPLACE的区别：

1）UPDATE在没有匹配记录时什么都不做，而REPLACE在有重复记录时更新，在没有重复记录时插入。

2）UPDATE可以选择性地更新记录的一部分字段。而REPLACE在发现有重复记录时就将这条记录彻底删除，再插入新的记录。也就是说，将所有的字段都更新了。

多表查询




超大型数据尽可能尽力不要写子查询，使用连接（JOIN）去替换它

1）因为在大型的数据处理中，子查询是非常常见的，特别是在查询出来的数据需要进一步处理的情况，无论是可读性还是效率上，这时候的子查都是更优。

2）然而在一些特定的场景，可以直接从数据库读取就可以的，比如一个表（A表 a，b，c字段，需要内部数据交集）join自己的效率必然比放一个子查在where中快得多。

使用联合(UNION)来代替手动创建的临时表

union查询：它可以把需要使用临时表的两条或更多的select查询合并的一个查询中（即把两次或多次查询结果合并起来。）。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们只需要用UNION作为关键字把多个select语句连接起来就可以了，要注意的是所有select语句中的字段数目要想同。

要求：两次查询的列数必须一致（列的类型可以不一样，但推荐查询的每一列，相对应的类型要一样）

可以来自多张表的数据：多次sql语句取出的列名可以不一致，此时以第一个sql语句的列名为准。

如果不同的语句中取出的行，有完全相同(这里表示的是每个列的值都相同)，那么union会将相同的行合并，最终只保留一行。也可以这样理解，union会去掉重复的行。

如果不想去掉重复的行，可以使用union all。

如果子句中有order by,limit，需用括号()包起来。推荐放到所有子句之后，即对最终合并的结果来排序或筛选。


总结

（1）对于要求全面的结果时，我们需要使用连接操作（LEFT JOIN / RIGHT JOIN / FULL JOIN）；

（2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：


备注、描述、评论之类的可以设置为 NULL，其他最好不要使用NULL。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num = 0

（3）in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)
对于连续的数值，能用between就不要用in
select id form t where num between 1 and 3
很多时候用exists代替in是一个好的选择
select num form a where num in(select num form b)
用这个替换：select num from a where exists(select 1 from b where num =a.num)

（4）尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

（5）尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

使用MySQL的一些连接操作对查询没有多么大的改善，其实核心是索引