mysql02

1、普通索引和唯一索引对查询和更新的影响

查询过程

执行查询语句是select id from T where k=5,查询语句在索引树上查找的过程，先是通过B+树从树根开始，按层搜索到叶子节点

对于普通索引，查找到满足条件的第一个记录（5,500）后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录

对于唯一索引，由于定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索

两者性能差距不大

引擎是按页读写的，当找到k=5时，他所在数据页都在内存里，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存，在InnoDB中，每个数据页的大小默认是16kb，对于普通索引，要多做的那一次“查找和判断下一条记录”操作，就只需要一次指针寻找和一次计算

更新过程

change buffer：当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果没在，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了，在下次查询需要访问这个数据页时，将数据读入内存，然后执行change buffer中与这个页有关的操作

他实际是可以持久化的数据。从change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge，系统有后台线程会定期merge，在数据库正常关闭过程中，也会merge

普通索引用change buffer

若在表中插入新记录（4,400），InnoDB处理流程：

        （1）这个记录要更新的目标页在内存

                        唯一索引：找到3,5之间位置，判断有无冲突，插入

                        普通索引：找到3,5之间位置，插入

        （2）这个记录要更新的目标页不在内存

                唯一索引：需要将数据页读入内存，判断有无冲突，插入

                普通索引：将更新记录在change buffer,

                change buffer减少了随机磁盘访问，对更新性能提升明显

redo log主要节省的是随机写磁盘的IO消耗（转成顺序写），change buffer主要节省的是随机读磁盘的IO消耗 

2、前缀索引：定义字符串的一部分作为索引

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多查询成本

我们在建立索引时关注的是区分度，区分度越高越好，因为区分度越高，意味着重复的键值越少

对于字符串字段创建索引的场景，可以使用的方式：

        1、直接创建完整索引，但可能比较占用空间

        2、创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引

        3、倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题

        4、创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围存储

3.mysql抖一下

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”，内存数据写入磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”

4、表数据删掉一般，表文件大小为啥不变

如果只是delete表里面不用的数据的话，表文件的大小是不会变的，只是把记录的位置，或者数据页标记为了“可复用”，要通过alter table 命令重建表，才能达到表文件变小的目的

一个InnoDB包包含两部分：表结构定义和数据，在mysql8.0以前，表结构是存在以.frm为后缀的文件里，而mysql8.0，则已经允许把表结构定义放在系统表中了，因为表结构定义用的空间小。

参数innodb_file_per_table，参数设置为off，表的数据放在系统共享表空间，也就是跟数据字典放在一起。设置为on，每个InnoDB表数据存储在一个以.ibd为后缀的文件中。在mysql5.6.6后，默认设置为on。设置为on较好，因为一个表单存储为一个文件跟容易管理，当你不需要时，通过drop table直接删除，而如果放在共享表，即使表删除了，空间也不会回收

通过大量增删改的表，都可能是存在漏洞的，通过重建表，alter table A engine=InnoDB ,可以达到收缩表空间的目的。但这不是online的，如果在往临时表插入数据时，有新数据写入列表时，会造成数据丢失。

mysql5.6引入online DDL，流程：

（1）建立一个临时文件，扫描表A主键的所有数据页

（2）用数据页中表A的记录生成B+树，存到临时文件中

（3）生成临时文件的过程中，将所有对A的操作记录在一个日志文件中(row log)中

（4）临时文件生成后，将日志文件中的操作应用到临时表，得到一个逻辑数据上与表A相同的数据文件

（5）用临时文件替换表A的数据文件

5、count（*）

MyISAM表虽然count（*）很快，但不支持事务

show table status 命令虽然返回很快，但是不准确

InnoDB表直接count（*）会遍历全表，虽然结果准确，但会导致性能问题

InnoDB默认级别是可重复读，在代码上就是通过多版本并发控制，也就是MVCC实现。每一行记录都要判断自己是否对这个会话可见。 

？？？对于一个经常要显示交易系统操作记录总数的页面，要如何技数？？？

（1）用缓存系统保存计数。用一个redis服务来保存这个表，可能保存加1时，redis重启了等突发情况，导致加1操作丢失，但缓存系统可能会丢失更新，即使在正常工作情况下，逻辑上还是不准确的，

