sql优化问题
概要
减少磁盘io
减少磁盘随机io
如何创建表
如何创建索引
索引的数据结构
innodb和myisam的区别
如何避免索引失效
避免回表 索引覆盖 是建联合索引的原因 using index
order by using filesort
in
exist
limit
join nlj bnl
count（*） count(1) count(字段) count(id)
查看索引工具 explain trace
sql的执行过程
innodb的三大特性:buffer pool 双写缓冲区 自适应哈希
buffer pool
事务 特性 隔离级别
mvcc undo readview
锁 行锁 表多 临键锁 空隙所
数据类型的选择 数字 字符串 时间 二进制
1.优化什么
优化查询速度 避免全表扫描、避免索引不命中、避免文件排序、磁盘排序、未完待续。
2.什么是索引
是一种帮助数据库快速查询的数据结构。
2.1主键索引 聚簇索引 聚集索引 密集索引
InnoDB中使用了聚集索引，就是将表的主键用来构造一棵B+树，并且将整张表的行 记录数据存放在该B+树的叶子节点中。也就是所谓的索引即数据，数据即索引。由 于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。 聚集索引的叶子节点就是数据页。换句话说，数据页上存放的是完整的每行记录。 因此聚集索引的一个优点就是：通过过聚集索引能获取完整的整行数据。另一个优 点是：对于主键的排序查找和范围查找速度非常快。 如果我们没有定义主键呢？MySQL会使用唯一性索引，没有唯一性索引，MySQL也会 创建一个隐含列RowID来做主键，然后用这个主键来建立聚集索引。
2.1二级索引 辅助索引 稀疏索引
上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据 都是按照主键进行排序的,那如果我们想以别的列作为搜索条件怎么办？我们一般 会建立多个索引，这些索引被称为辅助索引/二级索引。 对于辅助索引(Secondary Index，也称二级索引、非聚集索引)，叶子节点并不包 含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还 包含了相应行数据的聚集索引键。
比如辅助索引index(node)，那么叶子节点中包含的数据就包括了(主键、note)。
2.3联合索引 复合索引
前面我们对索引的描述，隐含了一个条件，那就是构建索引的字段只有一个，但实 践工作中构建索引的完全可以是多个字段。所以，将表上的多个列组合起来进行索 引我们称之为联合索引或者复合索引，比如index(a,b)就是将a,b两个列组合起来 构成一个索引。 千万要注意一点，建立联合索引只会建立1棵B+树，多个列分别建立索引会分别以 每个列则建立B+树，有几个列就有几个B+树，比如，index(note)、index(b)，就 分别对note,b两个列各构建了一个索引。 index(note,b)在索引构建上，包含了两个意思： 1、先把各个记录按照note列进行排序。 2、在记录的note列相同的情况下，采用b列进行排序
2.4回表
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助 索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级 别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个 完整的行记录。这个过程也被称为回表。也就是根据辅助索引的值查询一条完整的 用户记录需要使用到2棵B+树----一次辅助索引，一次聚集索引。为什么我们还需要一次回表操作呢?直接把完整的用户记录放到辅助索引d的叶子节 点不就好了么？如果把完整的用户记录放到叶子节点是可以不用回表，但是太占地 方了，相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍，这就有点太 浪费存储空间了。而且每次对数据的变化要在所有包含数据的索引中全部都修改一 次，性能也非常低下。 很明显，回表的记录越少，性能提升就越高，需要回表的记录越多，使用二级索引 的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引。 那什么时候采用全表扫描的方式，什么时候使用采用二级索引 + 回表的方式去执 行查询呢？这个就是查询优化器做的工作，查询优化器会事先对表中的记录计算一 些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录 数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。具体怎么算的，我们后面会详细说到。
2.5MRR
从上文可以看出，每次从二级索引中读取到一条记录后，就会根据该记录的主键值 执行回表操作。而在某个扫描区间中的二级索引记录的主键值是无序的，也就是说这些 二级索引记录对应的聚簇索引记录所在的页面的页号是无序的。 每次执行回表操作时都相当于要随机读取一个聚簇索引页面，而这些随机IO带来的 性能开销比较大。MySQL中提出了一个名为Disk-Sweep Multi-Range Read (MRR，多范围 读取)的优化措施，即先读取一部分二级索引记录，将它们的主键值排好序之后再统一执 行回表操作。 相对于每读取一条二级索引记录就立即执行回表操作，这样会节省一些IO开销。使用这 个 MRR优化措施的条件比较苛刻，所以我们直接认为每读取一条二级索引记录就立即执 行回表操作。MRR的详细信息，可以查询官方文档。
2.6自适应哈希索引
InnoDB存储引擎除了我们前面所说的各种索引，还有一种自适应哈希索引，我们知 道B+树的查找次数,取决于B+树的高度,在生产环境中,B+树的高度一般为3~4层,故 需要3~4次的IO查询。 所以在InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么 就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)，创建以后，如果下次又查询到这个索引，那么直接通 过hash算法推导出记录的地址，直接一次就能查到数据，比重复去B+tree索引中查 询三四次节点的效率高了不少。 InnoDB存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。注 意，对于自适应哈希索引仅是数据库自身创建并使用的，我们并不能对其进行干 预。通过命令show engine innodb status\G可以看到当前自适应哈希索引的使用 状况，
哈希索引只能用来搜索等值的查询,如 SELECT* FROM table WHERE index co=xxx。而对于其他查找类型,如范围查找,是不能使用哈希索引的, 因此这里会显示non- hash searches/s的统计情况。通过 hash searches: nonhash searches可以大概了解使用哈希索引后的效率。 由于AHI是由 InnoDB存储引擎控制的,因此这里的信息只供我们参考。不过我们可 以通过观察 SHOW ENGINE INNODB STATUS的结果及参数 innodb_adaptive_hash_index来考虑是禁用或启动此特性,默认AHI为开启状态。 什么时候需要禁用呢？如果发现监视索引查找和维护哈希索引结构的额外开销远远 超过了自适应哈希索引带来的性能提升就需要关闭这个功能。 同时在MySQL 5.7中，自适应哈希索引搜索系统被分区。每个索引都绑定到一个特 定的分区，每个分区都由一个单独的 latch 锁保护。分区由 innodb_adaptive_hash_index_parts 配置选项控制 。在早期版本中，自适应哈希 索引搜索系统受到单个 latch 锁的保护，这可能成为繁重工作负载下的争用点。 innodb_adaptive_hash_index_parts 默认情况下，该 选项设置为8。最大设置为 512。当然禁用或启动此特性和调整分区个数这个应该是DBA的工作，我们了解即 可。
2.7索引类型
从数据结构角度可分为B+树索引、哈希索引、以及FULLTEXT索引（现在MyISAM和InnoDB 引擎都支持了）和R-Tree索引（用于对GIS数据类型创建SPATIAL索引）； 从物理存储角度可分为聚集索引（clustered index）、非聚集索引（non-clustered index）； 从逻辑角度可分为主键索引、普通索引，或者单列索引、多列索引、唯一索引、非唯一 索引等等。
2.8 什么是密集索引和稀疏索引
面试中还会被问到什么是密集索引和稀疏索引。 密集索引的定义：叶子节点保存的不只是键值，还保存了位于同一行记录里的其他列的 信息，由于密集索引决定了表的物理排列顺序，一个表只有一个物理排列顺序，所以一 个表只能创建一个密集索引。 稀疏索引：叶子节点仅保存了键位信息以及该行数据的地址，有的稀疏索引只保存了键 位信息机器主键。 mysam存储引擎，不管是主键索引，唯一键索引还是普通索引都是稀疏索引，innodb存储 引擎：有且只有一个密集索引。 所以，密集索引就是innodb存储引擎里的聚簇索引，稀疏索引就是innodb存储引擎里的 普通二级索引
2.9索引覆盖
覆盖索引也是我们经常见到的名词，InnoDB存储引擎支持覆盖索引(covering index，或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚 集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信 息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。所以记住，覆盖索 引可以视为索引优化的一种方式，而并不是索引类型的一种。 除了覆盖索引这个概念外，在索引优化的范围内，
2.10前缀索引
阿里手册中建议，这个前缀的长度为20比较合适，但是其实后面的“count(distinct left(列名, 索引长度))/count(*)的区分度来确定”会更合适点。在工程实践中具体如何做呢？ 可以这样： SELECT COUNT(DISTINCT LEFT(order_note,3))/COUNT(*) AS sel3,
前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点MySQL无法使用 前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。 有时候后缀索引 (suffix index)也有用途（例如，找到某个域名的所有电子邮件地 址)。MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀 索引。可以通过触发器或者应用程序自行处理来维护索引
2.11三星索引
· 索引将相关的记录放到一起则获得一星； 如果索引中的数据顺序和查找中的排列顺序一致则获得二星； 如果索引中的列包含了查询中需要的全部列则获得三星。
二星（排序星）： 在满足一星的情况下，当查询需要排序，group by、 order by，如果查询所需的顺序与 索引是一致的（索引本身是有序的），是不是就可以不用再另外排序了，一般来说排序 可是影响性能的关键因素。
在满足了二星的情况下，如果索引中所包含了这个查询所需的所有列（包括 where 子句 和 select 子句中所需的列，也就是覆盖索引），这样一来，查询就不再需要回表了， 减少了查询的步骤和IO请求次数，性能几乎可以提升一倍。 一星按照原文稍微有点难以理解，其实它的意思就是：如果一个查询相关的索引行是相 邻的或者至少相距足够靠近的话，必须扫描的索引片宽度就会缩至最短，也就是说，让 索引片尽量变窄，也就是我们所说的索引的扫描范围越小越好。 这三颗星，哪颗最重要？第三颗星。因为将一个列排除在索引之外可能会导致很多磁盘 随机读（回表操作）。第一和第二颗星重要性差不多，可以理解为第三颗星比重是50%， 第一颗星为27%，第二颗星为23%，所以在大部分的情况下，会先考虑第一颗星，但会根 据业务情况调整这两颗星的优先度。
2.12索引的开销
1、一个索引就是一个B+树，索引让我们的查询可以快速定位和扫描到我们需要的数据 记录上，加快查询的速度。
2、一个select查询语句在执行过程中一般最多能使用一个二级索引来加快查询，即使 在where条件中用了多个二级索引。
每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲 过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是 叶子节点中的记录，还是非叶子内节点中的记录都是按照索引列的值从小到大的顺序而 形成了一个单向链表。 而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间 进行一些记录移位，页面分裂、页面回收的操作来维护好节点和记录的排序。如果我们 建了许多索引，每个索引对应的B+树都要进行相关的维护操作，这必然会对性能造成影 响。 既然索引这么有用，我们是不是创建越多越好？既然索引有代价，我们还是别创建了 吧？当然不是！按照经验，一般来说，一张表6-7个索引以下都能够取得比较好的性能权 衡。
2.13磁盘和B+树
一个磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动（各个磁盘必须同步转动）。在磁 盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不 能转动，但是可以沿磁盘半径方向运动。 盘片被划分成一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道，所有半径相同的磁 道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁 盘的最小存储单元也是最小读写单元。现在磁盘扇区一般是512个字节~4k个字节。 磁盘上数据必须用一个三维地址唯一标示：柱面号、盘面号、扇区号。 读/写磁盘上某一指定数据需要下面步骤： (1) 首先移动臂根据柱面号使磁头移动到所需要的柱面上，这一过程被称为定位或查找。 (2)所有磁头都定位到磁道上后，这时根据盘面号来确定指定盘面上的具体磁道。 (3) 盘面确定以后，盘片开始旋转，将指定块号的磁道段移动至磁头下。 经过上面步骤，指定数据的存储位置就被找到。这时就可以开始读/写操作了
可以看见，磁盘读取依靠的是机械运动，分为寻道时间、旋转延迟、传输时间三个部分，这三 个部分耗时相加就是一次磁盘IO的时间，一般大概9ms左右。寻道时间（seek）是将读写磁头 移动至正确的磁道上所需要的时间，这部分时间代价最高；旋转延迟时间（rotation）是磁盘 旋转将目标扇区移动到读写磁头下方所需的时间，取决于磁盘转速；数据传输时间 （transfer）是完成传输数据所需要的时间，取决于接口的数据传输率，在纳秒级，远小于前 两部分消耗时间。磁盘读取时间成本是访问内存的几百倍到几万倍之间。 为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每 次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据 放入内存，这个称之为预读。这样做的理论依据是计算机科学中著名的局部性原理： 当一个数据被用到时，其附近的数据也通常会马上被使用。 程序运行期间所需要的数据通常比较集中。
就能看到局部性原理对程序性能的影响。 由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），一般来说，磁盘的 顺序读的效率是随机读的40到400倍都有可能，顺序写是随机写的10到100倍（SSD盘则差距 要小的多，顺序读写的效率是随机读写的7到10倍，但是有评测表明机械硬盘的顺序写性能稍 优于SSD。总的来说Mysql数据库如果由硬盘由机械的换成SSD的，性能会有很大的提升）， 因此对于具有局部性的程序来说，预读可以提高I/O效率。 预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往 往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页，页大小通常为4k当 然也有16K的，主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发 一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取 一页或几页载入内存中，然后异常返回，程序继续运行。 按照磁盘的这种性质，如果是一个页存放一个B+树的节点，自然是可以存放很多的数据的，比 如InnoDB里，默认定义的B+树的节点大小是16KB，这就是说，假如一个Key是8个字节，那 么一个节点可以存放大约1000个Key，意味着B+数可以有1000个分叉。同时InnoDB每一次磁 盘I/O，读取的都是 16KB的整数倍的数据。也就是说InnoDB在节点的读写上是可以充分利用 磁盘顺序IO的高速读写特性。 同时按照B+树逻辑结构来说，在叶子节点一层，所有记录的主键按照从小到大的 顺序排列，并且形成了一个双向链表。同一层的非叶子节点也互相串联，形成了一 个双向链表。那么在实际读写的时候，很大的概率相邻的节点会放在相邻的页上， 又可以充分利用磁盘顺序IO的高速读写特性。所以我们对MySQL优化的一大方向 就是尽可能的多让数据顺序读写，少让数据随机读写
2.14索引的数据页
数据页一般是16k 数据页之间有双向指针，数据页里面是数据是单向指针 从小到大排列，如果出现增、删、该的操作 会对数据页进行分裂 增加删减的现象。
3.为什么选择b+ tree
提高查询速度，减少磁盘io
从文章的题目开始就是一个问题，我们并没有直接回答为什么 MySQL 采用 B+树作为索引的答案，而是相反的问出了两个疑惑我，但不知道有没有疑惑到你的问题，一个问题是那么多数据结构，为什么选树结构？另一个问题是那么多的树结构，又为什么偏偏采用 B+树？
要得到果，得先知道因，我们从两个方面开始出发，因为 MySQL 的数据是放在磁盘的，而磁盘的处理速度是毫秒级别的，如果在磁盘 IO 做过多查询操作，会给查询带来负担，所以要尽少在磁盘 I/O 操作中做查询
另一个是从索引本身的首要目的，要能按区间高效地范围查找。
有了因，我们就开始去探索果，我们就可以先来回答第一个问题，“那么多数据结构，为什么选树结构？”
在其他数据结构中按逻辑结构的线性结构有哈希表和跳表。哈希表底层基于数组，虽然可以高效查询，但是只能等值查询，而不能支持范围查询。
而跳表底层是链表，通过索引层可以实现高效的区间查询，但是随着数据量的递增，索引层也随着数据量的增多而增加。
所以采用树的数据结构，树结构其特性决定了遍历数据方式本身就纯天然的支持按区间查询。
树结构在插入等操作不用线性结构数组的开销，所以更适合插入更新等动态操作的数据结构。
接着我们另一个问题，“那么多的树结构，又为什么偏偏采用 B+树？” 我们从树结构中父节点最多只能有两个子节点的二叉树，再从二叉树加上二分查找的二叉查找树，二叉树展现了高效的查询能力；但二叉查找树在极端情况下会退化成链表。
所以进阶到自平衡二叉树，自平衡二叉树约束了每个节点的左右子树相差不能大于 1。
但是二叉树因为只能最多是两个子节点，所以树的高度过高会导致磁盘做过多 I/O 的查询操作负担。
所以最后真正到了 B 树，B 树是多叉树，但只能高效单查询，并不能高效区间查询。
所以才有 B+树，B+树是 B 树的升级，所有非叶子节点都用来做索引，只有叶子节点存储数据而且是有序双向的链表，树节点做了冗余，相比于 B 树既能支持高效的区间查询，插入和删除都比 B 树更加出色
3.1 各个数据结构的特点
链表：查询次数O(n)
HASH桶 查询速度很快，但是对于 范围查询、不等于等不能查询 、hash冲突
二叉树：可能成为链表。树的高度比较高、查询次数比较多
b树：相对降低了io次数，但是因为数据存在各个节点中，减少了数据页承载数据的量，所以不如b+树
b+树：非叶子节点存在冗余、叶子节点存在双向指针、每个数据也可容纳更多的数据、高度为三的b+树可达到4千万级数据
3.2主键
1.建议设置成整型的自增主键 innodb的主键和数据存在一起 是聚簇索引
innodb 选择索引额的顺序：主键id 唯一索引 自建的rowid
2.整型 相对比其他数据类型所占字节少、自增避免数据库做树优化浪费性能、不增加主键数据库innodb会自动生成
4.sql 执行的过程
客户端---连接器--缓存---分析器--有优化器--执行器---执行引擎
4.1.客户端：navicat 等
4.2.连接器：经过tcp的三次握手连接后，先校验账号密码，在把权限缓存，后续的操作权限都是这个缓存中获取，所以即使管理员更改了权限也要等再次连接的时候生效。
查看连接的进程：show processlist; kill id 杀掉进程；
默认休眠时间：8小时
show global variables like 'wait_timeout';
set global wait_timeout=28801
密码错误
解决办法：在my.cnf 中添加 skip-grant-tables 重启mysql 登录时不用输入密码即可登录
mysql8 修改密码 alter user 'root'@'localhost' identified with mysql_native_password by '新密码';
mysql8之前修改密码：update mysql.user set authentication_string=password('新密码') where user='root'
更新密码 flush privileges;
然后把my.cnf中的skip-grant-tables 注释掉 重启mysql service mysqld restart
密码不合法 因为我这边没有装密码验证工具 所以没验证，以待后面遇到存证
show variables like 'validate_password%'
大小写敏感报错
我用的是mysq5.7 版本 所以
show variables like '%expire_logs_days%' ;
set global expire_logs_days=30
4.3缓存：
查看缓存是否开启 show global variables like "%query_cache_type%"; 0 off 1:on 2 :demand (查询字段前面增加 SQL_CACHE) demand 比较灵活
查看缓存命中的情况:show status like "%Qcache%";
Qcache_free_blocks:表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理。
Qcache_free_memory:查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整。
Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。
Qcache_inserts:表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数，次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。
Qcache_not_cached:表示因为query_cache_type的设置而没有被缓存的查询数量。
Qcache_queries_in_cache:当前缓存中缓存的查询数量。
Qcache_total_blocks:当前缓存的block数量。
4.4分析器：
词法分析器 ---语法分析器---语义分析器---构造语法树-生成执行计划 sql语句写错了就在此步骤发现 sql error
4.5 优化器：
优化执行所选用的索引、语句的调整、等
4.6执行器：调用引擎接口执行sql
4.7用到的sql
连接数据库：mysql -uroot -p
创建用户： create user 'xxx'@'%' identified by '123456';
赋予权限： grant all privileges on *.* to 'xxx'@"%";
刷新数据库：flush privileges;
查看用户全新：show grants for 'xxx'@'%';
更改密码：alter user 'xxx'@'%' identified with mysql_native_password by '654321';
更改用户权限：update user set host='localhost' where user='xxx';
查看表结构: describe user;
创建索引：alter tablename add index indexname(colum);
清空表：
关闭mysql5.7新特性对衍生表的合并优化：set session optimizer_switch='derived_merge=off';
还原默认配置：set session optimizer_switch='derived_merge=on';
4.4.bin_log
server层实现、逻辑日志、不限大小追加写入
查看是否开启：show variables like '%log_bin%';
创建新的log文件：flush logs;
清空所有日志：reset master;
显示最后一个日志的信息：show master status;
查看日志保存时间：show variables like '%expire_logs_days%' ;
设置日志保存时间：set global expire_logs_days=30
select @@max_binlog_size;
select @@binlog_format
开启bin log 在my.cnf中进行配置
log_bin=路径，配置前需要创建好 并且赋予权限
server_id=1
binlog_format=ROW statement 存储的是要执行的sql 存储的数据小，效率快 但是可能出错，row 存储的是执行后的结果，效率慢 但是比较安全，mixed存贮的前两者的结合。
sync_binlog=1
binlog文件是不可读的二进制文件，
读取binlog文件：/usr/bin/mysqlbinlog --no-defaults /之前配置的路径/mysql-bin.000001
/usr/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001;
读取id：begin-commit 前后的id
恢复全部数据：/usr/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001|mysql -uroot -p 数据库名称
恢复指定位置数据：/usr/bin/mysqlbinlog --no-defaults --start-position="123" --end-position="dfd" /usr/local/mysql/data/binlog/mysql-bin.000001|mysql -uroot -p 数据库名称
按照时间恢复数据: /usr/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 --start-date="2020-03-06 12:00:00" --stop-date="2020-03-06 12:00:00"|mysql -uroot -p 数据库名称
5.优化sql 的工具
5.1 .工具介绍 explain show warning trace
5.2. explain
名词解释：模拟优化器执行sql语句，分析查询语句后者结构的性能瓶颈，执行计划，未运行sql语句
id: 根据select 出现的顺序依次增加，id越大执行优先级越高，id形同先执行前面的。null最后执行
select_type: simple 简单查询、primary 复杂查询最外层的select 、subquery 非from 后面的查询 derived 子查询包含在from 后面的 将结果存在一个临时表中、union union中的第二个select
table: select_type +id 或者是表名
type: null 查询字段是主键的最大值或者最小值、system ：是const的特例 表里只有一元祖匹配、const 唯一索引 只有一条匹配 只读取一次速度比较快、eq_ref 唯一索引 全连接 只返回一条 简单的查询不会出现这种、ref 非唯一索引 或唯一索引的前缀 可能返回多行、range 范围查询、index 非主键索引的全表扫描、all 主键索引的全表扫描
possible_key：可能用到的索引 但不一定会用到
key:用到的索引，在查询中可使用force index() ignore index(),
key_len：显示使用索引的字节数，索引长度，用于计算联合索引中用到的索引长度
字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节
char(n)：如果存汉字长度就是3n字节
varchar(n)：如果存汉字则长度是3n+2字节，加的2字节用来存储字符串长度，因为varchar是变长字符串数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节时间类型
date：3字节
timestamp：4字节
datetime：8字节
如果允许为null 会多出来一个字节
ref:这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
rows:这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
filter:是一个半分比的值，rows*filtered/100可以估算出将要和explain中前一个表进行连接的行数（前一个表指explain中的id值比当前表id值小的
extre:这一列展示的是额外信息。常见的重要值如下：
using index 覆盖索引 查询的字段在索引中可查到不用回表
using where 查询中使用where 并且查询的列未被索引覆盖
using index condition 查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
using temporary 使用了临时表 一般需要优化，
using filesort 将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
Select tables optimized away： 使用某些聚合函数（比如max、min）来访问存在索引的某个字段是
5.3. show warning
在explain 后面紧跟show warning 可展示出优化后的sql 不一定可以执行
5.4 .开启看sql的 评估过程
set session optimizer_trace="enabled=on",end_markers_in_json=on;‐‐开启trace
查询语句后紧跟 SELECT*FROMinformation_schema.OPTIMIZER_TRACE; 可在其中看到优化过程
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on; --开启trace
mysql> select * from employees where name > 'a' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
查看trace字段：
{
"steps": [
{
"join_preparation": { --第一阶段：SQL准备阶段，格式化sql
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { --第二阶段：SQL优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { --条件处理
"condition": "WHERE",
"original_condition": "(`employees`.`name` > 'a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`employees`.`name` > 'a')"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [ --表依赖详情
{
"table": "`employees`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [ --预估表的访问成本
{
"table": "`employees`",
"range_analysis": {
"table_scan": { --全表扫描情况
"rows": 10123, --扫描行数
"cost": 2054.7 --查询成本
} /* table_scan */,
"potential_range_indexes": [ --查询可能使用的索引
{
"index": "PRIMARY", --主键索引
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_position", --辅助索引
"usable": true,
"key_parts": [
"name",
"age",
"position",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": { --分析各个索引使用成本
"range_scan_alternatives": [
{
"index": "idx_name_age_position",
"ranges": [
"a < name" --索引使用范围
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false, --使用该索引获取的记录是否按照主键排序
"using_mrr": false,
"index_only": false, --是否使用覆盖索引
"rows": 5061, --索引扫描行数
"cost": 6074.2, --索引使用成本
"chosen": false, --是否选择该索引
"cause": "cost"
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`employees`",
"best_access_path": { --最优访问路径
"considered_access_paths": [ --最终选择的访问路径
{
"rows_to_scan": 10123,
"access_type": "scan", --访问类型：为scan，全表扫描
"resulting_rows": 10123,
"cost": 2052.6,
"chosen": true, --确定选择
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 10123,
"cost_for_plan": 2052.6,
"sort_cost": 10123,
"new_cost_for_plan": 12176,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`employees`.`name` > 'a')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`employees`",
"attached": "(`employees`.`name` > 'a')"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`employees`.`position`",
"items": [
{
"item": "`employees`.`position`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`employees`.`position`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
] /* steps */,
"index_order_summary": {
"table": "`employees`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "unknown",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [
{
"table": "`employees`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { --第三阶段：SQL执行阶段
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描
mysql> select * from employees where name > 'zzz' order by position;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
查看trace字段可知索引扫描的成本低于全表扫描，所以mysql最终选择索引扫描
mysql> set session optimizer_trace="enabled=off"; --关闭trace
6.sql 查询的三个途径 全表扫描、走主键、走非聚簇索引
7.影响sql选择查询途径的原因
扫描行数、回表次数（覆盖索引、索引下推）、是否走索引 （是否有序）
7.1.扫描行数
举例：
7.2回表次数
举例:
7.3是否可用索引：是否有序
7.4 覆盖索引
8.优化实例
8.1order by
索引排序 索引必须有序，联合索引可能会受左边索引影响导致索引排序失败，排序字段和where条件也需要遵循最左原则，并且排序顺序要一致 不能出现desc asc 这样
优化总结：
1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
2、orderby满足两种情况会使用Usingindex。1)orderby语句使用索引最左前列。2)使用where子句与orderby子句条件列组合满足索引最左前列。
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4、如果orderby的条件不在索引列上，就会产生Usingfilesort。
5、能用覆盖索引尽量用覆盖索引
6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。
Usingfilesort文件排序原理详解filesort文件排序方式
1、单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示<sort_key,additional_fields>或者<sort_key,packed_additional_fields>
2、双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID，然后在sortbuffer中进行排序，排序完后需要再次取回其它需要的字段；
用trace工具可以看到sort_mode信息里显示<sort_key,rowid>
3、MySQL通过比较系统变量max_length_for_sort_data(默认1024字节)的大小和需要查询的字段总大小来判断使用哪种排序模式。如果字段的总长度小于max_length_for_sort_data，那么使用单路排序模式；如果字段的总长度大于max_length_for_sort_data，那么使用双路排序模∙式。
mysql> select * from employees where name = 'zhuge' order by position;
mysql> select * from information_schema.OPTIMIZER_TRACE;
trace排序部分结果：
"join_execution": { --Sql执行阶段
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": { --文件排序信息
"rows": 10000, --预计扫描行数
"examined_rows": 10000, --参与排序的行
"number_of_tmp_files": 3, --使用临时文件的个数，这个值如果为0代表全部使用的sort_buffer内存排序，否则使用的磁盘文件排序
"sort_buffer_size": 262056, --排序缓存的大小，单位Byte
"sort_mode": "<sort_key, packed_additional_fields>" --排序方式，这里用的单路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
mysql> set max_length_for_sort_data = 10; --employees表所有字段长度总和肯定大于10字节
mysql> select * from employees where name = 'zhuge' order by position;
mysql> select * from information_schema.OPTIMIZER_TRACE;
trace排序部分结果：
"join_execution": {
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`employees`",
"field": "position"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 10000,
"examined_rows": 10000,
"number_of_tmp_files": 2,
"sort_buffer_size": 262136,
"sort_mode": "<sort_key, rowid>" --排序方式，这里用的双路排序
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
mysql> set session optimizer_trace="enabled=off"; --关闭trace
注意，如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。
注意，如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。
8.2 group by
后面可以 desc asc 其余规则同 order by
8.3 覆盖索引 查询的所有列都在索引树中 不用回表 推荐使用
8.4 索引下推 xx% 会走索引下推
索引下推（Index Condition Push down，ICP）,like KK%其实就是用到了索引下推优化什么是索引下推了？对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position = 'manager' 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是'LiLei'开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。MySQL5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是'LiLei'开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。
8.5 最左原则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。 除了索引下推，只有当前数据是有序的才可使用索引 。
如出现范围符号 > < 等 后面的索引可能无需不能使用索引了
优化器会优化索引的顺序 以尽最大的可能使用最多的索引
使用几个索引可通过key_len计算
8.6 索引失效
原因：const成本较大
1.联合索引后面的索引可能是无序的
2.索引上做了函数计算，类型转换
3.!= 、 <>、 or、 in、 not in、 not exists、 < 、 > 、>=、 <=、一般根据const成本计算可能会导致全表扫描
4.is null、is not null 一般也无法使用索引
5.字符串不加'' 会触发数据类型转换的函数 导致索引失效
6.联合索引第一个字段用范围不会走索引
9索引创建原则
9.1 代码先行 索引后上
9.2联合索引尽量覆盖条件
比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、orderby、groupby的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。
9.3 不要在小基数字段上创建索引
索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。
9.4长字符可以采用前缀索引
尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于KEYindex(name(20),age,position)。此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。但是假如你要是orderbyname，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的，groupby也是同理。所以这里大家要对前缀索引有一个了解。
9.5where order by冲突先where
在where和orderby出现索引设计冲突时，到底是针对where去设计索引，还是针对orderby设计索引？到底是让where去用上索引，还是让orderby用上索引?一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多。
9.6基于慢sql 进行优化
可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。关于慢sql查询不清楚的可以参考这篇文章：https://blog.csdn.net/qq_40884473/article/details/89455740
10 部分sql 关键字执行的过程
10.1 分页 limit
过程：把全部数据查不出来 然后取后面的几条 所以查询大表后面的数据 效率是很低的
优化：id自增连续、结果根据主键排序的情况下,id先查询范围 >800 limit 1
优化2：子查询出id 然后去聚簇索引中查询具体数据
select id from tablename 出现的数据不是有序的 但是select * from tablename 查询出来是时间是有序的 按照主键排序的
10.2join 联合查询
NLJ Nested-loop join 嵌套循环连接算法 被驱动表的关联字段是索引的情况下
1.从表t2中读取一行数据（如果t2表有查询过滤条件的，会从过滤结果里取出一行数据）；
2.从第1步的数据中，取出关联字段a，到表t1中查找；
3.取出表t1中满足条件的行，跟t2中获取到的结果合并，作为结果返回给客户端；
4.重复上面3步。
扫描行数：驱动表全表扫描行数*2
BNL block-nested-loop join 基于块的嵌套循环连接算法：Extra中的Using join buffer(Block Nested Loop)说明该关联查询使用的是BNL算法。 被驱动表所连接字段不是索引
1.把t2的所有数据放入到join_buffer中
2.把表t1中每一行取出来，跟join_buffer中的数据做对比
3.返回满足join条件的数据
两个全表扫描
扫描行数：驱动表全表扫描行数+被驱动表全表扫描行数 + join buffer 中两个行数的乘机 次比对
优化：
1.关联字段加索引，让mysql做join操作时尽量选择NLJ算法
2.小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间
straight_join解释：straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。比如：select * from t2 straight_join t1 on t2.a = t1.a;代表指定mysql选着t2表作为驱动表。straight_join只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序）尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。
对于小表定义的明确在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。
10.3 in 小表驱动大表，即小的数据集驱动大的数据集 当B表的数据集小于A表的数据集时，in优于exists
10.4 exist 当A表的数据集小于B表的数据集时，exists优于in
将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留
1、EXISTS(subquery)只返回TRUE或FALSE,因此子查询中的SELECT *也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别
2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析
10.5 select * 、select 1、select id 、select name
count(字段)：字段是索引字段 走非聚簇索引，比主键索引小，则比count(id) 快，若果为非索引字段不能走索引则比count(id) 慢，并且不统计为null的值
count(1):和count(字段)执行过程类似,不过不取出字段，只以1计数 所以比count(字段快一点)
count(*):count(*)是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代count(*)。
count(id):为什么对于count(id)，mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索性能应该更高，mysql内部做了点优化(应该是在5.7版本才优化)。
10.6 表的总行数
1、查询mysql自己维护的总行数对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算
对于innodb存储引擎的表mysql不会存储表的总记录行数(因为有MVCC机制，后面会讲)，查询count需要实时计算
2、show tables tatus如果只需要知道表总行数的估计值可以用如下sql查询，性能很高
3、将总数维护到Redis里插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性
4、增加数据库计数表插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作
10.6 MySQL数据类型选择
|
类型
|
大小
|
范围（有符号）
|
范围（无符号）
|
用途
|
|
TINYINT
|
1 字节
|
(-128, 127)
|
(0, 255)
|
小整数值
|
|
SMALLINT
|
2 字节
|
(-32 768, 32 767)
|
(0, 65 535)
|
大整数值
|
|
MEDIUMINT
|
3 字节
|
(-8 388 608, 8 388 607)
|
(0, 16 777 215)
|
大整数值
|
|
INT或INTEGER
|
4 字节
|
(-2 147 483 648, 2 147 483 647)
|
(0, 4 294 967 295)
|
大整数值
|
|
BIGINT
|
8 字节
|
(-9 233 372 036 854 775 808, 9 223 372 036 854 775 807)
|
(0, 18 446 744 073 709 551 615)
|
极大整数值
|
|
FLOAT
|
4 字节
|
(-3.402 823 466 E+38, 1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)
|
0, (1.175 494 351 E-38, 3.402 823 466 E+38)
|
单精度
浮点数值
|
|
DOUBLE
|
8 字节
|
(1.797 693 134 862 315 7 E+308, 2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308)
|
0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308)
|
双精度
浮点数值
|
|
DECIMAL
|
对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2
|
依赖于M和D的值
|
依赖于M和D的值
|
小数值
|
- 如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
- 建议使用TINYINT代替ENUM、BITENUM、SET。
- 避免使用整数的显示宽度(参看文档最后)，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用INT。
- DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置。
- 建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。
- 整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。
|
类型
|
大小
(字节)
|
范围
|
格式
|
用途
|
|
DATE
|
3
|
1000-01-01 到 9999-12-31
|
YYYY-MM-DD
|
日期值
|
|
TIME
|
3
|
'-838:59:59' 到 '838:59:59'
|
HH:MM:SS
|
时间值或持续时间
|
|
YEAR
|
1
|
1901 到 2155
|
YYYY
|
年份值
|
|
DATETIME
|
8
|
1000-01-01 00:00:00 到 9999-12-31 23:59:59
|
YYYY-MM-DD HH:MM:SS
|
混合日期和时间值
|
|
TIMESTAMP
|
4
|
1970-01-01 00:00:00 到 2038-01-19 03:14:07
|
YYYYMMDDhhmmss
|
混合日期和时间值，时间戳
|
- MySQL能存储的最小时间粒度为秒。
- 建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。
- 用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。
- 当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），MySQL会自动返回记录插入的确切时间。
- TIMESTAMP是UTC时间戳，与时区相关。
- DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。
- 除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一般会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。
- 有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处，这种格式处理起来不太方便，我们并不推荐它。
|
类型
|
大小
|
用途
|
|
CHAR
|
0-255字节
|
定长字符串，char(n)当插入的字符数不足n时(n代表字符数)，插入空格进行补充保存。在进行检索时，尾部的空格会被去掉。
|
|
VARCHAR
|
0-65535 字节
|
变长字符串，varchar(n)中的n代表最大字符数，插入的字符数不足n时不会补充空格
|
|
TINYBLOB
|
0-255字节
|
不超过 255 个字符的二进制字符串
|
|
TINYTEXT
|
0-255字节
|
短文本字符串
|
|
BLOB
|
0-65 535字节
|
二进制形式的长文本数据
|
|
TEXT
|
0-65 535字节
|
长文本数据
|
|
MEDIUMBLOB
|
0-16 777 215字节
|
二进制形式的中等长度文本数据
|
|
MEDIUMTEXT
|
0-16 777 215字节
|
中等长度文本数据
|
|
LONGBLOB
|
0-4 294 967 295字节
|
二进制形式的极大文本数据
|
|
LONGTEXT
|
0-4 294 967 295字节
|
极大文本数据
|
- 字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。
- CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计算的准确性和完整性。
- 尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。
- BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。
- BLOB和TEXT都不能有默认值。
