标题
索引
索引的分类
聚簇索引和非聚簇索引
在 MySQL 的 InnoDB 引擎中,每个索引都会对应一颗 B+ 树,而聚簇索引和非聚簇索引最大的区别在于叶子节点存储的数据不同,聚簇索引叶子节点存储的是行数据,因此通过聚簇索引可以直接找到真正的行数据;而非聚簇索引叶子节点存储的是主键信息,所以使用非聚簇索引还需要回表查询,因此我们可以得出聚簇索引和非聚簇索引的区别主要有以下几个:
- 聚簇索引叶子节点存储的是行数据;而非聚簇索引叶子节点存储的是聚簇索引(通常是主键 ID)。
- 聚簇索引查询效率更高,而非聚簇索引需要进行回表查询,因此性能不如聚簇索引。
- 聚簇索引一般为主键索引,而主键一个表中只能有一个,因此聚簇索引一个表中也只能有一个,而非聚簇索引则没有数量上的限制。
哪些情况适合创建索引
①字段的数值有唯一性的限制
②频繁作为WHERE查询条件的字段
③经常GROUP BY和ORDER BY的列
④UPDATE、DELETE的WHERE条件列
⑤DISTINCT字段需要创建索引
⑥使用列的类型小的创建索引
⑦使用字符串前缀创建索引
区分度计算公式:
count(distinct left(列名, 索引长度))/count(*)
要注意,order by不支持前缀索引
⑧使用最频繁的列放到联合索引的左侧
哪些情况不适合创建索引
①在where中使用不到的字段,不要设置索引
②数据量小的表最好不要使用索引
③有大量重复数据的列上不要建立索引
④避免对经常更新的表创建过多的索引
⑤不建议用无序的值作为索引
⑥删除不再使用或者很少使用的索引
⑦不要定义冗余或重复的索引
MyISAM与InnoDB的对比
索引有哪些优缺点
优点:
- 可以大大加快数据的检索速度,这也是创建索引的最主要原因。
缺点:
- 时间方面:创建索引和维护索引要耗费时间,具体的,当对表中的数据进行增,删,改的时候,索引也要动态维护,会降低增删该的执行效率。
- 空间方面:索引要占物理空间。
讲一讲MySQL的最左前缀原则
如果索引了多列,要遵循最左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中的列–带头大哥不能死,中间兄弟不能断。(联合索引)
索引下推
- 当未开启索引下推时:根据筛选条件在索引树中筛选第一个条件,获取结果后进行回表,再进行其他条件筛选,再次进行回表。
- 开启索引下推:在条件查询时,当前索引树如果满足全部筛选条件,可以在当前树中完成全部筛选过滤,得到比较小的结果集再进行回表操作。
哪些情况下索引会失效
①违反了最左前缀法则
②范围查询右边的列,不能使用索引
③不要在索引列上进行运算,索引将失效
④以%开头的like模糊查询,索引失效。如果仅仅是尾部模糊查询,索引不会失效。
为什么推荐使用自增长主键作为索引
-
如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页
-
如果使用非自增主键(如果×××号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置,此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构
MySQL中in和exists的区别
如果查询的两个表大小相当,那么用in和exists差别不大;如果两个表中一个较小一个较大,则子查询表大的用exists,子查询表小的用in;
例子 表A(小表),表B(大表):
select * from A where cc in(select cc from B) -->效率低,用到了A表上cc列的索引;
select * from A where exists(select cc from B where cc=A.cc) -->效率高,用到了B表上cc列的索引。
表A(大表),表B(小表):
select * from B where cc in(select cc from A) -->效率高,用到了B表上cc列的索引
select * from B where exists(select cc from A where cc=B.cc) -->效率低,用到了A表上cc列的索引。
覆盖索引
- 索引覆盖,即将查询sql中的字段添加到联合索引里面,只要保证查询语句里面的字段都在索引文件中,就无需进行回表查询;
- 实际开发中,不可能把所有字段都建立联合索引,可以根据实际业务场景,把经常需要查询的字段建立到联合索引中
- 如果返回的列中没有创建索引,有可能会触发回表查询,所以尽量避免使用select *
由于覆盖索引可以减少树的搜索次数,显著提高性能,所以使用覆盖索引是一个常用的优化手段
MySql超大分页查询
Mybatis 是如何进行分页的
- 第一种,直接在 Select 语句上增加数据库提供的分页关键字,然后在应用程序里面传递当前页,以及每页展示条数即可。
- 第二种,使用 Mybatis 提供的 RowBounds 对象,实现内存级别分页。
- 第三种,基于 Mybatis 里面的 Interceptor 拦截器,在 select 语句执行之前动态拼接分页关键字。
事务的特性
事务是一组操作的集合,他是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
ACID