mysql调优及锁

MySQL逻辑架构

• MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。
• MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。
• 最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。

MySQL查询过程

客户端/服务端通信协议

• MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。
• 客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。
• 与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。

查询过程

1.查询缓存

在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果 都不会被缓存。

既然是缓存，就会失效，那查询缓存何时失效呢？MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

1. 任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存
2. 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情，也不在本文讨论的范畴内。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：

1. 用多个小表代替一个大表，注意不要过度设计
2. 批量插入代替循环单条插入
3. 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适
4. 可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存

最后的忠告是不要轻易打开查询缓存，特别是写密集型应用。如果你实在是忍不住，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。

当然查询缓存系统本身是非常复杂的，这里讨论的也只是很小的一部分，其他更深入的话题，比如：缓存是如何使用内存的？如何控制内存的碎片化？事务对查询缓存有何影响等等，读者可以自行阅读相关资料，这里权当抛砖引玉吧。

2.语法解析和预处理

MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

3.查询优化

经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。

较易理解的优化策略

• 重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）
• 优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值，具体原理见下文）
• 提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）
• 优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多）

4.查询执行引擎

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

5.返回结果给客户端

查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等等。

如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。

结果集返回客户端是一个增量且逐步返回的过程。有可能MySQL在生成第一条结果时，就开始向客户端逐步返回结果集了。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以一个满足①中所描述的通信协议的数据包发送，再通过TCP协议进行传输，在传输过程中，可能对MySQL的数据包进行缓存然后批量发送。

回头总结一下MySQL整个查询执行过程，总的来说分为6个步骤：

1. 客户端向MySQL服务器发送一条查询请求
2. 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
3. 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划
4. MySQL根据执行计划，调用存储引擎的API来执行查询
5. 将结果返回给客户端，同时缓存查询结果

引擎

1. MyISAM

ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MySQL能够支持这样的备份应用程序。

Mysql 5.5版本之前的默认的存储引擎
1.不支持事务、不支持外键
2.只支持表级锁(后面介绍)
3.没有事务日志，故障恢复数据较麻烦
4.分区存放文件，平均分配IO，不用花费资源去处理事务，效率较高
5.索引为非聚簇索引

2. InnoDB

它提供了事务控制能力功能，它确保一组命令全部执行成功，或者当任何一个命令出现错误时所有命令的结果都被回退，可以想像在电子银行中事务控制能力是非常重要的。支持COMMIT、ROLLBACK和其他事务特性。最新版本的Mysql已经计划移除对BDB的支持，转而全力发展InnoDB。
PS：也就是说当你选用了ISAM作为mysql的数据引擎时，你在应用中的spring数据库事物管理配置将彻底失效，因为它根本就不支持事物！
查看哪些引擎支持事物
show engines; 在MySQL5.7当中，支持很多engines，可是支持事物的engines只有一个：innodb

ALTER TABLE a ADD unique INDEX aaa(b)

1.支持事务、支持外键
2.支持行级锁与表级锁
3.花费资源去处理事务，效率比不上MyISAM
4.有事务日志，恢复数据较方便
5.索引为聚簇索引
	虽然InnoDB不断优化，效率已经有了很大提升，但是还是比不上MyISAM

1.索引原理

类似于目录

B+Tree：每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。

B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，很适合查找范围数据。

B-Tree可以对<，<=，=，>，>=，BETWEEN，IN，以及不以通配符开始的LIKE使用索引。

2.聚簇索引

什么是聚簇索引

1. InnoDB对主键建立聚簇索引。在叶子节点存储主键和 数据，这样可以快速定位到数据，叶子节点之间连接，可以快速查询到相邻的数据
2. 如果你不指定主键，InnoDB会用一个具有唯一且非空值的索引来代替。
3. 如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建立聚簇索引。

• InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。叶子节点之间相互连接就不需要回表
• 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

聚簇索引的优势

1. 由于行数据和叶子节点存储在一起，，访问同一数据页不同行记录时，已经把页加载到了Buffer中，再次访问的时候，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。
2. 辅助索引使用主键作为"指针"而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定位）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的节点如何变化，辅助索引树都不受影响。
3. 聚簇索引适合用在排序的场合，非聚簇索引不适合
4. 取出一定范围数据的时候，使用用聚簇索引
5. 二级索引需要两次索引查找，而不是一次才能取到数据，因为存储引擎第一次需要通过二级索引找到索引的叶子节点，从而找到数据的主键，然后在聚簇索引中用主键再次查找索引，再找到数据
6. 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘 I/O。

聚簇索引的劣势

1. 维护索引很昂贵，特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片

2. 表因为使用UUId（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫面更慢，所以建议使用int的auto_increment作为主键

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了行的地址信息。表数据存储在独立的地方，

3.回表

回表就是先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

尽量 避免select * from …

覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

• 将被查询的字段，建立到联合索引里去 就能实现覆盖索引

索引失效

1.违反最左匹配原则

最左匹配原则：最左优先，以最左边的为起点任何连续的索引都能匹配上，如不连续，则匹配不上。

如：建立索引为(a,b)的联合索引，那么只查 where b = 2 则不生效。换句话说：如果建立的索引是(a,b,c)，也只有(a),(a,b),(a,b,c)三种查询可以生效。

2.遇到范围查询

（>、<、between、like）就会停止匹配。因为不能确定后面索引的值是不是在前面索引的范围内

比如：a= 1 and b = 2 and c>3 and d =4 如果建立(a,b,c,d)顺序的索引，d 是用不到索引的，因为 c 字段是一个范围查询，它之后的字段会停止匹配。

3.在索引列上做任何操作

如计算、函数、（手动或自动）类型转换等操作，会导致索引失效而进行全表扫描。

