MySQL索引

最新推荐文章于 2024-06-17 15:31:11 发布

Tao_oc

最新推荐文章于 2024-06-17 15:31:11 发布

阅读量118

点赞数

本文链接：https://blog.csdn.net/weixin_41183791/article/details/107469228

版权

索引

索引概念:索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息哪些字段可以建索引？一般都where、order by 或者 group by 后面的字段

索引的作用:

提高查询速度
确保数据的唯一性
可以加速表和表之间的连接,实现表与表之间的参照完整性
使用分组和排序子句进行数据检索时,可以显著减少分组和排序的时间
全文检索字段进行搜索优化

索引分类:

主键索引(Primary Key)
唯一索引(Unique):聚集索引,也叫聚簇索引
常规索引(Index)
全文索引(FullText)

主键索引:

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

主键:某一个属性组能唯一标识一条记录,创建主键自动添加索引.
主键索引(也叫聚簇/聚集索引)是特殊的唯一索引,该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问(不需要回表)。myisam不支持聚簇/聚集索引,它的索引和数据是分离的
特点:

最常见的索引类型
确保数据记录的唯一性
确定特定数据记录在数据库中的位置

唯一索引:

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)

要求:索引列的值必须唯一，但允许有空值

与主键索引的区别
主键索引只能一个
唯一索引可能有多个

普通索引:

ALTER TABLE tbl_name ADD INDEX index_name (column_list)

作用:快速定位特定数据

index和key关键字都可以设置常规索引
应加在查询找条件的字段
不宜添加太多常规索引,影响数据的插入,删除和修改操作

全文索引

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

快速定位特定数据
注意:

用于MyISAM类型的数据表,InnoDB存储引擎从1.2.x开始支持全文索引技术
只能用于CHAR,VARCHAR,TEXT数据列类型
适合大型数据集

联合索引

ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 );

添加(column1, column2, column3)的联合索引,相当于添加了(column1, column2, column3),(column1, column3),(column1,column2),如果不包含column1,则不会索引(最左前缀原则).

查看表中的索引:

SHOW INDEX FROM tbl_name

删除索引:

ALTER TABLE tbl_name DROP INDEX index_name;

最左匹配原则
Mysql创建复合索引的规则是首先会对复合索引最左边，也就是第一个字段的索引进行排序，在第一个字段排序的基础上，在对第二个字段进行排序，所以直接使用第二个字段是没有顺序的.
索引失效
1.以%开头的LIKE查询不能使用BTREE索引
2.联合索引不符合最左匹配原则
3.数据类型出现隐式转换时也不能使用索引,比如 where name=“01” ,数据库把01当成了数字1了.
4.由于对字段进行运算、函数操作导致无法用索引.
5.order by 字段混合使用DESC ASC 不会使用索引
6.Where条件过滤的关键字和Order by中所使用的不同不会使用索引
7.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
8.用 or 分割开的条件或者in 嵌套的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到

索引准则

索引不是越多越好,不被经常查询的不要加索引
不要对经常变动的数据加索引,更新表时,不仅要保存数据,还要维护索引文件
小数据量的表建议不要加索引,全表扫描更加高效
索引一般应加在查找条件的字段
如果某个数据列包含太多重复的内容,为它建立索引就没有太大的效果
索引文件占磁盘空间.
应该只为经常查询和经常排序的数据列建立索引
注意避免冗余索引
索引优化
对于索引列来最好使用union all，因复杂的查询【包含运算等】将使or、in放弃索引而全表扫描，除非你能确定or、in会使用索引
在使用联合索引的时候,注意最左前缀原则
选择重复度比较低的列作为索引
一般外键加索引

索引的数据结构:

hash类型的索引:查询单条快,范围查询慢
b tree类型的索引:b++树,层数越多,数据量指数级增长(mysql一般用它,InnoDB默认支持)

数据库引擎

InnoDB 支持事务,支持行级锁定,支持 B-tree,Full-text 等索引,不支持 Hash索引
MyISAM 不支持事务,支持表级别锁定,支持 B-tree,Full-text 等索引, 不支持 Hash 索引

B+树和B树的差异:

B+树非叶子节点仅具有索引作用,有关信息均存放在叶子节点上
B+树的所有叶子节点构成一个有序链表,可以按照元素排序的次序遍历全部记录,便于区间查找和遍历.

B+树的优缺点:

B+树的层级更少:非叶子节点存储的关键字更多,查询数据更快
B+树查询速度更稳定:B+树所有数据地址都在叶子节点上
B+树天然具备排序功能:叶子节点构成有序链表,在查询大小区间的数据时更方便,数据紧密度很高,缓存的命中率也会比B树高
B+树全节点遍历更快:B+树只要遍历叶子节点即可,B树要全树遍历
B树相当B+树的优点:如果信息离根节点很近,呢么查询它就比较块
这两个数据结构都用到了二分思想

InnoDB和MyISAM都使用B+树作为索引的数据结构,但是实现有区别

InnoDB的数据文件本身就是索引文件,MyISAM索引文件和数据文件是分离的,索引文件仅保存数据记录的地址
在InnoDB中,数据文件本身就是按B+Tree组织的一个索引结构,这颗树的叶子节点保存了完成的数据记录,InnoDB表数据文件本身就是主索引,InnoDB的数据文件本身要按主键聚集,索引InnoDB要求表必须有主键(MyISAM可以没有)
InnoDB的辅助索引data域存储相应记录主键的值而不是地址,innoDB所有辅助索引都引用主键作为data域

回表
无法在普通索引树上获取需要的全部信息,需要再次查询主键索引树.
比如user表中有,id,name,age.id作为主键,然后添加name的索引
select * from user where name=“xx”,我们需要整行信息,我们在查询普通索引name的时候,能查到name呢行的主键,我们还需要去查询主键索引,这个过程叫回表.总共有2次查询b+树的操作.
select id,name from user where name=“xx”,这样不需要回表.
我们也可以简历联合索引, (name,age),这样他在普通索引上就能获取到所需要的全部信息,不需要再去查询主键索引树,加快查询速度.

覆盖索引
只需要在一颗索引树上查询,无需回表,速度较非覆盖索引快.

为什么mysql用b+树作为索引而不用b树?

因为mysql的查找一般发生再磁盘上,而且索引一般都非常大,b+树的根节点只存储索引信息,而b树的根节点还存储数据,磁盘在读取根节点的时候一次读取的信息有限,读取b树的次数可能更多,影响性能.
b+树的叶子节点用双向链表串起来了,更适合遍历全部索引,而b树需要遍历整颗树.

MySQL索引背后的数据结构及算法原理学习链接:http://blog.codinglabs.org/articles/theory-of-mysql-index.html
详细深入的索引总结:https://zhuanlan.zhihu.com/p/29118331
Mysql中key 、primary key 、unique key 与index区别:https://www.cnblogs.com/zjfjava/p/6922494.html
b树和b+树的构造:https://blog.ouyangsihai.cn/mian-shi-guan-wen-ni-b-shu-he-b-shu-jiu-ba-zhe-pian-wen-zhang-diu-gei-ta.html