数据库索引

1. 索引原理

   • 数据的准备

     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 ;慢

2. 数据库使用的时候有什么注意事项

   • 建表的角度
     • 合理安排表关系
     • 尽量把固定长度的字段放在前面
     • 尽量使用char代替varchar
     • 分表：
       • 水平分：
       • 垂直分：
   • 从搭建数据库的角度上来描述问题
     • 搭建集群
     • 读写分离
     • 分库
   • 操作数据的角度：
     • 尽量用where来约束数据的范围到一个比较小的程度.比如说分页的时候。
       • select * from table_name where 条件 group by 字段 having 聚合。
     • 尽量使用联表查询而不是子查询。
     • 删除数据或者修改数据的时候尽量使用主键作为条件。
     • 合理的创建和使用索引。(在上面有具体内容)

3. 索引合并

   • 当使用了两个索引去select的时候，临时把两个索合并成一个索引

4. 覆盖索引

   • 对id字段创建了索引
   • select count(id) from 表 where id = 100; 覆盖索引，在查找一条数据的时候，命中索引，不需要再回表
   • select name from 表 where id = 100; 非覆盖索引，相对覆盖索引慢些。

5. 执行计划：SQL语句执行效率的分析，可以看到有哪些索引，实际用到哪些索引，执行的type等级

   • explain
   • Id(创建了索引) name(创建了索引) email
   • select * from s1 where id = 10000000 and name = 'eva' and email ='eva100000@oldboy'
     • 有没有索引
     • 有几个
     • 用哪个索引效率比较高
   • 什么是MySQL的执行计划：
     • 在执行SQL语句之前，MySQL进行的一个优化SQL语句执行效率的分析(计划)，可以看到有哪些索引，实际用到哪些索引，执行的type等级

6. 慢查询优化的基本步骤

   • 首先SQL的角度优化：

     • 把每一句单独执行，找到效率低的表，优化这句SQL

• 了解业务场景，适当创建索引，帮助查询

  • 尽量用连表查询代替子查询

• 确认命中索引的情况

• 考虑修改表结构

  • 拆表

    • 把固定的字段往前调整

  • 使用执行计划，观察sql的type通过以上调整是否提高

7. 慢日志：

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