Mysql优化摘要系列3360https://blog.csdn.net/QQ _ 32534441/article/details/86523894
Mysql优化之三：数据库yjdcc原理与优化3360https://blog.csdn.net/QQ _ 32534441/article/details/86493753
Mysql优化系列： https://blog.csdn.net/QQ _ 32534441/article/category/8610043
什么是yjdcc :
yjdcc就像一本书的目录，它是与表或视图关联的磁盘结构，可以加快从表或视图中查找行的速度。 yjdcc包含从表或视图的一个或多个列生成的键。 这些密钥存储在结构(BTree )中，以便SQL可以快速高效地查找与密钥值相关的行。
2. yjdcc原理：
yjdcc的原理大致概括为在空间上改变时间。 在未添加yjdcc时查询数据库，缺省情况下是进行全量搜索或全局扫描，有多少数据就进行多少次查询，找到匹配的数据后放入结果集中直到所有表扫描结束。 创建yjdcc后，会将创建yjdcc的KEY值放置在n叉树中。 由于b树的特征适于在光盘等直接存储装置中组织动态查找表，所以每次用yjdcc进行条件检索时，都到树中根据key值直接进行检索。 次数约为log总根数，底数为页存储数，例如100万数据表，在页存储数为100的情况下，有yjdcc的查询次数为3次log1000000100，但全量检索
3 .为什么要创建yjdcc呢？ 这是因为创建yjdcc可以大大提高系统性能。
第一，可以通过创建唯一性yjdcc来确保数据库表中每行数据的唯一性。
其次，可以大幅提高数据的检索速度。 这也是制作yjdcc的最主要理由。
三是加快桌子和桌子之间的连接。 特别是在实现数据的参照完整性方面特别有意义。
第四，使用分组和排序子句搜索数据也可以大大减少查询中分组和排序所需的时间。
第五，通过yjdcc，可以在查询过程中使用优化的隐藏器来提高系统性能。
人们可能会问，既然增加yjdcc有很多优点，为什么不为表中的每一列创建yjdcc呢？ 这种想法有合理性，但也有片面性。 虽然yjdcc有很多优点，但在表的每一列中添加yjdcc是不明智的。 这是增加yjdcc也有许多不利的一个方面。
第一，创建和维护yjdcc需要时间，并且时间会随着数据量的增加而增加。
第二，yjdcc必须占用物理空间。 除了数据表占用数据空间外，每个yjdcc还占用一定的物理空间。 构建集群yjdcc时，所需空间会更大。
第三，添加、删除、修改表中的数据时，yjdcc也必须动态维护，降低了数据维护速度。
yjdcc是在数据库表的某些列之上创建的。 因此，在创建yjdcc时，必须仔细考虑哪些列可以创建yjdcc，哪些列不能创建yjdcc。 一般是http://www.Sina.com/http://www.Sina.com /，例如：
应该在这些列，http://www.Sina.com/http://www.Sina.com /
上创建yjdcc，在经常需要搜索的列上
可以加快搜索的速度；，http://www.Sina.com/http://www.Sina.com /
，http://www.Sina.com/http://www.Sina.com /
3358 www.Sina.com/http://www.Sina.com/，http://www.Sina.com /
3358 www.Sina.com/http://www.Sina.com/，http://www.Sina.com /
同样，有些列不应该创建yjdcc。 一般为在作为主键的列上
http://www.Sina.com/http://www.Sina.com/http://www.Sina.com /
rong>第二，对于那 些只有很少数据值的列也不应该增加yjdcc。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加yjdcc，并不能明显加快检索速度。
第三，对于那些定义为text, image和bit数据类型的列不应该增加yjdcc。这是因为，这些列的数据量要么相当大，要么取值很少。
第四，当修改性能远远大于检索性能时，不应该创建索 引。这是因为，修改性能和检索性能是互相矛盾的。当增加yjdcc时，会提高检索性能，但是会降低修改性能。当减少yjdcc时，会提高修改性能，降低检索性能。因 此，当修改性能远远大于检索性能时，不应该创建yjdcc。
4.创建yjdcc的方法和yjdcc的特征
创建yjdcc的方法
创 建yjdcc有多种方法，这些方法包括直接创建yjdcc的方法和间接创建yjdcc的方法。直接创建yjdcc，例如使用CREATE INDEX语句或者使用创建yjdcc向导，间接创建yjdcc，例如在表中定义主键约束或者唯一性键约束时，同时也创建了yjdcc。虽然，这两种方法都可以创建yjdcc，但 是，它们创建yjdcc的具体内容是有区别的。
直接创建yjdcc：
使用CREATE INDEX语句或者使用创建yjdcc向导来创建yjdcc，这是最基本的yjdcc创建方式，并且这种方法最具有柔性，可以定制创建出符合自己需要的yjdcc。在使用这种方式 创建yjdcc时，可以使用许多选项，例如指定数据页的充满度、进行排序、整理统计信息等，这样可以优化yjdcc。使用这种方法，可以指定yjdcc的类型、唯一性和复合 性，也就是说，既可以创建聚簇yjdcc，也可以创建非聚簇yjdcc，既可以在一个列上创建yjdcc，也可以在两个或者两个以上的列上创建yjdcc。
间接创建yjdcc：
通 过定义主键约束或者唯一性键约束，也可以间接创建yjdcc。主键约束是一种保持数据完整性的逻辑，它限制表中的记录有相同的主键记录。在创建主键约束时，系统自动创建了一个唯一性的聚簇yjdcc。虽然，在逻辑上，主键约束是一种重要的结构，但是，在物理结构上，与主键约束相对应的结构是唯一性的聚簇yjdcc。换句话说，在物理实现上，不存在主键约束，而只存在唯一性的聚簇yjdcc。同样，在创建唯一性键约束时，也同时创建了yjdcc，这种yjdcc则是唯一性的非聚簇yjdcc。因此，当使用约束创建yjdcc时，yjdcc的类型和特征基本上都已经确定了，由用户定制的余地比较小。
当在表上定义主键或者唯一性键约束时，如果表 中已经有了使用CREATE INDEX语句创建的标准yjdcc时，那么主键约束或者唯一性键约束创建的yjdcc覆盖以前创建的标准yjdcc。也就是说，主键约束或者唯一性键约束创建的yjdcc的优先 级高于使用CREATE INDEX语句创建的yjdcc。
yjdcc的特征
yjdcc有两个特征，即唯一性yjdcc和复合yjdcc。
唯一 性yjdcc保证在yjdcc列中的全部数据是唯一的，不会包含冗余数据。如果表中已经有一个主键约束或者唯一性键约束，那么当创建表或者修改表时，SQL Server自动创建一个唯一性yjdcc。然而，如果必须保证唯一性，那么应该创建主键约束或者唯一性键约束，而不是创建一个唯一性yjdcc。当创建唯一性yjdcc 时，应该认真考虑这些规则：当在表中创建主键约束或者唯一性键约束时，SQL Server自动创建一个唯一性yjdcc；如果表中已经包含有数据，那么当创建yjdcc时，SQL Server检查表中已有数据的冗余性；每当使用插入语句插入数据或者使用修改语句修改数据时，SQL Server检查数据的冗余性：如果有冗余值，那么SQL Server取消该语句的执行，并且返回一个错误消息；确保表中的每一行数据都有一个唯一值，这样可以确保每一个实体都可以唯一确认；只能在可以保证实体 完整性的列上创建唯一性yjdcc，例如，不能在人事表中的姓名列上创建唯一性yjdcc，因为人们可以有相同的姓名。
复合yjdcc就是一个yjdcc创建 在两个列或者多个列上。在搜索时，当两个或者多个列作为一个关键值时，最好在这些列上创建复合yjdcc。当创建复合yjdcc时，应该考虑这些规则：最多可以把16个列合并成一个单独的复合yjdcc，构成复合yjdcc的列的总长度不能超过900字节，也就是说复合列的长度不能太长；在复合yjdcc中，所 有的列必须来自同一个表中，不能跨表建立复合列；在复合yjdcc中，列的排列顺序是非常重要的，因此要认真排列列的顺序，原则上，应该首先定义最唯一的列，例 如在（COL1，COL2）上的yjdcc与在（COL2，COL1）上的yjdcc是不相同的，因为两个yjdcc的列的顺序不同；为了使查询优化器使用复合yjdcc，查询语 句中的WHERE子句必须参考复合yjdcc中第一个列；当表中有多个关键列时，复合yjdcc是非常有用的；使用复合yjdcc可以提高查询性能，减少在一个表中所创建的 yjdcc数量。
5.yjdcc的类型
根据yjdcc的顺序与数据表的物理顺序是否相同，可以把yjdcc分成两种类型。一种是数据表的物理顺序与yjdcc顺序相同的聚簇yjdcc，另一种是数据表的物理顺序与yjdcc顺序不相同的非聚簇yjdcc。
所谓聚簇yjdcc，就是指主yjdcc文件和数据文件为同一份文件，聚簇yjdcc主要用在Innodb存储引擎中。在该yjdcc实现方式中B+Tree的叶子节点上的data就是数据本身，key为主键，如果是一般yjdcc的话，data便会指向对应的主yjdcc，如下图所示：
在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图4中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。
非聚簇yjdcc就是指B+Tree的叶子节点上的data，并不是数据本身，而是数据存放的地址。主yjdcc和辅助yjdcc没啥区别，只是主yjdcc中的key一定得是唯一的。主要用在MyISAM存储引擎中，如下图：
非聚簇yjdcc比聚簇yjdcc多了一次读取数据的IO操作，所以查找性能上会差。
MyisAMyjdcc与InnoDByjdcc相比较MyisAM支持全文yjdcc（FULLTEXT）、压缩yjdcc，InnoDB不支持；InnoDB支持事务，MyisAM不支持；MyisAM顺序储存数据，yjdcc叶子节点保存对应数据行地址，辅助yjdcc很主键yjdcc相差无几；InnoDB主键节点同时保存数据行，其他辅助yjdcc保存的是主键yjdcc的值；MyisAM键值分离，yjdcc载入内存（key_buffer_size），数据缓存依赖操作系统；InnoDB键值一起保存，yjdcc与数据一起载入InnoDB缓冲池；MyisAM主键（唯一）yjdcc按升序来存储存储，InnoDB则不一定MyisAMyjdcc的基数值（Cardinality，show index 命令可以看见）是精确的，InnoDB则是估计值。这里涉及到信息统计的知识，MyisAM统计信息是保存磁盘中，在alter表或Analyze table操作更新此信息，而InnoDB则是在表第一次打开的时候估计值保存在缓存区内；MyisAM处理字符串yjdcc时用增量保存的方式，如第一个yjdcc是‘preform’，第二个是‘preformence’，则第二个保存是‘7，ance’，这个明显的好处是缩短yjdcc，但是缺陷就是不支持倒序提取yjdcc，必须顺序遍历获取yjdcc为什么选用B+/-Tree一般来说，yjdcc本身也很大，不可能全部存储在内存中，因此yjdcc往往以yjdcc文件的形式存储的磁盘上。这样的话，yjdcc查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为yjdcc的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，yjdcc的结构组织要尽量减少查找过程中磁盘I/O的存取次数。
简单点说说内存读取，内存是由一系列的存储单元组成的，每个存储单元存储固定大小的数据，且有一个唯一地址。当需要读内存时，将地址信号放到地址总线上传给内存，内存解析信号并定位到存储单元，然后把该存储单元上的数据放到数据总线上，回传。
写内存时，系统将要写入的数据和单元地址分别放到数据总线和地址总线上，内存读取两个总线的内容，做相应的写操作。
内存存取效率，跟次数有关，先读取A数据还是后读取A数据不会影响存取效率。而磁盘存取就不一样了，磁盘I/O涉及机械操作。磁盘是由大小相同且同轴的圆形盘片组成，磁盘可以转动(各个磁盘须同时转动)。磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不动，磁盘转动，但磁臂可以前后动，用于读取不同磁道上的数据。磁道就是以盘片为中心划分出来的一系列同心环(如图标红那圈)。磁道又划分为一个个小段，叫扇区，是磁盘的最小存储单元。
磁盘读取时，系统将数据逻辑地址传给磁盘，磁盘的控制电路会解析出物理地址，即哪个磁道哪个扇区。于是磁头需要前后移动到对应的磁道，消耗的时间叫寻道时间，然后磁盘旋转将对应的扇区转到磁头下，消耗的时间叫旋转时间。所以，适当的操作顺序和数据存放可以减少寻道时间和旋转时间。
为了尽量减少I/O操作，磁盘读取每次都会预读，大小通常为页的整数倍。即使只需要读取一个字节，磁盘也会读取一页的数据(通常为4K)放入内存，内存与磁盘以页为单位交换数据。因为局部性原理认为，通常一个数据被用到，其附近的数据也会立马被用到。
B-Tree：如果一次检索需要访问4个节点，数据库系统设计者利用磁盘预读原理，把节点的大小设计为一个页，那读取一个节点只需要一次I/O操作，完成这次检索操作，最多需要3次I/O(根节点常驻内存)。数据记录越小，每个节点存放的数据就越多，树的高度也就越小，I/O操作就少了，检索效率也就上去了。
B+Tree：非叶子节点只存key，大大滴减少了非叶子节点的大小，那么每个节点就可以存放更多的记录，树更矮了，I/O操作更少了。所以B+Tree拥有更好的性能。
• yjdcc分类
○ 直接创建yjdcc和间接创建yjdcc
○ 普通yjdcc和唯一性yjdcc
○ 单个yjdcc和复合yjdcc
○ 聚簇yjdcc和非聚簇yjdcc
• yjdcc失效
如果条件中有or，即使其中有条件带yjdcc也不会使用（这就是为什么尽量少使用or的原因）（注意：要想使用or，又想让yjdcc生效，只能将or条件中的每个列都加上yjdcc）对于多列yjdcc，不是使用的第一部分（不符合最左前缀原则），则不会使用yjdcc，例子如下：如果select * from key1=1 and key2= 2;则建立组合yjdcc（key1，key2）;
select * from key1 = 1;组合yjdcc有效；
select * from key1 = 1 and key2= 2;组合yjdcc有效；
select * from key2 = 2;组合yjdcc失效；不符合最左前缀原则like查询是以%开头如果列类型是字符串，那一定要在条件中使用引号引起来，否则不会使用yjdcc如果mysql估计使用全表扫描比使用yjdcc快，则不使用yjdcc