weixin_59868842的博客

mysql的3大日志、redolog、undolog、binlog

mysql锁的类型，行锁、表锁、间隙锁、意向锁、共享锁、独占锁、死锁的产生以及mysql解决方案

Mysql，MVCC架构介绍，以及脏读、不可重复读、幻读、问题的产生，以及mysql中是如何解决脏读、不可重复读、幻读、问题的

1表示将缓存所有结果，除非sql语句中使用sql_no_cache禁用查询缓存。2表示只缓存select语句中通过sql_cache指定需要缓存的查询。：缓存中相邻内存块的个数，如果值比较大，那么查询缓存中碎片比较多。：多少条query因为内存不足而被移除cache。：当前cache中缓存的query数量。：当前cache中block的数量。：查询缓存中剩余的内存大小。：表示多少次未命中而插入。：表示有多少此命中缓存。

对于用户而言，分区表是一个独立的逻辑表，但是底层是由多个物理子表组成。分区表对于用户而言是一个完全封装底层实现的黑盒子，对用户而言是透明的，从文件系统中可以看到多个使用#分隔命名的表文件。mysql在创建表时使用partition by子句定义每个分区存放的数据，在执行查询的时候，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询就无须扫描所有分区。分区的主要目的是将数据安好一个较粗的力度分在不同的表中，这样可以将相关的数据存放在一起。

1、无法使用查询缓存2、不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名或者limit子句3、用户自定义变量的生命周期是在一个连接中有效，所以不能用它们来做连接间的通信4、不能显式地声明自定义变量地类型5、mysql优化器在某些场景下可能会将这些变量优化掉，这可能导致代码不按预想地方式运行6、赋值符号：=的优先级非常低，所以在使用赋值表达式的时候应该明确的使用括号7、使用未定义变量不会产生任何语法错误。

,mysql使用内存进行快速排序操作，如果内存不够排序，那么mysql就会先将树分块，对每个独立的块使用快速排序进行排序，并将各个块的排序结果存放再磁盘上，然后将各个排好序的块进行合并，最后返回排序结果。先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序IO读取所有的数据，而无须任何的随机IO，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据。当需要排序的列的总大小加上order by 的列的大小。

在很多应用场景中我们需要将数据进行分页，一般会使用limit加上偏移量的方法实现，同时加上合适的orderby 的子句，如果这种方式有索引的帮助，效率通常不错，否则的化需要进行大量的文件排序操作，还有一种情况，当偏移量非常大的时候，前面的大部分数据都会被抛弃，这样的代价太高。要优化这种查询的话，要么是在页面中限制分页的数量，要么优化大偏移量的性能当limit前面的数很大时limit就会进行全表的扫描。

数据表的关联并不总是按照在查询中指定的顺序进行，决定关联顺序时优化器很重要的功能。

有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，范围从1/#T到1之间。一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对应BLOB,TEXT,VARCHAR类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。

1、如果一个索引包含所有需要查询的字段的值，我们称之为覆盖索引2、不是所有类型的索引都可以称为覆盖索引，覆盖索引必须要存储索引列的值3、不同的存储实现覆盖索引的方式不同，不是所有的引擎都支持覆盖索引，memory不支持覆盖索引。

-range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, , >, >=,

当包含多个列作为索引，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要。哈希索引自身只需存储对应的hash值，所以索引的结构十分紧凑，这让哈希索引查找的速度非常快。4、基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题。1、哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行。不是单独的索引类型，而是一种数据存储方式，指的是数据行跟相邻的键值紧凑的存储在一起。6、哈希冲突比较多的话，维护的代价也会很高。

5、访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行。当包含多个列作为索引，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要。哈希索引自身只需存储对应的hash值，所以索引的结构十分紧凑，这让哈希索引查找的速度非常快。1、哈希索引只包含哈希值和行指针，而不存储字段值，索引不能使用索引中的值来避免读取行。查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引。

MySQL 中的索引有很多中不同的分类方式，可以按照数据结构分，可以按照逻辑角度分，也可以按照物理存储分，其中，按照物理存储方式，可以分为聚簇索引和非聚簇索引。，在本次查询中因为查询结果只存在主键索引树中，我们必须回表才能查询到结果，那么如何优化这个过程呢？覆盖索引(covering index ，或称为索引覆盖)即。对于第二种查询方式而言，一共搜索了两棵 B+Tree，，sql命令与建立后的索引树结构如下。，那么先在我们删除掉之前以字段。从上面的分析中我们也能看出，当发起一个索引覆盖查询时，在。

以根节点为例，关键字为 16 和 34，P1 指针指向的子树的数据范围为小于 16，P2 指针指向的子树的数据范围为 16~34，P3 指针指向的子树的数据范围为大于 34。读取数据的磁盘块是16kb，也就是4页，B树每个节点都有data，导致存放的页数变少，3次io只能查询16^3次方数据，也就是4096条数据，而在企业中是不满足业务要求的。1、B+Tree每个节点可以包含更多的节点，这个做的原因有两个，第一个原因是为了降低树的高度，第二个原因是将数据范围变为多个区间，区间越多，数据检索越快。

当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR类型的大字段的时候，如果我们大部分访问这张表的时候都不需要这个字段，我们就该义无反顾的将其拆分到另外的独立表中，以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加，既减少物理 IO 次数，也能大大提高内存中的缓存命中率。2.这样的场景由于每次Join仅仅只是为了取得某个小字段的值，Join到的记录又大，会造成大量不必要的 IO，完全可以通过空间换取时间的方式来优化。缓存衍生值也是有用的。

可以使用的几种整数类型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT分别使用8，16，24，32，64位存储空间。尽量使用满足需求的最小数据类型1，tinyint 整数tinyint(m) 1个字节 范围(-128~127),22，smallint 整数smallint(m) 2个字节 范围(-32768~32767)3，mediumint 整数int(m) 4个字节 范围(-2147483648~2147483647)...

当设置完成之后，在服务器上执行的所有语句，都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。在mysql的命令行模式下只能显示两位小数的时间，可以使用如下命令查看具体的执行时间。此工具默认是禁用的，可以通过服务器变量在绘画级别动态的修改。4.cpu：显示用户cpu时间、系统cpu时间。7.source：显示源码中的函数名称与位置。2.block io：显示块io操作的次数。6.page faults：显示页错误数量。5.IPC：显示发送和接受的消息数量。8.swaps：显示swap的次数。

必须保证数据库设计的合理性,对数据库设计总结的一些经验性的规范,称之为范式1.范式是符合某一种设计要求的总结。2.要想设计一个结构合理的关系型数据库，必须满足一定的范式。3.数据库设计关系整个系统的架构，关系到后续开发效率和运行效率4.数据库的设计主要包含了设计表结构和表之间的联系。