关闭

mysql 优化

标签: mysql
101人阅读 评论(0) 收藏 举报
分类:
1、优化一般步骤

1.1 show status 和应用特点了解各种SQL 的执行频率:

Myisam   和 Innodb 存储引擎都计数:
Com_select  执行select 操作次数,一次查询只累加1;
Com_insert  执行insert 操作次数,对于批量插入的insert操作,只累加一次;
Com_update     执行update 操作次数;
Com_delete     执行delete 操作次数; 

Innodb 存储引擎计数:
Innodb_rows_read        select查询返回的行数
Innodb_rows_inserted    执行Insert 操作插入的行数
Innodb_rows_updated        执行update 操作更新的行数
Innodb_rows_delete        执行delete 操作删除的行数

对于事务型的应用:
Com_commit , Com_rollback  可以了解事务提交和回滚的情况

以下参数便于我们了解数据库的基本情况:
Connections        试图连接mysql 服务器的次数
uptime            服务器工作时间
slow_queries    慢查询的次数

1.2  定位执行效率较低的SQL
1)可以通过慢查询日志定位
2)慢查询日志在查询结束以后才纪录,所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题,可以使用 show processlist 命令查看当前mysql 在进行的线程,包括线程状态、是否锁表等等,可以实时查看SQL 执行情况,同时对一些锁表操作进行优化。

1.3 通过explain 分析低效SQL 的执行计划
可以通过explain 或者desc 获取MySql 如何执行 select 语句的信息,包括select 语句执行过程表如何连接和连接的次序。explain 可以知道什么时候必须为表加入索引以得到一个使用索引来录找记录的更快的select。
explain 解析sql 返回:
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref      | rows    | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE       | s        | ALL   | NULL              | NULL | NULL     | NULL  |    4       | Using temporary; Using filesort |
|  1 | SIMPLE       | g       | ALL   | NULL              | NULL | NULL     | NULL  |   16       |                                   |
|  1 | SIMPLE       | c       | ALL    | NULL              | NULL | NULL     | NULL  |    5        |                                    |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

select_type     select 类型
table               输出结果集的表
type                表示表的连接类型
    当表中仅有一行是type的值为system 是最佳连接类型
    当select 操作中使用索引进行连接时type 的值为ref
    当select 的表连接没有使用索引时,经常会看到type 的值为ALL,表示对该表进行了全表扫描,这时需要考虑通过创建索引来提高表连接的效率。
possible_keys    表示查询时,可以使用的索引列
key                     索引长度
rows                   扫描范围
Extra                  执行情况的说明和描述

查看索引使用情况:
Handler_read_key  如果索引正在工作,这个值很高。这个值代表了一个行被索引值读的次数。很低的值表明增加索引得到的性能改善不高,因为索引并不经常使用。
Handler_read_rnd_next    这个值高意味着查询运行低效,并且应该建立索引。这个值的含义是在数据文件中读下一行的请求数。如果你正在进行大量的表扫描该值较高。通常说明表索引不正确或写入的查询没有利用索引。
查看值语法:
mysql>show status like 'Handler_read%';

使用索引
1、索引最左前缀原则。
2、like 后面是常量,并且 % 不在第一个字符。索引才能被用到。如  like '44%'
3、like 后面跟一个列的名字,也不会用到索引
4、对于大的文本搜索,使用全文索引,不使用 like  '%....%'

索引存在不使用索引
1、如果MySQL估计使用索引比全表扫描慢
2、如果使用MEMORY/HEAP 并且where条件中不使用“=” 进行索引列,那么不会使用到索引。heap 表只有在“=”的条件下才会使用索引
3、用or 分割开的条件,如果or 前的条件的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。
4、如果不是索引列的第一部分
5、like 以%开始
6、如果列类型是字符串,那么一定在where 条件中把字符常量使用引号引起来,否则即使这个列上有索引,也不会用到。如 列name 类型为字符串, where name='234' 会用到,where name=234  会全表扫描

