Mysql索引

最新推荐文章于 2024-09-13 22:38:14 发布

奔跑的蜗牛...

最新推荐文章于 2024-09-13 22:38:14 发布

阅读量265

点赞数 5

分类专栏：数据库文章标签： mysql b树数据库

本文链接：https://blog.csdn.net/xiaomengxin521/article/details/121683809

版权

数据库专栏收录该内容

12 篇文章 0 订阅

订阅专栏

本文深入探讨了数据库索引的作用、类型和结构，包括BTree和Hash索引等。强调了索引设计原则，如选择高频查询列、创建唯一索引和复合索引。同时，介绍了如何创建、查看和删除索引，以及索引使用中的注意事项，如最左前缀法则和索引失效情况。并提供了分析索引使用情况的方法，帮助优化数据库性能。

摘要由CSDN通过智能技术生成

索引概述

索引是帮助Mysql高效获取数据的数据结构（有序），索引每个节点维护着表中数据的引用

索引的优势劣势

优势

类似书籍的目录索引，提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序成本，降低cpu消耗

优势

索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录
索引列也是要占用空间的（磁盘）
虽然提高了查询效率，同时降低了更新表的速度，对表进行insert、update、delete时要对索引键值变化做调整

索引结构

索引是在Mysql的存储引擎层中实现的，而不是在服务器层实现的，所以每种存储引擎的索引都不一定完全相同
也不是所有的存储引擎都支持所有的索引类型的

BTREE索引

最常见的索引类型，大部分索引都支持B树索引

Btree又叫多路平衡搜索树，一颗m叉树的Btree特性
	树中每个节点最多包含m个孩子
	除根节点与叶子节点外每个节点至少有[ceil(m/2)]个孩子
	若跟节点不是叶子节点，则至少有两个孩子
	所有叶子节点都在同一层
	每个非叶子节点又n个key与n+1个指针组成，其中[cell(m/2)-1] <= n <= m -1
B+tree是BTree的变种
	1、n叉B+Tree最多含有n个key，而Btree最多含有n-1个key
	2、B+Tree的叶子节点保存所有的key信息，依key大小顺序排列
	3、所有的非叶子节点都可以看作是key的索引部分

HASH索引

只有Memory引擎支持，使用场景简单

R-tree索引（空间索引）

空间索引是MyISAM引擎的一个特殊引用类型，主要用于地理空间数据类型，通常使用较少

Full-text（全文索引）

全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引

索引分类

单值索引：一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：一个索引包含多个列

索引设计原则

查询频次较高，且数据量比较大的表建立索引
索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用，过滤效果最好的列的组合
使用唯一索引，区分度越高，使用索引的效率越高
索引不宜太多，减少DML维护成本
使用短 索引，短索引创建之后也是使用硬盘存储的，因此提升索引访问的I/O效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升Mysql访问索引效率
利用最左前缀，N个列组合而成的组合索引，那么相当于创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询sql可以利用组合索引来提升查询效率

索引使用

语法

创建索引
	alter table 表名 add primary key(列名)---添加主键索引--唯一，不为null
	alter table 表名 add unique 索引名(列名)---唯一索引，可以是null，存在多个null
	alter table 表名 add index 索引名(列名) ---添加普通索引
	alter table 表名 add fulltext 索引名(列名) 添加全文索引
	create index 索引名称 on 表名(,,,...列名)
	create index index_表名_ 列名 on 表名(列名)
查看索引
	show index from 表名
删除索引
	drop index  索引名称 on 表名

磁盘占用

查看表占用内存
	select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,
	concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB
	from information_schema.tables
	where table_schema= 'db_test' AND table_name='t_corp';
查看库占用磁盘空间大小
	select 
	TABLE_SCHEMA, 
	concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
	concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
	from information_schema.tables
	group by TABLE_SCHEMA
	ORDER BY data_size desc;

索引失效

查看索引的使用情况
	show global status like 'Handler_read%'
	Handler_read_first    | 5  |读取索引第一个条目的次数
    Handler_read_key      | 27 |通过index获取数据的次数
    Handler_read_last     | 0  |读取索引最后一个条目次数
    Handler_read_next     | 1  |通过索引读取下一条数据的次数
    Handler_read_prev     | 0  |通过索引读取上一条数据的次数
    Handler_read_rnd      | 0  |从固定位置读取数据的次数
    Handler_read_rnd_next | 46 |从数据节点读取下一条数据的次数
复合索引遵循最左前缀法则：最左侧索引开始，不能跳过使用，不然不使用索引
字符串不使用引号，索引失效
索引列使用运算操作索引失效
is null 或者is not null ：如果表数据null值多 is null不走索引，如果表数据null值少甚至没有 null ，is not null 不走索引 。取反原则走索引（根据数据量确定的）
in 使用索引，not in 不使用索引
尽量使用复合索引：因为复合索引可以相互之间关联可以组成多个索引，而多个单列索引只会用到一种最优的索引（辨识度最高的）