第四讲 索引 (上)
总结
- 索引的作用：提高数据查询效率
- 常见索引模型：哈希表、有序数组、搜索树
- 哈希表：键 - 值(key - value)。
- 哈希思路：把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置
- 哈希冲突的处理办法：链表
- 哈希表适用场景：只有等值查询的场景
- 有序数组：按顺序存储。查询用二分法就可以快速查询，时间复杂度是：O(log(N))
- 有序数组查询效率高，更新效率低
- 有序数组的适用场景：静态存储引擎。
- 二叉搜索树：每个节点的左儿子小于父节点，父节点又小于右儿子
- 二叉搜索树：查询时间复杂度O(log(N))，更新时间复杂度O(log(N))
- 数据库存储大多不适用二叉树，因为树高过高，会适用N叉树
- InnoDB中的索引模型：B+Tree
索引类型：主键索引、非主键索引
主键索引的叶子节点存的是整行的数据(聚簇索引)，非主键索引的叶子节点内容是主键的值(二级索引)- 主键索引和普通索引的区别：主键索引只要搜索ID这个B+Tree即可拿到数据。普通索引先搜索索引拿到主键值，再到主键索引树搜索一次(回表)
- 一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。
- 从性能和存储空间方面考量，自增主键往往是更合理的选择。
- 没有主键的表，innodb会给默认创建一个Rowid做主键
第五讲 索引(下)
覆盖索引
使用非主键索引查询时，不可避免的有回表操作，使用覆盖索引可取消回表操作，覆盖索引，即索引中的内容覆盖了查询的内容，不需要回表操作。
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 where k=2; -- 有回表操作 select ID from where k=2; -- 无回表操作- 由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
最左前缀原则
最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。
CREATE TABLE `tuser` (
`id` int(11) NOT NULL,
`id_card` varchar(32) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`ismale` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `id_card` (`id_card`),
KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB
select * from `tuser` where name like '张%'; -- 该语句可使用索引name_age
-- name_age 索引包含两个索引 (name, age) 和 (name)建立联合索引时，评估索引复用性。
字符串索引占用的空间要比整形索引大。
二级索引可以看作是和主键索引的联合索引。
索引下推
select * from tuser where name like '张%' and age=10 and ismale=1;
-- 以上查询语句可用到name索引在 MySQL 5.6 之前，只能先根据name索引找出符合条件的内容，然后到主键索引上找出数据行，再对比字段值。而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。可以在索引中比较age，减少回表次数。
第六讲 全局锁和表锁
全局锁
全局加读锁 FTWRL，整个数据库处于只读状态，一般用于不支持可重复读隔离级别的数据库整体备份，确保数据一致性。
全局锁主要用在逻辑备份过程中。对于全部是 InnoDB 引擎的库，建议选择使用–single-transaction 参数，对应用会更友好。
Flush tables with read lock;\
# 解锁
UNLOCK TABLES;
# 第二种让数据库整体只读的方式 不推荐使用
set global readonly=true；建议用FTWRL代替 readonly
一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，不建议你使用。
二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高
表级锁
lock table T1 read;
lock table T2 write;
# or
lock table T1 read, T2 write;
# 解锁
unlock tables;MDL（metadata lock)
DML： 数据内容的增删改查 select update
DDL： 数据表结构的修改 alter drop
DCL： 数据库的控制 revoke grant
MDL 不需要显式的使用，作用是保证读写的正确性，对一个表做增删改查，默认开启MDL读锁，修改一个表的结构，开启MDL写锁。MDL主要是解决DML与DDL的冲突。
MDL读锁之间不互斥，可以同时对表进行增删改查操作。
MDL读锁与写锁，写锁与写锁之间互斥。
information_schema 库的 innodb_trx 表可查询当前正在执行的长事务。
行锁
MyIsam不支持行锁，InnoDB支持
两阶段锁
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。
死锁及死锁检测
当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。
两种策略解决死锁问题
- innodb_lock_wait_timeout 设置超时时间 默认50s
- 开启死锁检测 innodb_deadlock_detect 设置为 on
一般使用策略2，策略1如果设置时间过短，可能会误伤到正常的锁等待，时间过长的话，业务无法接受。
死锁检测要耗费CPU资源，减少死锁的主要方向，就是控制访问相同资源的并发事务量，降低死锁的检测成本。