MySQL高级进阶

一、root密码设置和开机自启动

1.1、root密码设置

1.2、开机自启动

1. centos6之前设置开机自动启动mysql服务chkconfig mysql oncentos7之前设置开机自动启动mysql服务systemctl enable mysql.service //让服务开机启动
2. centos6之前查看mysql服务是否开机自动启动chkconfig --list mysqlcentos7查看mysql服务是否开机自动启动systemctl list-units --all --type=service #查看所有service
3. 查看mysql服务是否开机自启ntsysv（类图形界面管理模式来设置开机启动）
   上下键：可以在中间的方框当中，在各个服务之间移动；
   空格键：可以用来选择你所需要的服务，[*]表示开起启动；
   tab键：可以在方框、OK、Cancel之间移动；
   [F1]键：可以显示该服务的说明。

1.3、修改字符集

1>查看字符集show variables like '%char%';，默认的是客户端和服务端都使用latin1，所以会乱码

2>修改字符集
2.1、使用vim打开etc安装目录下的my.cnf文件
2.2、在[mysqld]上面加入下面两句话

  [client]  
  default-character-set=utf8 

2.3、在[mysqld]最下面加入下面几句话

  default-storage-engine=INNODB  
  character-set-server=utf8 
  collation-server=utf8_general_ci

2.4、重启Mysql，再查看字符集sudo service mysqld restart，这里不知道为什么使用mysql提示找不到文件，使用mysqld则可以，猜测是mysql5.7版本的原因

可见服务端和客户端的字符集已经被改变了！！！

一定要重新创建一个数据库，在新创建的数据库中，建表插入数据。如果还是旧数据库还是会报错。

二、逻辑架构

• List item

MySQL架构总共三层，在上图中以虚线作为划分。
　　首先，最上层的服务并不是MySQL独有的，大多数给予网络的客户端/服务器的工具或者服务都有类似的架构。比如：连接处理、授权认证、安全等。
　　
　　第二层的架构包括大多数的MySQL的核心服务。包括：查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数）。同时，所有的跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
　　
　　第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API和存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明化。存储引擎API包含十几个底层函数，用于执行“开始一个事务”等操作。但存储引擎一般不会去解析SQL（InnoDB会解析外键定义，因为其本身没有实现该功能），不同存储引擎之间也不会相互通信，而只是简单的响应上层的服务器请求。

1、Connectors
　　指的是不同语言中与SQL的交互。
　　
2、Connection Pool
　　管理缓冲用户连接，线程处理等需要缓存的需求。负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。

３、 Management Serveices & Utilities
　　系统管理和控制工具。

4、 SQL Interface
　　接受用户的SQL命令，并且返回用户需要查询的结果。
　　
5、 Parser
　　SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query ，在 MySQL Server 里面，连接线程接收到客户端的一个 Query 后，会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。
主要功能：
　　a 、 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的；
　　b、 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的。

6、 Optimizer
　　查询优化器：SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求query，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果。
　　使用的是“选取-投影-联接”策略进行查询：
用一个例子就可以理解： select uid,name from user where gender = 1;
这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤；然后根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤。最后将这两个查询条件联接起来生成最终查询结果。

7 、Cache和Buffer
　　查询缓存：主要功能是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值 做一个对应。该 query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。
　　如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。

8 、存储引擎接口
　　MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。
注意：存储引擎是基于表的，而不是数据库。

原文链接：https://blog.csdn.net/z_ryan/article/details/82260663

三、存储引擎

3.1、InnoDB

• 查看mysql当前默认的存储引擎show variables like "%storage_engine%";
  
• InnoDB概念
  InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