select * from table where id +1=5; 不会使用索引

改成select * from table where id = 4;可以使用索引

4.使用不等于（!= 、<>）

5.like 中以%开头(’%abc’)

www.cdqf.com 类似这种结构的数据 可以采取反过来存储的方法

com.qq.www select * from xxx where url like ‘com.%’

• 实在不行就用Elasticsearch

6.数字字符串不加单引号

select * from user where name = 2000;

改成select * from user where name = ’2000‘; 可以使用name这个索引

or 连接索引失效，

order by

如果字段顺序与索引一致那么可以用索引 如果违反违反最左前缀法则，导致额外的文件排序filesorts

（会降低性能）。

group by

正常的话使用索引，如果违反最左原则 那么会产生临时表，会降低性能

filesorts

当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序；

如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序，使用临时表）。

对于filesort，MySQL有两种排序算法：

• 两次扫描算法(Two passes)

先将需要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需的Columns。

该算法是MySQL4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。另一方面，内存开销较小。

• 一次扫描算法(single pass)

该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出。

从MySQL4.1版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。

在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种：当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。

当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出“Using temporary;Using filesort”。

为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。

查询优化器

当然即使我们都遵守得这些规则，mysql用不用索引，还得看查询优化器得计算成本

最终还是mysql 查询优化器：自己选

#等价变换策略
A:5=5 and a>5 改成 a > 5 去除恒成立条件
a < b and a=5 改成b>5 and a=5

#基于联合索引，调整条件位置等 

#优化count、min、max等函数
InnoDB引擎min函数只需要找索引最左边
InnoDB引擎max函数只需要找索引最右边
MyISAM引擎count(*)，不需要计算，直接返回


#提前终止查询
使用了limit查询，获取limit所需的数据，就不在继续遍历后面数据

#in的优化
MySQL对in查询，会先进行排序，再采用二分法查找数据。比如where id in (2,1,3)，变
成 in (1,2,3) 

B:join优化：inner join/join 自动优化小表驱动大表，left right没法优化
select from a join b  10万个  20万--40万
select from b join a  1000个  2000-4000   
a表大，b表小，基于主键索引，每条数据查询的IO次数2-4

7.计算成本

https://blog.csdn.net/cblstc/article/details/117413036

https://www.cnblogs.com/zhuwenjoyce/p/14968183.html

写了sql语句----生成解析树-----分析sql语句----预估----

查询成本

EXPLAIN FORMAT = JSON SELECT * FROM module; 会显示出当前查询的成本

SHOW TABLE STATUS like 'xxx' #查询表的状态

mysql把区间成本，页成本看成1.0  
每次回表1.0
cpu0.2

确定走不走索引

强制
force index(ctime):索引



EXPLAIN FORMAT=json SELECT * FROM salaries WHERE  salary>100000
EXPLAIN  SELECT * FROM salaries WHERE  salary>100000
ALTER TABLE salaries ADD INDEX aaa(salary)
SELECT COUNT(*) FROM salaries

计算方法

1、IO成本

mysql的innodb存储引擎会把数据存储到磁盘上，这时候无论怎么优化SQL，都是需要从磁盘中读取数据到内存，就是IO成本，每次读取磁盘，至少耗时0.01秒，至少读一页，innodb一个页的数据存储大小是16KB，这个磁盘的IO时间成本是1.0，这里的1.0没有单位，就是个比较值。

2、CPU成本

从磁盘读到数据后要放到内存中处理数据的过程，这是CPU成本。读取后并且检测可能的where条件，这个CPU的IO时间成本为0.2，这里的1.0和0.2被称之为成本常数。

3.单表全扫描成本计算

聚簇索引页数= data_length / 16kb = data_length/16/1000

• IO成本=聚簇索引页数x1.0+1.1

1.0是成本系数，1.1是一个微调值

• CPU成本=记录数x0.2+1.0 （记录数就是所有的数据的行数）

0.2是成本系数，1.0是一个微调值

• 总成本=IO成本+CPU成本

4.使用索引的成本计算

• 二级*级索引查询的成本=IO成本+CPU成本
  • IO成本=索引的扫描区间x1.0 即索引字段的个数
  • CPU成本=二级索引记录数x0.2+0.01
    二级索引记录数可在执行计划的rows列看到，mysql是如何计算这个记录数的呢？
• 回表的成本=IO成本+CPU成本
  • IO成本=查询出的条数x1.0 mysql认为回表时有多少条记录就相当于访问多少个页
  • CPU成本=查询出的条数x0.2
• 总成本=二级索引查询的成本+回表的成本
• =二级索引记录数x0.2*2 + 二级索引记录数+1

多表连接查询成本

多表连接查询成本 = 一次驱动表成本 + 从驱动表查出的记录数 * 一次被驱动表的成本

查看mysql执行计划的分析过程、

SET optimizer_trace="enabled=on"; -- 开启追踪
select * from orders where xxx=xxx; -- 执行查询语句
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off"; -- 关闭追踪

10.索引的创建

• 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
• 组合索引，即一个索包含多个列。

• 普通索引 INDEX 它没有任何限制。加快对数据的访问速度，为经常被查询或者排序的字段加
• 唯一索引 UNIQUE 值唯一
• 主键索引 PRIMARY KEY 相当于notnull 和unique

1.创建方法：

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。

#创建索引
CREATE INDEX indexName ON mytable(username(length));
#修改表结构
ALTER mytable ADD INDEX [indexName] (username(length))
#创建表的时候直接指定
CREATE TABLE mytable( 
    ID INT NOT NULL, username VARCHAR(16) NOT NULL, 
    INDEX [indexName] (username(length)) );
    
