MySQL数据库查看索引占用的空间大小,查看是否存在锁和事务，以及事务的隔离级别测试

MySql BTree和Hash索引的比较，为什么InnoDB不使用Hash索引

MySql BTree和Hash索引的比较，为什么InnoDB不使用Hash索引

-1 为啥要用B+Tree而不用btree?

btree所有的节点都存储数据，而b+tree只有叶子节点才存储数据
而内存每次加载数据的大小是有限的，而有数据的时候，加载的量就会很小，如果都是索引，就会加载更多的索引值

索引覆盖：MySQL官网，类似的说法出现在explain查询计划优化章节，即explain的输出结果Extra字段为Using index时，能够触发索引覆盖。 其实简单些：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

1. 主键索引当然不需要索引覆盖，因为本身能查询到所有的数据
2. 非主键索引分为几个情况：

	1. 比如name是索引，id是主键，
	则select id，name  from XXX where name ='a'使用到了索引覆盖
	2. 联合索引情况，id主键，name version 是联合索引 ，
	则select id，name，version  from XXX where name ='a' and version='B' 使用到了索引覆盖

回表是指：在非主键索引的情况下，先查询到了主键，再通过主键查询数据

索引下推ICP：官网介绍

0. B+Tree能存多少数据

mysql> show global status like 'Innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set

1. innodb的所有数据文件.idb大小都是16K即16384的整数倍
2. 我们的InnoDB页的大小默认是16k？为啥是16K？因为16K的话，高度为3的树就可以存储千万级别的数据，如下有说明
3. 假设一行数据的大小是1k，那么一个页可以存放16行这样的数据。
4. 我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节
5. 我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。
6. 那么可以算出一棵高度为2的B+树，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数单个叶子节点记录行数。能存放117016=18720条这样的数据记录。
7. 根据同样的原理我们可以算出一个高度为3的B+树可以存放：1170117016=21902400条这样的记录。

所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。
在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

1. 统计数据库索引占用的空间大小

如果想知道MySQL数据库中每个表占用的空间、表记录的行数的话，可以打开MySQL的 information_schema 数据库。在该库中有一个 TABLES 表，这个表主要字段分别是：

TABLE_SCHEMA : 数据库名
TABLE_NAME：表名
ENGINE：所使用的存储引擎
TABLES_ROWS：记录数
DATA_LENGTH：数据大小
INDEX_LENGTH：索引大小

其他字段请参考MySQL的手册，我们只需要了解这几个就足够了。

1. 首先查看某一实例下的所有占用磁盘空间
   （表数据+索引数据，得到的结果为B，这里做了数据处理转成M）：

select concat(round((sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024,2),'M')
 from information_schema.tables where table_schema='数据库名称';

3. 上面是查询所有的表计的累计量，下面是是查询单个表计的的SQL(按照实例名查询)：

select table_name,
DATA_LENGTH/1024/1024 as tablesData,
INDEX_LENGTH/1024/1024 as indexData 
from information_schema.tables
where table_schema='数据库名称'
ORDER BY  tablesData desc;

2.查看是否有表锁

1、查询正在使用哪个表
show OPEN TABLES where In_use > 0;

2、查询进程
show processlist
查询到相对应的进程===然后 kill id

补充：
查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 

查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

 
# 查询是否有表锁
SELECT * from information_schema.INNODB_TRX
kill trx_mysql_thread_id

从 information_schema.innodb_trx 表中查看当前未提交的事务

select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx;
字段意义：

trx_state: 事务状态，一般为RUNNING
trx_started: 事务执行的起始时间，若时间较长，则要分析该事务是否合理
trx_mysql_thread_id: MySQL的线程ID，用于kill
trx_query: 事务中的sql
一般只要kill掉这些线程，DDL操作就不会Waiting for table metadata lock。

2. 调整锁超时阈值
lock_wait_timeout 表示获取metadata lock的超时（单位秒），允许的值范围为1到31536000（1年）。 默认值为31536000 (1年)。官方解释：https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_lock_wait_timeout 。

修改等待时间：

set session lock_wait_timeout = 1800;
set global lock_wait_timeout = 1800;

3. MySQL8.0 查看默认事务的隔离级别，并测试

如下：MySQL默认是：可重复读

mysql> select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

3.1 更改隔离级别为：未提交读测试

两个命令行客户端分别为A，B；不断改变A的隔离级别，在B端修改数据。
但是不改变B的隔离级别，只改变A的,B还是默认的隔离级别：可重复读
A:

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
## 查看
mysql>  select @@global.transaction_isolation,@@transaction_isolation;
+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | READ-UNCOMMITTED        |
+--------------------------------+-------------------------+
1 row in set (0.00 sec)

0. 准一张表,测试事务

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `num` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=