• 支持事务操作，具有事务 ACID隔离特性，默认的隔离级别是可重复读(repetable-read)、通过MVCC（并发版本控制）来实现的。能够解决脏读和不可重复读的问题。
• InnoDB 支持外键操作。
• InnoDB 默认的锁粒度行级锁，并发性能比较好，会发生死锁的情况。
• 和 MyISAM 一样的是，InnoDB 存储引擎也有 .frm文件存储表结构 定义，但是不同的是，InnoDB的表数据与索引数据是存储在一起的，都位于 B+ 数的叶子节点上，而 MyISAM 的表数据和索引数据是分开的。
• InnoDB 有安全的日志文件，这个日志文件用于恢复因数据库崩溃或其他情况导致的数据丢失问题，保证数据的一致性。
• InnoDB 和 MyISAM 支持的索引类型相同，但具体实现因为文件结构的不同有很大差异。增删改查性能方面，果执行大量的增删改操作，推荐使用 InnoDB 存储引擎，它在删除操作时是对行删除，不会重建表。
• innodb引擎的4大特性
  插入缓冲（insert buffer),二次写(double write),自适应哈希索引(ahi),预读(read ahead)
• InnoDB为什么推荐使用自增ID作为主键？
  答：自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

原文链接：https://blog.csdn.net/qq_35642036/article/details/82820178

3.2、MyISAM

在 5.1 版本之前，MyISAM 是 MySQL 的默认存储引擎，MyISAM 并发性比较差，使用的场景比较少，主要特点是

• 不支持事务操作，ACID 的特性也就不存在了，这一设计是为了性能和效率考虑的。

• 不支持外键操作，如果强行增加外键，MySQL 不会报错，只不过外键不起作用。

• MyISAM 默认的锁粒度是表级锁，所以并发性能比较差，加锁比较快，锁冲突比较少，不太容易发生死锁的情况。

• MyISAM 会在磁盘上存储三个文件，文件名和表名相同，扩展名分别是
  .frm(存储表定义)、.MYD(MYData,存储数据)、MYI(MyIndex,存储索引)。这里需要特别注意的是 MyISAM只缓存索引文件，并不缓存数据文件。

• MyISAM 支持的索引类型有 全局索引(Full-Text)、B-Tree 索引、R-Tree 索引

   Full-Text 索引：它的出现是为了解决针对文本的模糊查询效率较低的问题。
  
   B-Tree 索引：所有的索引节点都按照平衡树的数据结构来存储，所有的索引数据节点都在叶节点
  
   R-Tree索引：它的存储方式和 B-Tree 索引有一些区别，主要设计用于存储空间和多维数据的字段做索引,目前的 MySQL 版本仅支持 geometry 类型的字段作索引，相对于 BTREE，RTREE 的优势在于范围查找。
  

• 数据库所在主机如果宕机，MyISAM 的数据文件容易损坏，而且难以恢复。

• 增删改查性能方面：SELECT 性能较高，适用于查询较多的情况

3.3、InnoDB与MyISAM的区别

• MyISAM：如果应用程序通常以检索为主，只有少量的插入、更新和删除操作，并且对事物的完整性、并发程度不是很高的话，通常建议选择MyISAM 存储引擎。
• InnoDB：如果使用到外键、需要并发程度较高，数据一致性要求较高，那么通常选择 InnoDB引擎，一般互联网大厂对并发和数据完整性要求较高，所以一般都使用 InnoDB 存储引擎。

四、SQL性能

4.1、SQL性能下降原因分析

性能下降SQL慢、执行时间长、等待时间长

1. 查询语句写的烂
2. 索引失效
3. 关联查询太多join（设计缺陷或不得已的需求）
4. 服务器调优及各个参数设置（缓冲、线程数等）

4.2、SQL的执行顺序

理解sql语句的执行顺序对优化sql非常重要！！！

• 机读加载顺序

1. from;明确数据的来源先查询出笛卡尔积，select * from a,b;此时就会出现笛卡尔积

from >on> where > group by > select > order by

4.2、七种JOIN理论【超级重点！！！】

1. 内连接-等值连接（得到两张表的交集）
   
   SQL语句select <select_list> from tableA A inner join tableB B on A.key=B.key

测试：

2. 左外连接（得到左表的全部内容，右表没有则显示为null）
   
   SQL语句select <select_list> from table A left join tableB B on A.key=B.key

测试：

3. 右外连接（得到右表的全部内容）
   
   SQL语句select <select_list> from tableA A right join tableB B on A.key=B.key

测试：

4. 左外连接（得到左表的独有内容）
   
   SQL语句select <select_list> from table A left join tableB B on A.key=B.key where B.key is null

测试：

