网站开发账务处理,wordpress不用php,皮肤病在线咨询医生免费咨询,新沂市网站建设这里写目录标题 索引底层的数据结构#xff1a;B树B树与B树的区别InnoDB与MyISAM在B树使用索引结构的不同#xff1f; 聚簇索引非聚簇索引联合索引 B树索引适用的条件查询全值匹配匹配左边的列匹配列前缀匹配范围的值精确匹配某一列并范围匹配另外一列避免使用隐式转换 排序必… 这里写目录标题 索引底层的数据结构B树B树与B树的区别InnoDB与MyISAM在B树使用索引结构的不同 聚簇索引非聚簇索引联合索引 B树索引适用的条件查询全值匹配匹配左边的列匹配列前缀匹配范围的值精确匹配某一列并范围匹配另外一列避免使用隐式转换 排序必须按照索引列的顺序可以只使用索引列中左边 的列不能ASC、DESC混用WHERE 子句中不能出现非排序使用到的索引列排序列不能包含非同一个索引的列排序列不能使用了复杂的表达式 分组 回表回表较少的数据列覆盖索引索引下推 如何挑选索引**只为用于搜索、排序或分组的列创建索引**基数大的列建立索引索引列的类型尽量小**考虑使用字符列前缀****索引列在比较表达式中单独出现****避免冗余和重复索引**主键索引拥有 AUTO_INCREMENT 属性 最左匹配原则相关阅读 本篇以MySQL中的InnoDB引擎为主 建立索引的出发点该怎么建索引储存的数据结构
索引底层的数据结构B树
B树与B树的区别
在叶子节点的存储上B树存储的是Key和data。而B树任务节点都可以存放Key和Data。B树的叶子节点的引用链指向相邻的叶子结点节点也就是双向链表哦页内的记录是单链表哦而B树的叶子节点都是孤单节点。在范围查询时B树只需要对于叶子结点进行遍历而 B 树中进行范围查询时首先找到要查找的下限然后对 B 树进行中序遍历直到找到查找的上限
InnoDB与MyISAM在B树使用索引结构的不同
在MyISAM中B树的data域中存储的是 主键索引 数据记录的地址行号。在索引检索的时候首先检查 索引文件中的 叶子结点存储 主键值 行号如果指定的 Key 存在则根据行号取出 数据文件 相应的记录按照插入顺序。即在MyISAM中索引文件跟数据文件是分离的 在InnoDB中索引即数据也就是聚簇索引的那棵B树的叶子结点存储了所有完整的用户记录。而在其他二级索引时候跟MyISAM实现的类似B树的叶子节点存储的是二级索引列 主键。
聚簇索引
在InnoDB 中聚簇索引就是主键索引。 聚簇索引的特性
使用 记录数据列的主键值的大小进行记录排序和页排序B 树的叶子节点存储的是完整的用户记录目录项是 主键页号 的搭配。
InnoDB 存储引擎会自动的为我们创建聚簇索引。 在 InnoDB 存储引擎中 聚簇索引 就是数据的存储方式所有的用户记录都存储在了 叶子节点 也就是所谓的索引即数据数据即索引。
非聚簇索引
非聚簇索引又称为二级索引辅助索引。 非聚簇索引的特性
使用记录的二级索引列进行记录排序和页排序B 树的叶子节点存储的是 记录的二级索引列 主键目录项变成了记录的二级索引列 页号 的搭配。
也就是说我们还需要进行一次回表操作。
联合索引
同时为多个列建立索引也就是以同时以多个列的大小作为排序规则 根据非聚簇索引的特性我们很容易的得出 假定为 c2 和 c3 列建立的索引
每条 目录项记录 都由 c2 、 c3 、 页号 这三个部分组成各条记录先按照 c2 列的值进行排序如果记录 的 c2 列相同则按照 c3 列的值进行排序。B 树叶子节点处的用户记录由 c2 、 c3 和主键 c1 列组成。
B树索引适用的条件
CREATE TABLE person_info(id INT NOT NULL auto_increment,name VARCHAR(100) NOT NULL,birthday DATE NOT NULL,phone_number CHAR(11) NOT NULL,country varchar(100) NOT NULL,PRIMARY KEY (id),KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);创建了两颗B树聚簇索引 id以及 非聚簇索引 idx_name_birthday_phone_number其中idx_name_birthday_phone_number 索引对应的 B 树中页面和记录的排序方式就是 先按照 name 列的值进行排序。如果 name 列的值相同则按照 birthday 列的值进行排序。如果 birthday 列的值也相同则按照 phone_number 的值进行排序。
查询
全值匹配
搜索列与索引列完全一致。
SELECT *
FROM person_info
WHERE name Ashburn AND birthday 1990-09-27 AND phone_number 15123983239;
-- 搜索条件的顺序对查询的执行过程没有影响
SELECT *
FROM person_info
WHERE birthday 1990-09-27 AND phone_number 15123983239 AND name Ashburn;匹配左边的列
搜索列只匹配左边的索引列或包含多个左边的列。 如果我们想使用联合索引中尽可能多的列搜索条件中的各个列必须是联合索引中 从最左边连续的列
SELECT * FROM person_info
WHERE name Ashburn;SELECT * FROM person_info
WHERE name Ashburn AND birthday 1990-09-27;下面是个不走索引的例子是因为 根据B树组合索引的建立规则name 列的值不同的记录中 birthday 的值可能是无序的。
SELECT *
FROM person_info
WHERE birthday 1990-09-27;匹配列前缀
对于字符串类型的索引列来说我们只匹配 它的前缀也是可以快速定位记录的
SELECT *
FROM person_info
WHERE name LIKE As%;下面是个不走索引的例子如果只给出后缀或者中间的某个字符串
SELECT *
FROM person_info
WHERE name LIKE %As%;匹配范围的值
如果对多个列同时进行范围查找的话只有对索引最左边的那个 列进行范围查找的时候才能用到 B 树索引
SELECT *
FROM person_info
WHERE name Asa AND name Barlow;对于联合索引 idx_name_birthday_phone_number 来说只能用到 name 列的部分而用不到 birthday 列 的部分因为只有 name 值相同的情况下才能用 birthday 列的值进行排序而这个查询中通过 name 进行范围查 找的记录中可能并不是按照 birthday 列进行排序的所以在搜索条件中继续以 birthday 列进行查找时是用不到 这个 B 树索引的
SELECT *
FROM person_info
WHERE name Asa AND name Barlow AND birthday 1980-01-01;精确匹配某一列并范围匹配另外一列
对于同一个联合索引来说虽然对多个列都进行范围查找时只能用到最左边那个索引列但是如果左边的列是精 确查找则右边的列可以进行范围查找。
SELECT *
FROM person_info
WHERE
name AshburnAND
birthday 1980-01-01 AND birthday 2000-12-31 AND
phone_number 15100000000;name ‘Ashburn’ 和 birthday ‘1980-01-01’ AND birthday ‘2000-12-31’ 肯定能够用到索引。 对于 phone_number ‘15100000000’ 通过 birthday 的范围查找的记录的 birthday 的值可能不同所以这个 条件无法再利用 B 树索引了只能遍历上一步查询得到的记录。 即索引下推哦
SELECT *
FROM person_info
WHERE name Ashburn AND birthday 1980-01-01 AND AND phone_number 15100000000;避免使用隐式转换
MySQL性能优化MySQL中的隐式转换造成的索引失效 - 夜月归途 通过上面的测试我们发现 MySQL 使用操作符的一些特性
当操作符左右两边的数据类型不一致时会发生隐式转换。当 where 查询操作符左边为数值类型时发生了隐式转换那么对效率影响不大但还是不推荐这么做。当 where 查询操作符左边为字符类型时发生了隐式转换那么会导致索引失效造成全表扫描效率极低。字符串转换为数值类型时非数字开头的字符串会转化为0以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。
排序
索引列用于排序语句。
必须按照索引列的顺序
可以只使用索引列中左边 的列
对于 联合索引 有个问题需要注意 ORDER BY 的子句后边的列的顺序也必须按照索引列的顺序给出或只使用索引列中左边 的列
SELECT *
FROM person_info
WHERE name A
ORDER BY birthday, phone_number LIMIT 10;这个索引使用联合索引进行排序是因为 name 列的值相同的记录是按照 birthday , phone_number 排序的
不能ASC、DESC混用
SELECT *
FROM person_info
ORDER BY name, birthday DESC
LIMIT 10;这个不会使用索引排序因为如果使用索引排序的话过程就是这样的
先从索引的最左边确定 name 列最小的值然后找到 name 列等于该值的所有记录然后从 name 列等于该值 的最右边的那条记录开始往左找10条记录。如果 name 列等于最小的值的记录不足10条再继续往右找 name 值第二小的记录重复上边那个过程直 到找到10条记录为止。
WHERE 子句中不能出现非排序使用到的索引列
SELECT *
FROM person_info
WHERE country China
ORDER BY name
LIMIT 10;这个查询只能先把符合搜索条件 country ‘China’ 的记录提取出来后再进行排序是使用不到索引
排序列不能包含非同一个索引的列
SELECT *
FROM person_info
ORDER BY name, country
LIMIT 10;name 和 country 并不属于一个联合索引中的列所以无法使用索引进行排序
排序列不能使用了复杂的表达式
索引进行排序操作必须保证索引列是以单独列的形式出现而不是修饰过的形式
SELECT *
FROM person_info
ORDER BY UPPER(name)
LIMIT 10;使用了 UPPER 函数修饰过的列就不是单独的列啦这样就无法使用索引进行排序啦。
分组
使用 B 树索引进行排序是一个道理分组列的顺序也需要和索引列的顺序一致也可以只使用索引列中左边 的列进行分组
SELECT name, birthday, phone_number, COUNT(*)
FROM person_info
GROUP BYname, birthday, phone_number回表
用 idx_name_birthday_phone_number 索引为例下面的这个查询就会用到回表
SELECT *
FROM person_info
WHERE name Asa AND name Barlow;从索引 idx_name_birthday_phone_number 对应的 B 树中取出 name 值在 Asa Barlow 之间的用户记录。由于索引idx_name_birthday_phone_number 对应的 B 树用户记录中只包含 name 、 birthday 、 phone_number 、 id 这4个字段而查询列表是 * 意味着要查询表中所有字段也就是还要包括 country 字段。这时需要把从上一步中获取到的每一条记录的 id 字段都到聚簇索引对应的 B 树中找到完整的用户记 录也就是我们通常所说的 回表 然后把完整的用户记录返回给查询用户。
因此需要回表的记录越多使用二级索引的性能就越低甚至让某些查询宁愿使用全表扫描也不使用 二级索引 。
回表较少的数据列
减少回表的次数可以查询获取较少的记录数会让优化器更倾向于选择使用 二级索引 回表
SELECT *
FROM person_info
WHERE name Asa AND name Barlow
LIMIT 10;覆盖索引
为了彻底告别 回表 操作带来的性能损耗最好在查询列表里只包含索引列 比如这样
SELECT name, birthday, phone_number
FROM person_info
WHERE name Asa AND name Barlow索引下推
索引条件下推Index Condition PushdownICP就是过滤的动作由下层的存储引擎层通过使用索引来完成而不需要上推到Server层进行处理。 例如对于联合索引 idx_name_birthday_phone_number 来说:
SELECT *
FROM person_info
WHERE name Asa AND name Barlow AND birthday 1980-01-01;对于上述的查询语句
InnoDB使用联合索引查出所有 Asa name Barlow 的二级索引数据假设得到得到10w个主键值拿到主键索引进行回表到聚簇索引中拿到这10w个完整的用户记录InnoDB把这10w条完整的用户记录返回给MySQL的Server层在Server层过滤出 birthday 1980-01-01的唯一的用户。
而使用了索引下推 我们第一步已经通过联合索引查出所有 Asa name Barlow 的10w个数据而且birthday字段也恰好在联合索引的叶子节点的记录中。这个时候可以直接在联合索引的叶子节点中进行遍历筛选出birthday 1980-01-01记录找到唯一的那条记录最后只需要进行1次回表操作即可找到符合全部条件的1条记录返回给Server层。
如何挑选索引
只为用于搜索、排序或分组的列创建索引
SELECT birthday, country
FROM person name
WHERE name Ashburn;像查询列表中的 birthday 、 country 这两个列就不需要建立索引我们只需要为出现在 WHERE 子句中的 name 列创建索引就可以了。
基数大的列建立索引
列的基数 指的是某一列中不重复数据的个数比方说某个列包含值 2, 5, 8, 2, 5, 8, 2, 5, 8 虽然有 9 条 记录但该列的基数却是 3 。 最好为那些列的基数大的列建立索引为基数 太小列的建立索引效果可能不好。
索引列的类型尽量小
数据类型越小在查询时进行的比较操作越快这是CPU层次的东东数据类型越小索引占用的存储空间就越少在一个数据页内就可以放下更多的记录从而减少磁盘 I/O 带 来的性能损耗也就意味着可以把更多的数据页缓存在内存中从而加快读写效率。
考虑使用字符列前缀
二级索引的记录中只保留字符串前几个字符。 这样在查找记录时虽然不能精确的定位 到记录的位置但是能定位到相应前缀所在的位置然后根据前缀相同的记录的主键值回表查询完整的字符串 值再对比就好了。 这样只在 B 树中存储字符串的前几个字符的编码既节约空间又减少了字符串的比较时 间还大概能解决排序的问题
CREATE TABLE person_info(name VARCHAR(100) NOT NULL,birthday DATE NOT NULL,phone_number CHAR(11) NOT NULL,country varchar(100) NOT NULL,KEY idx_name_birthday_phone_number (name(10), birthday, phone_number)
);name(10) 就表示在建立的 B 树索引中只保留记录的前 10 个字符的编码
索引列在比较表达式中单独出现
也就是索引列进行做了其他操作例如数值计算、使用函数、手动或自动类型转换等操作会导致索引失效。
1. WHERE my_col * 2 4 ×
2. WHERE my_col 4/2 √避免冗余和重复索引
冗余索引指的是索引的功能相同能够命中索引(a, b)就肯定能命中索引(a) 那么索引(a)就是冗余索引。
主键索引拥有 AUTO_INCREMENT 属性
尽可能少的让 聚簇索引 发生页面分裂和记录移位的情况
最左匹配原则
从上面就可以得出来最左匹配原则的定义咯 在使用组合索引时
搜索列必须全部为索引列搜索列可以为部分索引列但是部分索引列必须满足顺序性搜索列的范围查询搜索列的最左边的列才会用到索引排序条件、分组条件与搜索列一致要么全部为索引列要么部分索引列必须满足顺序性
相关阅读
联合索引的最左匹配原则__https://mp.weixin.qq.com/s/8qemhRg5MgXs1So5YCv0fQ 图解索引覆盖、索引下推以及如何避免索引失效https://zhuanlan.zhihu.com/p/481750465 MySQL是怎样运行的https://book.douban.com/subject/35231266/
