mysql优化

一、慢查询的概念

MySQL中语句的执行时间（响应时间）超过指定的long_query_time（即慢查询时间），通俗来说也就是执行的时间超过此数据库配置中设置的阈值，我们把超过这个时间的SQL语句查询称为“慢查询”。

二、导致慢查询的原因

1、没有索引或者没有用到索引

2、I/O吞吐量小，形成了瓶颈效应。

3、没有创建计算列导致查询不优化。

4、内存不足

5、网络速度慢

6、查询出的数据量过大(可以采用多次查询，其他的方法降低数据量)

7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)

8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。

9、返回了不必要的行和列

10、查询语句不好，没有优化

三、慢查询优化

1、把数据、日志、索引放到不同的I/O设备上，增加读取速度。数据量(尺寸)越大，提高I/O越重要.
2、纵向、横向分割表，减小表的尺寸(sp_spaceuse) ；
3、升级硬件 ；
4、根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当(最好是使用默认值0)。索引应该尽量小，使用字节数小的列建索引好(参照索引的创建),不要对有限的几个值的字段建单一索引如性别字段 ；
5、提高网速;
6、①扩大服务器的内存。
②配置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQL Server? 2000 时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的 1.5 倍。如果另外安装了全文检索功能，并打算运行 Microsoft 搜索服务以便执行全文索引和查询，可考虑：将虚拟内存大小配置为至少是计算机中安装的物理内存的 3 倍。将 SQL Server max server memory 服务器配置选项配置为物理内存的 1.5 倍(虚拟内存大小设置的一半)。
7、增加服务器CPU个数;但是必须明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MsSQL自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例如耽搁查询的排序、连接、扫描和GROUP BY字句同时执行，SQL SERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作UPDATE,INSERT，DELETE还不能并行处理。
8、如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引，耗空间。 like ‘a%’ 使用索引 like ‘%a’ 不使用索引用 like ‘%a%’ 查询时，查询耗时和字段值总长度成正比,所以不能用CHAR类型，而是VARCHAR。对于字段的值很长的建全文索引。
9、DB Server 和APPLication Server 分离;OLTP和OLAP分离
10、分布式分区视图可用于实现数据库服务器联合体。联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层 Web 站点的处理需要。有关更多信息，参见设计联合数据库服务器。(参照SQL帮助文件’分区视图’)
a、在实现分区视图之前，必须先水平分区表
b、在创建成员表后，在每个成员服务器上定义一个分布式分区视图，并且每个视图具有相同的名称。这样，引用分布式分区视图名的查询可以在任何一个成员服务器上运行。系统操作如同每个成员服务器上都有一个原始表的复本一样，但其实每个服务器上只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。
11、注意使用DISTINCT和ORDER BY，在没有必要时不要用，这些动作可以改在客户端执行。它们增加了额外的开销。这同UNION 和UNION ALL一样的道理。 它同UNION一样会使查询变慢。对于distinct，重复的记录在查询里是没有问题的（注意union和union all 的区别。UNION all更好）
12、查询时不要返回不需要的行、列
13、用select top 100 / 10 Percent 来限制用户返回的行数或者SET ROWCOUNT来限制操作的行。
14、尽量少用视图，它的效率低。
15、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数
16、用Profiler来跟踪查询，得到查询所需的时间，找出SQL的问题所在;用索引优化器优化索引

四、慢查询日志

慢查询日志是mysql的一个日志记录，用来记录mysql语句执行时间超过指定的long_query_time的SQL语句,long_query_time的默认值是10s，但是其实这个值我们可以通过更改配置改成自己需要的值。
如果数据库每次遇到慢查询的数据库语句，都将其记录到慢查询日志里，会影响其性能，所欲慢查询日志默认情况下是不开启的。但我们可以手动打开，但是手动打开只会对本次启动有效，mysql关闭重启后可以看到恢复默认状态，若要持久生效要改变my.ini配置文件（Window系统下）。
可通过show variables like '%slow_query_log%'来查看日志开启情况
可以用下面语句来改变默认的阀值
set long_query_time = 3;
1
然后我们可以用此代码来查看是否更改生效，若没有生效，可尝试重启一下mysql客户端即可
show variables like ‘long_query_
time’

mysqldumpslow日志分析工具
在实际的数据库使用过程中可能会有多条日志记录，数据复杂，人工分析费事费力，mysql提供了一个日志分析工具mysqldumpslow
可以根据你设定的参数查询出满足条件的日志记录，方便查看
可用的参数有
-s, 是表示按照何种方式排序
排序方式有
c: 访问计数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
-t, 是top n的意思，即为返回前面多少条的数据；
-g, 后边可以写一个正则匹配模式，大小写不敏感的；

Example：
得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
ps：建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

五、explain

对于一行可以执行的sql语句，我们可以通过在前面加上explain来使打印出一张神奇的表从里面我们可以得到以下信息：
表的读取顺序
数据读取操作的类型
哪些索引可以使用
哪些索引实际被使用
表之间的引用
每张表有多少行被优化器扫描…
id：select查询的序列号，代表的是select执行的顺序，主要有以下三种情况
id相同时，则按照从上到下依次执行
id不同时，id值越大优先级越高，越先被执行
id有相同有不同，则相同的id为一个组，不同组的id值按照规则二的优先级执行，同组id则按照规则一依次执行
select_type：select查询的类型，有以下常用几种
simple:表示该查询没有子查询和UNION连接查询
primary:有子查询时的最外层查询
subquery:有子查询时的内层嵌套查询
derived:在from中包含的select就称为derived(衍生) ，mysql会递归这些子查询，把结果放在临时表中
union:union的第二个或者最后一个
union result:union的结果
table:执行当前SQL语句用到的表
partitions:代表当前表所使用的分区
type:显示使用了何种查询，按照常见的几种查询最好到最坏排序为system>const>eq_ref>ref>range>index>all
system,const:mysql能够对这部分进行查询优化使能够将其转换成一个常量（system只返回一行，const有多行),如某一行的主键放入WHERE子句里的方式来选取此行的主键,MySQL就能将这个查询转换成一个常量。然后就可以高效的将表从联接执行中移除
eq_ref:使用该索引查找，mysql知道最多返回一条数据，可以在使用主键或者唯一性索引查找时用到
ref:非唯一性索引的索引查找
range:范围扫描，例如带有between或者>,<,in等
index:扫描所有索引行
all:扫描所有数据行
possible_keys/kesy:代表可能用到的索引和实际用到的索引
key_len:在索引中使用的字节数
ref:显示了之前的表在key列记录的索引中查找值所用的列或常量
rows:mysq估计的要找到满足条件的行所需要扫描的行数
filtered:给出了一个百分比的值，这个百分比的值和rows列的值一起使用，可以估计出那些将要和QEP中的前一个表进行连接的行的数目。前一个表就是指id列的值比当前表的id小的表
**extra:**给出一些额外但重要的信息，常见重要的信息有：
using index:使用了覆盖索引，以避免扫描表（良好情况）
using filesort:索引创建数据排序方式不满足要求，mysql在外部重新排序（严重，需要优化）
using temporary：mysql创建使用了临时表来保存信息（严重，需要优化）
using where：使用了where
using join buffer：在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果（需要增加索引进行优化）