5. 右外连接（得到右表的独有内容）
   
   SQL语句select <select_list> from tableA A right join tableB B on A.key=B.key where A.key is null

测试：

6. 全连接（获取A,B两张表的全部内容）
   
   SQL语句select <select_list> from tableA A full outer join tableB B on A.key=B.key

测试：、MySQL不支持这种语法

测试： 由于MySQL不支持这种语法，所以需要用到union关键字

合并查询结果是将多个SELECT语句的查询结果合并到一起。合并查询结果使用UNION和UNION ALL关键字。

（1）使用UNION关键字是将所有查询结果合并到一起，然后去除相同的记录。

（2）使用UNION ALL关键字则是将所有的结果合并到一起。

7. 全连接(得到A,B两张表的独有内容)
   
   SQL语句select <select_list> from tableA A full outer join tableB B on A.key=B.key where A.key is null or B.key is null

测试：

五、索引【检索和排序】

5.1、索引概念

1. 索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
2. 索引是排好序的快速查找数据结构
3. 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。
4. 我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

5.2、索引的优劣势

• 优势：
  1、可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
  2、通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些。
如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多。

• 劣势：
  1、索引会占据磁盘空间
  2、索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

原文链接：https://blog.csdn.net/qq_35190492/article/details/109257302

5.3、索引的分类

5.3.1、单值索引

• 概念
  一个索引只包含单个列，一个表可以有多个单个索引。

5.3.2、唯一索引

• 概念
  索引列中的值必须是唯一的，但是允许为空值。

5.3.3、复合索引

• 概念
  复合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则）。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

5.3.4、主键索引

• 概念
  索引列中的值必须是唯一的，不允许有空值

5.3.5、普通索引

• 概念
  MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值

5.3.6、全文索引

• 概念
  只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。

5.4、索引的基本语法

• 创建
  索引的创建方式一create [unique] index indexName on mytable(colName(length));
  索引的创建方式二alter mytable add [unique] index [indexName] on (colName(length));
• 删除
  索引的删除drop index [indexName] on mytable;
• 查看
  索引的查看show index from table_name
• 有四种方式来添加数据表的索引
  方式一，添加主键索引alter table tbl_name add primary key(col_list);,添加一个主键，该列索引值必须是唯一的，且不能为NULL
  方式二，添加唯一索引alter table tbl_name add unique index_name(col_list);创建一个唯一索引，这列索引的值必须是唯一的(除了NULL以外，NULL可能会多次出现)
  方式三，添加普通索引alter table tbl_name add index index_name(col_list);添加普通索引，索引值可以出现多次
  方式四，添加全文索引alter table tbl_name add fulltext index_name(col_list);添加全文索引

5.5、索引的数据结构【面试重点】

5.5.1、哈希索引

Hash表
Hash表，在Java中的HashMap，TreeMap就是Hash表结构，以键值对的方式存储数据。我们使用Hash表存储表数据Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

显然这种并不适合作为经常需要查找和范围查找的数据库索引使用。

5.5.2、B树：改造二叉树

MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘IO 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会发生一次IO，如果想要减少磁盘IO操作，就需要尽量降低树的高度。那如何降低树的高度呢？

因为在MySQL的InnoDB存储引擎一次IO会读取的一页（默认一页16K）的数据量，而二叉树一次IO有效数据量只有16字节，空间利用率极低。为了最大化利用一次IO空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。每个节点可以存储1000个索引（16k/16=1000），这样就将二叉树改造成了多叉树，通过增加树的叉树，将树从高瘦变为矮胖。构建1百万条数据，树的高度只需要2层就可以（1000*1000=1百万），也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了，查询数据的效率也就提高了。

这种数据结构我们称为B树，B树是一种多叉平衡查找树，如下图主要特点：

1. B树的节点中存储着多个元素，每个内节点有多个分叉。
2. 节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。
3. 父节点当中的元素不会出现在子节点中。
4. 所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

看到这里一定觉得B树就很理想了，但是前辈们会告诉你依然存在可以优化的地方：

1、B树不支持范围查询的快速查找，你想想这么一个情况如果我们想要查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。
2、如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

5.5.3、B+树：改造B树

