发布时间:2023-04-22 17:30
为什么不是Hash哈希表存储:
为什么不是平衡二叉树存储:
为什么不是b树存储:
为什么是b+树:
在二叉搜索树的基础上,通过左旋或右旋的自动优化流程,实现了左右子树的高度差不超过一,防止出现极端只有右子树的情况
代码实现:
左旋转方法
Public void leftRotate(){
//创建新的节点,以当前根节点的值
Node newNode = new Node(value);
//把新的节点的左子树设置成当前节点的左子树
newNode.left = left;
//把新的节点的右子树设置成当前节点的右子树的左子树
newNode.right = right.left;
//把当前节点的值替换成右子节点的值
value = right.value;
//把当前节点的右子树设置成当前节点右子树的右子树
right = right.right;
//把当前节点的左子树设置成新的节点
left = newNode;
}
右旋转方法代码略写,与左旋转类似
有了左旋和右旋,我们还需要定义一个获取左右子树高度的方法
有了上面的方法后,在add方法内部,就可以在执行完插入代码后,对左右子树的高度进行获取求差,如果左边大,则右旋,反之亦然,但是会有一种特殊的情况发生,因此,在添加方法内部,正确严谨的条件判断应该是:
正确的条件执行如下:
1.当符合右旋转的条件时
2.如果它的左子树的右子树高度大于它的左子树的高度
3.先对当前节点的左节点进行左旋转
4.再对当前节点进行右旋转的操作即可
同理对左旋转的条件判断相反即可
添加方法代码实现:
public void add(Node node){
//添加操作代码
…
//当添加完一个节点后,如果右子树的高度-左子树的高度>1,左旋转
If(rightHeight() - leftHeight() >1 ) {
//如果它的右子树的左子树高度大于它的右子树的右子树高度
If(right != null && right.leftHeight()//先对当前节点的右节点 -> 右旋转
Right.rightRotate();
//再对当前节点进行左旋转
leftRotate();
} else {
//直接进行左旋转
leftRotate();
}
}
//当添加完一个节点后,如果左子树的高度-右子树的高度>1,右旋转
If(leftHeight() - rightHeight() >1 ) {
//如果它的左子树的右子树高度大于它的左子树的左子树的高度
If(left != null && left.rightHeight() > left.leftHeight()) {
//先对当前节点的左节点 -> 左旋转
left.leftRotate();
//再对当前节点进行右旋转
rightRotate();
} else {
//直接进行右旋转
rightRotate();
}
}
}
小总结:先写出左旋和右旋的方法,还有跟树高度,左右子树高度的方法,然后在add方法内部进行两种情况的条件判断,右旋转之前可能需要先左旋一波,左旋转之前可能需要右旋一波。
索引是一种排好序的快速查找的数据结构
索引编写:
不建议创建索引:
-> EXPLAIN + SQL语句 -<
id值的特点:
Type值是访问类型 ,是一个较为重要的一个指标
Possible_keys和key:
Ref:
Extra:
索引不失效的情况:key中不是null 且 Rxtra中没有 using firesort 文件内排序
索引最好设置经常查询的字段复合组合
单表分析:
范围条件会导致using firesort文件内排序,也就是索引失效,一般写=不写<>,所以实现索引优化,就是新建一个索引组合,去掉范围查询的字段,此时再次查询,此索引不会失效。
结论:对左连接的需要对右边的表建立索引,右连接的需要对左边的表建立索引!!!
三表分析:
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card
= book.card
LEFT JOIN phone ON book.card
= phone.card
;
ALTER TABLE book ADD INDEX Y(card); //创建索引
ALTER TABLE phone ADD INDEX Y(card); //创建索引
优化成功
三种情况: 建立的索引用不上; key显示的索引为null; 或者key有,然后using firesort
创建复合索引:
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(NAME,age,pos); //建立索引
情况1,2:出现了跨阶梯的条件查询,违反最佳左前缀法则
复合索引是阶梯型的,按索引顺序条件的不会失效,如果条件查询出现了跨阶梯,则会索引失效,等于断链了
例如索引 idx_abc 查询a, ab, abc,ac都可以使用索引,最佳左前缀法则
就此表的索引分析,如果查询 WHERE age=1 AND pos=1; 则会索引失效,缺少开头name字段,违反了最佳左前缀法则
如果查询 WHERE NAME=1 AND pos=1; 索引不会失效,
但是生效的只有a,而c不会生效
带头大哥不能死,中间兄弟不能断
情况3:在索引列上做任何操作,会导致转向全表扫描
EXPLAIN SELECT * FROM staffs WHERE NAME = ‘July’; //可以正常使用索引
EXPLAIN SELECT * FROM staffs WHERE LEFT(NAME,4) = ‘July’; //尽管查询结果一样,但是对索引使用了函数,使得索引使用失败
情况4:范围之后全失效
对条件语句的范围判断字段之后的字段失效,只对前部分的字段索引生效
情况5:尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))
减少select *
在索引列查找到需要的数据,如果字段在索引表内都存在,则不需要回表去查询数据表的内容,直接返回输出索引表的数据内容即可,效率会高一点,体现在extra项多了一个using index
情况6:使用不等于 != 的时候索引会失效导致全表扫描,mysql8.0 的时候是range,不会是all全表扫描了
情况7:is null is not null 也无法使用索引
情况8:like以通配符开头‘%abc’mysql索引失效会变成全表扫描的操作
一般写%写在右边,这样查询的type是range,而不是all全表扫描
但是,如果%x%,可以使用覆盖索引或者id避免all全表扫描,type是index了
情况9:字符串不加单引号会导致索引失效
情况10:少用or,用它来连接时会索引失效
索引口诀:
全值匹配我最爱,带头大哥不能死,中间兄弟不能断,索引列上少计算,
范围之后全失效,Like百分写最右,覆盖索引不写星,字符串里有引号
CREATE TABLE test03(
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
c1 CHAR(10),
c2 CHAR(10),
c3 CHAR(10),
c4 CHAR(10),
c5 CHAR(10)
);
INSERT INTO test03(c1,c2,c3,c4,c5) VALUES(‘a1’,‘a2’,‘a3’,‘a4’,‘a5’);
INSERT INTO test03(c1,c2,c3,c4,c5) VALUES(‘b1’,‘b2’,‘b3’,‘b4’,‘b5’);
INSERT INTO test03(c1,c2,c3,c4,c5) VALUES(‘c1’,‘c2’,‘c3’,‘c4’,‘c5’);
INSERT INTO test03(c1,c2,c3,c4,c5) VALUES(‘d1’,‘d2’,‘d3’,‘d4’,‘d5’);
INSERT INTO test03(c1,c2,c3,c4,c5) VALUES(‘e1’,‘e2’,‘e3’,‘e4’,‘e5’);
建索引
CREATE INDEX idx_test03_c1234 ON test03(c1,c2,c3,c4);
SHOW INDEX FROM test03;
/
EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2=‘a2’ AND c3=‘a3’ AND c4=‘a4’;
//全索引使用
EXPLAIN SELECT * FROM test03 WHERE c4=‘a4’ AND c2=‘a2’ AND c3=‘a3’ AND c1=‘a1’;
//全索引使用,mysql自动优化执行顺序,把c1移到前面
EXPLAIN SELECT * FROM test03 WHERE c4=‘a4’ AND c2=‘a2’ AND c3>‘a3’ AND c1=‘a1’;
//c123有效,c3使用了范围查询使得c4失效
/
EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2=‘a2’ ORDER BY c3;
//c12有效,c3的作用在排序而不是查找
EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2=‘a2’ AND c4=‘a4’ ORDER BY c3;
//c4无效
EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2=‘a2’ ORDER BY c4;
//出现using filesort
/
EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ ORDER BY c2, c3;
//正常,order by后的字段按照顺序排列了,查询使用了c1,c2,c3在排序上作用了
EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ ORDER BY c3, c2;
//出现firesort 因为order by后的字段不按正确的顺序排序
EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ AND c2=‘a2’ ORDER BY c3, c2;
//不出现firesort,因为c2已经是常量了,当作无用即可
/
EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ GROUP BY c2, c3;
//正常,和orderby类似,按索引字段顺序即正常
EXPLAIN SELECT * FROM test03 WHERE c1=‘a1’ GROUP BY c3, c2;
//出现using——temporary 灭绝师太
1 Explain
2 show profile
小表驱动大表,小的数据集驱动大的数据集
例子:
1.当B表的数据集小于A表的数据集,用in优于exisits
SELECT * FROM A WHERE id IN(SELECT id FROM B )
等价于:
FOR SELECT id FROM B
FOR SELECT * FROM A WHERE A.id = B.id
2.当A表的数据集小于B表的数据集,用exisits优于in
SELECT * FROM A WHERE EXISTS(SELECT 1 FROM B WHERE A.id = B.id)
等价于:
FOR SELECT * FROM A
FOR SELECT * FROM B WHERE A.id = B.id
总结: in会先加载右边的语句,所以右边的需要放数据集较小的,这样外层循环才满足小的,exists则是先执行左边的语句,因此相反
尽量使用Index方式排序,避免使用Filesort方式排序
CREATE TABLE tblA(
age INT,
birth TIMESTAMP NOT NULL
);
INSERT INTO tblA(age,birth) VALUES(22,NOW());
INSERT INTO tblA(age,birth) VALUES(23,NOW());
INSERT INTO tblA(age,birth) VALUES(24,NOW());
CREATE INDEX idx_A_ageBirth ON tblA(age,birth); //创建索引
EXPLAIN SELECT * FROM tblA WHERE age>20 ORDER BY age; //正常
EXPLAIN SELECT * FROM tblA WHERE age>20 ORDER BY age,birth; //正常
EXPLAIN SELECT * FROM tblA ORDER BY birth; //firesort,缺少头部
EXPLAIN SELECT * FROM tblA WHERE age = 20 ORDER BY birth;
//不会重排序,因为第一个字段已经是常量了
总结:
orderby如果没有where语句的话,就需要按照从头开始,中间兄弟不能断,否则会出现firesort
如果有where语句,则组合的字段应该是连接的,如果where有范围判断,则会出现firesort
默认mysql没有开启慢查询日志,需要手动开启,如果不是为了调优,则不加一开启此功能,因为会带来一定的性能影响
SHOW VARIABLES LIKE ‘%slow_query_log%’;
SHOW VARIABLES LIKE ‘%slow_query_log%’; //查看是否开启,日志文件位置
SET GLOBAL slow_query_log = 1;
//只对当前的数据库生效,且重启后恢复默认OFF,改配置文件可以默认ON
SHOW VARIABLES LIKE ‘long_query_time%’;
//默认超时时间10秒
SET GLOBAL long_query_time = 10;
//修改默认超时时间,重启或者新开端口可生效
SHOW GLOBAL VARIABLES LIKE ‘long_query_time%’; //查看全局新的超时时间
SELECT SLEEP(11); //模拟查询超过3秒,后续查看慢查询日志
Mysqlddumpslow -s -t 10 日志文件绝对路径
得到返回记录集最多的十个SQL
CREATE TABLE mylock (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) DEFAULT ‘’
) ENGINE MYISAM;
INSERT INTO mylock(NAME) VALUES(‘a’);
INSERT INTO mylock(NAME) VALUES(‘b’);
INSERT INTO mylock(NAME) VALUES(‘c’);
INSERT INTO mylock(NAME) VALUES(‘d’);
INSERT INTO mylock(NAME) VALUES(‘e’);
//查看open的表
SHOW OPEN TABLES;
1手动增加表锁,可读或可写
LOCK TABLE mylock READ,book WRITE;
2全解锁
UNLOCK TABLES;
3加读锁
LOCK TABLE mylock READ;
加了读锁之后,此会话内不能对此表进行读以外的任何操作,可以操作其他表
其他会话可以对此表进行读操作,因为读锁是共享锁,
其他会话进行读以外的任何操作则会进入阻塞状态,在主会话执行unlock tables之后会执行阻塞了的操作
SELECT * FROM mylock; //读操作正常
INSERT INTO mylock(NAME) VALUES (‘e’); //写操作报错,因为读锁未解
UNLOCK TABLES; //解锁
4加写锁
LOCK TABLE mylock WRITE;
SELECT * FROM mylock;
//读操作正常
INSERT INTO mylock(NAME) VALUES (‘e’);
//写操作正常,主会话所有对此表的操作都正常
SELECT * FROM book;
//读取其他表出错
其他会话: 可以读取其他表,查询写锁表会进入阻塞状态,等待锁释放
myisam引擎的读写调度机制是写优先,则myisam不适合做写为主表的引擎,
因为写锁会阻塞其他线程的任何操作,而频繁的写操作会造成其他线程的永久阻塞
InnoDB与MyISAM的最大不同有两点:一是支持事务,二是采用了行级锁
事务复习:
事务的四大问题: 更新丢失,脏读,不可重复读,幻读
事务的隔离级别: 未提交读,已提交读,可重复读,可序列化
(级别越高越安全,但是并发度越低)
mysql的默认隔离级别是可重复读RepeatableRead
(不能解决幻读,但是mysql内部有间隙锁来解决幻读)
行锁:
行锁是事务特有的,在事务未提交前对数据行的修改操作则相当于对此数据行加了锁,其他会话在此事务未提交之前读取到的数据是老数据,而且不能对此数据行进行操作,这就是行锁.
代码测试:
CREATE TABLE test_innodb_lock (
a INT(11),
b VARCHAR(16)
)ENGINE=INNODB;
SET autocommit=0; //设置手动提交事务,才会有行锁的出现
UPDATE test_innodb_lock SET b=‘4001’ WHERE a=4;
//修改后未提交事务,其他会话读取的数据是未修改的4000,并且写此数据行会阻塞
COMMIT; //提交事务,更新数据,解除其他会话的写阻塞状态
主会话对数据的修改,然后未commit的情况下,其他会话读取到的数据都是未修改的
//varchar类型的数据在select的时候不加‘’单引号,虽然mysql会自动类型转换,操作不会报错,但是行锁会升级为表锁
什么是间隙锁
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁,
InnoDB会给符合条件的已有数据记录的索引项加锁,对于键值在条件范围内但并不存在的记录,叫做间隙
间隙锁解决了RR隔离级别下会出现的幻读漏洞问题
间隙锁的危害
当锁定一个范围内的键值之后,即使某些不存在的键值也会被无辜的锁定,而造成在锁定的时候无法插入锁定键值范围内的任何数据
锁总结
Innodb存储引擎实现了行锁锁定,与Mylsam的表锁相比,付出的性能损耗要高一些,但是在整体并发处能力要强很多
1.master将改变记录到二进制日志 binarylog
2.slave将master的binarylog EVENTS 拷贝到它的中继日志relarylog
3.slave重做中继日志中的事件,将改变应用到自己的数据库中,mysql复制是异步的且串行化的