关闭

浅谈MYSQL常用优化策略

标签: mysql优化
324人阅读 评论(0) 收藏 举报
分类:

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;

相关资料:

explain的使用:http://www.cnblogs.com/you-yang/archive/2012/01/18/2325841.html

转载请注明出处:http://www.cnblogs.com/hongfei/archive/2012/10/20/2732516.html

0
0
查看评论

数据库性能优化策略

有数据表明:用户可以承受的最大等待时间为8秒。 之前曾见过某个产品的一个列表页,40秒左右才能加载出来,几乎没有进行任何优化措施。 没有索引,没有缓存机制,没有进行sql优化(sql语句很长,并且各种left join表关联)。 数据库优化策略有很多,设计初期,建立好的数据结构对于后期性能优化...
  • u013628152
  • u013628152
  • 2016-07-05 22:24
  • 2390

MySQL性能优化方案总结

MySQL进行优化, 效果: SQL和索引 > 数据库表结构 > 系统配置 > 硬件 ;但成本从低到高。
  • sinat_23080035
  • sinat_23080035
  • 2016-10-13 00:26
  • 4781

mysql数据库简单优化措施

优化sql的一般步骤 通过show status了解各种sql的执行频率 定位执行效率低的sql语句 通过explain分析效率低的sql 通过show profile分析sql 通过trace分析优化器如何选择执行计划 确定问题,采取措施优化 索引优化措施 mysql中使用索引的典型场景匹配全值,...
  • baidu_21483933
  • baidu_21483933
  • 2016-08-05 06:16
  • 308

浅谈linux性能调优之十五:常见网络参数整理

1.tcp连接保持管理: net.ipv4.tcp_keepalive_time = 7200 如果在该参数指定时间内某条连接处于空闲状态,则内核向远程主机发起探测 net.ipv4.tcp_keepalive_intvl = 75 内核向远程主机发送的保活探测的时间间隔 ...
  • u011359591
  • u011359591
  • 2013-07-23 08:18
  • 780

SQL高级优化之常用的优化策略-1(The Return Of The King)

# ########################################### # 索引相关 # ########################################### – 查询(或更新,删除,可以转换为查询)没有用到索引     这是...
  • GreatElite
  • GreatElite
  • 2014-06-10 22:54
  • 1433

web应用负载均衡策略

1.  名词解释 1.  正向代理与反向代理 简单说 我们内网访问facebook用的代理就叫正向代理 从美国访问我们内网需要的代理就叫反向代理   多台服务器处于一个内网,而我们要访问这些服务器,中间加一台 反向代理,根据各台服务器的负载,指定访问其中一台。...
  • flyhawk_xjtu
  • flyhawk_xjtu
  • 2016-03-02 15:18
  • 2130

Android加载大图的优化策略

当我们使用大的Bitmap图片时很容易出现OOM的现象,今天我们就来看下该怎么解决这个问题。 一般有两种方法: 1、压缩图片; 2、LruCache缓存; 当然这两种方式同时使用效果更好^^ 一、压缩图片 先介绍下图片质量(Bitmap.Config),一共有4种: ALPHA_8 只...
  • wdong_love_cl
  • wdong_love_cl
  • 2016-06-04 14:18
  • 4056

mysql优化的常用方法

最近,在使用Mysql的时候,遇到一些查询优化问题,自己查阅资料,并顺带整理了一下。 Mysql的优化,大体可以分为三部分:索引的优化,sql语句的优化,表的优化 1.索引的优化 只要列中含有NULL值,就最好不要在此例设置索引,复合索引如果有NULL值,此列在使用...
  • seudongnan
  • seudongnan
  • 2017-02-25 18:05
  • 1492

你不得不知道的 MySQL 优化原理

说起MySQL的查询优化,相信大家收藏了一堆奇淫技巧:不能使用SELECT *、不使用NULL字段、合理创建索引、为字段选择合适的数据类型….. 你是否真的理解这些优化技巧?是否理解其背后的工作原理?在实际场景下性能真有提升吗?我想未必。因而理解这些优化建议背后的原理就尤为重要,希望本文能让你重新审...
  • liyantianmin
  • liyantianmin
  • 2017-05-08 14:03
  • 710

LINUX上MYSQL优化三板斧

现在MySQL运行的大部分环境都是在Linux上的,如何在Linux操作系统上根据MySQL进行优化,我们这里给出一些通用简单的策略。这些方法都有助于改进MySQL的性能。  闲话少说,进入正题。   一、CPU 首先从CPU说起。  你仔细检查的话,有些服务器上...
  • jb19900111
  • jb19900111
  • 2014-01-13 18:42
  • 1571
    个人资料
    • 访问:6980次
    • 积分:217
    • 等级:
    • 排名:千里之外
    • 原创:14篇
    • 转载:8篇
    • 译文:0篇
    • 评论:0条
    文章分类