数据库-索引

索引模块

1. 索引

• 目的：快速查询数据
• 构成：主键，唯一键以及普通键等
• 索引数据结构：
  （1） 二叉查找树(每深入一层，进行依次I/O操作)
  （2）B-/B Tree（相比于二叉数每个节点能存储更多的索引）
  （3）B+ Tree（非叶子节点不携带数据，即指向被索引数据的指针，每个节点可以存储更多的索引）
  （4）Hash（速度最快）
  （5）BitMap（位图索引）

B+树更适合用来做存储索引：

1. B+树的磁盘读写代价更低（每个节点可以存储更多的索引）
2. B+树的查询效率更稳定（每次查询都必须深入到叶子）
3. B+树更有利于对数据库的扫描（范围查询，叶子节点按顺序连接）

哈希索引缺点：

1. 仅仅能满足“=”，“in”，不能使用范围查询
2. 无法被用来避免数据的排序操作
3. 不能利用部分索引键查询
4. 不能避免表扫描
5. 遇到大量hash值相等的情况后性能并不一定就会比B-Tree索引高

2. 密集索引和稀疏索引

密集索引

• 密集索引文件中的每个搜索码值都对应一个索引值，其叶子节点保存不仅仅是键值，还保存了位于同一行记录里的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只能有一个物理排列顺序，所以一个表只能创建一个密集索引。

稀疏索引

• 稀疏索引文件只为索引码的某些值建立索引项，其叶子节点仅保存了键位信息以及该行数据的地址或者主键。

MySQL的两种主流搜索引擎

• MyISAM：不管是主键索引，唯一键索引或者普通索引，其索引均属于稀疏索引

• Innodb：必须有且只有一个密集索引
  a) 若一个主键被定义，该主键则作为密集索引
  b) 若没有主键被定义，该表的第一个唯一非空索引则作为密集索引
  c) 若不满足以上条件，InnoDB内部会生成一个隐藏主键(密集索引)
  d) 非主键索引存储相关键位和其对应的主键值，包含两次查找

InnoDB

• 使用的是密集索引，将主键组织到一颗B+Tree中，而行数据就存在叶子节点上，因为InnoDB的主键索引和对应的数据是保存在同一个文件当中的，所以检索的时候在加载叶子节点的主键进入内存的同时，也加载了对应的数据
• 若对稀疏索引进行条件筛选，则需要经过两个步骤，第一步在稀疏索引的B+Tree中检索该键，对应到主键信息，第二步是根据找到的主键信息在B+Tree中再执行一遍B+Tree的索引操作，最终再到达叶子节点获取整行的数据

MyISAM

• 使用的均为稀疏索引，其主键索引和其他辅助键索引的两颗树看上去没什么不同，节点的结构完全一致，只是存储的内容不一样而已，主键索引B+Tree的节点存储了主键， 辅助键索引B+Tree的节点存储了辅助键，表数据存储在独立的地方，也就是索引和数据是分开存储的，这两类索引的B+Tree叶子节点都使用地址指向真正的表数据，对于表数据来说，这两类键没有任何的差别。

3. 如何定位并优化慢查询sql

a 根据慢日志定位慢查询sql

• show variables like ‘%quer%’：查询日志信息（show_query_log:慢查询日志 / show_query_log_file:慢日志记录文件 / long_query_time: 执行时间判断，超过即为慢查询）
• show status like ‘%slow_queries%’：查询慢查询的数量
• sset global show_query_log = on：无需重新连接
• set global long_query_time = 1：需重新连接数据库生效

b 使用explain等工具分析sql

• explain SELECT count(*) FROM employees.employees：分析查询语句
• type：找到数据的方式（system>const>…>index>all）,当type是index/all的时候，表示需要优化
• extra：出现以下2项意味着Mysql根本不能使用索引，效率会受到重大影响，应尽可能对此进行优化
  – Using filesort：表示mysql会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关的内容。可能在内存或者磁盘进行排序。mysql中无法利用索引完成的排序操作称为文件排序。
  – Using temporary：表示mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

c 修改sql或者尽量让sql走索引（可以用force index测试哪个索引最好）

4. 联合索引的最左匹配原则的成因

最左匹配原则：

• key ‘index_area_title’(‘area’,‘title’)：联合索引，如果仅有where title = ’ '将不会采取索引查找
• mysql会一直向右匹配直到遇到范围查询（>,<,between,like）就停止匹配，比如a = 3 and b = 4 and c > 5 and d=6 如果建立（a,b,c,d）顺序的索引，d是用不到索引的，如果建立（a,b,d,c）的索引则都可以用到，a,b,d的顺序可以任意调整。
• =和in可以乱序，比如a = 1 and b =2 and c = 3建立（a,b,c）索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

成因：

• 进行复合索引首先会对最左边的就行排序，即第一个字段绝对有序，后面的相对有序

5. 索引是建立的越多越好吗

• 数据量小的表不需要建立索引，建立会增加额外的索引开销
• 数据变更需要维护数据索引，因此更过的索引意味着更多的维护成本
• 更多的索引意味着也需要更多的空间