贵阳搜索玩的网站,wordpress 简约企业,怎么做存储网站,linux服务器WordPress建站教程文章目录 MySQL 索引索引分类索引创建和删除索引的执行过程explain 查看执行计划explain 结果字段分析 索引的底层实现原理B-树B树哈希索引 聚集和非聚集索引MyISAM#xff08;\*.MYD#xff0c;*.MYI#xff09;主键索引辅助索引#xff08;二级索引#xff09; InnoDB树哈希索引 聚集和非聚集索引MyISAM\*.MYD*.MYI主键索引辅助索引二级索引 InnoDB*.IBD主键索引辅助索引二级索引 自适应哈希索引 MySQL 索引 当表中的数据量到达几十万甚至上百万的时候SQL查询所花费的时间会很长导致业务超时出错此时就需要用索引来加速SQL查询。 由于索引也是需要存储成索引文件的因此对索引的使用也会涉及到磁盘I/O操作。如果索引创建过多使用不当会造成SQL查询时进行大量无用的磁盘I/O操作降低了SQL的查询效率适得其反因此需要掌握良好的索引创建原则 索引分类
索引是创建在表上的是对数据库表中的一列或者多列的值进行排序的一种结果。索引的核心是提高查询的速度
物理上聚集索引 非聚集索引/ 逻辑上…
索引的优缺点 优点提高查询效率 缺点索引并非越多越好过多的索引会导致CPU使用率居高不下由于数据的改变会造成索引文件的改动过多的磁盘I/O会造成CPU负荷太重 普通索引没有任何限制条件可以给任何类型的字段创建普通索引创建新表 已创建表数量是不限的一张表的一次SQL查询只能用一个索引 where a 1 and b ‘M’唯一性索引使用 UNIQUE 修饰的字段值不能够重复主键索引就隶属于唯一性索引主键索引使用 PRIMARY KEY 修饰的字段会自动创建索引MyISAM、InnoDB单列索引在一个字段上创建索引多列索引在表的多个字段上创建索引uid cid多列索引必须使用到第一列才能够用到多列索引否则索引用不上全文索引使用 FULLTEXT 参数可以设置全文索引只支持 CHAR、VARCHAR、TEXT 类型的字段上常用于数据量较大的字符串类型上可以提高查询速度线上项目支持专门的搜索功能给后台服务器增加专门的搜索引擎支持快速高效的搜索 elesticsearch 简称 esC开源的搜索引擎搜狗的workflow
索引创建和删除
创建表的时候指定索引字段
CREATE TABLE index1(id INT,name VARCHAR(20),sex ENUM(male, female),INDEX(id, name),INDEX index name (sex) # 没有名字会默认会生成# show create table 进行查看创建SQL语句
)# Create Table: CREATE TABLE student (
# uid int unsigned NOT NULL AUTO_INCREMENT,
# name varchar(50) NOT NULL,
# age tinyint unsigned NOT NULL,
# sex enum(M,W) NOT NULL,
# PRIMARY KEY (uid),
# KEY nameidx (name)
#) ENGINEInnoDB AUTO_INCREMENT7 DEFAULT CHARSETutf8mb4 #COLLATEutf8mb4_0900_ai_ci在已经创建的表上添加索引
CREATE [UNIQUE] INDEX 索引名 ON 表明 (属性名 (length) [ASC | DESC]);# create index nameidx on student(name); // 默认btree索引# 添加 hash 索引
# create index ageidx on studetn(age) using hash;删除索引
DROP INDEX 索引名 ON 表名;查看所有索引
show indexes from 表明;1、经常作为 where 条件过滤的字段考虑添加索引
2、字符串列创建索引时尽量规定索引的长度而不能让索引值的长度 key_len 过长
3、索引字段涉及类型强转、mysql函数调用、表达式计算等索引就用不上了索引失效 索引的执行过程
explain 查看执行计划
使用explain查看SQL的执行计划分析索引的执行过程mysql的user权限示例如下
可以看到使用了主键索引共扫描了1行Using index 表示直接从索引树上查询到结果不需要回表 由于Host和User设立了联合索引也就是多列索引而多列索引只有第一列的索引使用了才能进行索引查询不然不会进行索引查询 explain 结果字段分析 select_type simple表示不需要union操作或者不包含子查询的简单select语句。有连接查询时外层的查询为simple且只有一个 primary一个需要union操作或者含有子查询的select位于最外层的单位查询的select_type即为primary且只有一个 unionunion连接的两个select查询除了第一个表外第二个以后的表的select_type都是union union result包含union的结果集在union和union all语句中因为它不需要参与查询所以id字段为null table 显示查询的表明 如果不涉及对数据库的操作这里就显示null 如果显示为尖括号就表示这是个临时表后边的N就是执行计划中的id表示结果来自于这个查询产生的 如果是尖括号括起来union M, N也是一个临时表表示这个结果来自于union查询的id为MN的结果集 type const使用唯一索引或者主键返回记录一定是1行已经的等值where条件时通常type就是const ref常见于辅助索引的等值查找或者多列主键唯一索引中使用第一个列之外的列作为等值查找会出现返回数据不唯一的等值查找也会出现 range索引范围扫描常见于使用is nullbetweeninlike等运算符的查询中 index索引全表扫描把索引从头到尾扫一遍常见于使用索引列就可以处理不需要读取数据文件的查询可以使用索引排序或者分组的查询 all全表扫描数据文件然后在server层进行过滤返回符合要求的记录 ref 如果使用常数等值查询这里显示const 如果是连接查询被驱动表的执行计划这里会显示驱动表的关联字段 Extra using filesort排序时无法用到索引常见于 order by 和 group by 语句中using index查询时不需要回表查询直接通过索引就可以获取查询的数据
索引的底层实现原理 数据库索引是存储在磁盘上的当数据量大时就不能把整个索引全部加载到内存了只能逐一加载每一个磁盘块对应索引树的节点索引树越低越”矮胖“磁盘I/O次数就越少 MySQL支持两种索引一种是B-树索引一种是哈希索引两者在数据查询的效率是非常高的
主要对于InnoDB存储引擎进行讨论基于B-树的索引结构MySQL实际采用的是B树结构
B-树
B-树是一种m阶平衡树一般来说m300~500叶子节点都在同一层由于每一个节点存储的数据量比较大索引整个B-树的层数是比较低的基本上不超时三层。
二分查找时间复杂度O(logn)
由于磁盘的读取也是按block块操作的内存是按page页面操作的因此B-树的节点大小一般设置为和磁盘块大小一致这样一个B-树节点就可以通过一次磁盘I/O把一个磁盘块的数据全部存储下来所以当使用B-树存储索引的时候磁盘I/O的操作次数是最少的MySQL的读写效率主要集中在磁盘I/O上 select * from student where uid 5uid添加了索引; uid 有索引 存储引擎 kernel操作系统 磁盘I/O读索引文件 内存上 用索引的数据构建B-树加速搜索 一次磁盘I/O读取的磁盘块的内容刚好存储在B树的一个节点中 因为非叶子节点都有左右子树所以指针域 数据域 1 假设有2000W条数据用AVL存储2000W条数据构建下来有25层计算器以log10为底所以得这么除 如果 m 500最多三层也就是最多花费三次的磁盘I/O大大减少了速率 扩展B-树的缺点 每个节点中有key也有data但是每一个节点的存储空间是有限的如果data数据较大时会导致每个节点能存储的key的数据很小当存储的数据量很大时同样会导致B-树的高度较大磁盘I/O次数花费增大导致效率降低 面试考点为什么MySQLMyISAM和InnoDB索引底层采用B树而不是B-树 1、索引 数据内容分散在不同的节点上离根节点近的搜索就快离根节点远的搜索就慢 2、每一个非叶子节点上不仅仅要存储索引key还要存储索引值所在的那一行的data数据。一个节点所能存放的索引key的个数比只存储key值的节点的个数要少得多。例如索引key要存4个字节对应data数据也要4个字节那就是8个字节每一块数据但是如果只存索引key那就可以存两个数据最后去叶子节点上找 3、这棵树不方便做范围搜索整表遍历看起来也不方便 例如where age 11 and age 18相当于17的左右子树都要去查然后12的左右子树也要去查。很不方便看上图 因此由于以上的三个原因 MySQL采用B树来构建索引树 B树 一句话B树最后其实就是一个循环链表串起来了所以说最后的查询就是在循环链表中进行查询 特点
每一个非叶子几点只存放索引key不存放数据data好处就是一个节点存放的索引key更多B树在理论上来说层数会更低一些搜索的效率会更好一些叶子节点上存储了所有的索引值数据data搜索每一个索引对应的值data都需要在叶子节点上这样子每一行记录搜索的时间是平均的叶子节点被串在一个链表当中形成一个有序的链表如果要进行索引树的搜索整表搜索直接遍历叶子节点的有序链表即可或者做范围查询的时候直接遍历叶子节点的有序链表即可 面试考点B-树和B树在存储结构上有什么不同 1、B-树的非叶子节点存了索引key和对应的数据地址而B树的非叶子节点只存了索引key不存数据data因此B树的每一个非叶子节点存储的关键字是远远多于B-树的因此从树的高度上来说B树的高度要小于B-树使用的磁盘I/O次数少因此查询的会快一些 2、B-树由于每个节点都存储了索引key和对应的数据因此离根节点近的数据查询的就快离根节点远的数据查询的就慢而B树所有的数据都存储在叶子节点上因此在B树上搜索索引key找到对应数据的时间是比较平均的没有快慢之分 3、在B-树上如果做区间查找遍历的节点是非常多的B树所有的叶子节点被连结成了有序链表结构因此做整表遍历和区间查找是非常容易的 哈希索引 哈希索引由哈希表实现的哈希表对数据并不排序因此不适合做区间查找效率非常低需要搜索整个哈希表结构 时间复杂度O1链式哈希表 优点搜索的效率更好、磁盘I/O花费要少 hash(name) hashkey % bucket_num 哈希冲突 哈希表中的元素没有任何顺序可言只能进行等值比较
select * from student where name zhangsan;select * from student where name like zhang%; error范围搜索前缀搜索order by 排序这些操作哈希索引都不适合它没办法处理磁盘上的数据加载到内存上构建高效的搜索数据结构因为它没有办法减少磁盘I/O的次数
聚集和非聚集索引
MyISAM*.MYD*.MYI select * from student where name zhangsan;没有索引直接会在整张表中进行全查整表搜索 有索引进行*.MYI的索引文件的查询生成B树然后进行二分查找 主键索引
MyISAM引擎使用B树作为索引结构叶节点的data域存放的是数据记录的地址通过这个地址去查找数据data。 即主键对应的data是数据的地址具体的数据都需要去寻址在一个data数据表中
辅助索引二级索引
在MyISAM中主键索引和辅助索引在结构上没有任何区别只是主索引要求key是唯 一的而辅助索引的key可以重复如果给其它字段创建辅助索引结构图如下 总结MyISAM存储引擎索引结构叶子节点存储关键字和数据地址也就是说索引关键字和数据 没有在一起存放体现在磁盘上就是索引在一个文件存储数据在另一个文件存储例如一个user 表会在磁盘上存储三个文件 user.frm表结构文件 user.MYD表的数据文件 user.MYI表的索引文件。
MyISAM的索引方式也叫做非聚集索引
InnoDB*.IBD
InnoDB存储引擎的索引关键字和数据是存放在一起的 select * from student where name zhangsan;没有索引会自动生成B树 有索引会根据索引数据生成一张新的B树 主键索引
uid 是主键主键索引树 每个索引下面对应的是对应的数据例如15 34 Bob
辅助索引二级索引
InnoDB的辅助索引叶子节点上存放的是索引关键字和对应的主键即如果要获取数据需要通过主键进行回表查询主键索引树进行获取到数据 select * from student where name Alice;1、先搜索name的二级索引树找到Alice对应的主键uid18 2、再拿uid18回表在书简索引树搜索uid那一行记录 问题select * from student where age 20 order by name如果只给age添加索引行不行还有什么没有考虑到 不行如果只有age键索引会有using filesort因此需要创建age、name的多列索引 先按照age进行排序再按照name进行排序age相同按name进行排序 总结辅助索引的B树先根据关键字找到对应的主键再去主键索引树上找到对应的行记录数据。从索引树上可以看到InnoDB的索引关键字和数据都是在一起存放的体现在磁盘存储上例如创建一个user 表在磁盘上只存储两种文件user.frm存储表的结构user.ibd存储索引和数据。
InnoDB的索引树叶节点包含了完整的数据记录这种索引叫做聚集索引。
两者的区别MyISAM比InnoDB多了磁盘I/O的使用它需要读取*.MYI文件获取索引数据进行生成B树相当于多了一部I/O操作两个文件
自适应哈希索引 InnoDB存储引擎监测到同样的二级索引不断被使用那么它会根据这个二级索引在内存上根据二级索引树B树上的二级索引值在内存上构建一个哈希索引来进行加索引搜索 自适应哈希索引本身的数据维护也是要耗费性能的并不是说自适应哈希索引在任何情况下都会提升二级索引的查询性能我们需要根据参数指标来具体分析是否打开或者关闭自适应哈希索引
show engine innodb status\G1、RW-latch等待的线程数量自适应哈希索引默认分配了8个分区同一个分区等待的线程数量过多
2、走自适应哈希索引的频率低和二级索引树搜索的频率高面试考点SQL和索引的优化问题 通过explain分析SQL 项目 业务 千条、万条SQL 流程从什么地方能够获取哪些运行时间长耗性能的SQL然后再通过explain去分析它
