mysql——语句优化简介

sql语句的优化可以从那些方面入手：前提是硬件资源没到瓶颈。

1、索引最简单也是最常用的 （主要介绍的点）

2、Mysql的分区、分表、读负载均衡、读写分离

3、Mysql的配置文件优化

 

 

具体的优化步骤：

1、寻找运行缓慢的语句：方法很多，比较常用的“慢日志”、“show full processlist;”“开发直接不负责任的扔给你的”具体慢日志如何开启怎么查看，以及show processlist使用请查下博客;

2、确认低效查询，重复运行sql语句（加上不缓存的关键字）。如果运行时间仍然很长则基本上确定这条语句需要优化。

例如：SELECT SQL_NO_CACHE id,field FROM table WHERE id=1; 运行时加上SQL_NO_CACHE强制不缓存数据。

3、生成查询计划：用explain关键字+执行的sql。具体explain请查博客

4、优化查询：索引虽然比较方便但最好不要随便加，索引过多会影响写的性能。另外对于数据量很大的表，增加索引会阻塞增删改查等的操作（msyql5.6之后innodb引擎表是可以利用插件来实现不阻塞），添加索引可能需要很长时间所以这些影响需要提前想好。利用现有的索引优化sql也是不错的选择。当然可以通过建索引，给表分区或分表，或者配置文件进一步细化，当然读写分离集群等措施也是有助于降低服务压力的。

5、确认优化完成

直接运行语句查看运行时间、或者explain关键字+执行的sql语句。查看是否使用了索引即扫描的行数。

大致的步骤就是上边的，下边是注意事项和一些工具的使用。

 常用的命令：

show table  status like ‘table_name’\G;

查看表数据量,对于innodb引擎的表是个估计值。

1.Name  

       表名称

2.Engine:

       表的存储引擎

3.Version:

       版本

4.Row_format

       行格式。对于MyISAM引擎，这可能是Dynamic，Fixed或Compressed。动态行的行长度可变，例如Varchar或Blob类型字段。固定行是指行长度不变，例如Char和Integer类型字段。

5. Rows

       表中的行数。对于非事务性表，这个值是精确的，对于事务性引擎，这个值通常是估算的。

6.Avg_row_length

       平均每行包括的字节数 

7.Data_length

       整个表的数据量(单位：字节)

8.Max_data_length

      表可以容纳的最大数据量

9.Index_length

      索引占用磁盘的空间大小 

10.Data_free

     对于MyISAM引擎，标识已分配，但现在未使用的空间，并且包含了已被删除行的空间。

11.Auto_increment

     下一个Auto_increment的值

12.Create_time

     表的创建时间

13.Update_time

     表的最近更新时间

14.Check_time

     使用 check table 或myisamchk工具检查表的最近时间

15.Collation

     表的默认字符集和字符排序规则

16.Checksum

如果启用，则对整个表的内容计算时的校验和
17.Create_options

指表创建时的其他所有选项

18.Comment

包含了其他额外信息，对于MyISAM引擎，包含了注释徐标新，如果表使用的是innodb引擎 ，将现实表的剩余空间。如果是一个视图，注释里面包含了VIEW字样。

 

 

EXPLAIN 的关键字：PARTITIONS 、EXTENDED

 Explain partitions select * from table_name where year in (2011,2012)\G;

 查看是否使用了分区过滤，若表上有分区，使用分区过滤会有很大的效率提升。

 

 

 

 

Mysql read的计数器。对于mysql系统的索引使用情况有个宏观的概括。

1. mysql> show status like 'handler_read_%';

2. +-----------------------+-------+

3. | Variable_name | Value |

4. +-----------------------+-------+

5. | Handler_read_first | 1 |

6. | Handler_read_key | 1 |

7. | Handler_read_last | 0 |

8. | Handler_read_next | 0 |

9. | Handler_read_prev | 0 |

10. | Handler_read_rnd | 0 |

11. | Handler_read_rnd_next | 21 |

12. +-----------------------+-------+

13. 7 rows in set (0.01 sec) 

如上所示，mysql中关于read的计数器，有7个。他们的数值对于系统的状况的了解，对于系统的调优都十分重要。我们应该理解他们的含义。本文是自己的一些理解。
首先7个计数器，我们应该分为两部分：
1）对索引读的计数器：前面的5个都是对索引读情况的计数器，
     Handler_read_first：是指读索引的第一项（的次数）；
     Handler_read_key：是指读索引的某一项（的次数）；
     Handler_read_next：是指读索引的下一项（的次数）；
     Handler_read_last：是指读索引的最后第一项（的次数）；
     Handler_read_prev：是指读索引的前一项（的次数）；
5者应该有四种组合：
1. Handler_read_first 和 Handler_read_next 组合应该是索引覆盖扫描
2. Handler_read_key 基于索引取值
3. Handler_read_key 和 Handler_read_next 组合应该是索引范围扫描
4. Handler_read_last 和 Handler_read_prev 组合应该是索引范围扫描（orde by desc）

2）对数据文件的计数器：后面的2个都是对数据文件读情况的计数器，
Handler_read_rnd:
The number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that do not use keys properly.

Handler_read_rnd_next

The number of requests to read the next row in the data file. This value is high if you are doing a lot of

table scans. Generally this suggests that your tables are not properly indexed or that your queries are

not written to take advantage of the indexes you have.

 

这里很重要的一点要理解：索引项之间都是有顺序的，所以才有first, last, next, prev等等，所以前面的5个都是对索引读情况

的计数器，而后面的2个是对数据文件的读情况的计数器。

 

很显然的一点：后面的2个 Handler_read_rnd 和 Handler_read_rnd_next 是越低越好，如果很高，应该进行索引相关的调优或者你们的开发写的sql语句太烂或者dba的水平差。而Handler_read_key的数值,肯定是越高越好，越高代表使用索引读很高，dba能力不错干活不错或尽职尽责了。

 

 

 

一些常识：

1、使用索引连接表：当查询涉及表连接时最好连接字段在两张表中都是有索引的。

 

 2、使用索引进行模式匹配：意思就是在sql中有类似于like ‘abc%’之类的语句时一定不能将通配符%放到前边，放到前边索引不会用到。

 

3、创建多个单列索引时最好一起创建，可节约很多时间。

 

 4、合并where和order by 语句：所以要合并where和order by 语句：意思是当sql语句需要多个索引时建立合适的组合索引可以使同时满足where和order by对索引的需求。

 

5、索引提示：USE、IGNORE、FORCE、不常用。在查询时可以自行定义使用的索引。

 

 6、索引列不要和mysql内置的函数一起使用，这样会导致索引列不会被用到。

 

 7、创建更好的索引：“覆盖索引”就是在where或者order by 或者group by子句中都用到的列都建立索引，那么效率可以更高。

 

 8、Mysql配置选项，好的配置当然会使查询效率增高。但个人觉得优化sql不要从这个方面下手。效果并不明显。

 

 9、截取sql语句：方法很多依个人习惯挑选使用。