mysql 索引优化 sem,MySQL性能诊断方法论及优化方向

一、性能诊断方法论

1、性能问题

2、解决方案

2.1 测量任务所花费的时间

2.2 对结果进行统计和排序，将重要的任务排到前面

3、对应用程序进行性能剖析

3.1 影响因素

4、剖析MySQL查询

4.1 剖析服务器负载

4.2 剖析单条查询

4.3 使用性能剖析

5、诊断间歇性问题

5.1 单条查询问题还是服务器问题

5.2 捕获诊断数据

5.2.1 一个可靠且实时的触发器，就是什么时候问题会出现

5.2.2 收集什么样的数据

5.2.3 解释结果数据

6、其他剖析工具

二、优化方向

1. 服务器及OS优化

1.1 文件系统

1.2 内核参数

1.3 硬件提升

2. MySQL参数调整

2.1 内存参数

2.2 事务日志相关

2.3 IO参数

2.4 其他参数

3. SQL优化

4. 表架构优化

4.1 选择优化的数据类型

4.1.1 浮点类型

4.1.2 VARCHAR和CHAR类型

4.1.3 BLOB和TEXT类型

4.1.4 使用枚举代替字符串类型

4.2 表设计原则

5. 索引优化

如何确认服务器是否达到了性能最佳状态

找出某条语句为什么执行不够快

卡死等某些间歇性疑难故障

周期性变化还是偶尔

检查mysql的io和cpu利用比例

执行时间

服务器需要做大量的工作，从而导致大量消耗CPU

可以参考 Percona Toolkit中的pt-collect

等待时间

在等待某些资源被释放

GDB的堆栈跟踪

pt-pmp剖析器

外部资源，比如调用了外部的Web服务或搜索引擎

应用需要处理大量的数据，比如分析一个超大的XML文件

在循环中执行昂贵操作，比如滥用正则

使用了低效算法，比如暴力搜索算法

New Relic的软件即服务(software-as-a-service)产品

捕获查询到日志文件中

分析查询日志

使用 show profile测量耗费时间和查询执行状态变更相关数据

使用慢查询日志

使用Performance Schema

使用 show status

使用show global status

使用show processlist

使用查询日志

可以使用工具 Percona Toolkit的pt-stalk

系统状态

CPU利用率

磁盘使用率和可用空间

ps的输出采样

内存利用率

检查问题是否真的发生了，避免误报

是否有非常明显的跳跃性变化

将Percona Toolkit中pt-mysql-summary和pt-summary的输出结果打包，用pt-sift快速检查收集到的样本数据

什么导致资源性能低下

资源过度使用，余量不足以正常工作

资源没有被正确配置

资源已经损坏或者失灵

使用USER_STATISTICS表

可以查找使用得最多或者使用得最少的表和索引

可以查找出从未使用的索引，可以考虑删除之

可以看看复制用户的CONNECTED_TIME和BUSY_TIME，以确认复制是否会很难跟上主库的进度

使用strace

调查系统调用情况

文件数限制: /etc/security/limit.conf

soft nofile 65535

hard nofile 65535

磁盘调度策略: /sys/block/devname/queue/scheduler

echo deadline > /sys/block/devname/queue/schedulerXFS

echo '/dev/sda1/ext4 native,nodiratime,data=writeback 1 1' >> /etc/fstab1.2 内核参数可以参考Oracle的内核参数的调整修改/etc/sysctl.conf

fs.aio-max-nr = fs.file-max = 6815744kernel.shmall = 2097152#kernel.shmmax = 4398046511104  //一般设置为系统内存75%单位是字节kernel.shmmni = 4096kernel.sem = 250 32000 100 128net.ipv4.ip_local_port_range = 9000 65500net.core.rmem_default = 262144net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 10485861.3 硬件提升CPU

非计算密集型 - 多核

计算密集型 - 高频

SSD & PCIE卡

万兆网卡

线程独享

sort_buffer_size

join_buffer_size

read_buffer_size

read_rnd_buffer_size

线程共享

innodb_buffer_pool_size

key_buffer_size

tmp_table_size

max_head_table_size

innodb_log_file_size

innodb_log_files_in_group

innodb_log_buffer_size

innodb_flush_log_at_trx_commit

innodb_flush_method = O_DIRECT

innodb_file_per_table = 1

innodb_doublewrite = 1

delay_key_write

innodb_read_io_threads

innodb_read_io_threads

innodb_io_capacity

innodb_flush_neighbors

sync_binlog

expire_logs_days

max_allowed_packet

skip_name_resolve

skip_slave_start

max_connections

SQL优化内容较多，单独一章

MySQL自身实现，运算较慢

DOUBLE、FLOAT

CPU直接支持，运算较快

提升效率方法

在数据量较大时，使用BIGINT代替DECIMAL。乘以相应倍数即可。

字符串列的最大长度比平均长度大很多

列的更新很少，所以碎片不是问题

使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储

存储很短的字符串

经常变更

ENUM和ENUM关联会很快

避免使用数字作为枚举常量，双重性容易导致混乱

字符串列表是固定的，添加或删除字符串必须使用ALTER TABLE

更小的通常更好

简单就好

尽量避免NULL

查询越频繁的表应该设计越简单

查询越频繁的关联表应该多考虑冗余

复合索引：最常用的放在最前面，无where顺序无关索引是不可更改的，想更改必须删除重新建