MYSQL--Spring JDBC、日志系统、存储引擎、索引、SQL优化

11、Spring JDBC

11.1、初识Spring JDBC

Spring JDBC 是Spring框架对JDBC的简单封装。它提供了一个JdbcTemplate对象，用于简化JDBC的开发。

使用步骤：

1. 导入jar包

   • Spring自己的5个jar包
   • 数据库驱动jar包
   • 数据库连接池jar包

2. 创建JDBCTemplate对象

   • 依赖于DataSource

     JdbcTemplate template = new JdbcTemplate(ds);

3. 调用JdbcTemplate的方法来完成CRUD（增删改查）的操作

   • update()：执行DML增删改查
   • queryForMap()：查询结果，将结果集封装成Map集合
   • queryForList()：查询结果，将结果集封装成List集合
   • query()：查询结果，将结果封装成JavaBean对象
   • queryForObject()：查询结果，将结果封装为对象

使用演示：

public class jdbcTemplateDemo1 {
    public static void main(String[] args) {
        //创建JdbcTemplate对象
        JdbcTemplate template = new JdbcTemplate(DruidUtils.getDataSource());
        //定义sql
        String sql = "update user set count = 500 where id = ?";
        //调用方法
        int count = template.update(sql, 4);
        if (count > 0){
            System.out.println("Update Completed!");
        }
    }
}

无需手动释放资源、获取连接，只需要专注于如何获取数据和处理数据。

十、MySQL 基础架构

1、MySQL体系结构

• 连接层
  • 完成一些连接、授权认证、安全相关的操作。
  • 大多数基于网络的客户端/服务器的工具，都有类似的架构。
• 服务层
  • 完成大多数核心服务功能，比如SQL分析、优化、所有的内置函数。
  • 所有的跨存储引擎的功能也在这一层实现，比如存储过程、触发器、视图。
• 引擎层
  • 存储引擎真正负责了MySQL中数据的存储和提取
  • 服务器层通过API与存储引擎通信，这些接口屏蔽了不同存储引擎之间的差异
• 存储层
  • 将数据存储在文件系统之上，完成与存储引擎的交互

2、一个连接是如何建立的

1、连接建立的过程

在进行操作之前，需要先连接数据库：

mysql ‑h$ip ‑P$port ‑u$user ‑p

这里的mysql是客户端工具，用来跟服务端建立连接。

在完成TCP三次握手后，连接器就会使用输入的用户名、密码，进行身份认证。

• 如果用户名或密码错误，就会收到一个"Access denied for user"错误，随后客户端结束执行。
• 如果用户名密码认证通过，连接器会到权限表中查出此用户拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。所以，一个用户成功连接后，即使修改了它的权限，也不会影响已经存在连接的权限，只有新建立的连接才会使用新的权限。

在连接完成后，不做任何操作，此连接就处于空闲状态，可以通过这个命令查看：

show processlist

Command为sleep的列，表示一个空闲连接。如果空闲太久（默认为8小时），连接器就会自动将它断开。空闲时间的参数：

wait_timeout

在连接被断开后，如果客户端再发送请求，就会收到一个错误提醒：Lost connection to MySQL server during query。

此时必须重新建立连接，再执行请求。

简单来说，建立连接需要经过这些步骤：建立网络连接（TCP三次握手）、验证用户名和密码、读取用户权限，连接成功建立。

2、长连接与短连接

• 长连接：如果客户端持续有请求，则一直使用同一个连接
• 短连接：每次执行完很少的几次查询，就断开连接，下次查询再重新建立一个连接

建立连接的过程比较复杂，所以建议尽量减少连接的动作，多使用长连接。

3、长连接的问题

如果全部使用长连接，有时候MySQL占用内存会涨得特别快。

这是因为MySQL在执行过程中，临时使用的内存是管理在连接对象中的，这些资源在连接断开时才会释放。所以长连接会累积这些资源。

如果最后一个连接占用的内存过大，会被系统强行杀掉（OOM），那么现象是MySQL会异常重启。

可以从两方面解决：

• 定期断开长连接。设定一个时间，或者判断程序中执行过占用内存大的查询后，就断开连接，下次查询时建立一个新的连接
• 如果是5.7以上的版本，可以在每次执行完一个占用内存大的操作后，执行 mysql_reset_connection，重新初始化连接资源，会将连接恢复到刚建立的状态，而且不用重连和重新做权限验证，代价更小。

4、连接管理

每个客户端连接，都会在MySQL服务器进程中拥有一个线程。这个连接的查询只会在它的单独线程中执行，该线程会被正常调度给CPU执行。

MySQL服务器会负责缓存线程，不需要为每一个新建的连接去创建或销毁线程。

3、一条查询语句的执行细节

在建立连接后，执行一条SQL语句，会经过服务层的分析器做词法分析和语法分析，明确任务，优化器进行优化，确定执行细节

然后由执行器去调用存储引擎的API，实现数据的获取。

1、查询缓存为什么被抛弃

在MySQL的早期版本，MySQL会先在“查询缓存”中查找，如果命中就直接返回结果，这个效率很高。

但MySQL 8.0版本彻底取消了这个功能，为什么？

因为查询缓存的失效非常频繁。

• 只要对一个表进行更新，那么这张表上所有的查询缓存都会被清空。
• 而平时的每次查询，如果没有命中缓存，还需要写入缓存。

这会导致一个问题，平时耗费资源写好的缓存，往往会被一条更新语句全部清空，得不偿失。

2、分析器

拿到一条SQL语句，MySQL需要先解析它，才能知道任务是什么。

分析器会先对它做“词法分析”，识别出里面的字符串分别是什么，代表着什么

随后就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断此SQL语句是否符合MySQL的语法。

简单来说，分析器会做这些事情：

• 判断语句是否正确
• 表是否存在
• 语句中的字段是否存在

3、优化器

经过了分析器，MySQL就已经知道了任务的内容，但在真正开始执行之前，还需要经过优化器的处理。

优化器主要的优化方面：

• 在表中存在多个索引时，选择使用哪个索引
• 一个语句中有多表关联（join）时，决定各表的连接顺序。

可以在SQL语句中显式地干预优化阶段，比如指定使用哪个索引，这样可以提高一些效率。

经过优化器之后，这条SQL语句的执行方案就确定下来了。

1、优化器选择索引的策略

考虑查询的字段有二级索引的情况。

优化器考虑的是，要查这么多数据，我是先走二级索引再回表扫描效率高，还是直接全表扫描效率高。

这个效率体现在需要扫描的行数，行数越少，那么IO操作就越少，效率也就越高

系统无法准确统计两种方式各自会扫描的行数，只能预测。

预测的策略是：根据索引的区分度。一个索引上的不同数值越多，说明区分度越高，走索引的收益就越大。

系统会抽样检测，预测索引的区分度。由于扫描相同的行数，二级索引需要额外的回表查询操作，所以一般索引的区分度小于一半，就会走全表扫描。

但是，由于系统是抽样检测，就很有可能错误估计索引的区分度，进而选择错误的执行方法。此时就会影响SQL语句的执行效率。

查看索引被计算出的区分度：show index from t;

重新计算索引的区分度：analyze table t;

4、执行器

执行器负责具体执行这条SQL语句。

开始执行之前，会先判断该用户对目标的表是否有操作权限。如果有权限，就打开表继续执行。

在打开表时，会根据表的引擎定义，去使用该引擎提供的接口。

比如要执行这条SQL语句：

select * from 'user' where age=10；

age字段没有索引，采用全表扫描。执行器的执行流程：

1. 执行器调用InnoDB引擎接口，读取这个表的第一行，判断age字段是不是10，如果不是则跳过，如果是则将这行储存在记录集中
2. 执行器调用InnoDB引擎接口，读取下一行，执行同样的逻辑，直到读取到整张表的最后一行
3. 执行器将所有满足条件的行组成的记录集，作为结果集返回给客户端

如果有索引，执行的逻辑也差不多：

• 第一次调用“取满足条件的第一行”接口
• 之后调用“满足条件的下一行”接口

