数据库索引

索引原理
- 数据的准备
```
1. 
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

2.
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into s1 values(i,'eva','female',concat('eva',i,'@oldboy'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

3.
call auto_insert1();
```
- 读一次硬盘的时间开销
  - 磁盘的预读性(Linux 中 block 4096个字节)
- 新的数据结构：树
  - 二叉树
  - 平衡树(balance tree—>btree)
    - 平衡树不一定是二叉树
    - b+树
      - 平衡树，能够让查找某一个值经历的查找速度尽量平衡。
      - 分支节点不存储数据——让树的高度尽量矮，让查找数据的效率尽量稳定。
      - 在所有的叶子节点之间加入了双向的地址链接 --查找范围非常的快。
- mysql中存储数据的两种方式
  - 聚集索引（把数据存在叶子节点），只有innodb拥有聚集索引，且只有一个聚集索引：主键。
  - 辅助索引，非聚集索引，非聚簇索引（不把数据存在叶子节点）
  - 两种索引的差别：
  - innodb中，主键默认会创建一个聚集索引。
- 索引
  - 创建
    - 创建主键 primary key 聚集索引 + 非空 + 唯一
    - 创建唯一约束 unique 辅助索引 + 唯一
    - 添加普通索引
      - create index 索引名 on 表(字段名);
  - 删除
    - drop index 索引名 on 表名；
  - 知道使用了它速度快
- 正确使用索引
  - 给经常作为查询条件的字段创建索引
  - 创建索引时尽量使用区分度大，短的字段：重复率小于1/10比较适合创建索引
  - 查询时尽量约束到比较小的范围：范围查询速度快
    - Like 'a%' 快，like '%a' 慢
  - 条件列不要参与计算，不要用函数
  - and 和 or
    - and 的工作原理
      - 多个条件的组合，如果用and连接，其中有一列含有索引，都可也加快查找速度
    - or的工作原理
      - 多个条件的组合，如果用or连接，所有列含有索引，才可加快查找速度
  - 联合索引：最左前缀原则:
    - 必须带着最左边的列做条件，从出现范围开始整条索引失效。
    - create index mix_ind on 表(Id,name,email)
    - create index ind_mix on s1(id,name,email);
    - Select * from s1 where id = 180000 and name ='eva' and email = 'eva1800000@oldboy';
  - 条件中写出来的数据类型必须和定义的数据类型一致
    - select * from 表 where name = 666 # 不一致
  - select 的字段应该包含order by的字段
    - Select age from 表 order by age; 快
    - select name from 表 order by age ;慢
数据库使用的时候有什么注意事项
- 建表的角度
  - 合理安排表关系
  - 尽量把固定长度的字段放在前面
  - 尽量使用char代替varchar
  - 分表：
    - 水平分：
    - 垂直分：
- 从搭建数据库的角度上来描述问题
  - 搭建集群
  - 读写分离
  - 分库
- 操作数据的角度：
  - 尽量用where来约束数据的范围到一个比较小的程度.比如说分页的时候。
    - select * from table_name where 条件 group by 字段 having 聚合。
  - 尽量使用联表查询而不是子查询。
  - 删除数据或者修改数据的时候尽量使用主键作为条件。
  - 合理的创建和使用索引。(在上面有具体内容)
索引合并
- 当使用了两个索引去select的时候，临时把两个索合并成一个索引
覆盖索引
- 对id字段创建了索引
- select count(id) from 表 where id = 100; 覆盖索引，在查找一条数据的时候，命中索引，不需要再回表
- select name from 表 where id = 100; 非覆盖索引，相对覆盖索引慢些。
执行计划：SQL语句执行效率的分析，可以看到有哪些索引，实际用到哪些索引，执行的type等级
- explain
- Id(创建了索引) name(创建了索引) email
- select * from s1 where id = 10000000 and name = 'eva' and email ='eva100000@oldboy'
  - 有没有索引
  - 有几个
  - 用哪个索引效率比较高
- 什么是MySQL的执行计划：
  - 在执行SQL语句之前，MySQL进行的一个优化SQL语句执行效率的分析(计划)，可以看到有哪些索引，实际用到哪些索引，执行的type等级
慢查询优化的基本步骤
- 首先SQL的角度优化：
  - 把每一句单独执行，找到效率低的表，优化这句SQL

了解业务场景，适当创建索引，帮助查询
- 尽量用连表查询代替子查询
确认命中索引的情况
考虑修改表结构
- 拆表
  - 把固定的字段往前调整
- 使用执行计划，观察sql的type通过以上调整是否提高

慢日志：
- 在mysql的配置中开启设置一下
- 在超过设定时间之后，这条SQL总是会被记录下来
- 这个时候我们可以对这些被记录的SQL进行定期优化

posted @ 2020-08-31 22:20 彻底疯狂+100 阅读(108) 评论(0) 收藏举报

刷新页面返回顶部

学无止境

数据库索引

公告