mysql优化篇(2)
来源:互联网 发布:mac的dock隐藏快捷 编辑:程序博客网 时间:2024/05/16 20:31
提纲
首先进入mysql的命令行控制台
mysql有4种类型的语句 重点是select
现在windows下很多集成开发环境 找到mysql/bin 输入 mysql -h<主机名> -u<用户名> -p
首先show status ,看一下mysql当前运行状态
我们优化的重点是慢查询
show session status like 'Com_select';(这个语句可以看出当前控制台发生的select查询)
show
如何优化慢查询呢?
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.
问题是:如何在一个项目中,找到慢查询的select语句?
答案:mysql支持把慢查询语句记录到日志文件中。程序员需要修改php.ini的配置文件,默认情况下,慢查询记录是不开启的。
开启慢查询记录的步骤:
打开 my.ini ,找到 [mysqld] 在其下面添加 (linux版本是my.cnf)
long_query_time = 2
log-slow-queries = D:/mysql/logs/slow.log#设置把日志写在那里,可以为空,系统会给一个缺省的文件
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 WHEREid=12345
会产生如下信息:
select_type:表示查询的类型。
table:输出结果集的表
type:表示表的连接类型(system和const为佳)
possible_keys:表示查询时,可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
rows:扫描的行数
Extra:执行情况的描述和说明
注意:要尽量避免让type的结果为all,extra的结果为:usingfilesort
4.确定问题并采取相应的优化措施
- 常用的优化措施是添加索引。添加索引,我们不用加内存,不用改程序,不用调sql,只要执行个正确的’createindex’,查询速度就可能提高百倍千倍。但是天下没有免费的午餐,查询速度的提高是以插入、更新、删除的速度为代价的,这些写操作,增加了大量的I/O。
例如:给字段id添加索引:
ALTER TABLE order_copy ADD PRIMARYKEY(id)
给1千万的数据添加primary key 需要耗时:428秒(7分钟)
EXPLAIN SELECT * FROM order_copy WHEREid=12345
正是因为给id添加了索引,才使得rows的结果为1
但是索引并不是可以随便添加的,以下几种情况需牢记在心:
- 较频繁的作为查询条件字段应该创建索引
- 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
- 更新非常频繁的字段不适合创建索引
- 不会出现在WHERE子句中字段不该创建索引
索引的类型:
- PRIMARY索引
=> 在主键上自动创建 - INDEX索引
=> 就是普通索引 - UNIQUE索引
=> 相当于INDEX + Unique - FULLTEXT
=> 只在MYISAM 存储引擎支持,目的是全文索引,在内容系统中用的多,在全英文网站用多(英文词独立). 中文数据不常用,意义不大 国内全文索引通常 使用 sphinx来完成.
索引的使用
- 建立索引 create [UNIQUE|FULLTEXT]
indexindex_name on tbl_name (col_name [(length)] [ASC| DESC] , …..);
alter table table_name ADD INDEX [index_name](index_col_name,...)
添加UNIQUE(唯一索引)
mysql>ALTER TABLE `table_name` ADD UNIQUE(`column`)
添加INDEX(普通索引)
mysql>ALTER TABLE `table_name` ADD INDEX index_name( `column` )
添加FULLTEXT
mysql>ALTER TABLE `table_name` ADD FULLTEXT (`column`)
添加多列索引
mysql>ALTER TABLE `table_name` ADD INDEX index_name( `column1`, `column2`, `column3` )
- 删除索引 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优化篇(2)
- mysql 优化(2)索引优化策略
- mysql优化(2)索引优化 配置优化
- mysql优化(2)索引优化 配置优化
- mysql优化(2)索引优化 配置优化
- mysql优化篇(1)
- mysql优化篇(3)
- mysql优化篇(一)
- mysql优化篇(二)
- mysql优化篇(三)
- 【mysql 性能优化篇】优化MySQL服务器
- mysql优化篇(四)-表结构的优化
- MySQL (10)MySQL优化
- mysql 优化之索引篇(一)
- mysql索引优化篇(一)
- mysql索引优化篇(二)
- mysql索引优化篇(三)
- MySQL优化—工欲善其事,必先利其器(2)
- 如何将一篇图文并茂的文章截取指定…
- DIV+CSS篇:怎样快速切图!
- Linux 系统挂载数据盘
- Gh0st 学习笔记之 zlib 使用
- mysql优化篇(1)
- mysql优化篇(2)
- mysql优化篇(3)
- php 判断该时间戳是否在当日
- 【官方】Android支持不同的密度或分辨率(Supporting Different Densities)
- php路径问题的研究!
- 12个有趣的C语言问答
- 电子行业的智能化
- boost简介
- 基数排序!一种美妙实用高效的排序算法!