文档章节

MySql优化的一般步骤

落叶刀
 落叶刀
发布于 2016/06/07 11:12
字数 3466
阅读 56
收藏 2

 

MySql优化的一般步骤:

1.通过show status 命令了解各种sql的执行效率

  SHOW STATUS提供msyql服务器的状态信息

  一般情况下,我们只需要了解以”Com”开头的指令

  show session status like ‘Com%’:显示当前的连接的统计结果

  show global status like ‘Com%’ :显示自数据库上次启动至今的统计结果

  注:默认是session级别的

  其中Com_XXX表示XXX语句所执行的次数。
重点注意:Com_select,Com_insert,Com_update,Com_delete通过这几个参数,可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主,以及各类的SQL大致的执行比例是多少。

  另外,还有几个参数需要注意下:

  show status like ‘Connections’// 试图连接MySQL服务器的次数

  show status like ‘Uptime’//服务器工作的时间(单位秒)

  show status like ‘Slow_queries’//慢查询的次数 (默认是10秒中就当做是慢查询,如下图所示)

  

  a) 如何查询mysql的慢查询时间

    Show variables like 'long_query_time';

  b) 修改mysql 慢查询时间

    set long_query_time=2//如果查询时间超过2秒就算作是慢查询

2. 定位执行效率较低的SQL语句(dql出现问题的概率较dml的大)

  问题是:如何在一个项目中,找到慢查询的select语句?

  答案:mysql支持把慢查询语句记录到日志文件中。程序员需要修改php.ini的配置文件,默认情况下,慢查询记录是不开启的。

  开启慢查询记录的步骤:

  打开 my.ini ,找到 [mysqld] 在其下面添加

  long_query_time = 2

  log-slow-queries = D:/mysql/logs/slow.log #设置把日志写在那里,可以为空,系统会给一个缺省的文件

     例子:我们数据表中有1千万条的数据量

  DQL语句:SELECT * FROM order_copy WHERE id=12345;

  

  查询耗时:19s>2s,所以mysql会将该条select语句记录到慢查询日志中

  SELECT * FROM order_copy WHERE id=12345的执行时间:

  添加索引前:19s

  添加索引后:0.08s

3.通过explain分析低效率的SQL语句的执行情况

  使用explain分析该dql语句:

EXPLAIN SELECT * FROM order_copy WHERE id=12345
会产生如下信息:
select_type:表示查询的类型。
table:输出结果集的表
type:表示表的连接类型(system和const为佳)
possible_keys:表示查询时,可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
rows:扫描的行数
Extra:执行情况的描述和说明

注意:要尽量避免让type的结果为all,extra的结果为:using filesort

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

  • 常用的优化措施是添加索引。添加索引,我们不用加内存,不用改程序,不用调sql,只要执行个正确的’create index’,查询速度就可能提高百倍千倍。但是天下没有免费的午餐,查询速度的提高是以插入、更新、删除的速度为代价的,这些写操作,增加了大量的I/O。

例如:给字段id添加索引:

ALTER TABLE order_copy ADD PRIMARY KEY(id)

给1千万的数据添加primary key 需要耗时: 428秒(7分钟)

EXPLAIN SELECT * FROM order_copy WHERE id=12345

正是因为给id添加了索引,才使得rows的结果为1

但是索引并不是可以随便添加的,以下几种情况需牢记在心:

  • 较频繁的作为查询条件字段应该创建索引

    select * from order_copy where id = $id

  • 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件

   select * from order_copy where sex=’女’

  • 更新非常频繁的字段不适合创建索引

    select * from order_copy where order_state=’未付款’

  • 不会出现在WHERE子句中字段不该创建索引

索引的类型:

  • PRIMARY 索引      => 在主键上自动创建
  • INDEX 索引          => 就是普通索引
  • UNIQUE 索引        => 相当于INDEX + Unique
  • FULLTEXT            => 只在MYISAM 存储引擎支持, 目的是全文索引,在内容系统中用的多, 在全英文网站用多(英文词独立). 中文数据不常用,意义不大 国内全文索引通常 使用 sphinx 来完成.

