MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。
MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。
最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。
MySQL查询过程
客户端/服务端通信协议
在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它。
客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。
与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果。因而在开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。
查询缓存
查询时会先去缓存读取数据，如果有有效缓存数据则会直接取缓存而不是真的去查。
如果两个查询实质上需要查找的东西一样，那最好使用完全相同的SQL语句，在缓存有效的情况下能够大大加速查询速度。
如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果
都不会被缓存。
既然是缓存，就会失效，那查询缓存何时失效呢？MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外
任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存
如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗
基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化
用多个小表代替一个大表，注意不要过度设计
批量插入代替循环单条插入
理控制缓存空间大小，一般来说其大小设置为几十兆比较合适
可以通过
SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将
query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存
语法解析和预处理
MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。
查询优化
经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。
MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。
查询执行引擎
在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。
返回结果给客户端
结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。
如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。
MySQL优化汇总
谨慎使用select *
会让优化器无法完成索引覆盖扫描这类优化，而且还会增加额外的I/O、内存和CPU的消耗
当然合理地使用能够提高代码的复用性，要根据实际情况使用
SELECT * 和 SELECT 所有列，两者差别几乎可忽略。
是否扫描的太多额外的记录
如果需要扫描上千上万条数据，但最后只返回几条结果，则考虑优化
看看能否改表结构。例如使用汇总表
看看获取数据结果的方式是否最优，获取路劲是否已经是最短
使用覆盖索引，把所有需要的列都放到索引中，以减少返回表中对应行中取数据的步骤
LIMIT 1可以避免全表扫描，找到对应结果就不会再继续扫描了
切分某些SQL语句
MySQL从设计上让连接和断开都很轻量，在一般服务器上可以支持每秒超过10万的查询，所以在某些场景下可以将一个大的查询语句切分成多个小的查询语句
对于全量数据查询变成分页。假如一张表中有数千万条数据，一次select all，肯定是不行的。可以换成一次取一部分，把一次的压力分摊
删除大量旧数据的时候，不要一个大的语句一次性清完，推荐 一次删一万条。如果用一个大的语句一次性完成的话，可能需要一次锁住大量数据，占满大量日志事务，让Mysql停在那儿了，为避免这种情况发生，最好一次性删除一万条左右的数据，然后每次删完暂停一会儿再操作，将服务器上的一次性压力分散
慎用join操作
推荐先从一张表查出id列表，然后对另一张表使用where in查询
让应用的缓存(redis、memcache等)更高效。例如在第一张表中查询出部分
id了，如果命中了缓存，就可以省去一条where in语句了更容易应对业务的发展，方便对数据库进行拆分，更容易做到高性能和高扩展
对
where in中的id进行升序排序后，查询效率比join的随机关联更高效减少多余的查询。在应用层中两次查询，意味着对某条记录应用只需要查询一次，而使用
join可能需要重复的扫描访问一部分数据单张表查询可以减少锁的竞争
假如非用不可，可以采用以下方式来优化确保
ON或者using子句中的列上有索引确保任何的
group by和order by中的表达式只涉及到一个表中的列
OR改写成IN
OR的效率是n级别，IN的效率是log(n)级别，in的个数建议控制在200以内
在性能要求比较高的场景中，杜绝查询中使用临时表
MySQL的临时表是没有任何索引的，使用临时表一般都意味着性能比较低,因此在对性能要求比较高的场景中，最好不要使用带有临时表的操作
未带索引的字段上的
group by操作UNION查询查询语句中的子查询
部分
order by操作，例如distinct函数和order by一起使用且distinct和order by同一个字段。再例如某些情况下group by和order by字段不同
具体是否用到临时表，可以通过explain来查看，查看Extra列的结果，如果出现Using temporary则需要注意
在够用的前提下选择占用空间最小的数据结构
TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年通常来讲，没有太大的必要使用
DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题对整形设置宽度不会影响占用的空间
大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用
ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高
大表
ALTER TABLE非常耗时，MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久
NOT NULL不会带来多少性能提升
应当用程序来保证非空
索引相关优化
使用表达式或是函数来表示参数会导致SQL无法使用索引
如果列很长，通常可以索引开始的部分字符，这样可以有效节约索引空间，从而提高索引效率
如果筛选条件中有多列有索引，应当把筛选性强的条件放在前面
如果筛选条件中有很多列索引，各个索引的结果互相合并也会带来CPU压力
避免同时使用多个范围条件，多于一个的范围条件会导致MySQL无法使用索引
如果你要查询的列都有索引，那就只取这几个列，查询效率会很高
不要在同一列上按照相同的顺序创建的相同类型的索引
大多数情况下建议扩展已有索引而不是新建索引
定期删除一些长时间未使用过的索引
应尽量避免在
WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描尽量不用
UNIQUE，由程序保证约束
特定类型查询优化
COUNT()查询
如果指定了列，则会统计列值的数量，统计列值数量时不会计算值为
NULL的列COUNT(*)会直接统计行数，性能比统计列值数量要好如果业务不要求很精确的count值可以使用
EXPLAIN来近似，执行EXPLAIN并不需要真正地去执行查询，所以成本非常低
关联查询
当前MySQL关联执行的策略非常简单，它对任何的关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后在嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为为止。然后根据各个表匹配的行，返回查询中需要的各个列。
在大数据场景下，表与表之间通过一个冗余字段来关联，要比直接使用JOIN有更好的性能
尽量确保
ON和USING字句中的列上有索引确保任何的
GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化在关联查询的第二张表的关联列上建立索引，第一张表的关联列不必
LIMIT分页
当偏移量非常大的时候，比如：LIMIT 10000 20这样的查询，MySQL需要查询10020条记录然后只返回20条记录，前面的10000条都将被抛弃，这样的代价非常高。
分两次查询
先用覆盖索引查出真正需要的id，再查这几个id的所有字段，能够大大减少回表次数，只回表真正需要的行
SELECT * FROM `operation_log` LIMIT 34000, 10;
从上面改成下面，此优化在3.4w数据量的情况下已经能够有明显性能差距
SELECT * FROM `operation_log` as a, (SELECT id FROM operation_log LIMIT 34000, 10) as b WHERE a.id=b.id;
利用betweenand和主键索引
利用主键自增id，我们如果知道了分页的上边界，可以写为
select xxx,xxx from table_name where id between xxxxx and xxxx;
直接从指定位置开始扫描
如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET，比如
SELECT id FROM t LIMIT 10000, 10;
改为：
SELECT id FROM t WHERE id > 10000 LIMIT 10;
其他优化的办法还包括使用预先计算的汇总表，或者关联到一个冗余表，冗余表中只包含主键列和需要做排序的列。
UNION去重合并/UNION ALL不去重合并
MySQL处理UNION的策略是先创建临时表，然后再把各个查询结果插入到临时表中，最后再来做查询。因此很多优化策略在UNION查询中都没有办法很好的时候。经常需要手动将WHERE、LIMIT、ORDER BY等字句“下推”到各个子查询中，以便优化器可以充分利用这些条件先优化。
除非确实需要服务器去重，否则就一定要使用UNION ALL，如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致整个临时表的数据做唯一性检查，这样做的代价非常高。当然即使使用ALL关键字，MySQL总是将结果放入临时表，然后再读出，再返回给客户端。虽然很多时候没有这个必要，比如有时候可以直接把每个子查询的结果返回给客户端。
其他
SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM titles; 计算某列的选择性，值越大选择性越强
主键选用自增ID和UUID各有利弊
自增 ID 很容易会被暴力破解，数据迁移的时候，特别是发生表格合并这种操作的时候，会不可避免地存在冲突。UUID 则能够保证唯一性，彻底避免冲突
自增字段的长度较 UUID 小很多，这会对检索的性能有较大影响。Innodb 引擎进行数据检索时，也是先根据索引找到主键，然后根据主键找到记录；这样在主键长度短的情况下，会有较好的读性能
自增 ID 并且高并发的情况下，竞争自增锁会降低数据库的吞吐能力。UUID 则能够在应用层生成 UUID，提高数据库的吞吐能力
InnoDB 中表数据是按照主键顺序存放的，在写入数据时候如果发生了随机 IO，那么就会频繁地移动磁盘块。当数据量大的时候，写的短板将非常明显。自增 ID 中新增的数据可以默认按序排列，对于性能有很大的提升；UUID 的主键之间没有顺序规律
配置项调优
1、innodb_buffer_pool_size=500M
太小，严重影响数据库性能。服务器共500G内存，但只给mysql缓冲池分配了500M，非常影响数据库性能，且造成资源浪费。建议设置为服务器内存的60%。
2、expire_logs_days=7
太短，只能保留7天的binlog，只能恢复7天内的任意数据。建议设置为参数文件里被覆盖的90天的设置。
3、long_query_time=10
太长，建议设置为2秒，让慢查询日志记录更多的慢查询。
4、transaction-isolation = read-committed
建议注释掉，使用数据库默认的事务隔离级别
5、innodb_lock_wait_timeout = 5
设置得太小，会导致事务因锁等待超过5秒，就被回滚。建议和云门户设置得保持一致，云门户大小为120。
6、autocommit = 0
建议改为mysql默认的自动提交(autocommit=1)，提升性能，方便日常操作。
其他
varchar存储时会按实际内容大小来存储，在实际内容不变的情况下，字段长度上限的变化并不会真的影响占用空间，但还是建议以实际使用情况来设置字段长度上限，因为mysql建立索引时，如果没有限制索引长度，则会默认按字段长度建立索引，所以字段长度余越短，索引占用的空间也越小，另外，所有列长度加起来不能超过65535bytes
MySQL的Innodb引擎表索引字段长度的限制为767字节，因此对于多字节字符集的大字段或者多字段组合，创建索引时会出现1709错误，可参考https://help.aliyun.com/knowledge_detail/41707.html解决
