mysql唯一索引冲突更新 mysql唯一索引原理

转载

mob64ca13fe62db 2024-02-02 17:46:03

文章标签 mysql唯一索引冲突更新数据辅助索引主键 文章分类 MySQL 数据库

索引分类：

主键索引、唯一索引、普通索引、组合索引、全文索引；

主键索引

非空唯一索引，一个表只有一个主键索引

PRIMARY KEY(key)

唯一索引

不可以出现相同的值，可以有 NULL 值；

UNIQUE(key)

普通索引

允许出现相同的索引内容

INDEX(key)

- OR

KEY(key [,…]

组合索引

对表上的多个列进行索引

INDEX idx(key1, key2[, …]);

UNIQUE(key1, key2[, …]);

PRIMARY KEY(key1, key2[, …]);

全文索引

FULLTEXT;

在短字符串中用LIKE %; 在全文索引中用 match 和 against;

主键的选择

innodb 中的表是索引组织表，每张表有且仅有一个主键；

1. 如果显示设置 PRIMARY KEY, 则该设置的 key 就为该表的主键；

2. 如果没有显示设置，则从非空唯一索引中选择

a. 只有一个非空唯一索引，则选择该索引为主键；

b. 有多个非空唯一索引，则选择声明的第一个为主键

3. 没有非空唯一索引，则自动生成一个 6字节的 _rowid 作为主键。

约束

为了实现数据的完整性，对于innodb, 提供了以下几种约束，primary key, unique key, foreign key, default, not null

外键约束

外键用来关联两个表，来保证参照完整性；MyISAM存储引擎本身并不支持外键，只起到注视作用；而innodb完整支持外键；

create table parent (
	id int not null,
	primary key(id)
) engine = innodb;

create table child(
	id int,
	parent_id int,
	foreign key(parent_id) references parent(id) on delete cascade on  update cascade
) engine = innodb;

- - 被引用的表为父表， 引用的表称为字表；
- - 外键定义时，可以设置  行为  on delete  和 on update,  行为 发生时 操作可选择；
- - cascade   子表做同样的行为
- - set null  更新子表相应字段为 NULL
- - NO ACTION  父类做相应行为报错
- - RESTRICT  同 NO ACTION


insert into  parent values(1);
insert into  parent values(2);
insert into  child values(10, 1);
insert into  child values(20, 2);
delete from parent where id =1;

如果直接删除parent 表，则报错！图如下：

错误码3730

mysql唯一索引冲突更新 mysql唯一索引原理_数据

试一试： NO ACTION

试一试： NO ACTION

create table parent (
	id int not null,
	primary key(id)
) engine = innodb;

create table child(
	id int,
	parent_id int,
	foreign key(parent_id) references parent(id) on delete no action on  update no action
) engine = innodb;

查看 child 的外键如下图：

mysql唯一索引冲突更新 mysql唯一索引原理_辅助索引_02

准备数据来插入：

insert into  parent values(1);
insert into  parent values(2);
insert into  child values(10, 1);
insert into  child values(20, 2);

进行试验删除试试。

delete from parent where id =1;

报错，因为外键约束引用它！！！

1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)), Time: 0.012000s

进行试验删除试试

delete from parent where id =3;
 
Affected rows: 1, Time: 0.003000s

这个成功删除了，是因为child 中数据没有引用 id=3 的！

约束和索引的区别：

创建主键索引或者唯一索引的同时创建了相应的约束；

但是约束是逻辑上的概念；索引是一个数据结构即包含逻辑的概念也包含物理的存储方式；

索引存储

innodb 由段，区，页组成；

段分为数据段，索引段，回滚段等；

区大小为 1MB ，一个区由64个连续页构成；

页的默认值为 16k; 页为逻辑页，磁盘物理页大小一般为 4K 或 8K

为了保证区中的页的连续，存储引擎一般一次从磁盘中申请 4-5个区

页的补充：

页是 innodb 磁盘管理的最小单位；默认是 16k, 可通过 innodb_page_size 参数来修改；

图：

mysql唯一索引冲突更新 mysql唯一索引原理_mysql唯一索引冲突更新_03

B+树实现的索引：

结构如下：

mysql唯一索引冲突更新 mysql唯一索引原理_mysql唯一索引冲突更新_04

聚集索引

b+树中查找数据的过程

按照主键构造的B+树；叶子节点中存放数据页；数据也是索引的一部分；

select * from user where id >= 18 and id < 40;

