Mysql之案例分析（一）

可见性分析 CREATE TABLE `t` ( `id` int(11) NOT NULL, `k` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; insert into t(id, k) values(1,1),(2,2);

A：1

B：3

数据修改的诡异现象 begin; select * from t; +--------+----+ | id | c | +--------+----+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +--------+----+ update t set c=0 where id=c; select * from t; +--------+----+ | id | c | +--------+----+ | 1 | 1 | | 2 | 2 | | 3 | 3 | | 4 | 4 | +--------+----+

上文中update无法修改的问题，为什么会产生这种情况？

场景1：update之前，另一个事务B中执行update t set c=c+1

update是当前读，可以读取最新的数据，id不等于c，更新失败

select是快照读，事务B是处于高水位之后红色部分，对于select的事务不可见

场景2：第一次select前启动事务B，update前事务B执行update t set c=c+1，且提交

update是当前读，可以读取最新的数据，id不等于c，更新失败

select是快照读，事务B对于当前事务是活跃的，处于黄色部分，不可见

索引场景分析 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; select * from geek where c=N order by a limit 1; select * from geek where c=N order by b limit 1;

非主键索引的叶子节点上会挂着主键，因此：

索引c+主键索引，可以看做是c、a、b

索引ca+主键索引，可以看做是c、a、b，重叠部分合并

由上可以得出，索引c可以等价于ca，保留较小的索引，去除索引ca

重建索引 -- 非主键索引重建 alter table T drop index k; alter table T add index(k); -- 主键索引重建方式1 alter table T drop primary key; 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key alter table T add primary key(id); -- 主键索引重建方式2 alter table T engine=InnoDB;

索引重建：碎片整理可通过索引重建进行

主键索引：

InnoDB必须有一个主键索引，未主动声明时，Mysql会默认给创建一列6字节的整数列

自增只能定义在索引列上，因此直接删除自增列上索引异常：1075

主键索引重建方式1中删除并重建的方式，其实相当于创建了两次索引，建议采用方式2

大批量删除数据 -- 第一种，直接执行 delete from T limit 10000; -- 第二种，在一个连接中循环执行 20 次 delete from T limit 500; -- 第三种，在 20 个连接中同时执行 delete from T limit 500

第一种：长事务，索引时间较长，且可能导致主从延迟

第三种：人为造成锁冲突

IS NULL、IS NOT NULL是否走索引 mysql> show index from t; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | t | 0 | PRIMARY | 1 | id | A | 93536 | NULL | NULL | | BTREE | | | | t | 1 | a | 1 | a | A | 93536 | NULL | NULL | YES | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ mysql> explain select * from t where a is null; +----+-------------+-------+------+---------------+-----+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-----+---------+-------+------+-----------------------+ | 1 | SIMPLE | t | ref | a | a | 5 | const | 1 | Using index condition | +----+-------------+-------+------+---------------+-----+---------+-------+------+-----------------------+ 1 row in set mysql> explain select * from t where a is not null; +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | t | ALL | a | NULL | NULL | NULL | 93536 | Using where | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 1 row in set

is null使用了索引，is not null未使用索引。那么，是否可以得出结论：is null走索引，is not null不走索引呢？