B+树，作为B树的升级版，在B树基础上，MySQL在B树的基础上继续改造，使用B+树构建索引。B+树和B树最主要的区别在于非叶子节点是否存储数据的问题

• B树：非叶子节点和叶子节点都会存储数据。
• B+树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。
  B+树真实的数据存在于叶子结点，非叶子结点不存储真实的数据，只存储指引搜素方向的数据项

B+树的最底层叶子节点包含了所有的索引项。从图上可以看到，B+树在查找数据的时候，由于数据都存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据。所以在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系，但是从另一方面来说，由于数据都被放到了叶子节点，所以放索引的磁盘块锁存放的索引数量是会跟着增加的，所以相对于B树来说，B+树的树高理论上情况下是比B树要矮的。也存在索引覆盖查询的情况，在索引中数据满足了当前查询语句所需要的全部数据，此时只需要找到索引即可立刻返回，不需要检索到最底层的叶子节点。

• 等值查询例子
  
• 范围查询
  
  可以看到B+树可以保证等值和范围查询的快速查找，MySQL的索引就采用了B+树的数据结构。

原文链接：https://blog.csdn.net/qq_35190492/article/details/109257302

5.6、哪些情况需要索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引
5. where条件里用不到的字段不创建索引
6. 在高并发下倾向创建组合索引
7. 查询中排序的字段，排序字段若通过索引的去访问将大大提高排序速度
8. 查询中统计或者分组的字段

5.7、哪些情况不需要索引

1. 表记录太少
2. 经常增删改的表，因为提高了查询速度，同时会降低更新表的速度，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
3. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引，注意，如果某个数据列包含许多的重复的内容，为他建立索引就没有太大的实际效果

索引的选择性是指索引列中不同值的数目与表中记录数的比，如果表中有2000条记录，表所有列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99，一个索引的选择性越接近于1，这个索引的效率就越高

六、性能分析【Explain- - 面试的谈资】

6.1、什么是Explain

使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。

6.2、Explain的作用

执行计划包含的信息explain [SQL语句]

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

6.3、Explain字段解释

6.3.1、id【重点】

• 作用：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
• 三种情况
  1、id相同，执行顺序由上至下
  

2、id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

3、id既有相同又有不同，同时存在

6.3.2、select_type

• 作用：查询的类型，主要用于区别普通查询、联合查询、子查询等的复杂查询，一共有六种类型
  

1. SIMPLE:简单的select查询，查询中不包含子查询或者UNION
2. PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
3. SUBQUERY：在select或where列表中包含的子查询
4. DERIVED：在from列表中包含的子查询被标记为derived(衍生)，MySQL会递归这些子查询，把结果集放在临时表里
5. UNION：若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为derived
6. UNION RESULT：从union表回去结果的select

6.3.3、table

• 作用：显示这一行的数据是关于哪张表的

6.3.4、partitions

• 作用：匹配的分区

6.3.5、type【重点】

• 作用：访问类型排列，显示查询使用了何种类型

从最好到最差依次为
system>const>eq_ref>ref>range>index>all
一般来说，得保证查询至少达到range级别，最好能达到ref

1. system:表只有一行记录（等于系统表），这时const类型的特例，平时不会出现，这个可以忽略不计
2. const：表示通过索引一次就找到了。const用于比较primary key 或者unique索引，因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
3. eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
4. ref：非唯一索引扫描，返回匹配某个单独值得所有行，本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
5. range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、in等的查询，这种范围扫描索引比全表扫描要好，因为他只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引
6. index：index与all区别为index类型只遍历索引树，这通常比all快，因为索引文件通常比数据文件小（也就是说虽然all和index都是读全表，但是index是从索引中读取的，而all是从硬盘中读）
7. all：全表扫描

6.3.6、possible_keys

• 作用：显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用

6.3.7、key【重点】

• 作用：实际使用的索引，如果为NULL,则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中

6.3.8、key_len

• 作用：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

6.3.9、ref

• 作用：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值

6.3.10、rows【重点】

• 作用：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

6.3.11、Extra 【重点】

• 作用：包含不适合在其他列中显示但十分重要的额外信息