找到过程如下：

mysql唯一索引冲突更新 mysql唯一索引原理_辅助索引_05

辅助索引

叶子结点不包含行记录的全部数据；辅助索引的叶子结点，除了用来排序的 key 还包含一个 bookmark; 该书签存储了聚集索引的 key;

- 某个表包含 id name lockyNum; id 是主键， lockyNum 存储辅助索引；

select * from user where lockyNum = 33;

mysql唯一索引冲突更新 mysql唯一索引原理_主键_06

覆盖索引

从辅助索引中就能找到数据，而不需通过聚集索引查找；

因为辅助索引树一般低于聚集索引树；较少磁盘io;

索引失效

select …. where A and B 若 A和 B 中有一个不包含索引，则索引失效；

索引字段参数运算，则索引失效；例如 from_unixtime(idx) = ‘2021-04-30’;

索引字段发生隐式转换，则索引失效；例如 ‘1’ 隐式转换为 1；？？

Like 模糊查询，通配符%开头，则索引失效；例如： select * from user where name like ‘%ark’;

在索引字段上使用 NOT <> != 索引失效；如果判断 id <> 0 则修改为 idx > 0 or idx < 0;

组合索引中，没使用第一列索引，索引失效；

索引的原则

查询频次较高且数据量大的表建立索引；

索引选择使用频次较高，过滤效果好的列或者组合；

对于很长的动态字符串，考虑使用前缀索引；

有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样可以大大的节约索引空间，从而提高索引效率，

但这会降低索引的选择性。

索引的选择性：指不重复的索引值和数据表记录总数的比值。

索引的选择性越高则查询效率越高，因为选择性更高的索引可以让 mysql 在查找的时候过滤掉更多的行。

对于BLOB, TEXT, VARCHAR 类型的列，必须使用前缀索引，因为mysql 不允许索引这些列的完整长度，

总之，原则就是要选择足够长的前缀以保证较高的选择性，但又不能太长。

select count(distinct left(name, 3))/count(*)  as  sel3,
    count( distinct left(name, 4))/ count(*)    as  sel4,
    count( distinct left(name,5))/count(*)      as sel5,
    count( distinct left(name,6))/count(*)      as  sel6
    from user;
 
    alter table user add key(name(4));

对于组合索引，考虑最左侧匹配原则和覆盖索引；

尽量选择区分度高的列作为索引；该列的值相同的越少越好；

select count(distinct idx) / count(*)  from table_name;

尽量扩展索引，在现有索引的基础上，添加复合索引；

不要 select * ; 尽量只列出需要的列字段；

索引列，列尽量设置为非空；

B+ 树索引

B 代表 balance 平衡;

B+ 树索引通过键值（如 id=3 ）并不能直接找到具体的行。它是把行（数据行记录）所在的页，读入内存，再从内存中查找，最后得到要找的记录（数据）。

二分查找法：（折半查找法）

首先是有一组排好顺序的记录。如 10， 20，30， 40，50，60，70， 80，90，100

问题是从这样的一组排好顺序的记录中查找某一个指定记录。？

采取的方法是：1. 首先将中间位置的记录作为比较对象。 2；要找的元素和比较对象比较，如果小于比较对象缩小到左半部分；如果大于比较对象缩小到右半部分。

结论：平均来说二分查找法比顺序查找法要好，效率高。

二叉树：/（二叉查找树）。

二叉树有哪些遍历方法？

前序遍历：先访问根结点，再访问左子树，再访问右子树；

中序遍历：先访问左子树，在父节点，再右子树；

后序遍历：先访问左子树，再右子树；最后是访问根结点；

层序遍历：先访问根结点，从上到下逐层遍历。同一层中从左到右访问。

二叉排序树 / 二叉查找树。 binary sort tree

它特点1: 左子树上所有结点的值均小于它的根结点的值；

2. 右子树上所有结点的值，均大于它的根结点的值；

平衡二叉树 (Self -Balancing Binary Search Tree) : 首先是一种二叉排序树；其中每一个结点的左子树和右子树的高度差小于等于 1；

维护一个平衡二叉树，比如插入，更新和删除操作。这些都是通过左旋或右旋来实现的，这都是开销。

B+ 树：是一种平衡查找树。叶子结点上从小到大排序顺序排序。各个叶子结点使用指针连接。

示意图： todo;

B+ 树插入操作： 3种情况； Leaf Page满； Index Page满操作表 todo; 有拆页的可能

