淮南微信网站建设,如何搭建网站,泰安房产网数据中心,营销型企业网站优点我们都知道MySQL在查询时底层会进行索引的优化#xff0c;假设有两个普通索引#xff0c;且where 后面也根据这两个普通索引查询数据#xff0c;那么执行查询语句时会使用到那个索引#xff1f; 为了方便演示#xff0c;新建users表#xff0c;新建idx_name、idx_city这两… 我们都知道MySQL在查询时底层会进行索引的优化假设有两个普通索引且where 后面也根据这两个普通索引查询数据那么执行查询语句时会使用到那个索引 为了方便演示新建users表新建idx_name、idx_city这两个普通索引如下
CREATE TABLE users (id INT PRIMARY KEY,name VARCHAR(50) ,age INT,city VARCHAR(50) ,INDEX idx_name (name),INDEX idx_city (city)
) DEFAULT CHARSETutf8mb4i;INSERT INTO users (id, name, age, city)
VALUES(1, 张三, 25, 北京),(2, 李四, 30, 上海),(3, 王五, 40, 广州),(4, 赵六, 35, 深圳),(5, 张三, 28, 上海);1根据单个索引查询
根据name 查询数据时如下图key idx_name ,即走了idx_name的索引
explain select * from users where name 张三;根据city查询数据时如下图key idx_city 即走了idx_city的索引 2根据多个普通索引查询
示例1
根据name和city查询并且name和city能够定位到一条数据
explain select * from users where name 张三 and city 上海;即使没有复合索引优化器也可以选择使用索引合并策略。它可以使用 idx_name 索引定位满足 name 张三 的行然后使用 idx_city 索引在之前的结果集上进行进一步筛选以满足 city 上海 的条件。
示例2
根据name和city查询并且单独查询name时name ‘张三’ 有两条记录单独查询city时city‘广州’ 有一条记录
explain select * from users where name 张三 and city 广州;此时优化器会走idx_city索引这是因为如果走idx_name索引要查询两次根据idx_city一次查询就能定位到具体的数据因此此处优化器采用idx_city作为索引。
同样执行如下SQL也是走的idx_city的索引因为city北京’的记录只有一条
explain select * from users where name 张三 and city 北京;再来看看下面的这个SQL语句会走那个索引呢
explain select * from users where name 李四 and city 上海;如上图当根据name 李四’查询出来数据只有一条、city上海’有两条数据最终结果走的是idx_name索引
示例3
explain select * from users where city 广州 and name 赵六;explain select * from users where name 赵六 and city 广州;上面两个SQL语句查询执行计划时发现两条语句的查询计划是一致的都是直接走idx_name索引不管where条件后面name和city的先后顺序 原因是如上图执行计划中possiblie_keys idx_name,idx_city。因为idx_name 先创建所以优化器会先判断是否走了idx_name索引name‘赵六’ 刚好检索出一条记录
实例4
explain select * from users where city 广州 and name 张三;这个时候走的是idx_city的索引不管where条件后面name和city的顺序。
案例5
explain select * from users where city 广州 and name 王五;
explain select * from users where name 王五 and city 广州 ;以上两个SQL都走了idx_name的索引和案例1有一些区别案例1中name ‘张三’ 或者 city 上海’都能查询多多行数据如果使用联合索引的话效率更高。案例5中由于根据idx_name就能把这一行的数据给定位到了因此采用idx_name索引就能满足。
以上都是MySQL优化器自动选择索引那如果我们想强制使用自己的索引可以使用 force index具体如下
查询name ‘张三’ 、city 广州’的数据我们通过查询计划得知走的是idx_city索引。
explain select * from users where name 张三 and city 广州;如果我们强制使用idx_name索引看看效果发现已经强制使用idx_name索引