1. Using filesort说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MySQL中无法利用索引完成的排序操作称为‘文件排序’
2. Using temporary使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表，常见于order by和分组查询 group by
3. Using index表示相应的select操作中使用了覆盖索引，避免了访问表的数据行，效率不错！如果同时出现 using where，表明索引被用来执行索引键值的查找，如果没有同时出现，表明索引用来读取数据而非执行查找动作

覆盖索引：

就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖
注意
1、如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有的字段一起做索引会导致索引文件过大，查询性能下降

七、索引优化案例【重点】

7.1、索引单表优化案例

1. 基本表
   
2. 查询案例：查询category_id为1且comments 大于1的情况下，views最多的article_id。
   
3. 分析SQL
   

结论：type是ALL，即最坏的情况，Extra里还出现了 Using filesort 也是最坏的情况，优化是必须的

4. 开始优化

创建索引一般有两种方式
方式一：ALTER TABLE article ADD INDEX idx_article_ccv('category_id', 'comments', 'views');
方式二：create index idx_article_ccv on article(category_id, comments, views);

• 为用到的字段创建联合索引，并观察索引建立情况
  

此时可以观察到，对应的字段上都有了相应的Seq_in_index，这个表示执行顺序，先从1开始查找

• 观察建立完索引的type
  
• 但是Extra里的Using filesort还存在，还要继续优化

分析：虽然我们已经建立了索引，但是为啥没用呢？
1、这是因为按照B+Tree索引的工作原理，先排序 category_id，如果遇到相同的 category_id 则再排序comments，如果遇到相同的 comments 则再排序 views。
2、当comments字段在联合索引里处于中间位置时，因为comments>1条件是一个范围值（所谓 range），MySQL 无法利用索引再对后面的views部分进行检索，即 range 类型查询字段后面的索引无效。

• 发现Extra里的Using filesort还存在，证明当前索引不能完美解决问题

删除当前索引，从新创建索引
删除索引：DROP INDEX idx_article_ccv ON article;

观察到索引已经删除

从新创建索引：create index idx_article_ccv on article(category_id, views);

再次观察，发现此时type与extra都符合要求

见解：由于 range 后（comments > 1）的索引会失效，这次我们建立索引时，直接抛弃 comments 列，先利用 category_id 和 views 的联合索引查询所需要的数据，再从其中取出 comments > 1 的数据

7.2、索引两表优化案例

1. 基本表

2. 查询案例：实现两表的连接，连接条件是 class.card = book.card

测试左连接

3. 分析SQL（explain）

type 有 All ，rows 为表中数据总行数，说明 class 和 book 进行了全表检索
即每次 class 表对 book 表进行左外连接时，都需要在 book 表中进行一次全表检索

4. 开始优化

   4.1、为book表的card字段添加索引
   
   4.2、再次分析SQL
   
   结果发现，type变成了ref，rows也变成了一行，优化效果明显这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引。 左表连接右表，则需要拿着左表的数据去右表里面查，索引需要在右表中建立索引

   4.3、验证上述理论左表连接右表，则需要拿着左表的数据去右表里面查，索引需要在右表中建立索引
   
   再次观察发现此时为左表建立索引的效果并不理想
   

测试右连接

测试右连接时，发现把索引加到左表时，效果明显

分析：这是因为RIGHT JOIN条件用于确定如何从左表搜索行，右边一定都有，所以左边是我们的关键点，一定需要建立索引。 class RIGHT JOIN book ：book 里面的数据一定存在于结果集中，我们需要拿着 book 表中的数据，去 class 表中搜索，所以索引需要建立在 class 表中

总结：左连接加右表，右连接加左表

7.3、索引三表优化案例

1. 基本表
   

2. 查询案例：三表连接查询
   

3. 分析SQL（explain）
   
   结论：
   ①type 有All ，rows 为表数据总行数，说明 class、 book 和 phone 表都进行了全表检索
   ②Extra 中 Using join buffer ，表明连接过程中使用了 join 缓冲区

测试左连接

4. 开始优化
   4.1、按照两表建立索引一样，依次为右表建立索引
   
   4.2、再次分析SQL
   
   可以看到此时建立索引之后，book和phone的type都变成了ref，并且总rows都变的很小，证明优化效果明显，因此索引最好设置在需要经常查询的字段中

