MySql （中、高级）总结

索引的基本原理

可以拿字典来举例

把创建了索引的列的内容进行排序

对排序的结果生成排序标识

在排序表内容上拼接数据地址链

查询数据先拿到倒排表内容，再取出数据地址链，然后拿到数据

mysql聚簇索引和非聚簇索引的区别

聚簇索引：索引和数据放在了一起，顺序一致，适合范围查询、排序，innodb

非聚簇索引：数据索引分开， myisam

mysql索引的数据结构，各自优缺点

mysql索引数据结构：1、b+tree 2、hash索引

hash索引只适合单条等值匹配的数据，不适合联合查询的最左匹配，不适合利用索引进行排序，like模糊查询（范围查询）等

b+tree索引可以以上操作

mysql 二叉树，红黑树（平衡二叉树），B-tree(多路平衡查找树)，B+tree，hash

顺序插入数据时，会形成一个单路链表，查询性能会降低 。

红黑树是在二叉树基础上做了拓展，也叫平衡二叉树，但是数据量大的话，层级也会很深

B-tree 深度最多只能有5个，每个节点最多只能存储4个key ,5个指针（n+1）

B+tree 所有的数据都会出现在叶子节点，叶子节点形成一个单向链表

hash索引是通过hash算法，把键，值算成新的hash值，然后存储到hash表中，如果hash冲突了，就通过链表解决该问题

为什么innoDB存储引擎采用B+tree 索引结构

比二叉树层级少，搜索效率高

比B-tree的存储key减少，因为每个叶子节点固定大小16k,B-tree是无论哪个层级都存了数据，就因为1,2,3,4,5层级存储了数据，导致层级会变高（大数据情况下），所以B+tree是较好的选择

hash索引只能等值匹配，不能范围匹配，所以B+tree是最优选择

索引设计原则

1、适合索引的列是出现在where子句中的列，或者连接子查询的列中

2、表数据量不多的情况下可以不建索引

3、使用短索引

4、不要过度索引，会降低写操作的性能

5、有外键的话一定要建索引

6、更新频繁的字段不需要建立索引

7、性别、标识不适合建索引，因为查出来的数据回很多，

8、尽量的扩展索引，不要新建索引，用联合索引

9、text,image,bit等数据类型列不需要创建索引，如需内容查找可以建立fulltext(全文索引)

mysql锁的类型有哪些

行级锁、表级锁、记录锁、页级锁、间隙锁等

innodb：默认使用行锁

myisam: 默认使用表锁

事务的基本特性和隔离级别

特性：ACID （原子性，一致性，隔离性，持久性）

级别：

read uncommit 读未提交（脏读）

read commit 读已提交（两次读取结果不一致，不可重复读）（oracle默认隔离级别）

repeatable read 可重复读（mysql默认级别 幻读）

serializable 串行 ，一般不用，会导致大量的锁竞争

myisam和innodb的区别

myisam:不支持事务，支持表级锁，会根据hash值找数据块

innodb:支持acid的事务，支持行级锁和外键约束，支持并发的写操作，

为什么不能给所有数据库列加索引

1、索引不是越多越好，因为方便了查询，但是会降低修改，删除等操作

2、索引也需要占用物理空间

SQL慢查询优化

开启mysql慢查询配置开关，然后查阅对应的文件，取出sql进行优化

查询条件是否命中，是否加载了不需要用到的列，是否数据量太大，

通过sql执行计划查看是否有没有命中索引

mysql的EXPLAIN执行计划

type:  all<index<range<ref<eq_ref<const<system 

system: 表中只有一行数据，相当于系统表

const: 通过索引一次命中 ，匹配一行数据

eq_ref: 唯一索引扫描 ，会回表查询

ref: 非唯一索引扫描，会回表查询

range: 范围查询，一般用于 between、 >、 < 

index: 只遍历索引数，所有的索引

All: 全表扫描，没有走索引

索引失效

