文章目录
1、什么是索引
索引在MySQL中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构,索引对于良好的性能非常关键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要
索引优化应该是对查询性能优化最有效的手段了,索引能够轻易将查询性能提高好几个数量级
索引相当于字典的音序表,如果要查某个字,如果不使用音序表,则需要从几百页中逐页去查
2、为何要有索引
一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境中,我们遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,因此对查询语句的优化显然是重中之重,说起加速查询,就不得不提到索引了
3、索引原理
索引的目的在于提高查询效率,与我们查阅图书所用的目录是一个道理: 先定位到章,然后定位到该章下的一个小节,然后找到页数,相似的例子还有: 查字典,查火车车次,飞机航班等
本质: 通过不断地缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是说,有了这种索引机制,我们可以总是用同一种查找方式来锁定数据
4、索引的数据结构
B+树是通过二叉查找树，再由平衡二叉树，B树演化而来
浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中
# b+树的查找过程
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高
# b+树性质
1、索引字段要尽量的小：通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
2、索引的最左匹配特性：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。
5、聚集索引
5.1、定义
InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接
如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引
如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引
由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多少情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询
5.2、聚集索引的好处
聚集索引的好处之一:
它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。如用户需要查找一张表，查询最后的10位用户信息，由于B+树索引是双向链表，所以用户可以快速找到最后一个数据页，并取出10条记录
mysql> desc s1; # 最开始没有主键
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | char(6) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.11 sec)
mysql> explain select * from s1 order by id desc limit 10; # Using filesort，需要二次排序
+----+-------------+-------+------------+------+---------------+------+---------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 292066 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+
1 row in set, 1 warning (0.00 sec)
mysql> alter table s1 add primary key(id); # 添加主键
Query OK, 0 rows affected (20.27 sec)
mysql> explain select * from s1 order by id desc limit 10; # 基于主键的聚集索引在创建完毕后就已经完成了排序，无需二次排序
+----+-------------+-------+------------+-------+---------------+---------+---------+---+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+---+
| 1 | SIMPLE | s1 | NULL | index | NULL | PRIMARY | 4 | NULL | 10 | 100.00 | Backward index scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+---+
1 row in set, 1 warning (0.00 sec)
聚集索引的好处之二:
范围查询(range query)，即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可
mysql> alter table s1 drop primary key;
Query OK, 293108 rows affected (31.45 sec)
mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | char(6) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.11 sec)
mysql> explain select * from s1 where id > 1 and id < 1000000; # 没有聚集索引，预估需要检索的rows数如下
+----+-------------+-------+------------+------+---------------+------+---------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+
| 1 | SIMPLE | s1 | NULL | ALL | NULL | NULL | NULL | NULL | 292352 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+
1 row in set, 1 warning (0.00 sec)
mysql> alter table s1 add primary key(id);
Query OK, 0 rows affected (17.12 sec)
mysql> explain select * from s1 where id > 1 and id < 1000000; # 有聚集索引，预估需要检索的rows数如下
+----+-------------+-------+------------+-------+---------------+---------+---------+---+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+---+
| 1 | SIMPLE | s1 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 146113 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+---+
1 row in set, 1 warning (0.12 sec)
6、辅助索引
表中除了聚集索引外其他索引都是辅助索引(Secondary Index，也称为非聚集索引)，与聚集索引的区别是: 辅助索引的叶子节点不包含行记录的全部数据
叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含一个书签(bookmark)该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据
由于InnoDB存储引擎是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得只想主键索引的主键，然后再通过主键索引来找到一个完整的行记录
举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问才能得到最终的一个数据页
7、MySQL索引管理
7.1、功能
1、索引的功能就是加速查找
2、mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能
7.2、MySQL常用的索引
普通索引INDEX: 加速查找
唯一索引:
-主键索引PRIMARY KEY: 加速查找+约束(不为空、不能重复)
-唯一索引UNIQUE: 加速查找+约束(不能重复)
联合索引:
-PRIMARY KEY(id,name): 联合主键索引
-UNIQUE(id,name): 联合唯一索引
-INDEX(id,name): 联合普通索引
# 举例说明
举个例子来说，比如你在为某商场做一个会员卡的系统
这个系统有一个会员表
有下列字段:
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT
那么这个 会员编号，作为主键，使用 PRIMARY
会员姓名 如果要建索引的话，那么就是普通的 INDEX
会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE(唯一的，不允许重复)
# 除此之外还有全文索引，即FULLTEXT
会员备注信息，如果需要建索引的话，可以选择全文搜索
用于搜索很长一篇文章的时候，效果最好
用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以
但其实对于全文搜索，我们并不会使用MySQL自带的该索引，而是会选择第三方软件如Sphinx，专门来做全文搜索
# 其他的如空间索引SPATIAL，几乎不用
7.3、索引的两大类型hash与btree
# 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引: 查询单条快，范围查询慢
btree类型的索引: b+树，层数越多，数据量指数级增长(我们就用它，因为innodb默认支持它)
# 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引
7.4、创建/删除索引的语法
# 方法一: 创建表时
CREATE TABLE 表名 (
字段名1 数据类型 [完整性约束条件…],
字段名2 数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
[索引名] (字段名[(长度)] [ASC |DESC])
);
# 方法二: CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (字段名[(长度)] [ASC |DESC]) ;
# 方法三: ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名 (字段名[(长度)] [ASC |DESC]) ;
# 删除索引: DROP INDEX 索引名 ON 表名字;
结果:
PS: 一个字段上可以创建多个索引. 索引的名字也是字段名加序号
查看:
show create table table-name
删除:
通常不做
通常主键上有自动增长, 不能直接删除主键, 需要先去掉自动增长特性
后期添加索引:
7.5、总结
1、一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引
2、在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快
比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中
建完以后，再查询就会很快了
3、需要注意的是: innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI
MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引
因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型
8、索引使用场景
8.1、数据准备
测试数据量: 800W左右
8.2、条件过滤 where
查询字段 first_name没有索引, 执行计划如下:
增加 查询字段 first_name 索引
一但有索引, 就可以被使用!
8.3、排序 order by|group by
利用某个字段排序, 检索:
在没有索引的情况下:
创建该排序字段的索引
存在索引的情况下:
8.4、关联 join
table1 join table2 ON table1.field = table2.field
table1.field , table2.field 需要在关联字段上建立索引
测试, 连接查询:
添加关联字段的索引
查询: 时间也很长, 但是比较而言, 索引,已经有效果!
实际中的limit的配合使用:
如果数据量大, 连接查询, 导致大量的结果, 以上的统计, 进一步的优化的可选方案: 逆范式
9、索引使用条件
9.1、字段独立原则
如果字段上存在索引, 要求使用, 字段必须要独立出现在表达式的一侧
测试, 主键索引的检索:
id-1 没有字段独立, 通过时间 没有用索引:
9.2、左原则
9.2.1、like不能以通配符开头
执行计划
like %keyword% 不能使用索引, 内容包含某个关键字的业务逻辑, 使用全文索引完成. mysql不支持中文的全文索引, 只能使用第三方的全文索引引擎实现
9.2.2、复合索引, 左边的字段才可以使用索引
A Z
B X
原因: 如果是复合索引, 先根据第一个字段排序, 如果相同, 再根据第二个字段做
左边字段:
右边字段:
除非, 左边的字段已经可以确认，first_name = XXX AND last_name=YYY的查询才可以
9.3、自动判断
MySQL会判断使用索引, 比未使用索引, 更消耗资源
主要原因:
大量的随机磁盘开销, 会大于 全表扫描的顺序开销
9.4、OR 全索引
如果条件出现了OR逻辑, 保证, 每个条件都需要存在可以使用索引才可以
first_name = ‘murry’ OR user=”ABCD%”
以上的查询 first_name 和 user 都有所以才可以
3252
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