这些接口都是存储引擎中定义好的，屏蔽了实现细节。

慢查询日志的rows_examined字段，表示的就是这个语句执行过程中，扫描了多少行。这个值是执行器每次调用引擎获取数据行时累加的。

有些场景，执行器调用一次，会在引擎内部扫描多行数据，所以实际的引擎扫描数，和这个rows_examined，有时并不是完全相同的。

比如执行范围查询时，存储引擎实际会多访问一条记录（超过范围的记录），而实际会返回给执行器两条记录，所以rows_examined为2

4、一条更新语句是如何执行的

更新操作的思想和查询不同，因为任何一次更新都会导致数据库的内容发生变化。

如果出现了误操作，MySQL可以立即恢复到“任何时间”任意一秒的状态，这是binlog日志实现的。

比如这样一条语句：

update 'user' set 'password' = 123 where id=2;

与查询语句一样，它会经过分析器进行分析，优化器决定使用主键索引，执行器负责找到这一行，更新数据。

与查询流程不同的是，更新操作涉及到两个重要的日志模块：binlog、redo log。

这条更新语句在执行器和InnoDB引擎中的具体流程：

1. 执行器调用引擎API，取到id=2的这一行。

• id是主键，引擎直接在主键索引中搜索
• 如果这一行的数据页在内存中（Buffer pool）存在，就直接返回给执行器
• 否则，先将数据页从磁盘中读入内存，然后再返回

1. 执行器拿到引擎返回的行数据，修改对应字段的值，得到新的一行数据，再调用引擎接口写入这行新数据
2. 引擎会将这行新数据更新到内存中
   • 同时将这个更新操作记录到redo log中，此时redo log处于“prepare”状态
   • 通知执行器，操作已完成，随时可以提交事务
3. 执行器生成这个操作的binlog，并把binlog写入磁盘
4. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log修改为“commit”状态，更新完成。

十一、MySQL 日志系统

1、详解 redo log

redo log是由InnoDB存储引擎提供的，用于服务事务的持久性。

它由两部分组成：

• 重做日志缓冲（redo log buffer），在内存中
• 重做日志文件（redo log file），在磁盘中

Redo log不是记录数据页“更新之后的状态”，而是记录这个页 “做了什么物理修改”，属于物理日志

它的作用是，如果MySQL丢失了在内存中的已经提交的事务数据，后续能通过redo log找回它们，持久化到磁盘。

1、数据库的更新操作流程

如果每一次更新操作都要写入磁盘，那么就必须在磁盘中找到该条记录，再去更新。这期间的查找成本、IO成本是很大的。

所以在更新操作时不会直接访问磁盘，而是先将操作写入内存中的redo log中，再更新内存Buffer Pool中的数据页。

在MySQL比较空闲的时候，会将更新的记录实际写入磁盘中（刷脏页）

当事务提交之后，会把所有修改信息都会存到该日志中。作用是：在刷新脏页到磁盘时如果发生错误，可以进行数据恢复，从而保证持久性

redo Log不会永久保存，隔一段时间会清理之前的老旧日志。因为事务一旦提交，它也就没有价值了

2、redo log的作用

如果没有redo log，可能产生的问题

InnoDB的持久化过程，是先将数据保存在内存缓冲，再通过后台线程定期将缓冲池内的脏页刷新到磁盘中，从而保证缓冲池的数据与磁盘一致。

问题在于，事务不知道这个过程。当事务提交后，数据其实保存在了缓冲池中，此时就会提示，事务操作成功。

但是，如果在后续的刷脏页过程中出现了问题，那么这个事务的数据就没有真正持久化保存到磁盘中，没有保证事务的持久性。

通过redo log，如何解决上面的问题

有了redo log后，对缓冲区数据页的修改操作，都会记录在redo log buffer中，具体记录的数据页的物理变化。

在事务提交时，会将redo log buffer中的数据记录在redo log磁盘文件中。如果一个事务成功提交，那么redo log肯定是已经刷新成功的。

该事务后续的刷脏页过程中，如果出现错误，就可以通过redo log进行数据恢复，由此保证了持久性。

如果脏页已经成功刷新到磁盘，那么redo log就可以被删除。

3、循环写入

InnoDB的redo log是固定大小的，从头开始写，如果写到了末尾，就回到开头循环写。

所以redo log不能持久保存，因为会不断覆盖旧值。不过redo log设计之初就不需要持久保存。

比如配置一组有4个文件：

• write pos：是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头
• checkpoint：是当前要擦除的位置，也是往后推移并且循环的，擦除记录前会把记录更新到数据文件

write pos和checkpoint之间的，是当前redo log空闲的部分。

如果write pos追上了checkpoint，此时就不能执行新的更新，必须等一些记录持久化到磁盘后，删除对应的redo log记录，腾出一些空间。

4、安全崩溃

有了redo log，InnoDB就可以保证，即使数据库发生异常重启，之前已经提交但尚未刷脏页的记录都不会丢失，因为对应的redo log已经持久化到磁盘中了。这个能力称为“crash-safe”。

5、redo log的两阶段提交

两阶段提交的具体过程：

• 引擎将新数据更新到内存数据页中
  • 同时将这个更新操作记录到redo log中，此时redo log处于“prepare”状态
  • 通知执行器，操作已完成，随时可以提交事务
• 提交事务
• 执行器把这个操作写入binlog
• 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log修改为“commit”状态，更新完成。

新语句执行时，将redo log的写入拆成了两个步骤：prepare和commit，这就是“两阶段提交”。

为什么设计“两阶段提交”

将redo log分成两阶段提交的意义，是为了让两份日志（redo log和binlog）之间的逻辑一致，避免使用binlog恢复数据时产生问题。

其实和事务的思想很类似：要么都成功，要么都失败，不能有中间状态（即一个成功一个失败）。

假设不使用两阶段提交，而是先写完一个日志，再写另一个，会出现什么问题？

比如要执行这条SQL语句，假设password原先的值是0

update 'user' set 'password' = 123 where id=2;

假设这条语句执行时，第一个日志已经写好了，但在写第二个日志的期间发生了崩溃：

• 如果是先写redo log，再写binlog：
  • 假设：redo log写完了，binlog还没有写完时，MySQL进程异常重启
  • redo log已经写好了，系统即使崩溃，也能把数据恢复，所以password 的值还是123
  • 但是，binlog中没有这条记录，因此之后备份日志时，存起来的binlog中就没有这条语句
  • 如果后续需要使用这个binlog来恢复数据库，由于这个语句的binlog丢失，恢复出来的数据中，这行记录的password为0，与原库的数据不符。
• 如果是先写binlog，再写redo log：
  • 假设：binlog写完了，redo log还没有写完时，MySQL进程异常重启
  • 由于redo log中没有存储这条记录，所以崩溃之后，这条事务无效，数据不会恢复，这行记录的password仍为0
  • 但是binlog中已经记录的这个日志，后续使用binlog来恢复时，就会多出一个事务，恢复出来的这行password为123，与原库的数据不符。

这个问题其实很严重。如果这样操作，那么binlog就是不可靠的，无法保证按照某一时刻的binlog恢复的数据，是否与当时的真实数据完全一致。

两阶段提交怎么解决这个问题

• 如果是redo log的prepare状态，redo log写好了，binlog写入期间发生了崩溃：
  • MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务
• 如果是redo log的prepare状态，redo log写好了，binlog也写好了，事务提交时发生了崩溃：
  • 此时不会回滚事务，因为虽然redo log是处于prepare阶段，但是能通过事务id找到对应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据。
  • 事务提交后，redo log就变为commit状态，所以数据库重启后，数据会恢复，而且binlog也写入了这条操作，没有问题。

注意：

• 如果一个事务包含多条SQL更新语句，那么redo log会源源不断地写入，状态都为prepare
• 只有提交事务时，才会写bin log，然后所有的redo log立即变为commit状态，之后事务成功提交。

6、redo log的落盘时机

在事务运行的过程中，MySQL会先将日志写到内存中的Log Buffer中，等到事务提交的时候，再把redo log buffer中的数据写入redo log中。