#删除索引的语法：删除时不区分索引类型 都用index
DROP INDEX [indexName] ON mytable;
ALTER TABLE boys DROP INDEX `testindex`;



#组合索引
CREATE TABLE mytable( 
    ID INT NOT NULL, 
    username VARCHAR(16) NOT NULL, 
    city VARCHAR(50) NOT NULL, a
    ge INT NOT NULL );
    
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

2.建立索引的时机

一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，因为MySQL的B-Tree只对<，<=，=，>，>=，BETWEEN，IN，以及不以通配符开始的LIKE才会使用索引。

例如：

SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city=‘郑州’

此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

索引设计：

• 索引字段尽量使用数字型
• 尽量不要让字段的默认值为NULL 在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。索引不会包含有NULL值的列，只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。

前缀索引和索引选择性

• 对串列进行索引，如果可能应该指定一个前缀长度
• 对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MYSQL不允许索引这些列的完整长度。
• 前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做order by和group by，也无法使用前缀索引做覆盖扫描。

一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。

例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。

短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。在绝大多数应用里，数据库中的字符串数据大都以各种各样的名字为主，把索引的长度设置为10~15个字符已经足以把搜索范围缩小到很少的几条数据记录了。

通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。

• 索引的选择性是指，不重复的索引值（基数）和数据表中的记录总数的比值。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MYSQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

决窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”。

为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。例如以下查询：

select count(*) as cnt,city from sakila.city_demo group by city order by cnt desc limit 10;

select count(*) as cnt,left(city,7) as perf from sakila.city_demo group by city order by cnt desc limit 10;

直到这个前缀的选择性接近完整列的选择性。

计算合适的前缀长度的另一个方法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性，如下：

select count(distinct city)/count(*) from sakila.city_demo;
#前缀索引
select count(distinct left(city,7))/count(*) from sakila.city_demo;

使用唯一索引

考虑某列中值的分布。索引的列的基数越大，索引的效果越好。

例如，存放出生日期的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“ M” 和“F”，则对此列进行索引没有多大用处，因为不管搜索哪个值，都会得出大约一半的行。

使用组合索引代替多个列索引

一个多列索引（组合索引）与多个列索引MySQL在解析执行上是不一样的，如果在explain中看到有索引合并（即MySQL为多个列索引合并优化），应该好好检查一下查询的表和结构是不是已经最优。

注意重复/冗余的索引、不使用的索引

MySQL允许在相同的列上创建多个索引，无论是有意还是无意的。大多数情况下不需要使用冗余索引。

对于重复/冗余、不使用的索引：可以直接删除这些索引。因为这些索引需要占用物理空间，并且也会影响更新表的性能。

查看索引使用情况：

show status like 'Handler_read%';

• Handler_read_first 代表读取索引头的次数，如果这个值很高，说明全索引扫描很多。
• Handler_read_key 代表一个索引被使用的次数，如果我们新增加一个索引，可以查看Handler_read_key是否有增加，如果有增加，说明sql用到索引。越高说明索引效果越好
• Handler_read_next 代表读取索引的下列，一般发生range scan。
• Handler_read_prev 代表读取索引的上列，一般发生在ORDER BY … DESC。
• Handler_read_rnd 代表在固定位置读取行，如果这个值很高，说明对大量结果集进行了排序、进行了全表扫描、关联查询没有用到合适的KEY。
• Handler_read_rnd_next 代表进行了很多表扫描，查询性能低下。

undo和redo

undo：

• Undo：意为撤销或取消，以撤销操作为目的，返回指定某个状态的操作。
• Undo Log：数据库事务开始之前，会将要修改的记录存放到 Undo 日志里，当事务回滚时或者数据库崩溃时，可以利用 Undo 日志，撤销未提交事务对数据库产生的影响。
• Undo Log产生和销毁：Undo Log在事务开始前产生；事务在提交时，并不会立刻删除undolog，innodb会将该事务对应的undo log放入到删除列表中，后面会通过后台线程purge thread进行回收处理。Undo Log属于逻辑日志，记录一个变化过程。例如执行一个delete，undolog会记录一个insert；执行一个update，undolog会记录一个相反的update。
• Undo Log存储：undo log采用段的方式管理和记录。在innodb数据文件中包含一种rollbacksegment回滚段，内部包含1024个undo log segment。可以通过下面一组参数来控制Undo log存储。
• 实现事务的原子性Undo Log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 Undo Log 中的备份将数据恢复到事务开始之前的状态。实现多版本并发控制（MVCC）Undo Log 在 MySQL InnoDB 存储引擎中用来实现多版本并发控制。事务未提交之前，Undo Log保存了未提交之前的版本数据，Undo Log 中的数据可作为数据旧版本快照供其他并发事务进行快照读
  事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo Buffer 中。
  事务B手动开启事务，执行查询操作，会读取 Undo 日志数据返回，进行快照读

redo：

数据修改后的内容，在缓冲池，然后数据修改后生成redo日志，需要把这些内存中的数据插入到磁盘。这个时候当数据库宕机的时候。这些redo就是重要的记录，重启之后会把redo日志也就是修改的数据重新写入数据库。

