3.mysql数据库
3.14 索引
3.14.1. 磁盘I/O和预读
1）磁盘I/O
磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分。
（1）寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；
（2）旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200转，也就是说1秒钟能转120转，转一圈需要8.33ms。
最长旋转延时是转了一圈才找到，为8.33ms。
最短旋转延时不用转直接找到，为0ms。
平均旋转延迟：(8.33+0)/2=4.17ms
（3）传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。
那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右。
一台500 -MIPS（Million Instructions Per Second）的机器每秒可以执行5亿条指令，9ms可以执行约450万条指令。为了优化9ms的延迟，引入了磁盘预读机制
2）磁盘预读
假设我们打开文件，循环从文件中数据，每次读取10个字节。
操作系统在执行读取命令时，不会每次只读十个字节。为了提高效率操作系统每次会把相邻的数据也都读取到内存缓冲区内，每次都读取固定大小的数据块。数据块的大小和操作系统有关，一般为4k或8k。
3.14.2. 索引的数据结构：树
1）树状数据结构
A是B、C的父节点，B、C是的A子节点；同理，B是D的父节点，D是B的子节点。
最多几次能够查到数据呢？查找数据的最大次数，由树高决定。
2）二叉树：最多只有两个子节点的树形结构
比当前节点数据小的在左侧子节点，比当前节点数据大的在右侧子节点
3）平衡树（balance tree）：b树
为了保持平衡树的平衡状态，添加数据后平衡书内的数据要进行调整。
平衡树可以降低树高，减小最大查找次数。
保证所有数据查找经历的I/O次数相同。
4）b+树
b+树在b树（平衡树）的基础上增加了2个新特性：
（1）分支节点只存储索引信息，在叶子节点存储数据，降低B+树的高度。
（2）在所有叶子结点之间加入了双向地址连接，查找范围时速度加快
在mysql数据库中，B+树的高度一般都在24层，这也就是说查找某一个键值的行记录时最多只需要2到4次IO。当前一般的机械硬盘每秒至少可以做100次I/O，24次的IO意味着查询时间只需要0.02~0.04秒。
3.14.3. 聚集索引与辅助索引
数据库中的B+树索引可以分为聚集索引和辅助索引（非聚集索引）。
聚集索引与辅助索引相同的是：内部都是B+树的形式，即高度是平衡的，叶子结点存放着数据。
聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息。
聚集索引的叶子结点存放的是整行信息；
辅助索引的叶子结点存放的是数据地址。
InnoDB搜索引擎采用的是聚集索引和辅助索引。
Myisam搜索引擎采用的是辅助索引（数据和索引分开存放）
1）聚集索引
InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。
聚集索引就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。
聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。
如果未定义主键，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。
聚集索引的优点：
（1）它对主键的排序查找和范围查找速度非常快。
（2）支持范围查询，即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可
2）辅助索引
除了聚集索引外其他索引都是辅助索引（Secondary Index，也称为非聚集索引），与聚集索引的区别是：辅助索引的叶子节点不包含行记录的全部数据。
叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签。该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。
举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问才能得到最终的一个数据页。
从辅助索引取得聚集索引关键字，并返回聚集索引查询数据的过程，叫回表查询。
3.14.4. mysql索引管理
1）索引的功能
索引的功能就是加速查找
mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能
2）MySQL常用的索引
（1）唯一索引：
mysql建表时，会自动给主键和唯一字段创建索引，包括联合主键和联合唯一
（2）普通索引：
需要手动添加：CREATE INDEX 索引名 ON 表名 (字段名);
3）创建/删除索引的语法
（1）建表时创建
CREATE TABLE 表名 (
字段名1 数据类型 [约束],
字段名2 数据类型 [约束],
INDEX KEY [索引名] (字段名));
（2）CREATE在已存在的表上创建索引
CREATE INDEX 索引名 ON 表名 (字段名1,字段名2...);
（3）ALTER TABLE在已存在的表上创建索引
（4）删除索引：DROP INDEX 索引名 ON 表名;
4）索引的效果
（1）创建索引前的运行时间
（2）创建索引后的运行时间
3.14.5. 正确使用索引
1）查询的字段应该是索引字段
2）应该对区别度较大的列创建索引，1/10以下的重复率比较适合创建索引。
例如：学号、身份证号、姓名适合创建索引；班级、性别不适合创建索引
3）范围越大查询速度越慢，范围越小查询速度越快，不等于（!=）是大范围搜索。
4）like ”a%”命中索引（比较快），like ”%a”无法命中索引（比较慢）
5）条件列参与计算，不命中索引
6）使用函数，不命中索引
select * from tb1 where reverse(email) = ‘egon‘;
7）and和or
对于连续多个and：mysql会按照联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)
对于连续多个or：mysql会按照条件的顺序，从左到右依次判断
多个条件组合，使用and连接时，其中一列有索引，都可以加快查找速度
多个条件组合，使用or连接时，所有列都有索引，才能加快查找速度
8）联合索引：最左前缀原则
（1）使用联合索引时，检索必须带着最左边字段（创建联合索引时的排序）做查询条件，才能命中索引
（2）从出现范围开始，索引失效
9）类型不一致，不命中索引
如果列是字符串类型，传入条件是数字，无法命中索引
select * from tb1 where email = 999;
10）排序条件为索引，则select字段必须也是索引字段，否则无法命中索引
select name from s1 order by email desc; #只有email索引时，无法命中
当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢
select email from s1 order by email desc; #只有email索引时，可以命中
特别的：如果对主键排序，则还是速度很快：
select * from s1 order by id desc; # id 是主键
3.14.6. 其它注意事项
1）避免使用select *
2）可以使用count(*)
3）创建表时尽量使用 char 代替 varchar
4）表的字段顺序固定长度的字段优先
5）组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
6）尽量使用短索引
7）使用连表查询（JOIN）来代替子查询(Sub-Queries)
8）连表时注意条件类型需一致
9）索引散列值（重复少）不适合建索引，例：性别不适合
10）不要创建不必要的索引，及时删除不用的索引。索引会降低写入速度
3.14.7. 覆盖索引
InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。
使用覆盖索引的一个好处是：辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作
查询过程，不再需要回表查询：
select id from 表 where id >10000; # id有索引
索引合并：分别创建的2个索引在某次查询时临时合并成1条
3.14.8. 查询优化神器-explain
具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快。所以优化语句基本上都是在优化rows。
执行计划：explain select 语句；能够查看sql语句也没有按照预期执行，可以查看索引的使用情况和type等级
3.14.9. 慢查询优化的基本步骤
1）先运行看看是否真的很慢，注意设置SQL_NO_CACHE
2）where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
3）explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
4）order by limit 形式的sql语句让排序的表优先查
5）了解业务方使用场景，增加索引
6）加索引时参照建索引的几大原则
7）观察结果，不符合预期继续从0分析
3.14.10. 慢日志管理
1）在mysql的配置中开启并设置
2）在超过设定时间之后，这条sql语句会被记录下来
3）对被记录的sql语句进行优化