数据库专辑
文章目录
一、索引模块
1、MySQL索引数据结构（两种）
一般数据库使用的是B-树或者B+树做索引。索引本身也是特别大，不可能都存在内存中，所以部分存在磁盘中，就会导致查找的时候需要读取IO，导致速度受影响。
（1）MyISAM（非聚集索引，索引跟值放在两个文件，先找索引，后找值）引擎使用B+Tree作为索引结构，索引文件只保存地址。MyISAM数据域存储的是索引值。（也就是在叶子节点中，存储的只是每个data的地址，然后会单独有一张表存储这些data的值，data就是搜索的每一行记录）
（2）InnoDB（聚集索引，索引跟值放在同一个文件）也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。这棵树的叶节点data域保存了完整的数据记录。
（3）mysql5.1以后默认使用的存储引擎是innoDB，主要考虑innoDB支持事务，是行级锁，所以相对更加安全而且效率高。
补充：从支持索引也了解到，InnoDB有且只有一个聚集索引，数据文件和索引绑定在一起，必须有主键（最好设置int型或者自增主键，如果没有主键，将会自动选择一个适合的作为主键或者生成虚拟列作为主键），通过主键索引效率高，但是辅助索引要查两次，先查主键再查数据。但ISAM是非聚集索引，数据和索引文件分离，索引保存的是数据文件的指针，主键索引和辅助索引是独立的，因此ISAM在增删改很少的系统中，性能要比innoDB好。
【补充】MYSQL需要索引的原因：好比查找书中的一页，当客户端要查找数据库的内容的时候，索引相当于目录，能够帮助我们快速定位到要查找的内容。
需要注意：如果要使用事务，要更改为innodb，否则实际不会使用事务且不报错。
对于innodb，也有特例，比如UPDATE student SET age=10 WHERE name LIKE ‘王%’，这种情况不能确定要UPDATE的行位置，InnoDB同样会锁住整个表。
【MySQL用B+树的原因】：最好的利于查找和插入的两种树是B和B+、但是采用B+树的原因是：在B树中，非叶子节点也会存一些key对应的data（数据），而且存的数据远大于key的大小，因此导致非叶子结点不能存很多key,这样当数据量很多的时候，就会导致树的高度很快变大，最后效率又降低。但是B+树不同，非叶子节点不存数据，因此非叶子节点可以放很多key，所有的data数据都放在叶子节点，这样子即使当数据很多（也就是key也会很多），同样不会导致树的高度太大，这样子查找效率就搞了。
https://blog.csdn.net/csdnlijingran/article/details/102309593 。
2、MySQL执行SQL的流程（以查询为例）
（1）客户端发送一条查询请求。
（2）先在缓存中找，找到了立即返回。（MySQL执行查询语句前，会通过缓存查看，是否执行过该语句（以k-v形式缓存在内存），key为查询语句，value为结果集。）
（3）如果没找到，则分别通过解析器进行解析、预处理器进行预处理（也可以说是分析器进行词法语法等的分析）、然后由优化器对解析树生成对应的查询计划。
（4）MYSQL根据查询计划，调用存储API来执行，并返回结果给客户端。
3、红黑树是怎么转的：学一下HashMap的原理就差不多了
回答：红黑树是建立在AVL（二叉平衡搜索树，这个又是建立在二叉树的基础上，要求每个节点的左子树和右子树的长度差不超过1）的基础上，要求每个节点的左右子树高度差不超过2倍。
（一）索引调优
1、如何定位并优化慢查询SQL：
1、可以开启慢日志功能，当有慢查询的时候，就会自动记录
2、用explain分析方法：在语句前面加explain即可，就会打印出分析结果。e
Explain关键字段：Type（看看是否是index和all，如果是，就是全局查找，需要优化），extra：
如果用的不是慢查询，比如用主键索引查询，就可能查询速度会快一些。
解决方法：如果是慢查询，就考虑对查询的字段添加索引。如下：
注意：【补充】主键索引，未必是最快的索引。可以用force index(索引值)来测试
（二）联合索引的最左匹配原则和成因
1、最左匹配原则是什么？
2、引发的成因？
对于联合索引（a,b,c），其实建立索引的时候，是先对a建立，然后再对b建立，再到c。所以如果在查找的时候，不使用索引a，直接用索引b，实际上是没有利用联合索引的。（必须得把a加上）
（三）索引越多越好吗？
二、锁模块
（一）MyISAM和InnoDB关于锁方面的区别是什么？
1、MyISAM默认用的是表级锁，不支持行级锁。
2、InnoDB默认用的是行级锁，也支持表级锁。
补充：
对于MyISAM，锁包括读锁和写锁。
1、读锁（select）也叫共享锁:就是加了读锁，其他语句也能读内容（相当于加读锁，因为读的时候会加锁 ），但是如果是写内容，就会被block住。也就是共享锁，支持其他操作也上共享锁。
【注意】但是也可以让读锁变成排他锁，在select后面加上for update。
2、写锁（update,write等命令）也叫排他锁：当上了写锁，再上读锁（就是读内容）或写锁（写内容），是不行的，会被block住。
对于innoDB，的锁默认是支持行级的。
1、加了共享锁，就不能加排他锁。显式加共享锁方法：lock in share mode;
2、innoDB在走索引的时候，使用行级锁，但是当不走索引的时候（比如查找一个非索引字段），就会用表级锁，默认锁住整张表，此时无法进行更新其他字段。
总结：（很重要）
行级锁一定比表级锁更好吗？未必，粒度越细，代价越高。相比表级锁直接在表头部上锁，行级锁还需要扫描到那行，对齐上锁，代价高。InnoDB支持事物同时，也比ISAM代价开销更大。从支持索引也了解到，InnoDB有且只有一个聚集索引，数据文件和索引绑定在一起，必须有主键（原因见下图），通过主键索引效率高，但是辅助索引要查两次，先查主键再查数据。但ISAM是非聚集索引，数据和索引文件分离，索引保存的是数据文件的指针，主键索引和辅助索引是独立的，因此ISAM在增删改很少的系统中，性能要比innoDB好。
分析两个引擎适用场景：
（1）MyISAM：适合查询等
1、MyISAM会保存表长度。而innoDB没有，需要求。
2、修改会锁表，阻塞，所以不适合，但是适合读。
3、不支持事物。
（2）InnoDB：适合增删改
1、增删改只是锁行，不会有过多阻塞。
2、支持事物。
（二）锁的分类：
1、页级锁介于表与行之间。
2、自动锁：比如读200W条数据，就会自动上锁。显式锁：for update排他锁，lock in share mode共享锁。
三、数据库事务特性
1、原子性
2、一致性
3、隔离性：多个事务间相互隔离。
4、持久性：操作是持久性的。
数据库的事务特性：
事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持久性（ Durability ）。这四个特性简称为 ACID 特性。
1 、原子性
事务是数据库的逻辑工作单位，不可分割，事务中包含的各操作要么都做，要么都不做
2 、一致性
事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就*中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。
3 、隔离性
一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。 事务的隔离级别有4级，可以查看这篇博客，关于MySQL的事务处理及隔离级别。
4 、持续性
也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的，不能回滚。接下来的其它操作或故障不应该对其执行结果有任何影响。
【补充】
1、脏读：一个事务读到另外一个事务未提交的数据。如果是最低事务级别，read-uncommitted无法避免，必须要read-committed只有事务提交后才可被读，才能避免。
2、不可重复读：一个事务读到了第一次数据后，另一个事务对数据进行了修改，此时第一个事务用一开始读到的数据进行操作，就会出问题，这就是不可重复读。用read-committed也无法处理，此时read-committed只能让事务提交后读，但是第一个事务会读到两个不同的值，此时用户会很奇怪，虽然第二个值是正确的。要用repeatable-read就能解决，是使得第一个事务多次读可以读到同一个结果（尽管其他事物怎么修改数据，第一个读的结果相同），但是第一个事务实际操作的结果，还是以其他事务最新提交的教过为准。
3、幻读：就是第一个事务读了数据（假设有3行），此时第二个事务插入或删除了一条数据，然后第一个事务去修改某个值，发现竟然影响了4行（因为第二个插入了一行），这个叫幻读。要用事务*别Serilizable来解决。
注意：脏读跟幻读很相似，都是第一个事务读取数据后，第二个事务进行操作，然后第一个事务进行操作。但是脏读侧重的是第二个事务修改数据，而幻读侧重的是第二个事务插入或者删除数据。
但是事务级别越高，串行化约严重，影响性能，所以只要足够避免问题，就不用设置太高级别。
补充：
1、当前读：加锁的操作（增删改查，无论共享还是排他）
2、快照读：不加锁的select操作，开销低，但是可能读到历史版本，不是最新的版本。
快照读前提：事务不在串行化级别，因为串行化就不可能多个事务共同操作，就不可能有快照读了。
四、语法部分
例如：Group by
1、查询所有同学的学号、选棵数、总成绩
解释：使用group by，使用方法为配合select执行，但是要求select语句中除了group by根据的列可以单独出现外，其他字段的值必须要用函数包围起来，比如count,sum,max,min,avg这些函数。
此题答案：select studentID,count(courseID),Sum(score) from score group by studentID。
2、查询所有同学的学号、姓名、选课数、总成绩
select sc.studentID,stu.Name,count(sc.ourseID),sum(sc.score)
from
score sc,
student stu
where sc.studentID=stu.studentID
group by sc.studentID
例如having:
1、查询平均成绩大于60分的同学的学号和平均成绩
答案：
select studentID,avg(score)
from score
group by studentID
having avg(score)>60
注意：where最前，group by其次，having最后。此外，当没有gourp by的时候，having跟where功能相同。
课后练习题：
select * from score having/where studentID=1
先分析，没有学全所有课，只能在成绩表中体现，也就是一个学生，他只有两项成绩，但实际上有三门课。
select stu.studentID,stu.name
from
score sc,
student stu
where sc.studentID=stu.studentID
group by stu.studentID,stu.name
having count()<(select count() from course)
3、Inner Join的用法：
假设表内容如上，则若要连接以上两张表来读取runoob_tbl表中所有runoob_author字段在tcount_tbl表对应的runoob_count字段值：
select a.runoob_id , a.runoob_author, b.runoob_count from runoob_tb1 a inner join tcount_tb1 b on a.runoob_author = b.runoob_author
相当于：select a.runoob_id , a.runoob_author, b.runoob_count from runoob_tb1 a,tcount_tb1 b where a.runoob_author = b.runoob_author
4、 Left Join 的应用----》衔接上面这个应用-----》MySQL left join 与 join 有所不同。 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据。
举例：select a.runoob_id , a.runoob_author, b.runoob_count from runoob_tb1 a left join tcount_tb1 b on a.runoob_author = b.runoob_author
结果：除了会输出两边共有的，还会输出右边没有的左表项。
5、Right Join----》与Left Join概念类似。
举例：select a.runoob_id , a.runoob_author, b.runoob_count from runoob_tb1 a right join tcount_tb1 b on a.runoob_author = b.runoob_author
结果：划框的为tcount_tb1 具有，但是runoob_tb1 不具有的，所以只有一个数量22，但是在tcount_tb1 中找不到对应的id和作者。
【补充】MYSQL最大连接数？
（1）特定数据库上只能接受限定数目的链接，用户可以设定最大连接数，MySQL默认max_connection是100。
【其他】
（1）select,poll,epoll的区别：在选择select，poll，epoll时要根据具体的使用场合以及这三种方式的自身特点。
1、表面上看epoll的性能最好，但是在连接数少并且连接都十分活跃的情况下，select和poll的性能可能比epoll好，毕竟epoll的通知机制需要很多函数回调。
2、select低效是因为每次它都需要轮询。但低效也是相对的，视情况而定，也可通过良好的设计改善
数据库分类及代表：
（1）关系型数据库：MYSQL、Oracle、SqlServer
（2）非关系型数据库：Redis、Memcached、MongoDB、Hadoop