旋转发生在Leaf Page已满。但是其左右结点没有满的情况下，这时 B+树不急于拆分页的操作，而是将记录移到页的兄弟结点上。

旋转使B+ 树减少了一次拆分操作。

B+ 树的删除操作。

B+ 树使用填充因子 fill factor 来控制树的删除； /依据填充因子来决定怎么删除；填充因子 >= 50%

叶子节点小于填充因子，中间节点小于填充因子，操作三种情况表 todo; 有合并页的可能。

B+ 树索引； B+ 树在数据库的应用/实现。

B+ 树索引特点：高扇出性，B+ 树的高度一般 2-4 层。查找一行记录（ID = xxx, ID 是主键）最多需要2到4次 IO；假如机械硬盘每秒100次IO，则查询一次需要时间 0.02——0.04 秒。

B+ 树索引分为：聚集索引（clustered index) ; 辅助索引（secondary index) / 非聚集索引（non-clustered index);

叶子节点存放数据；聚集索引的叶子节点存放时一整行的数据（完整的记录）；

聚集索引，中每个叶子节点都是一个页；叶子节点之间使用双向链表来进行链接。

可以使用 py_innodb_page_info.py 工具来分析表空间。

使用 hexdump 工具来查看数据。

图： 5-14 todo;

注意：存储方式：首先页不是物理上连续的；通过双向链接；再者。页中的记录也是通过双向链表进行维护的。

聚集索引好处：对主键的排序查找；和范围查找查找速度非常快。

mysql > explain select * from Profile order by id limit 10;

mysql > explain select * from Profile where id > and id < 10000\G;

辅助索引：

叶子节点包含键值（索引列字段值）；还包含 bookmark（主键的值）

每张表中可以有多个辅助索引。

例如：通过辅助索引怎么找到一行数据？

例如：在一棵高度为3的辅助索引树中查找数据，首先需要对这颗辅助索引遍历3次找到指定主键，如果

指定的聚集索引树的高度同样为 3，那么还需要对聚集索引树进行3次查找。最终找到一个完整的数据行所在的页。算下来，一共需要6次逻辑IO得到最终的一个数据页。

例子分析图 5-16 todo;

B+ 树索引的分裂（拆分页）

InnoDB存储引擎的 Page Header 中有几个部分来保存插入的顺序信息。 PAGE_LAST_INSERT PAGE_DIRECTION PAGE_N_DIRECTION

增值插入时分裂点就是插入记录本身（如果要分裂的话）；其他插入情况暂时不深究。

索引创建和删除：

两种方式：

一：

ALTER  TABLE   tbl_name
ADD  {INDEX| KEY }   [index_name]   [index_type]  (index_col_name ,…)  [index_option] …
 
ALTER TABLE tbl_name
DROP PRIMARY KEY
| DROP  FOREIGN KEY  fk_symbol
| DROP  {INDEX| KEY} index_name

二：

CREATE  【UNIQUE  | FULLTEXT | SPATIAL ] INDEX index_name
[index_type]
ON  tbl_name  (index_col_name,…)   [index_option]   [algorithm_option | lock_option]  …
 
DROP INDEX index_name  ON  tbl_name  [algorithm_option | lock_option]  …
 
algorithm_option :
ALGORITHM [=]  {DEFAULT | INPLACE | COPY}
 
lock_option :
LOCK [=]  {DEFAULT |NONE  |SHARED | EXCLUSIVE}

查看索引：

SHOW INDEX FROM  tbl_name;

例子：用户可以设置整个列的数据进行索引，也可以只索引一个列的开头部分数据，如 b 为 varchar(8000) , 用户可以只索引前 100个字段，如：

ALTER TABLE t       ADD  KEY idx_b (b(100));

SHOW INDEX 结果每一列的含义。

Collation: 列以什么方式存储在索引中， B+ 树总是 A

Cardinality: 索引中唯一值的数目的估计值。；它不是实时更新的，是个大概的值。

优化器会根据 Cardinality 的值来选择是否使用这个索引。

ANALYZE TABLE 操作会跟新 Cardinality 的值。

对现有的数据表（有很多数据）进行索引的创建或删除，会造成什么影响，效率怎么样？以前是怎么做的，现在是怎么做的？

InnoDB 1.0.x 开始支持快速索引创建 Fast Index Creation 简称： FIC。针对的是辅助索引。

