Mysql高级（索引及优化）

补录：视图

1. 创建视图，检查选项（避免添加的数据查不到）

   create view 视图名 as select… with [local] check option;

2. 删除视图

   drop view 视图名

3. 查询视图

   select * from 视图名

4. 添加数据 ，

   insert into 视图名[(字段…)] valuses (…)

5. 视图的检查选项
   

1. 存储引擎

1.1 指定存储引擎

在创建表时，指定存储引擎

create table 表名(
	......
)engine=innodb;

1.2 查看当前数据库支持的存储引擎：

show engines;

1.3 InnoDB的存储结构

1.3.1 InnoDB的特点

	* 支持事务
	* 行级锁，提高并发访问性能
	* 支持外键`FOREIGN KEY`

1.3.2 MyISAM

	* 不支持事务，不支持外键
	* 支持表锁，不支持行锁
	* 访问速度快

1.3.3 Memory

	* 内存存放
	* hash索引 

1.4 三种存储引擎的区别

1.5 存储引擎的选择

2. 索引

索引：一种高效查询的数据结构

2.1.1 索引的优缺点：

2.2 索引的结构

2.2.1 特点：

1. Hash索引只能用于对等比较，不支持范围查询
2. 无法利用索引完成排序操作
3. 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+Tree索引

2.2.2 为什么InnoDB存储引擎选择使用B+Tree索引结构？

1. 相对于二叉树，层级更少，搜索效率高
2. 相对于B-Tree，无论叶子节点还是非叶子节点，都会保存数据，导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
3. 相对于Hash索引，B+tree支持范围匹配和排序操作

2.2 索引的分类

• 索引分类：
  
  • 聚集索引选取规则：
    1. 主键索引为聚集索引
    2. 不存在主键，则将使用第一个唯一索引作为聚集索引
    3. 如果都没有，则会自动生成一个rowid作为隐藏的聚集索引
  • 聚集索引和二级索引
    
    如：select * from user where name = 'Arm';因为name字段的索引属于二级索引，则先查找二级索引构成的b+树，找到对应的叶子节点，因为该节点下挂载的是id，而查询的是全部数据，所以根据挂载的id进行回表查询聚集索引构建的B+树
• InnoDB 主键索引的B+Tree能存放多少行数据？

2.3 索引语法

• 创建索引

  CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名(字段1,字段2…)

• 查看所有

  SHOW INDEX FROM 表名

• 删除索引

  DROP INDEX 索引名 ON 表名

2.4 SQL 性能分析

2.4.1 SQL 执行频率

MySQL客户端连接成功后，通过 show [session|global] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的 增删改查的访问频率
> SHOW GLOBAL STATUS LIKE ‘Com_______’;

2.4.2 慢日志查询

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，需要在配置文件 my.cnf中配置如下信息

查看慢查询日志的开启状态：show variables like ‘slow_query_log’;

2.4.3 profile 详情

show profiles 能够在做SQL优化时间帮助我们了解消费的时间，通过 have_profiling参数，能够查看当前MySQL是否支持profile操作

select @@have_profiling

默认profiling是关闭的，可以通过set语句在 session/global级别开启profiling

开启：set profiling = 1;
查看开启状态：select @@profiling;

2.4.4 执行计划

直接在select 语句前加上关键字 explain / desc

explain select 字段列表 from 表名 where 条件;

explain执行计划各字段含义：

2.5 索引使用

2.5.1 最左前缀法则

如果使用的是联合索引，要遵循最左前缀法则。查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）

假设user表存在联合索引（profession,age,status）
desc select * from user where profession = ‘软件’ and age = 31 and status=‘0’; 索引全部生效
desc select * from user where profession = ‘软件’ and age=31; status索引失效
desc select * from user where profession=‘软件’; age,status索引失效
desc select * from user where age=31 and status=‘0’;索引全部失效

2.5.2 索引失效

联合索引中，出现范围查询（>，<），范围查询右侧的列索引失效，所以范围查询尽可能的使用 >= 而不是>，<

索引列运算

在索引列上进行运算操作，索引将会失效
> select * from user where substring(phone,10,2)=‘15’;索引失效

字符串不加引号

字符串类型字段使用时，不加引号，索引将失效
> desc select * from user where phone=‘11111111111’;索引生效
> desc select * from user where phone=‘11111111111’;索引失效

模糊查询，头部模糊匹配

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引将失效
> select * from user where phone like ‘156%’;索引生效
select * from user where phone like ‘%6’; **索引失效 **

or 连接的条件

用 or 分割开的条件，必须两个条件中列都有索引，否则索引失效

数据分布影响

如果MySQL 评估使用索引比全表更慢，则不使用索引

select * from user where phone >= ‘17799999999’;使用索引
select * from user where phone >=‘17700000000’;不使用索引

SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的

• use index：使用该索引

  desc select * from user use index(idx_user_pro) where profession = ‘软件工程’;

• ignore index：不使用该索引

  desc select * from user ignore index(idx_user_pro) where profession=‘软件工程’;

• force index：必须使用该索引

  desc select * from user force index(idx_user_pro) where profession=‘软件工程’;

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中能够全部找到），减少 select *

using index condition：查找使用了索引，但需回表查询数据
using where; using index：查询使用了索引，但需要的数据都在索引列中能找到，所以不需要回表查询数据