不过此时redo log依然是在内存中，没有持久化到磁盘上。

redo log什么时候会被持久化到磁盘上：

第一种情况：

• 设置innodb_flush_log_at_trx_commit参数
• 为1，每次事务提交时，直接将redo log持久化到磁盘，这是默认值
• 为0，每次事务提交时，只将日志信息留在Log Buffer中，不持久化到磁盘
• 为2，每次事务提交时，只将日志信息从Log Buffer中写入redo log中，不持久化到磁盘

第二种情况：

• 后台线程会每秒轮询一次，将Log Buffer中的数据写成redo log，持久化到磁盘，而不管事务是否提交。这是兜底的

第三种情况：

• Log Buffer占用的空间达到设定值的一半时，会将数据写入redo log，保存到内存中，腾出Log Buffer空间。但不会立即持久化到磁盘

为什么每次提交事务时，选择先持久化redo log，而不是立即刷新脏页？

在事务提交时，如果任何操作失败，那么事务就会失败。所以逻辑上讲，完全可以在事务提交时立即刷脏页，然后反馈脏页是否刷新成功。

这里涉及到效率问题：

• 在业务操作中，我们操作数据一般都是随机读写磁盘的，而不是顺序读写磁盘
• 而redo log在往磁盘文件中写入数据时，由于是日志文件，所以都是顺序追加的
• 磁盘顺序写入的效率要远大于随机写入。 这种先写日志的方式，称之为 WAL（Write-Ahead Logging）

2、详解 binlog

redo log是InnoDB引擎特有的日志，而MySQL的Server层也有自己的日志，称为binlog（归档日志）。

最开始MySQL没有InnoDB引擎，自带的引擎是MyISAM，但它不具备crash-safe的能力，binlog只用于归档，用于记录某一时刻的数据库状态，可以恢复数据。

binlog有两种模式：

• statement 格式：记sql语句，直接在空库运行即可恢复
• row 格式：会记录行的内容，会记两条，更新前和更新后都有，需要手动改写成SQL语句，再去执行

redo log和binlog的区别

• 提供者不同。redo log是InnoDB引擎特有的日志，而binlog是MySQL的Server层实现的，所有存储引擎都可以使用
• 日志内容不同。redo log是物理日志，记录的是“在某个数据页上做了什么修改”，而binlog是逻辑日志，记录的是这条语句的原始逻辑
• 写入方式不同。redo log是循环写的，设置了规定大小，可能使用完，而binlog可以追加写入，不会覆盖以前的日志
• 写入时间不同。redo log会在事务进行中不断写入，而binlog只在提交事务完成后进行一次写入。
• 作用不同。redo log主要用于确保数据最终能够持久化到磁盘中，binlog主要用于恢复此前的数据库记录。

sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘。

这个参数建议设置成1，这样可以保证MySQL异常重启之后binlog不丢失。

3、详解 undo log

undo log由InnoDB提供。

undo log是回滚日志，它记录了数据被修改前的信息 , 作用包含两个 :

• 提供回滚功能，保证事务的原子性
• 服务于MVCC（多版本并发控制）

undo log和redo log记录物理日志不一样，它是逻辑日志，记录的内容是和操作相反的，所以如果要回滚，直接执行undo log的语句即可。

比如，执行一条Delete语句，undo log会记录一条相反的Insert语句

当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

Undo log销毁：undo log在事务执行时产生。事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC。

Undo log存储：undo log采用段的方式进行管理和记录，存放在 rollback segment 回滚段中，内部包含1024个undo log segment。

4、刷脏页的时机

有4种场景：

• redo log写满了

  • 它的写入指针遇到了覆盖指针，意味着整个redo log对应的内容都还没有持久化到磁盘中

    此时就需要暂停其他的工作，用大量资源将redo log数据从内存缓冲区同步到磁盘中

    此时会导致正常的SQL语句执行很慢

• 脏页被内存淘汰

  • 如果一次查询很多数据，而且有很多数据不在内存缓冲区中，就需要申请内存，将磁盘的数据页加载到内存中

  • 如果内存不够用了，就必须淘汰一部分内存中的数据页，来存放新的数据页

    如果是干净页，就立即释放。如果是脏页，就必须先刷回磁盘中。

• MySQL认为系统空闲的时候

  • 系统空闲的时候，MySQL就会疯狂刷脏页
  • 在系统的高峰期阶段，如果内存足够用，可以每隔0.5秒向MySQL发送一个简单的查询，避免MySQL认为系统空闲，开始刷脏页

• MySQL正常关闭的时候

  • 进程关闭前，MySQL会将内存的所有脏页都持久化到磁盘中

十、存储引擎

1、存储引擎简介

存储引擎是存储数据、建立索引、更新/查询数据等技术的实现方式。

存储引擎是基于表的，而不是基于数据库的。所以，存储引擎也被称为“表的类型”

如果在建表时不指定存储引擎，默认使用InnoDB。（MySQL 5.5 之后默认是InnoDB，早先版本默认是MyISAM）

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

show engines;

每列的含义：引擎名称、是否被支持、注释、是否支持事务、是否支持XA协议、是否支持“保存点”。

常用的是：InnoDB、MyISAM、MEMORY

如何指定存储引擎

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

create table 表名{
	...
}engine=INNODB

2、InnoDB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎。

特点：

• DML操作遵循ACID模型，支持事务
• 支持行级锁，提高并发访问性能
• 支持外键约束，保证数据的完整性和正确性

文件：

• xxx.ibd：xxx是表名，InnoDB引擎的每张表都会对应这样一个“表空间文件”，存储该表的表结构（早先是frm，8.0 后在 sdi 存放）、数据、索引
• 有一个参数：innodb_file_per_table，含义是，是否为每张表单独对应一个表空间文件，默认是开启的（on）
• ibd 是二进制文件，无法直接查看。可以通过一个指令：ibd2sdi xxx.ibd，就能以json形式展示表结构。

InnoDB 存储结构

一行数据，对应一个 row

一个 page 是磁盘操作的最小单元，固定大小是16K

一个 extent，固定大小是1M，包含64个页

3、MyISAM

MyISAM是MySQL早期的默认存储引擎

特点

• 不支持事务、不支持外键
• 只支持表锁，不支持行级锁
• 访问速度快
• 崩溃后无法安全恢复
• 不支持MVCC

文件结构

• xxx.sdi：存放表结构信息，json格式
• xxx.MYD：存放数据
• xxx.MYI：存放索引

MyISAM压缩表

如果一张表在创建并导入数据后，不会再进行修改操作，这样的表就很适合采用MyISAM压缩表。

可以使用myisampack对MyISAM的表进行压缩，压缩后的表不能修改，除非先解除压缩，修改后再重新压缩。

压缩表的好处：

• 可以极大减少磁盘空间占用，因此可以减少磁盘IO，提升查询性能
• 每行记录是独立压缩的，读取单行记录不用解压整个表
• 压缩表也支持索引，不过索引也是只读的

4、Memory

Memory引擎的表数据是存储在内存中的，常作为临时表或缓存表使用，不能持久保存。

特点：

• 访问速度快
• 支持hash索引
• 对表的大小有限制，而且无法保障数据的安全性（内存断电丢失）

文件：

• xxx.sdi：存储表结构信息

5、InnoDB与MyISAM的区别

• 是否支持行级锁：
  • MyISAM只支持表锁
  • InnoDB支持表锁和行锁，默认为行锁
• 是否支持事务：
  • MyISAM强调性能，每次查询都具有原子性，执行速度更快，但不支持事务
  • InnoDB支持事务
• 是否支持外键：
  • MyISAM不支持
  • InnoDB支持
• 是否支持MVCC：
  • 只有InnoDB支持。应对高并发事务时，MVCC比加锁更高效
• 安全方面：
  • InnoDB具有崩溃修复能力，安全性更好
  • MyISAM崩溃后无法安全恢复

6、存储引擎的选择

存储引擎没有好坏之分，需要根据业务场景选择。

选择InnoDB：

