一、分析Mysql慢的原因
1、CPU计算复杂,语句问题
2、频繁的I/O
二、准确定位
1、检查慢查询
(1)先可以查看日志和慢查询
查看所有日志状态: show variables like '%quer%';
查看慢查询状态:show variables like 'show%'
(2)如何发现有问题的sql
long_Qury_time
注意:查看是否开启慢查询
eg:show variables like '%long_Qury_time%'
开启
set glaobal slow_Qury_log=1
默认慢查询是10秒,如果修改之后需要重新打开一个新的对话
(3)explain返回各列的含义
3.1 MySql内部函数explain(查询sql的执行计划)
使用方法以及返回各列的含义explain返回各列的含义
table:显示这一行的数据是关于哪张表的
type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index 和ALL
possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。
key:实际使用的索引。如果为NULL,则没有使用索引。
keyjen:使用的索引的长度。在不损失精确性的情况下,长度越短越好ref:显示索引的哪一列被使用了,如果可能的话,是一个常数
rows: MYSQL认为必须检查的用来返回请求数据的行数
2、批量数据脚本
select now() from dual
3、show profile
show profle cpu.block io for Query
分析问题:
converting HEAP to MYISAM 查询的结果比较大,内存不够用,搬去磁盘
creating tmp table 创建临时表
copying to tmp table disk 把内存中的临时表拷贝到磁盘中
4、全局查询
set global general_log=1
set global log_output='TABLE'
select * from myQsl.general_log
5、explain sql语句
三、优化方案
1、sql优化
(1)可以在where 实现的就不要放在having中
(2)根据需要查询所需的字段,少用或不用*
(3)在数据量比较大的时候可以使用limmit分页
(4)大部分连接的效率大于子查询的效率
(5)多表连接时,使用小表驱动大表
eg:from 小表 join 大表
(6)常见的查询可以使用缓存
还有实例如下:
应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num is null
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from t where num=0
应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num=10 or num=20
可以这样查询:
select id from t where num=10
union all
select id from t where num=20
in 和 not in 也要慎用,否则会导致全表扫描,如:
select id from t where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3
下面的查询也将导致全表扫描:
select id from t where name like '%abc%'
应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where num/2=100
应改为:
select id from t where num=100*2
应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where substring(name,1,3)='abc'--name以abc开头的id
应改为:
select id from t where name like 'abc%'
2、表的优化
(1)考虑将表分区
(2)表的字段不为空
(3)将大表按时间或者一些标志划分为一些小表
3、索引优化
只要列中含有NULL值,就最好不要在此例设置索引,复合索引如果有NULL值,此列在使用时也不会使用索引
尽量使用短索引,如果可以,应该制定一个前缀长度
对于经常在where子句使用的列,最好设置索引,这样会加快查找速度
对于有多个列where或者order by子句的,应该建立复合索引
对于like语句,以%或者‘-’开头的不会使用索引,以%结尾会使用索引尽
量不要在列上进行运算(函数操作和表达式操作)
尽量不要使用not in和<>操作
4、数据库结构优化
(1)选择合适的数据结构类型
eg:使用可以存储数据的最小的数据类型
使用简单的数据类型,如:int由于varcahr
尽可能使用not null定义字段
(2)表的范式化
5、除此之外,如果条件 允许的话,可以提高硬件的配置以及服务器的配置,可以提高效率