一列MySQL优化方案
2017-11-30 17:23:01 
阿炯
一、MySQL的主要适用场景1、Web网站系统
2、日志记录系统
3、数据仓库系统
4、嵌入式系统
二、MySQL架构图
优化mysql的一些方案
三、MySQL存储引擎概述
1)MyISAM存储引擎
MyISAM存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件。首先肯定会有任何存储引擎都不可缺少的存放表结构定义信息的.frm文件，另外还有.MYD和.MYI文件，分别存放了表的数据(.MYD)和索引数据(.MYI)。每个表都有且仅有这样三个文件做为MyISAM存储类型的表的存储，也就是说不管这个表有多少个索引，都是存放在同一个.MYI文件中。
MyISAM支持以下三种类型的索引：
1、B-Tree索引
B-Tree索引，顾名思义，就是所有的索引节点都按照balancetree的数据结构来存储，所有的索引数据节点都在叶节点。
2、R-Tree索引
R-Tree索引的存储方式和b-tree索引有一些区别，主要设计用于为存储空间和多维数据的字段做索引，所以目前的MySQL版本来说，也仅支持geometry类型的字段作索引。
3、Full-text索引
Full-text索引就是我们长说的全文索引，他的存储结构也是b-tree。主要是为了解决在我们需要用like查询的低效问题。
2)Innodb 存储引擎
1、支持事务安装
2、数据多版本读取
3、锁定机制的改进
4、实现外键
3)NDBCluster存储引擎
NDB存储引擎也叫NDBCluster存储引擎，主要用于MySQLCluster分布式集群环境，Cluster是MySQL从5.0版本才开始提供的新功能。
4)Merge存储引擎
MERGE存储引擎，在MySQL用户手册中也提到了，也被大家认识为MRG_MyISAM引擎。Why？因为MERGE存储引擎可以简单的理解为其功能就是实现了对结构相同的MyISAM表，通过一些特殊的包装对外提供一个单一的访问入口，以达到减小应用的复杂度的目的。要创建MERGE表，不仅仅基表的结构要完全一致，包括字段的顺序，基表的索引也必须完全一致。
5)Memory存储引擎
Memory存储引擎，通过名字就很容易让人知道，他是一个将数据存储在内存中的存储引擎。Memory存储引擎不会将任何数据存放到磁盘上，仅仅存放了一个表结构相关信息的.frm文件在磁盘上面。所以一旦MySQLCrash或者主机Crash之后，Memory的表就只剩下一个结构了。Memory表支持索引，并且同时支持Hash和B－Tree两种格式的索引。由于是存放在内存中，所以Memory都是按照定长的空间来存储数据的，而且不支持BLOB和TEXT类型的字段。Memory存储引擎实现页级锁定。
6)BDB存储引擎
BDB存储引擎全称为BerkeleyDB存储引擎，和Innodb一样，也不是MySQL自己开发实现的一个存储引擎，而是由SleepycatSoftware所提供，当然，也是开源存储引擎，同样支持事务安全。
7)FEDERATED存储引擎
FEDERATED存储引擎所实现的功能，和Oracle的DBLINK基本相似，主要用来提供对远程MySQL服务器上面的数据的访问接口。如果我们使用源码编译来安装MySQL，那么必须手工指定启用FEDERATED存储引擎才行，因为MySQL默认是不起用该存储引擎的。
8)ARCHIVE存储引擎
ARCHIVE存储引擎主要用于通过较小的存储空间来存放过期的很少访问的历史数据。ARCHIVE表不支持索引，通过一个.frm的结构定义文件，一个.ARZ的数据压缩文件还有一个.ARM的meta信息文件。由于其所存放的数据的特殊性，ARCHIVE表不支持删除，修改操
作，仅支持插入和查询操作。锁定机制为行级锁定。
9)BLACKHOLE存储引擎
BLACKHOLE存储引擎是一个非常有意思的存储引擎，功能恰如其名，就是一个“黑洞”。就像我们unix系统下面的“/dev/null”设备一样，不管我们写入任何信息，都是有去无回。
10)CSV存储引擎
CSV存储引擎实际上操作的就是一个标准的CSV文件，他不支持索引。起主要用途就是大家有些时候可能会需要通过数据库中的数据导出成一份报表文件，而CSV文件是很多软件都支持的一种较为标准的格式，所以我们可以通过先在数据库中建立一张CVS表，然后将生成的报表信息插入到该表，即可得到一份CSV报表文件了。
四、影响MySQLServer性能的相关因素
1 商业需求对性能的影响
典型需求：一个论坛帖子总量的统计，要求：实时更新。
2 系统架构及实现对性能的影响
以下几类数据都是不适合在数据库中存放的：
二进制多媒体数据
流水队列数据
超大文本数据
通过Cache技术来提高系统性能：
系统各种配置及规则数据；
活跃用户的基本信息数据；
活跃用户的个性化定制信息数据；
准实时的统计信息数据；
其他一些访问频繁但变更较少的数据；
3 Query语句对系统性能的影响
需求：取出某个group(假设id为1)下的用户编号(id)，用户昵称(nick_name)，并按照加入组的时间(user_group.gmt_create)来进行倒序排列，取出前20个。
解决方案一：
SELECT id,nick_name FROM user,user_group WHERE user_group.group_id=1 and user_group.user_id=user.id ORDER BY user_group.gmt_create desc limit 100,20;
解决方案二：
SELECT user.id,user.nick_name FROM (SELECT user_id FROM user_group WHERE user_group.group_id=1 ORDER BY gmt_create desc limit 100,20) t,user WHERE t.user_id=user.id;
通过比较两个解决方案的执行计划，我们可以看到第一中解决方案中需要和user表参与Join的记录数MySQL通过统计数据估算出来是31156，也就是通过user_group表返回的所有满足group_id=1的记录数(系统中的实际数据是20000)。而第二种解决方案的执行计划中，user表参与Join的数据就只有20条，两者相差很大，我们认为第二中解决方案应该明显优于第一种解决方案。
4 Schema设计对系统的性能影响
尽量减少对数据库访问的请求。
尽量减少无用数据的查询请求。
5 硬件环境对系统性能的影响
1、典型OLTP应用系统
对于各种数据库系统环境中大家最常见的OLTP系统，其特点是并发量大，整体数据量比较多，但每次访问的数据比较少，且访问的数据比较离散，活跃数据占总体数据的比例不是太大。对于这类系统的数据库实际上是最难维护，最难以优化的，对主机整体性能要求也是最高的。因为不仅访问量很高，数据量也不小。
针对上面的这些特点和分析，我们可以对OLTP的得出一个大致的方向。
虽然系统总体数据量较大，但是系统活跃数据在数据总量中所占的比例不大，那么我们可以通过扩大内存容量来尽可能多的将活跃数据cache到内存中；
虽然IO访问非常频繁，但是每次访问的数据量较少且很离散，那么我们对磁盘存储的要求是IOPS表现要很好，吞吐量是次要因素；
并发量很高，CPU每秒所要处理的请求自然也就很多，所以CPU处理能力需要比较强劲；
虽然与客户端的每次交互的数据量并不是特别大，但是网络交互非常频繁，所以主机与客户端交互的网络设备对流量能力也要求不能太弱。
2、典型OLAP应用系统
用于数据分析的OLAP系统的主要特点就是数据量非常大，并发访问不多，但每次访问所需要检索的数据量都比较多，而且数据访问相对较为集中，没有太明显的活跃数据概念。
基于OLAP系统的各种特点和相应的分析，针对OLAP系统硬件优化的大致策略如下：
数据量非常大，所以磁盘存储系统的单位容量需要尽量大一些；
单次访问数据量较大，而且访问数据比较集中，那么对IO系统的性能要求是需要有尽可能大的每秒IO吞吐量，所以应该选用每秒吞吐量尽可能大的磁盘；
虽然IO性能要求也比较高，但是并发请求较少，所以CPU处理能力较难成为性能瓶颈，所以CPU处理能力没有太苛刻的要求；
虽然每次请求的访问量很大，但是执行过程中的数据大都不会返回给客户端，最终返回给客户端的数据量都较小，所以和客户端交互的网络设备要求并不是太高；
此外，由于OLAP系统由于其每次运算过程较长，可以很好的并行化，所以一般的OLAP系统都是由多台主机构成的一个集群，而集群中主机与主机之间的数据交互量一般来说都是非常大的，所以在集群中主机之间的网络设备要求很高。
3、除了以上两个典型应用之外，还有一类比较特殊的应用系统，他们的数据量不是特别大，但是访问请求及其频繁，而且大部分是读请求。可能每秒需要提供上万甚至几万次请求，每次请求都非常简单，可能大部分都只有一条或者几条比较小的记录返回，就比如基于数据库的DNS服务就是这样类型的服务。
虽然数据量小，但是访问极其频繁，所以可以通过较大的内存来cache住大部分的数据，这能够保证非常高的命中率，磁盘IO量比较小，所以磁盘也不需要特别高性能的；
并发请求非常频繁，比需要较强的CPU处理能力才能处理；
虽然应用与数据库交互量非常大，但是每次交互数据较少，总体流量虽然也会较大，但是一般来说普通的千兆网卡已经足够了。
五、MySQL 锁定机制简介
行级锁定(row-level)
表级锁定(table-level)
页级锁定(page-level)
在MySQL数据库中，使用表级锁定的主要是MyISAM，Memory，CSV等一些非事务性存储引擎，而使用行级锁定的主要是Innodb存储引擎和NDBCluster存储引擎，页级锁定主要是BerkeleyDB存储引擎的锁定方式。
六、MySQL Query的优化
Query语句的优化思路和原则主要提现在以下几个方面：
1. 优化更需要优化的Query；
2. 定位优化对象的性能瓶颈；
3. 明确的优化目标；
4. 从Explain入手；
5. 多使用profile；
6. 永远用小结果集驱动大的结果集；
7. 尽可能在索引中完成排序；
8. 只取出自己需要的Columns；
9. 仅仅使用最有效的过滤条件；
10.尽可能避免复杂的Join和子查询；
合理设计并利用索引
1)B-Tree索引
一般来说，MySQL中的B-Tree索引的物理文件大多都是以BalanceTree的结构来存储的，也就是所有实际需要的数据都存放于Tree的LeafNode，而且到任何一个LeafNode的最短路径的长度都是完全相同的，所以我们大家都称之为B-Tree索引当然，可能各种数据库(或MySQL的各种存储引擎)在存放自己的B-Tree索引的时候会对存储结构稍作改造。如Innodb存储引擎的B-Tree索引实际使用的存储结构实际上是B+Tree，也就是在B-Tree数据结构的基础上做了很小的改造，在每一个LeafNode上面出了存放索引键的相关信息之外，还存储了指向与该LeafNode相邻的后一个LeafNode的指针信息，这主要是为了加快检索多个相邻LeafNode的效率考虑。
2)Hash索引
Hash索引在MySQL中使用的并不是很多，目前主要是Memory存储引擎使用，而且在Memory存储引擎中将Hash索引作为默认的索引类型。所谓Hash索引，实际上就是通过一定的Hash算法，将需要索引的键值进行Hash运算，然后将得到的Hash值存入一个Hash表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash运算，然后再和Hash表中的Hash值进行比较并得出相应的信息。
Hash索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；
Hash索引无法被利用来避免数据的排序操作；
Hash索引不能利用部分索引键查询；
Hash索引在任何时候都不能避免表扫面；
Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高；
3)Full-text索引
Full-text索引也就是我们常说的全文索引，目前在MySQL中仅有MyISAM存储引擎支持，而且也并不是所有的数据类型都支持全文索引。目前来说，仅有CHAR，VARCHAR和TEXT这三种数据类型的列可以建Full-text索引。
索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据，更新数据会带来的IO量和调整索引所致的计算量的资源消耗。
是否需要创建索引，几点原则：较频繁的作为查询条件的字段应该创建索引；唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；更新非常频繁的字段不适合创建索引；
不会出现在WHERE子句中的字段不该创建索引；
Join语句的优化
尽可能减少Join语句中的NestedLoop的循环总次数；“永远用小结果集驱动大的结果集”。
优先优化NestedLoop的内层循环；
保证Join语句中被驱动表上Join条件字段已经被索引；
当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置；
ORDER BY，GROUP BY和DISTINCT优化
1)ORDER BY的实现与优化
优化Query语句中的ORDER BY的时候，尽可能利用已有的索引来避免实际的排序计算，可以很大幅度的提升ORDER BY操作的性能。
优化排序：
1.加大max_length_for_sort_data参数的设置；
2.去掉不必要的返回字段；
3.增大sort_buffer_size参数设置；
2)GROUP BY的实现与优化
由于GROUP BY实际上也同样需要进行排序操作，而且与ORDER BY相比，GROUP BY主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY的实现过程中，与ORDER BY一样也可以利用到索引。
3)DISTINCT的实现与优化
DISTINCT实际上和GROUP BY的操作非常相似，只不过是在GROUP BY之后的每组中只取出一条记录而已。所以，DISTINCT的实现和GROUP BY的实现也基本差不多，没有太大的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现，当然，在无法仅仅使用索引即能完成DISTINCT的时候，MySQL只能通过临时表来完成。但是，和GROUP BY有一点差别的是，DISTINCT并不需要进行排序。也就是说，在仅仅只是DISTINCT操作的Query如果无法仅仅利用索引完成操作的时候，MySQL会利用临时表来做一次数据的“缓存”，但是不会对临时表中的数据进行filesort操作。
插入优化
MyISAM批量导入
非空的表导入的时候会同时处理索引，导致导入效率低下，所以通过以下方式可以快速导入。
//关闭非唯一索引
ALTER TABLE table_name DISABLE KEYS;
load data infile 'path' in to table table_name;
//开启非唯一索引
ALTER TABLE table_name ENABLE KEYS;
InnoDB批量导入
1、按主键顺序导入，InnoDB的数据按主键排列的B+Tree结构，所以数据按主键排序可以提升效率。
2、关闭唯一性校验
SET UNIQUE_CHECKS=0;
load data infile 'path' in to table table_name;
//开启唯一校验
SET UNIQUE_CHECKS=1;
3、关闭自动提交
SET AUTOCOMMIT=0;
load data infile 'path' in to table table_name;
//提交数据
COMMIT;
//开启自动提交
SET AUTOCOMMIT=1;
INSERT优化
1、合并多条INSERT语句，减少客户端与MySQL的通讯次数，减少SQL解析次数。
2、采用 INSERT DELAYED立即执行SQL语句(在内存中，之后写入到硬盘)。
3、表分区、独立表空间，将数据分布在不同硬盘提高IO吞吐量。
查询优化
ORDER BY语句
两种排序方式
排序工作如果查询的数据在索引中，可以直接通过索引返回数据(using index)，否则进行filesort。filesort会在每个线程的 sort_buffer_size规定的排序区进行排序，如果超过size大小，会在硬盘进行归并排序。
两种filesort排序方式
1、两次排序
读取排序字段和条件字段在sort_buffer中进行排序，如果超过大小会在硬盘中排序，排序完成后读取硬盘数据。这种方式节省内存，但是可能造成大量IO。
2、一次扫描
一次去处所有满足条件的字段，然后在sort_buffer中排序，排序后直接返回。这种方式效率高但是消耗内存比较大，容易导致大量swap。MySQL通过max_length_for_sort_data来决定使用哪一种方式进行排序。
排序优化方式
1、尽量使用索引进行排序，同时减少排序返回的字段。
2、根据实际情况权衡后，调整sort_buffer的大小。
3、通过调整排序字段顺序，充分利用索引排序。
4、适当的limit语句来避免全表扫描。
GROUP BY 语句
group by默认会对分组字段进行排序，所以使用order by null可以减少排序损耗(使用explain时会发现没有了filesort)。
子查询
使用 join 来代替字查询(可以看到rows减少了非常多)。
索引控制
//建议使用索引
SELECT * FROM user use index(索引名);
//忽略某个索引
SELECT * FROM user ignore index(索引名);
//强制使用索引
SELECT * FROM user force index(索引名);
七、MySQL数据库Schema设计的性能优化
高效的模型设计
适度冗余-让Query尽量减少Join
大字段垂直分拆-summary表优化
大表水平分拆-基于类型的分拆优化
统计表-准实时优化
合适的数据类型
时间存储格式总类并不是太多，我们常用的主要就是DATETIME，DATE和TIMESTAMP这三种了。从存储空间来看TIMESTAMP最少，四个字节，而其他两种数据类型都是八个字节，多了一倍。而TIMESTAMP的缺点在于他只能存储从1970年之后的时间，而另外两种时间类型可以存放最早从1001年开始的时间。如果有需要存放早于1970年之前的时间的需求，我们必须放弃TIMESTAMP类型，但是只要我们不需要使用1970年之前的时间，最好尽量使用TIMESTAMP来减少存储空间的占用。
字符存储类型
CHAR[(M)]类型属于静态长度类型，存放长度完全以字符数来计算，所以最终的存储长度是基于字符集的，如latin1则最大存储长度为255字节，但是如果使用gbk则最大存储长度为510字节。CHAR类型的存储特点是不管我们实际存放多长数据，在数据库中都会存放M个字符，不够的通过空格补上，M默认为1。虽然CHAR会通过空格补齐存放的空间，但是在访问数据的时候，MySQL会忽略最后的所有空格，所以如果我们的实际数据中如果在最后确实需要空格，则不能使用CHAR类型来存放。
VARCHAR[(M)]属于动态存储长度类型，仅存占用实际存储数据的长度。TINYTEXT，TEXT，MEDIUMTEXT和LONGTEXT这四种类型同属于一种存储方式，都是动态存储长度类型，不同的仅仅是最大长度的限制。
事务优化
1. 脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
2. 不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
3. 幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
Innodb在事务隔离级别方面支持的信息如下：
1.READ UNCOMMITTED
常被成为Dirty Reads(脏读)，可以说是事务上的最低隔离级别：在普通的非锁定模式下SELECT的执行使我们看到的数据可能并不是查询发起时间点的数据，因而在这个隔离度下是非Consistent Reads(一致性读)；
2.READ COMMITTED
这一隔离级别下，不会出现DirtyRead，但是可能出现Non-RepeatableReads(不可重复读)和PhantomReads(幻读)。
3. REPEATABLE READ
REPEATABLE READ隔离级别是InnoDB默认的事务隔离级。在REPEATABLE READ隔离级别下，不会出现DirtyReads，也不会出现Non-Repeatable Read，但是仍然存在PhantomReads的可能性。
4.SERIALIZABLE
SERIALIZABLE隔离级别是标准事务隔离级别中的最高级别。设置为SERIALIZABLE隔离级别之后，在事务中的任何时候所看到的数据都是事务启动时刻的状态，不论在这期间有没有其他事务已经修改了某些数据并提交。所以，SERIALIZABLE事务隔离级别下，PhantomReads也不会出现。
八、可扩展性设计之数据切分
数据的垂直切分
数据的垂直切分，也可以称之为纵向切分。将数据库想象成为由很多个一大块一大块的“数据块”(表)组成，我们垂直的将这些“数据块”切开，然后将他们分散到多台数据库主机上面。这样的切分方法就是一个垂直(纵向)的数据切分。
垂直切分的优点
◆数据库的拆分简单明了，拆分规则明确；
◆应用程序模块清晰明确，整合容易；
◆数据维护方便易行，容易定位；
垂直切分的缺点
◆部分表关联无法在数据库级别完成，需要在程序中完成；
◆对于访问极其频繁且数据量超大的表仍然存在性能平静，不一定能满足要求；
◆事务处理相对更为复杂；
◆切分达到一定程度之后，扩展性会遇到限制；
◆过读切分可能会带来系统过渡复杂而难以维护。
数据的水平切分
数据的垂直切分基本上可以简单的理解为按照表按照模块来切分数据，而水平切分就不再是按照表或者是功能模块来切分了。一般来说，简单的水平切分主要是将某个访问极其平凡的表再按照某个字段的某种规则来分散到多个表之中，每个表中包含一部分数据。
水平切分的优点
◆表关联基本能够在数据库端全部完成；
◆不会存在某些超大型数据量和高负载的表遇到瓶颈的问题；
◆应用程序端整体架构改动相对较少；
◆事务处理相对简单；
◆只要切分规则能够定义好，基本上较难遇到扩展性限制；
水平切分的缺点
◆切分规则相对更为复杂，很难抽象出一个能够满足整个数据库的切分规则；
◆后期数据的维护难度有所增加，人为手工定位数据更困难；
◆应用系统各模块耦合度较高，可能会对后面数据的迁移拆分造成一定的困难。
数据切分与整合中可能存在的问题
1.引入分布式事务的问题
完全可以将一个跨多个数据库的分布式事务分拆成多个仅处于单个数据库上面的小事务，并通过应用程序来总控各个小事务。当然，这样作的要求就是我们的俄应用程序必须要有足够的健壮性，当然也会给应用程序带来一些技术难度。
2.跨节点Join的问题
推荐通过应用程序来进行处理，先在驱动表所在的MySQLServer中取出相应的驱动结果集，然后根据驱动结果集再到被驱动表所在的MySQL Server中取出相应的数据。
3.跨节点合并排序分页问题
从多个数据源并行的取数据，然后应用程序汇总处理。
九、可扩展性设计之Cache与Search的利用
通过引入Cache(Redis、Memcached)，减少数据库的访问，增加性能。
通过引入Search(Lucene、Solr、ElasticSearch)，利用搜索引擎高效的全文索引和分词算法，以及高效的数据检索实现，来解决数据库和传统的Cache软件完全无法解决的全文模糊搜索、分类统计查询等功能。
本文源自：优化mysql的一些方案
Sql优化是一个大家都比较关注的热门话题，无论你在面试，还是工作中，都很有可能会遇到。优化sql语句，对它的改造成本相对于代码来说也要小得多。这里从15个方面，分享了sql优化的一些小技巧，希望对你有所帮助。
1、避免使用select *
很多时候，我们写sql语句时，为了方便，喜欢直接使用select *，一次性查出表中所有列的数据。
反例：select * from user where id=1;
在实际业务场景中，可能我们真正需要使用的只有其中一两列。查了很多数据，但是不用，白白浪费了数据库资源，比如：内存或者cpu。
此外，多查出来的数据，通过网络IO传输的过程中，也会增加数据传输的时间。还有一个最重要的问题是：select * 不会走覆盖索引，会出现大量的回表操作，而从导致查询sql的性能很低。那么如何优化呢？
正例：select name,age from user where id=1;
sql语句查询时，只查需要用到的列，多余的列根本无需查出来。
2、用union all代替union
我们都知道sql语句使用union关键字后，可以获取排重后的数据。而如果使用union all关键字，可以获取所有数据，包含重复的数据。
反例：
(select * from user where id=1)
union
(select * from user where id=2);
排重的过程需要遍历、排序和比较，它更耗时，更消耗cpu资源。所以如果能用union all的时候，尽量不用union。
正例：
(select * from user where id=1)
union all
(select * from user where id=2);
除非是有些特殊的场景，比如union all之后，结果集中出现了重复数据，而业务场景中是不允许产生重复数据的，这时可以使用union。
3、小表驱动大表
小表驱动大表，也就是说用小表的数据集驱动大表的数据集。
假如有order和user两张表，其中order表有10000条数据，而user表有100条数据。这时如果想查一下，所有有效的用户下过的订单列表。可以使用in关键字实现：select * from order where user_id in (select id from user where status=1)
也可以使用exists关键字实现：select * from order where exists (select 1 from user where order.user_id = user.id and status=1)
前面提到的这种业务场景，使用in关键字去实现业务需求，更加合适。为什么呢？
因为如果sql语句中包含了in关键字，则它会优先执行in里面的子查询语句，然后再执行in外面的语句。如果in里面的数据量很少，作为条件查询速度更快。而如果sql语句中包含了exists关键字，它优先执行exists左边的语句（即主查询语句）。然后把它作为条件，去跟右边的语句匹配。如果匹配上，则可以查询出数据。如果匹配不上，数据就被过滤掉了。
这个需求中，order表有10000条数据，而user表有100条数据。order表是大表，user表是小表。如果order表在左边，则用in关键字性能更好。
总结一下：
in 适用于左边大表，右边小表。
exists 适用于左边小表，右边大表。
不管是用in，还是exists关键字，其核心思想都是用小表驱动大表。
4、批量操作
如果你有一批数据经过业务处理之后，需要插入数据，该怎么办？
反例：
for(Order order: list){
orderMapper.insert(order):
}
在循环中逐条插入数据：insert into order(id,code,user_id) values(123,'001',100);
该操作需要多次请求数据库，才能完成这批数据的插入。但众所周知，在代码中每次远程请求数据库，是会消耗一定性能的。而如果我们的代码需要请求多次数据库，才能完成本次业务功能，势必会消耗更多的性能。
那么如何优化呢？
正例：orderMapper.insertBatch(list):
提供一个批量插入数据的方法。
insert into order(id,code,user_id) values (123,'001',100),(124,'002',100),(125,'003',101);
这样只需要远程请求一次数据库，sql性能会得到提升，数据量越多，提升越大。但需要注意的是，不建议一次批量操作太多的数据，如果数据太多数据库响应也会很慢。批量操作需要把握一个度，建议每批数据尽量控制在500以内。如果数据多于500，则分多批次处理。
5、多用limit
有时候，我们需要查询某些数据中的第一条，比如：查询某个用户下的第一个订单，想看看他第一次的首单时间。
反例：select id, create_date from order where user_id=123 order by create_date asc;
根据用户id查询订单，按下单时间排序，先查出该用户所有的订单数据，得到一个订单集合。然后在代码中，获取第一个元素的数据，即首单的数据，就能获取首单时间。
List<Order> list = orderMapper.getOrderList();
Order order = list.get(0);
虽说这种做法在功能上没有问题，但它的效率非常不高，需要先查询出所有的数据，有点浪费资源。那么如何优化呢？
正例：select id, create_date from order where user_id=123 order by create_date asc limit 1;
使用limit 1，只返回该用户下单时间最小的那一条数据即可。此外在删除或者修改数据时，为了防止误操作，导致删除或修改了不相干的数据，也可以在sql语句最后加上limit。
例如：update order set status=0,edit_time=now(3) where id>=100 and id<200 limit 100;
这样即使误操作，比如把id搞错了，也不会对太多的数据造成影响。
6、in中值太多
对于批量查询接口，我们通常会使用in关键字过滤出数据。比如：想通过指定的一些id，批量查询出用户信息，sql语句如下：
select id,name from category where id in (1,2,3...100000000);
如果我们不做任何限制，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。这时该怎么办呢？
select id,name from category where id in (1,2,3...100) limit 500;
可以在sql中对数据用limit做限制。不过我们更多的是要在业务代码中加限制，伪代码如下：
public List<Category> getCategory(List<Long> ids) {
if(CollectionUtils.isEmpty(ids)) {
return null;
}
if(ids.size() > 500){
throw new BusinessException("一次最多允许查询500条记录")
}
return mapper.getCategoryList(ids);
}
还有一个方案就是：如果ids超过500条记录，可以分批用多线程去查询数据。每批只查500条记录，最后把查询到的数据汇总到一起返回。不过这只是一个临时方案，不适合于ids实在太多的场景。因为ids太多，即使能快速查出数据，但如果返回的数据量太大了，网络传输也是非常消耗性能的，接口性能始终好不到哪里去。
7、增量查询
有时候，我们需要通过远程接口查询数据，然后同步到另外一个数据库。
反例：select * from user;
如果直接获取所有的数据，然后同步过去。这样虽说非常方便，但是带来了一个非常大的问题，就是如果数据很多的话，查询性能会非常差。这时该怎么办呢？
正例：
select * from user where id>#{lastId} and create_time >= #{lastCreateTime} limit 100;
按id和时间升序，每次只同步一批数据，这一批数据只有100条记录。每次同步完成之后，保存这100条数据中最大的id和时间，给同步下一批数据的时候用。通过这种增量查询的方式，能够提升单次查询的效率。
8、高效的分页
有时候，列表页在查询数据时，为了避免一次性返回过多的数据影响接口性能，我们一般会对查询接口做分页处理。在mysql中分页一般用的limit关键字：
select id,name,age from user limit 10,20;
如果表中数据量少，用limit关键字做分页，没啥问题。但如果表中数据量很多，用它就会出现性能问题。
比如现在分页参数变成了：select id,name,age from user limit 1000000,20;
mysql会查到1000020条数据，然后丢弃前面的1000000条，只查后面的20条数据，这个是非常浪费资源的。
那么，这种海量数据该怎么分页呢？优化sql：
select id,name,age from user where id > 1000000 limit 20;
先找到上次分页最大的id，然后利用id上的索引查询。不过该方案，要求id是连续的，并且有序的。
还能使用between优化分页：
select id,name,age from user where id between 1000000 and 1000020;
需要注意的是between要在唯一索引上分页，不然会出现每页大小不一致的问题。
9、用连接查询代替子查询
mysql中如果需要从两张以上的表中查询出数据的话，一般有两种实现方式：子查询和连接查询。子查询的例子如下：
select * from order where user_id in (select id from user where status=1)
子查询语句可以通过in关键字实现，一个查询语句的条件落在另一个select语句的查询结果中。程序先运行在嵌套在最内层的语句，再运行外层的语句。
子查询语句的优点是简单，结构化，如果涉及的表数量不多的话。但缺点是mysql执行子查询时，需要创建临时表，查询完毕后，需要再删除这些临时表，有一些额外的性能消耗。
这时可以改成连接查询。具体例子如下：
select o.* from order o inner join user u on o.user_id = u.id where u.status=1
10、join的表不宜过多
根据阿里巴巴开发者手册的规定，join表的数量不应该超过3个。
反例：
select a.name,b.name.c.name,d.name from a
inner join b on a.id = b.a_id
inner join c on c.b_id = b.id
inner join d on d.c_id = c.id
inner join e on e.d_id = d.id
inner join f on f.e_id = e.id
inner join g on g.f_id = f.id
如果join太多，mysql在选择索引的时候会非常复杂，很容易选错索引。并且如果没有命中中，nested loop join 就是分别从两个表读一行数据进行两两对比，复杂度是 n^2。所以我们应该尽量控制join表的数量。
正例：
select a.name,b.name.c.name,a.d_name
from a
inner join b on a.id = b.a_id
inner join c on c.b_id = b.id
如果实现业务场景中需要查询出另外几张表中的数据，可以在a、b、c表中冗余专门的字段，比如：在表a中冗余d_name字段，保存需要查询出的数据。
不过我之前也见过有些ERP系统，并发量不大，但业务比较复杂，需要join十几张表才能查询出数据。所以join表的数量要根据系统的实际情况决定，不能一概而论，尽量越少越好。
11、join时要注意
我们在涉及到多张表联合查询的时候，一般会使用join关键字。而join使用最多的是left join和inner join。
left join：求两个表的交集外加左表剩下的数据。
inner join：求两个表交集的数据。
使用inner join的示例如下：
select o.id,o.code,u.name
from order o
inner join user u on o.user_id = u.id
where u.status=1;
如果两张表使用inner join关联，mysql会自动选择两张表中的小表，去驱动大表，所以性能上不会有太大的问题。使用left join的示例如下：
select o.id,o.code,u.name
from order o
left join user u on o.user_id = u.id
where u.status=1;
如果两张表使用left join关联，mysql会默认用left join关键字左边的表，去驱动它右边的表。如果左边的表数据很多时，就会出现性能问题。要特别注意的是在用left join关联查询时，左边要用小表，右边可以用大表。如果能用inner join的地方，尽量少用left join。
12、控制索引的数量
众所周知，索引能够显著的提升查询sql的性能，但索引数量并非越多越好。因为表中新增数据时，需要同时为它创建索引，而索引是需要额外的存储空间的，而且还会有一定的性能消耗。阿里巴巴的开发者手册中规定，单表的索引数量应该尽量控制在5个以内，并且单个索引中的字段数不超过5个。
mysql使用的B+树的结构来保存索引的，在insert、update和delete操作时，需要更新B+树索引。如果索引过多，会消耗很多额外的性能。那么，问题来了，如果表中的索引太多，超过了5个该怎么办？
这个问题要辩证的看，如果你的系统并发量不高，表中的数据量也不多，其实超过5个也可以，只要不要超过太多就行。但对于一些高并发的系统，请务必遵守单表索引数量不要超过5的限制。那么，高并发系统如何优化索引数量？
能够建联合索引，就别建单个索引，可以删除无用的单个索引。将部分查询功能迁移到其他类型的数据库中，比如：ElasticSeach、HBase等，在业务表中只需要建几个关键索引即可。
13、选择合理的字段类型
char表示固定字符串类型，该类型的字段存储空间的固定的，会浪费存储空间。
alter table order add column code char(20) NOT NULL;
varchar表示变长字符串类型，该类型的字段存储空间会根据实际数据的长度调整，不会浪费存储空间。
alter table order add column code varchar(20) NOT NULL;
如果是长度固定的字段，比如用户手机号，一般都是11位的，可以定义成char类型，长度是11字节。
但如果是企业名称字段，假如定义成char类型，就有问题了。
如果长度定义得太长，比如定义成了200字节，而实际企业长度只有50字节，则会浪费150字节的存储空间。
如果长度定义得太短，比如定义成了50字节，但实际企业名称有100字节，就会存储不下，而抛出异常。
所以建议将企业名称改成varchar类型，变长字段存储空间小，可以节省存储空间，而且对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
我们在选择字段类型时，应该遵循这样的原则：
能用数字类型，就不用字符串，因为字符的处理往往比数字要慢。
尽可能使用小的类型，比如：用bit存布尔值，用tinyint存枚举值等。
长度固定的字符串字段，用char类型。
长度可变的字符串字段，用varchar类型。
金额字段用decimal，避免精度丢失问题。
还有很多原则，这里就不一一列举了。
14、提升group by的效率
我们有很多业务场景需要使用group by关键字，它主要的功能是去重和分组。通常它会跟having一起配合使用，表示分组后再根据一定的条件过滤数据。
反例：select user_id,user_name from order group by user_id having user_id <= 200;
这种写法性能不好，它先把所有的订单根据用户id分组之后，再去过滤用户id大于等于200的用户。分组是一个相对耗时的操作，为什么我们不先缩小数据的范围之后，再分组呢？
正例：select user_id,user_name from order where user_id <= 200 group by user_id
使用where条件在分组前，就把多余的数据过滤掉了，这样分组时效率就会更高一些。其实这是一种思路，不仅限于group by的优化。我们的sql语句在做一些耗时的操作之前，应尽可能缩小数据范围，这样能提升sql整体的性能。
15、索引优化
sql优化当中，有一个非常重要的内容就是：索引优化。
很多时候sql语句，走了索引，和没有走索引，执行效率差别很大。所以索引优化被作为sql优化的首选。索引优化的第一步是：检查sql语句有没有走索引。那么，如何查看sql走了索引没？
可以使用explain命令，查看mysql的执行计划。例如：
explain select * from `order` where code='002';
结果：图片通过这几列可以判断索引使用情况，执行计划包含列的含义如下图所示：
说实话，sql语句没有走索引，排除没有建索引之外，最大的可能性是索引失效了。下面说说索引失效的常见原因：
如果不是上面的这些原因，则需要再进一步排查一下其他原因。此外可能会遇到过这样一种情况：明明是同一条sql，只有入参不同而已。有的时候走的索引a，有的时候却走的索引b？
没错，有时候mysql会选错索引。必要时可以使用force index来强制查询sql走某个索引。