SQL优化

通过show status命令了解各种sql的执行效率

结果

• Com_select:执行select操作的次数，依次查询之累加1
• Com_insert:执行insert操作的次数，对于批量插入的insert操作，只累加依次
• Com_update:执行update操作的此时
• Com_delete:执行delete的次数

上面的参数是对所有存储引擎的表进行累计，下面参数是针对InnoDB存储引擎的，累加的算法也是略有不同的

• Innodb_rows_read:SELECT查询返回的行数
• Innodb_rows_insered:执行inser操作插入的行数
• Innodb_rows_updated:执行UPDATE操作更新的行数
• Innodb_rows_deleted执行DELETE操作删除的行数

通过上述的参数可以了解当前数据库的应用是插入更新为主还是查询操作为主，以及各类的SQL的执行比例是多少。对于更新操作的计算，是对执行次数的计数，无论提交还是回滚都会进行累加对于事务形的应用，通过Com_commit和Com_rollback可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在的问题

• Connections:试图连接MySql服务器的次数
• Uptime：服务器工作时间
• Slow_queries:慢查询的次数

定位执行效率低的SQL语句

1. 通过慢查询日志定位那些执行效率较低的sql语句，用--log-show-queries[=file_name]选项去启动，mysqlId写一个包含所有执行时间超过long_querty_time秒的sql语句的日志文件
2. 慢查询日志在查询结束后才记录，所以在应用反应执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前Mysql在进行的线程，包括线程的状态，是否锁表等，可以实时查看sql的执行情况，同时对一些锁表进行优化

通过explain分析执行sql的执行计划

explain或者desc获取mysql如何执行select语句的信息

每个列说明

• select_type：表示SELECT的类型，常见的取值有simple（简单表，即不用表连接或者子查询），primary（主查询，即外部查询），union（union中的第二个或者后面的查询语句），subquery（子查询中的第一个select）等
• table:输出结果集
• type：表示Mysql在表中找到所需行的方式，或者叫访问类型，常见类型如：all,index,range,ref,eq_ref,const,system,null,从做到右，性能由差到好

type=all，全表扫描，mysql遍历全表来找到匹配的行

explain select * from film where rating>9

type=index,索引全扫描，MySQL遍历整个索引来查询匹配的行

explain select title from film

type=range,索引范围扫描，常见,>=,between

explain select * from payment where customer_id>=300 and customer_id

type=ref，使用费索引扫描或唯一索引的前缀扫描

explain select * from payment where customer_id=350

type=eq_ref,类似ref，区别在于使用的索引是唯一索引，对于每个索引键值，表中有一条记录匹配；简单来说就是多表连接使用primary key或者unique index作为关联条件

explain select * from film a,film_text b where a.film_id=b.film_id

type=const/system,单表中最多有一个匹配行，查询起来非常迅速，索引这个匹配行中的其他列的值可以被优化器在当前查询中当做常量来处理,例如根据主键primary key或者唯一一个索引来查询

type null,mysql不用访问数据库直接得到结果

explain select 1 from dual where 1

mysql 4.1引入了explain extended命令，通过explain extended 加上show warnings可以查看mysql 真正被执行之前优化器所做的操作

explain select * from users;

show warnings;

可以从warning的字段中能够看到，会去除一些恒成立的条件，可以利用explain extended的结果来迅速的获取一个更清晰易读的sql语句

通过show profile 分析sql

通过profile，我们能够更清楚的了解sql执行的过程。例如我们知道，MyISAM表有表元数据的缓存（例如行，即COUNT()值),对于MyISAM表的COUNT()是不需要消耗太多资源，而对于Innodb来说，就没有这种元数据，CONUT（*）执行的比较慢

select count(*) from users;

执行完毕查看

show profiles

可以查看之前的queryid

show profile for query 2； 可以查看执行过程中线程的每个状态和消耗时间

其中 sendingdata 状态表示mysql线程开始访问数据行并把结果返回给客户端，而不仅仅是返回给客户端，由于在sending data状态下，mysql线程往往需要做大量的磁盘读取操作；所以经常是整个查询中最耗时的状态

mysql 支持进一步选择all,cpu,block io,context,switch,page faults等明细来查看mysql在使用什么资源上耗费了过高的时间，例如，选择查看cpu的耗费时间

show profile cpu for query 6;

对比MyISAM的操作，同样执行count()操作，检查profile，Innodb表经历了Sending data状态，而MyISAM的表完全不需要访问数据

*如果对Mysql 源码感兴趣**，可以通过show profile source for query查看sql解析执行过程的每个步骤对应的源码文件

show profile source for query 6

通过trace分析优化器如何

MySql 5.6提供对sql的跟踪trace，通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划，帮助我们更好地了解优化器的行为

索引问题

mysql提供四种索引

• B-Tree索引:最常见的的索引，大部分引擎支持B树索引
• HASH索引:只有Memory引擎支持，使用场景简单
• R-Tree索引:空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
• Full-text：全文索引也是MyISAM的一个特殊索引，主要用于全文索引，InnoDb从MySql5.6开始提供支持全文索引

MySql目前不支持函数索引，但是能对列的前面某一部分进行索引，例如标题title字段，可以只取title的前10个字符索引，这样的特性大大缩小了索引文件的大小，但前缀索引也有缺点，在排序order by和分组group by操作的时候无法使用

