Mysql调优

一、mysql结构

开启性能检测

set profiling=1  #开启
show profiles  #获取全部的执行时间
show profile for query 2 #指定查询哪条sql
1
2
3

对于性能检测，mysql在慢慢抛弃profile，使用Performance schema（性能模式），开启需要在配置文件中进行设置。

instruments：生产者，用于采集MySQL 中各种各样的操作产生的事件信息，对应配置表中的配置项我们可以称为监控采集配置项，以下提及生产者均统称为instruments

consumers：消费者，对应的消费者表用于存储来自instruments采集的数据，对应配置表中的配置项我们可以称为消费存储配置项，以下提及消费者均统称为consumers

my.conf中添加
[mysqld]
performance_schema=ON
1
2
3

show variables like 'performance_schema';  #查看是否开启性能模式

#打开等待时间的采集器配置项开关
UPDATE setup_instruments SET ENABLED = 'YES',TIMED = 'YES' where name like 'wait%';
#打开等待时间的保存表配置开关
UPDATE comsumers SET ENABLE = 'YES' where name like '%wait%';
#查看当前正在执行的线程等待的时间
select * from events_waits_current\G
#查看当前连接
show processlist;

1
2
3
4
5
6
7
8
9
10
11

一、schema与数据类型的优化

1.1 数据类型优化

• 更小的更好(tinyint>int>bigint)
• 简单的更好 #在mysql中存取时间使用datetime比使用varchar更快；mysql中存取ip使用varchar占用空间，通常使用INET_ATON(‘192.168.29.2’)转成整形进行存储.转回来使用INET_NTOA
• 避免列为null，数据库的列要尽量避免设计成可为null的列。
  
  
  整型类型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT分为用4、8、16、32、64字节来存储。（尽量用最小的）整形长度和大小无关，长度只是数值未满足，则左边补0.
  
  
  字符串和字符类型：char、varchar、text、blob
  
  
  datetime和timestamp:
• datetime：占用8个字节、时间使用datetime，不适用varchar，varchar会损失空间。
• timestamp：占用4个字节，精确到秒，采用整型存储，依赖数据库设置的时区。
• date：占用3个字节，占用字节最少。
  
  

1.2 执行计划

id：select查询的序列号，id越大越先执行，id一样从上往下执行。
select_type：将最外层查询为PRIMARY,子查询为SUBQUERY
type：以何种方式扫描数据。
possible_keys：执行sql可能会用到的索引。
key：执行sql、实际用到的索引。
key_len：索引使用的字节数。
rows：根据表的统计信息和索引情况，大概估算出所需要的读取的行数。
extra：额外信息

type级别: