MySQL optimization

一、Overview

I.在数据库层面优化

1. 表结构是否合理
   1.1 表字段是否的数据类型是否正确；
   1.2 根据应用系统的工作类型（例如OLAP/OLTP系统），每个表是否有合适的字段数量，比如执行频繁更新操作的应用通常使用更多的表，每个表更少的字段；分析大量数据的应用通常使用更少的表，每个表更多的字段。数据库范式设计和反范式设计。

2. 是否使用正确的索引，以使查询高效

3. 是否使用正确的存储引擎，利用存储引擎的长处和特性。比如事务型存储引擎InnoDB和非事务型引擎MyISAM

4. 每张表是否使用的正确的行/记录格式，这个选择依赖于存储引擎。压缩表使用更少的存储空间，读写数据使用更少的磁盘IO。压缩表可应用于InnoDB和只读性的MyISAM。

5. 应用是否使用正确的锁策略。例如使用共享锁，可以并发地执行数据库操作；使用排他锁，关键的操作获得最高执行优先级。InnoDB处理大量的锁问题，而不用认为参与。

6. 物理内存是否大小合适地分配给了缓存。频繁访问的数据量过大，导致物理内存超载和分页。

II.在硬件层面优化

1. 磁盘查询。目前的磁盘，平均查询时间在10ms以下，即一秒100次查询。这个查询时间很难对单表优化，优化方式是将数据分布到不止一个磁盘上。

2. 磁盘读写。目前的磁盘，传输速率在10-20MB/s。优化方式是并行地从多个磁盘读取数据。

3. CPU cycles

4. 内存尺寸范围

III.平衡可移植性和性能

语法/*! */，指定版本的sql执行里面的关键字

二、优化SQL语句

1. 优化insert
   1.1 插入多条记录时，使用多个values的单个insert语句
   INSERT INTO yourtable VALUES (1,2), (5,5), ...;
   1.2 使用LOAD DATA …从文件载入到数据库，这通常是insert语句执行速度的20倍
   1.3 利用列字段的默认值，只有在插入的值与默认值不同时，再明确地指定插入的值
   1.4 针对InnoDB的优化：插入前临时关闭autocommit、unique_checks（要保证插入的数据唯一索引字段值不重复）

2. 优化update：更新速度取决于更新的数据量和更新的索引的数量，索引字段的数据没有改变，索引也不会更新
   2.1 和select优化类似
   2.2 一次更新多条数据，比一次更新一次数据更快

3. 优化delete
   3.1 MyISAM表，删除单条数据记录和索引的数量线性相关；为了更快删除，增加key_buffer_size的键缓存
   3.2 MyISAM表，删除所有的数据记录的情况，TRUNCATE TABLE tbl_name比DELETE FROM tbl_name快

4. 优化select

以上内容翻译整理来自MySQL手册，不当之处，请多指教