5. Join 语句优化的结论

1、尽可能减少Join语句中的NestedLoop(嵌套循环)的循环总次数；
2、永远用小结果集驱动大的结果集（在大结果集中建立索引，在小结果集中遍历全表）；
3、优先优化NestedLoop的内层循环；
4、保证Join语句中被驱动表上Join条件字段已经被索引；
5、当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置；

理解：使用小表驱动大表，这就相当于外层 for 循环的次数少，内层 for 循环的次数多 。然后我们在大表中建立了索引，这样内层 for 循环的效率明显提高，外层for循环可以看成连接数，内层for循环可以看成操作数，即在最少的连接数的情况下尽量多的查询多的数据，即小表驱动大表 综上，使用小表驱动大表，在大表中建立了索引

八、索引失效【重点】

8.1、演示索引失效

1. 基本表
   
2. 建立复合索引

3. 演示索引失效

索引失效准则【重点】
1、全值匹配我最爱
2、最佳左前缀法则：如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
3、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
4、存储引擎不能使用索引中范围条件右边的列
5、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
6、mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
7、is null，is not null 也无法使用索引（早期版本不能走索引，后续版本应该优化过，可以走索引）
8、like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描操作
9、字符串不加单引号索引失效
10、少用or，用它连接时会索引失效

8.1.1、全值匹配我最爱

• 匹配一个字段值
  

• 匹配两个字段值
  

• 匹配三个字段值
  
  注意：匹配的字段越多，精度越高，对应的len_key越大

• 当查询的顺序与建立索引的顺序不一致时，索引会失效
  

8.1.2、最佳左前缀法则【重点】

• 概念：最佳左前缀法则：如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列

最佳左匹配法则：带头大哥不能死，中间兄弟不能断

• 带头大哥不能死
  

• 中间兄弟不能断
  
  ref = const 表明只使用了一个常量，即第二个常量（pos = ‘dev’）没有生效

8.1.3、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换）

8.1.4、存储引擎不能使用索引中范围条件右边的列（范围之后全失效）

8.1.5、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

使用覆盖索引，可以观察到Extra使用了Using index;Using index有比没有好

8.1.6、mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描

8.1.7、is null，is not null 也无法使用索引（早期版本不能走索引，后续版本应该优化过，可以走索引）

8.1.8、like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描操作

重点：解决like'%字符串%'时索引不被使用的方法

1. 基本表
   

2. 创建联合索引
   

3. 测试覆盖索引
   3.1、在没有建立索引之前，所有的都是全表扫描
   
   
   3.3、建立联合索引之后（id也可以使用，是因为id是主键）
   
   但凡有索引之外的多余字段，覆盖索引就会失效
   

8.1.9、字符串不加单引号索引失效

注意：如果字符串忘记写'' ，那么 mysql 会为我们进行隐式的类型转换，但凡进行了类型转换，索引都会失效

8.1.10、少用or，用它连接时会索引失效

8.1.11、总结【重点】

重点：
全值匹配我最爱， 最左前缀要遵守；
带头大哥不能死， 中间兄弟不能断；
索引列上少计算， 范围之后全失效；
LIKE 百分写最右， 覆盖索引不写 *；
不等空值还有 OR， 索引影响要注意；
VAR 引号不可丢， SQL 优化有诀窍。

九、索引面试题【重点】

• 基本表
  

查询优化器：SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求query，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果。

• 第一组（全职匹配）
  
  分析：第一组全职匹配，索引生效！第二组由于MySQL查询优化器会优化客户端请求的query，所以索引也生效了

• 第二组（范围查询）
  
  分析：第一组由于c3 列使用了索引进行排序，并没有进行查找，导致索引失效，并没有查到c4的内容！第二组由于c4之后就什么也没有了，c3并没有受到影响

• 第三组（Order By）

