侧边栏壁纸
博主头像
云中有鹿博主等级

行动起来,活在当下

  • 累计撰写 57 篇文章
  • 累计创建 12 个标签
  • 累计收到 10 条评论

目 录CONTENT

文章目录

MySQL知识点

likecy
2023-08-31 / 0 评论 / 0 点赞 / 168 阅读 / 5425 字

索引定义

排好序的可以快速查找数据的数据结构(单链表,双链表)

索引的数据结构是二叉树的索引方式,没有索引mysql就会一条条遍历,容易造成数据冗余,加大工作量

二叉树缺点:在极端情况下比如初始数据是2,继续插入会形成链表,容易降低效率; 解决这种情况是使用B-Tree结构,他会平衡起来

优势:降低数据排序成本,降低CPU消耗,提高查询速度 缺点:索引需要占用空间,会降低表的更新速度

索引使用场景:

1.主键自动建立

2.频繁查询作为查询条件的字段应创建索引(where)后面

3.外键关系应建立

4.多字段查询向创建组合索引

5.查询排序的字段

6.查询统计或者分组字段

索引分为:主键索引,唯一索引,单值索引,复合索引

explain关键字可以进行分析查询执行过程 访问类型排序:ALL>index>range>ref>eq_ref>const>system>Null

索引失效

  1. 最佳左前级法则:如果索引多列,要道循最左前级法则,指的是查询从索引的最左前列开始并且不跳过索引中的列。

    alter table students add index idx_sname_age_score(sname,age,score);
    
    explain select * from students where sname ="小明" and age = 22 and score =100;
    
    explain select * from students where sname ="小明" and age = 22;
    
    explain select * from students where sname ="小明";
    
    -- 会导致score索引失效,只使用了sname的索引 
    explain select * from students where sname ="小明" and score =100;
    -- 索引失效,全表扫描
    explain select * from students where age = 22 and score =100;
    
  2. 不在索引列上做任何计算、函数操作,会导致索引失效而转向全表扫描。

    explain select * from students where sname ="小明";
    -- 索引失效 全表扫描
    explain select * from students where left(sname,2) ="小明";
    
  3. 存储引擎不能使用索引中范围条件右边的列。

    -- 索引只会使用sname,age.
    explain select * from students where sname ="小明" and age > 22 and score =100;
    
  4. MYSQL 在使用不等于时无法使用索引会导致全表扫描。

    -- 索引失效,全表扫描
    explain select * from students where sname !="小明";
    
  5. is null 可以使用索引,但是 is not null 无法使用索引。

    explain select * from students where sname is null;
    -- 索引失效,全表扫描
    explain select * from students where sname is not null;
    
  6. like 以通配符开头会使索引失效导致全表扫描。

    -- 索引失效,全表扫描
    explain select * from students where sname like "%明";
    -- 使用到了索引
    explain select * from students where sname like "明%";
    
  7. 字符串不加单引号索引会失效。

    	-- 索引失效
    	explain select * from students where sname = 123;
    
  8. 使用OR连接时索引失效。

    -- 索引失效,全表扫描
    explain select * from students where sname ="小明" or age = 22;
    

假设index(a,b,c)复合索引: 注意 or 索引不会生效,and 会自动调整顺序为最左前列

Where语句索引是否被使用
where a = 3Y,使用到了a
where a=3 and b =5Y,使用到了a,b
where a = 3 and b = 4 and c = 5Y,使用了a,b,c
where b=3 或者 where b =3 and c =4 或者 where c = 4N
where a = 3 and c = 4Y,使用到了a,但是c不可以,b中间断了,key_len 是a的
where a = 3 and b > 4 and c = 5Y,使用到了a和b,C不能使用,它在范围查询之后,b之后就断了
where a = 3 and b like ‘xx%’ and c = 4Y,使用到了a,b,c
where a = 3 and b like ‘%xx’ and c = 4Y,只使用到了a
where a = 3 and b like ‘xx%xx%’ and c = 4Y,使用到了 a,b,c

总结:

  1. 对于单值素引,尽量选择针对当前查询字段过滤性更好的素引。

  2. 对于组合索引,当前 where 查询中过滤性更好的字段在索引字段顺序中位置越靠前越好。

  3. 对于组合索引,尽量选择能够包含在当前查询中 where 子句中更多字段的素引。

  4. 尽可能通过分析统计信息和调整 query 的与法来达到选择合适索引的目的。

排序优化

  1. 尽量避免使用Using FileSort 方式排序。
  2. order by 语句使用索引最左前列或者使用where 子句 与 order by 子句条件组合满足索引最左前列。
  3. where 子句中如果出现索引范围查询会导致order by 索引失效。
KEY a_b_c(a,b,c)

-- order by 能使用索引最左前缀
ORDER BY a
ORDER BY a,b
ORDER BY a,b,c
ORDER BY a DESC,b DESC,c DESC

-- 如果WHERE使用索引的最左前缀定义为常量,则 order BY 能使用索引
WHERE a = const ORDER BY b,c
WHERE a = const and b = const ORDER BY c
WHERE a = const and b > const ORDER BY b,c

-- 不能使用索引进行排序
 ORDER BY a ASC,b,DESC,c DESC -- 排序不一致
 WHERE g = const ORDER BY b,c  -- 丢失索引a
 WHERE a = const ORDER BY c -- 丢失索引b
 WHERE a = const ORDER BY a,d -- d 不是索引的一部分
 WHERE a in (...) ORDER BY b,c -- 对于排序来说,多个相等条件也是范围查询

关联查询优化

内连接时,MYSQL 会自动把小结果集选为驱动表,所以大表的字段最好加上索引。左连接时,左表会全表扫描,索引右边大表字段最好加上索引,右外连接 同理。

我们最好保证被驱动表上的字段建立索引。

0

评论区