MySql调优

索引

• 重复性较强的字段， 不适合添加索引

性能监控

• show status like “handler_read%” 查看用到索引的次数
• show status like ‘last_query_coat’ 最后一次查询所耗费的成本
• set profiling = 1; 开启监控
  • show profiles （query n） 根据query id查看sql执行时间
  • show profile 查看sql各阶段的执行时间
  • show profile all 显示所有性能信息
  • show profile block io 显示块io操作的次数
  • show profile context switches 显示上下文切换次数，主动和被动
  • show profile cpu 显示用户cpu时间、系统cpu时间
  • show profile ipc 显示发送和接受的消息数量
  • show profile page faults 显示页错误数量
  • show profile source 显示源码中的函数名称和位置
  • show profile swaps 显示swap的次数
• show processlist;查看当前数据库有多少个连接

schema与数据类型的优化

• 数据类型的优化
  • 更小的通常更好
    • 应该尽量使用可以正确存储的最小数据类型。因为更快，占用更少的磁盘，内存和CPU缓存并且处理时需要的CPU周期更少
  • 简单就好
  • 尽量避免null
  • 实际细则
    • datetime和timestamp及date

范式和反范式

• 范式
  • 优点
    • 范式化的更新通常比反范式块
    • 当数据较好的范式化后，很少或者没有重复的数据
    • 范式化的数据比较小,可以放在内存中，操作比较快
  • 缺点
    • 通常需要进行关联
• 反范式
  • 优点
    • 数据都在一张表中，可以避免关联
    • 可以设计有效的索引
  • 缺点
    • 表格内冗余较多，删除数据时会造成表有些有用的信息丢失
• 主键：代理主键 自然主键
• 单表索引限制在5个以内
• 单索引字段不允许超过五个

查询优化

原因

• 网络
• CPU
• IO
• 上下文切换
• 系统调用
• 生成统计信息
• 锁等待时间

服务器参数设置

• datadir=/var/lib/mysql 数据文件存放的目录
• socket=/var/lib/mysql/mysql.sock mysql.socket表示server和client在同一台服务器，并且使用localhost进行连接，就会使用socket进行连接
• pid_file=/var/lib/mysql/mysql.pid 存储mysql的pid
• port=3306 mysql服务的端口号
• default_storage_engine=InnoDB mysql存储引擎
• skip-grant-tables 当忘记mysql的用户名密码的时候，可以在mysql配置文件中配置该参数，跳过权限表验证，不需要密码即可登录mysql

调优技巧

设计表时要考虑的问题

• 设计表时要注意：
  • 表字段避免null值出现，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替null。
  • 尽量使用INT而非BIGINT，如果非负则加上UNSIGNED（这样数值容量会扩大一倍），当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
  • 使用枚举或整数代替字符串类型
  • 尽量使用TIMESTAMP而非DATETIME
  • 单表不要有太多字段，建议在20以内
  • 用整型来存IP

在表中使用null值的影响

• （1）负向比较（例如：!=）会引发全表扫描；
• （2）如果允许空值，不等于 (!=) 的查询，不会将空值行 (row) 包含进来，此时的结果集往往是不符合预期的，此时往往要加上
  一个 or 条件，把空值 (is null) 结果包含进来；但这样会引发全表扫描，此时可以优化为 union 查询；
• （4）建表时加上默认 (default) 值，这样能避免空值的坑；

翻页查询

• 对于limit m, n分页查询，越往后面翻页即m越大的情况下SQL的耗时会越来越长，对于这种应该先取出主键id，然后通过主键id跟原表进行Join关联查询。

慢Sql查询与优化

• 启用慢查询日志
• 分析慢查询日志
  • 工具推荐：
    • 内置工具：mysqldumpslow

      mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log  # 按耗时排序，显示前10条

    • 高级工具：pt-query-digest（Percona Toolkit）

      pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.txt

• 使用EXPLAIN分析执行计划
• 索引优化
• 表结构与参数调优
• 监控和持续优化

为什么不建议使用UUID作为数据库主键

• 写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机IO
• 因为写入是乱序的,innodb不得不频繁的做页分裂操作,以便为新的行分配空间,页分裂导致移动大量的数据，一次插入最少需要修改三个页以上
• 由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片