软件开发培训班 >> 编程开发 >> MYSQL


MySQL SQL优化的实现并不难

　　本文标签：MySQL SQL

　　此文章主要向大家描述的是MySQL SQL优化的实际操作方案，同时还涉及一个MySQL数据库查询所使用index的问题，MySQL数据库的强制索引（Force Index）能实现哪些优化？以下的文章都有答案  。

　　

　　MySQL5下大数据量查询优化的问题

　　

　　推荐圈子: Database圈子

　　更多相关推荐 一般使用MySQL SQL的时候你是不会去想到优化  。但是面对一个有SQL性能问题的数据库时，我们应该如何入手进行系统的分析，使得能够尽快定位问题SQL，并且尽快解决问题  。

　　

　　1.使用show status 命令了解各种MySQL SQL的执行频率

　　引用

　　例如在MySQL的Cline上输入

　　show status like Com_%;

　　

　　

　　显示的是一些:Com_xxx.

　　

　　Com_xxx 表示每个xx语句执行的次数  。通常情况下我们比较关注如下一些操作：

　　

　　引用

　　Com_select:执行select操作的次数

　　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操作删除的行数

　　

　　

　　通过上面的一些参数，我们可以了解当前数据库的应用是以插入为主还是以查询为主  。以及各种类型的MySQL SQL大致的执行比例是多少  。对于更新操作的计数，是对执行次数的计数，不管提交还是回滚都会进行累加  。

　　

　　对于事务型的应用，通过Com_commit和Com_rollback进行分析  。如果回滚操作非常频繁那么要思考下是不是编写存在问题  。

　　下面有几个参数用于了解数据库的基本情况

　　

　　引用

　　Connections:试图连接MySQL服务器的次数（执行的命令是：show status like Con_%;）

　　Uptime: 服务器工作时间（执行的命令是：show status like Up_%;）

　　

　　Slow_queries:慢查询的次数（执行的命令是：show status like Slow_%;）

　　

　　2. 定位执行效率较低的SQL语句

　　要想定义效率较低的SQL可以按照下面两种方式试试  。

　　引用

　　1. 通过慢查询日志定位那些执行效率较低的SQL语句，用 --log-slow-queries[=file_name]选项启动时，MySQLd写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件  。

　　2. 慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候进行查询慢查询日志并不能定位问题，可以使show processlist 命令查看当前MySQL在进行的线程，包括线程的状态，是否锁表等，可以实时地查看MySQL SQL的执行情况，同时对一些锁表操作进优化  。

　　

　　

　　3. 使用EXPLAIN分析低效SQL的执行计划  。

　　在查询到效率低的SQL语句后，那我们可以使用explain或者DESC命令获取Myswl如何执行SELECT语句的信息，包括在Select语句执行过程中表如何连接和连接的顺序  。

　　

　　例如你想计数xxxx年公司的销售额，那么需要操作sales和comapny table，并对money字段进行sum操作  。看看怎么使用explain：

　　

　　引用

　　explain select sum(moneys) from sales a company b where a.company_id = b.id and a.year=XXXX \G;(注意加上\G是为了更好的看)

　　显示如下：

　　

　　

　　

  1. id: 1   
  2. select_type: SIMPLE   
  3. table: a   
  4. type: ALL   
  5. possible_keys: NULL   
  6. key:NULL   
  7. key_len: NULL   
  8. ref: NULL   
  9. rows:1000   
  10. Extra: Using where   
  11. id: 2   
  12. select_type: SIMPLE   
  13. table: b   
  14. type: ref   
  15. possible_keys: ind_company_id   
  16. key:ind_comapany_id   
  17. key_len: 5   
  18. ref: sakila.a.company_id   
  19. rows:1   
  20. Extra: Using where;Using index  

　　下面解释下每个列的含义：

　　引用

　　select_type: 表示SELECT的类型，常见的取值为SIMPLE(简单表，不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)、UNION、SUBQUERY

　　table: 输出结果集的表

　　type: 表示表的连接类型，性能由好到差的类型类型为

　　(System(表中仅有一行，即常量表)，

　　

　　const(单表中最多有一个匹配行)，

　　

　　eq_ref(对于前面的每一行,在此表中只查询一条记录)，

　　

　　ref(使用普通的索引)，

　　

　　ref_or_null(和ref类似，但是条件中包含对于NULL查询),

　　

　　index_merge(索引合并优化),

　　

　　unique_subquery(in的后面是一个查询主键字段的子查询),

　　

　　index_subquery(类似unique_subquery，主要是in的后面是查询非唯一索引字段的子查询),

　　

　　range(单表中的范围查询),

　　

　　index(对于当前的每一行，都通过查询索引来得到数据),

　　

　　all(对于当前的每一行,都通过全表扫描来得到数据))

　　

　　possible_keys: 表示查询时，可能使用的索引

　　key:表示实际使用的索引

　　

　　key_len:索引字段的长度

　　

　　rows:扫描行的数量

　　

　　Extra:执行情况的说明和描述

　　

　　

　　

　　以上的相关内容就是对MySQL SQL优化的笔记的介绍，望你能有所收获  。