索引模块
- 索引
- 目的:快速查询数据
- 构成:主键,唯一键以及普通键等
- 索引数据结构:
(1) 二叉查找树(每深入一层,进行依次I/O操作)
(2)B-/B Tree(相比于二叉数每个节点能存储更多的索引)
(3)B+ Tree(非叶子节点不携带数据,即指向被索引数据的指针,每个节点可以存储更多的索引)
(4)Hash(速度最快)
(5)BitMap(位图索引)
B+树更适合用来做存储索引:
- B+树的磁盘读写代价更低(每个节点可以存储更多的索引)
- B+树的查询效率更稳定(每次查询都必须深入到叶子)
- B+树更有利于对数据库的扫描(范围查询,叶子节点按顺序连接)
哈希索引缺点:
- 仅仅能满足“=”,“in”,不能使用范围查询
- 无法被用来避免数据的排序操作
- 不能利用部分索引键查询
- 不能避免表扫描
- 遇到大量hash值相等的情况后性能并不一定就会比B-Tree索引高
- 密集索引和稀疏索引
密集索引
- 密集索引文件中的每个搜索码值都对应一个索引值,其叶子节点保存不仅仅是键值,还保存了位于同一行记录里的其他列的信息,由于密集索引决定了表的物理排列顺序,一个表只能有一个物理排列顺序,所以一个表只能创建一个密集索引。
稀疏索引
- 稀疏索引文件只为索引码的某些值建立索引项,其叶子节点仅保存了键位信息以及该行数据的地址或者主键。
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叶子节点都使用地址指向真正的表数据,对于表数据来说,这两类键没有任何的差别。
- 如何定位并优化慢查询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测试哪个索引最好)
- 联合索引的最左匹配原则的成因
最左匹配原则:
- 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的查询优化器会帮你优化成索引可以识别的形式。
成因:
- 进行复合索引首先会对最左边的就行排序,即第一个字段绝对有序,后面的相对有序
- 索引是建立的越多越好吗
- 数据量小的表不需要建立索引,建立会增加额外的索引开销
- 数据变更需要维护数据索引,因此更过的索引意味着更多的维护成本
- 更多的索引意味着也需要更多的空间