mysql数据库优化 SQL语句优化

9870780

一、如何发现有问题的sql？
1、使用mysql慢查询日志对有效率问题的sql进行监控

//查看是否开启慢查询日志
show variables like 'slow_query_log'

set global slow_query_log =on;//开启慢查询

//设置保存慢查询日志路径
set global slow_query_log_file = '/var/lib/mysql/slow_log.log';
//记录下没有使用索引的query,
show variables like 'log_queries_not_using_indexes' //先查看是否开启
set global log_queries_not_using_indexes = on;

//设置sql执行时间
show variables like 'long_query_time';
set global long_query_time =1; //有点bug（修改了，在当前会话查询发现好像没有修改一样，不过退出后再登录mysql发现已经修改了）


以上设置了慢查询常用参数


接下来测试一下
运行一条sql语句

select sleep（5）；


vim /var/lib/mysql/slow-low.log


2、慢查询日志分析工具
mysqldumpslow：直接同mysql数据库一同安装
//查询下相关使用参数

mysqldump -h


//直接分些下慢查询日志
mysqldumpslow -t 3 /var/lib/mysql/mysql-slow.log


pt_query_digest

3、如歌通过慢查询日志发现有问题的sql?
(1)查询次数多且每次查询占用时间长的sql
(2)IO大的sql
(3)未命中索引的sql

二、使用explain查询SQL的执行计划
explain返回各列的含义
table:显示这一行的数据是关于哪张表的；
type：显示连接使用了何种类型；从最好到最差的连接类型为const、eq_reg、ref、range、index、all
possible_keys:显示可能应用在这种表的索引；如果为空，没有可能的索引；
key:实际使用的索引，如果为NULL，则没有使用索引；
key_len:使用索引的长度，在不损失精确性的情况下，长度越短越好；
ref:显示索引的哪一列被使用了，如果可能的话，是一个常数
rows:mysql认为必须检查的用来返回请求数据的行数
extra:using filesort（mysql需要进行格外的步骤来发现如何对返回的行排序）/using temporary（mysql创建一个临时表来存储结果）(看到这两个的时候语句就需要优化了)

三、索引优化

如何选择合适的列建立索引?
1、在where从句，group by从句，order by 从句，on从句中出现的列
2、索引字段越小越好
3、离散度大的列放到联合索引的前面
离散度：一列值重复度的大小

覆盖索引:一个索引包含了查询的所有列称为覆盖索引

索引的维护及优化---重复及冗余索引
重复索引：指相同的列一相同的顺序建立的同类型索引；例如一张表的主键又建立unique()唯一性索引就属于重复索引
冗余索引：指多个索引的前缀列相同，或是在联合索引中包含了主键索引

pt-duplicate-key-checker：工具可以查看数据库中多余的索引