索引的使用

  • 建立索引 create [UNIQUE|FULLTEXT]  index index_name on tbl_name (col_name [(length)] [ASC | DESC] , …..);
    alter table table_name ADD INDEX [index_name] (index_col_name,...)

    添加主键(索引) ALTER TABLE 表名 ADD PRIMARY KEY(列名,..); 联合主键

  • 删除索引 DROP INDEX index_name ON tbl_name;
    alter table table_name drop index index_name;
  • 删除主键(索引)比较特别: alter table t_b drop primary key;
  • 查询索引(均可) show index from table_name;
    show keys from table_name;
    desc table_Name;

相关资料:

MySql优化的一般步骤:

1.通过show status 命令了解各种sql的执行效率

  SHOW STATUS提供msyql服务器的状态信息

  一般情况下,我们只需要了解以”Com”开头的指令

  show session status like ‘Com%’:显示当前的连接的统计结果

  show global status like ‘Com%’ :显示自数据库上次启动至今的统计结果

  注:默认是session级别的

  其中Com_XXX表示XXX语句所执行的次数。
重点注意:Com_select,Com_insert,Com_update,Com_delete通过这几个参数,可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主,以及各类的SQL大致的执行比例是多少。

  另外,还有几个参数需要注意下:

  show status like ‘Connections’// 试图连接MySQL服务器的次数

  show status like ‘Uptime’//服务器工作的时间(单位秒)

  show status like ‘Slow_queries’//慢查询的次数 (默认是10秒中就当做是慢查询,如下图所示)

  

  a) 如何查询mysql的慢查询时间

    Show variables like 'long_query_time';

  b) 修改mysql 慢查询时间

    set long_query_time=2//如果查询时间超过2秒就算作是慢查询

2. 定位执行效率较低的SQL语句(dql出现问题的概率较dml的大)

  问题是:如何在一个项目中,找到慢查询的select语句?

  答案:mysql支持把慢查询语句记录到日志文件中。程序员需要修改php.ini的配置文件,默认情况下,慢查询记录是不开启的。

  开启慢查询记录的步骤:

  打开 my.ini ,找到 [mysqld] 在其下面添加

  long_query_time = 2

  log-slow-queries = D:/mysql/logs/slow.log #设置把日志写在那里,可以为空,系统会给一个缺省的文件

     例子:我们数据表中有1千万条的数据量

  DQL语句:SELECT * FROM order_copy WHERE id=12345;

  

  查询耗时:19s>2s,所以mysql会将该条select语句记录到慢查询日志中

  SELECT * FROM order_copy WHERE id=12345的执行时间:

  添加索引前:19s

  添加索引后:0.08s

3.通过explain分析低效率的SQL语句的执行情况

  使用explain分析该dql语句:

EXPLAIN SELECT * FROM order_copy WHERE id=12345
会产生如下信息:
select_type:表示查询的类型。
table:输出结果集的表
type:表示表的连接类型(system和const为佳)
possible_keys:表示查询时,可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
rows:扫描的行数
Extra:执行情况的描述和说明

注意:要尽量避免让type的结果为all,extra的结果为:using filesort

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

  • 常用的优化措施是添加索引。添加索引,我们不用加内存,不用改程序,不用调sql,只要执行个正确的’create index’,查询速度就可能提高百倍千倍。但是天下没有免费的午餐,查询速度的提高是以插入、更新、删除的速度为代价的,这些写操作,增加了大量的I/O。

例如:给字段id添加索引:

ALTER TABLE order_copy ADD PRIMARY KEY(id)

给1千万的数据添加primary key 需要耗时: 428秒(7分钟)

EXPLAIN SELECT * FROM order_copy WHERE id=12345

正是因为给id添加了索引,才使得rows的结果为1

但是索引并不是可以随便添加的,以下几种情况需牢记在心:

  • 较频繁的作为查询条件字段应该创建索引

    select * from order_copy where id = $id

  • 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件

   select * from order_copy where sex=’女’

  • 更新非常频繁的字段不适合创建索引

    select * from order_copy where order_state=’未付款’

  • 不会出现在WHERE子句中字段不该创建索引

索引的类型:

  • PRIMARY 索引      => 在主键上自动创建
  • INDEX 索引          => 就是普通索引
  • UNIQUE 索引        => 相当于INDEX + Unique
  • FULLTEXT            => 只在MYISAM 存储引擎支持, 目的是全文索引,在内容系统中用的多, 在全英文网站用多(英文词独立). 中文数据不常用,意义不大 国内全文索引通常 使用 sphinx 来完成.

