目录
本文讲的索引/事务都是基于Mysql5.5之后默认的数据库引擎InnoDB
一、什么是索引
索引是⼀种特殊的⽂件,包含着对数据表⾥所有记录的引⽤指针。可以对表中的⼀列或多列创建索引,并指定索引的类型,各类索引有各⾃的数据结构实现。索引通俗的理解,就是书的目录,通过这个目录就 可以快速找到对应的内容(通过索引我们就可以快速找到对应的数据)
二、说一下mysql的数据库引擎,mysql常见的引擎有哪些?
MySQL 最著名的引擎有两个:InnoDB 和 MyISAM,MyISAM 是 MySQL 5.5 之前默认的引擎,MySQL 5.5 之后的默认引擎是 InnoDB。
二者的区别:
稳定性:InnoDB引擎支持事务(保证数据的稳定性),稳定性比MyISAM好,MyISAM不支持事务;
性能:MyISAM性能比较高,而InnoDB性能不如MyISAM。
对于使⽤者来说牺牲⼀些性能换取更⾼的稳定性是⾮常明智的选择。
三、索引的作用
1、数据库中的表、数据、索引之间的关系,类似于书架上的图书、书籍内容和书籍目录的关系。
2、索引所起的作用类似书籍目录,可用于快速定位、检索数据。
3、索引对于提高数据库的性能有很大的帮助。
四、使用场景
要考虑对数据库表的某列或某几列创建索引,需要考虑以下几点:
1、数据量较大,且经常对这些列进行条件查询。
2、该数据库表的插入操作,及对这些列的修改操作频率较低。
3、索引会占用额外的磁盘空间。
满足以上条件时,考虑对表中的这些字段创建索引,以提高查询效率。反之,如果非条件查询列,或经常做插入、修改操作,或磁盘空间不足时,不考虑创建索引。
五、查询数据库引擎
show create table table_name;
show variables like ‘default_storage_engine’;
六、为什么要使用索引?
使用索引后可以大大提升查询寻效率。
1.使用索引可以避免顺序查询,可以直接将查询的访问定位出来,这样的话查询效率就能大大的提升;
2.使用索引可以将数据库中的关键索引信息存储到内存中,二内存的操作速度远比磁盘快。
查询数据存储的⽬录:show variables like ‘%dir%’;
数据库中数据存储在磁盘,磁盘的顺序查询速度是很慢的,所谓的顺序查找指的是读取磁盘中的数据⼀条⼀条的进⾏查找。
索引就是为了避免顺序查询,提供查询速度的。
⼀句话概括:索引可以提⾼查询效率,所以每个表中都会有索引。
七、索引的优缺点
优点:
1、提⾼数据库查询效率。
2、减少主从复制从库的延迟时间。
缺点:
1、索引维护成本很⾼,因为它使⽤的 B+ 树,每次新增、删除数据都需要整理树结构。
2、占⽤更多的存储空间(磁盘和内存):每个索引都对应⼀个 B+ 树。
3、索引过多会对 MySQL 的优化器造成⼀定的负担。
八、注意事项
1.如果是不常使⽤的列,或经常做插⼊、修改操作的列,不适合加索引,因为添加、删除需要重新整理索引,速度很慢。
2. 索引会占⽤额外的磁盘空间,或磁盘空间不⾜时,不考虑创建索引。
3. 不要在线上环境随意创建索引,创建索引会锁表,线上数量⼤,会导致其他所有接⼝不可⽤,从⽽造成重⼤事故。
九、索引的分类
索引的分类有以下⼏种:
按照是否为主键:
主键索引(聚簇索引/聚集索引):⼀种特殊的唯⼀索引,不允许有空值,⼀般是在建表的时候同时创建主键索引(通过 primary key)。
⾮主键索引(非聚簇索引/非聚集索引/⼆级索引):除主键索引之外的其他索引。
按特征分类:
唯⼀索引:不能重复的索引,能保证唯一性
普通索引:可以重复也可以为 NULL 的索引。
联合索引:使⽤多个字段联合组成的索引。
注意:创建主键约束(PRIMARY KEY)、唯⼀约束(UNIQUE)、外键约束(FOREIGN KEY)时,会⾃动创建对应列的索引。
十、使用索引
1、查看索引
2.手动创建普通索引
注意:每个索引都会对应⼀个 B+ 树。
对于非主键、非唯一约束、非外键的字段,可以创建普通索引。
3.创建唯一索引
4.创建主键索引
5.删除索引
6.索引vs约束
索引和约束的区别:
①定义和作⽤不同:约束是保证数据的可靠性的;索引是加速查询的,不是⼀回事。
②可以相互创建:当创建了主键、外键、唯⼀约束也就创建了相应的索引;当创建了唯⼀索引,也就创建了唯⼀约束。
7.索引的注意事项