Mysql 之最左原则

什么是聚集索引和非聚集索引

mysql的底层使用b+树来存储缩印的 且数据都存在叶子节点 对于Innodb来说 他的键索引和行记录都是存储在一起的 因此叫做聚集索引

MyISAM 的行记录是单独存储的，不和索引在一起，因此 MyISAM也就没有聚集索引。

除了聚集索引，其它索引都叫做非聚集索引(secondary index)。包括普通索引，唯一索引等

在 InnoDB 中有且只有一个聚集索引。它有三种情况：

1. 若表存在主键，则主键索引就是聚集索引。

2. 若不存在主键，则会把第一个非空的唯一索引作为聚集索引。

3. 否则，就会隐式的定义一个 rowid 作为聚集索引。

   创建一张表，结构如下，并添加几条记录(张三，李四，王五，孙七)：

                   CREATE TABLE `student` (
     `id` int(11) NOT NULL,
     `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
     `age` int(11) DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `idx_stu` (`name`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
   
   insert into student(id,name,age) values(1,‘zs‘,12);
   insert into student(id,name,age) values(5,‘ls‘,14);
   insert into student(id,name,age) values(9,‘ww‘,12);
   insert into student(id,name,age) values(11,‘sq‘,13);
   
               

   在Innodb中 主键缩印的叶子节点存储的是主键和行记录 而普通索引的叶子节点储存的主键

   ps:对于myisam来说主键的叶子节点储存的是主键和对应行记录指针 普通索引的叶子节点储存的是当前的索引列和对应的行记录的指针 这也是myisam查询速度快的原因

   因此，id 为聚集索引，name 为非聚集索引。它们对应的 B+ 树结构如下图所示
   

什么是回表查询

就拿上的例子来说

如果我们查询的时候 走的是主键索引 也就是聚集索引 那么就会走聚集索引树 直接可以查到相对应的行记录

对于普通索引，则是先寻找对应的主键然后再用主键在聚集索引树种寻找对应的行记录 而这个过程就称为回表

        select * from student where name = ‘zs‘ 
1.首先通过非聚集索引查找到主键 1
2.然后通聚集索引 就是主键 查找相对应的行数据


    

查询过程：

什么是索引覆盖

? 索引覆盖的作用就是通过减少回表操作 来提高查询效率

? 而通过创建联合索引来生成索引覆盖

eg:key(‘name‘,‘age‘)

        create index  idx_name_age on tableName (age,name) 

    

上一个语句的优化

        

SELECT * id,name,age from student where name=‘zs‘ and age=12

    

最左原则

若建立一个联合索引为(a,b,c);

所谓的最左原则就是 在进行联合索引查询的时候 优先走最左边上的索引 就相当于创建了 a单列索引 （a,b）联合索引 （a,b,c）联合索引

实战

        create table `tb_seller` (
	`sellerid` varchar (100),
	`name` varchar (100),
	`nickname` varchar (50),
	`password` varchar (60),
	`status` varchar (1),
	`address` varchar (100),
	`createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4; 

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values(‘alibaba‘,‘阿里巴巴‘,‘阿里小店‘,‘e10adc3949ba59abbe56e057f20f883e‘,‘1‘,‘北京市‘,‘2088-01-01 12:00:00‘);
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values(‘baidu‘,‘百度科技有限公司‘,‘百度小店‘,‘e10adc3949ba59abbe56e057f20f883e‘,‘1‘,‘北京市‘,‘2088-01-01 12:00:00‘);
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values(‘huawei‘,‘华为科技有限公司‘,‘华为小店‘,‘e10adc3949ba59abbe56e057f20f883e‘,‘0‘,‘北京市‘,‘2088-01-01 12:00:00‘);
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values(‘itcast‘,‘传智播客教育科技有限公司‘,‘传智播客‘,‘e10adc3949ba59abbe56e057f20f883e‘,‘1‘,‘北京市‘,‘2088-01-01 12:00:00‘);
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values(‘itheima‘,‘黑马程序员‘,‘黑马程序员‘,‘e10adc3949ba59abbe56e057f20f883e‘,‘0‘,‘北京市‘,‘2088-01-01 12:00:00‘);
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values(‘luoji‘,‘罗技科技有限公司‘,‘罗技小店‘,‘e10adc3949ba59abbe56e057f20f883e‘,‘1‘,‘北京市‘,‘2088-01-01 12:00:00‘);
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values(‘oppo‘,‘OPPO科技有限公司‘,‘OPPO官方旗舰店‘,‘e10adc3949ba59abbe56e057f20f883e‘,‘0‘,‘北京市‘,‘2088-01-01 12:00:00‘);
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values(‘ourpalm‘,‘掌趣科技股份有限公司‘,‘掌趣小店‘,‘e10adc3949ba59abbe56e057f20f883e‘,‘1‘,‘北京市‘,‘2088-01-01 12:00:00‘);
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values(‘qiandu‘,‘千度科技‘,‘千度小店‘,‘e10adc3949ba59abbe56e057f20f883e‘,‘2‘,‘北京市‘,‘2088-01-01 12:00:00‘);
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values(‘sina‘,‘新浪科技有限公司‘,‘新浪官方旗舰店‘,‘e10adc3949ba59abbe56e057f20f883e‘,‘1‘,‘北京市‘,‘2088-01-01 12:00:00‘);
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values(‘xiaomi‘,‘小米科技‘,‘小米官方旗舰店‘,‘e10adc3949ba59abbe56e057f20f883e‘,‘1‘,‘西安市‘,‘2088-01-01 12:00:00‘);
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values(‘yijia‘,‘宜家家居‘,‘宜家家居旗舰店‘,‘e10adc3949ba59abbe56e057f20f883e‘,‘1‘,‘北京市‘,‘2088-01-01 12:00:00‘);


create index idx_seller_name_sta_addr on tb_seller(name,status,address);

    

        #都符合最左原則
explain select * from tb_seller where name=‘小米科技‘ and status=‘1‘ and address=‘北京市‘
explain select * from tb_seller where name=‘小米科技‘ and status=‘1‘ 
explain select * from tb_seller where name=‘小米科技‘ 

    

        #不走联合索引 
explain select * from tb_seller where status=‘1‘
explain select * from tb_sellere where address=‘北京市‘



    

!

联合索引内部就是有序的，我们可以把它理解为类似于 order by name,age,address 这样的排序规则。会先根据 name 排序，若name 相同，再根据 age 排序，依次类推。

        explain select * from tb_seller where name=‘小米科技‘ and status > ‘1‘ and address=‘北京市‘;

    

这种情况其实是name 和 status做了索引查询 而 address 则是没有

（a,b,c）

该组合索引是一个以a字段排序而b与c相对有序的B+树，引擎可以通过二分查找定位到a=1的数据，b在a=1确定得情况下是有序的（所以b的有序是相对的），依然可以通过二分查找取出所有b大于2的数据，但这些数据的b字段可能有很多个不同的值，所以c字段是无序的，无法用二分查找来查询c=3的数据，故c用不到索引。

当出现索引覆盖时的情况

        explain select name,status,address from tb_seller where address = ‘北京市‘;


    

符合最左原则是

        explain select name,status,address from tb_seller where name= ‘阿里巴巴‘ and address=‘北京市‘;

    

index:会对整个索引树进行扫描 不会特定的算法查找

ref 代表 mysql 会根据特定的算法查找索引，这样的效率比 index 全扫描要高一些。但是，它对索引结构有一定的要求，索引字段必须是有序的。而联合索引就符合这样的要求！

最左前缀原则。当最左列有序时，才可以保证右边的索引列有序。

若不符合最左前缀原则，但是符合覆盖索引，就可以扫描整个索引树，从而找到覆盖索引对应的列(避免了回表)。

原文：https://www.cnblogs.com/hcly/p/14673150.html