• redo log 的存储是顺序存储，而缓存同步是随机操作。
• 缓存同步是以数据页为单位的，每次传输的数据大小大于redo log。
• Redo：顾名思义就是重做。以恢复操作为目的，在数据库发生意外时重现操作。
• Redo Log：指事务中修改的任何数据，将最新的数据备份存储的位置（Redo Log），被称为重做日志。
• Redo Log 的生成和释放：随着事务操作的执行，就会生成Redo Log，在事务提交时会将产生Redo Log写入Log Buffer，并不是随着事务的提交就立刻写入磁盘文件。等事务操作的脏页写入到磁盘之后，Redo Log 的使命也就完成了，Redo Log占用的空间就可以重用（被覆盖写入）。
• Redo Log工作原理 Redo Log 是为了实现事务的持久性而出现的产物。防止在发生故障的时间点，尚有脏页未写入表的 IBD 文件中，在重启 MySQL 服务的时候，根据 Redo Log 进行重做，从而达到事务的未入磁盘数据进行持久化这一特性

redo log是用来恢复数据的 用于保障，已提交事务的持久化特性

• redo log 的存储是顺序存储，而缓存同步是随机操作。
• 缓存同步是以数据页为单位的，每次传输的数据大小大于redo log。

mysql锁

MySQL 全局锁和表锁 - keme - 博客园 (cnblogs.com)

• 增删改，mysql会默认加写锁

行级锁

Record Lock 行锁

对某一行的数据锁定

#显示的加写锁 
select * from  people where id =3 for update;
#显示加读锁  读读不互斥  读写互斥（阻塞其他事务的写操作）
SELECT ... FOR SHARE
# 写操作 自动开启
update people set name='James' where id=3

• 对于显示的加锁或增删改操作，条件判断必须是精确匹配（也就是=） ，不能用>,<,between或like等范围查询方式，因为这样会使行锁变成next-key Lock。
• FOR SHARE 确保查到的数据是最新的数据，并且不允许其他人来修改数据，但是可以继续添加共享锁。所以自己不一定能够修改数据，因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。
• FOR UPDATE 确保查到的数据是最新的数据，并且不允许其他人来修改数据，也不允许加任何的锁，允许快照读，但是阻塞当前读

表级锁

锁住整张表，开销小，加锁快；不会出现死锁(因为MyISAM会一次性获得SQL所需的全部锁)；锁定粒度大，发生锁冲突的概率最高,并发度最低。 演示:两个cmd

#该表可以读，不能ddl 和 dml 中增删改，只能读取表数据
LOCK TABLES ss READ;  
#不能读也不能写
LOCK TABLES ss WRITE;
unlock tables

全局锁

Flush tables with read lock
由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁

全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，

当需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都select出来存成文本

但是让整个库都只读，可能出现以下问题：

如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
如果在从库上备份，那么在备份期间从库不能执行主库同步过来的binlog，会导致主从延迟

避免阻塞与死锁

1.详细的分析产品需求 会有哪些查询，哪些修改,好好建表,减少长事务操作

2.分布式事务中，采用弱一致性，可以减少阻塞

3.最直接最简单的方法就是把表的数据量变小(针对于数据量确实很大了，分库分表)

4.当然sql语句调优、提高代码质量等都是很重要的

如果真的出现了：

show full PROCESSLIST：查看状态，可以参考前面的属性，必要情况下 直接kill

3.5.mysql死锁

相互等待对方释放锁，形成死锁

避免死锁
1.编号解死锁：对锁编号，按顺序锁。 比如AB 每个线程都做判断，始终先锁A 在锁B

2.首先在innodb搜索引擎中，会根据算法主动进行部分死锁的检测与释放，比如上面的例子自动释放。

3.大事务拆小。大事务更倾向于死锁，大事务锁的时间长，如果业务允许，将大事务拆小（DBA建表功底）。

4.为表添加合理的索引

事务及原理

事务，由一个有限的数据库操作序列构成，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。

1.事务的特性

• 原子性 Atomicity：一个事务多个数据库操作，要么同时成功 要么同时失败 -----undo日志保证，存储一条相反的操作 ，当回滚时 执行对应的反向操作

• 一致性 Consistency：因为上面三个特性保证了 指在事务开始之前和事务结束以后，数据不会被破坏,约束一致性 业务一致性，

• 隔离性 Isolation：事务之间相互隔离，隔离级别-----mvcc

• 持久性 Durabilily：一旦事务提交或者回滚对数据的操作是永久的，哪怕停电依然提交回滚 —redo日志保证

隔离级别

• 读未提交（read-uncommitted）: 脏读，读取到未提交得事务
• 读已提交（read-committed）不可重复读 A事务读取数据 B事务修改数据 A事务再次读取 不一致
• 可重复读（repeatable-read） 避免不可重复度 幻读：A事务查询一个范围的数据，B事务插入数据，A事务查询的结果数据条数不一致
• 串行化（serializable）：锁住，A事务必须做完了 B事务才能操作

实现原理

实现隔离机制的方法主要有两种：

• 读写锁
• 一致性快照读，即 MVCC

MySql使用不同的锁策略(Locking Strategy)/MVCC来实现四种不同的隔离级别。RR、RC的实现原理跟MVCC有关，RU和Serializable跟锁有关。

读未提交（Read Uncommitted）

读未提交，采取的是读不加锁原理。

• 事务读不加锁，不阻塞其他事务的读和写
• 事务写阻塞其他事务写，但不阻塞其他事务读；

串行化（Serializable)

官方的说法:

InnoDB implicitly converts all plain SELECT statements to SELECT … FOR SHARE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

• 读加共享锁 ，所有SELECT语句会隐式转化为SELECT … FOR SHARE。读的时候阻塞其他写，读读不互斥，读写互斥
• 写加排他锁，读写互斥。如果有未提交的事务正在修改某些行，所有select这些行的语句都会阻塞。

RC,RR 及mvcc

MVCC，中文叫多版本并发控制，它是通过读取历史版本的数据，来降低并发事务冲突，从而提高并发性能的一种机制。它的实现依赖于隐式字段、undo日志、快照读&当前读、Read View