分析：
1、第一组c1、c2索引都生效了，都是const常量，但是由于c4前边c3索引只进行了排序，没有进行查找，c4列没有用到索引
2、第二组与第一组一致。
3、第三组因为索引建立的顺序和使用的顺序不一致，导致 mysql 动用了文件排序。
4、第四组只用 c1 一个字段索引，但是c2、c3用于排序，无filesort。
5、出现了filesort，我们建的索引是1234，它没有按照顺序来，32颠倒了
6、用c1、c2两个字段索引，并且c2已经查询出来了，所以c2、c3用于排序与顺序没有关系了，无filesort
7、用c1、c2两个字段索引，但是c2、c3用于排序，无filesort，跟c5没有什么关系
8、注意查询条件 c2=‘a2’ ，我都把 c2 查出来了（c2 为常量），我还给它排序作甚，所以没有产生 filesort

使用order by 要么同升要么同降，不然会出现Using filesort,即order by a DESC ,b DESC ,c DESC

order by 总结
可以使用索引进行排序


1、order by 能使用索引最左前缀
2、如果where使用索引的最左前缀定义为常量，则order by 能使用索引
例子：
2.1>where a=const order by b,c
2.2>where a=const and b=const order by c
2.3>where a=const and b>const order by b,c


不可以使用索引进行排序
1、order by a ASC,b DESC,c DESC 排序不一致
2、where g=const order by b,c 丢失a索引
3、where a=const order by c 丢失b索引

• 第四组（Group By）

分析：
1、顺序为 1 2 3 ，没有产生文件排序
2、group by 表面上叫分组，分组之前必排序，group by 和 order by 在索引上的问题基本是一样的
3、定值、范围还是排序，一般order by是给个范围，group by 基本上都需要进行排序，但凡使用不当，会有临时表产生

一般性建议
1、对于单键索引，尽量选择针对当前query过滤性更好的索引
2、在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠左越好。
3、在选择组合索引的时候，尽量选择可以能包含当前query中的where子句中更多字段的索引
4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
重点：like 后面以常量开头，比如 like ‘kk%’ 和 like ‘k%kk%’ ，可以理解为就是常量

十、in与exists的区别

• 优化原则：小表驱动大表，即小的数据集驱动大的数据集

1、表a中100000条数据,表b中100条数据，查询数据库次数=1(表a查一次)+100000(子查询：查询表b的次数)100001次

2、 表a中 100条数据，表b100000条，查询数据库次数=1(表a查一次)+100(子查询次数)，一共 101次

• in

当B表的数据集必须小于A表的数据集时，用in优于exists。in:适合外部表数据大于子查询的表数据的业务场景

select * from A where id in(select id from B);
等价于
for select id from B
	for select * from A where A.id=B.id;

分析：in()中的查询只执行一次，它查询出B中的所有的id并缓存起来，然后检查A表中查询出的id在缓存中是否存在，
如果存在则将A的查询数据加入到结果集中，直到遍历完A表中所有的结果集为止。

注意：A表与B表的ID字段应建立索引

• EXISTS

1、EXISTS()查询是将主查询的结果集放到子查询中做验证，根据验证结果是true或false来决定主查询数据结果是否得以保存。

2、当A表的数据集小于B表的数据集时，用exists优于in， exist适合子查询中表数据大于外查询表中数据的业务场景

select * from A where exists(select id from B);
等价于
for select id from A
	for select * from B where B.id=A.id;
	
分析：
EXISTS()查询会执行SELECT * FROM A 查询，执行A.length次，并不会将EXISTS()查询结果结果进行缓存，
因为EXISTS()查询返回一个布尔值true或flase，它只在乎EXISTS()的查询中是否有记录，与具体的结果集无关。
	
注意：A表与B表的ID字段应建立索引

• in与exists的区别

  1、如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。

  2、in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的

• 总结
  1、EXISTS：select ... from table where exists(subquery);
  可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（true或false）来决定主查询的数据结果是否保存

十一、MySQL锁机制

首先对mysql锁进行划分：

1. List item按照锁的粒度划分：行锁、表锁、页锁
2. 按照锁的使用方式划分：共享锁、排它锁（悲观锁的一种实现）
3. 还有两种思想上的锁：悲观锁、乐观锁。
4. InnoDB中有几种行级锁类型：Record Lock、Gap Lock、Next-key Lock
5. Record Lock：在索引记录上加锁
6. Gap Lock：间隙锁
7. Next-key Lock：Record Lock+Gap Lock

