MySQL调优

MySQL调优

根据高并发、高可用MySQL视频进行整理

建议关掉MySQL5.6、5.7自带的缓存

存储引擎

MyISAM

mysql5.5.5之前默认的存储引擎，插入数据快。空间利用率高。因为采用B+树结构和不支持事务。

查询效率要求非常高的可考虑

InnoDB

MySQL5.5.5之后默认存储引擎、最主流

支持事务、外键

支持崩溃修复、并发控制

Memory

数据在内存、速度快、不安全

临时表

Archive

数据压缩、空间利用率高

插入快

查询差、不支持索引（磁带）

归档

索引组织表

表按照主键顺序组织存放

InnoDB表均为索引组织表，数据被主键的索引组织起来

主键：非空、最先申明唯一索引

索引算法：B+树

主索引/聚簇索引：

索引和数据放在一起了

叶子节点直接存放数据

按照主键构造B+树

辅助索引

叶子节点不包含行数据

InnoDB逻辑存储结构

表空间（tablespace）

默认所有数据存在共享表空间

最好挡在独占表空间（idb文件）

段（segment）

数据段：叶子节点

索引段：非叶子节点

区（extent）

大小为1M，64个page（节点）

页（page）

InnoDB磁盘读写的最小逻辑单位，默认16kb

行（row）

Trx id

回滚指针

数据

变长列

• 长度不固定的数据类型：varchar、varbinary、blob、text
• 占用空间大于768byte的不变长类型：char
• 变长编码下的char

行溢出

解决数据表某一字段存的数据太大，字段过长，退化为二叉树

将大的数据转移到blob页存，热表尽量不要存长字段

禁止将图片等大文件序列化成二进制数据存在数据库

行记录

Dynamic格式

索引注意事项

联合索引

可代替最左侧字段的单独索引

口头禅：带头大哥不能死，中间兄弟不能丢

字符串的前缀索引

如果字符串过长，可考虑使用前缀索引节约空间，如邮箱

如果前缀区分度太小，可考虑：

• 倒序存储
• 新建hash字段

例：alter table user add index index2(email(6)) 以邮箱前六位建索引

或alter bable 表名 add key (列名(n))

字符串like

使用模糊查询（like %关键字% 或 like %关键字）会使索引失效

使用左模糊（like 关键字%）可以使用索引

InnoDB约束数据方法

主键/唯一值

主键：唯一且非空

唯一索引：唯一

唯一约束插入性能开销大，慎用

外键

对数据正确性实现约束，使用少

默认值/非空

Default/NOT NULL

如果innodb_strict_mode开启了，设置非空生效，未开启不生效

触发器

插入修改时校验数据

干扰业务，使用少

视图View

视图算法：

merge：

将视图sql合并到主查询sql，用一个sql查询，性能更高

temptable

将视图当作一张临时表处理

无法使用merge的SQL

• 聚集函数
• distinct
• group by
• having
• union、union all
• 子查询

提高查询效率

覆盖索引

查询语句从执行到返回均使用同一索引，不用回表，如查询联合索引的字段

查看建表语句：show create table 表名;

查看表结构：desc +表名;

查看索引：show index from 表名; cardinality 基数，代表索引可能性

查看执行计划：explain + 查询语句;

执行计划中extra代表是否索引覆盖，possible_keys 代表使用的索引

索引优化

analyze table 重新统计索引信息，修复基数估计错误

force index 强制使用某条索引

优化count

count(非索引字段)效率低，需要逐条扫描，判断是否为空

count(索引字段)、count(主键)、count(1)都需要提交给server层判断是否为null

最佳办法：使用count(*)

在MyISAM中，count(*)能直接返回数据库中记录的数据表行数，在InnoDB中数据库不记录，但MySQL专门优化了count(*)直接返回索引树中数据个数！

优化order by

排序缓存：sort_bufffer

优化排序查询时间，适当增大sort_buffer_size，使中间表（where查出来的数据）尽可能放在内存中运行

加了order by，前面的where语句相当于查的中间表，是不能走索引的！

排序阈值：max_length_for_sort_data 大于阈值时，只生成排序字段+主键的中间表，然后回表查出所有数据

最高效：使用索引覆盖

当筛选字段和排序字段全在一条索引中可用

select film_id,title from film order by title;
--title字段加了索引

优化RAND（）

rand()生成0-1的随机数

order by rand() 随机排序输出

下面语句存在性能问题：

select title,description from film order by rand() limit 1;

步骤：

1.创建一个临时表，临时表的字段为rand、title、description_
2.从表中取出一行，调用RAND（），将结果和数据放入临时表，以此类推
3.针对临时表，将rand字段+行位置（主键）放入sort buffer
4.对sort_buffer排序，取出第一个的行位置（主键ÿ