简单记录一下mysql的索引学习

索引的各种不恰当用法

https://www.bilibili.com/video/BV12b411K7Zu 得空专门走一遍

频繁删改字段不适合做索引，易索引失效，且更新慢，删不会慢

大数据量的表，要花时间研究最优秀的索引

in查询不是问题，问题是要加索引且得有效（区分度低效果不大）

复合索引优于单值索引，名字不可做索引（重名的人太多了）

唯一索引：索引列的值必须唯一，但可以 null

复合索引：一个索引包含多个列

primary 最外层查询，优先级最低

explain：id号大的先执行， id相同从上至下执行；有相同和不同的，大的id先执行，相同的id自上而下

derived：衍生

覆盖索引：创建索引时的列和查询的列对应一致，这个时候mysql不用全表扫描，可以直接从索引查

filesort：文件内排序，联合索引顺序应与查询条件和排序字段顺序一致

temporary：新建内部临时表，比filesort更严重

group by：使用时要加索引且索引字段的顺序要和创建时的相同

show index from tablet_name;

范围（> < ）会导致索引（及后面的索引）失效

drop index idx_xxx on table_name;

create index idx_xxx on table_name(col_1,col_2);

有范围查询放到最后，等值在前。

左连接索引加右表，右连接索引加左表。

小表驱动大表。

for(i<=100;i++){
	for(j<=10000;j++){
		....
	}
}

最佳左前缀法则：

​ 中间兄弟不能断。

!= 、<>、is null、is not null、%value导致索引失效，value%索引类型为range

type 扫描方式由快到慢

system > const > eq_ref > ref > range > index > ALL

解决覆盖索引列太多的情况

子查询使用等值匹配获取主键，通过主键获取其他列

mysqldumpslow --help

生成数据的脚本：

