MySQL进阶：索引使用与SQL优化

索引的使用

索引提升查询效率

/*创建索引*/
create index index_name on t_user(name);
/*删除索引*/
drop index index_name on t_user;
           

避免索引失效

1. 全值匹配

   全值匹配，对索引中所有列都指定具体值。改情况下，索引生效，执行效率高。

2. 最左前缀法则

   联合索引中，在创建索引的顺序中的索引必须左边索引都必要出现，先后顺序无关，但必须出现，不能跳过，例如 name，status，address只能出现以下情况

   /* 以下都会走索引 */
   select * from t_user where name='Himit_ZH'
   
   select * from t_user where name='Himit_ZH' and status='1' 
   
   select * from t_user where name='Himit_ZH' and status='1' and address='广东'\G;
   
   select * from t_user where  status='1' and address='广东' and name='Himit_ZH'\G;
   
              

3. 范围查询右边的列，不能使用索引。

   // 后面的 address的索引会失效。
   select * from t_user where name='Himit_ZH' and status>'1' and address='广东'\G;
              

4. 索引列上不能使用运算操作，否则索引失效。

   // 如此查询，索引将失效。
   select * from t_user where substring(name,6,2)='ZH';
              

5. 字符串不加单引号，索引失效。

   // name走索引，但是status不走索引
   select * from t_user where name ='Himit_ZH' and status = 1
   
   // name和status都走索引
   select * from t_user where name ='Himit_ZH' and status = '1'
              

6. 尽量使用覆盖索引，避免使用select *

用到了索引，但是由于是select * 还会到表结构查询相应的行数据。

此时查询的字段name在B+树中已经存了数据，不需要回表查询了。

此时查询的字段多了一个sex，并没有为这个字创建索引，此时又会发生回表查询。

1. 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。（and 会走索引）

   例如：name有索引，sex没有索引。

   

   进行了全文扫描，并未走索引查询。
2. 使用%模糊匹配时，%出现在模糊字符串前面不走索引，例如

   %ZH

   或者

   %ZH%

   ,但是

   ZH%

   只在后面会走索引。

   

   解决方法：使用覆盖索引，即获取的查询字段都有建立索引。(name,address,status)

   

3. 有可能SQL判断走索引比全文扫描慢，就不会走索引。
4. is NULL或者is not NULL

   有时候

   不走索引，都是MySQL底层会进行字段判断，看执行效率是否需要走索引还是走全文扫描。
5. in走索引，not in不走索引。

   

   

单列索引与复合索引

尽量使用复合索引，少用单列索引。

相当于创建了name和sex索引，这样无论出现单个name，sex还是同时出现，都走一个索引。
create index idx_name_sex on t_user(name,sex)


这样创建，如果两个索引条件同时出现，MySQL只会走一个最优索引，而不是两个索引。
create index idx_name on t_user(name)
create index idx_sex on t_user(sex)
           

查看索引的使用情况

show status like 'Hander_read%';

show global status like 'Hander_read%';
           

SQL优化

explain分析，表中的type是访问类型，效率从高到低如下：

优化插入大量数据

1）批量插入数据主键最好有序，这样InnoDB引擎创建B+树索引时效率更快。

2）关闭唯一性校验

插入数据前关闭唯一性校验

插入后重新开启

3)手动提交事务

插入数据前关闭自动提交事务

插入后再开启

insert语句优化

• 多行数据时使用
• 按主键顺序插入

order by语句优化

查询的字段有创建索引，也就是覆盖索引时，order by会走索引排序，而非全文排序,例如 age，id有索引。

但若是select * 会走filesort

多字段排序时,必须同升同降，一升一降会导致走filesort，同升同降才会走索引排序。

group by语句优化

group by默认会使用filesort排序，如果不想排序，再分组后面加上order by null，关掉会提高分组效率。

嵌套子查询优化

可以看到type变成ref，比index效率更高，所以建议使用多表查询。

or条件优化

建议使用union替换or

type的const的效率远高于range。

limit分页优化

1. 先从索引上完成分页操作，再使用关联查询查回所需的其它字段。

1. 仅适用于主键自增的表，不能有断层。(效率超高，总结走索引，但是有前提)

索引提示

USE INDEX 提示建议MySQL使用指定的索引

IGNORE INDEX 提示忽略使用指定索引

FORCE INDEX 强制使用索引

innoDB 行锁

ACID属性：

• 原子性(Atomicity )：事务是一个原子操作单元，其对数据的修改，要么全部成功，要么全部失败。
• 一致性(Consistent )：在事务开始和完成时，数据都必须保持一致状态。
• 隔离性(lsolation )：事务完成之后，对于数据的修改是永久的。
• 持久性(Durable )：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境下运行。

事务并发的问题：

问题	含义
丢失更新（Lost Update )	当两个或多个事务选择同一行，最初的事务修改的值，会被后面的事务修改的值覆盖。
脏读（Dirty Reads )	当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
不可重复读〔Non-Repeatable Reads )	一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现和以前读出的数据不一致。
幻读(Phantom Reads )	一个事务按照相同的查询条件重新读取以前查询过的数据，却发现其他事务插入了满足其查询条件的新教据。

InnoDB实现了以下两种类型的行锁。

• 共享锁(S）∶又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

• 排他锁(X）∶又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获

  取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁(X);

对于普通SELECT语句，innoDB不会加任何锁;

手动加锁

共享锁(S）: SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁(x): SELECT * FROM table_name WHERE ... FOR UPDATE

           

• 无索引（索引失效）的操作会升级行锁成表锁，此次操作事务不提交，他人无法进行更新操作。
• 间隙锁：当我们用范围条件，而不是使用相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据进行加锁;对于键值在条件范围内但并不存在的记录，叫做"间隙(GAP )"，InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的间隙锁(NextKey锁)。