MySQL 性能优化

性能优化：

性能优化：

性能分析：

-- 查询执行频次
SHOW GLOBAL STATUS LIKE 'COM_______'

慢查询日志：

默认关闭

• 慢查询日志会记录MySQL中查询时间 超过指定时间的sql语句

-- 查询慢查询日志的开启情况
SHOW VARIABLES LIKE 'slow_query%'
-- 查询慢查询的时间参数
SHOW VARIABLES LIKE 'long_query_time%'

• 开启慢查询日志
  修改MySQL的配置文件

# Linux环境下 my.cnf
slow_query_log= ON
slow_query_log_file= /path/xxx.log
long_query_time= 1

# windows环境下 my.ini
slow_query_log= on
slow_query_log_file= D:\path\xxx.log
long_query_time= 1

Profile：

sql耗时分析

-- 查看数据库是否支持profile
SELECT @@have_profiling
-- 查看是否开启
SELECT @@profiling

-- 查看最近执行语句的耗时
SHOW PROFILES
-- 查看指定query_id的sql语句在各个步骤的耗时
SHOW PROFILE FOR QUERY 106

Explain

在任意sql语句之前 加EXPLAIN||DESC 可以查看该sql的执行计划
可以看到在执行中是否用到了索引 表之间是如何连接的 连接顺序之类的

• id：越大越先执行
• select_type：表示查询的类型（主查询、子查询、或者是连接之类的） 意义不大
• type：表示连接类型 性能由好到差是
  • NULL（不访问表或者索引）
  • system（系统表）
  • const（主键）
  • eq_ref（唯一性索引）
  • ref（非唯一性索引）
  • range（索引范围扫描） 常见于 '<', '<=', '>', '>=', 'between' 等操作符
  • index（全索引扫描）
  • all（全表扫描）
• possible_keys：可能用到的索引
• key：实际用到的索引
• key_len：索引中使用的字节长度 （是最大可能长度 并非实际长度 越短越好）

优化方式：

索引使用：

• 注意最左前缀原则
• 不要在索引字段上做运算操作（索引失效）
• 字符串不加单引号（索引失效）
• 尾部模糊查询 索引不失效 头部模糊查询 索引失效
• or 连接的前后条件字段都要是索引字段才行 否则失效
• 数据分布：MySQL评估使用索引比全表扫描慢 则不会使用索引 （大部分数据满足索引条件时）
  如：性别、状态这种大部分数据都相同的情况下 就不用建立索引
  范围查询也会导致这个问题
• 尽量使用覆盖索引 尽量少用select *
• 前缀索引：大字段想要建立索引可以考虑前缀索引
• 索引越多 维护成本越高

SQL提示：

• use index：使用指定索引（只能算是建议）

  select * from table_name use index (PRIMARY) where table_id = 'xxx'
  

• ignore index：不使用指定索引

  select * from table_name ignore index (PRIMARY) where table_id = 'xxx'
  

• force index：必须使用这个索引

  select * from table_name force index (PRIMARY) where table_id = 'xxx'
  

语句优化：

• 插入优化：
  • 批量插入 （五百到一千条比较合适）
  • 手动提交事务
  • 主键顺序插入 （乱序插入会导致页分裂现象）
  • 大数据导入 不建议使用insert语句 建议使用load指令
• 主键优化：
  • 尽量使用顺序插入
  • 尽量降低主键长度
  • 尽量不要使用uuid等自然主键 如身份证号
• 排序优化：order by
  • filesort：查询到数据之后 在排序缓冲区sort buffer中完成排序 （不可避免的file sort 可以适当增大排序缓冲区）
  • index：使用索引直接返回排序 推荐使用索引排序（索引创建的时候可以指定排序规则）
• 分组优化：group by
  • 同样推荐建立复合索引提高效率
• 分页优化：limit
  • 直接使用limit查询数据量大了之后会很慢
    可以在排序字段建立索引 根据排序字段查出结果行的id 再通过id去查询行数据
• count优化：
  • innodb中count会从引擎中一行行的读取数据 然后累计计数（MyISAM引擎会存储总数 所以很快）
  • count函数的本质是取数据 数据不为NULL 就+1
    所以如果是count(name)然后name中有NULL值 那么count(name) != count(*)
  • 常见的使用count效率：count(*)>count(1)>count(主键)=count(字段 not null)>count(字段)
    innodb对count(*)有优化 直接计数不取数据 所以是最快的
• 更新优化：
  • 尽量使用索引字段进行更新 否则会上表锁 （且索引不能失效）
  • 以索引字段为条件的更新 只会上行锁 所以推荐使用索引字段更新

其他优化：

• 用查询缓存优化查询：
  查询缓存：可以提高相同查询的效率 会将相同查询的结果会被放到缓存中
  但是根据sql的区别 可能会导致缓存失效 如：使用CURDATE函数 SQL函数都不会开启查询缓存

• 当只要一行数据时使用 LIMIT 1

• 常用的查询字段 加上索引

• 需要Join关联的两个字段 建立索引
  如表A中字段a要经常与表B中字段b join连接 那么可以为两个字段添加索引
  两个有索引的字段join MySQL会在Join进行优化 两个字段的数据类型必须相同

• 尽量使用覆盖索引 避免 SELECT * 最好是查询的数据就在索引中 直接通过索引就能找到数据 不用回表查询

• 永远为每张表设置一个ID
  我们应该为每张表都设置一个ID做为主键 最好是INT型（推荐使用UNSIGNED） 然后让他自增
  使用字符集类型来当主键会使得性能下降

• 从 PROCEDURE ANALYSE() 取得建议
  PROCEDURE ANALYSE会根据你表中的数据和你表的结构 提出一些优化建议

• 尽可能不要使用NULL值
  因为在MySQL中null值也是占用空间的 因为他需要表明他是不是null值 长度是null
  而空值就是空值 长度就是零
  除了占用内存之外 null值的使用也会使程序作出不必要得判断 需要单独判断 is not null

• 垂直分割
  表结构垂直分割 减少表中字段 比如用户表中可以将地址、身份证等 这些不是每次都需要的数据分割到另一张表
  再比如用户表一般会有最后登录时间 这个最后登录时间也可以单独放一张表来处理 这样就可以减少用户表的操作
  当然被分割出去的表 前提是你查询的时候不经常使用 不然你还总要去join 那样的话不分割可能还更好一点

• 选择正确的存储引擎

  • MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。
  • InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。

• 不要使用 ORDER BY RAND()
  MySQL中RAND()函数很耗CPU时间 尽量不要使用 在Java中对数据进行处理也行

性能扩展：

• 使用缓存
• 读写分离 主从复制
• 分库分表
  • 垂直分表：按照业务拆分
  • 水平分表：按照指定规则拆分
• 消息队列 削峰