索引的使用

  • 建立索引 create [UNIQUE|FULLTEXT]  index index_name on tbl_name (col_name [(length)] [ASC | DESC] , …..);
    alter table table_name ADD INDEX [index_name] (index_col_name,...)

    添加主键(索引) ALTER TABLE 表名 ADD PRIMARY KEY(列名,..); 联合主键

  • 删除索引 DROP INDEX index_name ON tbl_name;
    alter table table_name drop index index_name;
  • 删除主键(索引)比较特别: alter table t_b drop primary key;
  • 查询索引(均可) show index from table_name;
    show keys from table_name;
    desc table_Name;

相关资料:

MySql优化的一般步骤:

1.通过show status 命令了解各种sql的执行效率

  SHOW STATUS提供msyql服务器的状态信息

  一般情况下,我们只需要了解以”Com”开头的指令

  show session status like ‘Com%’:显示当前的连接的统计结果

  show global status like ‘Com%’ :显示自数据库上次启动至今的统计结果

  注:默认是session级别的

  其中Com_XXX表示XXX语句所执行的次数。
重点注意:Com_select,Com_insert,Com_update,Com_delete通过这几个参数,可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主,以及各类的SQL大致的执行比例是多少。

  另外,还有几个参数需要注意下:

  show status like ‘Connections’// 试图连接MySQL服务器的次数

  show status like ‘Uptime’//服务器工作的时间(单位秒)

  show status like ‘Slow_queries’//慢查询的次数 (默认是10秒中就当做是慢查询,如下图所示)

  

  a) 如何查询mysql的慢查询时间

    Show variables like 'long_query_time';

  b) 修改mysql 慢查询时间

    set long_query_time=2//如果查询时间超过2秒就算作是慢查询

2. 定位执行效率较低的SQL语句(dql出现问题的概率较dml的大)

  问题是:如何在一个项目中,找到慢查询的select语句?

  答案:mysql支持把慢查询语句记录到日志文件中。程序员需要修改php.ini的配置文件,默认情况下,慢查询记录是不开启的。

  开启慢查询记录的步骤:

  打开 my.ini ,找到 [mysqld] 在其下面添加

  long_query_time = 2

  log-slow-queries = D:/mysql/logs/slow.log #设置把日志写在那里,可以为空,系统会给一个缺省的文件

     例子:我们数据表中有1千万条的数据量

  DQL语句:SELECT * FROM order_copy WHERE id=12345;

  

  查询耗时:19s>2s,所以mysql会将该条select语句记录到慢查询日志中

  SELECT * FROM order_copy WHERE id=12345的执行时间:

  添加索引前:19s

  添加索引后:0.08s

3.通过explain分析低效率的SQL语句的执行情况

  使用explain分析该dql语句:

EXPLAIN SELECT * FROM order_copy WHERE id=12345
会产生如下信息:
select_type:表示查询的类型。
table:输出结果集的表
type:表示表的连接类型(system和const为佳)
possible_keys:表示查询时,可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
rows:扫描的行数
Extra:执行情况的描述和说明

注意:要尽量避免让type的结果为all,extra的结果为:using filesort

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

  • 常用的优化措施是添加索引。添加索引,我们不用加内存,不用改程序,不用调sql,只要执行个正确的’create index’,查询速度就可能提高百倍千倍。但是天下没有免费的午餐,查询速度的提高是以插入、更新、删除的速度为代价的,这些写操作,增加了大量的I/O。

例如:给字段id添加索引:

ALTER TABLE order_copy ADD PRIMARY KEY(id)

给1千万的数据添加primary key 需要耗时: 428秒(7分钟)

EXPLAIN SELECT * FROM order_copy WHERE id=12345

正是因为给id添加了索引,才使得rows的结果为1

但是索引并不是可以随便添加的,以下几种情况需牢记在心:

  • 较频繁的作为查询条件字段应该创建索引

    select * from order_copy where id = $id

  • 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件

   select * from order_copy where sex=’女’

  • 更新非常频繁的字段不适合创建索引

    select * from order_copy where order_state=’未付款’

  • 不会出现在WHERE子句中字段不该创建索引

