Part 3 —— 索引与慢查询优化、数据库三大范式

一、索引

索引.png

1、索引与慢查询优化

  1. 索引:简单的理解为可以帮助你加快数据查询速度的工具
  2.     也可以把索引比喻成书的目录
  4. 算法:解决事务的办法
  5.     入门级算法:二分法
  6.        二分法前提:数据集必须有序
  8. 数据结构
  9.     二叉树(只能分两个叉)
  10.     b树 
  11.     b+树(叶子节点添加了指针) 
  12.     b*树(枝节点也添加了指针)
  13.     # 对于精确查找(找某个数据),查找次数看深度,b，b+，b*查找次数没有区别
  14.     # 添加指针是为了加快范围查询的速度
  16. 将某个字段添加成索引就相当于依据该字段建立了一颗b+树从而加快查询速度
  17. 如果某个字段没有添加索引 那么依据该字段查询数据会非常的慢(一行行查找)

2、索引的分类

  1. 1.primary key
  2.     主键索引除了有加速查询的效果之外 还具有一定的约束条件
  3. 2.unique key
  4.     唯一键索引 除了有加速查询的效果之外 还具有一定的约束条件
  5. 3.index key
  6.     普通索引 只有加速查询的效果 没有额外约束
  7. 4.
  8.     # 注意外键不是索引 它仅仅是用来创建表与表之间关系的

3、如何操作索引

  1. 创建唯一索引需要提前排查是否有重复数据
  2.     select count(字段) from t1
  3.   select count(distinct(字段)) from t1
  4. 查看当前表内部索引值
  5.     show index from t1;
  6. 主键索引
  7.     alter table t1 add primary key pri_id(id);
  8. 唯一索引
  9.     alter table t1 add unique key uni_age(age)
  10. 普通索引
  11.     alter table t1 add index idx_name(name)
  12. 前缀索引(属于普通索引)
  13.     避免对大列建索引,如果有就使用前缀索引
  14.         eg:博客内容 百度搜索内容等
  15.     alter table t1 add index idx_name(name(4))
  16. 联合索引(属于普通索引)
  17.         相亲平台 搜索心仪对象的时候
  18.         # 最左匹配原则
  19.         gender money height beautiful 
  20.     alter table t1 add index idx_all(name,age,sex)
  21. 删除索引
  22.     alter table t1 drop index 索引名(idx_name、idx_all...)

4、explain句式

  1. 全表扫描
  2.     不走索引 一行行查找数据 效率极低 生产环境下尽量不要书写类似SQL
  3. 索引扫描
  4.     走索引 加快数据查询 建议书写该类型SQL
  6. explain就是帮助我们查看SQL语句属于那种扫描
  8. # 常见的索引扫描类型:
  9.     1）index
  10.     2）range
  11.     3）ref
  12.     4）eq_ref
  13.     5）const
  14.     6）system
  15.     7）null
  16. 从上到下，性能从最差到最好，我们认为至少要达到range级别
  19. # 不走索引情况(起码记忆四条及以上)
  20.     1.没有查询条件，或者查询条件没有建立索引
  21.     2.查询结果集是原表中的大部分数据(25％以上)
  22.     3.索引本身失效，统计数据不真实
  23.     4.查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)
  24.     5.隐式转换导致索引失效
  25.         eg:字段是字符类型 查询使用整型
  26.     6.<> ，not in 不走索引
  27.         单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit、or或in尽量改成union
  28.     7．like "%_" 百分号在最前面不走
  29.     8.单独引用联合索引里非第一位置的索引列
  31. """
  32. 索引的创建会加快数据的查询速度 但是一定程度会拖慢数据的插入和删除速度
  33. """

二、数据库三大范式

三、隔离级别

  1. 在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
  2. InnoDB支持所有隔离级别
  3.     set transaction isolation level 级别
  5. 1.read uncommitted(未提交读)
  6.     事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
  7. 2.read committed(提交读)
  8.     大多数数据库系统默认的隔离级别
  9.   一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
  10. 3.repeatable read(可重复读)        # MySQL默认隔离级别
  11.     能够解决"脏读"问题，但是无法解决"幻读"
  12.   所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
  13. 4.serializable(可串行读)
  14.     强制事务串行执行，很少使用该级别
  16. https://www.cnblogs.com/LionYie/p/15569484.html