mysql学习笔记-优化篇

sql优化

优化sql语句的一般过程

通过show status命令了解sql的执行频率

show [global/session]status like Com_%;
/*
Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。
 Com_select：执行 select 操作的次数，一次查询只累加 1。
 Com_insert： 执行 INSERT 操作的次数， 对于批量插入的INSERT 操作， 只累加一次。
 Com_update：执行 UPDATE 操作的次数。
 Com_delete：执行DELETE 操作的次数。
上面这些参数对于所有存储引擎的表操作都会进行累计。下面这几个参数只是针对
InnoDB 存储引擎的，累加的算法也略有不同。
 Innodb_rows_read：select 查询返回的行数。
 Innodb_rows_inserted：执行INSERT 操作插入的行数。
 Innodb_rows_updated：执行 UPDATE 操作更新的行数。
 Innodb_rows_deleted：执行DELETE 操作删除的行数。
通过以上几个参数，可以很容易地了解当前数据库的应用是以插入更新为主还是以查询
操作为主，以及各种类型的 SQL 大致的执行比例是多少。对于更新操作的计数，是对执行
次数的计数，不论提交还是回滚都会进行累加。
对于事务型的应用， 通过Com_commit 和Com_rollback 可以了解事务提交和回滚的情况，
对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。
此外，以下几个参数便于用户了解数据库的基本情况。
 Connections：试图连接 MySQL 服务器的次数。
 Uptime：服务器工作时间。
 Slow_queries：慢查询的次数。
*/