明明创建了索引，为什么写的sql,通过explain查阅，却没有索引？

       1、SQL的联合索引是否包含最左边的字段，因为sql是最左匹配的(全部失效)

       2、联合索引有三列，中间的索引字段不查，查询出来只会有最左边的（部分失效）

       3、使用了范围查询的右侧的索引也会失效，解决方案：大于改成大于等于，小于改成小于等于

       4、不要在索引列上做函数运算操作

       5、字符串不加单引号也会失效

       6、模糊查询，如果是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，则会失效

       7、用or分割的条件，前面的条件有索引，后面的没有索引，也会失效 解决方案：创建后面没有索引的列

       8、如果mysql评估使用索引比全表更慢，则不使用索引

       9、如果索引字段的值为null，也会造成索引失效

SQL优化

insert 优化

1、执行批量插入操作（一次性插入数据不超过1000条）

insert into table_name values(1,'张三'),(2,'李四'),...

2、手动事务提交

start transaction;
insert into table_name values(1,'张三'),(2,'李四'),...
insert into table_name values(1001,'张三'),(1002,'李四'),...
insert into table_name values(2001,'张三'),(1002,'李四'),...
commit;

3、主键顺序插入

4、load指令

update 优化

        更新字段的时候要根据索引字段更新，不然会出现行锁升级为表锁

        这里分享一个我项目中遇到的一个sql, 意思是 B表的数据修改到A表

UPDATE user_copy1 AS a
INNER JOIN `user` AS b ON a.id = b.id 
SET a.NAME = b.NAME 
WHERE
	b.id IN ( 3, 8 )

主键优化

叶分裂 叶合并

1、减小主键的长度

2、插入数据时，尽量顺序插入，选择auto_increment自增主键

3、尽量不要用UUID作为主键或者其他自然主键

4、不要修改主键

order by 优化

1、根据排序字段建立合适的索引

2、尽量使用覆盖索引

group by 优化

1、创建索引

2、分组的操作也满足最左匹配

limit 优化

越往后的数据，分页查询就很慢

1、覆盖索引+子查询

未优化前sql查询

select * from table_name limit 10000000,10

优化后的sql查询

select id from table_name order by id limit 10000000,10  -- 这里查询id 不需要回表查询其他数据
select * from table_name where id in(select id from table_name order by id limit 10000000,10) -- 这个sql运行起来，可能会报错，当前mysql不支持这个语法 in后面用到了limit关键字

最终sql为：
select * from table_name t1,(select id from table_name order by id limit 10000000,10) t2 where t2.id = t1.id

2、阈值

SELECT * FROM table_name WHERE id >= (SELECT id FROM table_name LIMIT 15000000, 1) LIMIT 10;

3、通过between and

SELECT * FROM table_name WHERE id BETWEEN 10000000 AND 10000010;

count 优化

count(主键)

        把每一行主键id值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加（主键不可能为null），返回对应的值

count(某个字段)

        没有not null 约束：把每一行对应字段的值都取出来，返回给服务层，服务层拿到值后，进行判断值是否为null，不为null，进行累加，返回对应的值

        有not null约束：把每一行对应字段的值都取出来，返回给服务层，服务层拿到值后，进行累加，返回对应的值

count(1)

        遍历每行数据，把对应这行数据赋值为1 ，然后进行累加，返回对应的值

count(*)

        直接按行进行累加

骚操作查询

-- 查询出一张表多个重复的数据
SELECT
	* 
FROM
	user_base 
WHERE
	phone IN ( SELECT phone FROM ( SELECT phone, count( 1 ) count FROM user_base GROUP BY phone ) t1 WHERE count > 1 ) 
ORDER BY
	phone

 如果小伙伴们有什么疑问，欢迎下面评论。欢迎指正。如还有什么不懂的加我 QQ：517861659

如果没有及时回复，可以点我先问问AI机器人https://chatgpt.byabstudio.com/login?code=202307011314