隐式字段

对于InnoDB存储引擎，每一行记录都有两个隐藏列DB_TRX_ID、DB_ROLL_PTR，如果表中没有主键和非NULL唯一键时，则还会有第三个隐藏的主键列DB_ROW_ID。

• DB_TRX_ID，记录每一行最近一次修改（修改/更新）它的事务ID，大小为6字节；
• DB_ROLL_PTR，这个隐藏列就相当于一个指针，指向回滚段的undo日志，大小为7字节；
• DB_ROW_ID，隐含的自增ID，大小为6字节；
• 实际还有一个删除flag隐藏字段, 记录被更新或删除并不代表真的删除，而是删除flag变了

column	column	column	DB_TRX_ID	DB_ROLL_PTR	DB_ROW_ID

undo日志

事务未提交的时候，修改数据的镜像（修改前的旧版本），存到undo日志里。以便事务回滚时，恢复旧版本数据，撤销未提交事务数据对数据库的影响。当对数据库操作时，undo log中存储一段对应的反向操作，当需要回滚时执行。存储undo日志的地方就是回滚段。

多个事务并行操作某一行数据时，不同事务对该行数据的修改会产生多个版本，然后通过回滚指针（DB_ROLL_PTR）连一条Undo日志链。

快照读&当前读

快照读：

读取的是记录数据的可见版本（有旧的版本），不加锁,普通的select语句都是快照读,如：

select * from account where id>2;
复制代码

当前读：

读取的是记录数据的最新版本，显示加锁的都是当前读

select * from account where id>2 lock in share mode;
select * from  account where id>2 for update;
复制代码

Read View

​ Read View就是事务执行快照读时，产生的读视图。事务执行快照读时，会生成数据库系统当前的一个快照，记录当前系统中还有哪些活跃的读写事务，把它们放到一个列表里 m_ids。Read View主要是用来做可见性判断的，即判断当前事务可见哪个版本的数据

• trx_ids: 当前系统中那些活跃的读写事务ID，数据结构为List
• min_limit_id: m_ids事务列表中，最小的事务ID
• max_limit_id: m_ids事务列表中，最大的事务ID
• creator_trx_id: 创建当前read view的事务版本号

Read View可见性判断条件

• db_trx_id < min_limit_id || db_trx_id == creator_trx_id（显示）
  • 如果数据事务ID小于read view中的最小活跃事务ID，则可以肯定该数据是在当前事务启之前就已经存在了的,所以可以显示。
  • 或者数据的事务ID等于creator_trx_id ，那么说明这个数据就是当前事务自己生成的，自己生成的数据自己当然能看见，所以这种情况下此数据也是可以显示的。
• db_trx_id > max_limit_id（不显示）

如果数据事务ID大于read view 中的当前系统的最大事务ID，则说明该数据是在当前read view 创建之后才产生的，所以数据不显示。如果小于则进入下一个判断

• min_limit_id =< DB_TRX_ID <= max_limit_id, 当前事务id在 活跃列表事务
  • 需要判断db_trx_id是否在活跃事务（trx_ids）中
  • 不存在：则说明read view产生的时候事务已经commit了，这种情况数据则可以显示。
  • 存在：则代表我Read View生成时刻，你这个事务还在活跃，还没有Commit，你修改的数据，我当前事务也是看不见的。

RR跟RC隔离级别，最大的区别就是：RC每次读取数据前都生成一个ReadView，而RR只在第一次读取数据时生成一个ReadView。

RC 情境下：

• 事务A 查询x=10 事务id为100 修改数据事务id为50 ；
• 事务B开启事务 修改x=20 事务id为200
• 事务B查询 生成最新ReadView 修改数事务id为200 但是创建ReadView的事务id也为200 所以结果为x=20
• 事务A再次查询 重新生成一个最新的ReadView 在trx_ids 中活跃的事务ID为200 ；修改数据的事务id为200 所以不可见，从之前版本中查到id为50的 查询结果：数据x=10
• 事务B 提交

事务A再次查询 重新生成一个最新的ReadView 在trx_ids 中活跃的事务ID为空，直接查询当前修改数据的事务id200 数据x=20

RR情景

每次事务A查询都是第一次的ReadView 所以活跃的事务不变 ，第一次查询后提交，第二次查询 时trx_ids 中活跃Id为 200， 第三次查询时 还是200 所以 查询结果不可见 结果x=10 达到了可重复读的效果，但是解决不了幻读

幻读优化和锁

Record Lock 行锁

对某一行的数据锁定

#显示的加写锁 
select * from  people where id =3 for update;
#显示加读锁  读读不互斥  读写互斥（阻塞其他事务的写操作）
SELECT ... FOR SHARE
# 写操作 自动开启
update people set name='James' where id=3

• 对于显示的加锁或增删改操作，条件判断必须是精确匹配（也就是=） ，不能用>,<,between或like等范围查询方式，因为这样会使行锁变成next-key Lock。
• FOR SHARE 确保查到的数据是最新的数据，并且不允许其他人来修改数据，但是可以继续添加共享锁。所以自己不一定能够修改数据，因为有可能其他的事务也对这些数据 使用了 in share mode 的方式上了 S 锁。
• FOR UPDATE 确保查到的数据是最新的数据，并且不允许其他人来修改数据，也不允许加任何的锁，允许快照读，但是阻塞当前读

Gap Lock：

间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。Gap Lock的唯一目的就是阻止其他事务插入到间隙中。Gap Lock可以同时存在，不同的事务可以同时获取相同的Gap Lock，并不会互相冲突。Gap Lock也是可以显示的被禁止的，只要将事务的隔离级别降低到 READ COMMITTED。