原文链接：https://blog.csdn.net/qq_38238296/article/details/88362999

11.1、表锁【偏读】

• 特点
  开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

• 支持引擎
  MyISAM、MEMORY、InNoDB，InnoDB默认的是行级锁

• 表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）

• 共享锁的概念【读锁】
  共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。（只能读不能改）
  用法：LOCK TABLE table_name [ AS alias_name ] READ

• 排它锁的概念【写锁】
  一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
  用法：LOCK TABLE table_name [AS alias_name][ LOW_PRIORITY ] WRITE

11.2、行锁【偏写】

• 特点
  开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 支持引擎
  InNoDB，InnoDB默认的是行级锁
• 表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）
• 共享锁【读锁】
  用法：select ... lock in share mode;

共享锁就是允许多个线程同时获取一个锁，一个锁可以同时被多个线程拥有。

• 排它锁【写锁】
  用法：select ... for update;

排它锁，也称作独占锁，一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁。

行级锁分为：
1、记录锁（Record lock）：对索引项加锁，即锁定一条记录。
2、间隙锁（Gaplock）：对索引项之间的‘间隙’、对第一条记录前的间隙或最后一条记录后的间隙加锁，即锁定一个范围的记录，不包含记录本身
3、Next-keyLock：锁定一个范围的记录并包含记录本身（上面两者的结合）

11.2.1、行级锁的间隙锁（Next-Key lock）

（1）什么时候会出现间隙锁？
用法：select * from 表名 where 字段名>参数**（在一个范围内）** for update；
使用范围条件而不是相等条件检索数据，InnoDB除了给索引记录加锁，还会给不存在的记录（间隙）加锁，其他事务不能操作当前事务锁定的索引与间隙
（2）目的
(a)防止幻读,避免其他事务插入数据
(b)满足其恢复和复制的需要，MySQL的恢复机制是通过BINLOG记录来执行IUD操作来同步Slave的，这就要求：在一个事务未提交前，其他并发事务不能插入满足其锁定条件的任何记录，为了恢复不能插入其他事务

11.2.2、行级锁（Record lock）导致的死锁

为什么会产生死锁？
1、产生死锁原理：在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。
在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key
locking。
2、死锁导致原因：当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。
3、如何避免死锁：
用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因和改进措施
（1）如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
（2）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
（3）对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
（4）在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。

原文链接：https://blog.csdn.net/qq_32679835/article/details/93745182

11.2.3、索引失效行锁变表锁

当一个事务对表的一行进行修改：

update test_innodb_lock set a=4 where b=4000;

b 是varchar 型，但是输入的是int型。发生了类型转化，索引失效，导致 行锁变为表锁；

11.3、乐观锁和悲观锁

​ 在数据库的锁机制中介绍过，数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。

​ 乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

11.3.1、乐观锁

在关系数据库管理系统里，乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法。它假设多用户并发的事务在处理时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务又修改了该数据。如果其他事务有更新的话，正在提交的事务会进行回滚。

​ 乐观锁（ Optimistic Locking ） 相对悲观锁而言，乐观锁假设认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

​ 相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制。一般的实现乐观锁的方式就是记录数据版本。

• 乐观锁的优点和不足：
  ​ 乐观并发控制相信事务之间的数据竞争(data race)的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。

11.3.2、悲观锁

​ 在关系数据库管理系统里，悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作对某行数据应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

​ 悲观锁，正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度(悲观)，因此，在整个数据处理过程中，将数据处于锁定状态。 悲观锁的实现，往往依靠数据库提供的锁机制 （也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）

• 在mysql/InnoDB中使用悲观锁：
  ​ 首先我们得关闭mysql中的autocommit属性，因为mysql默认使用自动提交模式，也就是说当我们进行一个sql操作的时候，mysql会将这个操作当做一个事务并且自动提交这个操作。
• 悲观锁的优点和不足：
  ​ 悲观锁实际上是采取了“先取锁在访问”的策略，为数据的处理安全提供了保证，但是在效率方面，由于额外的加锁机制产生了额外的开销，并且增加了死锁的机会。并且降低了并发性；当一个事物所以一行数据的时候，其他事物必须等待该事务提交之后，才能操作这行数据。