索引的基本原理
可以拿字典来举例
把创建了索引的列的内容进行排序
对排序的结果生成排序标识
在排序表内容上拼接数据地址链
查询数据先拿到倒排表内容,再取出数据地址链,然后拿到数据
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