Mysql优化

Mysql优化可从四个层面着手：

1. 硬件配置
   • RAM类型，高速硬盘，CPU处理能力；
   • 一般来说，数据库都是I/O密集型，CPU密集型需对如Mysql参数做调整；
   • Innodb优化，包括buffer， heap，temp，flush_log；
   • MYISAM优化：key_buffer的优化会对索引性能有很大提高；
2. MySQL配置
   • 适当控制数据库单表的数据量和数据文件大小；
   • query_cache_size：用来缓存sql语句及对应的查询结果。如果表没有变化，再次查询会直接返回结果，这样提高查询效率；但如果表变化频繁，则需要不断更新cache中的内容，并且这个时候锁粒度非常大，反而会成为性能瓶颈，所以很多情况下会关闭这个选项，将参数设置0；

   • tmp_table_size：

   • innodb_buffer_pool_size：物理内存的50%--75%；

   • innodb_flush_method：
   • innodb_thread_concurrency：增加系统的并发吞吐量
   • 数据库表空洞碎片查看及优化；
3. 查询优化
   • 使用explain分析查询功能是否恰当执行，参考：https://www.cnblogs.com/yycc/p/7338894.html
   • 使用慢日志查询分析（slow query log）
4. 表结构的设计

• • 字段类型的选取，尽量考虑使用整型类定义字段，比如在符合范围值的情况下使用tinyint、smallint；甚至可将IP定位为整型（4个字节，32位，IP的每一段刚好可以用8位二进制表示）；
  • 如果字段类型为blob/text的列查询较少，而其它字段列有频繁查询，可将这些列分开设计在不同的表里（同一张表缓存查询有较大影响）；
  • mysql默认开启查询缓存，但部分系统函数如 CURDATE()、NOW() 和 RAND()等不定值，是关闭缓存的，需要用变量代替这些函数；
  • 要常使用 EXPLAIN 分析查询语句或是表结构的性能瓶颈，包括索引主键如何被使用的；
  • 按需读取，避免使用select *；
  • 每个表带一个主键id，既可优化查询，也可在集群或分区中被使用；
  • 选项型的字段，如状态、部门、类型等，使用ENUM类型要比VARCHAR有效率（当表覆盖一个全量的数据时，可以使用 PROCEDURE ANALYSE() 从mysql本身获取表改进的proposal）；
  • 尽可能使用not null，因为Null会占用额外空间；(NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.)；
  • 使用Prepared Statements，可提高性能，也可提高安全性，避免“SQL注入式”攻击；
  • 把IP地址存成 UNSIGNED INT，只需要4个字节，而不是VARCHAR(15)，然后可以使用 INET_ATON() 和 INET_NTOA() 进行整型和字符串的相互转化；
  • 表字段设计尽量使用固定长度，会提升查询效率（容易计算出偏移量），但会占用更多空间；
  • 如果知道结果只有一条，加上limit 1，这样数据库引擎会在找到一条数据后停止搜索，而不是继续往后查；比如下面的查询检查数据是否存在，2）要优于1）：
  1. 1. SELECT * FROM table1 WHERE field1= 'XXX'；

     2. SELECT 1 FROM table1 WHERE field1= 'XXX' LIMIT 1；

  • 与where、group by 和sort by 查询询字段建立适当索引，以下情况不适合建索引：

• • • CLOB和BLOB类型的数据不宜建索引（可以使用数据库本身支持的全文索引）；
    • 小型表（如300条记录以下的表）不需建索引（如果频繁更新操作会造成额外负担）；
    • 查询中很少使用和参考的列不适合建索引；
    • 当修改性能远大于检索性能时不合适建索引
   建索引策略：
  • 主键索引类型尽量为int类型，由于索引越多，写入数据越慢，数据文件也越大，所以设计二级索引应按需添加，避免重复和冗余；
  • 创建唯一性索引可保证该列字段值的唯一性操作；
  • 逻辑上表空间和索引空间分开，但最好从物理上将索引数据和表数据分开存储；
  • 建索引时，会对表进行全表扫描，并进行排序，所以相应的初始化参数可能需要调整；同时因为会有锁表操作，所以不能在业务对该表进行操作时进行索引创建；
  • Mysql查询每次只能使用一个索引，所以如查询“select * from staff where name='XXX' and department='XXX' and age=23”,最好创建复合索引（name+department+age），该索引实际上是建立了（name+industry+age），（name+industry），（name）三个索引；如果name在查询中最频繁使用，则放在最左边；
  • Join查询，对join字段建索引，但必须是相同类型，相同字符编码；
  • 返回一页随机数据或一行数据，不能使用order by rand() limit 1，可以先得到total(count(*))，然后使用随机变量，或者使用join，取rand max(id)，例如SELECT *  FROM `table1` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table1`)) AS id) AS t2 WHERE t1.id >= t2.id ORDER BY t1.id ASC LIMIT 1；
  • order by rand()的问题所在：ORDER BY RAND() copies the whole table into a temporary table and adds a new column with a random value(fill data with rand value). Finally, it sorts the data by that column；
  • 索引值不会包含NULL值，所以如果某一复合索引列的值含有NULL值，则列索引会失效；
  • 短索引：如果一个列定义为char(255)，但前10个或20个char基本可以定义该列的唯一性，则可以将该列索引指定前缀长度为10或20，这样可以大大提高查询效率，而且可以节省磁盘空间和提高I/O效率；
  • 索引排序：假设表A建立索引index1(part1,part11)，index2(part2)，则下面的查询可以使用索引排序：

SELECT * FROM A ORDER BY part1,part11;
SELECT * FROM A WHERE part1 = XX ORDER BY part11;
SELECT * FROM A WHERE part1 > XX ORDER BY part1 ASC;
SELECT * FROM A WHERE part1 = XX AND part11 > YY ORDER BY part11;

但下面的查询无法使用索引排序，只能使用filesort排序：

SELECT * FROM A ORDER BY part1,part11, part2;  //多个索引并存
SELECT * FROM A ORDER BY part11, part1; //与创建索引顺序不一致
SELECT * FROM A ORDER BY part1 DESC, part11 ASC; //升降序不一致
SELECT * FROM A WHERE part1> XX ORDER BY part11;//part1使用范围，part2无法使用索引排序