• 对事务的完整性有要求
• 在并发条件下要求数据的一致性
• 除了插入和查询外，还包含很多的更新、删除操作，InnoDB的行级锁就很适合
• 大部分场景下选择InnoDB。

选择MyISAM：

• 以插入和查询操作为主，只有少量的更新、删除操作
• 对事务的完整性、并发性要求不高
• 非核心业务适合使用
• 不常用，可用MongoDB替代

选择Memory：

• 用作临时表或缓存
• 不常用，可被Redis替代

混合使用多种存储引擎应该考虑的问题：

• 事务。支持事务的存储引擎能够回滚，但不支持事务的存储引擎不能够回滚，可能产生数据不一致问题。
• 备份。混合存储对一致性备份带来了困难
• 崩溃恢复。MyISAM崩溃后发生损坏的概率很高，而且恢复速度也慢
• 存储引擎的特性。

十一、索引

sql优化，主要就是针对索引进行优化的。

1、概述

索引（index）是帮助MySQL高效获取数据的数据结构。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，它们以某种方式指向数据，可以在这些数据结构上实现高级查找算法。

这种数据结构就是索引，它是有序的。

如果没有索引，在执行这样一条sql语句时：

select * from user where age = 45;

会逐行匹配，找到符合条件的数据（此时也不会停下，直到扫描完整张表，因为可能有多个匹配的数据）。这样效率很低，属于“全表扫描”。

在有索引时，会在索引结构上查找，效率很高。

索引结构是由存储引擎实现的，对MySQL的执行器屏蔽了细节。所以同样的数据结构，不同引擎的实现细节可能不同。

2、索引的优缺点

优点：

1. 索引大大减少了服务器需要扫描的数据量
2. 索引对数据进行排序，避免产生临时表，降低数据排序对CPU的消耗
3. 索引可以将随机IO变为顺序IO

缺点：

1. 索引额外占用了一定的空间
2. 索引会降低更新表的效率，因为在改变数据时还要维护索引。

3、索引的结构

索引是由存储引擎实现的，不同的存储引擎有着不同的索引结构。主要包含以下几种：

• B+Tree索引：最常见，大部分引擎都支持
• Hash索引：底层数据结构使用哈希表实现，性能很高，但只支持精确匹配，不支持范围查询
• R-Tree（空间索引）：是MyISAM的一个特殊索引类型，主要用于存储地理位置
• Full-text（全文索引）：建立倒排索引，快速匹配文档

支持情况：

4、有序数组 索引

其实也可以使用有序数组来充当索引，它有这几个好处：

• 等值查询时，可以使用二分查找，效率也很高，时间复杂度O(logn)
• 范围查询也很有优势，只需要找到最左边的元素，然后向右遍历，直到超出范围即可。

缺点：

如果仅仅看查询效率，有序数组就是最好的数据结构了。

但是，在需要更新数据的时候就麻烦了，往中间插入一个记录就必须得挪动后面所有的记录，成本太高

所以，有序数组索引只适用于静态存储引擎，它只需要做一些查找操作，那还是很不错的

5、B+Tree 索引

1、如何考虑索引的数据结构

SQL对数据有这些常见的操作：排序、查找、增删改、区间查找等。

• 涉及到查找，显然链表结构就不适合，因为链表的查找效率是O(n)，非常慢
• 涉及到增删，显然数组结构也不适合，因为数组进行增删需要进行大量的元素复制搬移操作
• 所以树结构比较适合，应该选用平衡的二叉排序树，但需要进行一些优化

为什么不用二叉树排序树

1. 可能会退化成链表，效率极低
2. 大数据量情况下，层级较深，检索速度慢，需要多次I/O。

为什么不用AVL树

AVL树虽然不会退化成链表，但插入数据涉及到自旋操作，效率较低，而且层级很深，需要多次I/O。

为什么不用红黑树

红黑树不会退化成单链表，效率也还可以，但每个节点容量小，层级还是太深。

关于树的层级问题

如果树存储在内存中，那问题不大。但是索引结构是需要持久化到磁盘中的，这时树的结构就很重要了。

比如有一颗100万节点的平衡二叉树，树高20，那么最坏的情况下，查找一个数据需要找到20个节点。

这20个节点在磁盘中是散乱存放的，最坏的情况，每个节点都在一个不同的磁盘块中，而从机械硬盘中读取一个数据块需要10ms的寻址时间。

如果查询一行记录需要访问20个磁盘块，就至少需要耗时200ms，非常耗时。

所以，树的高度越小，可以使得每次查询需要使用到的节点越少，需要访问的磁盘块也越少，那么磁盘IO的耗时也就越小，查询越快。

而且树的高度降低也并不会影响任何操作效率，所以树肯定是越低越好

2、B-Tree

于是设计了B-Tree：多路平衡查找树，是为磁盘等外存储设备设计的一种平衡查找树。

它的主要特点是：

• 一个节点可以有多个子节点，相同的数据量下减少了层数。
• 每个节点占用一个盘块的磁盘空间

B 树被作为实现索引的数据结构被创造出来，是因为它能够完美地利用“局部性原理”

和两个机制有关

1. 磁盘预读：
   • 磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据
   • 如果未来要读取的数据就在这一页中，可以避免未来的磁盘 IO，提高效率
2. 局部性原理
   • 软件设计要尽量遵循“数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘 IO

3、为什么使用B+Tree

但最终使用了B+Tree，B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构。

1、B+Tree 的特点

• 所有的元素都存储在叶子节点。上面的父节点只起到索引的作用，不存储数据，所以索引占用的空间也不是很大，允许保存在内存中
• 叶子节点之间存在一条单链表，每个节点会指向下一个节点元素，可以实现快速的区间查找
• 逻辑上相当于给一条单链表建立了树状索引，可以快速定位到链表的某个区域
• 叶子节点存储了索引+数据，因此所有的非叶子节点都是冗余节点，用空间换时间

这些特点带来的好处：

• B+树中根到每一个节点的路径长度一样，而 B 树不是，所以B+树的查找效率稳定在O(logn)，而B-树的查找效率最好是O(1)
• 范围查找，定位 min 与 max 之后，中间的叶子节点就是结果集，不用中序回溯。这是B+树最大的优势（得益于单链表）
• 叶子节点存储实际记录行，是紧挨着存储的，适合大数据量磁盘存储。非叶子节点存储记录的Key，用于查询加速，适合内存存储。
• 非叶子节点不存储实际记录，而只存储记录的 KEY 的话，那么在相同内存的情况下，B+树能够存储更多索引， 提高在索引上的搜索效率

2、量化说下B+树的高度

InnoDB 存储引擎中页的大小为 16KB，索引页只存放一条记录的主键id和指针。一般表的主键类型为 INT（占用 4 个字节）或 BIGINT（占用 8 个字节），指针类型也一般为 4 或 8 个字节，也就是说一个页（B+Tree 中的一个节点）中大概存储 16KB/(8B+8B)=1K 个键值。

一个深度为 3 的 B+Tree 索引可以维护 10^3 _ 10^3 _ 10^3 = 10 亿条记录。实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree 的高度一般都在 2~4 层。

MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要 1~3 次磁盘 I/O 操作。

3、总结 B+Tree 的优点

1. 适合磁盘存储，充分利用磁盘预读、局部性原理
2. 树的高度很低，每个节点能存储大量数据
3. 索引本身占用内存很小
4. 能够很好地支持单点查询、范围查询、有序性查询

4、为什么不用 B-Tree

1. 对于B-Tree，每个节点需要存放数据，而每页的容量是有限的，每个节点只能存放较少的指针。在相同数据量的情况下，只能增加树的高度，导致效率降低。而B+树可以存放很多指针，降低了层数。
2. B+树的单链表结构，便于范围搜索和排序，更加优秀。

4、MyISAM 的B+树实现

在MyISAM中，叶子节点存储的是数据的物理地址，相当于二级索引，但是不用根据id回表查询，可以直接获得数据

它的索引文件和数据文件是分离的。

MyISAM使用了前缀压缩技术，使索引更小

5、InnoDB 的B+树实现

