七、MySQL锁机制
❝数据库的乐观锁和悲观锁？ MySQL 中有哪几种锁，列举一下？ MySQL中InnoDB引擎的行锁是怎么实现的？ MySQL 间隙锁有没有了解，死锁有没有了解，写一段会造成死锁的 sql 语句，死锁发生了如何解决，MySQL 有没有提供什么机制去解决死锁
锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。
打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？这里肯定要用到事物，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。
锁的分类
从对数据操作的类型分类：
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，不会互相影响
- 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁
从对数据操作的粒度分类：
为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念。
- 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低（MyISAM 和 MEMORY 存储引擎采用的是表级锁）；
- 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高（InnoDB 存储引擎既支持行级锁也支持表级锁，但默认情况下是采用行级锁）；
- 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。
行锁 | 表锁 | 页锁 | |
|---|---|---|---|
MyISAM | √ | ||
BDB | √ | √ | |
InnoDB | √ | √ | |
Memory | √ |
MyISAM 表锁
MyISAM 的表锁有两种模式：
- 表共享读锁 （Table Read Lock）：不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
- 表独占写锁 （Table Write Lock）：会阻塞其他用户对同一表的读和写操作；
MyISAM 表的读操作与写操作之间，以及写操作之间是串行的。当一个线程获得对一个表的写锁后， 只有持有锁的线程可以对表进行更新操作。其他线程的读、 写操作都会等待，直到锁被释放为止。
默认情况下，写锁比读锁具有更高的优先级：当一个锁释放时，这个锁会优先给写锁队列中等候的获取锁请求，然后再给读锁队列中等候的获取锁请求。
InnoDB 行锁
InnoDB 实现了以下两种类型的行锁：
- 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
- 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：
- 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
- 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
索引失效会导致行锁变表锁。比如 vchar 查询不写单引号的情况。
加锁机制
乐观锁与悲观锁是两种并发控制的思想，可用于解决丢失更新问题
乐观锁会“乐观地”假定大概率不会发生并发更新冲突，访问、处理数据过程中不加锁，只在更新数据时再根据版本号或时间戳判断是否有冲突，有则处理，无则提交事务。用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式
悲观锁会“悲观地”假定大概率会发生并发更新冲突，访问、处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。
锁模式(InnoDB有三种行锁的算法)
记录锁(Record Locks)：单个行记录上的锁。对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
代码语言：javascript复制SELECT * FROM table WHERE id = 1 FOR UPDATE;
它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行
在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：
代码语言：javascript复制-- id 列为主键列或唯一索引列
UPDATE SET age = 50 WHERE id = 1;
间隙锁（Gap Locks）：当我们使用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁。对于键值在条件范围内但并不存在的记录，叫做“间隙”。
InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。
对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。
间隙锁基于非唯一索引，它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法，请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。
SELECT * FROM table WHERE id BETWEN 1 AND 10 FOR UPDATE;
即所有在（1，10）区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。
GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况
临键锁(Next-key Locks)：临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。(临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。)
Next-Key 可以理解为一种特殊的间隙锁，也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。
对于行的查询，都是采用该方法，主要目的是解决幻读的问题。
❝select for update有什么含义，会锁表还是锁行还是其他
for update 仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时，通过“for update”语句，MySQL会对查询结果集中每行数据都添加排他锁，其他线程对该记录的更新与删除操作都会阻塞。排他锁包含行锁、表锁。
InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！假设有个表单 products ，里面有id跟name二个栏位，id是主键。
- 明确指定主键，并且有此笔资料，row lock
SELECT * FROM products WHERE id='3' FOR UPDATE;
SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;
- 明确指定主键，若查无此笔资料，无lock
SELECT * FROM products WHERE id='-1' FOR UPDATE;
- 无主键，table lock
SELECT * FROM products WHERE name='Mouse' FOR UPDATE;
- 主键不明确，table lock
SELECT * FROM products WHERE id<>'3' FOR UPDATE;
- 主键不明确，table lock
SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;
注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。注2: 要测试锁定的状况，可以利用MySQL的Command Mode ，开二个视窗来做测试。
❝MySQL 遇到过死锁问题吗，你是如何解决的？
死锁
死锁产生：
- 死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环
- 当事务试图以不同的顺序锁定资源时，就可能产生死锁。多个事务同时锁定同一个资源时也可能会产生死锁
- 锁的行为和顺序和存储引擎相关。以同样的顺序执行语句，有些存储引擎会产生死锁有些不会——死锁有双重原因：真正的数据冲突；存储引擎的实现方式。
检测死锁：数据库系统实现了各种死锁检测和死锁超时的机制。InnoDB存储引擎能检测到死锁的循环依赖并立即返回一个错误。
死锁恢复：死锁发生以后，只有部分或完全回滚其中一个事务，才能打破死锁，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。所以事务型应用程序在设计时必须考虑如何处理死锁，多数情况下只需要重新执行因死锁回滚的事务即可。
外部锁的死锁检测：发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁， 这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决
死锁影响性能：死锁会影响性能而不是会产生严重错误，因为InnoDB会自动检测死锁状况并回滚其中一个受影响的事务。在高并发系统上，当许多线程等待同一个锁时，死锁检测可能导致速度变慢。有时当发生死锁时，禁用死锁检测（使用innodb_deadlock_detect配置选项）可能会更有效，这时可以依赖innodb_lock_wait_timeout设置进行事务回滚。
MyISAM避免死锁：
- 在自动加锁的情况下，MyISAM 总是一次获得 SQL 语句所需要的全部锁，所以 MyISAM 表不会出现死锁。
InnoDB避免死锁：
- 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用
SELECT ... FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。 - 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
- 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
- 通过
SELECT ... LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。 - 改变事务隔离级别
如果出现死锁，可以用 show engine innodb status;命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的 SQL 语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。
八、MySQL调优
❝日常工作中你是怎么优化SQL的？ SQL优化的一般步骤是什么，怎么看执行计划（explain），如何理解其中各个字段的含义？ 如何写sql能够有效的使用到复合索引？ 一条sql执行过长的时间，你如何优化，从哪些方面入手？ 什么是最左前缀原则？什么是最左匹配原则？
影响mysql的性能因素
- 业务需求对MySQL的影响(合适合度)
- 存储定位对MySQL的影响
- 系统各种配置及规则数据
- 活跃用户的基本信息数据
- 活跃用户的个性化定制信息数据
- 准实时的统计信息数据
- 其他一些访问频繁但变更较少的数据
- 二进制多媒体数据
- 流水队列数据
- 超大文本数据
- 不适合放进MySQL的数据
- 需要放进缓存的数据
- Schema设计对系统的性能影响
- 尽量减少对数据库访问的请求
- 尽量减少无用数据的查询请求
- 硬件环境对系统性能的影响
性能分析
MySQL Query Optimizer
- MySQL 中有专门负责优化 SELECT 语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的 Query 提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是 DBA 认为是最优的，这部分最耗费时间）
- 当客户端向 MySQL 请求一条 Query，命令解析器模块完成请求分类，区别出是 SELECT 并转发给 MySQL Query Optimize r时，MySQL Query Optimizer 首先会对整条 Query 进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对 Query 中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query 中的 Hint 信息（如果有），看显示 Hint 信息是否可以完全确定该 Query 的执行计划。如果没有 Hint 或 Hint 信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据 Query 进行写相应的计算分析，然后再得出最后的执行计划。
MySQL常见瓶颈
- CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
- IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件的性能瓶颈：top，free，iostat 和 vmstat来查看系统的性能状态
性能下降SQL慢 执行时间长 等待时间长 原因分析
- 查询语句写的烂
- 索引失效（单值、复合）
- 关联查询太多join（设计缺陷或不得已的需求）
- 服务器调优及各个参数设置（缓冲、线程数等）
MySQL常见性能分析手段
在优化MySQL时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分析查询，profiling分析以及show命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。
性能瓶颈定位
我们可以通过 show 命令查看 MySQL 状态及变量，找到系统的瓶颈：
代码语言：javascript复制Mysql> show status ——显示状态信息（扩展show status like ‘XXX’）
Mysql> show variables ——显示系统变量（扩展show variables like ‘XXX’）
Mysql> show innodb status ——显示InnoDB存储引擎的状态
Mysql> show processlist ——查看当前SQL执行，包括执行状态、是否锁表等
Shell> mysqladmin variables -u username -p password——显示系统变量
Shell> mysqladmin extended-status -u username -p password——显示状态信息
Explain(执行计划)
是什么：使用 Explain 关键字可以模拟优化器执行SQL查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈
能干吗：
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
怎么玩：
- Explain SQL语句
- 执行计划包含的信息（如果有分区表的话还会有partitions）
expalin
各字段解释
- id（select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序）
- id相同，执行顺序从上往下
- id全不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id部分相同，执行顺序是先按照数字大的先执行，然后数字相同的按照从上往下的顺序执行
- select_type（查询类型，用于区别普通查询、联合查询、子查询等复杂查询）
- SIMPLE ：简单的select查询，查询中不包含子查询或UNION
- PRIMARY：查询中若包含任何复杂的子部分，最外层查询被标记为PRIMARY
- SUBQUERY：在select或where列表中包含了子查询
- DERIVED：在from列表中包含的子查询被标记为DERIVED，MySQL会递归执行这些子查询，把结果放在临时表里
- UNION：若第二个select出现在UNION之后，则被标记为UNION，若UNION包含在from子句的子查询中，外层select将被标记为DERIVED
- UNION RESULT：从UNION表获取结果的select
- table（显示这一行的数据是关于哪张表的）
- type（显示查询使用了那种类型，从最好到最差依次排列 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ）
tip: 一般来说，得保证查询至少达到range级别，最好到达ref
- system：表只有一行记录（等于系统表），是 const 类型的特例，平时不会出现
- const：表示通过索引一次就找到了，const 用于比较 primary key 或 unique 索引，因为只要匹配一行数据，所以很快，如将主键置于 where 列表中，mysql 就能将该查询转换为一个常量
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
- ref：非唯一性索引扫描，范围匹配某个单独值得所有行。本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，它可能也会找到多个符合条件的行，多以他应该属于查找和扫描的混合体
- range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需开始于索引的某一点，而结束于另一点，不用扫描全部索引
- index：Full Index Scan，index于ALL区别为index类型只遍历索引树。通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
- ALL：Full Table Scan，将遍历全表找到匹配的行
- possible_keys（显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用）
- key
- 实际使用的索引，如果为NULL，则没有使用索引
- 查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠，仅出现在key列表中
explain-key
- key_len
- 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
- key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
- ref（显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值）
- rows（根据表统计信息及索引选用情况，大致估算找到所需的记录所需要读取的行数）
- Extra（包含不适合在其他列中显示但十分重要的额外信息）
- using filesort: 说明mysql会对数据使用一个外部的索引排序，不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。常见于order by和group by语句中
- Using temporary：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
- using index：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现using where，表明索引被用来执行索引键值的查找；否则索引被用来读取数据而非执行查找操作
- using where：使用了where过滤
- using join buffer：使用了连接缓存
- impossible where：where子句的值总是false，不能用来获取任何元祖
- select tables optimized away：在没有group by子句的情况下，基于索引优化操作或对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
- distinct：优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作
case:
explain-demo
- 第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name......】
- 第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id,name from t1 where other_column=''】
- 第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】
- 第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行【select name,id from t2】
- 第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】
慢查询日志
MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阈值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。
long_query_time的默认值为10，意思是运行10秒以上的语句- 默认情况下，MySQL数据库没有开启慢查询日志，需要手动设置参数开启
查看开启状态
代码语言：javascript复制SHOW VARIABLES LIKE '%slow_query_log%'
开启慢查询日志
- 临时配置：
mysql> set global slow_query_log='ON';
mysql> set global slow_query_log_file='/var/lib/mysql/hostname-slow.log';
mysql> set global long_query_time=2;
也可set文件位置，系统会默认给一个缺省文件host_name-slow.log
使用set操作开启慢查询日志只对当前数据库生效，如果MySQL重启则会失效。
- 永久配置 修改配置文件my.cnf或my.ini，在[mysqld]一行下面加入两个配置参数
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/hostname-slow.log
long_query_time = 3
注：log-slow-queries 参数为慢查询日志存放的位置，一般这个目录要有 MySQL 的运行帐号的可写权限，一般都将这个目录设置为 MySQL 的数据存放目录；long_query_time=2 中的 2 表示查询超过两秒才记录；在my.cnf或者 my.ini 中添加 log-queries-not-using-indexes 参数，表示记录下没有使用索引的查询。
可以用 select sleep(4) 验证是否成功开启。
在生产环境中，如果手工分析日志，查找、分析SQL，还是比较费劲的，所以MySQL提供了日志分析工具mysqldumpslow。
通过 mysqldumpslow --help 查看操作帮助信息
- 得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log - 得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/hostname-slow.log - 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/hostname-slow.log - 也可以和管道配合使用
mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log | more
也可使用 pt-query-digest 分析 RDS MySQL 慢查询日志
Show Profile 分析查询
通过慢日志查询可以知道哪些 SQL 语句执行效率低下，通过 explain 我们可以得知 SQL 语句的具体执行情况，索引使用等，还可以结合Show Profile命令查看执行状态。
Show Profile 是 MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
默认情况下，参数处于关闭状态，并保存最近15次的运行结果
分析步骤
mysql> show profiles; ---------- ------------ --------------------------------- | Query_ID | Duration | Query | ---------- ------------ --------------------------------- | 1 | 0.00385450 | show variables like "profiling" | | 2 | 0.00170050 | show variables like "profiling" | | 3 | 0.00038025 | select * from t_base_user | ---------- ------------ ---------------------------------
代码语言：javascript复制
- converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。
- Copying to tmp table on disk 把内存临时表复制到磁盘
- 诊断SQL，show profile cpu,block io for query id(上一步前面的问题SQL数字号码)
- 是否支持，看看当前的mysql版本是否支持
mysql>Show variables like 'profiling'; --默认是关闭，使用前需要开启 - 运行SQL
❝查询中哪些情况不会使用索引？
性能优化
索引优化
- 全值匹配我最爱
- 最佳左前缀法则，比如建立了一个联合索引(a,b,c)，那么其实我们可利用的索引就有(a), (a,b), (a,b,c)
- 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
- 存储引擎不能使用索引中范围条件右边的列
- 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select
- is null ,is not null 也无法使用索引
- like "xxxx%" 是可以用到索引的，like "%xxxx" 则不行(like "%xxx%" 同理)。like以通配符开头('�c...')索引失效会变成全表扫描的操作，
- 字符串不加单引号索引失效
- 少用or，用它来连接时会索引失效
- <，<=，=，>，>=，BETWEEN，IN 可用到索引，<>，not in ，!= 则不行，会导致全表扫描
一般性建议
- 对于单键索引，尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
- 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
- 少用Hint强制索引
查询优化
永远小标驱动大表（小的数据集驱动大的数据集）
代码语言：javascript复制slect * from A where id in (select id from B)`等价于
#等价于
select id from B
select * from A where A.id=B.id
当 B 表的数据集必须小于 A 表的数据集时，用 in 优于 exists
代码语言：javascript复制select * from A where exists (select 1 from B where B.id=A.id)
#等价于
select * from A
select * from B where B.id = A.id`
当 A 表的数据集小于B表的数据集时，用 exists优于用 in
注意：A表与B表的ID字段应建立索引。
order by关键字优化
- order by子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序
- MySQL 支持两种方式的排序，FileSort 和 Index，Index效率高，它指 MySQL 扫描索引本身完成排序，FileSort 效率较低；
- ORDER BY 满足两种情况，会使用Index方式排序；①ORDER BY语句使用索引最左前列 ②使用where子句与ORDER BY子句条件列组合满足索引最左前列
- 尽可能在索引列上完成排序操作，遵照索引建的最佳最前缀
- 如果不在索引列上，filesort 有两种算法，mysql就要启动双路排序和单路排序
- 双路排序：MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据
- 单路排序：从磁盘读取查询需要的所有列，按照order by 列在 buffer对它们进行排序，然后扫描排序后的列表进行输出，效率高于双路排序
- 优化策略
- 增大sort_buffer_size参数的设置
- 增大max_lencth_for_sort_data参数的设置
GROUP BY关键字优化
- group by实质是先排序后进行分组，遵照索引建的最佳左前缀
- 当无法使用索引列，增大
max_length_for_sort_data参数的设置，增大sort_buffer_size参数的设置 - where高于having，能写在where限定的条件就不要去having限定了
数据类型优化
MySQL 支持的数据类型非常多，选择正确的数据类型对于获取高性能至关重要。不管存储哪种类型的数据，下面几个简单的原则都有助于做出更好的选择。
- 更小的通常更好：一般情况下，应该尽量使用可以正确存储数据的最小数据类型。 简单就好：简单的数据类型通常需要更少的CPU周期。例如，整数比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较复杂。
- 尽量避免NULL：通常情况下最好指定列为NOT NULL
