一、MySQL 性能优化的维度

硬件： CPU、内存、存储、网络设备等

系统配置： 服务器系统、数据库服务参数等

数据库表结构： 高可用、分库分表、读写分离、存储引擎、表设计等

SQL 及 索引： SQL 语句优化、索引使用等

二、查询优化

1. 慢查询

• 慢查询开启日志
• 慢查询分析

2. Explain

帮助开发人员分析 SQL 问题

• id：查询序列号
• select_type：表示查询的类型
  • simple：简单的查询
  • primary：最外面的select
  • union：
  • dependent union：
  • union result ：union 的结果
  • subquery：子查询的第一个select
  • dependent subquery：子查询的第一个select，取决于外面的查询
  • derived：导出表的 select
• table：查询数据是关于哪张表
• type：区间索引，显示连接用了何种类型，最好到最差的连接类型（至少达到range，最后达到 ref）
  • system
  • const：数据表最多只有一个匹配行，因为只匹配一行数据，所以很快
  • eq_ref：使用=比较带索引的列
  • ref：可用于 = 或 < 或 > 操作符的带索引的列
  • ref_or_null
  • index_merge
  • unique_subquery
  • index_subquery
  • range：只检索给定范围的行，使用一个索引来选择行
  • index：该连接类型与 all 相同，除了只有索引树被扫描，通常比 all 快，因为索引文件通常比数据文件少
  • all：对于每个来自于先前的表的行组合，进行完整的表扫描
• possible_keys：指出 MySQL 能使用哪个索引在该表中找到行，如果是空的，没有想关的索引，这时要提高性能，可通过检验 where 子句，看是否引用某些字段，或者检查字段是否适合索引
• key：实际使用到的key
• key_len：最长的索引宽度
• ref：显示使用哪个列或常数与key一起从表中选择行
• rows：显示MySQL认为它执行查询时必须检查的函数
• Extra：执行状态说明，包含MySQL解决查询的详细信息

三、索引优化

1. 索引的类型

• 主键索引 Primary Key
• 唯一索引 Unique
• 普通索引 Index
• 组合索引（联合索引） Index
• 全文索引 FullText

2. 索引的存储结构

• BTree 索引
• 哈希索引
• full-text 全文索引

3. 索引的使用场景

索引对性能造成的损失

• 索引会增加写操作的成本
• 太多的索引会增加查询优化器的选择时间

使用索引的场景

• 主键自动创建唯一索引
• 经常作为查询条件在 where 或者 order by 语句中出现
• 作为排序的列要建立索引
• 查询占用与其他表关联的字段，外键关系建立索引
• 高并发条件下倾向建立组合索引
• 用于聚合函数的列可以建立索引

不使用索引的场景

• 有大量重复的列不单独建立索引
• 表记录太少不要建立索引，因为没有太大的作用
• 不会作为查询的列不要建立索引

四、存储优化

1. InnoDB 存储引擎

特定：

• InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全
• 提供了对数据事务 ACID（原子性，一致性，隔离性，持久性）的支持，实现 SQL 标准的四种隔离级别
• 设计目标就是处理最大容量的数据库系统
• 执行 select count(*) from table 语句是需要扫描全表
• InnoDB 引擎是行锁，粒度更小，所以写操作不会锁定全表，在并发较高时，使用 InnoDB 会提升效率，即存在大量 update/insert 操作时，效率较高
• InnoDB 清空数据量大的表时，是非常缓慢的，这是因为 InnoDB 必须处理表中的每一行

使用场景：

• 经常 update/insert 的表，使用处理多并发的写请求
• 支持事务，必选 InnoDB
• 可以从灾难中恢复（日志 + 事务回滚）
• 外键约束，列属性 AUTO_INCREMENT 支持

2. MyISAM 存储引擎

特点：

• 不支持事务，不支持外键，select 为主的应用可以使用该引擎
• 每个 MyISAM 在存储成 3 个文件，扩展名分别是
  • frm：存储的定义
  • MYD：存储数据
  • MYI：存储索引
• 不同 MyISAM 表的索引文件和数据文件可以放置在不同的路径下

使用场景：

• 经常 select/insert 的表，插入不频繁，查询非常频繁
• 不支持事务
• 做很多 count 的计算

3. InnoDB 和 MyISAM 的区别

• InnoDB 是事务安全的，MyISAM 是非事务安全的
• InnoDB 锁的粒度是行级，MyISAM 锁的粒度是表级
• InnoDB 支持外键，MyISAM 不支持外键
• MyISAM 相对简单，所以效率上要优于 InnoDB，小型应用可以考虑使用 MyISAM
• InnoDB 表比 MyISAM 表更安全

4. 存储优化

• 禁用索引
• 禁用唯一性检查
• 禁用外键检查
• 批量插入数据
• 禁止自动提交

五、数据库结构优化

1. 表结构优化

• 尽量将表字段定义为 NOT NULL 约束，NULL 值会使索引以及索引的统计信息表变得很复杂
• 对于只包含特定类型的字段，可以使用 enum、set 等数据类型
• 数值型字段的比较比字符串的比较效率高的多，字段类型尽量使用最小，最简单的数据类型
• 尽量使用小的数据类型，非负加上 UNSIGNED
• varchar 的长度只分配真正需要的空间
• 尽量使用 TIMESTAMP 而非 DATETIME，
• 单表不要有太多字段，20 以内
• 合理加入冗余字段可以提高查询速度

2. 表拆分

• 垂直拆分
• 水平拆分

3. 表分区

• range 分区
• list 预定义列表分区
• hash 分区
• key 键值分区

4. 读写分离

读写分离操作

主从复制功能

5. 数据库集群

六、硬件优化

• 内存
• 磁盘
• CPU
• 网络

七、缓存优化

1. 查询缓存

2. 全局缓存

• key_buffer_size
• innodb_buffer_pool_size
• innodb_additional_mem_pool_size
• innodb_log_buffer_size

3. 局部缓存

• read_buffer_size
• sort_buffer_size
• read_rmd_buffer_size
• tmp_table_size
• record_buffer

4. 其它缓存

• table_cache
• thread_cache_size

八、服务器优化

1. MySQL 参数

• back_log
• wait_timeout
• max_connections
• max_user_connections
• thread_concurrency
• skip-name-resolve
• default-storage-engine

2. Linux 系统优化

• 避免使用 Swap交换分区，因为交换时是从硬盘读取的，速度很慢。
• 将操作系统和数据分区分开，不仅仅是逻辑上，还包括物理上，因为操作系统的读写会影响数据库的性能。
• 把 MySQL临时空间和复制日志与数据放到不同的分区，数据库后台从磁盘进行读写时会影响数据库的性能。
• 避免使用软件磁盘阵列。
• 在 Linux中设置swappiness的值为0，因为在数据库服务器中不需要缓存文件。
• 使用 noatime 和 nodirtime 挂载文件系统，因为不需要对数据库文件修改时间。
• 使用 XFS 文件系统，一种比ext3更快、更小的文件系统。
• 调整 XFS 文件系统日志和缓冲变量 – 为了最高性能标准。
• 使用 64位的操作系统，这会支持更大的内存。
• 删除服务器上未使用的安装包和守护进程，节省系统的资源占用。
• 把使用 MySQL的host和你的MySQL host放到一个hosts文件中。