在并发系统里，我们无法准确控制不同线程的执行时刻

（2）在数据库保存计数

count(*)   count(主键id） count（1）区别

count（）是一个聚合函数，对于返回的结果集，一行行判断，如果count函数的参数不为null，累计值加1，否则不加。

所以三者都表示返回满足条件的结果集的总行数，而count（字段）则表示返回满足的数据化里，参数字段不为null的总个数

当分析性能差别是，原则：

        server层要什么就给什么

        InnoDB只给必要值

        现在优化器只优化count（*）的语义为取行数，其他优化并没有

count（主键id）来说，InnoDB引擎会遍历整张表，把每一行id都取出来，返回给server层，server层拿到id后，判断是不为空的，按行累加

count（1），InnoDB引擎遍历整张表，但不取值，server对于返回的每一行，放一个数字1，判断是不可能为空的，按行累加

count（字段）：如果这个字段是定义为not null，一行行从记录里读出这个字段，判断不为null，按行累加；如果这个字段定义为允许为null，则执行时，判断到有可能是null，还要把值取出来再判判出来，不是null才累加

count（*）例外，并不会把全部字段取出来，而是专门做了优化，不去治，count（*）肯定不是null，按行累加

因此，按行效率排序的话，count（字段）<count(主键id）<count(1)约等于count(*) 

6、order by怎么工作的

select city,name,age from t where city='杭州' order by name limit 1000  ;

（1）全字段排序 

给city字段加索引

        （1）初始化sort_buffer,确定放入name,city,age三个字段

        （2）从索引city找到第一个满足city='杭州’ 条件的主键id，图中ID_X

        （3）到主键id索引取出整行，取name city  age三个字段的值，存入sort_buffer中

        （4）从索引city取下一个记录的主键id

        （5）重复步骤3,4，知道city的值不满足查询条件为止，对应的主键id也就是图中ID_Y

        （6）对sort_buffer中的数据按照字段name做快速排序

        （7）按照排序结果取前1000行返回客户端

 其中按name排序，可能在内存中完成，可能需要使用外部排序，取决于排序所需内存和参数sort_buffer_size。外部排序一般使用归并排序。如果sort_buffer_size超过了需要排序的数据量大小，number_of_tmp_files是0，表示排序直接在内存完成。若number_of_tmp_files不为0，为12（或其他），可以这样，mysql将需要排序的数据分成12份，每一份单独排序后存在这些临时文件中，然后把这12个有序文件再合并成一个有序的大文件。

（2）rowid排序

如果要查询返回的字段很多，sort_buffer要放字段太多的，此时内存同事放下行数很少，会分成多个临时文件，排序性能变差。

max_length_for_sort_data，是mysql中专门用于控制用于排序的行数据的长度的一个参数，如果单行长度超过这个值，就换一个算法

此时流程变为

        （1）初始化sort_buffer,确定放入两个字段，即name和id

        （2）从索引city找到第一个满足city=‘杭州’,条件主键id（图中ID_X）

        （3）到主键id索引取出整行，取name  id两个字段，存入sort_buffer

        （4）从索引city取下一个记录的主键id

        （5）重复3 4，知道不满足 city=‘杭州’,

        （6）对sort_buffer中的数据按照字段name进行排序

        （7）遍历排序结果，取1000行，并按照id的值回到原表取出city   name  age三字段返回客户端

 

 两种方法的sort_mode不同，numer_of_tmp_files使用暂时文件不同

（3）若我们在表中创建一个city和name的联合索引，取出数据就不用排序了

                （1）从索引(city,name)找到第一个满足city=‘杭州’条件的主键id

                （2）到主键id索引取出整行，取name city  age三个字段值，作为结果集的一部分直接返回

                （3）从索引(city  name)取下一个记录主键id

                （4）重复2  3 ，查到第1000条记录，或不满足city=‘杭州’条件时 结束

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据

（4）创建city  name age索引

alter table t add index city_user_age(city, name, age);

        （1）从索引（city,name,age）找到第一个满足city='杭州’条件的 记录，取出city,name,age三个字段值，作为结果集的一部分直接返回

        （2）从索引（city,name,age）取下一个记录，同样取这仨字段值，作为结果集一部分直接返回

        （3）重复2，知道查到1000条记录，或不满足city='杭州'条件时循环结束