mysql性能优知乎_MySQL性能优化

本文主要对MySQL的性能优化进行研究。 参考文章 MySQL 对于千万级的大表要怎么优化

MySQL的性能优化可以从很多方面进行操作，下面是我认为比较合理的方案和优化顺序。优化SQL和索引

关于MySQL索引的介绍，可以参考之前的文章MySQL Index 。只有了解所使用索引的实现原理，才能更好的优化索引的使用。

从技术和业务两个层面考虑来优化SQL和索引。这里只说技术方面。

-索引优化

数据量不多时不要建立索引；

InnoDB中最好定义业务无关的自增字段(INT UNASSIGNED AUTO_INCREMENT)作为主键，从而作为主索引；

使用小的简单数据类型作索引，使得索引结点中存放的关键字越多越好；

查询频繁的列，在where，group by，order by，on从句中出现的列(where中，>=，between，in，like str+%出现的列)；

离散度大的列建立索引且放在联合索引前面(离散度：不同的列值数量越大，分散度越大，离散程度越高)；

联合索引注意顺序和查询条件保持一致，同时删除不必要的单列索引；

-SQL编写优化

常使用explain来查看sql的执行计划；

使用PROCEDURE ANALYSE() 来根据数据来获取建议，但注意数据不多导致建议不好；

尽可能使用NOT NULL来定义字段，NULL会占用额外空间并使得比较复杂，null列；

尽量使用limit来避免全表扫描；

避免select *，将要查询的column name列出来，防止schema变化造成的问题；

使用join来代替子查询，join的column应该建立索引并且类型相同；

拆分大的sql，尤其是delete和insert，因为他们会锁表；

OR改写成IN，OR的时间开销为n，而IN的开销为logn；

对于连续数值，使用BETWEEN不用IN：WHERE num BETWEEN 1 AND 5；

不做列操作，会导致全表扫描，函数，计算表达式等，尽量将计算移到=的右侧；

避免在WHERE子句中使用!=或<>或not in或||或like '%str'操作符，否则引擎放弃使用索引而进行全表扫描；

避免在WHERE子句中对字段进行NULL值判断，否则引擎放弃使用索引而进行全表扫描；

千万不要使用ORDER BY RAND()，用来打乱返回顺序，每行求rand并排序返回；

对选项column使用ENUM来替代VARCHAR;

固定长度的表速度会更快，避免使用VARCHAR/TEXT/BLOB，若真需要，使用垂直分割，将表分为定长表和非定长表；

垂直拆分大的表，分为常用字段和非常用字段；

MyISAM存储引擎的读效果好，写效果差。因为该引擎使用非聚集索引，即数据和索引分开存放。这样数据存放是连续的，而且索引叶结点的data域为数据的物理地址，所以，读比较块。但MyISAM锁是表锁，只有读读之间是并发的，写写之间和读写之间是串行的，所以写起来慢。MyISAM不要使用查询时间太长的sql，可能会导致写饿死，尽量去拆分查询效率低的sql。

InnoDB一般都是行锁，即sql用到索引的时候，行锁是加在索引上的，不是加在数据记录上的，如果sql没有用到索引，仍然会锁定表,mysql的读写之间是可以并发的。InnoDB为了避免二次查找可以使用索引覆盖技术。

总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表。

2. 加缓存：memcached,redis

要研究整个缓存体系，待续。目前，项目中会使用本地缓存Guava Cache来缓存DB中查询出的记录。

3. 主从复制或主主复制，读写分离

使用replica DB，主从复制中主数据库进行读写操作并复制写入到从数据库，从数据库进行读操作。主数据库离线，数据库以只读模式进行或将某个从数据库提升为主数据库；主主复制中，两个主数据库进行读写操作，写入时，互相协调，当一个主数据库挂机，系统仍能执行读写操作。

可以在应用层做，效率高，也可以用三方工具，第三方工具推荐360的atlas。

4. 使用MySQL自带的分区功能

分区是MySQL 5.1版本引入的简单的水平拆分，需要在建表时加上分区参数，对程序透明。

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引。

5. 垂直拆分

根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统，按功能将系统切分。

6. 水平切分，分库分表

选择合理的sharding key，需要修改代码来实现，使用的sql中尽量带sharding key，从而限定到特定的表上，避免全表扫描。关于分库分表，可以参考我之前的文章 分库分表 分库分表主键问题 。

插入--->

MySQL Limit

limit子句可以被用于强制 SELECT 语句返回指定的记录数。Limit接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。

//初始记录行的偏移量是 0(而不是 1)：

mysql> SELECT * FROM table LIMIT 5,10; //检索记录行6-15

//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：

mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last

//如果只给定一个参数，它表示返回最大的记录行数目。换句话说，LIMIT n 等价于 LIMIT 0,n：

mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行

若每个人只有一个email，下面sql1会导致全表扫描；而sql2会找到一条记录立即返回，避免全表扫描。

Sql1>SELECT * FROM user WHERE email=?;

Sql2> SELECT * FROM user WHERE email=? limit 1;