索引的类型:

  • PRIMARY 索引      => 在主键上自动创建
  • INDEX 索引          => 就是普通索引
  • UNIQUE 索引        => 相当于INDEX + Unique
  • FULLTEXT            => 只在MYISAM 存储引擎支持, 目的是全文索引,在内容系统中用的多, 在全英文网站用多(英文词独立). 中文数据不常用,意义不大 国内全文索引通常 使用 sphinx 来完成.

索引的使用

  • 建立索引 create [UNIQUE|FULLTEXT]  index index_name on tbl_name (col_name [(length)] [ASC | DESC] , …..);
    alter table table_name ADD INDEX [index_name] (index_col_name,...)

    添加主键(索引) ALTER TABLE 表名 ADD PRIMARY KEY(列名,..); 联合主键

  • 删除索引 DROP INDEX index_name ON tbl_name;
    alter table table_name drop index index_name;
  • 删除主键(索引)比较特别: alter table t_b drop primary key;
  • 查询索引(均可) show index from table_name;
    show keys from table_name;
    desc table_Name;

© 著作权归作者所有

共有 人打赏支持
落叶刀
粉丝 39
博文 125
码字总数 107201
作品 2
浦东
运维
私信 提问
SQL 常用优化手段总结 - 小技巧

中国有句古话叫做:欲速则不达。在一口气学完了分析 SQL 语句的一般步骤与索引的正确运用方式后小憩片刻。搭配上红茶与白兰地轻松享用下面这些小技巧吧! 系列文章回顾 SQL 常用优化手段总结...

给你添麻烦了
2018/01/10
0
0
一个小网站的搬迁过程

老网站:php5.2.6+sqlite,sqlite数据库文件大小20M左右,包括12张数据表。php文件50个左右,目录结构混乱。 新网站:php5.3.3+mysql 步骤: 确定搬迁目标机器环境(文件服务器,数据库服务器...

阿采
2016/03/04
122
0
MySQL索引原则和慢查询优化步骤

建索引的几大原则 1.最左前缀匹配原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。 2.=和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺...

乐在克里特
2018/04/06
0
0
MySQL慢查询 ------ 开启慢查询

一、简介 开启慢查询日志,可以让MySQL记录下查询超过指定时间的语句,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。 二、参数说明 slowquerylog 慢查询开启状态 slowquerylog...

a928154159
2018/01/16
0
0
Mysql彻底卸载

步骤1 【开始】—>MySQL—>MySQL Server 5.1—>MySQL Server Instance Config Wizard—>Remove Instance 步骤2 进入服务管理窗口,服务里面找到MySQL服务,将MySQL的服务给停止。 步骤3 进入...

woshixuye111
2014/06/07
0
0

没有更多内容

加载失败,请刷新页面

加载更多

MySQL查询执行

当我们希望MySQL能够以更高的性能运行查询时,最好的办法就是弄清楚MySQL是如何优化和执行查询的。一旦理解了这一点,很多查询优化工作实际上就是遵循一些原则让优化器能够按照预想的合理方式...

Linux就该这么学
2分钟前
0
0
爱可生开源社区官网正式发布啦!

近期大事记 2018/12/31 DBLE年度报告发版 2019/01/07 DBLE 2.18.12.0 新版发布,修复 issue 60+ 2019/01/09 DBLE 2.18.12.0 Release Notes 详细解读 2019/01/15 DBLE Logo 首发 + DBLE 团队迎......

爱可生
10分钟前
0
0
【分布式缓存系列】Redis实现分布式锁的正确姿势

一、前言   在我们日常工作中,除了Spring和Mybatis外,用到最多无外乎分布式缓存框架——Redis。但是很多工作很多年的朋友对Redis还处于一个最基础的使用和认识。所以我就像把自己对分布式...

编辑之路
21分钟前
0
0
3.x 在Unix系统上面启动守护进程

12.14 在Unix系统上面启动守护进程 问题 你想编写一个作为一个在Unix或类Unix系统上面运行的守护进程运行的程序。 解决方案 创建一个正确的守护进程需要一个精确的系统调用序列以及对于细节的...

dragon_tech
21分钟前
0
0
ES6中的class

class Point {constructor(x, y, z) {this.x = x;this.y = y;this.z = z;}toString() {return `${this.x},${this.y}`}get prop() {return `获取的是get${this.z...

chinahufei
24分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部