InnoDB 的数据文件本身，就是索引文件（指的是主键索引），主键索引树的叶子节点直接存储的是行数据，所以更加高效。

注意，正因如此，InnoDB中不走索引时的“逐行扫描”，其实是在遍历聚集索引B+树。

• InnoDB的二级索引存储的是主键值，需要回表查询才能获得完整的数据。
• InnoDB的B+树，叶子节点之间是双向链表

6、Hash 索引

使用一个哈希算法，通过字段值得到哈希值，映射到对应的槽位上，存储在哈希表中。

查询/插入/修改/删除的平均时间复杂度都是 O(1)，适用于单行查询。

1、Hash 索引的特点

• 只能用于精确查询，不支持范围查询
• 无法通过索引完成排序操作
• 查询效率很高，只需要一次检索（无哈希冲突时），比B+树快。

在InnoDB中，有一个“自适应Hash”功能：根据B+树索引，在指定条件下自动构建Hash索引

2、Hash索引与B+树索引的区别

• B+树支持范围查询，Hash索引不行
• B+树对于联合索引支持最左前缀法则，而Hash索引不支持，必须完全覆盖索引
• B+树支持排序，Hash索引不支持
• Hash索引在等值查询时效率比B+树索引高
• Hash索引不支持模糊查询

3、Memory的哈希索引

只有Memory引擎显式支持哈希索引，哈希索引是Memory的默认索引。

Memory支持非唯一哈希索引，如果多个列的哈希值相同，索引会以链表的方式存放在同一个哈希条目中。

7、索引的分类

1、概述

• 唯一索引：索引字段的值必须唯一，允许有null值。在给某个字段添加“唯一约束”时，会自动为这个字段创建“唯一索引”。
• 主键索引：特殊的唯一索引，不允许null值。会根据主键自动创建
• 联合索引：将多个字段联合起来，作为一个联合索引，有着严格的顺序

在InnoDB中，根据索引的存储形式，又可以分为以下两种：

• 聚集索引：索引结构的普通节点只保存主键值，叶子节点保存了行数据。
• 二级索引：数据与索引分开存储，索引结构的普通节点存储索引列的值，叶子节点存储的是对应数据的主键值。

2、聚集索引

1、概述

聚集索引并不是一种索引类型，而是一种数据存储的方式。InnoDB的聚集索引是指，在同一个结构中保存了B+树索引和表数据。

表数据存放在叶子节点中，普通节点只包含了索引列，一般是主键。

聚集的含义是，数据行和相邻的键值连续存储在一起。但InnoDB只会将同一个页中的记录连续存储，所以相邻的记录如果在不同的页中，可能物理存储隔得很远。

由于一个表数据只能有一种物理存储方式，所以一张表只能有一个集聚索引。

2、聚集索引的选取规则

• 如果定义了主键，主键索引就是聚集索引
• 如果没有定义主键，会使用一个唯一的非空索引作为聚集索引。不应该为经常更新的列创建聚集索引。
• 如果没有主键，也没有唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

3、聚集索引的优缺点

优点：

• 数据行连续存储，只需要从磁盘读取少量的数据页就能实现一段数据的查找
• 数据访问更快，因为索引和数据都保存在一个结构中，顺着索引能直接找到数据

缺点：

• 插入速度严重依赖于插入顺序，因为如果是随机插入数据，可能会造成频繁的页分裂，会占用更多的磁盘空间，产生碎片
• 更新聚集索引列的代价很高，因为聚集索引的索引列有严格的顺序
• 在行比较稀疏，或者由于页分裂导致数据存储不连续时，聚集索引会导致全表扫描变慢。因为全表扫描就是在遍历聚集索引
• 二级索引会更大，因为二级索引的叶子节点必须包含该数据行的主键
• 二级索引必须回表查询才能获得全部的行数据

3、二级索引

二级索引的叶子节点包含主键列，所以如果主键列长度很大，那么其他的所有索引都会很大。所以如果表上的索引很多，主键长度应该尽可能小。

注意，InnoDB的二级索引，叶子节点保存的是该记录的主键值，而不是该行数据的物理位置。

所以，二级索引必须回表查询才能获得全部的行数据

为什么这么设计

因为聚集索引会发生页分裂或者页合并，会导致数据行的物理位置发生变化。

让二级索引保存索引值而不是该行数据的物理地址，这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作，因为二级索引只存储主键，相当于一种屏蔽差异。

4、回表查询

在聚集索引中，B+树上会存储这一行的全部数据，但是非聚集索引只会存储该列对应的值和相应行的主键。

是不是聚集索引的定义与主键索引很像？

其实就是的，当我们没有定义主键索引时，MYSQL会指定从左到有的第一个加了唯一索引和非空约束的列建立聚集索引，用它来代替主键索引。

聚集索引≈主键索引=唯一性约束+非空约束

那我们说了这么多？到底什么是回表查询呢？

比如有一张表，内容是id、username、xxx，其中id是主键，构建了聚集索引；username构建了二级索引

select * from user where username = 'abc';

会先去二级索引中根据username查找，找到对应的id；再去聚集索引中根据id查找，获得具体数据。

这种先二级索引获取id，再聚集索引获取行数据的操作，称为“回表查询”。

示例

还是上面的情况，有两条语句：

select * from user where id = 10;

select * from user where username = 'abc';

显然是第一条效率更高，因为直接聚集索引中查询即可。语句2会进行回表查询。

5、唯一索引和主键索引的区别

唯一索引是一张表上的一个或多个字段建立的索引，这几个字段组合的值在整张表中不能重复。一张表能建立多个唯一索引，但一般只建立一个。允许值为null

主键索引是特殊的唯一索引，不允许值为null。一张表最多有一个主键，也可以没有主键。

6、唯一索引和普通索引的区别

唯一索引和普通索引都属于二级索引，在没有覆盖索引的情况下都需要回表查询，才能获得完整的数据。

唯一索引是为表的字段建立唯一约束时产生的，因此，该列上的所有数据都是唯一的，不能重复。而普通索引没有这个约束。

因此：

• 在唯一索引上查找到满足条件的第一个记录后，就会停止搜索
• 在普通索引上查找到满足条件的第一个记录后，如果语句中没有加limit 1，还会继续寻找下一条记录

不过这不会造成性能差距，因为InnoDB是按页读写的，下一条记录一般也会包含在该页中，无非是增加了移动一次指针的开销。

但大多数时候，要尽量避免使用唯一索引。

因为真正会产生性能差距的，是由于InnoDB内存结构中的insert buffer和change buffer的存在，因为它们只适用普通索引。

平时的插入过程，如果目标页不在缓冲区中，那么也不会直接插入到磁盘，因为这属于离散IO，效率非常低。

它会将数据先插入内存中的insert buffer中，等下次查询用到这个数据时，再去将它合并到索引中，这样的效率就很高。

对待更新和删除操作也是一样。不过，这样的优化只适用于普通索引，因为唯一索引必须保证数据的唯一性，就必须及时去索引中检查，而不能先暂存在内存缓冲中。

所以，在写多读少的场景，页面刚写上就被访问的可能性较小，所以普通索引的这种优化策略会大大提高效率。

如果需要保证数据的唯一约束，可以考虑在业务层面保证，而不是数据库层面。

8、索引的语法

创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name, ...);

如果不加创建什么索引，则创建的是常规索引。后面指定表和字段，一个索引可以关联多个字段。

• 单列索引：一个索引只关联了一个字段
• 组合索引：一个索引关联了多个字段（字段顺序很重要）

注意：

• 索引的名称一般的规则是：idx_表名_字段名
• InnoDB，默认索引结构是B+树，不用显式指定
• 如果字段是不重复的，就创建唯一索引。如果允许重复，就创建常规索引

查看索引

SHOW INDEX FROM table_name;

查看指定表的全部索引

删除索引

DROP INDEX index_name ON table_name;

删除某张表的某个索引

9、索引的使用

1、验证索引的效率

搞一个200w数据的表：

直接查询：

SELECT * FROM tb_sku WHERE price = '77607';

