索引概述
索引是帮助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 不使用索引
尽量使用复合索引:因为复合索引可以相互之间关联可以组成多个索引,而多个单列索引只会用到一种最优的索引(辨识度最高的)