数据库索引, Explain浅度学习

数据库

1. 数据库索引

Mysql索引是帮助Mysql高效获取数据的排好序的数据结构

1.1 索引的数据结构

• 二叉树 单边增长的数据结构 , 比如自增id , 每次查询也相当于权标扫描(类似链表)

• 红黑树 数据量太大 , 数据层级太更深

• hash表 比如查询条件设计>, < 等运算 , hash表索引结果没有优势

• B - Tree 会存储索引行的所有字段值, 字段过多

  

  • 叶几点具有相同深度 , 叶节点的指针为空
  • 所有索引元素不重复
  • 节点中的数据索引从左到右递增排列

• B + Tree

  • 非叶子节点不存data , 只存储索引(冗余) , 可以存放更多的索引

  • 非叶子节点包含所有索引字段

  • 叶子节点用指针连接 , 提高区间访问性能

    

查询innodb索引存储大小
show global status like 'innodb_page_size';

1.2 InnoDB索引实现(聚集)

• 表数据文件本身就是以B+Tree组织的一个索引文件

• 聚集索引-叶子节点包含了完整的数据记录

• 为什么InnoDB表必须有主键 , 并且推荐使用整形自增主键

  • 如果表使用自增主键
    那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，主键的顺序按照数据记录的插入顺序排列，自动有序。当一页写满，就会自动开辟一个新的页

    如果使用非自增主键（如果身份证号或学号等）
    由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

• 为什么非主键索引结构叶子节点 , 存储的是主键值 , (一致性和节省空间)

  

右边图说明 : 在某个字段上创建一个普通索引 , 索引存储的data是该条记录的主键

1.2.1 为什么普通索引要要存储主键?

​ 为了解决主键索引和普通索引的一致性 , 维护了普通索引还要维护主键索引 , 会存在一个一致性的问题 , 就会有事务介入 , 有事务介入就会存在性能问题 . 并且存储主键索引的话还能节省空间 .

1.2.2 联合索引存储结构

​

联合索引补充

2索引优化原则

2.1 Explain工具介绍

• 使用explain关键字可以模拟优化器执行SQL语句 , 分析你的查询语句或是结构的性能瓶颈
• 在SELECT语句之前加上EXPLAIN关键字 , MySQL会在查询上设置一个标记 , 执行查询会返回执行计划信息 , 而不是执行这条SQl
• 注意 : 如果from含有子查询 , 仍会执行改子查询 , 将结构放入临时表中

一般来说 , 得保证查询达到range级别 , 最好达到ref级别

2.2 Explain各列说明

