Mysql 非主键的自增字段 mysql非主键索引

转载

mob6454cc6aeeaf 2023-08-16 14:46:36

文章标签 Mysql 非主键的自增字段 msyql 主键搜索数据 文章分类 MySQL 数据库

一、前言

主键是逻辑键，索引是物理键。主键不实际存在，而索引实际存在于数据库中。
索引会真正产生文件。数据会真正产生文件。
redo log 记录的内容：物理日志，"某个数据页上做了什么修改" ，循环使用。
bin log 记录的内容：逻辑日志，语句的原始逻辑"ID=1 ,2 " ，追加使用。
主键不会产生文件，主键的搜索依靠ID索引树。

二、数据的概念

数据有数据页的概念，mysql也有内存的概念。mysql查询的时候，先检测所在的数据页是否在内存中，如果存在，查询直接返回；如果不存在，则从磁盘加载到内存，再返回结果。

对于更新操作，如果数据在内存中不存在，则先从磁盘加载到内存，cpu修改后，再将值放入内存，并且再更新到redo log 日志表中。

三、索引的原理

1.B+树索引

B+树所有的关键字都出现在叶子节点的链表（稠密索引）中，且链表中的关键字是有序的。非叶子节点只起索引作用（稀疏索引）。
如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+树。
如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，ID 的值为 500，再到 ID 索引树搜索一次。这个过程称回表。
也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，在应用中应该尽量使用主键查询。

2. 一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。
当相邻的两个数据页利用率很低的时候，系统会做数据页合并，合并的过程是分裂过程的逆过程。

3.从性能和存储空间方面考量，自增主键往往是更合理的选择。

4.显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
因此自增主键比较合适。

四、覆盖索引

create table T 
( ID int primary key, 
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '', 
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),
(200,2,'bb'),
(300,3,'cc'),
(500,5,'ee'),
(600,6,'ff'),
(700,7,'gg');

select * from T where k between 3 and 5

这种查询K的索引，首先搜索到主键，然后搜索主键的索引，回表拿到具体的信息。
select ID from T where k between 3 and 5 这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

五、最左前缀原则

索引为(a,b)，查询条件b无法使用联合索引，但是查询条件a可以使用索引。
%%开头是不会使用索引的。

六、关于索引的题

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

由于历史原因，这个表需要 a、b 做联合主键。
那意味着，单独在字段 c 上创建一个索引，就已经包含了三个字段，为什么要创建“ca”“cb”这两个索引？
select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;
问题：这位同事的解释对吗? 哪些索引没有必要，可以删除？

答案：
（1）主键 a，b 的聚簇索引组织顺序相当于 order by a,b ，也就是先按 a 排序，再按 b 排序，c 无序。
–a--|–b--|–c--|–d--
1 2 3 d
1 3 2 d
1 4 3 d
2 1 3 d
2 2 2 d
2 3 4 d

（2）索引 ca 的组织是先按 c 排序，再按 a 排序，同时记录主键。
–c--|–a--|–主键部分b-- （注意，这里不是 ab，而是只有 b）
2 1 3
2 2 2
3 1 2
3 1 4
3 2 1
4 2 3
上面的这个索引ca 与索引c 的数据是一模一样的。