徐州做网站的哪个好,深圳网站建设行吗,开发工具箱,电子商务网站分析目录索引的优缺点索引类型聚簇索引#xff08;主键索引#xff09;非聚簇索引#xff08;二级索引、辅助索引#xff09;索引匹配的原则最左匹配原则无法使用索引的场景索引创建的原则使不使用索引的依据到底是什么#xff1f;参考索引的优缺点
常见的索引结构有: B 树主键索引非聚簇索引二级索引、辅助索引索引匹配的原则最左匹配原则无法使用索引的场景索引创建的原则使不使用索引的依据到底是什么参考索引的优缺点
常见的索引结构有: B 树 B树和 Hash。
优点加快数据的检索速度缺点 创建索引和维护索引需要耗费许多时间当对表中的数据进行增删改的时候如果数据有索引那么索引也需要动态的修改会降低 SQL 执行效率。占用物理存储空间 索引需要使用物理文件存储也会耗费一定空间。
索引类型
聚簇索引主键索引
聚簇索引即索引结构和数据一起存放的索引结构为B树。 InnoDB中表数据文件本身就是按BTree组织的一个索引结构聚簇索引就是按照每张表的主键构造一颗B树同时叶子节点中存放的就是整张表的行记录数据也将聚集索引的叶子节点称为数据页。
聚簇索引结构如下 非聚簇索引二级索引、辅助索引
在聚簇索引之上创建的索引称之为辅助索引二次索引辅助索引节点只包含索引列值和主键值因此辅助索引访问数据总是需要二次查找即通过二次索引找到主键值后回到聚簇索引找到对应的数据行。 辅助索引叶子节点存储的不再是行的物理位置而是主键值。通过辅助索引首先找到的是主键值再通过主键值找到数据行的数据页再通过数据页中的Page Directory找到数据行。 Innodb辅助索引非叶子节点只有索引列不含主键叶子节点并不包含行记录的全部数据叶子节点只包含索引列和相应行数据的聚簇索引键。
Innodb二级索引索引列值全相同的情况下节点按主键值排序。
二级索引结构如下
索引匹配的原则
最左匹配原则
如果创建一个联合索引, 此索引的任何前缀都会用于查询, 例如 (col1, col2, col3)这个联合索引的所有前缀就是(col1), (col1, col2), (col1, col2, col3), 包含这些列的查询都会启用索引查询. 其他所有不在最左前缀里的列都不会启用索引, 即使包含了联合索引里的部分列也不会使用索引. 即上述中的(col2), (col3), (col2, col3) 都不会启用索引去查询. 注意(col1, col3)会启用(col1)的索引查询。
无法使用索引的场景
复合索引的情况下查询条件不满足索引最左的原则Mysql估计使用索引比全表扫描慢索引 不能是表达式函数的一部分 只有独立的列能使用索引如SELECT * FROM table WHERE id -1 1000; 无法使用索引 因为索引 id-1 不为独立的列负向查询not , not in, not like, , ! ,!,! 不会使用索引以%开头的LIKE查询不能够利用B-tree索引用or分割开的条件or前条件有索引or后的列没有索引 因为or后面的条件没有索引那么后面的查询肯定要进行全表扫描在存在全表扫描的情况下就没有必要多一次索引扫描增加IO访问。
索引创建的原则
不要使用更新频繁的列作为主键不适用多列主键相当于联合索引不要使用 UUID,MD5,HASH,字符串列作为主键无法保证数据的顺序增长容易导致页分裂及随机IO影响插入的速度。推荐使用自增值作为主键。索引列的顺序 区分度最高的放在联合索引的最左侧区分度列中不同值的数量/列的总行数尽量把字段长度小的列放在联合索引的最左侧因为字段长度越小一页能存储的数据量越大IO 性能也就越好使用最频繁的列放到联合索引的左侧这样可以比较少的建立一些索引 避免建立冗余索引和重复索引增加了查询优化器生成执行计划的时间 冗余索引示例index(a,b,c)、index(a,b)、index(a)对于频繁的查询优先考虑使用覆盖索引 覆盖索引就是包含了所有查询字段 (where,select,ordery by,group by 包含的字段) 的索引 覆盖索引的好处 避免 Innodb 表进行索引的二次查询: Innodb 是以聚集索引的顺序来存储的对于 Innodb 来说二级索引在叶子节点中所保存的是行的主键信息如果是用二级索引查询数据的话在查找到相应的键值后还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中二级索引的键值中可以获取所有的数据避免了对主键的二次查询 减少了 IO 操作提升了查询效率。 可以把随机 IO 变成顺序 IO 加快查询效率: 由于覆盖索引是按键值的顺序存储的对于 IO 密集型的范围查找来说对比随机从磁盘读取每一行的数据 IO 要少的多因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。 使不使用索引的依据到底是什么
在满足了使用索引的条件下是否使用索引取决于使用索引的成本。
此段摘抄自MySQL中IS NULL、IS NOT NULL、!不能用索引胡扯 MySQL中决定使不使用某个索引执行查询的依据很简单就是成本够不够小。而不是是否在WHERE子句中用了IS NULL、IS NOT NULL、!这些条件。 答案很简单成本。对于使用二级索引进行查询来说成本组成主要有两个方面 1. 读取二级索引记录的成本 2. 将二级索引记录执行回表操作也就是到聚簇索引中找到完整的用户记录的操作所付出的成本。
查询列不在二级索引时要扫描的二级索引记录条数越多那么需要执行的回表操作的次数也就越多达到了某个比例时使用二级索引执行查询的成本也就超过了全表扫描的成本举一个极端的例子比方说要扫描的全部的二级索引记录那就要对每条记录执行一遍回表操作自然不如直接扫描聚簇索引来的快。
参考
什么是索引?官方文档解释MySQL最左匹配(最左前缀)原则MySQL中IS NULL、IS NOT NULL、!不能用索引胡扯MySQL优化指南mysql 存在索引但不能使用索引的典型场景聚簇索引和非聚簇索引(通俗易懂 言简意赅)
