mysql 性能报告_mysql性能分析

索引

普通索引

Index(xx) 或者 key(xx)

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

唯一索引

UNIQUE INDEX UniqIdx(xx)

索引列中的值必须是唯一的，但是允许为空值

主键索引

PRIMARY KEY(id)

是一种特殊的唯一索引，不允许有空值。

组合索引

INDEX MultiIdx(id, name, age)

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

全文索引

只有在MyISAM引擎上才能使用，略

空间索引

只有在MyISAM引擎上才能使用，略

覆盖索引

定义：如果索引包含满足查询的所有数据，就称为覆盖索引。

MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后进行回表操作，减少IO，提高了效率。

当使用到了覆盖索引，使用EXPLAIN可以在Extra一列中看到“Using index”

一般配合组合索引生效(查询的字段是组合索引中的字段)

MySQL性能医生：orzdba

安装

先装各种perl相关的依赖包和控件

$ yum install -y perl-Test-Simple.x86_64 perl-Time-HiRes perl-ExtUtils-CBuilder

$ yum install -y perl-ExtUtils-MakeMaker perl-DBD-MySQL perl-DBI perl-Module-Build

注意my.cnf的配置，否则连不上

[mysqld]

略略略

socket=/home/apps/mysql/mysql.sock

# 需要增加以下配置，socket设置为[mysqld]下的数值

[client]

default-character-set=utf8

socket=/home/apps/mysql/mysql.sock

[mysql]

default-character-set=utf8

socket=/home/apps/mysql/mysql.sock

添加环境变量(实现免密码快速登录mysql)export MYSQL_PWD='hll@123'

设置orzdba可执行权限即可chmod 755 orzdba

使用

$ ./orzdba -h

==========================================================================================

Info :

Created By zhuxu@taobao.com

Usage :

Command line options :

-h,--help Print Help Info.

-i,--interval Time(second) Interval.

-C,--count Times.

-t,--time Print The Current Time.

-nocolor Print NO Color.

-l,--load Print Load Info.

-c,--cpu Print Cpu Info.

-s,--swap Print Swap Info.

-d,--disk Print Disk Info.

-n,--net Print Net Info.

-P,--port Port number to use for mysql connection(default 3306).

-S,--socket Socket file to use for mysql connection.

-com Print MySQL Status(Com_select,Com_insert,Com_update,Com_delete).

-hit Print Innodb Hit%.

-innodb_rows Print Innodb Rows Status(Innodb_rows_inserted/updated/deleted/read).

-innodb_pages Print Innodb Buffer Pool Pages Status(Innodb_buffer_pool_pages_data/free/dirty/flushed)

-innodb_data Print Innodb Data Status(Innodb_data_reads/writes/read/written)

-innodb_log Print Innodb Log Status(Innodb_os_log_fsyncs/written)

-innodb_status Print Innodb Status from Command: 'Show Engine Innodb Status'

(history list/ log unflushed/uncheckpointed bytes/ read views/ queries inside/queued)

-T,--threads Print Threads Status(Threads_running,Threads_connected,Threads_created,Threads_cached).

-rt Print MySQL DB RT(us).

-B,--bytes Print Bytes received from/send to MySQL(Bytes_received,Bytes_sent).

-mysql Print MySQLInfo (include -t,-com,-hit,-T,-B).

-innodb Print InnodbInfo(include -t,-innodb_pages,-innodb_data,-innodb_log,-innodb_status)

-sys Print SysInfo (include -t,-l,-c,-s).

-lazy Print Info (include -t,-l,-c,-s,-com,-hit).

-L,--logfile Print to Logfile.

-logfile_by_day One day a logfile,the suffix of logfile is 'yyyy-mm-dd';

and is valid with -L.

Sample :

shell> nohup ./orzdba -lazy -d sda -C 5 -i 2 -L /tmp/orzdba.log > /dev/null 2>&1 &

==========================================================================================

# 输出到文件

$ nohup ./orzdba -lazy -d sda -C 5 -i 2 -L /tmp/orzdba.log > /dev/null 2>&1 &

慢查询日志配置

慢查询相关参数查看：

$ SHOW VARIABLES LIKE 'slow_query%';

$ SHOW VARIABLES LIKE 'long_query_time';

直接修改配置文件：

# 是否开启慢查询日志

slow_query_log=1

# 指定保存路径及文件名，默认为数据文件目录，

slow_query_log_file=/home/logs/mysql/mysql-slow.log

# 指定多少秒返回查询的结果为慢查询

long_query_time=1

# 记录所有没有使用到索引的查询语句

log_queries_not_using_indexes=1

# 记录那些由于查找了多于1000行而引发的慢查询

min_examined_row_limit=1000

# 记录那些慢的管理语句：optimize table，analyze table和alter table语句

log_slow_admin_statements=1

# 记录由Slave所产生的慢查询

log_slow_slave_statements=1

或命令行方式修改：

$ set global slow_query_log=1;

$ set global slow_query_log_file=/home/logs/mysql/mysql-slow.log;

$ set long_query_time=1;

$ set global log_queries_not_using_indexes=1;

$ set global min_examined_row_limit=1000;

$ set global log_slow_admin_statements=1;

$ set global log_slow_slave_statements=1;

常用分析命令

缓存

# 缓存相关参数查看

$ SHOW VARIABLES LIKE '%query_cache%';

# 查看缓存命中情况

$ show status like '%qcache%';

# 关闭/开启缓存(特定性能测试中为避免走到缓存可以先关闭)

# 动态修改：

$ set global query_cache_size=0;

$ set global query_cache_type=0;

# 或my.cnf配置修改：

query_cache_type=0

query_cache_size=0

# 直接指定用/不用缓存

Select sql_no_cache count(*) from pythonlearn.lianjia;

Select count(*) from pythonlearn.lianjia;

表状态

# 显示哪些进程正在运行，可以杀掉进程，如：kill 1825;

# 显示前100条

$ SHOW PROCESSLIST;

# 显示所有

$ SHOW FULL PROCESSLIST;

# 显示哪些表的打开的

$ SHOW OPEN TABLES;

# In_use列表示有多少线程正在使用某张表

# Name_locked表示表名是否被锁，这一般发生在Drop或Rename命令操作这张表时

事务

# 查询正在执行的事务

SELECT * FROM information_schema.INNODB_TRX

# 查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS

# 查看等待锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS

查看正在执行的sql

只能看到正在执行的select * from information_schema.PROCESSLISTwhere info is not null;

查看完整的

# 1. 开启日志

$ SET GLOBAL log_output = 'TABLE';

$ SET GLOBAL general_log = 'ON';

# 2. 查询日志

$ SELECT * from mysql.general_log ORDER BY event_time DESC;

$ 3. 清空表(这个表不允许使用delete，只能用truncate)

$ truncate table mysql.general_log;

# 4. 最后记得关闭

$ SET GLOBAL general_log = 'OFF';

其他

# 修改密码

# 查看密码复杂度

$ SHOW VARIABLES LIKE 'validate_password%';

# 修改密码验证等级

$ SET GLOBAL validate_password_policy=LOW;

# 密码最小长度限制修改(最小是4)

$ SET GLOBAL validate_password_length=6;

# 允许远程连接：

$ GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

FLUSH PRIVILEGES;

配置优化