#InnoDB不仅会锁住id为5和6两行的数据，也会锁住id为4(虽然该行并不存在)的纪录。
select * from  people where id > 3 AND id <7 for update;

• Next-Key Lock：

锁定一个范围，并且锁定记录本身**。根据索引会形成一个个左开右闭的一个区间，根据查询的条件其所在的区间，并且包括其后的区间。
（1）防止间隙内有新数据被插入
（2）防止已存在的数据，更新成间隙内的数据

people表

id	name	age
1	JAMES	37
2	OVEN	28
3	LOVE	34

如果age是索引的话，相关的区域有
(-无穷,28]
(28,34]
(34,37]
(37,+无穷)

#那么会锁住(28,37] 
select * from  people where age =34 for update;
#锁住(28,34)这么范围
select * from  people where age =33 for update;

innodb自动使用间隙锁的条件：
（1）必须在Repeatable Read级别下
（2）检索条件必须有普通索引（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

**注意：这里的普通索引不包括主键索引和唯一索引，如果在这两个索引下因为能精确检索出结果，所以会使用Record Lock直接锁定具体的行（范围查询除外）。

mysql优化

修改myql配置

找到mysql该目录下的my.ini，对里面的参数进行优化，以下=后边的参数为我已经修改后的参数，即本地优化后的。

• datadir= 修改实际mysql数据目录存储地址，以免出现默认存到C盘去的现象
• max_connections=1500 加大数据库连接数。从原来的151修改为1500
• query_cache_size=2M 加大查询缓存。如果你查询相同sql的次数多，那么再次执行sql会直接从缓存取，如果你的表经常改变或者sql经常改变，则可能会拖慢查询速度，慎用。
• tmp_table_size=200M 这个值的最大值为内存值，如果一个表数据大于这个值，那么它自动转为为基于磁盘表。这个参数的限制是针对于表。
• key_buffer_size=0M 这个值只针对表引擎为MyISAM时。一般情况下我们建表和数据库都是为了事物支持，所以默认都是InnoDB。
• innodb_flush_log_at_trx_commit=1 有三个值0，1，2。
  • 当值为0时，大约每一秒就会把日志写入到文件，并把文件刷新到磁盘，也就是保存日志文件。
  • 当值为1时，InnoDB每次commit操作都会使事物日志刷新到磁盘，也就是日志持久化，比如日志文件
  • 当值为2时，表示每次commit时都写入日志文件，但是大约每一秒会执行一次日志刷新到磁盘操作
• innodb_log_buffer_size=5M InnoDB的log数据缓冲区大小，如果这个InnoDB设置为每一秒刷新到磁盘的话，那这个值没太大意义，即使是长事物。

慢查询定位/慢sql

#可以通过sql日志 或者druid 查看哪些操作是慢sql  然后对sql进行优化
slow_query_log = ON   #开启慢查询
slow_query_log_file =D://my_log.log  #日志位置
long_query_time =1  #慢查询实际   这儿超过一秒就被认为是慢查询，mysql默认10秒

增删改查 超过1秒就是慢查询

druid中定位

# 通过connectProperties属性来打开mergeSql功能；慢SQL记录  慢查询：增删改查 慢查询
spring.datasource.druid.connection-properties=druid.stat.mergeSql=true;
spring.datasource.druid.filter.stat.slow-sql-millis=1

explain分析sql

在MySQL的sql语句后加上\G，表示将查询结果进行按列打印，可以使每个字段打印到单独的行。即将查到的结构旋转90度变成纵向；

#在 查询语句前使用explain 
EXPLAIN SELECT * from user_info WHERE  id < 300;

https://segmentfault.com/a/1190000008131735

mysql> explain select * from user_info where id = 2\G
*************************** 1. row ***************************
           id: 1 #SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
  select_type: SIMPLE #查询的类型
        table: user_info #查询的是哪个表
   partitions: NULL #匹配的分区
         type: const  #常用类型
possible_keys: PRIMARY #此次查询中可能选用的索引
          key: PRIMARY #此次查询中确切使用到的索引.
      key_len: 8 #索引长度
          ref: const  #哪个字段或常数与 key 一起被使用
         rows: 1   #显示此查询一共扫描了多少行. 这个是一个估计值
     filtered: 100.00  #表示此查询条件所过滤的数据的百分比
        Extra: NULL  
1 row in set, 1 warning (0.00 sec)

select_type

select_type 表示了查询的类型, 它的常用取值有:

• SIMPLE, 表示此查询不包含 UNION 查询或子查询
• PRIMARY, 表示此查询是最外层的查询
• UNION, 表示此查询是 UNION 的第二或随后的查询
• DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
• UNION RESULT, UNION 的结果
• SUBQUERY, 子查询中的第一个 SELECT
• DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.

type 常用类型

type 常用的取值有:

• system: 表中只有一条数据. 这个类型是特殊的 const 类型.

• const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.
  例如下面的这个查询, 它使用了主键索引, 因此 type 就是 const 类型的.

  select * from user_info where id = 2
  

• eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是=`, 查询效率较高. 例如:

  EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id\G
  

• ref: 此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.

  EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id AND order_info.user_id = 5\G
  

• range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
  当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个.

• index: 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.
  index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index.

• ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.

type 类型的性能比较

通常来说, 不同的 type 类型的性能关系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < system

rows

rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

Extra

EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:

• Using filesort
  当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.

• Using index
  “覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错

• Using temporary
  查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.

3.Scheme设计与数据类型优化

选择数据类型只要遵循小而简单的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少。越简单的数据类型在计算时需要更少的CPU周期，比如，整型就比字符操作代价低，因而会使用整型来存储ip地址，使用DATETIME来存储时间，而不是使用字符串。

这里总结几个可能容易理解错误的技巧：

1. 通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT NULL。
2. 对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。
3. UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255。
4. 通常来讲，没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。
5. TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。
6. 大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。
7. schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。
8. 大表ALTER TABLE非常耗时，MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久。当然有一些奇技淫巧可以解决这个问题，有兴趣可自行查阅。

创建高性能索引

索引是提高MySQL查询性能的一个重要途径，但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用，从而影响应用程序的整体性能。应当尽量避免事后才想起添加索引，因为事后可能需要监控大量的SQL才能定位到问题所在，而且添加索引的时间肯定是远大于初始添加索引所需要的时间，可见索引的添加也是非常有技术含量的。

接下来将向你展示一系列创建高性能索引的策略，以及每条策略其背后的工作原理。但在此之前，先了解与索引相关的一些算法和数据结构，将有助于更好的理解后文的内容。

根据索引失效原因设计索引

前缀索引

如果列很长，通常可以索引开始的部分字符，这样可以有效节约索引空间，从而提高索引效率。

3、多列索引和索引顺序

在多数情况下，在多个列上建立独立的索引并不能提高查询性能。理由非常简单，MySQL不知道选择哪个索引的查询效率更好，所以在老版本，比如MySQL5.0之前就会随便选择一个列的索引，**而新的版本会采用合并索引的策略。**举个简单的例子，在一张电影演员表中，在actor_id和film_id两个列上都建立了独立的索引，然后有如下查询：

select film_id,actor_id from film_actor where actor_id = 1 or film_id = 1

老版本的MySQL会随机选择一个索引，但新版本做如下的优化：

select film_id,actor_id from film_actor where actor_id = 1  
union all 
select film_id,actor_id from film_actor where film_id = 1 and actor_id <> 1

• 当出现多个索引做相交操作时（多个AND条件），通常来说一个包含所有相关列的索引要优于多个独立索引。
• 当出现多个索引做联合操作时（多个OR条件），对结果集的合并、排序等操作需要耗费大量的CPU和内存资源，特别是当其中的某些索引的选择性不高，需要返回合并大量数据时，查询成本更高。所以这种情况下还不如走全表扫描。

因此explain时如果发现有索引合并（Extra字段出现Using union），应该好好检查一下查询和表结构是不是已经是最优的，如果查询和表都没有问题，那只能说明索引建的非常糟糕，应当慎重考虑索引是否合适，有可能一个包含所有相关列的多列索引更适合。

索引选择性

是指不重复的索引值和数据表的总记录数的比值，选择性越高查询效率越高，因为选择性越高的索引可以让MySQL在查询时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

理解索引选择性的概念后，就不难确定哪个字段的选择性较高了，查一下就知道了，比如：

SELECT * FROM payment where staff_id = 2 and customer_id = 584

是应该创建(staff_id,customer_id)的索引还是应该颠倒一下顺序？执行下面的查询，哪个字段的选择性更接近1就把哪个字段索引前面就好。

select count(distinct staff_id)/count(*) as staff_id_selectivity,
       count(distinct customer_id)/count(*) as customer_id_selectivity,
       count(*) from payment

多数情况下使用这个原则没有任何问题，但仍然注意你的数据中是否存在一些特殊情况。举个简单的例子，比如要查询某个用户组下有过交易的用户信息：

select user_id from trade where user_group_id = 1 and trade_amount > 0

MySQL为这个查询选择了索引(user_group_id,trade_amount)，如果不考虑特殊情况，这看起来没有任何问题，但实际情况是这张表的大多数数据都是从老系统中迁移过来的，由于新老系统的数据不兼容，所以就给老系统迁移过来的数据赋予了一个默认的用户组。这种情况下，通过索引扫描的行数跟全表扫描基本没什么区别，索引也就起不到任何作用。

推广开来说，经验法则和推论在多数情况下是有用的，可以指导我们开发和设计，但实际情况往往会更复杂，实际业务场景下的某些特殊情况可能会摧毁你的整个设计。

4、避免多个范围条件

实际开发中，我们会经常使用多个范围条件，比如想查询某个时间段内登录过的用户：

select user.* from user where login_time > '2017-04-01' and age between 18 and 30;

这个查询有一个问题：它有两个范围条件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但无法同时使用它们。

5、覆盖索引

如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。覆盖索引是非常有用的工具，可以极大的提高性能，因为查询只需要扫描索引会带来许多好处：

• 索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量
• 索引是有按照列值顺序存储的，对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多

6、使用索引扫描来排序

MySQL有两种方式可以生产有序的结果集，其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的。如果explain的结果中type列的值为index表示使用了索引扫描来做排序。

扫描索引本身很快，因为只需要从一条索引记录移动到相邻的下一条记录。但如果索引本身不能覆盖所有需要查询的列，那么就不得不每扫描一条索引记录就回表查询一次对应的行。这个读取操作基本上是随机I/O，因此按照索引顺序读取数据的速度通常要比顺序地全表扫描要慢。

在设计索引时，如果一个索引既能够满足排序，又满足查询，是最好的。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向也一样时，才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有ORDER BY子句引用的字段全部为第一张表时，才能使用索引做排序。ORDER BY子句和查询的限制是一样的，都要满足最左前缀的要求（有一种情况例外，就是最左的列被指定为常数，下面是一个简单的示例），其他情况下都需要执行排序操作，而无法利用索引排序。

 // 最左列为常数，索引：
 (date,staff_id,customer_id) select staff_id,customer_id from demo where
  date = '2015-06-01' order by staff_id,customer_id

7、冗余和重复索引

冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应当尽量避免这种索引，发现后立即删除。比如有一个索引(A,B)，再创建索引(A)就是冗余索引。冗余索引经常发生在为表添加新索引时，比如有人新建了索引(A,B)，但这个索引不是扩展已有的索引(A)。

大多数情况下都应该尽量扩展已有的索引而不是创建新索引。但有极少情况下出现性能方面的考虑需要冗余索引，比如扩展已有索引而导致其变得过大，从而影响到其他使用该索引的查询。

8、删除长期未使用的索引

定期删除一些长时间未使用过的索引是一个非常好的习惯。

关于索引这个话题打算就此打住，最后要说一句，索引并不总是最好的工具，只有当索引帮助提高查询速度带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，简单的全表扫描更高效。对于中到大型的表，索引就非常有效。对于超大型的表，建立和维护索引的代价随之增长，这时候其他技术也许更有效，比如分区表。最后的最后，explain后再提测是一种美德。

特定类型查询优化

1.优化COUNT()查询

COUNT()可能是被大家误解最多的函数了，它有两种不同的作用，其一是统计某个列值的数量，其二是统计行数。统计列值时，要求列值是非空的，它不会统计NULL。如果确认括号中的表达式不可能为空时，实际上就是在统计行数。最简单的就是当使用COUNT(*)时，并不是我们所想象的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计行数。

我们最常见的误解也就在这儿，在括号内指定了一列却希望统计结果是行数，而且还常常误以为前者的性能会更好。但实际并非这样，如果要统计行数，直接使用COUNT(*)，意义清晰，且性能更好。

有时候某些业务场景并不需要完全精确的COUNT值，可以用近似值来代替，EXPLAIN出来的行数就是一个不错的近似值，而且执行EXPLAIN并不需要真正地去执行查询，所以成本非常低。通常来说，执行COUNT()都需要扫描大量的行才能获取到精确的数据，因此很难优化，MySQL层面还能做得也就只有覆盖索引了。如果不还能解决问题，只有从架构层面解决了，比如添加汇总表，或者使用redis这样的外部缓存系统。

2.优化关联查询

在大数据场景下，表与表之间通过一个冗余字段来关联，要比直接使用JOIN有更好的性能。如果确实需要使用关联查询的情况下，需要特别注意的是：

• 确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器关联的顺序是A、B，那么就不需要在A表的对应列上创建索引。没有用到的索引会带来额外的负担，一般来说，除非有其他理由，只需要在关联顺序中的第二张表的相应列上创建索引（具体原因下文分析）。
• 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化。

要理解优化关联查询的第一个技巧，就需要理解MySQL是如何执行关联查询的。当前MySQL关联执行的策略非常简单，它对任何的关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后在嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为为止。然后根据各个表匹配的行，返回查询中需要的各个列。

太抽象了？以上面的示例来说明，比如有这样的一个查询：

SELECT A.xx,B.yy 
FROM A INNER JOIN B USING(c)
WHERE A.xx IN (5,6)

假设MySQL按照查询中的关联顺序A、B来进行关联操作，那么可以用下面的伪代码表示MySQL如何完成这个查询：

outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);
outer_row = outer_iterator.next;
while(outer_row) {
    inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;
    inner_row = inner_iterator.next;
    while(inner_row) {
        output[inner_row.yy,outer_row.xx];
        inner_row = inner_iterator.next;
    }
    outer_row = outer_iterator.next;
}

可以看到，最外层的查询是根据A.xx列来查询的，A.c上如果有索引的话，整个关联查询也不会使用。再看内层的查询，很明显B.c上如果有索引的话，能够加速查询，因此只需要在关联顺序中的第二张表的相应列上创建索引即可。

3.优化LIMIT分页

当需要分页操作时，通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY字句。如果有对应的索引，通常效率会不错，否则，MySQL需要做大量的文件排序操作。

一个常见的问题是当偏移量非常大的时候，比如：LIMIT 10000 20这样的查询，MySQL需要查询10020条记录然后只返回20条记录，前面的10000条都将被抛弃，这样的代价非常高。

优化这种查询一个最简单的办法就是尽可能的使用覆盖索引扫描，而不是查询所有的列。然后根据需要做一次关联查询再返回所有的列。对于偏移量很大时，这样做的效率会提升非常大。考虑下面的查询：

SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;

如果这张表非常大，那么这个查询最好改成下面的样子：

SELECT film.film_id,film.description
FROM film INNER JOIN (
    SELECT film_id FROM film ORDER BY title LIMIT 50,5
) AS tmp USING(film_id);

这里的延迟关联将大大提升查询效率，让MySQL扫描尽可能少的页面，获取需要访问的记录后在根据关联列回原表查询所需要的列。

有时候如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET，比如下面的查询：

SELECT id FROM t LIMIT 10000, 10;
改为：
SELECT id FROM t WHERE id > 10000 LIMIT 10;

其他优化的办法还包括使用预先计算的汇总表，或者关联到一个冗余表，冗余表中只包含主键列和需要做排序的列。

4.优化UNION

MySQL处理UNION的策略是先创建临时表，然后再把各个查询结果插入到临时表中，最后再来做查询。因此很多优化策略在UNION查询中都没有办法很好的时候。经常需要手动将WHERE、LIMIT、ORDER BY等字句“下推”到各个子查询中，以便优化器可以充分利用这些条件先优化。

除非确实需要服务器去重，否则就一定要使用UNION ALL，如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致整个临时表的数据做唯一性检查，这样做的代价非常高。当然即使使用ALL关键字，MySQL总是将结果放入临时表，然后再读出，再返回给客户端。虽然很多时候没有这个必要，比如有时候可以直接把每个子查询的结果返回给客户端。