price字段不带索引，查询了2.199s

给price添加索引：

CREATE INDEX idx_sku_price ON tb_sku(price);

加索引的过程也比较耗时，需要给200w条数据构建B+树

查看当前表的全部索引：

SHOW INDEX FROM tb_sku;

添加索引之后，再执行相同的查询：

SELECT * FROM tb_sku WHERE price = '77607';

只花了0.014秒，效率极大提升。

查看细节：

EXPLAIN SELECT * FROM tb_sku WHERE price = '77607';

可以看到，使用了刚才建立的索引。

2、索引失效的几种情形

1、最左前缀法则

如果创建了联合索引（一个索引关联了多个字段），要遵守最左前缀法则。

最左前缀法则：

• 查询会从索引的最左列开始，并且不跳过索引中的列。
• 如果查询条件中，最左边的列不存在，索引将全部失效！
• 如果查询条件中，中间跳过了某一个索引列，索引将部分失效！（只有最左前缀列的索引有效，后面字段不走索引）
• 只要查询的条件包含联合索引的全部字段，不管查询的参数顺序，都会全部走索引。

图解：

比如，为name、age、years字段创建了联合索引：

• 查询全部的索引字段：
  • select * from xxx where name = x and age = x and years = x;
  • 把联合索引的每一列都用上了，效率就很高
• 忽略右边的索引字段，保留左边，且中间不跳过：
  • select * from xxx where name = x and age = x;
  • 没有查询索引的最右边一列，满足两个条件：1.使用了最左边的列 2.中间没有跳过列，那么不会影响索引的效率，索引的长度会变短
• 忽略最左边的索引字段：
  • select * from xxx where age = x and years = x;
  • 这种情况不走索引，使用的是全表扫描
• 跳过中间的列：
  • select * from xxx where name = x and years = x;
  • 这种情况会走索引，但只有左边的列走索引，被跳过的列后面的列，不走索引！

最左前缀法则的原理

在建立索引时，如果指定了多个字段，比如A、B、C。那么在排序时，就会先根据A，再根据B，最后根据C。

这也就导致：

• 如果A不一致，就按照A从大到小排序。如果A一样，就按照B从大到小排序。B和C同理。
• 如果忽略A来看B的话，那么树结构中的B，大小关系是不可靠的，必须在A的基础上，才能判断B。这是“不能忽略左边的列”的原因
• 同样，如果只根据A和C的话，那么C其实也是不可靠的，必须通过B来判断C。这是“不能跳过中间的列”的原因。

图解：

可以看出，确实是满足最左前缀，才能在索引上正确查询。

由于最左前缀法则，在建立联合索引时，需要仔细考虑字段的顺序来提高索引的复用性。

比如，对name和age都有频繁的查询需求，而且它们会被联合查询，就可以考虑建立这样的索引：

• （name、age）
• （age）

这样就省去了单独建立name索引的必要。

2、范围查询

联合索引中，如果出现范围查询（>或<），则范围查询右侧的列，索引失效。

解决方法也很简单：尽量将<改成<=，>改成>=，就可以使用全部的索引

3、索引列做运算

如果在索引列上进行运算操作，索引将失效。（不管索引类型如何，都会失效，单列索引也会失效）

price是索引列，进行分隔字符串的运算：

EXPLAIN SELECT * FROM tb_sku WHERE SUBSTRING(price, 5, 2) = '77';

结果没有走索引。

4、字符串不加引号

查询字符串类型的字段时，如果不加单引号，会导致索引失效。

比如：

EXPLAIN SELECT * FROM tb_sku WHERE `num` = '111'; # 走索引

EXPLAIN SELECT * FROM tb_sku WHERE `num` = 111; # 不走索引

注意：varchar类型，如果查询条件包含中文，不写单引号会报错

这种不走索引的特点是：MySQL认为，可能走的索引是它的索引，但实际走的索引是null，方式是全表扫描。

5、头部模糊查询

如果仅仅是尾部模糊查询，索引不会失效。但如果存在头部模糊查询，则索引失效。

• 尾部模糊：‘软件%’ 走索引
• 头部模糊：‘%工程’ 不走索引，全表扫描
• 两边模糊：‘%xxx%’ 不走索引，全表扫描

解决方案：大数据量的情况下，避免使用头部模糊匹配！

6、or连接的条件

用or分隔开的条件，如果or前的条件中的列有索引，而后面中的列没有索引，那么涉及到的索引都会失效。

（注意：如果后面的列在联合索引中，但它不是最左边的索引，那么它的索引会失效，也相当于没有索引）

比如：

select * from xxx where id = 10 or age = 20;

id有索引，age没有索引，则这条SQL会全表扫描，不会使用id的索引

解决方案：如果真的有需求，就给后面的字段也单独建立索引。

7、数据分布影响

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

这种场景也很常见：

• 比如针对有索引的字段做条件查询，但查出来的结果几乎是整张表，那么MySQL就会走全表扫描，不走索引。
• 相应的，如果查询的结果只占全表的一小部分（不超过一半），MySQL就会走索引。
• 所以，最终走不走索引，取决于当时数据库的数据分布情况（如果查询的结果超过全表数据的一半，就不走索引，反之就走索引）

索引的意义是精确匹配，匹配的结果过多，效率也不高，所以这个做法很合理。

3、更好地使用索引

1、显示声明使用哪个索引

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

在表名后面书写索引的声明：

• use index，声明，建议用哪个索引查询（如果指定的索引不是很好，MySQL不会采纳）

select * from xxx use index(索引名) where ...

• ignore index，声明，不要用哪个索引查询

select * from xxx ignore index(索引名) where ...

• force index，声明，必须用这个索引查询

select * from xxx force index(索引名) where ...

2、覆盖索引

1、概述

在查询中，尽量使用覆盖索引，减少使用select *

覆盖索引：查询时使用了索引，并且需要返回的列在该索引中能够全部找到。简单来说就是，让查询结果全面覆盖索引的字段

这也很好理解：如果查询的结果能在索引上全部找到，就可以直接返回，不需要进行回表查询。

MySQL执行计划的 extra部分，也能体现出来：

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

比如，有这几个字段：id、name、age、address，id是主键自带索引，name和age建立了联合索引

• 如果查询条件只有id，name，age 那么覆盖了索引，效率很高（联合索引的叶子节点存储了id这个主键数据，所以它们可以在一个索引上查出来）
• 如果查询结果写成 * ，那么需要回表查询出address，效率较低。（通过二级索引查出id，再去聚集索引中查询address字段）

解决方案：尽量让查询的结果覆盖索引中。可以从两个方面考虑：

• 修改查询结果，减少几个字段，实现覆盖索引
• 为需要的字段专门建立联合索引

注意：联合索引属于二级索引，叶子节点存储了id，所以不用把id也加入联合索引的字段中。

2、覆盖索引的好处

• 索引条目的大小通常远小于数据行的大小，所以如果能够只读取索引，就能极大减少数据访问量，也能减少IO
• 在单个页中，索引是按照索引列的值顺序存储的，所以范围查询是顺序访问，效率很高
• 对于InnoDB，覆盖索引可以避免回表查询
• 极大提高排序的效率。因为如果索引中不能获得全部的数据，就不得不每扫描一条记录就回表查询一次，这种基本都是随机IO

3、能使用覆盖索引优化的场景

• 列查询会回表查询的场景，可以建立联合索引，省去回表查询
• 经常全表count查询的字段，最好建立索引
• 分页查询的查询列，最好覆盖索引

3、前缀索引

当字段类型为字符串（vachar、text等）时，为它建立索引：

• 如果内容很长，比如一篇十万字的文章，就需要索引很长的字符串，导致索引变得很大。
• 查询时，会浪费大量的磁盘IO，影响查询效率。
• 可以使用前缀索引解决，思想是，将字符串的一部分前缀抽取出来，建立索引。

语法：

create index 索引名 on 表名(字段名(前缀长度));

这个前缀长度，表示提取这个字段的前多少个字符串构建索引。

如何确定，什么样的前缀长度效率最高？

