Mysql学习笔记和面试题总结
mysql学习笔记
sql优化相关？
检索语句中尽量避免使用*而使用具体字段代替。
避免回表操作。
尽量提高联合索引的使用率。
过大过小的字段不要建立索引。
使用联合索引的时候要注意最左前缀原则order by 、group by同样适用此原则。
like模糊查询时尽量模糊后半部分例如：like "abc"%
尽量避免使用子查询，可以使用关联查询来代替子查询。因为子查询会在内存中建立一个临时表所以不建议使用它
检索单条或者少量的sql语句时使用limit 0 ,1来限制检索范围
可以使用explain来查看sql执行情况。
OR前后的每个字段都有索引才会生效
尽量避免写范围查询的sql语句，范围查询百分百不能走索引。
索引建立标准？
尽量给查询频繁的数据列建立索引，尽量不要给修改频繁的数据列建立索引（索引的优势就在于读操作快但会降低写操作的效率）
联合索引优于多个单列索引（每次查询只会选择一个执行计划即一次查询只会选择一颗索引树）
尽量给区分度高的列上建立索引且建立联合索引时尽量把区分度高的列放在前面好缩小数据检索的范围（性别这种就没必要建立索引，区分度太低了）
分区表？
分区表是指逻辑上为一个表，物理上存储在多个文件中。
分区表的类型有：HASH分区、RANGE分区、LIST分区
mysql长连接与短连接的区别？
长连接：它是相对于短连接而言的，是指在一个连接上可以发送多个数据包。默认最长闲置时间为8小时。
短连接：收发做数据交互式建立一次连接，交互完成后断开连接，每次连接只完成一项业务的发送。
Innodb存储引擎聚簇索引相关？
聚簇索引的B+树的结构？
B+树有叶子节点和非叶子节点。
叶子节点：是B+树最底层的。叶子节点存储的是真实的记录信息（索引即数据）和一些行信息以及页信息，叶子节点之间通过链表连接。示例：id=5 的叶子节点包含（id, name, age, address...）等完整数据，所以说索引即数据。
非叶子节点：是除叶子节点以外的所有节点。它包含目录项和根节点，目录项和叶子节点类似只不过目录项中存储的是目录项记录（页号，页中最大、小的主键ID）
目录项与目录项之间采用双向链表的结构来连接的，目录项内的记录是通过单向链表结构来连接的，目录项的内外都是通过主键ID的大小来进行排序的。
聚簇索引优缺点？
优点：聚簇索引是根据主键进行查找的即通过主键就可以获得记录信息无需回表操作。
缺点：当发生不规则的主键的写入操作是会造成行迁移和页分裂的，行迁移和页分裂发生时会导致从叶子节点开始到各层非叶子节点都发生改变，这样的操作开销是很大的。
什么是回表？
回表操作多发生在Innodb存储引擎中，是指使用二级索引查询数据时用于获取不包含在二级索引中的字段时需要根据二级索引叶子节点的主键ID返回聚簇索引进行查找的操作。这个"从二级索引→回聚簇索引"的过程就是回表操作。
为什么会有回表操作？
因为二级索引的叶子节点存储的是索引值+主键ID而不保存完整的记录信息。在获取叶子节点上不存在的数据时就需要回表操作了。
如何避免回表操作？
1、触发索引覆盖，在二级索引中包含查询所需的所有字段
-- 创建包含所有查询字段的索引 CREATE INDEX idx_department_covering ON employees(department_id, salary, hire_date); -- 现在这样查询就不需要回表： SELECT department_id, salary, hire_date FROM employees WHERE department_id = 3;
2、使用主键ID查询，如果知道主键ID一定要用主键ID进行查找，这样会直接查找聚簇索引的B+树，可以避免回表操作，减少磁盘IO次数，效率会更高。
3、避免使用Select *查询
-- 避免回表 SELECT id, department_id FROM employees WHERE department_id = 3; -- 需要回表（因为需要name,salary等字段） SELECT * FROM employees WHERE department_id = 3;
聚簇索引查找会发生什么？
查找时会从最高层的目录项开始到最底层的叶子节点依次向下根据二分法来查找并定位数据所在的位置最后通过对应的槽号来获取数据。
聚簇索引编辑会发生什么？
编辑的时候会根据数据的变化来调整目录项和叶子节点中的分布情况，例如：行迁移，页的拆分。所以说聚簇索引在编辑时的维护成本还是比较高的。
Innodb存储引擎二级索引相关？
啥是二级索引？
二级索引是户自己建立的索引，它不像聚簇索引那样时系统自己创建的。二级索引有一颗属于自己的B+树。
二级索引的查找？
二级索引在查找的时候会根据查找要求使用二分法从顶层的目录项到底层的叶子节点依次进行查找获取行号和主键ID值（非叶子节点放的是索引值，叶子节点放的是主键ID），然后必要时（例如*）根据主键ID去聚簇索引中进行回表操作来获取信息。
也就是说必要时二级索引的查找是要查两颗B+树的，一颗为二级索引自己的索引树另一颗为聚簇索引的索引树。
二级索引的排序？
二级索引在order by排序时只有符合“最左前缀原则“且DESC和ASC不混用时才会生效，二级索引会把对顺序的维护分散到每次的增删改操作中，而不是查询时从零开始做排序工作。
二级索引对于index(a,b,c)索引的语句select * from table where a=1 order by b , c是可以走索引的，因为a=1可以保证局部数据相关性也就是说可以直接进行order by b , c的排序操作，但是如果where部分改为where a > 1的范围查询时是不可以走索引的因为它无法保证局部数据的相关性。
二级索引的编辑？
当编辑发生时依次更改二级索引中的相对应的内容字段即可，如果其主键ID发生了变化还需做回表操作进行核实并更新。
二级索引可以是单个字段的也可以是多个字段的。当它是多个字段时成为联合索引
索引覆盖？
当前索引就可以满足查找的所有需要就叫做索引覆盖。例如：二级索引查找中不使用回表操作就是索引覆盖的一个体现（这也是我们常说的尽量只取必要的字段避免“select * ”这样的操作，即使表中只有两个字段也是如此因为你不知道随着业务的扩张未来当前表会变成啥样子）。
优点：
无需回表操作，减少了二次查找时IO的开销。
减少遍历数据页的数量，例如：聚簇索引中叶子节点占用的数据页的数量肯定比原表少，二级索引中叶子节点的体量肯定是比聚簇索引小的。
mysql中的整型数值类型？
以int为例int(4)和int(11)存储的长度是一样的，因为整型的存储范围是固定的。括号内的数字表示的只是列宽，比如位数不够补零啥的，对实际存储数值大小没影响。
mysql中char与varchar的区别？
char是定长，不存在边界问题。读写效率高于varchar适合存储读写频繁、固定长度的数值（手机号，身份证号）但不够灵活。
varchar长度不固定，但可以通过指定数值的方式来指定上限，适合存储长度波动频繁但更新不频繁的数据但它很灵活。
因为char是定长的缘故在做数据检索的时候char的效率要高于varchar
B树与B+树的理解和区别？
B树的节点会存储整行数据占用空间大存储记录数少，B+树的节点只存储主键ID能容纳更多的记录
B+树的非叶子节点中存储的是记录的主键ID而记录的真实数据存储在叶子节点上因此同一非叶子节点中B+树比B树能存储更多的记录数所以B+树会“矮”一些，IO更少
B树查询效率不稳定，最好情况是根节点最坏情况是叶子节点。B+树每次都要查询叶子节点相对更稳定
B+树的叶子节点是有序列表，非常便于范围查询
总结：B+树的树高比B树更低意味着磁盘IO次数更少所以更适合用作MySql的存储
mysql中Hash索引的理解以及与B+树索引的区别？
hash优势：速度快，只需要一次计算就可以得到存储的地址
hash劣势：不支持模糊查询、范围查询、排序
hash索引是根据哈希算法为索引列计算出唯一的存储地址。只需要计算一次即可所以速度很快，hash计算后会减弱数据的关联性，可以参考HashMap它put和get的顺序是完全不一样的。
Innodb和myisam引擎的区别？
innodb支持事物，myisam不支持
innodb锁粒度更细为行锁，myisam锁粒度更粗为表锁
innodb存储是分为：表结构+表数据及索引（常说的innodb是索引及数据），myisam存储分为：表结构+表数据+索引
mysql中alter修改表结构是modify和change的区别？
change 可以更改列名 和 列类型 (每次都要把新列名和旧列名写上, 即使两个列名没有更改,只是改了类型)
modify 只能更改列属性 只需要写一次列名, 比change 省事点
浙公网安备 33010602011771号