Mysql索引学习之路

前言 

        工作中一直要接触mysql数据库，以前只知道mysql 基本操作, 没有深入去掌握mysql 知识线上出现数据库相关问题时无从下手，于是抽出时间，去网上找了相关资料，深入学习了一下mysql索引的相关知识。

索引分类

• 主键索引

                非空唯一索引，一个表只有一个主键索引；在innodb中，主键索引的B+树包含表数据信息；

                PRIMARYKEY(key)

• 唯一索引

                不可以出现相同的值，可以有NULL值；

                UNIQUE(key)

• 普通索引

                允许出现相同的索引内容；

                INDEX(key)

                --OR

                KEY(key[,...])

• 组合索引

                对表上的多个列进行索引

                INDEXidx(key1,key2[,...]);

                UNIQUE(key1,key2[,...]);

                PRIMARYKEY(key1,key2[,...]);

• 全文索引

                将存储在数据库当中的整本书和整篇文章中的任意内容信息查找出来的技术；关键词FULLTEXT;在短字符串中用LIKE%；在全文索引中用match和against；

主键选择

        innodb中表是索引组织表，每张表有且仅有一个主键；

• 如果显示设置PRIMARYKEY，则该设置的key为该表的主键；
• 如果没有显示设置，则从非空唯一索引中选择；
•         只有一个非空唯一索引，则选择该索引为主键；
•         有多个非空唯一索引，则选择声明的第一个为主键；
• 没有非空唯一索引，则自动生成一个6字节的_rowid作为主键；

约束

        为了实现数据的完整性，对于innodb，提供了以下几种约束，primary key，unique key，foreign key，default, not null；实际工作环境中不使用外键约束，外键约束影响数据库性能，一般是在程序中维护关系;创建主键索引或者唯一索引的时候同时创建了相应的约束；但是约束是逻辑上的概念；索引是一个数据结构既包含逻辑的概念也包含物理的存储方式。

索引实现

索引存储

        innodb由段、区、页组成；段分为数据段、索引段、回滚段等；区大小为1MB（一个区由64个连续页构成）；页的默认值为16k；页为逻辑页，磁盘物理页大小一般为4K或者8K；为了保证区中的页的连续，存储引擎一般一次从磁盘中申请4~5个区。

页

        页是innodb磁盘管理的最小单位；默认16k，可通过innodb_page_size参数来修改；B+树的一个节点的大小就是该页的值。

B+树

        多路平衡搜索树；红黑树平衡二叉搜索树。

聚集索引

        按照主键构造的B+树；叶子节点中存放数据页；数据也是索引的一部分。

辅助索引

　　　叶子节点不包含行记录的全部数据；辅助索引的叶子节点中，除了用来排序的key还包含一个bookmark；该书签存储了聚集索引的key。

最左匹配原则

        对于组合索引，从左到右依次匹配，遇到><betweenlike就停止匹配；explain 查看索引匹配情况。

覆盖索引

        从辅助索引中就能找到数据，而不需通过聚集索引查找；利用辅助索引树高度一般低于聚集索引树；较少磁盘io；sql中不要使用select * from table 只查询需要的字段。

索引失效        

        索引字段参与运算，则索引失效；

        例如：from_unixtime(idx)='2021-04-30';

        索引字段发生隐式转换，则索引失效；

        例如：'1'隐式转换为1；

        LIKE模糊查询，通配符%开头，则索引失效；

        例如：select*fromuserwherenamelike'%ark';

        在索引字段上使用NOT<>!=索引失效；

        如果判断id<>0则修改为idx>0oridx<0；

        组合索引中，没使用第一列索引，索引失效；

索引原则

•         查询频次较高且数据量大的表建立索引；
•         索引选择使用频次较高，过滤效果好的列或者组合；
•         使⽤短索引；节点包含的信息多，较少磁盘io操作；对于很长的动态字符串，考虑使用前缀索引；

                有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。对于BLOB,TEXT,VARCHAR类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。

•         对于组合索引，考虑最左侧匹配原则和覆盖索引；
•         尽量选择区分度⾼的列作为索引；该列的值相同的越少越好；
•         尽量扩展索引，在现有索引的基础上，添加复合索引；
•         不要select*；尽量只列出需要的列字段；
•         索引列，列尽量设置为非空；

总结

　　通过对mysq索引的系统学习，掌握了索引的基本原理，对索引不在陌生， 使用索引时更有把握， 工作时遇到相关问题，有解决问题的思路, 后面继续mysql相关知识的学习，深入掌握mysql。

技术参考

　　https://ke.qq.com/course/417774?flowToken=1040951&taid=12033308657868782