mysql之索引优化案例一

1. 创建表结构以及相关索引

   CREATE TABLE staffs(
   id INT PRIMARY KEY AUTO_INCREMENT,
   `name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
   `age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
   `pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
   `add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
   )CHARSET utf8 COMMENT'员工记录表';
   INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
   INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
   INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());
   
   ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
   
   CREATE TABLE `tbl_user`(
   `id` INT(11) NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(20) DEFAULT NULL,
   `age`INT(11) DEFAULT NULL,
   `email` VARCHAR(20) DEFAULT NULL,
   PRIMARY KEY(`id`)
   )ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
   
   INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
   INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
   INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
   INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');
   
   

   
2. 对应最佳左前缀法则
   1）需求描述：
         查询年龄为23，职位是dev的员工
    2）sql语句
          SELECT * from staffs where age = '23' and pos = 'dev'
    3）查看执行计划
           
           可以发现出现了全表扫描。索引竟然没有用到
    4) 我们在使用下面的sql试一下
           
           
              
           
    5）通过上面发现
           我们创建的索引是index_staffs_nameAgePos,那么sql语句中条件必须存在name,如果单独存在age或者pos,
           以及同时存在age和pos但是不存在name，那么索引都不会生效。这个是因为它违背了最佳左前缀法则。
           
         
    6）总结
          按照我们上面建立索引的方式，我们在查询条件的时候必须按照顺序去作为条件查询，否则会导致索引失效，
          注意：这里的顺序不是查询条件的顺序，而是索引的顺序，就是说，我们创建索引的顺序是name,age,pos,
          name在条件里面必须存在name,否则索引失效，如果存在name了，想要pos字段索引也生效，name必须存在
         age,否则pos不会生效。
3. 不在索引列上做任何操作（计算，函数，（自动or手动）类型转换）会导致索引失效而转向全表扫描
   1）我们使用left函数查询
        
   2）使用执行计划看看
          
4. 存储引擎不能使用索引中范围条件右边的列
    
5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
    
6. mysql在使用不等于(!=或者<>)的时候无法使用索引，会导致全表扫描
   
7. is null,is not null也会导致全表扫描
   
8. like以通配符开头（'%abc...'）也会导致全表扫描
   
   可以发现在右边写百分号，还是可以走索引的。
   那么如何解决这个like的问题呢？
   1) 首先我们为tb1_user表创建索引
         
   2）使用覆盖索引解决全表扫描的问题
         我们只查建立了索引的字段name和age（只查name或者age也可以）
          
   3）只查询主键也可以走索引（没啥意义，业务中几乎不可能只查主键）
         
   4）但是加上其它的字段就会导致索引失效
         
         
9. 字符串不加单引号索引失败
   
10. 少用or,用它来连接时会导致索引失效