简单优化方案
1、定期分析表
1)analyze table 用于分析和存储表的关键字分布。在分析期间,使用一个读取锁定对表进行锁定。 这对于 MyISAM, BDB和InnoDB 表有作用。 对于 MyISAM表,本语句与使用 myisamchk -a 相当。
2)、check table 检查一个或多个表是否有错误。CHECK TABLE 对 MyISAM 和 InnoDB 表有作用。对于MyISAM 表,关键字统计数据被更新。CHECK TABLE 也可以检查视图是否有错误,比如在视图定义中被引用的表已不存在。
3)、checksum table 报告一个表校验和。可以参考:http://dinglin.iteye.com/blog/1791922
2、使用optimize table。如果已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOB 或 TEXT 列的表)进行了很多更改,则应使用 OPTIMIZE TABLE。被删除的记录被保持在链接清单中,后续的 INSERT 操作会重新使用旧的记录位置。您可以使用 OPTIMIZE TABLE来重新利用未使用的空间,并整理数据文件的碎片。OPTIMIZE TABLE 只对 MyISAM, BDB 和 InnoDB 表起作用。


2、优化
大批量插入数据

Myisam 类型的表,可以通过以下方式快速的导入大量的数据:
alter table table_name disable keys;
#导入数据
alter table table_name enable keys;
这两个命令是用来打开或者关闭Myisam 表非唯一索引的更新。在导入大量的数据到一个非空的Myisam 表时,通过设置这两个命令,可以提高表导入的效率。对于导入大量数据到一个空的Myisam表,默认就是先导入数据再创建索引的,所以不用进行设置。

Innodb 的表参过上面方式不能提高导入数据的效率。可以通过下面方式:
1)Innodb 类型的表是按照主键的顺序保存的,导入数据按照主键的顺序排列,可以提高导入效率
2)关闭唯一性校验,导入结束后恢复唯一性校验。
    set unique_checks=0;  
    #导入数据
    set unique_checks=1;
3)关闭自动提交,导入结束后打开自动提交
    set autocommit=0;
    #导入数据
    set autocommit=1;

insert 语句
1、尽量使用多个值表的insert,这种方式将大大缩减客户端与数据库之间的连接,关闭等消耗。如 insert into test values(1,2),(3,4)....
2、插入多行,可以通过INSERT DELAYED 语句得到更高的速度。DELAYED 含义是让insert 语句马上执行,其实数据都被放在内存队列中,并没有真正写入磁盘。这与LOW_PRIORITY 刚好相反,在所有其他用户对表的读写完后才进行插入。
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] 
[INTO] tbl_name [(col_name,...)] 
VALUES ({expr | DEFAULT},...),(...),... 
[ ON DUPLICATE KEY UPDATE col_name=expr, ... ] 
3、将索引文件和数据文件分在不同的磁盘上存放
4、批量插入 可以增加bulk_insert_buffer_size 变量值方法提高速度。只对MyISAM 表使用
5)使用 LOAD DATA INFILE,比普通insert快20倍
6)根据应用情况使用 replace 语句代替 insert
7)根据应用情况使用 ignore 关键字忽略重复记录 (http://www.111cn.net/database/mysql/50135.htm)

group by
默认情况下  MySQL 对所有 group by c1,c2,c3.... 字段进行排序,和查询中指定order by c1,c2,c3... 类似。因此,如果显示包括一个相同的order by 子句对实际执行性能没有影响。如果用户想要避免排序结果的消耗,可以指定 order by null 禁止排序

order by
某些情况下MySQL 可以使用一个索引来满足 ORDER BY 子句,而不需要额外的排序。 where 条件和 order by 使用相同的索引, 并且 order by 的顺序和索引顺序相 同 ,并且 order by 的字段都是升序或者都是降序。
下列sql 可以使用到索引
select * from t1 order by key1,key2
select * from t1 where key1=1 order by key1 desc ,key2 desc
select * from t1 order by key1 desc,key2 desc
下列不使用索引
select * from t1 order by key1 desc ,key2 asc      ----order by 的字段混合 asc ,desc
select * from t1 where key2=1 order by key1      ---- 用于查询的行关键字与order by 中所使用的不同
SELECT * FROM t1 ORDER BY c1, c2;                    ------不对关键字order by 

子查询
使用 join,不会创建内存表


OR

有OR 查询的子句,如果要用到索引则OR之间的每一个条件都要用到索引。其实MySQL是执行了union 操作

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:37444次
    • 积分:1172
    • 等级:
    • 排名:千里之外
    • 原创:81篇
    • 转载:8篇
    • 译文:0篇
    • 评论:4条
    文章分类
    最新评论