对于辅助索引的创建； InnoDB存储引擎会对创建索引的表加上一个 S 锁。在创建过程中不需要重新建表。

辅助索引的删除：更新内部视图，将辅助索引的空间标记为可读，同时删除内部视图上对该表的索引定义。

主键的创建和删除同样需要重建一张表。

在线数据定义 Online DDL

MySQL 5.6版本开始支持 Online DDL 在线数据定义操作；允许辅助索引创建的同时，还可以允许其他像 INSERT UPDATE DELETE 这类DML 操作，

这极大地提高了Mysql 数据库在生成环境中的可用性。

还支持的“在线”操作如：

辅助索引的创建与删除

改变自增长值

添加或删除外键约束

列的重命名。

CREATE  【UNIQUE  | FULLTEXT | SPATIAL ] INDEX index_name
[index_type]
ON  tbl_name  (index_col_name,…)   [index_option]   [algorithm_option | lock_option]  …
ALGORITHM  指定了 创建 或删除索引的算法  可以取值如： COPY  INPLACE  DEFAULT;    默认 采用 DEFAULT  方式。
LOCK      创建或删除索引  添加锁的情况 ，可以取值如：
NONE,   //不加锁，这种模式可以获得最大的并发度
SHARE,    // S 锁， 并发的读可以，遇到写的事务，写事务就要等待。
EXCLUSIVE,   //  X 锁， 对目标 表 加上一个X 锁。 读写事务都不能进行。
DEFAULT ,  //  1, 首先判断能不能 使用 NONE，  2， 能不能使用 SHARE  , 3 能不能使用  EXCLUSIVE  .

Online DDL的原理：在执行创建或删除操作的同时，将 INSERT ,UPDATE, DELETE, 这类DML操作日志写入到一个缓存中，等到完成索引创建后，再将重做应用到表上。这个缓存默认大小是 128M （由参数 innodb_online_alter_log_max_size 参数控制）。

在索引的创建过程中，SQL 优化器不会选择正在创建中的索引。

什么样的情况下，适合加索引？哪些字段适合加索引？

像性别，地区，类型字段，他们的取值范围很小，低选择性；所以没必要加索引

像姓名就可以加索引。

Cardinality/ n_rows_in_table 应尽可能接近1。如果非常小，那么用户需要考虑是否有必要加索引。

Cardinality 是怎么统计的？是怎么计算的？

统计时通过采样来完成的； Cardinality统计更新发生在 INSERT 和 UPDATE 。

策略： 1. 表中 1/16 的数据已发生过变化。

2. stat_modified_counter > 2 000 000 000 .

默认采样数量是 8

当执行SQL 语句：

ANALYZE  TABLE;
SHOW  TABLE STATUS;
SHOW  INDEX;

以及访问 information_schema 下的表 tables 和 statistics

时，会导致InnoDB 存储引擎去重复计算索引 Cardinality 值。

如果表中数据量很大，并且表中有多个辅助索引，执行上述操作可能会非常慢。

不同应用中B+ 树索引的应用？

OLTP 应用一般只从数据库中取得一小部分数据，一般 10条，这种建立 B+树索引有意义。

OLAP 应用，都需要访问大量数据，多是面向分析的查询。这个时候通常对时间字段进行索引。因为大多数统计需要根据时间维度来进行数据的筛选。

联合索引：

create table t(
a int,
b int,
primary key (a),
key idx_a_b (a,b)
)engine = innoDB

图 5-22 todo;

select  * from  t  where  a=xxx  and b=xxx   //可以使用到索引；
select  *  from t where  b=xxx;       //使用不到这棵索引；
select *  from  t  where a=xxx  order by b;      //可以使用到联合索引

联合索引的好处是：已经对第二个键值进行了排序处理。例如：

create table buy_log(
userid  int unsigned  not null,
buy_date date
)engine=InnoDB

alter  table buy_log add key(userid);
alter  table buy_log  add  key(userid,  buy_date);
 
select *  from  buy_log  where userid=2;
//分析 有两个索引 可以使用； 最终选择的是索引 userid;

select * from buy_log  where  userid=1  order by  buy_date  desc  limit 3;

//分析可以用使用 userid, (userid, buy_date) 两个索引；最终选择了联合索引 userid_2; 因为联合索引中buy_date已经排好了，根据联合索引取出数据，无须对buy_date做一次额外的排序操作。

对 a, b, c添加联合索引（a, b, c); 如下：

