sql优化分析思路

优化 SQL  语句的一般步骤 ： ( 记得开启慢查询 )

1 、通过 show status  命令了解各种  SQL 的执行频率。

格式： mysql> show [session|global]status;

其中： session （默认）表示当前连接，  global 表示自数据库启动至今

例：                     

mysql>show status;

mysql>show global status;

mysql>show status like ‘Com_%’;

mysql>show global status like ‘Com_%’;

参数说明 ：

Com_XXX 表示每个  XXX 语句执行的次数如 ：

Com_select  执行  select 操作的次数，一次查询只累计加  1

Com_update  执行  update 操作的次数

Com_insert  执行  insert 操作的次数，对批量插入只算一次。

Com_delete  执行  delete 操作的次数

只针对于 InnoDB  存储引擎的：

InnoDB_rows_read  执行  select 操作的次数

InnoDB_rows_updated  执行  update 操作的次数

InnoDB_rows_inserted  执行  insert 操作的次数

InnoDB_rows_deleted  执行  delete 操作的次数

其他 ：

connections  连接  mysql 的数量

Uptime  服务器已经工作的秒数

Slow_queries: 慢查询的次数

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

1)explain select * from table  where id=1000;

2)desc select * from table where id=1000;

3 、通过  EXPLAIN 分析较低效 SQL  的执行计划

如： mysql>   explain select count(*) from stu where name like "a%"\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: stu

type: range

possible_keys: name,ind_stu_name

key: name

key_len: 50

ref: NULL

rows: 8

Extra: Using where; Using index

1 row in set (0.00 sec)

每一列的简单解释

id : 1

select_type : SIMPLE   表示 select 的类型，常见的取值有  SIMPLE （）简单表，即不使用表连接或者子查询）、  PRIMARY （主查询，即外层的查询）、  UNION （  UNION 中的第二个或者后面的查询语句）、  SUBQUERY （子查询中的第一个  SESECT ）等

table : stu     输出结果集的表

type : range    表示表的连接类型，性能有好到差：  system （表仅一行）、  const （只一行匹配）、  eq_ref （对于前面的每一行使用主键和唯一）、  ref （同  eq_ref ，但没有使用主键和唯一）、  ref_or_null （同前面对  null 查询）、  index_merge （索引合并优化）、  unique_subquery （主键子查询）、 index_subquery （非主键子查询）、  range （表单中的范围查询）、  index （都通过查询索引来得到数据）、  all （通过全表扫描得到的数据）

possible_keys : name,ind_stu_name    表查询时可能使用的索引。

key:  name   表示实际使用的索引。

key_len : 50    索引字段的长度

ref : NULL  

rows:  8     扫描行的数量

Extra : Using where; Using index   执行情况的说明和描述

索引问题

索引是数据库优化中最常见也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的  SQL 性能问题。

MyISAM 存储引擎的表的数据和索引是自动分开存储的，各自是独一的一个文件；  InnoDB 存储引擎的表的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。

MySQL 目前不支持函数索引，但是能对列的前面某一部分进行索引，例如  name 字段，可以只取  name 的前  4 个字符进行索引，这个特性可以大大缩小索引文件的大小，用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。

MySQL 如何使用索引

索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高  SELECT 操作性能的最佳途径 。

1 、使用索引

(1) 对于创建的多列索引，只要查询的条件中用到最左边的列，索引一般就会被使用。如下创建一个复合索引。

然后按 company_id  进行查询，发现使用到了复合索引

使用下面的查询就没有使用到复合索引

mysql>explain select * from sales2 where moneys=1\G

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

// 没有用到索引

mysql> explain select * from company2 where name like "%3"\G

// 用到了索引

mysql> explain select * from company2 where name like "3%"\G

(3) 如果对大的文本进行搜索，使用全文索引而不使用   like“%...%”.

(4) 如果列名是索引，使用  column_name is null 将使用索引。如下：

mysql> explain select * from company2 where name is null\G

2 、存在索引但不使用索引

(1) 如果  MySQL 估计使用索引比全表扫描更慢，则不使用索引（这种不常用）。例如如果列  key_part1 均匀分布在  1 到  100 之间，查询时使用索引就不是很好

mysql>select * from table_name where key_part1>1 and key_part<90;(2) 如果使用 MEMORY/HEAP  表并且 where 条件中不使用  “=” 进行索引列，那么不会用到索引。  Heap 表只有在  “=” 的条件下会使用索引。

(3) 用  or 分割开的条件，如果  or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

(4) 如果不是索引列的第一部分，如下例子  : 可见虽然在  money 上面建有复合索引，但是由于  money 不是索引的第一列，那么在查询中这个索引也不会被  MySQL 采用。

(5) 如果  like 是以  % 开始，可见虽然在  name 上面建有索引，但是由于  where 条件中  like 的值的  “%” 在第一位了，那么 MySQL 也会采用这个索引。

如： mysql> explain select * from company2 where name like‘%3’\G

(6) 如果列类型是字符串，但在查询时把一个数值型常量赋值给了一个字符型的列名  name ，那么虽然在  name 列上有索引，但是也没有用到

如： mysql> explain select * from company2 where name name=294\G

而下面的 sql  语句就可以正确使用索引。

mysql> explain select * from company2 where name name=‘294’\G

查看索引使用情况

如果索引正在工作， Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数。

Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。

两个简单实用的优化方法

1 、定期分析表和检查表

分析表的语法如下 :( 检查一个或多个表是否有错误   )

mysql> CHECK TABLE tbl_name[,tbl_name] … [option] … option = { QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

例： mysql>   check table sales;

2 、定期优化表

优化表的语法格式

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]

如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表进行了很多的改动，则需要做定期优化。这个命令可以将表中的空间碎片进行合并，但是此命令只对  MyISAM 、  BDB 和  InnoDB 表起作用。

例： mysql>   optimize table sales;