珠海斗门建设局网站,如何免费做网站,自助网站建设怎么建设,wordpress无法上传歌曲1.索引失效的情况
以tb_user表举例#xff0c;id为主键索引、name和phone字段上建立了一个普通索引#xff0c;name和phone均为varchar类型。 索引列运算 当在 WHERE 子句或 JOIN 子句中对列使用函数或表达式时#xff0c;索引会失效。 执行以下语句#xff0c;可以发现执…1.索引失效的情况
以tb_user表举例id为主键索引、name和phone字段上建立了一个普通索引name和phone均为varchar类型。 索引列运算 当在 WHERE 子句或 JOIN 子句中对列使用函数或表达式时索引会失效。 执行以下语句可以发现执行计划中索引已经生效。
explain select * from tb_user where name Jack;如果我们使用substring函数只取前三个字符则索引失效。
explain select * from tb_user where SUBSTRING(name, 1, 3) Jac;可以发现type为ALLkey为null说明本次查询没有执行索引走的是全表扫描。 隐式类型转换 当列的类型和查询中的值类型不同时MySQL 可能会进行隐式类型转换导致索引失效。 执行以下语句phone为varchar类型如果等号右侧不加引号则发生隐式转换索引失效。
explain select * from tb_user where phone 13016161546;前导通配符查询 使用通配符查询时如果通配符在字符串的前面索引会失效。 执行以下语句查询name字段后缀为ack的数据索引失效。
explain select * from tb_user where name like %ack;or连接条件 当 or 条件中某个列没有索引时索引会失效 执行以下语句因为name和phone都是索引字段索引正常生效。
explain select * from tb_user where name Jack or phone 15846234682;执行以下语句因为age字段没有设置索引所以索引失效查询。
explain select * from tb_user where name Jack or age 20;最左匹配原则 对于联合索引多个列组成的索引如果查询条件不包含索引的最左前缀部分索引会失效。 **TIPS**这里指的最左是联合索引中的顺序而不是SQL语句查询条件的顺序。 在本例中我们新建一个表table给字段col1、col2、age建立联合索引col1, col2, age 遵循最左匹配发展
按照最左前缀法则查询数据。
explain select * from table where col1 user and col2 password and age 21;可以发现联合索引的总长度为107 不遵循最左匹配法则查询条件中不包括联合索引的最左前缀部分
如果不按照最左匹配法则直接查询col2的数据
explain select * from table where col2 password;本次查询走的是index全索引扫描性能上要低于ref。 不遵循最左匹配法则查询条件中包含 范围查询
如果查询条件中使用了 则不遵循最左匹配法则可以使用其他范围查询符号范围查询右侧的索引失效。
执行以下语句由于age在联合索引col1, col2, age中是最后一个所以不存在其右侧索引失效的情况。
explain select * from table where col1 user and col2 password and age 21;但是如果我们将col2和age调换顺序改为col1, age, col2则col2索引失效。 数据分布情况 MySQL会根据表中数据的分布情况决定是否使用索引 举一个简单的例子如果表中的age字段最小值为10查询条件为age 10。则在查询时可能不会走索引因为走索引和不走索引都需要查询表中的全部数据不过判断一个语句是否走索引还是要根据explain关键字返回的结果进行判断。
2.回表查询
回表查询是指在使用辅助索引二级索引进行查询时由于辅助索引中不包含查询所需的所有列数据数据库必须通过索引找到对应的数据行位置再去实际的数据表即“回表”中读取完整的数据行。这种操作会增加额外的 I/O 开销因此回表查询通常比直接从索引中获取数据的查询更慢。
回表查询示例
假设有以下表数据id为主键索引name为普通索引。 主键索引id的索引结构如下图在叶子节点中存储的是每一行的数据。如果我们直接根据id查询就可以在遍历索引时直接拿到每一行的数据。
select * from tb_user where id 2;辅助索引name的索引结构如下叶子节点存储的是该行的主键id如果需要查询该行的数据则需要遍历索引后获得主键id再根据这个主键id前往主键索引中查询这个过程就是回表查询。
select * from tb_user where name Arm;避免回表查询
避免回表查询很简单只需要保证查询的列能够被索引结构覆盖即可。通过创建一个包含所有查询所需列的索引数据库可以直接从索引中获取所有需要的数据无需回表。
覆盖索引Covering Index是指查询所需的所有列都包含在同一个索引中从而避免回表操作。这样可以显著提高查询性能。
比如我们直接使用以下语句就可以避免回表查询因为name索引中包含了name和id的数据而无需回到数据库进行查询。
select name from tb_user where name Arm;select id, name from tb_user where name Arm;3.索引下推
索引下推Index Condition PushdownICP 是 MySQL 5.6 及以上版本中引入的一种优化技术用于提高使用索引查询的效率。ICP 可以减少回表操作即从索引表跳回数据表读取完整行数据的次数从而提高查询性能。
除了可以减少回表次数之外索引下推还可以减少存储引擎层和 Server 层的数据传输量。
工作原理
在没有索引下推的情况下MySQL 的查询执行流程通常是
索引扫描存储引擎使用索引查找满足索引条件的记录。返回记录将这些记录返回给 MySQL 服务器。行过滤MySQL 服务器根据剩余的查询条件进一步过滤这些记录。
使用索引下推后MySQL 优化器会在索引扫描阶段尽可能多地应用查询条件只有在通过索引扫描无法完全过滤的情况下才进行回表操作。
适用场景
索引下推在以下场景中尤其有效
范围查询对索引列进行范围查询时例如 BETWEEN、、 等。联合索引查询在联合索引的前缀列上进行查询并且查询条件涉及非索引列时。复杂条件查询查询条件包含多个过滤条件时例如 AND、OR 等。
示例
假设有一个包含联合索引 idx_name_age 的表 tb_user
CREATE TABLE tb_user (id INT PRIMARY KEY,name VARCHAR(50),age INT,address VARCHAR(255),INDEX idx_name_age (name, age)
);查询语句
explain select * from tb_user where name John and age 30 and address like %Street%;在没有索引下推的情况下MySQL 会
使用索引 idx_name_age 找到 name John 的所有记录。回表读取每一条记录的实际数据。对回表后的数据应用剩余条件 age 30 和 address LIKE %Street% 进行过滤。
在启用索引下推的情况下MySQL 会
使用索引 idx_name_age 找到 name John 且 age 30 的记录在索引扫描阶段应用部分条件。仅对符合前两个条件的记录进行回表操作。对回表后的数据应用剩余条件 address LIKE %Street% 进行最终过滤。 据应用剩余条件 age 30 和 address LIKE %Street% 进行过滤。
在启用索引下推的情况下MySQL 会
使用索引 idx_name_age 找到 name John 且 age 30 的记录在索引扫描阶段应用部分条件。仅对符合前两个条件的记录进行回表操作。对回表后的数据应用剩余条件 address LIKE %Street% 进行最终过滤。
