检查索引关键字:
-
explain
-
explain select * from emp where asl > 1000\G
官网文档:
-
https://dev.mysql.com/doc/refman/5.7/en/
-
id:选择标识符
-
select_type:表示查询的类型。
-
table:输出结果集的表
-
partitions:匹配的分区
-
type:表示表的连接类型
-
possible_keys:表示查询时,可能使用的索引
-
key:表示实际使用的索引
- PRI 主键约束UNI 唯一约束MUL 可以重复
-
key_len:索引字段的长度
-
ref:列与索引的比较
-
rows:扫描出的行数(估算的行数)
-
filtered:按表条件过滤的行百分比
-
Extra:执行情况的描述和说明
- Using index,Using index condition,Using where
- 其中Using index 表示使用了覆盖索引
索引是什么?
-
索引是帮助mysql高效的获取数据
-
索引存储在文件系统中
-
索引的文件存储形式和存储引擎有关
-
索引文件的结构:
- hash
- 二叉树
- B树
- B+树
-
索引的目的:
-
提高访问效率
-
加快sql语句的查询
-
索引的优势和劣势
-
优势
-
类似于书籍的目录索引,提高数据检索的效率,降低数据库的io成本
-
通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗
-
-
劣势
- 实际上索引也是一张表,该表中保存了主键与索引字段,并指向实体类的记录,所以索引列也是要占用空间的
- 虽然索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行INSERT,UPDATE,DELETE。因为更新表时,MYSQL不仅要保存数据,还需保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息
索引的分类:
功能划分:
-
主键索引:
- 创建了主键就自动就是主键索引
-
唯一索引:
- 设置了唯一约束,就自动是唯一索引
-
普通索引:
- 辅助索引
- 二级索引
-
全文索引:
- 文档管理系统里面放得都是文章,把文章放到mysql数据库里面了,想检索一下所有文章里面包含java这个关键字的文章怎么做?
- 主要用于全文检索
- MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引;
- MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;
- 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引
- 文档管理系统里面放得都是文章,把文章放到mysql数据库里面了,想检索一下所有文章里面包含java这个关键字的文章怎么做?
列数划分:
-
组合索引:
- 一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则,即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。
-
单列索引:
- 一个索引只包含一个列,一个表可以有多个单例索引。
物理分类:
索引的创建:
- 索引名称 index_name 是可以省略的,省略后,索引的名称和索引列名相同。
-- 创建普通索引
CREATE INDEX index_name ON table_name(col_name);
-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
-- 创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
-- 创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);
-- 创建联合全文索引列
create fulltext index keyname on xxtable(colume1,colume2);
alter table xxtable add fulltext index keyname (colume1,colume2);
-- 建表的时候
FULLTEXT KEY keyname(colume1,colume2)
- 删除索引
-- 直接删除索引
DROP INDEX index_name ON table_name;
-- 修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;
- 其他命令
-- 查看表结构
desc table_name;
-- 查看生成表的SQL
show create table table_name;
-- 查看索引信息(包括索引结构等)
show index from table_name;
-- 查看SQL执行时间(精确到小数点后8位)
set profiling = 1;
SQL...
show profiles;
如何设计一套性能优良的索引系统?
-
mysql的数据在磁盘
-
索引也在磁盘
- (加载的过程)当你启动mysql服务之后,会优先把一部分数据加载到内存
- 所以看起来好像从内存中取得一样,实际上是要持久化磁盘的
- 为什么持久化磁盘,因为服务器或电脑突然断电了,内存数据就没了难道要从新创建索引吗?效率非常之地的,所以需要持久化到磁盘
- 加载:就涉及到了IO的问题,读写问题(固态硬盘比机械硬盘效率高)
- IO可以是硬件问题,把机械换成固态(成本高)
- 减少IO次数(一次与十次),减少IO量(一兆或十兆)时间肯定是不一样的
- 加载:就涉及到了IO的问题,读写问题(固态硬盘比机械硬盘效率高)
磁盘预读:
- 局部性原理:
- 发现程序和数据的访问都有聚集成群的倾向,在一个时间段内,仅使用其中一小部分(称空间局部性),或者最近访问过的程序代码和数据,很快又被访问的可能性很大(称为时间局部性)
- 如果你想只读一个a字母,那么是把a的这一块数据都取了出来,取数据的大小是根据操作系统相关的,一般是4k,8k,就是页的整数倍(page)
- 主存和磁盘以页为单位交换数据
现在我有个256兆内存的机器,我有1T的数据文件,如何能利用256兆的空间把这1T的数据排序?
您可以使用外部排序的方法,将原始文件划分为多个小文件,每个小文件大小不超过256兆。然后,使用快速排序等算法对每个小文件进行排序,最后再使用归并排序将这些已排序的小文件合并成一个大文件,即可完成1T的数据文件的排序。
数据结构模型网站:
- https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
哈希表:
- 不支持范围查询
- 因为是无序的,必须挨个匹配查询
- 哈希碰撞,哈希冲突(扰动函数)
- hash索引仅能满足(=)(<>)和in查询
- 对于等值查询来说hash效率高,索引列的重复值如果很多,效率就会降低,这是因为遇到hash冲突时,需要遍历桶中的行指针来进行比较,找到查询的关键字,非常耗时,所以,hash索引通常不会用到重复值多的列上,比如列为性别,年龄的情况
- hash索引的数据存储是没有顺序的,在order by的情况下,使用hash索引还需要对数据重新排序
- 对于联合索引的情况,hash值是将联合索引建合并后一起来计算的,无法对单独的一个键或者几个索引键进行查询
- 为什么Memory支持hash索引呢?
- 因为它快,基于内存的表结构,所以速度是非常快的,挨个匹配也来得及
扰动函数:
B树:
- 3层B树就是16×16×16
B+树:
- 下面有指针
- 冗余
- 叶子节点包含了全量数据,非叶子节点不是全量的数据
- 数据都放在叶子节点里头,key值放在非叶子节点里
B树和B+树的区别?
- B树和B+树都是常见的数据结构,它们实际上是一种搜索树。它们都能够支持快速检索、插入和删除操作。B树和B+树之间有几个区别,在节点拆分和索引结构上略有不同,B+树还具有更高的范围查询效率。B树对于范围节点搜索速度较慢,因为需要在节点内遍历。而在B+树中,所有数据都存在叶子节点,在这里可以很容易地遍历数据并快速检索节点。因此,B+树通常用于实现关系型数据库。
索引几层?
- 一般情况下3-4层的B+树足以支持千万级别的数据量存储
能存多少是跟什么紧密相关的?
- 跟键值,表中记录的主键紧密相关
- 加入p1+28占100个字节
- 16×1024÷100×160×16
- 索引列的值要尽可量能少的占用空间(数据类型)
主键int和vachar在满足业务的需求下用那个来存储啊?
- int占用4个
- vachar占用不定,一个字长3字节:varchar(255)=255*3=765
- 非空设置:如果字段没设置非空就多占1字节
- 谁小用谁,key值尽可量的少暂用空间
主键要自增。
UUID尽量不要用:
- 因为它是无序的,插入的时候就会乱序插入
- 会造成什么?
- 页的分裂和合并
- 会导致下面数据满了开始分裂两块,上一层就会分裂指针来指向,会影响之前结构的变更(自增的话往后面加就可以了,不会影响分裂)
思考:
一个表中可以有几个索引?
- 多个
一个索引对应一棵树,还是多个索引对应一棵树?
- 一个索引一棵树
在B+树中叶子节点存储数据,那么实际的数据存储几份?
- 1份
其他的索引的叶子节点存储是什么?
- 在innoDB存储引擎中,数据在进行插入的时候必须要跟某一个索引列绑定在一起,如果有主键,那么选择主键,如果没有主键,那么选择唯一键,如果没有唯一键,那么mysql会生成一个6字节的rowid来绑定存储,类型为长整形
- 其他的索引的叶子节点中存储的是跟数据绑定存储的索引列的值
什么是存储引擎?
-
存储引擎就是不同的数据文件在物理磁盘上的不同的组织形式
-
代码查看: show engines;
innoDB是否支持hash索引?
- 支持的是自适应hash
- 什么是自适应hash?
- 我们人为是控制不了当前的索引的,这是mysql系统自动做判断的,会把我们的B+树转成对应的hash索引,这件事是用户没办法去干预的
索引失效:
- like左侧加%
- 类型不匹配
- 如果列是字符串类型,传入条件是必须用引号引起来,不然报错或索引失效。
- 使用函数
- 使用表达式
- in集合值太多(100行记录,in出来80个就不需要)
- or要分情况来讨论
or
的条件列除了同时是主键的时候,索引才会生效。其他情况下的,无论条件列是什么,索引都失效。
Mysql聚蔟和非聚蔟索引的区别是什么?
mysql的索引类型跟存储引擎是相关的,innoDB存储引擎数据文件跟索引文件全部放在idb文件中,而myisam的数据文件放在myd文件中,索引放在myi文件中,其实区分聚蔟和非聚蔟索引非常简单,只要判断数据跟索引是否存储在一起就可以了
innoDB存储引擎在进行数据插入的时候,数据必须根索引放在一起,如果有主键就使用主键,没有主键就使用唯一键,没有唯一键就使用6字节的rowid,因为跟数据绑定在一起的就是聚蔟索引,而为了避免数据冗余存储,其他的索引的叶子节点中存储的都是聚蔟索引的key值,因此innodb中既有聚蔟索引也有非聚蔟索引,而myisam中只有非聚蔟索引。
-
.frm是什么?
- 存储的是表结构相关的数据
-
.idb是什么?
- 存储的是数据文件和索引文件,看到idb就告诉你了,是innoDB存储引擎
-
myisam(买桑木)存储引擎中,数据文件和索引文件是分开的!
- .frm:存储的是表结构相关的数据
- .MYD:DATA数据
- .MYI:index索引
回表:
-
- id,name,gender,age - id主键,name普通索引 - select * from table where name =‘zhangsan’; - 查找过程:先根据name的值取name的B+树上找到对应的id值,然后再根据id值取id的B+树上找到整行的结果,这个过程叫回表 - 回表的效率低,要尽可能减少回表
需不需要回表取决于什么?
- 取决于你查询的列有没有在你叶子节点里包含全部的值,如果包含了就不需要回表,不包含就需要回表
索引覆盖:
-
- id,name,gender,age - id主键,name普通索引 - select id,name from table where name =‘zhangsan’; - 查找过程:直接根据name的值取name的B+树上检索数据,发现索引的叶子节点中包含了我们要查询的所有列的信息,此时不需要回表,这个过程叫做索引覆盖 - 索引覆盖效率高,要尽量使用
最左匹配原则:
-
组合原则:多个字段所创建的组合索引,必须已最左边的来查询为主
- 先匹配第一个,在匹配第二个,在匹配第三个,在第四个
-
- id,name,gender,age - id主键,name,age 组合索引 - select * from table where name =‘zhangsan’ and age=10;符合 - select * from table where age=10;不符合 - select * from table where name =‘zhangsan’;符合 - select * from table where age=10 and name =‘zhangsan’;符合,优化器,在调换位置后结果与没调换位置的结果一样时候,可以符合 在优化器里面,会对这种sql进行调整,转成最左匹配原则顺序,来满足我们的业务需要
索引下推:
-
- id,name,gender,age - id主键,name,age 组合索引 - select * from table where name =‘zhangsan’ and age=10;符合 执行过程:在没有索引下推之前,是在存储引擎中对name的值进行过滤,将赛选之后的数据拿到mysql的server中,然后在堆age进行数据过滤 有了索引下推之后,根据name和age的值直接在存储引擎中做数据筛选,返回的就是符合要求的数据 索引下推指的是,在server层进行的数据过滤下推到了存储引擎层
mysql的结构:
-
当数据从客户端传到了server层,在传到存储引擎层
- 在存储引擎中处理玩完,返还到server中进行排序,在返回客户端,这是正常的
- 在存储引擎中处理玩完排序,返还到server中是处理完的数据,在返回客户端,这是索引下推,把server中的步骤推到存储引擎去做了
索引设计原则:
索引的设计可以遵循一些已有的原则,创建索引的时候尽量考虑符合这些原则,使用提升索引的使用效率,更高效的使用索引。
- 对查询频繁次较高,且数量比较大的表建立索引
- 索引字段的选择,最佳候选列应当从where子句的条件中提取,如果where子句中的组合比较多,那么应当挑选最常用,过滤效果最好的列的组合
- 使用唯一索引,区分度越高,使用索引的效率越高
- 索引可以有效的提升查询数据的效率,但索引数量不是多多益善,索引越多,维护索引的代价自然也就水涨船高,对于插入,更新,删除等DML操作比较频繁的表来说,索引过多,就引入相当高的维护代价,降低DML操作的效率,增加相应操作的时间消耗,另外索引过多的话,MYSQL也会犯选择困难病,虽然最终仍然会找到一个可用的索引,但无疑提高了选择的代价
- 使用短索引,索引创建之后也是使用硬盘来存储的,因此提升索引访问的io效率,也可以提升总体的访问效率,假如构成索引的字段总长度比较短,那么在给定大小的存储块内可以存储更多的索引值,相应的可以有效的提升mysql访问索引的io效率
- 利用最左前缀,N个列组合而成的组合索引,南无相当于是创建了N个索引,如何查询时where子句中使用了组成该索引的前几个字段,南无这条查询sql可以利用组合索引来提升查询效率