select … from  table   where a=xxx  order by  b;   //可以使用索引
select …  from table  where a=xxx  and  b=xxx  order by  c;   //可以使用索引。

覆盖索引： / 索引覆盖（covering index):

从辅助索引中可以得到查询的话；就不需要查询聚集索引中的记录了。使用覆盖索引的好处是辅助索引中不包含整行记录的所有信息），所以大小要远小于聚集索引，因此可以减少大量的IO操作。

若叶子节点存放的数据为 (primary key1, primary key2, …, key1, key2, ….). 下面语句都可以仅使用一次辅助联合索引来完成查询。

select key2  from  table   where   key1=xxx;
select  primary key2, key2  from  table  where  key1=xxx;
select  primary key1, key2  from  table  where  key1=xxx;
select primary key1,  primary key2, key2  from table  where key1=xxx;

对于某些统计问题也可以仅使用辅助索引。

如  select count(*)  from  buy_log;           //Extra  Using index 代表使 优化器  进行了覆盖索引操作。
select count(*)  from buy_log  where  buy_date>=’2011-01-01’  and  buy_date<’2011-02-01’;   //

//(a, b) 的这种联合索引，一般是b 作为查询条件是使用不到索引的，但是如果是统计操作则优化器会进行选择。

什么情况下使用不到索引？什么情况下优化器不使用索引。

多发生在范围查找， join链接等情况下。

select *  from  orderdetails  where  orderid > 10000  and  orderid  < 102000;

如果要求访问的数据量很小，则优化器还是会选择辅助索引；如果当访问的数据占整个表中数据蛮大一部分（20% 左右），优化器会选择聚集索引来来查找数据。因为顺序读取的速度远远快于离散读。

索引提示：index hint

以下两种情况可以用到 index hint

MySQL 数据库的优化器错误地选择了某个索引。很少见
某个SQL语句可以使用的索引很多，这时查询优化器执行计划时间的开销可能会大于 SQL语句本身。

语法：

USE index 只是告诉优化器可以选择索引，实际上优化器还是根据自己的判断进行操作。可以使用 FORCE index 来强制使用索引。

Multi-Range  Read 优化 /  MRR 优化；（InnoDB  MyISAM 都支持）
MySQL 5.6 开始支持 MRR 优化；  MRR 适用于  range, ref,  eq_ref 类型 的查询。

MRR 的工作原理：/方式：

将查询得到的辅助索引键值存放于一个缓存中( 默认 256k)，这时缓存中的数据是安装辅助索引的键值进行排序的。
将缓存中的键值根据 rowID(主键ID）进行排序。
根据RowID 的排序顺序来访问实际的数据文件。

之所以称为优化，就是因为避免了离散读取。

select * from  salaries  where salary > 10000  and  salary < 40000;

开不开差 10倍。

Multi-Range Read 还可以将某些范围查询 ，拆分为键值 对， 来进行批量查询。如：
select * from t  where  key_part1 >=1000  and  key_part1 < 2000   and  key_part2 = 10000;
//优化器 会将 查询条件 拆分为（1000， 1000）， （1001， 1000）， （1002， 1000）…, (1999, 1000);

总是开启MRR:

mysql >  set @@optimizer_switch=‘mrr=on, mrr_cost_based=off’;

//查看缓存的大小

mysql > select @@read_rnd_buffer_size\G;
 
Index Condition Pushdown  ICP  优化；

msyql5.6 开始支持：开启 ICP 后，会在取出索引的同时，判断是否可以进行where 条件的过滤。

ICP 优化支持 range ,ref, eq_ref, ref_or_null 类型的查询。

如：某表有联合索引

开启 ICP 后执行时间的对比表5-5 todo;

哈希表：

一般来说都将关键字转换为自然树，然后通过除法散列表。 h(k) = k mod m

例如： innodb_buffer_pool_size 的大小为 10M ，则共有 640个 16KB的页。对哈希表来说需要 640 X 2 = 1280个槽，但不是质数，应该是 1399；

在InnoDB 存储引擎的缓冲池中对于其中的页是怎么进行查找的呢？

关键字  K=  space_id<<20  +  space_id  + offset;

自适应哈希：

hash 索引只能用来搜索等值的查询。范围查找是不能使用哈希索引的

select * from table where index_col=‘xxx’;

全文索引【暂时不深入研究】

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：clickhouse基于MySQL创建表 clickhouse mysql 增量

下一篇：docker安装部署kettle kettle docker部署

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