java|MYSQL优化自学笔记 java|mysql

文章目录

MYSQL的数据结构为什么是b+树
平衡二叉树（间接学习一下）
索引
SQL语句性能分析
索引优化
索引失效
索引面试题小测*
查询优化
ORDER BY 优化
慢查询日志
Mysql锁机制
- 表锁（myisam存储引擎）
- 行锁 (InnoDB存储引擎)
- 索引失效行锁变表锁
- 间隙锁
主从复制

MYSQL的数据结构为什么是b+树为什么不是Hash哈希表存储：

Hash结构，不利于范围查找，Hash存储方式本来就是无序的。

为什么不是平衡二叉树存储：

平衡二叉树虽然保证了左右子树的高度差，防止出现没有某一边子树的极端查找缓慢情况，但是当数据量大的情况下，树的深度也是非常高的，而查询的值越底则效率越慢。最致命的缺点是查找大于某一个数的时候，需要先定位到这个数的位置，然后不断的回旋去获取范围内的数值。

为什么不是b树存储：

b树的最大特点是一个节点可以存储两个值，这种的好处就是大大缩减了树的深度，使得查询效率变快，但是范围查找导致的回旋的情况还是存在。

为什么是b+树：

b+树的节点也可以存储两个值，非叶子节点只存储key，叶子节点才是真正的数据，等于非叶子节点上出现的树，在最后一层的叶子节点里面都会出现，value存储了数的地址。最底一层的叶子节点组合成排序了的双向链表，这样在范围查找的时候，只需要定位第一个数据，然后往后遍历即可，不需要回旋，比起b树，大大提高了查询效率。

平衡二叉树（间接学习一下）在二叉搜索树的基础上，通过左旋或右旋的自动优化流程，实现了左右子树的高度差不超过一，防止出现极端只有右子树的情况
代码实现：
左旋转方法

Public void leftRotate（）{
//创建新的节点,以当前根节点的值
Node newNode = new Node(value);
//把新的节点的左子树设置成当前节点的左子树
newNode.left = left;
//把新的节点的右子树设置成当前节点的右子树的左子树
newNode.right = right.left;
//把当前节点的值替换成右子节点的值
value = https://www.it610.com/article/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方法内部进行两种情况的条件判断，右旋转之前可能需要先左旋一波，左旋转之前可能需要右旋一波。
索引索引是一种排好序的快速查找的数据结构

优势是提高查询效率
劣势是在更新索引字段的时候降低表的更新速度，不仅要更新数据表，索引表的b+树也需要更新换位，索引表占用的空间是数据表的1.5倍

索引编写：

SHOW INDEX FROM emp; //查找索引
ALTER TABLE emp ADD INDEX dept_id(deptId); //添加索引

文章图片

不建议创建索引：

1表记录太少
2 经常增删改的表
3 包含许多重复的数据内容的数据列

SQL语句性能分析 -> EXPLAIN + SQL语句 -<

文章图片

【java|MYSQL优化自学笔记】id值的特点：

Id相同，则从上往下顺序之下
id不同的id值越大优先级越高，越先被执行

Type值是访问类型 ,是一个较为重要的一个指标

按最好到最坏的排序：system>const>eq_ref>ref>range>index>ALL
System 查询一个表中唯一的一条记录，最好的扫描情况
Const 通过索引依次就找到匹配的一行数据
Eq_ref 唯一性索引扫描，对于每一个索引键，表中只有一条记录与之匹配
Ref 非唯一性索引扫描，返回匹配某个单独值的所有行
Range 返回一个范围内的所有数据行
All 全表扫描，最坏的扫描情况

Possible_keys和key：

possible_keys 是指可能使用到的索引，key表示实际使用到的索引
Key_len：表示索引中使用的字节数，显示的值为索引字段的最大可能长度，并非实际长

Ref：

表之间的引用关系

Extra：

出现using diresort不好，说明mysql会对数据使用一个外部的索引排序
出现using temporary 使用了order by,group by等会出现临时表的情况，拖慢效率，应该使用和索引字段组合一模一样的来搜索，就不会出现此情况
出现using index是好的，出现了覆盖索引，就是查询的字段是索引字段包含的，不需要回表查询数据行，直接返回索引表的数据即可
temporary就是离谱的救不活了，firesort就是索引不小心失效了还能改改，index是最理想的情况

索引优化索引不失效的情况：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则是先执行左边的语句，因此相反
ORDER BY 优化尽量使用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
Mysql锁机制 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;
表锁（myisam存储引擎） 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存储引擎) 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复制是异步的且串行化的