当前位置:Gxlcms > 数据库问题 > 18. SQL优化

18. SQL优化

时间:2021-07-01 10:21:17 帮助过:4人阅读

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 服务器   客户端   

人气教程排行