什么是索引
索引是帮助MySQL高效获取数据的排好序的数据结构
数据库建立索引的常用规则如下:
1.表的主键和外键建立索引
2.在order by 或者 group by 后边建立索引
3.数据量超过300的应该建立索引
4.经常与其他表进行连接的表的字段,应该在该字段上建立索引
5.经常出现在where字句中的字段应该建立索引
6.复合索引在建立是应该仔细分析,经量用单字段索引代替
不应该建立索引的字段规则
1.不应该在字段比较长的字段上建立索引,因为会消耗大量的空间
2.对于经常频繁进行修改和插入的表应该少建立索引,因为在修改 和插入之后,数据库会去维护索引,会消耗资源
3.尽量少在无用字段上建立索引
索引存储在文件里
索引结构
二叉树:从左到右依次递增,左边的数小于它的父节点,右边的数大于它的父节点
红黑树
Hash :底层通过hash算法得到一个地址值映射到要查找的数据,不适合范围查找
B-Tree
度(Degree)-节点的数据存储个数
叶节点具有相同的深度
叶节点的指针为空
节点中的数据key从左到右递增排列
B+TREE(B-Tree变种)
非叶子节点不存储data,只存储key,可以增大度
叶子节点不存储指针
顺序访问指针,提高区间访问的性能
面试题:B+Tree为什么把data都移到叶子节点?
MySql的节点大小是有限制的,把data移到叶子结点意味着大的节点(大节点分配的内存默认是16K)可以横向存储更多的索引元素,索引的分叉将会更多,树的高度更加小了。
大节点分配的内存:
show GLOBAL STATUS like ‘innodb_page_size’;
叶子结点可以存储多少索引的数值:
:16K /14B =1170个,共有三层,1170 * 1170 * 16 大约 2千1百万左右
8B+6B =14B 叶子节点16K
MyISAM和InnoDB都是用来形容表的
MyISAM引擎的表在D:\JavaWeb\MySql5.5\data\mysql目录下有三个文件(以user表为例):user_frm、user_MYD、user_MYI
user_frm:存储表的定义
user_MYD:表对应的所有数据行
user_MYI:索引字段存储的位置
MyISAM索引的实现(非聚集):
以select * from user where col1=49为例
B+Tree存在MYI文件里面,拿到col1字段先从根节点去定位查找,找到叶子结点对应的data里面的数据(文件磁盘的指针),然后去MYD文件里面查找
Col1作为主键索引
data里面存储存储的是索引所在行的行数据的磁盘文件指针(16进制的值)
InnoDB索引实现(聚集)
MyISAM引擎的表在D:\JavaWeb\MySql5.5\data\mysql目录下有两个文件
(以student表为例):student_frm、student_ibd
叶子结点的指针解决了范围内的高校查找
聚集索引:主键索引和数据存储在一个文件里面
面试题:为什么InnoDB表必须要有主键,并且推荐使用整形的自增长主键?
如果设置了主键,那么InnoDB会选择主键作为聚集索引。如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增)。
整形存储在磁盘空间比字符串占用的空间更小;整形比较大小比字符串效率更高;主键自增可以尽可能的避免我插入的这个节点插入到叶子节点之后,原有的叶子节点已经满了之后做分裂以及自动平衡。
面试题:为什么非主键索引结结构叶子节点存储的是主键值?(一致性和节省存储空间)
减少了出现行移动或者数据页分裂时二级索引的维护工作(当数据需要更新的时候,二级索引不需要修改,只需要修改聚簇索引,一个表只能有一个聚簇索引,其他的都是二级索引,这样只需要修改聚簇索引就可以了,不需要重新构建二级索引)
聚簇索引也称为主键索引,其索引树的叶子节点中存的是整行数据,表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。因为索引(目录)只能按照一种方法进行排序。
非聚簇索引(普通索引)的叶子节点内容是主键的值。在 InnoDB 里,非主键索引也被称为二级索引(secondary index)。
常见的索引优化(未完待续)
密码怎样保存才安全?
使用MD5加密