可以根据索引字段的选择性来决定：选择性是指，不重复的索引值（基数）和数据表的记录总数的比值。

这个比值越大，说明重复数据越少，索引选择性越高，则查询效率越高。重复数据少，说明可以使用更小的前缀，来完成更快速的判断。

唯一索引的选择性是1，这是最好的索引选择性，效率也最高。

确定索引的选择性

公式：索引的选择性 = 不重复的记录数 / 总记录数

比如要给email字段建立索引：

select count(distinct email) / count(*) from tb_user ;

上面可以得出，整个字段的重复性。

比如我想选择前缀是5，可以验证一下，使用选择的前缀长度截取的该字段选择性是多少。如果比值挺大，就继续缩短。

select count(distinct substring(email,1,5)) / count(*) from tb_user ;

如果选择性差不多，那么肯定是越小的前缀和越好，因为建立的索引占用的空间更小。

前缀索引的查询顺序

一张表，有一个聚集索引，建立了一个二级索引（email前缀索引，前缀长为5）。

如果要执行这样一条SQL：

select * from xxx where email = 'xxxxxxxxxxxx@xx.com';

• 在二级索引中，匹配xxxxxxxxxxxx@xx.com的前缀
• 匹配到前缀，获取到id
• 回表查询，在聚集索引中查出全部数据
• 此时不能直接返回，而是先从数据中取出email字段的完整值，与查询条件进行比较，比对成功后才会返回。

4、单列索引与联合索引

在MySQL中，有这么一个情况：name、age字段都分别建立了单列索引，但执行下面的SQL：

select * from xxx where name = xx and age = xxx;

只有name会走索引，age不会走索引，那么必然涉及到回表查询。

而如果name和age建立了联合索引，则没有这个问题，会直接走联合索引。

联合索引的查询顺序：

解决方案：

• 在业务场景中，如果存在多个查询条件，应该建立联合索引，而不是分别建立单列索引。
• 如果有的列已经存在单列索引，可以在SQL语句中指定使用联合索引，避免出现选择问题

5、索引下推

这是MySQL的自动优化策略。

比如这样一条SQL：

select name, age from user where name like '张%' and age = 10;

其中，（name，age）建立了联合索引。

由于最左前缀法则，所以查询 name like ‘张%’ 时，可以走索引，找到第一条满足 name like ‘张%’ 条件的记录。

在MySQL 5.6之前，只能从该记录开始，依次根据id回表查询，找到对应的行数据，再判断条件是否符合。

但其实，索引中也是包含age字段的。

在5.6之后，引入了“索引下推”优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

结合上面的情形简单来说，在一个节点回表之前，会先判断索引中的age字段是否满足条件，如果满足再去回表查询。

6、利用组合索引

有些时候，如果不得不建立一个前缀选择性较低的索引，比如（sex, country）。后续查询country时，根据最左前缀法则，不会使用这个索引。

但是，可以在查询条件中新增and esx in(‘m’, ‘f’)，来使用到该索引。

4、索引的设计原则

1. 针对数据量较大（超过几十万），且查询非常频繁的表建立索引。
2. 针对常作为查询条件（where）、排序（order by）、分组（group by）操作的字段，建立索引
3. 尽量选择区分度高的列作为索引。区分度越高，索引效率越高
4. 对于字符串类型的字段，如果字段的内容很长，优先考虑建立前缀索引
5. 尽量使用联合索引，减少单列索引。联合索引很多时候可以覆盖索引，节省存储空间，避免回表查询
6. 要控制索引的数量，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
7. 如果索引列不能存储NULL值，建议在表结构中使用NOT NULL约束它，这样有利于优化器确定哪个索引可以最有效地查询
8. 更新特别频繁而且查询相对较少的列，不建议建索引，因为每次增删改都需要维护索引结构

5、冗余索引

MySQL允许在相同的列上创建多个相同的索引，这会影响性能，不应该这样做。原因：

• 每次对索引列的操作，需要额外维护冗余的索引
• 增大了优化器在查询优化时选择索引的成本

如果索引的类型不同，不属于冗余索引。

冗余索引通常会在这些场景下被创建：

• 在表中为已经存在索引的列指定唯一约束，不过这种不能算做冗余索引，这个索引的目的就是唯一约束
• 为主键列创建索引。主键本来就有聚集索引
• 为表添加新的索引。比如原本有一个索引a，新增了联合索引(a, b)。
• 或者扩展索引为(c, id)。id是主键，已经存在于二级索引的叶子节点，所以也属于冗余

大多数情况，应该考虑扩展索引，而不是建立冗余索引。但如果扩展索引会导致效率降低，那就建立冗余索引。

十二、SQL 优化

1、插入数据

1、insert 优化

如果需要一次性向数据库中插入多条数据，可以从以下三个方面进行优化

1、批量插入数据

每条单独的insert，都会与数据库建立连接，性能较低。所以，建议用一条SQL语句完成多条记录的插入。

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

注意，一条SQL中不建议包括超过一千条数据。如果要批量插入海量数据，可以分成多条批量insert。

2、手动提交事务

# 开启事务
start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
# 提交事务
commit;

如果要分成多个insert，建议手动控制事务，避免事务的频繁开启与提交。

3、主键顺序插入

主键顺序插入（从小到大插入），性能要高于乱序插入，因为主键乱序插入会触发页分裂，比较耗费性能。

2、大批量导入数据

如果需要一次性插入大量的数据（比如几百万的记录），那么使用insert语句的插入性能较低，建议使用MySQL提供的load指令进行插入

load指令，可以直接把本地的数据加载到MySQL表中。

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p

-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

-- 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields
terminated by ',' lines terminated by '\n' ;

2、主键优化

为什么主键顺序插入的效率更高？

因为乱序插入会导致页分裂，效率低

1、主键设计原则

1. 尽量减少主键长度。因为二级索引的叶子节点中存储了记录的主键，主键长度越小，占用磁盘空间越少
2. 插入数据时，尽量选择顺序插入，选择自增主键
3. 尽量不要使用UUID，或者其他自然主键，比如身份证号，因为这些数字是乱序的，而且比较长
4. 业务操作时，尽量避免对主键的修改

2、为什么不要用UUID作为主键

• UUID太长，主键值如果太长，由于二级索引的叶子节点会存储主键值，所以导致索引占用的空间增大
• UUID是随机的，不会趋势自增，会频繁触发页分裂，造成空间碎片，索引占用的空间也会增大
• 主键在聚集索引上会连续存储，如果一个UUID应该存储的页不在内存中，就不得不产生大量的随机IO去找到那个页

3、顺序的主键可能带来的问题

在高并发场景下，如果按主键自增顺序插入，会有一些问题：

主键的上界会成为插入热点，因为所有的插入都发生在这里，这会导致该间隙锁的竞争十分激烈

3、order by 优化

1、两种排序方式

MySQL的排序有两种方式，能在Extra中得知：

• Using filesort：
  • 通过表的索引，或全表扫描，读取满足条件的数据行，然后在排序缓冲区（sort buffer）中完成排序操作
  • 如果排序缓冲区满了，就会在磁盘中进行排序，效率很低
  • 所有不是通过索引直接返回排序结果的，都叫FileSort排序
• Using index：
  • 通过有序索引顺序扫描，直接返回有序结果，效率更高

在做排序操作时，尽量优化成Using index方式。

2、多种索引排序方式

索引升序，结果升序

比如，给age、phone建立联合索引。默认的索引是升序的

create index idx_user_age_phone_aa on tb_user(age,phone);

此时对age、phone进行升序排序，可以直接返回索引的有序结果，效率很高，因为索引也是升序结构

explain select id,age,phone from tb_user order by age , phone;

索引升序，结果降序

但是，如果对age、phone进行降序排序

explain select id,age,phone from tb_user order by age desc , phone desc ;

那么Extra中会出现两个属性：

• Using index
• Backward index scan，代表“反向扫描索引”，因为索引结构是升序的，而要求结果是降序的。

如果某些字段的业务就是大量的降序排序，那么可以给它创建降序索引，就别用普通的升序索引了

