SQL语句优化 (一) （52）
优化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>create index ind_company2_name on company2(name(4));
其中company表名 ind_company2_name索引名
MySQL如何使用索引
索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高SELECT操作性能的最佳途径。
1、使用索引
(1)对于创建的多列索引，只要查询的条件中用到最左边的列，索引一般就会被使用。如下创建一个复合索引。
mysql>create index ind_sales2_com_mon onsales2(company_id,moneys);
然后按company_id进行查询，发现使用到了复合索引
mysql>explain select * from sales2 where company_id=2006\G
使用下面的查询就没有使用到复合索引。
mysql>explain select * from sales2 where moneys=1\G
(2) 使用like的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能会被使用，如下：
mysql> explain select * from company2 where name like "%3"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec)
如下这个使用到了索引，而下面例子能够使用索引，区别就在于“%”的位置不同，上面的例子是吧“%”放在了第一位，而下面的例子则没有
mysql> explain select * from company2 where name like “3%"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: range
possible_keys: ind_company2_name
key:
ind_company2_name
key_len: 11
ref: NULL
rows: 103
Extra: Using where
1 row in set (0.00 sec)
(3)如果对大的文本进行搜索，使用全文索引而不使用 like“%...%”.
(4)如果列名是索引，使用column_name is null将使用索引。如下
mysql> explain select * from company2 where name is null\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: ref
possible_keys: ind_company2_name
key:
ind_company2_name
key_len: 11
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
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前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
mysql>show index from sales\G
*************************** 1. row ***************************
… …
key_name: ind_sales_year
seq_in_index：1
Column_name: year
… …
