18. SQL优化

1 优化SQL语句的一般步骤

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

? MySQL客户端连接成功后，通过show[session|global]status命令可以提供服务器状态信息

# 所有存储引擎的表统计信息
show status like ‘Com_%‘;
# InnoDB相关的表统计信息
show status like ‘Innodb_%‘;
# 查看数据库基本情况
# Connections：试图连接 MySQL 服务器的次数。 
# Uptime：服务器工作时间。 
# Slow_queries：慢查询的次数。
show status like ‘Connections|Uptime|Slow_queries‘;

Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。

• Com_select：执行 select 操作的次数，一次查询只累加 1。
• Com_insert：执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
• Com_update：执行 UPDATE 操作的次数。
• Com_delete：执行 DELETE 操作的次数。

1.2 定位执行效率低的SQL语句

1.3 通过EXPLAIN分析低效率SQL的执行计划

通过 EXPLAIN 或者 DESC 命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

1.4 确定问题并采取相应的优化措施

如果经过上述步骤已经分析出问题出现的原因，此时用户可以根据情况进行相应的优化。

比如全表扫描导致查询效率低可以考虑添加索引。

2 索引问题

2.1 索引的存储分类

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

MySQL 中索引的存储类型目前只有两种（BTREE 和 HASH），具体和表的存储引擎相关：MyISAM 和 InnoDB 存储引擎都只支持 BTREE 索引；MEMORY/HEAP 存储引擎可以支持 HASH和 BTREE 索引。

2.2 MySQL如何使用索引

1、使用索引

(1)多列索引，最左原则，查询的条件中用到了最左边的列，索引一般就会使用。

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

(3)如果对大的文本进行搜索，使用全文索引而不用使用 like ‘%…%’。

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

(1)如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。例如如果列key_part1 均匀分布在 1 和 100 之间，下列查询中使用索引就不是很好：

SELECT * FROM table_name where key_part1 > 1 and key_part1 < 90;

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

(3)like后面的值以%开头

(4)如果列类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引起来，否则的话即便这个列上有索引，MySQL 也不会用到的。

2.3 查看索引使用情况

如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。 Handler_read_rnd_next 的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，Handler_read_rnd_next的值较高，则通常说明表索引不正确或写入的查询没有利用索引，具体如下。

mysql> show status like ‘Handler_read%‘; 
+-----------------------+-------+ 
| Variable_name         | Value | 
+-----------------------+-------+ 
| Handler_read_first    | 0     | 
| Handler_read_key      | 5     | 
| Handler_read_next     | 0     | 
| Handler_read_prev     | 0     | 
| Handler_read_rnd      | 0     | 
| Handler_read_rnd_next | 2055  | 
+-----------------------+-------+

3 两个简单实用的优化方法

3.1 定期分析表和检查表

analyze table tb1_name[,tb2_name]...
check table tb_name...;

3.2 定期优化表

优化表的语法如下：

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

如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（含有 VARCHAR、BLOB 或 TEXT 列的表）进行了很多更改，则应使用 OPTIMIZE TABLE 命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但OPTIMIZE TABLE 命令只对 MyISAM、BDB 和 InnoDB 表起作用。

ANALYZE、CHECK、OPTIMIZE 执行期间将对表进行锁定，因此一定注意要在数据库不繁忙的时候执行相关的操作。

4 常用SQL的优化

搜索资料了解，

USE INDEX(index_name...);
IGGNORE INDEX(index_name...);
FORCE INDEX(index_name...);

18. SQL优化

标签：tab   表示   之间   含义   常用sql   应该   char   sql 服务器   客户端