索引升序，结果升序，但不满足最左前缀

创建索引时，是age、phone，而如果先根据phone，再根据age来排序

explain select id,age,phone from tb_user order by phone , age;

则排序方式是Using filesort，排序的优先顺序必须和索引的优先顺序一样，才能使用索引

索引升序，结果一个升序一个降序

explain select id,age,phone from tb_user order by age asc , phone desc ;

Extra的内容：Using index，Using filesort。效率不如直接用索引高。

解决这个问题，可以针对要求的排序方式，建立一个索引：age升序排序，phone降序排序

create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

在这样的索引上，执行对应的排序，就是完全走索引。

注意：同样的字段，可以根据每个字段的排序方式不同，建立不同的联合索引。在排序时，MySQL会自动选择最合适的索引。

3、order by 优化原则

1. 根据排序的字段，建立合适的索引。注意：多字段排序时，也遵循最左前缀法则
2. 尽量覆盖索引
3. 多字段排序，如果有的升序有的降序，可以为其“定制”索引规则。
4. 如果不可避免地使用filesort，在大数据量时，可以增大排序缓冲区大小（sort_buffer_size），默认256k，避免在磁盘中排序

4、group by 优化

分组查询，就是根据某个字段进行的数据过滤或统计。

如果没有索引，默认的分组查询方式是Using temporary（使用临时表），效率很低

要使用索引，就必须给group by的字段建立索引，注意，它也遵循最左前缀法则。

1、group by 优化原则

1. 给分组的字段建立索引
2. 注意最左前缀法则

5、limit 优化

数据量比较大时，进行分页查询，则越往后，查询效率越低！

• limit(0, 10)，效率很高
• limit(1000000, 10)，效率极低

问题描述

如果数据量少，offset和limit是没有问题的。

但是，当数据库里的数据量超过服务器内存能够存储的能力，并且需要对所有数据进行分页，就存在一个问题。

比如需要查询 offset 50000 的记录，limit 100行，则需要先查出5万条记录，然后获取需要的100行，其余的几万条记录丢弃掉。

也就是说，OFFSET 越高，查询时间就越长。

如何解决

优化思路：使用覆盖索引+子查询的方式

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

相当于是把查询出来的许多id看做一张表，通过多表联查解决问题。因为MySQL不支持在join in后有limit的操作。

数据量很大时，还是很慢，不过比原始的limit要快很多

基于指针的分页

比如要需要第50000条之后的10条记录，可以这样做：

select * from tbl where id > 500000 limit 10;

这样，显式告诉数据库需要从第几行开始查找，就不需要从头开始全表扫描，效率很高。

局限性是，这样的分页方式需要每行维护一个唯一的升序序号，比如自增id或时间戳。

6、count 优化

这是一个聚合函数，用来求符合条件的总数据量。如果数据量很大，那么count操作很耗时。

• MyISAM把一张表的总行数存放在了硬盘上，所以执行count(*)时可以直接读取，效率很高。但遇到有条件的count也没办法
• InnoDB更麻烦，它执行count(*)时，需要把数据一行一行从引擎中读出来，然后进行计数。

count目前没有特别好的优化策略，如果要优化InnoDB的count(*)，可以使用Redis自己计数。带条件的count没法处理

哪种count()性能最好

count()的含义是，统计符合查询条件的结果中，不为null的有多少条记录。

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count()，所以尽量使用 count()

count()的执行过程：

• server层会维护一个count变量，从存储引擎读取一条记录，如果指定的列不为null，就将count+1，直到符合要求的记录都被读取完
• 如果是count(主键)：
  • 如果没有二级索引，就遍历聚集索引，否则改为遍历二级索引。因为二级索引的叶子节点更小，IO成本低。
  • 存储引擎把每条记录的主键返回给server层，server层将count+1
  • 不会判断值是否为null，因为主键不会为null。不过涉及到读取具体值，所以效率比较低
• 如果是count(1)：
  • 和count(主键)类似，有二级索引就遍历二级索引
  • 不过不会读取具体的值，只要存储引擎返回一条记录，就将count+1，所以效率高一些
• 如果是count(*)：
  • count(*)其实就等同于count(0)
• 如果是count(字段)：
  • 它会进行全表扫描
  • 如果该字段没有非空约束，就需要判断每一行的该字段是否为null，效率最差
  • 如果有非空约束，不会判断是否为null，不过还是会读取值

7、update 优化

要注意，避免把行锁升级为表锁。

比如执行这条语句：

update tbl set name = xx where id = 1;

执行这条SQL语句，则会锁定id为1的那条记录，等到事务提交之后，行锁释放。

但如果改成这条语句：

update tbl set name = 'xx' where name = 'java';

上面的name没有索引。如果开启多个事务，执行这条语句，会导致行锁升级为表锁，使得执行效率大大降低。

原因：InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。如果没有索引，或者索引失效，会从行锁升级为表锁

优化建议：一定要根据索引字段进行update操作，否则会把行锁升级为表锁，导致大大降低并发性能

8、SQL性能分析

SQL的优化，主要是针对查询语句，优化的手段主要是：索引。

1、查看SQL的执行频率

通常只进行增删改的数据表，就没有必要优化了

可以通过这条命令，查看当前数据库的增删改查语句的频率：

show [session|global] status like 'com_______' #7个下划线

2、慢查询日志

慢查询日志可以定位出执行效率低的SQL语句，从而针对性优化。

它会记录所有执行时间超过指定参数（long_query_time，单位秒，默认10秒）的SQL语句。

查看慢查询日志是否开启：

show variables like 'slow_query_log';

默认没有开启慢查询日志，需要在配置文件（/etc/my.cnf）中配置如下信息：

# 开启慢查询日志
slow_query_log=ON
#设置慢日志的时间为1秒
long_query_time=1
#没有使用到索引的查询也将被记录在日志中
log-queries-not-using-indexes=ON

必须加在[mysqld]处，重启服务后生效。

3、profile 查看详情

有些SQL，业务很简单，但执行也很慢，不过没有慢到慢查询日志的范围内，如何捕捉？

show profiles 能够帮助我们知道，每条SQL语句的时间都耗费到哪里了。

默认profiling是关闭的，需要通过set语句开启，可以设置session/global级别：

set profiling = 1;

查看是否开启：

SELECT @@profiling;

查看每条SQL的耗时：

show profiles;

查看指定query_id的语句，各个阶段的耗时：

show profile for query query_id;

查看指定query_id的语句，CPU使用情况（也包含各个阶段的耗时）：

show profile cpu for query query_id;

4、explain 执行计划

这个东西非常重要。

语法：

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

其实就是在一条正常的语句前面，加上explain或desc（这俩关键字效果是一样的）。

• id：select查询的序列号，表示操作顺序
  • id越大的越先执行（联表查询时，一个select语句对应着多个执行计划！）
  • 如果id相同，则表的执行顺序是从上到下
• select_type：表示select的类型，常见的取值有
  • simple：简单表，不使用表连接或子查询
  • primary：主查询，即子查询外层的查询
  • union：union中的第二个或后面的查询语句
  • subquery：select或where之后包含的子查询
• type：【重点关注】表示连接类型
  • 性能由好到差：
    • null、system（访问系统表）
    • const（根据主键或唯一索引查询）、eq_ref
    • ref（根据非唯一的索引查询）
    • range（范围查询）
    • index（遍历整个索引树）
    • all（全表扫描）
  • 不访问任何表才会是null，业务中不可能出现。尽量往前优化即可。
• possible_key：【重点关注】可能应用在这张表上的索引，一个或多个
• key：【重点关注】实际用到的索引，没用到就为null
• key_len：【重点关注】索引中使用的字节数（不是实际使用长度，而是该索引字段的最大可能长度），
  • 不损失精度的前提下，越短越好
  • 和这个字段中存储的值有关
• ref：
• rows：MySQL认为必须执行查询的行数，在InnoDB中是一个估计值
• filtered：表示返回结果的行数占所需读取行数的百分比
  • 含义是有效遍历的比重，这个值越大越好