前缀索引

当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时浪费了大量的磁盘io，影响查询效率，此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，提高索引效率

• 语法：create index 索引名 on 表名(字段名(n)); n为截取前面 n 个字符
• 前缀长度：
  可以根据索引的选择性来决定，而选择性是值不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的

2.6 索引设计原则

1. 针对数据量较大，且查询比较频繁的表建立索引
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
4. 如果是字符串类型的字段，字段长度越长，可以针对于字段的特点，建立前缀索引
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
7. 如果索引列不能存储null 值，在创建表使用 not null约束它，当优化器知道每列是否包含null值时，它可以更好的确定哪个索引最有效地用于查询

3. SQL优化

3.1 插入数据

• insert 优化
  1. 批量插入
  2. 手动提交事务
  3. 主键顺序插入
• 大批量插入数据
  如果一次性需要插入大批量数据，使用insert 插入性能较低，推荐使用 load 指令

3.2 主键优化

• 主键设计原则
  1. 满足业务需求的同时，尽量降低主键的长度
  2. 插入数据时，尽量选择顺序插入，选择 AUTO_INCREMENT自增主键
  3. 尽量不要使用 UUID 做主键或者是其它自然主键，如身份证号
  4. 尽量避免对主键的修改

3.3 order by优化

1. Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 fileSort排序
2. Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index。不需要额外排序，操作效率高

创建索引时，指定索引的排序方式：
create index idx_user_age_phone on user(age desc,phone desc);

3.4 group by优化

• 分组操作时，可以通过索引来提高效率
• 索引的使用要满足最左前缀法则

3.5 limit优化

一个常见的问题：limit 2000000,10，此时需要MySQL排序前 200010 记录，仅仅返回2000000-2000010的记录，其它的记录丢弃，查询排序的代价非常大

• 优化思路：一般分页查询时，通过创建 覆盖索引 能够较好的提高性能，可以通过覆盖索引急+子查询形式进行优化

select s.* from stu s,(select id from stu order by id limit 2000000,10) a where s.id = a.id;

3.6 count优化

select count(*) from user;

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候直接返回这个数，效率很高
• InnoDB 引擎执行 count(*) 的时候，需要把数据一行一行地从引擎中读出来，然后累加计数
• count的几种用法及流程：
  1. count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL ，累计值+1，最后返回累计值
  2. 用法：count(*)、count(主键)、count(字段)、count(1)
  3. 底层流程
     • count(主键)：
       InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加
     • count(字段)：
       没有 not null 约束：InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加
       有 not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行累加
     • count(1)
       InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字 “1” 进去，直接按行进行累加
     • count(*) 推荐
       不取值，服务层直接按行进行累加

3.7 update优化

更新的条件必须加索引，InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效 ，否则会从行锁升级为表锁

4. 锁

4.1 全局锁：锁定数据库中的所有表

加锁后整个实力就处于只读状态，使用场景：全库的逻辑备份

开启全局锁：flush tables with read lock;
关闭全局锁：unlock tables;
mysqldump -uxx -pxx 库 >库.sql

4.2 表级锁：每次操作锁住整张表

1 表锁

表级锁，每次操作锁住整张表，锁定力度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB等存储引擎中

1. 分类
   • 表共享读锁（read lock）：每个客户端只能读不能写
   • 表独占写锁（write lock）：当前客户端能读写，其它客户端不能读写
2. 语法

   加锁：lock tables 表名… read/write
   释放锁：unlock tables 或 关闭客户端

2. 元数据锁

加锁过程是系统自动控制的，无需显示使用，在访问一张表的时候会自动加上
主要作用：当表中存在未提交的事务时，不允许修改表结构，保持了元数据的数据一致性

3 意向锁（两种）

为了避免 DML 在执行时，加的行锁与表锁的冲突，在 InnoDB中引入了意向锁，它可以使表锁不用检查每行数据是否加锁，减少了表锁的检查

• 可以使用以下SQL，查看意向锁及行锁的加锁情况

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

1. 意向共享锁（IS）

• 由语句 select…lock in share mode 添加
• 与表锁共享锁( read )兼容，与表锁排它锁( write )互斥

2. 意向排他锁（IX）

• 由 insert、update、delete、select … for update 添加
• 与表锁共享锁(read)及排它锁(write)都互斥，意向锁之间不会互斥

4.3 行级锁

每次操作锁住对应行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在 InnoDB存储引擎中
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁实现的，而不是对应记录加的锁

1. 行锁( Record Lock )

锁定单个记录的锁，防止其它事务对此进行 update 和 delete。在 RC、RR隔离级别下都支持

1. 共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁
2. 排它锁(X)：允许获取排它锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排它锁
   

2. 间隙锁(Gap Lock)

锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行 insert，产生欢度。在RR隔离级别下都支持

3. 临键锁(Next-Key Lock)

行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap，在RR隔离级别下支持

5. InnoDB引擎

5.1 逻辑存储结构

5.2 架构

5.2.1 缓冲池 Buffer Pool

5.2.2 更改缓冲区 Change Buffer（ps:8.0及以后出现）

自适应has索引，下图

5.3 事务原理

5.3.1 四大特性

1. 原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
2. 一致性：事务完成时，必须使所有的数据保持一致状态
3. 隔离性：数据库提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
4. 持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

5.4 MVCC