MYSQL学习笔记：索引

MYSQL学习笔记：索引

• 用索引也是要涉及磁盘I/O的操作的
• 索引也是一种数据结构，对某列建索引的时候都要进行一个排序的，这一列的文件有增加减少修改都是要涉及索引数据的改动操作的

索引的分类

物理上分为聚集索引和非聚集索引

• 普通索引：数量不限，可以给任意普通字段创建普通索引，一张表的SQL查询只能用一个索引
• 唯一性索引：使用UNIQUE修饰的字段，值不能重复，主键索引隶属于唯一性索引
• 主键索引：使用PRIMARY KEY修饰的字段，会自动创建索引（MYISAM,innoDB（如果没设主键，INNODB会自动创建，因为数据和索引存在一起，没有索引没办法存储数据）)
• • 单列索引 在一个字段上创建索引
• 多列索引： 在表的多个字段上创建索引（uid+cid，多列索引必须使用第一个列才能用到多列索引，否则用不到）
• 全文索引：使用FULLTEX修饰的字段可以设置全文索引，支支持char varchar和text类型字段，常用于较大的字符串类型上，可以提高查询速度

索引的创建删除

方法1：在建表的时候建立索引

CREATE TABLE index1(id INT,
name varchar(20),
sex enum ('male','famale'),
INDEX(id,name);

方法2：在已创建的表上创建索引

CREATE [UNIQUE] INDEX 索引名 ON 表名(属性名（length）[ASC|DESC]);
create index pwdindex on user(age);

删除索引：

DROP INDEX 索引名 ON 表名；

加索引后只扫一行：不管数据规模如何，花费的时间一样长

索引优化

1. 给经常用作where过滤条件的字段加索引
2. 给字符串创建索引索引值尽可能短，不然存储文件大，加载慢
3. 如果过滤条件涉及类型转换或者用了mysql的函数调用表达式计算，就不能用索引了。
   
   password是字符串类型，10000是默认INT类型，如果涉及类型转换或者用了mysql的函数就不能用索引了

B树索引

如果用m=500（一般取300~500）阶B树最多用----》3层：

最理想的情况是一次磁盘IO读取的磁盘块内容刚好存储在B树的一个节点中

B+树

为啥用B+树而不是B树
首先看B树的特点：

1. 索引和数据分散在不同的节点上，离根节点近搜索快，离根节点远搜索就慢，花费的IO不平均，每一行搜索花费的时间也不平均
2. 每一个非叶子节点上不仅仅要存储索引（key) 还要存索引所在哪一行的data数据。一个节点能存放索引的key的个数比只存储key的节点少得多
3. B树很不方便进行范围搜索（例如搜索13~18的数据，既要搜索左子树也要搜索右子树），整表遍历看起来不方便

非叶子节点只存储key不存储data，每个节点存储的key个数更多，理论上来讲层数更低，搜索效率更高
叶子节点上存储了所有的key值和对应的data，搜索每个节点最后都要到叶子节点上。每一行搜索时间非常平均
叶子节点被串在了一个链表当中形成了一个有序链表，如果对所引树进行搜索只需要遍历有序链表即可。进行范围查询的时候，也可以直接遍历有序链表，效率更高

InnoDB主键和二级索引树

二级索引树


这也是为啥多列所引想要被用到必须用到第一列

聚集索引与非聚集索引

myisam
非聚集索引：

主键索引：

INNODB辅助索引与主键索引：

反之，innoDB的那种数据索引不分离的方式就算聚集索引

哈希索引

hash索引就是一个链式哈希表

特点：

INNODB的自适应哈希索引

原来是先找到二级索引，然后在通过二级索引找到主键再搜索得到数据页
现在通过一个hash索引直接找到数据页

自适应hash索引的维护也是要耗费性能的，不是所有情况下都可以提升二级索引的查询性能

索引学习漫画链接

索引和慢查询

如何看更精确的时间：