# 关闭MySQL5.7对衍生表的合并优化新特性
SET SESSION optimizer_switch=`derived_merge=off`;
# 还原配置
SET SESSION optimizer_switch=`derived_merge=`on`;
EXPLAIN SELECT (SELECT 1 from actor where id = 1) FROM (SELECT * FROM film WHERE id = 1)der

1. id列

   1. id列的编号是select的的序列号 , 有几个select就有几个id , 并且id的顺序是按照select的顺序增长的.
   2. id值越大 , 优先级越高 , id相同则从上往下执行 , id为NULL最后执行

2. select_type列 表示对应行是简单查询还是复杂查询

   1. simple : 简单查询 , 不包括子查询和连接查询(union)

   2. primary : 复杂查询中的最外层select

   3. subquery : 包含在select中的子查询(不在from子句中)

   4. derived : 包含在from子句中的子查询 , MySQL会将结果存在一个临时表中 , 也称为派生表

   5. union : 在union中的第二个和随后的select

      EXPLAIN SELECT 1 UNION ALL SELECT 1;
      

      

3. table列 标识查询哪张表

   1. 当from子句有子查询的时候 , table列是 , N表示当前查询依赖id=N的查询 , 于是先执行id=N的查询

4. 当有union时, UNION RESULT的table列的值为<union1, 2> 1和2表示参加union的select行id

5. type列

   1. 这一列表示关联类型或访问类型 , 即MySQL决定如何查找表中的行 , 查找数据记录的行数大概范围(大概多少行)

   2. 依次排列 , 最优到最差 , system > const > eq_ref > ref > range > index > ALL

      1. const , system : mysql能对查询的某部分进行优化 , 并将其转换成一个常量 (可以看show warnning结果) , 用作primaey key或者unique key的所有列与常数比较时 , 所有表最多有一行匹配 , 速度比较快 , system是const的特例 , 表里只有一条元素匹配时 , 为system

         EXPLAIN extended SELECT * FROM (SELECT * FROM film WHERE id = 1)stu;
         

         

      2. eq_ref : primary key活unique key 索引的所有部分别连接使用 , 最多只会返回一条符合条件的记录 , 这可能是在const之外最好的连接类型了 , 简单的select查询不会出现这种type

         

      3. ref : 相比eq_ref , 不适用唯一索引 , 而使用普通索引或唯一索引的部分前缀 , 索引要和某个值比较 , 可能会找到多个符合条件的行 ,

         1. 简单的select , name是普通索引(非唯一索引)

         

      4. range : 范围扫描通常出现在in, between , >, < , >=, <=等操作中 , 使用一个索引来检索给定范围行

         select * from table where id > 1;
         

      5. index扫描全表索引 , 这通常比ALL快一点,(所有的字段都有索引)

         select id from demo_user;
         

      6. ALL : 全表数据扫描 , 不是对索引进行全表扫描

         select * from demo_user;
         

   3. possible_keys

      1. 表示可能会用到的索引
      2. possible_keys有显示 , 此时key是NULL , 这种情况因为表中数据不多 , mysql认为索引对此查询帮助不大 , 选择了全表查询.
      3. 如果possible_keys是NULL , 则没有相关的索引 , 这种情况可以通过where子句来判断是否可以创造适当索引 , 提高查询性能

      EXPLAIN SELECT * FROM demo_user WHERE id > 1;
      

      

   4. key 用到的索引

   5. key_len

      1. 这一列显示了mysql在索引里使用的字节数 , 通过这个值可以算出具体使用索引的哪些列

         1. 例 : film_actor的联合索引 idx_film_actor_id和actor_id两个int列组成 , 并且每个int是4个字节 , 通过结果中的key_len=4可以判断出查询使用了第一列 : film_id列来执行索引查找

            

         2. key_len长度越长 , 说明效率越高 , 过滤次数越多

   6. ref : 显示了在key列记录的索引中 , 表查找值所用到的列或常量 , 常见的有const(常量) , 字段名

   7. rows : 改列是MySQL估计要读取并检测的行数 , 注意这个不是结果集里的行数

   8. Extra列 这一列是额外信息 ,

      1. Using index : 使用覆盖索引 ,

         什么是覆盖索引 :  指被查询的列在索引数中 .  被查询的列有索引 . 
         SELECT name FROM table WHERE id = 1, table里的name字段有索引, 用explain查看执行过程extra就是Using index , 如果查询列中有不是索引列 , 则explain结果extra是空的
         

      2. Using where : 使用where语句来处理结果 , 查询的列未被索引覆盖

         SELECT col1, col2, col3 FROM table wehre ocl1 = abc;  这里col1, col2, col3都没有添加索引, 都不在索引Tree上
         

      3. Using index condition : 查询的列 不完全被索引覆盖 , where条件是一个前导列的范围

         SELECT col1, col2, col3 FROM table wehre ocl1 = abc;  这里col1, col2, col3有一个, 或者两个字段添加了索引 , 在索引数上 , 此时explain的extra上就会是Using index condition
         

      4. Using temporary : mysql需要创建一张临时表来处理查询 , 出现这种情况一般要进行优化 , 首先要考虑进行索引优化

         username没有索引

         EXPLAIN SELECT  DISTINCT username  FROM demo_user;
         

      5. Using filesort : 将用外部排序而不是索引排序 , 数据较小时从内存中排序 , 否则需要在磁盘完成排序 , 这种情况一般也要考虑建索引来优化

         EXPLAIN SELECT  DISTINCT *  FROM demo_user ORDER BY username;
         username没有创建索引
         

      6. select tables optimized away : 使用某些聚合函数 , max, min , 来访问存在索引的某些字段. MySQL内部自己优化过

      7. impossible where

2.3 索引实践

CREATE TABLE `employee`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` INT(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`, `age`, `position`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=4  COMMENT '员工记录表'

2.3.1 最左前缀法则

EXPLAIN SELECT * FROM employee where age = 22 不会走索引 , 会全表扫描, 以为要联合索引的数据结构是有顺序的 , 此时的索引是分三层的 , name开始 , 其次, age, posion , 想要使用索引必须从age开始

2.3.2 不要在索引上做任何操作

(计算 , 函数 , 自动OR手动类型转换), 会导致索引失效 , 导致全表扫描

EXPLAIN SELECT * FROM employee WHERE LEFT(name,3)='LiLei';

2.3.3 存储引擎中不能使用索引中范围条件右边的列

两个查询都会走索引
第一个效率大于第二个
对于range查询，在一条组合索引里面，range字段后面的列是不生效的，不会使用索引
EXPLAIN SELECT * FROM employee WHERE name = 'LiLei' AND age=22 AND position = 'manager';
EXPLAIN SELECT * FROM employee WHERE name = 'LiLei' AND age>22 AND position = 'manager';

2.3.4 尽量使用覆盖索引

只访问索引的查询 , 索引列包含查询列, 减少select * 语句

EXPLAIN SELECT name, age, position FROM	 employee;   -- Using index
EXPLAIN SELECT * FROM	 employee; -- User index Condition

2.3.5 mysql尽量不要使用不等于

在使用不等于(!= <>)的时候无法使用索引, 导致全表扫描

EXPLAIN SELECT * FROM employee WHERE name != 'LiLei';   -- 不走索引

2.3.6 IS NULL , IS NOT NULL一般也不会走索引

EXPLAIN SELECT * FROM employee WHERE name is null;  -- impossible where, key为空 , type问为空

2.3.7 like以通配符开头

左模糊会导致索引失效, 尽量使用likeRight

2.3.7.1 解决like %字符串%不使用索引的方法

 使用覆盖索引 , 查询字段必须是建立索引的字段

2.3.8 字符串不用单引号, 导致索引失效

EXPLAIN SELECT * FROM employee WHERE name = 1000;  -- 只有possiable key 没有key, extra是Using where
EXPLAIN SELECT * FROM employee WHERE name = '1000';  -- 使用索引

2.3.9 少用OR或者IN

用or或者in时 , mysql可能会不走索引 , mysql内部优化器 , 会根据检索比例 , 表大小等多个因素 , 整体评估是否使用索引 ,

EXPLAIN SELECT * FROM employee WHERE name = 'LiLei' OR name = 'HanMeiMei';  
-- 只有possiable key 没有key, extra是Using where

2.3.10 范围查询优化

可以将范围拆成多个小范围

一个垃圾的例子
目标sql
EXPLAIN SELECT * FROM employee WHERE age > 1 and age < 2000;
拆成
EXPLAIN SELECT * FROM employee WHERE age > 1 and age < 1000;
EXPLAIN SELECT * FROM employee WHERE age > 1001 and age < 2000;

3 排序优化总结

1. MySQL支持两种但是排序 , filesort和index, Using index是只MySQL扫描索引本身完成排序, index效率高 , filesort效率低, 在磁盘或者缓存中完成排序
2. order by两种情况会Using index
   1. order by语句使用索引最左前列
   2. 使用where子句与order by子句条件列组合满足索引最左前列
3. 尽量在索引列上完成排序 , 遵循索引建立(索引创建的顺序)时最左前缀法则
4. 如果order by的条件不在索引列上 , 就会使用filesort
5. 能用覆盖索引尽量使用覆盖索引
6. group by与order by类似 , 其实质是先排序后分组 , 遵照索引创建顺序的左前缀法则 , 对于group by的优化如果不需要排序的可以加上order by null禁止排序, 注意 , where高于having, 能卸载where中的限定条件就不要去having限定了.

4 关联查询优化

t2一万条 , t1一百条数据
CREATE TABLE `t1` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`a` INT(11) DEFAULT NULL,
`b` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=INNODB AUTO_INCREMENT = 10001 COMMENT '实例表';


CREATE TABLE `t2` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`a` INT(11) DEFAULT NULL,
`b` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=INNODB AUTO_INCREMENT = 10001 COMMENT '实例表';

4.1 MySQL表常见两种关联算法

• Nested-Loop Join算法 NLJ

  • 嵌套循环连接算法

    • 一次一次循环的从第一张表(称为驱动表)中读取行 , 在这行数据中取到关联字段 , 根据关联字段在另一张表(被驱动表), 里去满足条件的行 , 然后取出两张的结果合计

      EXPLAIN SELECT * FROM t1 inner join t2 on t1.a = t2.a;
      

    • inner join不一定按照书写顺序决定谁是驱动表 , 谁是被驱动表 , 会选择数据量小的作为驱动表

    • 使用了NLJ算法 , 一般join语句中 , 如果执行计划extra中不包含Using join buffer则表示join使用NJL算法

• Block Nested-Loop Join算法

  • 基于块的嵌套循环连接算法
    • 把驱动表(小表)的数据读取到join_buffer中, 然后扫描被驱动表 , 被驱动表的每一行被扫描出来放到join_buffer中做数据对比 .
    • 总共扫描100 * 10000 = 100W. 100不变 , 每次一万条数据中取一条去扫描100

4.2 对于关联查询的SQL优化

4.2.1 关联字段加索引 : 尽量让查询用NLJ算法 (连接条件上创建索引)

4.2.2 小表驱动达标 , 书写的时候明确知道表数据大小 , 尽量写成小表在前 , 省去mysql优化器判断表大小时间

5. 重点

MySQL版本 5.7.17

EXPLAIN SELECT count(1) FROM employee;
EXPLAIN SELECT count(id) FROM employee;
EXPLAIN SELECT count(name) FROM employee;
EXPLAIN SELECT count(*) FROM employee;

假设name字段有索引, 没有索引也是Select tables optimized away

返回的都是Select tables optimized away

没优化之前 , 实际上select count(id)效率并不是那么高 , 会查所有索引,反而select(name)效率会高 , 就查索引树一层

**select count(*) 会优化走索引, 辅助索引 **

select count(1) 会优化走索引, 辅助索引

大概排序 count(1) > count(name) = count(*) > count(id)

6. MySQL锁和事务隔离别

6.1 锁定义

• 锁是计算机协调多个进程或多个线程并发访问某一资源的机制
• 在数据库中除了传统的计算机资源 , (如CPU , RAM, I/O等)的争用以外 , 数据也是一种供需要用户共享的资源 , 如何保证数据并发访问的一致性 , 有效性是所有的数据库必须要解决的问题 , 锁冲突也是影响数据库并发访问性能的一个重要因素

6.2 锁分类

• 从性能上分为乐观锁(用版本对比来实现)和悲观锁
• 从对数据库的操作类型分为 , 读锁和写锁. 都是悲观锁
  • 读锁 (共享锁) : 针对同一份数据 , 多个读操作可以同时进行 , 互不影响
  • 写锁(排它锁) : 当写操作没有完成时 , 它会阻断其他写锁和读锁
• 从对数据操作的粒度分 : 表锁 , 行锁

6.2.1 表锁

CREATE TABLE `mylock` (
`id`	INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL ,
PRIMARY KEY (`id`)
) ENGINE = MyISAM 

• 每次操作锁住整张表 , 开销小 , 加锁快 , 不会出现死锁 , 锁粒度大 , 发生锁冲突的概率最高 , 并发度最低

  • locak table mylock read
    • 当前session和其他session都可以读该表
    • 当前session中插入或者更新锁定的表都会报错 , 其他session插入或者更新则会等待
  • lock table mylock write;
    • 当前session对该表进行增删改都没问题 , 其他session进行所有操作被阻塞

  案例结论

  ​ MyISAM在执行查询语句(SELECT)前 , 会自动给涉及的所有表加读锁 , 在执行增删改操作前 , 会自动给所有涉及的表加写锁

  简而言之 : 读锁会阻塞写 , 写锁会堵塞读写

6.2.2 行锁

• 每次操作锁住一行数据, 开销大 , 加锁慢 , 会出现死锁 , 锁粒度喜爱哦 , 发生锁冲突概率低 , 并发度高
• InnoDB与MyISAM最大区别
  • 支持行锁
  • 支持事务

6.2.2.1 行锁支持事务

• 事务及四大特性
  • 原子性: 事务是一个原子单元操作 , 要么都成功, 要么都失败
  • 一致性: 在事务开始和完成时 , 数据必须保持一致状态. 这意味着所有的数据规则 , 都必须应用于事务的修改, 以保持数据的完整性, 事务结束时 , 所有的内部数据结构也都必须是正确的.
  • 隔离性: 并发访问时 , 事务和事务之间相互隔离 , 互不影响
  • 持久性: 对数据库的操作是永久的
• 并发事务处理带来的问题
  • 丢失更新: 当一个或者两个事务选择同一行 , 然后基于最初选定的值更新改行的时候 , 由于每个事物都不知道其他事务的存在 , 就会发生丢失更新问题 , 最后事务提交的更新 , 会覆盖前面所有事务的更新
  • 脏读 : 一个事务读到另一个事务未提交的数据
  • 不可重复读: 一个事务两次查询结果不一致 , 中途有另一个事务对数据进行了update
  • 幻读 : 一个事务两次查询结果不一致 , 中途另一个事务多表进行了insert