MySQL高级--索引优化

1. 建表插入数据：

   • CREATE TABLE staffs (
     id INT PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR (24) NOT NULL COMMENT ‘姓名‘,
     age INT NOT NULL DEFAULT 0 COMMENT ‘年龄‘,
     pos VARCHAR (20) NOT NULL 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));

2. 查询插入的数据：SELECT * FROM staffs;

   • 
     　　

3. 为表添加复合索引：ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

4. 查询表的索引：show index from staffs;
   • 
5. sql语句1分析：explain select * from staffs where name=‘july‘;
   • 全值匹配
     • 
   • 最佳左前缀法则

     • 

   • 不在索引列上计算：
     • 
   • 范围后面的索引全失效（范围后面的索引失效但是范围本身用到了索引）
     • 
   •  查询列和索引类一样
     • 
   • 使用！=和<> 会造成全表扫描
     • 
   • 使用is  null 和is not null 就不能使用索引了
     • 

   • 通配符中like  %  开头的 会造成全表扫描，而 % 放在关键字后面则不会造成全表扫描，会使用索引

     • 

     • 实现like %两边%，而不进行全表扫描，使用索引的请求 
     • 建表插入数据：

     • 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,‘b@163.com‘);

     • 表中没有任何的索引
     • 
     • 为name 和age 字段建立覆盖索引： alter table tbl_user add index idx_user_nameAge (name,age);　　

     • 

     • 覆盖索引即是我们查询的字段，中只包含我们所建立的索引。即（id，name，age）id主键可以在index中查找

     • 

     • 总结：使用like % 时如果我们不能使%放在关键字的右面，或是业务需求 实现 like %关键字%时，我们应该使用复合索引，即 为我们建立的索引，查询时只查询我们建立了索引的字段。

   • varchar类型的字段，我们在sql语句中不能将 单引号　 去掉 ‘ ’　 否则造成隐形的类型转，造成全表扫描，
     • 

   • 受用 or ，用or 连接时也会使索引失效

     • 　 

       　　　　　　 
6. 总结：
   • 全值匹配我最爱
   • 最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
   • 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描

   • 存储引擎不能使用索引中范围条件右边的列圆

   • 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select*

   • mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

   • is null ,is not null也无法使用索引

   • like以通配符开头(%abc.. . ‘)mysq|索引失效会变成全表扫描的操作

   • 字符串不加单引号索引失效

   • 少用or,用它来连接时会索引失效冒

7.  

   附录：
   1. 带头大哥不能死
   2. 中间兄弟不能断，永远符合左前缀原则
   3. 索引列上无计算
   4. like %加右边
   5. 范围之后全失效
   6. 字符串里有引号　　
   • 
   • 
   • like 　　关键字%自身的索引可以使用　，但也会使后面的索引失效　，不能使用　

MySQL高级--索引优化

原文：https://www.cnblogs.com/zzhAylm/p/14774323.html