可以通过以下两种方式进行定位执行效率较低的SQL语句
- 通过慢查询日志定位那些执行效率较低的 SQL 语句，用–log-slow-queries[=file_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的SQL 语句的日志文件。
- 慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist 命令查看当前MySQL 在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。
通过explain分析低效sql的执行计划

每个列的简单解释如下：
- select_type：表示 SELECT 的类型，常见的取值有 SIMPLE （简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SELECT）等。
- table：输出结果集的表。
- type：表示表的连接类型，性能由好到差的连接类型为system（表中仅有一行，即常量表）、const（单表中最多有一个匹配行，例如 primary key 或者 unique index）、eq_ref（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用primary key 或者unique index）、 ref （与eq_ref 类似，区别在于不是使用primarykey 或者 unique index，而是使用普通的索引）、ref_or_null（与 ref 类似，区别在于条件中包含对 NULL 的查询）、index_merge(索引合并优化)、unique_subquery（in的后面是一个查询主键字段的子查询）、 index_subquery （与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引字段的子查询）、 range （单表中的范围查询）、index （对于前面的每一行，都通过查询索引来得到数据）、all （对于前面的每一行

使用索引

以下几种情况有可能是用到索引

对于创建多列索引，只要查询的条件用到了最左的列，索引一般就会被使用

create index idx_sales_companyid_moneys on sales(company_id, moneys);
select * from sales where company_id = ?;
--type: ref
select * from sales where moneys = ?;
--type: all

对于like的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能会被使用

select * from sales where name like '%3'
--type: all
select * from sales where name like '3%'
--type: ref

如果对于大的文本进行检索，使用全文索引而不用like %..%
如果列名是索引，使用column_name is null 将使用索引，如下查询name为null的记录就用到了索引
1
2
select * from company2 where name is null
--type: ref

以下情况是存在索引但不使用索引

如果mysql估计使用索引比全表扫描更慢，则不适用索引。例如如果索引key_part1均匀分布在1和100之间，下列查询使用索引就不好
1
select * from table_name where key_part1 > 1 and key_part1 < 90;
如果or分割的条件，如果or前的条件中的列有索引，而后面的列没有索引，那么涉及到的索引不会被用到，例如：
1
2
3
show index from sales;
column_name: years
select * from sales where years = 2000 and country = 'China';
(隐式转换）如果列类型是字符串，那么需要把字符常量值用引号引起来，否则的话即便这个列上有索引，mysql也不会用到
1
2
3
4
explain select * from company where name = 294
--type: all
explain select * from company where name = '294'
--type ref

查看索引使用情况

如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。

Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，Handler_read_rnd_next 的值较高，则通常说明表索引不正确或写入的查询没有利用索引，具体如下。

indexstatus

优化方法

定期分析表和检查表

分析表的命令如下

1	ANALYZE [LOCAL \| NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

检查表的作用是检查一个或多个表是否有错误。比如在视图定义中被引用的表已不存在。
1
2
CHECK TABLE tbl_name [, tbl_name] ... [option] ... option = {QUICK | FAST | MEDIUM | EXTENDED
| CHANGED}

定期优化表

1 2	--优化表的命令如下 OPTIMIZE [LOCAL \| NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（含有 VARCHAR、BLOB 或 TEXT 列的表）进行了很多更改，则应使用 OPTIMIZE TABLE 命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，OPTIMIZE TABLE 命令只对 MyISAM、BDB 和 InnoDB 表起作用。

常用的sql优化针对语句

大批量导入数据

当用 load 命令导入数据的时候，适当的设置可以提高导入的速度。

针对innodb的插入优化

因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。

1
2
3

mysql> load data infile '/home/mysql/film_test3.txt' into table film_test4;
Query OK, 1587168 rows affected (22.92 sec)
Records: 1587168 Deleted: 0 Skipped: 0 Warnings: 0

当导入数据前执行在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
如果应用使用的自动提交的方式，在导入之前执行SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

优化insert语句

在进行insert优化的时候可以考虑以下的优化方式

如果同时从同一客户插入很多行，尽量使用多个值表的INSERT 语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个 INSERT 语句快(在一些情况中几倍)。下面是一次插入多值的一个例子：
1
insert into test values(1,2),(1,3),(1,4)…
如果从不同客户插入很多行，能通过使用 INSERT DELAYED 语句得到更高的速度。DELAYED 的含义是让 INSERT 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快的多；LOW_PRIORITY 刚好相反，在所有其他用户对表的读写完后才进行插入；
将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）；

优化groupby语句

默认情况下，MySQL 对所有GROUP BY col1，col2….的字段进行排序。这与在查询中指定ORDER BY col1，col2…类似。因此，如果显式包括一个包含相同的列的 ORDER BY 子句，则对 MySQL 的实际执行性能没有什么影响。

如果查询包括 GROUP BY 但用户想要避免排序结果的消耗，则可以指定 ORDER BY NULL禁止排序，

优化orderby语句

在某些情况中， MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。WHERE 条件和 ORDER BY 使用相同的索引，并且 ORDER BY 的顺序和索引顺序相同，并且ORDER BY 的字段都是升序或者都是降序。

1
2
3

SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

以下情况不使用索引

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；
--order by的字段混合 ASC 和DESC
SELECT * FROM t1 WHERE key2=constant ORDER BY key1；
--用于查询行的关键字与 ORDER BY 中所使用的不相同
SELECT * FROM t1 ORDER BY key1, key2；
--对不同的关键字使用 ORDER BY：

优化嵌套查询

子查询这个技术可以使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接（JOIN）替代。

mysql针对or进行优化

对于含有 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。

使用sql提示（sql hint）

SQL 提示（SQL HINT）是优化数据库的一个重要手段，简单来说就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。

例如：

1	SELECT SQL_BUFFER_RESULTS * FROM...

use index

在查询语句中表名的后面，添加 USE INDEX 来提供希望MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。
1
explain select * from sales2 use index (ind_sales2_id) where id = 3\G;
ignore index

如果用户只是单纯地想让 MySQL 忽略一个或者多个索引，则可以使用 IGNORE INDEX 作为 HINT。同样是上面的例子，这次来看一下查询过程忽略索引 ind_sales2_id 的情况：
1
explain select * from sales2 ignore index (ind_sales2_id) where id = 3\G;

force index

为强制 MySQL 使用一个特定的索引，可在查询中使用FORCE INDEX 作为 HINT。例如，当不强制使用索引的时候，因为id 的值都是大于 0 的，因此 MySQL 会默认进行全表扫描，而不使用索引，如下所示：

explain select * from sales2 where id > 0 \G;
--全表扫描
explain select * from sales2 force index (ind_sales2_id) where id > 0
\G;
--，即便使用索引的效率不是最高，MySQL 还是选择使用了索引

小结：优化 SQL 语句经常需要考虑的几个方面，比如索引、表分析、排序等。

优化数据库对象

在数据库设计过程中，用户可能会经常遇到这种问题：是否应该把所有表都按照第三范式来设计？表里面的字段到底改设置为多大长度合适？这些问题虽然很小，但是如果设计不当则可能会给将来的应用带来很多的性能问题。本章中将介绍 MySQL 中一些数据库对象的优化方法，其中一些方法不仅仅适用于MySQL，也适用于其他类型的数据库管理系统。

• 1NF：消除重复列，确保字段值的原子性。

• 2NF：消除部分依赖，确保非主键字段完全依赖主键。

• 3NF：消除传递依赖，确保非主键字段直接依赖主键。

优化表的数据类型

表需要使用何种数据类型，是需要根据应用来判断的。虽然应用设计的时候需要考虑字段的长度留有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样即浪费磁盘存储空间,同时在应用程序操作时也浪费物理内存。

在 MySQL 中，可以使用函数PROCEDURE ANALYSE()对当前应用的表进行分析，该函数可以对数据表中列的数据类型提出优化建议，用户可以根据应用的实际情况酌情考虑是否实施优化。

通过拆分提高表的访问效率

这里所说的“拆分“。是指对数据表进行拆分。如果针对 MyISAM 类型的表进行，那么有两种拆分方法。

第一种方法是垂直拆分，即把主码和一些列放到一个表，然后把主码和另外的列放到另一个表中。

如果一个表中某些列常用，而另外一些列不常用，则可以采用垂直拆分，另外垂直拆分可以使得数据行变小，一个数据页就能存放更多的数据，在查询时就会减少 I/O 次数。其缺点是需要管理冗余列，查询所有数据需要联合（JOIN）操作。
第二种方法是水平拆分，即根据一列或多列数据的值把数据行放到两个独立的表中。

水平拆分通常在以下几种情况下使用：
1. 表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。
2. 表中的数据本来就有独立性，例如，表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。
3. 需要把数据存放到多个介质上。
  
  例如，移动电话的账单表就可以分成两个表或多个表。最近 3 个月的账单数据存在一个表中，3 个月前的历史账单存放在另外一个表中，超过 1 年的历史账单可以存储到单独的存储介质上，这种拆分是最常使用的水平拆分方法。
  
  水平拆分会给应用增加复杂度，它通常在查询时需要多个表名，查询所有数据需要 UNION 操作。在许多数据库应用中，这种复杂性会超过它带来的优点，因为只要索引关键字不大，则在索引用于查询时，表中增加 2 至 3 倍数据量，查询时也就增加读一个索引层的磁盘次数，所以水平拆分要考虑数据量的增长速度，根据实际情况决定是否需要对表进行水平拆分。

逆规范化

移动电话的用户每月都会查询自己的账单，账单信息一般包含用户的名字和本月消费总金额，设想一下，如果用户的姓名和属性信息存放在一个表中，假设表名为 A，而用户的编号和他对应的账单信息存放在另外一张 B 表中，那么，用户每次查询自己的月账单时，数据库查询时都要进行表连接，因为账单表 B 中并不包含用户的名字，所以必须通过关联 A 表取过来，如果在数据库设计时考虑到这一点，就可以在 B 表增加一个冗余字段存放用户的名字，这样在查询账单时就不用再做表关联，可以使查询有更好的性能。

反规范化的好处反规范的好处是降低连接操作的需求、降低外码和索引的数目，还可能减少表的数目，相应带来的问题是可能出现数据的完整性问题。加快查询速度，但会降低修改速度。因此决定做反规范时，一定要权衡利弊，仔细分析应用的数据存取需求和实际的性能特点，好的索引和其他方法经常能够解决性能问题，而不必采用反规范这种方法。

在进行反规范操作之前，要充分考虑数据的存取需求、常用表的大小、一些特殊的计算（例如合计）、数据的物理存储位置等。常用的反规范技术有增加冗余列、增加派生列、重新组表和分割表。

增加冗余列：指在多个表中具有相同的列，它常用在查询避免连接操作
增加派生列：值增加的列来自其他表中的数据，由其他表中的数据经过计算生成。增加的派生列水产在查询时减少连接操作，避免使用集函数。
重新组表：如果许多个用户需要查看两个表连接出来的结果数据，则把这两个表重新组合成一个表来减少连接而提高性能
分割表：

同时，逆规范化需要维护数据的完成行，一般维护数据的完整性使用的方法是批处理维护，应用逻辑和触发器

批处理维护是指对复制列或派生列的修改积累一定的时间后，运行一批处理作业或存储过程对复制或派生列进行修改，这只能在对实时性要求不高的情况下使用。
数据的完整性也可由应用逻辑来实现，这就要求必须在同一事务中对所有涉及的表进行增、删、改操作。用应用逻辑来实现数据的完整性风险较大，因为同一逻辑必须在所有的应用中使用和维护，容易遗漏，特别是在需求变化时，不易于维护。
另一种方式就是使用触发器（进行补偿），对数据的任何修改立即触发对复制列或派生列的相应修改。触发器是实时的，而且相应的处理逻辑只在一个地方出现，易于维护。一般来说，是解决这类问题比较好的办法。

使用中间表提高统计查询速度

对于数据量较大的表，在其上进行查询通常效率很低，还要考虑统计查询是否会对在线的应用产生负面的影响，通常在这种情况下可以使用中间表提高查询的效率，下面演示一下对于session表的统计来介绍中间表的使用

--session表记录了客户每天的消费记录
CREATE TABLE session (
cust_id varchar(10) , --客户编号
cust_amount DECIMAL(16,2), --客户消费金额
cust_date DATE, --客户消费时间
cust_ip varchar(20) –客户IP地址
)
--当前的case是想要针对客户一周的消费总金额和每天不同时段用户的消费总金额进行统计，针对这个需求我们可以通过两种方法来解决
mysql> select sum(cust_amount) from session where cust_date>adddate(now(),-7);


--创建中间表tmp_session
CREATE TABLE tmp_session (
cust_id varchar(10) , --客户编号
cust_amount DECIMAL(16,2), --客户消费金额
cust_date DATE, --客户消费时间
cust_ip varchar(20) –客户IP地址
) ;
--将要统计的信息直接转移到中间表上，可以得出想要的结果
mysql> insert into tmp_session select * from session where cust_date>adddate(now(),-7);
Query OK, 1573328 rows affected (6.67 sec)
Records: 1573328 Duplicates: 0 Warnings: 0

mysql> select sum(cust_amount) from tmp_session;