CREATE TABLE dept (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
deptno mediumint(8) unsigned NOT NULL DEFAULT '0',
dname varchar(20) NOT NULL DEFAULT '',
loc varchar(13) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8

CREATE TABLE emp (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
empno mediumint(8) unsigned NOT NULL DEFAULT '0',
ename varchar(20) NOT NULL DEFAULT '',
job varchar(9) NOT NULL DEFAULT '',
mgr mediumint(8) unsigned NOT NULL DEFAULT '0',
hiredate date NOT NULL,
sal decimal(7,2) NOT NULL,
comm decimal(7,2) NOT NULL,
deptno mediumint(8) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8

DELIMITER $$

CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i<n DO
set return_str=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
set i = i + 1;
end while;
return return_str;
end $$

create function rand_num()
returns int(5)
begin
declare i int default 0;
set i = FLOOR(100+RAND()*10);
return i;
end $$

CREATE PROCEDURE insert_emp10000(IN START INT(10),IN max_num INT(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into emp(empno, ename, job, mgr, hiredate,sal,comm,deptno) VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),FLOOR(1+RAND()20000),FLOOR(1+RAND()1000),rand_num());
until i=max_num
end repeat;
commit;
end $$

CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
INSERT INTO dept (deptno ,dname,loc ) VALUES (START +i ,rand_string(10),rand_string(8));
until i = max_num
END REPEAT;
commit;
end $$

# 删除

# DELIMITER ;

# drop PROCEDURE insert_dept;

Call insert_emp10000(0,1000000);

​

show procedure status where db='数据库名';.

show status like 'table%';

记录mysql内部表级锁定的情况

索引知识

jktime

1.哈希表适用等值查询

2.B+树的一层是页还是块？逻辑块

3.哈希、多叉树、跳表、LSM

4.主键索引（左，聚簇索引，innodb中）的叶子节点存的是整行（比如100这行，R1）数据，非主键索引（右，二级索引，innodb中）的叶子节点内容是主键ID(1、2、3、5、6是列的值)。

dcda101051f28502bd5c4402b292e38d

mysql> select * from T;
+-----+---+------+
| id | k | name |
+-----+---+------+
| 100 | 1 | NULL |
| 200 | 2 | NULL |
| 300 | 3 | NULL |
| 500 | 5 | NULL |
| 600 | 6 | NULL |
+-----+---+------+

根据上面的索引结构说明，我们来讨论一个问题：

​ 基于主键索引和普通索引的查询有什么区别？

主键索引不需要回表，普通索引需要回表（所有的普通索引都需要吗？普通索引指什么索引？）。

所谓的回表就是在普通索引查完后，又拿着查询的结果去主键索引查，因为普通索引是对比的value得到ID，然后主键索引对比的是ID，通过ID取到一整行的数据。

页

5.页分裂

​ 某个数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。

​ 除了有性能影响，还有利用率也会降低。

​ 有分裂就有合并。

​ 基于索引维护过程的案例：

​ 什么是自增主键？

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。

​ 每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

​ 假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？

​ 由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。

提问整理

6.请问没有主键的表，有一个普通索引。怎么回表？

​ 作者回复: 没有主键的表，innodb会给默认创建一个Rowid做主键

​ 索引只能定位到page，page内部怎么去定位行数据？

​ 作者回复: 内部有个有序数组，二分法

7.回表的好处？

​ 使用id值，相当于对主键索引与普通索引进行了解耦，主键索引页的变更不会影响到其他的普通索引。

8.delete、drop、truncate

​ Delete from table_name;清空表且不释放空间。

​ Drop table_name;会删除表的行以及表的定义，因此该表与其他表的所有关系将不再有效。

​ Truncate table_name;表结构保持不变，删除所有从表中的行并释放容纳空间

8.如何避免回表？

​ 覆盖索引

索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

9.联合索引

​ 在建立联合索引的时候，如何安排索引内的字段顺序？

如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

另外还需要考虑空间。比如为较小的字段单独创建索引。

**索引下推优化**

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录（有多个字段就对多个字段做筛选），减少回表次数。

10.上节课的答案

​ 对于上面例子中的 InnoDB 表 T，如果你要重建索引 k，你的两个 SQL 语句可以这么写：alter table T drop index k;alter table T add index(k);如果你要重建主键索引，也可以这么写：alter table T drop primary key;alter table T add primary key(id);

​ 我的问题是，对于上面这两个重建索引的作法，说出你的理解。如果有不合适的，为什么，更好的方法是什么？

重建索引 k 的做法是合理的，可以达到节省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建（重建又咋了？）。所以连着执行这两个语句的话，第一个语句就白做了（白做了是啥意思？意思是原本的关联还在吧，所以导致空间并没有节省？）。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB。

重建表的流程
1.创建一个临时表,扫描主键索引得到行数据,根据数据页生成B+树,存储到临时文件中(此时页中数据变的紧凑,不会存在空洞)
2.新数据写入原表的同时记录到redo_log中(这样保证拷贝过程数据不丢失)
3.临时文件生成完之后,在将redo_log操作到临时文件中,这样两者的数据则一致了
4.最后临时文件替换原表的数据文件

整个过程是online,获取MDL写锁->降级为MDL读锁->DDL->升级为MDL写锁->释放MDL锁

  • “我来也”的血泪史

    老师的每一篇都会讲到平常工作用遇到的事情.
    今天这个 alter table T engine=InnoDB 让我想到了我们线上的一个表, 记录日志用的, 会定期删除过早之前的数据. 最后这个表实际内容的大小才10G, 而他的索引却有30G. 在阿里云控制面板上看,就是占了40G空间. 这可花的是真金白银啊.
    后来了解到是 InnoDB 这种引擎导致的,虽然删除了表的部分记录,但是它的索引还在, 并未释放.
    只能是重新建表才能重建索引.
    如果当时看到了这个专栏,把这个语句拿来用,就可以省下不少钱了.

  • melon的理解

    关于联合索引我的理解是这样的：比如一个联合索引(a,b,c)，其实质是按a,b,c的顺序拼接成了一个二进制字节数组，索引记录是按该字节数组逐字节比较排序的，所以其是先按a排序，再按b排序，再按c排序的，至于其为什么是按最左前缀匹配的也就显而易见了

  • 覆盖索引必须要覆盖所有的查询条件中的列吗，经过试验发现，覆盖索引不能只覆盖要查询的列，同时必须将WHERE后面的查询条件的列都覆盖，能解释一下这个原因吗？

    作者回复: 你的发现是对的，
    因为覆盖索引的目的就是“不回表”，
    所以只有索引包含了where条件部分和select返回部分的所有字段，才能实现这个目的

锁🔒

​ 根据加锁范围可分：全局锁、表级锁、行锁。

全局锁

  • Flush tables with read lock (FTWRL)

  • 当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

    官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

​ single-transaction 方法只适用于所有的表使用事务引擎的库。

**既然要全库只读，为什么不使用 set global readonly=true 的方式呢?**

  • 一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更广，我不建议你使用。

  • 二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持* readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁

​ 分为表锁和元数据锁（meta data lock，MDL)。

​ 表锁的语法是 lock tables … read/write，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

​ 在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁

特点：

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

​ 给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。

​ 如何安全地给小表加字段（防止读写互斥）？

  • 首先要解决长事务，事务不提交，就会一直占着 MDL 锁
    • information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
  • 热点事务表，kill不管用咋办？
    • 在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程（AliSQL）。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...

MDL 会直到事务提交才释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

行锁

​ MySQL 的行锁是在引擎层由各个引擎自己实现的。

​ InnoDB 是支持行锁的，这是 MyISAM 被 InnoDB 替代的重要原因之一。

​ 同一张表上任何时刻只能有一个更新在执行（感觉和上一章提到的读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。冲突）评论群也有人发现了这个问题，以下是作者答复：

MDL锁和表锁是两个不同的结构。

比如：
你要在myisam 表上更新一行，那么会加MDL读锁和表的写锁；
然后同时另外一个线程要更新这个表上另外一行，也要加MDL读锁和表写锁。

第二个线程的MDL读锁是能成功加上的，但是被表写锁堵住了。从语句现象上看，就是第二个线程要等第一个线程执行完成。

两阶段锁协议

​ 在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。

​ 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

​ 可以把锁按照并发度高低先后执行，并发度高的放在最后。

死锁和死锁检测

​ 当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

​ 当出现死锁以后，有两种策略：

  • 直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置，default 50s。

  • 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on（default），表示开启这个逻辑。

    正常情况下我们还是要采用第二种策略，即：主动死锁检测

怎么解决由这种热点行更新导致的性能问题呢？

​ 一种头痛医头的方法，就是如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。

​ 有一定的风险。

​ 另一个思路是控制并发度。

​ 实现方式：中间件或改源码。

​ 实现思路：基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。

​ 如果团队里暂时没有数据库方面的专家，不能实现这样的方案，能不能从设计上优化这个问题呢？

​ 考虑通过将一行改成逻辑上的多行来减少锁冲突。（不得不说思路清奇。。）

​ 比如一行数据存账户金额，把这行数据拆分成100行，金额等分。这样要修改金额时随机一条记录增减。

​ 可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。

​ 这类方案需要根据业务逻辑做详细设计，比如退票，即当一部分行记录变成 0 的时候。

问答

1.关于死锁检测innodb_deadlock_detect是每条事务执行前都会进行检测吗？如果是这样，即使简单的更新单个表的语句，当每秒的并发量达到上千的话，岂不是也会消耗大量资源用于死锁检测吗？

如果他要加锁访问的行上有锁，他才要检测。

这里面我担心你有两个误解，说明下：

  1. 一致性读不会加锁，就不需要做死锁检测；

  2. 并不是每次死锁检测都都要扫所有事务。比如某个时刻，事务等待状态是这样的：

    B在等A，
    D在等C，
    现在来了一个E，发现E需要等D，那么E就判断跟D、C是否会形成死锁，这个检测不用管B和A

posted @ 2021-02-01 22:05  夜旦  阅读(391)  评论(0)  编辑  收藏  举报