create index idx_title on film(title(10));

常用的索引就是B-tree索引和hash索引，资只有memory引擎支持HASH索引，hash索引适用于key-value查询，通过hash索引比B-tree索引查询更加迅速，但是hash索引不支持范围查找例如==等操作，如果使用memory引擎并且where不使用=进行 索引列，就不会用的索引。Memory只有在"="的条件下才会使用索引

简单的优化方法

本语句可以用于分析和存储表的关键字分布，分析的结果可以使得系统得到准确的统计信息使得sql，能够生成正确的执行计划。如果用户感觉实际执行计划并不预期的执行计划，执行一次分析表可能会解决问题

analyze table payments;

检查表：检查表：检查表的作用是检查一个表或多个表是否有错误,也可以检查视图是否错误

check table payment;

优化表:如果删除了表的一大部分，或者如果已经对可变长度的行表（含varchar、blob、text列）的表进行改动，则使用optimize 进行表优化，这个命令可以使表中的空间碎片进行合并、并且可以消除由于删除或者更新造成的空间浪费

optimize table payment;

对于innodb引擎的表，可以通过设置innodb_file_per_taable参数，设置InnoDb为独立表空间模式，这样每个数据库的每个表都会生成一个独立的idb文件，用于存储表的数据和索引，可以一定程度减少Innodb表的空间回收问题,另外，在删除大量数据后，Innodb表可以通过alter table但是不锈钢引擎方式来回收不用的空间

alter table payment enigine=innodb;

ANALYZE,CHECK,OPTIMIZE,ALTER TABLE执行期间都是对表进行锁定，因此要在数据库不频繁的时候执行相关的操作

常用SQL的优化

大批量的插入数据

当用load导入数据，适当的设置可以提供导入的速度

对于MyISAM存储引擎的表，可以通过以下方式快速导入大量的数据

disable keys和enable keys 用来打开或者关闭MyISAM表非索引的更新。在导入大量的数据到一个非空的MyISAM表，通过设置这两个命令，可以提高导入的效率

对于Innodb类型的表不能使用上面的方式提高导入效率

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

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

优化insert语句

• 如果同时从一个客户端插入很多行，应尽量使用多个值表的insert语句，这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗，使得效率比分开执行的单个insert语句快（大部分情况下，使用多个值表的insert语句那比单个insert语句快上好几倍）。
• insert into test values(1,2),(1,3)...
• 如果从不同客户插入很多行，可以通过使用insert delayed语句提高更高的速度，delayed的含义是让insert语句马上执行，其实数据都被放到内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快的多；LOW_PRIORITY刚好相反，在所有其他用户对表的读写完成后才可以进行
• 将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）
• 如果进行批量插入，可以通过增加bulk_insert_buffer_size变量值的方法来通过速度，但是，这只能对MyISAM表使用。
• 当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句块快20倍

优化ORDER BY语句

MySQL有两种排序方式

• 第一种通过有序排序索引顺序扫描，这种方式在使用explain分析查询的时候显示为Using Index，不需要额外的排序，操作效率较高
• explain select customer_id from customer order by store_id;
• 第二张通过返回数据进行排序，也就是通常说的Filesort排序，所有不是通过索引直接返回排序结果的排序豆角Filesort排序。Filesort并不代表通过磁盘文件进行排序，而只是说明进行了一个排序操作，至于排序操作是否进行了磁盘文件或临时表等，则取决于MySql服务器对排序参数的设置和需要排序数据的大小
• explain select * from customer order by store_id;
• Filesort是通过相应的排序算法，将取得的数据在sort_buffer_size系统变量设置的内存排序区进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集。sort_buffer_size设置的排序区是每个线程独占的，所有同一个时刻，MySql存在多个sort buffer排序区

优化目标:尽量减少额外的排序，通过索引直接返回有序数据.where和ordery by 使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序。否则肯定需要额外的排序操作，这样就会出现filesort

优化group by 语句

如果查询包括group by 但用户想要避免排序结果的消耗，可以指定group by null

优化嵌套查询

子查询可以被更有效率的连接替代

explain select * from customer where customer_id not in(select customer_id from payment)

explain select * from customer a left join payment b on a.customer_id=b.customer_id where b.customer id is null

连接之所用更有效率是因为mysql不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作

优化分页查询

一般分页查询，通过创建覆盖索引能够比较好地提高性能。一个场景是"limit 1000,20",此时Mysql排序出前1020条数据后仅仅需要返回第1001到1020条记录，前1000条数据都被抛弃，查询和排序代价非常高

优化方式：可以增加一个字段last_page_record.记录上一页和最后一页的编号，通过

explain select ...where last_page_record<... desc limt ..>

如果排序字段出现大量重复字段，不适用这种方式进行优化

MySql常用技巧

正则表达式的使用

使用

select 'abcdefg' regexp '^a';

.....

如果range()提取随机行

随机抽取某些行

select * from categrory order by rand() limit 5;

利用group by的with rollup 子句

使用Group By的with rollup可以检索更多分组聚合信息

select date_from(payment_date,'%Y-%M'),staff_id,sum(amount) from payment group by date_formate(payment_date,'%Y-%M'),staff_id;

用BIT GROUP FUNCTIONS做统计

使用GROUP BY语句和BIT_AND、BIT_OR函数完成统计工作，这两个函数的一般用途就是做数值之间的逻辑