关于MySQL索引及InnoDB与MyISAM引擎的区别的学习笔记

最新推荐文章于 2024-10-02 12:42:27 发布

Jahree

最新推荐文章于 2024-10-02 12:42:27 发布

阅读量102

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_44504402/article/details/104822175

版权

1.索引

·对表中一列或多列的值进行排序
·定义一种存储结构
·快速检索到数据
·存储引擎级实现，不同存储引擎实现索引的机制是不一样的

2.索引类型

·普通索引：基本索引类型，没什么限制，允许重复值和空值
·唯一索引：索引列的值必须是唯一的，但允许空值
·主键索引：不允许有空值的唯一索引
·组合索引：多个字段组合上创建索引，最左原则，只有查询条件中使用了这些字段的最左边字段shift，索引才会被使用。（根据常用where语句设置）
·全文索引、空间索引

·非聚簇索引：索引树的叶子节点存储数据的位置信息 MyISAM
·聚簇索引：数据存储在索引树的叶子节点 InnoDB
·覆盖索引：查询直接使用索引就可以完成，既索引包含查询语句中的字段与条件的数据

注意事项

·每在数据库中插入一条数据，数据库也会自动添加一条索引
·执行查询的时候，MySQL只能使用一个索引
·不要使用like '%aaa%'操作，这种没法使用最左原则，不会匹配到索引，只能遍历全库
·存储越小的数据类型通常更好
·尽量避免Null（Null也是需要占内存的）
·最左前缀：组合索引中的左边列

InnoDB 及 MyISAM 的索引结构

·MyISAM：
使用B+树作为索引，叶子节点存放的是数据的地址。
数据具体的存放位置没有顺序。
主键索引和辅助索引都是一样的，主索引要求key不能重复，辅索引key可以重复。

·InnoDB：
引擎数据文件本身就是一个索引
叶子节点包含全部数据信息，（使用主键存储数据，主键索引和数据是存在一起的，他们是同一个文件
辅助索引叶子节点存放的是主键和key值–所以InnoDB的普通索引，实际有可能会扫描两遍
第一遍由普通索引找到主键
第二遍由主键找到行记录
或者
第一遍由普通索引查找到信息就是要找的，那就只找一遍，这就是覆盖索引

这边有个疑问：
难道说，InnoDB中的普通索引，和主键索引根本不是同一棵树？普通索引在自身树下查找到key值和主键值，然后再拿着主键值去主键索引的树中找具体数据？----每个索引都自己的索引文件！

举例说明InnoDB的主键索引和普通索引过程：

CREATE TABLE tb(
	id INT ( 10 ) NOT NULL auto_increment,
	age INT ( 10 ) NOT NULL,
	name VARCHAR ( 32 ) NOT NULL,
	PRIMARY KEY ( id ),
  KEY idx_name ( name ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8

select * from tb
+----+-----+-------+
| id | age | name  |
+----+-----+-------+
| 15 | 34  | Bob   |
| 18 | 77  | Alice |
| 20 | 5   | Jim   |
| 30 | 91  | Eric  |
| 49 | 22  | Tom   |
| 50 | 89  | Rose  |
+----+-----+-------+