数据库相关

数据库

一、关系型数据库

1.1 优缺点

• 优点：

  1. 二维表相较于网状，层次模型容易理解

  2. 使用方便，通用的SQL语法

  3. 易于维护，丰富的完整性约束，降低了数据冗余和数据不一致的概率

  4. 数据存储结构规范：关系型数据库是结构化存储，数据存储时具有良好的结构

• 瓶颈：

  • 难以高并发，在web2.0社交网站的高并发读写需求，关系型数据库很难满足

  • 难以海量数据，难以满足海量数据实时读写

  • 难以满足高扩展及可用性要求，很难横向扩展

  • 事务一致性难以实现，分布式事务中，实现事务一致性很困难

  • 多表关联查询等SQL语句比较复杂，很难编写，而且执行效率不高

1.2 SQL语言组成

1. DDL，数据库定义语言，用于定义数据库，表等，包含Create，Alter，Drop语句。

2. DML，数据库操作语言，用于数据库的添加，修改，删除操作，包括insert，update，delete

3. DQL，数据库查询语言，select

4. DCL，数据库控制语言，主要用于控制用户的访问权限，包括，Grant，revoke，commit，rollback语句。grant用于给用户添加权限，revoke用于收回用户权限，commit用于提交事务，rollback用于回滚事务

1.3 数据库实例

1.3.1 Oracle

1. Oracle的特有语法：

   1. 特有函数

      1. 空值函数：NVL(arg1, arg2)：如果参数1不为null，则返回参数1，否则返回参数2

      2. 空值函数：NVL2(arg1, arg2, arg3)：如果参数1不为null，返回参数2，否则返回参数3

      3. DECODE()函数：DECODE(expr, search1, result1, search2, result2, ..., default) expr是表达式或者函数，如果expr = search1，则返回result1的值，如果expr = search2，则返回search2的值，否则返回 default，注，这种语句可以使用CASE代替。

      4. NULLIF()，如果字段符合条件的值，就用空代替，比如：select nullif(name, '0') from student 如果name是’0’,则用空来进行代替。

      5. coalesce()：用来处理Null值的，COALESCE(expr1, expr2, ..., exprn)，取出第一个不为null值的值，如果全是null，就返回null

      6. greatest()：找出里边最大的值

      7. least()：找出里边最小的值

   2. 分析函数与开窗函数

      1. 概念：分析函数和开窗函数是同一个概念。就是将查询建立在不同的数据区，分别进行统计。

      2. 模型：

         select
             employeeId,
             salary,
             row_number() over (partition by ... order by ...) as ...
         from orders
         

      3. 实例：

         INSERT INTO sales (region, sales_date, amount) VALUES
         ('North', '2023-01-01', 100),
         ('South', '2023-01-01', 150),
         ('North', '2023-01-02', 200),
         ('South', '2023-01-02', 50),
         ('North', '2023-01-03', 300),
         ('South', '2023-01-03', 100);
         
         SELECT region, sales_date, amount,
         -- 计算每个地区每天的销售总额，然后进行加和
         SUM(amount) OVER (PARTITION BY region ORDER BY sales_date) AS running_total
         FROM sales;
         
         +--------+------------+--------+--------------+
         | region | sales_date | amount | running_total|
         +--------+------------+--------+--------------+
         | North  | 2023-01-01 | 100    | 100          |
         | North  | 2023-01-02 | 200    | 300          |
         | North  | 2023-01-03 | 300    | 600          |
         | South  | 2023-01-01 | 150    | 150          |
         | South  | 2023-01-02 | 50     | 200          |
         | South  | 2023-01-03 | 100    | 300          |
         +--------+------------+--------+--------------+转成md格式
         

         注：row_number()可以换成rank(), dense_rank(),sum(), leg(), lead()等等。

   3. 递归查询

      语句：select * from tablename start with 开始条件 connect by prior 递归条件

      SELECT employee_id, employee_name, manager_id
      FROM employees
      START WITH manager_id IS NULL  -- 根节点条件，可以根据实际情况修改
      CONNECT BY PRIOR employee_id = manager_id;
      

   4. insert

2. 分页语句

   1. 无order by的排序分页写法

      使用案例，通过嵌套子查询，进行两次筛选

      select * from 
      (select rownum as rownum, t.* from userinfo t 
      where t.birthday between TO_DATE('19810101', 'yyyymmdd') and TO_DATE('20210301', 'yyyymmdd') and rownum < 20*2
      ) table_alias
      where table_alias.rownum > 20*(2-1)
      

   2. 有order by 的排序分页写法（相当于加了一层嵌套，在这个嵌套里边先进行排序，之后的再进行取值）

      select * from 
      (select rownum as rownum, r.* from 
          (select * from userinfo t where t.shengri between to_date('19810101', 'yyyymmdd') and to_date('20210301', 'yyyymmdd') order by t.shengri desc) r 
      where rownum <= 20 * 2) table_alias
      where table_alias.rowno > 20*(2-1);
      

1.3.2 Mysql

1.3.2.1 Mysql架构

1. 分层

   1. 网络连接层（应用层）

   2. Mysql核心服务层

   3. 存储引擎层

   4. 系统文件层

2. 分层详解

   1. 网络连接层：也叫做应用层，主要负责与客户端进行通信

      作用

      1. 连接处理：MysqlServer对客户端发来的请求，使用线程池进行对接，且以后该客户端的请求，都由该线程进行对接。

      2. 用户鉴权：对客户端发来的连接请求进行鉴权处理，主要根据用户名，客户端主机地址，用户密码进行鉴权

      3. 安全管理：根据用户的权限，判断可以执行哪些操作。

   2. 核心服务层

      包含五个模块

      1. Mysql Management Server & utilities（系统管理）

         • 功能：

           1. 数据库备份和恢复

           2. 数据库安全管理，用户及安全管理

           3. 数据库复制管理

           4. 数据库集群管理

           5. 数据库分区，分库，分表管理

           6. 数据库元数据管理

      2. SQL Interface（SQL接口）

         主要用来接收用户的SQL命令，并且对其进行处理，得到用户所需要的结果，具体处理下面的几种语句。

         1. DML（增删改查）

         2. DDL（create/drop/alter等定义数据库和表的操作）

         3. 存储过程

         4. 视图

         5. 触发器

      3. SQL Parser（SQL 解析器）

         主要用来解析select查询语句，生成最终的语法树。

         sql解析器首先对查询语句进行分析，如果发现select语句上有语法错误，则返回相应的错误信息。

         语法审查通过之后，会先从缓存中进行查询，如果有就直接返回。

      4. Optimizer（查询优化器）

         主要是对查询语句进行优化，选择合适的索引，确定数据读取方式等。

      5. Cache & buffers（缓存）

         缓存包括全局缓存和特定引擎的缓存，如果命中缓存，就直接从缓存读取，无须再通过解析和执行，缓存机制是由一系列的小缓存组成的，表缓存，记录缓存，key缓存，权限缓存。

   3. 存储引擎层

      Mysql提供热拔插的存储引擎，供用户调用。存储引擎是mysql与具体文件打交道的子系统，是Mysql区别于别的数据库的重要特点。

      常见存储引擎有：Myisam，innodb，memory等。

   4. 系统文件层

      这是Mysql的最底层。

      Mysql运行时所需要的一些系统文件，日志记录文件。例如：redolog/undolog/binlog/errorlog/querylog/showlog/data/index

      注：这一层和日常的sql开发联系不大，可以通过修改或者查看某些日志，实现特定功能，或者为数据库提供优化。

1.3.2.2 select语句的执行步骤

1. 客户端发送一条select查询语句给Mysql服务器

2. 查缓存

   1. 描述：mysql服务器先从查询缓存中查询结果，如果缓存中存在指定的查询结果，则直接从缓存中返回命令的结果，如果缓存中不存在，则进入下一步

   2. 引入原因：

      由于查询在CRUD中最耗时，所以为了提高效率，提供了缓存机制，是mysql进行优化的重要一环。

   3. 原理：

      缓存系统其实是由哈希引用表进行维护的，这个哈希值中包含了查询本身，当前要查询的数据库，客户端协议版本等一些可能影响返回结果的信息。这个缓存系统会跟踪查询中涉及的每个表，如果表发生了变化，那么和这个表相关的所有的查询缓存都会失效。

   4. 缓存失效条件：

      1. 任何字符的不同，都会影响缓存的命中

      2. 查询语句中包含一些不确定的数据时，结果不会被缓存，

         例如函数：now(), current_date()等系统自带的函数，用户自定义的函数，或者存储函数，用户变量，临时表，mysql系统表，或者包含任何级别权限的表，都不会被缓存。注：虽然不会命中查询结果，但还是会查询缓存，但是不存。

   5. mysql中配置缓存

      1. query_cache_type：查询缓存类型，是否打开查询缓存，off/on/demand，demand表示只有在查询语句中明确写明sql_cache的语句才会进行查询缓存。

      2. query_cache_size：查询缓存使用的总内存空间

      3. query_cache_min_res_unit：查询缓存中分配内存块时的最小单元，如果这个值太小，会导致频繁内存块操作，但是可以减少分的过大，导致的内存浪费。

      4. query_cache_limit：Mysql能查询到的最大查询结果，如果查询结果大于这个值，则不会进行缓存。

3. 解析及预处理：

   1. 描述：服务器中的SQL解析器，会对sql进行解析，预处理，生成一个解析树

   2. 执行方式：使用Mysql语法和关键字，对sql语句进行解析及验证，生成相应的解析树。根据Mysql规则，检查解析树是否合法，解析名字和别名看是否有歧义，检查表和列是否存在。

4. 查询优化器生成生成执行计划

   1. 描述：核心服务层的查询优化器，会对解析树进行优化，生成对应的执行计划

   2. 原理：由于生成的解析树，有多重执行方式，所以查询优化器会根据基于成本的查询优化器（CBO），基于统计和代价模型，选择成本最小的方法。

5. 查询执行引擎执行sql查询

   Mysql根据执行计划，调用一个合适的存储引擎API进行查询。注：执行计划是一个数据结构，不是别的关系型数据库中常见的字节码。

6. 存缓存

   1. 描述：mysql会看下查询结果是否可以缓存，如果可以就在本阶段对结果进行缓存；

7. 将结果返回给客户端

   1. 描述：将结果集以增量，逐步返回的方式，推到客户端，换句话说，在查询生成第一条结果的时候，mysql就开始向客户端返回结果了。

1.3.2.3 mysql查重

1. distinct关键字

   select distinct expression from tables where...

2. distinct查重的方式

   1. distinct对完全相同的记录查重

      select distinct username from user

      注：distinct对null是不进行过滤的，返回的结果中包含null值，如果有多个null，也只会返回一个null

   2. distinct对有唯一id且部分字段相同的记录查重

      如果想要实现类似于select distinct id, username from user，这种sql语句，包含一个id和username，id是唯一键，这种去重无法去掉，可以根据用户名进行分组，使用聚合函数选出来一个某种条件的数据，然后得到的内容就是对某个字段去重的剩余的数据。

      select * from user where id in (select max(id) from user group by username)

   3. distinct对没有唯一id且部分字段相同的记录查重

      select address, group_concat(distinct username) as 姓名 from user group by username;

      INSERT INTO orders (order_id, user_id, order_number, order_date) VALUES
      (1, 1, 'A123', '2023-01-01'),
      (2, 1, 'B456', '2023-02-01'),
      (3, 2, 'C789', '2023-01-15'),
      (4, 2, 'D101', '2023-03-01'),
      (5, 1, 'E202', '2023-04-01');
      
      -- 执行
      
      SELECT 
      user_id,
      GROUP_CONCAT(order_number ORDER BY order_date SEPARATOR ', ') AS ordered_items
      FROM orders
      GROUP BY user_id;
      
      --结果是：
      

      user_id	ordered_items
      1	B456, A123, E202
      2	C789, D101

3. 通过分组+having短语实现查重

   除了使用distinct关键字，也可以使用group + having进行数据查重

   select * from user group by username having count(username) > 1;

1.3.2.4 Mysql去重

如果想要去除表中的重复数据，可以使用delete结合select

delete from user
where username in (
    select a.username from (
        select username from user group by username having count(username) > 1
    ) as a
)
and id not in (
    select b.bid from (
        select min(id) as bid from user group by username having count(username) > 1
    ) as b
);

找出usename大于1的，同时又不能是分组中最小id的，将其删去，如果不限制非最小id，就会将username大于1的全部删除。

二、SQL关键字

2.1 SQL关键字及执行顺序

1. from
2. on 注：有说join和on同时进行的
3. join
4. where
5. group by
6. 聚合函数 avg/sum/max/min/count…
7. with rollup或者cube
8. having
9. select
10. distinct
11. union
12. order by desc|asc
13. limit

2.2 关键字具体用法

1. from：

   1. 可以在from中同时指定多个表名，例如from t1, t2, t3，表示将前两个表，执行笛卡尔积（交叉连接），从而可以得到来自不同数据源的数据集，最后将这个结果集作为VT1。

   2. from 子句中的多个表的执行顺序，是从后往前，从右往左，所以，from子句中写在最后的表会被最先处理。当from子句中包含多个表，为了性能达到最优，一般将数据量最少的表，作为驱动表，遵循小表驱动大表。

2. on：

   1. on后边一般都是逻辑表达式，sql根据on后的逻辑表达式，对虚拟表vt1中的各个行进行筛选，筛选出条件为真的行，插入到生成的vt2中

3. join

   1. 用于连接多个表，添加外部行，配合outer来使用，分为三种情况，

      left outer join; 
      right outer join; 
      full outer join; 
      

      A left join B
      会将A放在左边，进行保留，匹配不到的数据被记成NULL

4. where

   1. 将条件为假的数据全部过滤掉。

   2. 由于此时还没有进行分组，所以不能在where过滤器中使用聚合函数对分组进行过滤

   3. 由于还没有进行列的选取操作，所以不能使用select中列的别名

5. group by

   1. 按照group by子句中指定的列，将虚拟表中的数据进行去重，将唯一的结果值，合并成为一个分组

   2. 如果使用了group by，那么后续所有的步骤都只能使用处理过的虚拟表了

   3. 在这一步，就可以使用聚合函数，以及使用select中列的别名了

   4. group by 的执行顺序是从左往右分组

   5. 为了提高效率，可以将不需要的记录，在group by 之前的where语句中过滤掉，避免在group by 之后通过having进行过滤

6. 聚合函数

   1. 用于对从列中获取的值进行计算，返回一个单一的值。

   2. 常用的聚合函数：

      1. AVG：返回平均值

      2. COUNT：返回行数

      3. FIRST：返回第一个记录的值

      4. LAST：返回最后一个记录的值

      5. MAX：返回最大值

      6. MIN：返回最小值

      7. SUM：返回总和

7. WITH ROLLUP 或 CUBE

   1. cube生成的数据，显示了所选列中的值的所有组合的聚合

      SELECT
      Region,
      Product,
      SUM(Sales) AS TotalSales
      FROM Orders
      GROUP BY CUBE(Region, Product);
      

   2. ROLLUP 生成的结果集，显示了所选并列中的值的某一层次的聚合

      SELECT
      Region,
      Product,
      SUM(Sales) AS TotalSales
      FROM Orders
      GROUP BY Region, Product WITH ROLLUP;
      

8. Having

   1. having和where的作用是相同的，having是对聚合值进行过滤，where关键字无法与聚合函数一起使用，having短语是在检索出所有记录之后才会对结果集进行过滤，这个处理需要进行排序，总计等操作，所以效率很低，如果能在group by之前的where 先对数据进行过滤，就能减少having的开销。

9. select

   1. select 中尽量避免使用 *，因为sql解析的过程中，会将*依次转换为所有的列名，是通过查询数据字典完成的。

   2. 尽量将所有的字段全部变为大写，因为sql解析的时候，会先进行转换成大写

10. distinct

    1. 去除重复行

11. UNION

    UNION用于对select的结果进行合并，默认去掉重复的记录

12. order by

    将虚拟表中的数据，按照当前指定的列进行排序，生成游标。这里和之前不一样的地方在于，这里生成的不是虚拟表，而是游标，所以在order by 子句中不能使用表达式，而且order by 子句的执行顺序是从左往右，很消耗资源。

13. Limit

    limit关键字用于从游标中，从指定位置选出指定行的数据，最终生成虚拟表。当数据量非常大的时候，limit非常低效，因为都是从头开始扫描，先定位到某一行，再开始读取。所以使用应用层缓存十分有必要。

2.3 关键字on, where, having的对比

1. 执行次序：on最先执行，where次之，having最后

2. 执行速度：

   1. on先将不符合条件的记录进行过滤掉，再进行统计，所以可以减少中间运算需要处理的数据。最快；

   2. 若为单表查询，过滤的字段不需要进行字段计算，where可以使用rushmore技术，所以where优于having；

2.4 in与exist

in

1. 简介

   用来确定条件中给定的值，是否与子查询或者列表中的值相互匹配。可以分为单表和多表，

   1. 单表：

      select * from user where id in (1,2,3);

      等效于：select * from user where id = 1 or id = 2 or id = 3;

   2. 多表：

      select * from A where id in (select id from B);

      进行子查询的时候，首先查询B表，然后将B与A进行笛卡尔积操作，最后按照条件筛选出符合要求的结果。简单说，就是先将B中查到的所有的id进行缓存，再检查A表中的id与B表中缓存的id是否相等，相等的放到结果集，直到遍历完成。类似于Java的双重循环

      in操作适用于A表数据多，B表数据少的场景。原因：

      1. 缓存效果。当在小表上执行查询的时候，数据库引擎会更容易将小表缓存在内存中，小表的数据量比较小，在执行主查询的时候，可以更快地在内存中查到匹配的数据，不必频繁访问磁盘。

      2. 索引效果。如果子查询上有索引，那么在小表上使用索引的效率更高，索引可以大大减少，在子查询中查找匹配值的时间。

      3. 减少对大表的处理。主查询的in子句，会将小表中的值，用于在大表中筛选匹配的行，由于小表的数据量比较小，这个筛选过程相对来说更加高效，因为数据库引擎不需要在大表上进行全表扫描。

2. not in 简介

   select * from A a where a.id not in (select b.id from B b)

   not in 的操作，本质上等于 != and != ***，由于!=会使索引失效，所以not in 的操作会进行全表扫描，而不使用索引，效率比较低。

3. exists简介

   1. 简介：exists通常和子查询一起操作，用于检查这个子查询是否至少返回了一行数据。exists不会返回数据，只是返回true或者false，当子查询返回为真时，外层查询语句会进行查询，当子查询返回为假时，外层查询语句将不进行查询或者查询不出任何记录。内表循环与外表进行对比，内表小，外表大，是常见的方式，也是对优化友好的，如果内表大，建索引就比较大。最好使用join来进行处理。

   2. 基本用法：

      select * from user where exists (select * from user where id = 1)

      select * from user是外表，select * from user where id = 1 是内表

   3. 执行逻辑：

      上面的sql，基本执行逻辑是，先对外表进行循环遍历，找到结果之后，查看外表中的记录和内表中的记录是否一致，如果匹配，就将结果放到入到结果集中。

      注：与in的区别是，exists先对外表进行查询，而且exists()不会对结果进行缓存，因为exists查询只会返回一个布尔值，只关心exists的查询是否有记录，与具体的结果无关。

      exists的本质，是对外表进行Loop循环，每次loop时都会对内表进行查询，当外表数据多，内表数据少，适合用exists，同样是小表驱动大表。

      注：exists适合用于内表数据量大，外表数据量小的查询，主要是基于查询的执行逻辑和性能考虑：

      1. 停止条件早。当外表数据量小的时候，对外表的每一行都能很快处理完成，在这种情况下，exists子句可能会更快地找到满足条件的记录并且停止查询。

      2. 内表数据量大也无所谓，只要找到一个满足的条件，那么就停止查询了。

      3. 当外表数据量比较小的时候，可以避免对大量不相关的数据进行处理，如果外表数据量很大，即使内表有索引，也可能需要在大量记录上执行exists子查询，降效率。

4. not exists简介

   select * from A where not exists (select * from B b where b.id = a.id)

   not exists内部是可以使用使用索引的，所以not exists的操作效率高于 not in

5. in与exists的区别

   1. 使用场景不同

      in适合于内表数据比较少，外表数据比较多的时候，内表做驱动表，外表做被驱动表。

      exists适用于外表数据量比较少，内表数据量比较多的时候。

   2. 子查询关联不同

      in一般是非关联子查询，非关联子查询必须先完成内层的查询，再进行外层的查询，

      exists一般是关联子查询，关联子查询必须先执行外层的查询，然后对所有满足过滤条件的记录，执行内层查询，外层查询会将数据传递给内层，所以外层查询会和内层查询相互依赖。

   3. 效率不同

      内表小，外表大，内表驱动外表的时候，in操作只会执行一次，尤其是外表查询存在索引时，in的速度较快

      内表大，外表小，外表驱动内表，exists会对外表进行loop循环，每次循环时，会对内表进行查询，此时即使外表没有索引，exists的效率也比较高。

2.5 group by 和 Having

group by 比 having执行早

1. group by 简介

   1. 用于分组操作，sql查询会按照group by子句中指定的列，将虚拟表中符合条件的数据进行去重，然后将唯一的结果值，组合成一个分组，最后生成另一个虚拟表。

2. having简介

   1. where子句是无法和聚合函数一起使用的

3. group by 和having的区别

   1. 作用不同

      having用于对where和group by查询出来的分组进行二次过滤，查询满足条件的分组结果，是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作。

      group by 对select查出来的结果集，按照某个字段或者表达式进行分组，获得一组结果，然后从每组中取出一个指定的字段或者表达式的值。

   2. 执行时机

      group by是在where 过滤部分数据之后，再对剩下满足条件的数据进行分组，group by 比having的执行条件要早。

      having是在group by 分组后，再找到特定的分组，通常会和聚合函数一起使用。

   3. 作用范围：

      group by 要紧跟在where的最后一个条件之后，不能被放在where的限制条件之间。

      having必须跟在group by 之后，使用having之前必须使用了group by，但使用group by不一定接着使用having

   4. 执行效率，group by 的执行顺序是从左往右的，效率一般，可以再group by之前先利用where 进行过滤，将不需要的记录提前过滤掉，再利用group by进行二次过滤。having短语是在检索出所有记录之后，才对结果集进行过滤，这个处理过程需要进行排序等操作，效率低。

2.6 date/time/datetime/timestamp的区别

sql语法中提供了几个与时间相关的关键字，不同数据库的关键字可能不太一样，以Mysql举例，常见的有上边几种。

1. time & date

   YYYY-MM-DD HH:MM:SS 分别对应了date和time，如果数据库设置的格式是date，但是同时存储了time，会丢掉精度，同时抛出warning。

2. dateTime

   1. 存储范围：1000-01-01 00:00:00.000000至9999-12-31 23:59:59.999999，占用的存储空间8字节

   2. 类型可以精确到毫秒，也可以格式化成为YYYY-MM-DD HH:mm:ss

   3. 允许使用null作为默认值，也可以自定义，但是系统不会自动更新数据值

   4. 在不允许存空值时，需要手动制定datetime字段的数据值来插入数据，也可以使用now变量来自动插入系统的当前时间。

   5. dateTime类型的值不会自动发生变化，适合记录数据的原始创建时间。

   6. mysql5.6之后，current_timestamp()函数支持使用datetime类型

3. timestamp简介

   1. 存储的时间范围1970-01-01 00:00:01.000000到2038-01-19 03:14:07.000000，存储空间四个字节

   2. 精度到毫秒，可以格式化成YYYY-MM-DD HH:mm:ss

   3. 默认值是current_timestamp()，即当前系统的时间。再mysql5.6之前，current_timestamp()使用的是timestamp类型。

   4. 该类型允许有空值，但不可以自定义值，所以为空值时没有任何意义。

   5. 该类型适合记录最后修改时间，只要修改了表中的其他字段，timestamp字段就会被数据库自动更新成为当前的系统时间

   6. 在有索引的时候，timestamp更轻量级，比datetime执行速度更快。

   7. timestamp会随着市区进行变化，适合用于跨时区的场景

2.7 select的基本语法

1. sql语句

   select name as '商品名称', AVG(good_number) as '平均数量'
   from weshop_goods
   where is_on_sale = 1
   group by name
   having 平均数量 > 40
   order by goods_number desc
   limit 100;
   

2. 执行顺序

   1. 先执行from，从weshop_goods中拿到数据，
   2. 执行where，筛选出商品表中的所有is_on_sale为1的商品，
   3. 执行group by 子句，按name进行分组
   4. 使用聚合函数，进行平均
   5. 执行having，筛选出平均数量大于40的商品
   6. 执行select，设置别名
   7. 执行order by
   8. 返回前100数据

三、mysql分页

3.1 limit语法进行分页

1. Limit的语法

   可以使用Limit来限制查询结果返回的数量，进而实现分页的效果

   select * from table limit [offset,] rows

   注：offset表示返回记录行的偏移量，默认从0开始，当offset=0时可以进行省略，Limit rows等同于Limit 0，rows

2. 用法

   select * from table limit 30, 10;  --表示从第30行开始取10行
   

   只需要动态地修改offset和rows两个值，即可实现分页功能。一般从前端向后台传递两个参数，currentPage和pageSize，分别对应offset和rows

   前端传递过来的currentPage一般是1开头，所以在sql中进行调整如下：

   select * from table limit (currentPage - 1) * pageSize, pageSize

3. limit分页原理

   limit 20,10 一般是查询出前30条数据，然后舍弃掉前20条。

   当表中记录数比较多的时候，分页查询的效率会变慢。

4. 优化

   select * from table order by id desc limit 100000, 2000;

   该条语句可以优化为：

   select * from table where id <= 102000 order by id asc limit 2000;

   原因：之前是将所有的数据全部倒序排列，取出102000条数据，舍弃100000条，现在是，只拿102000条数据，倒序，只取前2000条。其实这样做有问题，因为id不一定是正好在102000之间的。如果有102001，那优化后的数据，id<102000就扫描不到数据。 这个思想是对的，先不使用Limit进行切分，直接拿出来那个范围内的数据。从头开始，而不是从某个位置进行切分。这种方式，

   如果使用这种优化方式，拿下一页可以使用：

   select * from table where id > 102000 order by id asc limit 2000;

   不管翻多少页，每次只扫描2000行，效率会提高，但是只能提供上一页，下一页的操作。

   进一步优化：指明查询列

   select 列1, 列2, 列3... from table where id > 102000 order by id asc limit 2000

   进一步优化，使用主键或者唯一索引进行分页，假设主键或者唯一索引为Id,客户端传来的currentPage为10001,pagesize=1000

   select 列名1, 列2, 列3... from mytable where id > (currentpage-1) * pagesize limit pageSize;

   进一步优化，

   select 列名1, 列2, 列3... from table where id > (currentpage -1) * pagesize order by id limit pagesize

   这种情况会根据id进行排列，适用于数据量很多的情况，order by 后面的列是主键或者唯一索引，这样使order by 操作能利用索引，查询的速度就会快很多

   进一步优化，

   表中的id是连续递增的，可以根据查询的页数和查询记录，算出要查询的范围，可以使用between和and进行查询

   select 列名1, 列2, 列3... from table where id between 100000 and 1000200 limit 200;

   这种方式能极大加快查询方式，但是只能用于能推算出来数据范围的情况

3.2 PageHelper进行分页

支持12种数据库，支持常见的rowBounds,RowBounds 是 MyBatis 中用于控制查询结果行数的对象。遵循queryInterceptor规范。

3.2.1 使用方式

1. pom导入依赖

2. 配置拦截器插件

   新版的拦截器插件使用的是com.github.pagehelper.PageInterceptor，旧版使用的是com.github.pagehelper.PageHelper。现在是一个特殊的dialect实现类，是分页插件的默认实现类，提供了和以前相同的用法。

   <plugins>
       <plugins interceptor="com.github.pagehelper.PageInterceptor">
           <property name = "param1" value = "value1">
       </plugin>
   </plugins>
   

3. 使用方式

   1. 直接使用RowBounds方式

      List<Country> list = sqlSession.selectList("x.y.slelectIf", null, new RowBounds(0, 10))；
      

   2. 使用Mapper接口方式调用，这种方式一定要在查询开始的时候，将pagehelper放在前面

      1. startPage()

         PageHelper.startPage(1, 10);
         List<Country> list = countryMapper.selectIf(1);
         

      2. offsetPage()

         PageHelper.offsetPager(1, 10);
         List<Country> list = countryMapper.selectIf(1);
         

3.2.2 PageHelper源码及原理分析

1. Mybatis对插件的处理过程

   Mybatis是通过拦截器进行处理的，内部真正执行sql语句的有四个插件对象：

   1. executor：负责拦截update,query,getTransaction,commit,rollback,close等方法
   2. statementHandler：处理prepare,parameterize,batch,update,query等方法
   3. parameterHandler：处理setParameters,getParameterObject等方法
   4. ResultSetHandler：处理handlerResultSets,handleOutputParameters

2. 自定义插件

   要实现自定义插件，必须要实现Interceptor接口，实现接口中的三个方法，否则无法被拦截。

   有了拦截器，对上述四大对象进行拦截。

   //args 属性是一个 Class[] 数组，用于指定被拦截方法的参数类型。在你的例子中，args = {MappedStatement.class, RowBounds.class, ResultHandler.class} 表示被拦截的 query 方法的参数类型分别是 MappedStatement、RowBounds 和 ResultHandler。
   
   @Intercepts({@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, RowBounds.class, ResultHandler.class})})
   public class MyPlugins implements Interceptor {
       @Override
       public Object intercept(Invocation invocation) throws Throwable {
           System.out.println("成功拦截了Executor的query方法，在这里可以进行业务操作。");
           Object result = invocation.proceed();
           System.out.println("After executing query...");
           return result;
       }
   
       @Override
       public Object plugin(Object target) {
           return Plugin.wrap(target, this);
       }
   
       @Override
       public void setProperties(Properties properties) {
           System.out.println("自定义属性：name->" + properties.getProperty("name"));
       }
   }
   

   注：@Interceptor注解用来声明当前类是一个拦截器，后面的@Signature用于标识需要拦截的方法签名，通过以下三个参数来确定

   • type：被拦截的类名

   • method：被拦截的方法名

   • args：标注方法的参数类型

   然后在Mybatis-config中对上述插件进行配置即可。

   <plugins>
       <plugin interceptor = "com.yuanheng.mybatis.plugin.myPlugin">
           <property name = "name" value = "一一哥">
       </plugin>
   </plugins>
   

3. pagehelper实现的拦截器

   pageHelper插件用的拦截器是：PageInterceptor，

   @Override
   public Object intercept(Invocation invocation) throws Throwable {
           // 在Mybatis的拦截器中，invocation表示对被拦截方法的一次调用，
           // invocation内部包含三个属性
               // Object target, 被拦截的目标对象，即实际执行方法的对象
               // Method method, 被拦截的目标方法，即，要执行的方法
               // Object[] args，方法的参数数组
           Object[] args = invocation.getArgs();
   }
   

   invocation.getArgs()可以用来获取参数，里面的几个参数分别是：

   1. MappedStatement ms = (MappedStatement) args[0];

      • MappedStatement 包含了执行 SQL 语句所需的所有信息，包括 SQL 语句本身、参数映射信息、结果映射信息等。

      1. ID标识符：每个映射语句都有唯一一个id，用于在mybatis中唯一标识一个映射语句

      2. sql语句：包含映射语句的实际sql语句，可以使增删改查等操作

      3. 参数映射信息：包含了参数的类型，参数的属性映射关系等信息，用于将传递给映射语句的参数和sql语句的占位符进行映射

      4. 结果映射信息：包括了查询结果的映射信息，用于将数据库查询结果集映射为java对象

      5. 缓存配置：包括了关于缓存的配置信息，如缓存的类型，缓存的大小

      6. 语句类型：标识映射语句的类型，包括select/insert/update/delete

   2. Object parameter = args[1];

   3. RowBounds rowBounds = (RowBounds) args[2];

   4. ResultHandler resultHandler = (ResultHandler) args[3];

   Executor executor = (Executor) invocation.getTarget(); //executor是获取执行实际方法的对象

4. 大致流程

   • pagehelper首先将前端传来的参数保存到page对象中，接着将Page对象放到ThreadLocal对象中

   • pagehelper在使用拦截器的时候，会查询threadLocal中有没有分页数据

   • 执行查询语句，获取当前线程中的分页参数

   • 通过拦截器在sql语句中添加分页参数，拼接出完整的分页sql语句，实现分页查询

   • 查询结束之后，在finally中清除threadLocal中的分页查询参数

四、主键

4.1 引入

很多表设计之初，都会给表分配一个id主键字段，主键的名字不一定叫id，可能是别的名字。主键一般是作为数据库表格里的第一个字段，主键的生成策略和自增长比较重要。分布式主键的生成策略也比较重要。

主键一般是一列或者多列的组合，该值能唯一地标识表中的每一行，保证了实体的完整性。而且可以和别的表的外键相关联，也可以在主键上创建索引以提高查询效率。

主键不是必须的。但是最好设置一个主键，不管是单主键还是复合主键，主键的存在，代表表的结构完整性。

4.2 Mysql主键

1. 主键的作用

   1. 保证实体的完整性

   2. 加快数据库的操作速度

   3. 在添加数据时，DMS可以防止新纪录与已有数据主键重复

   4. 数据库管理系统会自动按主键值的顺序来显示表中的记录

2. 主键的特点

   1. 一个表只能有一个主键

   2. 唯一性原则，主键值，必须可以唯一地标识出表中的每一行，且不能为Null。表中不会存在逐渐相同的两行数据。

   3. 一个字段只能在联合主键中出现一次。

   4. 联合主键要满足最小化原则，联合主键中不能包含不必要的字段。即，如果将联合主键的某一字段删除后，剩下的字段如果构成主键，仍满足唯一性原则，那么这个联合主键就是不正确的。

   5. 主键的内容不能被更新。

   6. 主键不应该包含动态变化的数据，例如，时间戳，创建时间列，修改时间列。

3. 主键的生成策略

   基于Mysql，说明几种主键的生成策略

   1. auto_increment

      • 描述

        常见，对于一般的表，可以使用此种策略。一般适用于int,short,long等整型类型的id。

        常用于单线程，向同一张表中插入数据时使用。在并发环境下避免使用本策略。

        create table user (
            id int(4) primary key not null auto_increment,
            username varchar(16) not null,
            password varchar(16) not null
        );
        

      • 主键用完的处理机制

        如果采用int类型作为自增主键，取值范围是0~43亿。如果不够，就是用bigInt类型。

        如果已经上线了，也可以直接在线上将id的类型改成bigInt。如果使用的是mysql5.6版本以上的，可以直接使用在线修改功能

        alter table tableName change oldFieldName newFieldName fieldType
        

        也可以采用第三方工具，在线修改表结构，例如pt-osc(pt-online-schema-change)，ghost

        不过一般在单表数据超过5000w之后，就考虑分库分表了，否则数据查询效率很低

   2. 联合主键

      create table student_course(
          name varchar(25),
          stuId int(11),
          cid int(11),
          primary key(stuId, cid)
      )
      

   3. uuid

      mysql支持使用uuid作为唯一主键，特点如下

      1. 优点

         • 全局唯一，安全，可移植
         • 能够保证独立性，程序可以在不同的数据库之间迁移，效果不受影响
         • uuid不仅独立于表，也独立于库，在切分数据的时候，这种特性尤其重要

      2. 缺点

         • uuid无序性，造成性能不如自增id，字符串存储空间大，查询效率低
         • uuid无序性，对于InnoDb引擎，会增加I/O压力
         • uuid过长，影响内存的索引命中率，索引查询的时候性能很差

      drop table if t_user;
      create table t_user (
          id varchar(50) not null,
          name varchar(50) not null,
          primary key(id)
      );
      
      -- 插入数据需要手动指定主键的值，数据库不能自己生成
      insert into t_user(id, name) values (select UUID(), 'zhangsan');
      

   4. 自定义序列表

      自定义序列表，是指，在数据库中创建一张用于生成序列的表来存储序列信息，该序列的生成策略，是通过代码来实现的。

      create table 't_user' (
          'name' varchar(50) not null,
          'id' bigint(20) unsigned not null default '0',
          primary key ('name')
      ) engine=InnoDB;
      

      id不是主键，使用该表的时候，先向里边插入一些初始数据

      insert into t_user(name) values ('zhangsan', 'lisi', 'wangwu');
      

      之后，通过执行下面的sql，获得新的用户id，

      update t_user set id = LAST_INSERT_ID(id + 1)
      where name = 'zhangsan';
      
      select LAST_INSERT_ID();
      

      id的序列生成会脱离数据库层面，由应用层负责，可以使用spring框架实现，但会增加开发的复杂度。

获取自动生成的主键

1. jdbc2.0提供的insertRow()方式

   Statement stmt = null;
   ResultSet rs = null;
   
   try {
       stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_UPDATABLE); // 创建Statement
       stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial");
       stmt.executeUpdate("CREATE TABLE autoIncTutorial (priKey INT NOT NULL AUTO_INCREMENT,dataField VARCHAR(64), PRIMARY KEY (priKey))");
       rs = stmt.executeQuery("SELECT priKey, dataField FROM autoIncTutorial");
       rs.moveToInsertRow(); 
       rs.updateString("dataField", "AUTO INCREMENT here?"); 
   
       rs.insertRow();
       rs.last();
       int autoIncKeyFormRs = rs.getInt("priKey")
   } finally {
       rs.close();
       rs = null;
   }
   

2. jdbc3.0提供的getGenerateKeys()来实现

   Statement stmt = null;
   ResultSet rs = null;
   try {
       stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                                   java.sql.ResultSet.CONCUR_UPDATABLE);  
       
       // ...
       
       stmt.executeUpdate(
               "INSERT INTO autoIncTutorial (dataField) "
               + "values ('Can I Get the Auto Increment Field?')",
               Statement.RETURN_GENERATED_KEYS);                      // 向驱动指明需要自动获取generatedKeys！
       
       int autoIncKeyFromApi = -1;
       rs = stmt.getGeneratedKeys();                                  // 获取自增主键！
       if (rs.next()) {
           autoIncKeyFromApi = rs.getInt(1);
       }  else {
           // throw an exception from here
       } 
       
       rs.close();
       rs = null;
       System.out.println("Key returned from getGeneratedKeys():"
           + autoIncKeyFromApi);
   }  finally { ... }
   

3. 通过sql的select LAST_INSERT_ID()函数来实现

   Statement stmt = null;
   ResultSet rs = null;
   try {
       stmt = conn.createStatement();
       // ...
       // 省略建表
       // ...
       stmt.executeUpdate(
               "INSERT INTO autoIncTutorial (dataField) "
               + "values ('Can I Get the Auto Increment Field?')");
       
       int autoIncKeyFromFunc = -1;
       rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");             // 通过额外查询获取generatedKey
       
       if (rs.next()) {
           autoIncKeyFromFunc = rs.getInt(1);
       }  else {
           // throw an exception from here
       } 
       
       rs.close();
       System.out.println("Key returned from " +
                       "'SELECT LAST_INSERT_ID()': " +
                       autoIncKeyFromFunc);
   }  finally {...}
   

4. 通过SQL @@INDENTITY 变量来实现

   
   INSERT INTO t_user VALUES(NULL, 'yyg'), (NULL, 'syc');
   
   SELECT @@identity;
   

5. 通过MYBATIS框架来实现

   <insert id="id" parameterType="User" useGeneratedKeys="true" keyProperty="id">
   </insert>
   

五、存储引擎

存储引擎的作用

1. 对数据进行操作

   数据库实现增删改查的各种操作，本质上就是利用存储引擎来实现的。

2. 提供特色功能：使用不同的存储引擎，mysql的功能就不同，mysql的能力取决于存储引擎。

   1. 存储机制：规定了数据库如何进行存储

   2. 索引：如何为表构建索引，如何查询数据等

   3. 锁定水平：数据库的锁定水平是指，数据库管理系统对数据进行并发控制的一种机制

      • 读未提交

      • 读已提交

      • 可重复度

      • 串行化

存储引擎的分类

不同版本的mysql对存储引擎种类支持不同，可以使用show engines查看数据库中包含的存储引擎。

innoDB

1. 特性

   • 支持事务（innodb的核心特性）

   • 支持行级锁

     描述：粒度小，行级锁只锁定单独的行，别的事务可以同时访问同一表的不同行，从而减少了锁定的冲突。但是会造成资源消耗，引入更多的锁定管理开销（需要引入更多的锁），当多个事务同时请求多个行的锁，可能会造成死锁。

   • 支持自动增长列auto_increment

     描述：innodb表的自动增长列不能为空，可以进行手动插入

     但是若手动插入的值，是空或者0，实际插入的值仍然是自动增长后的值。

   • 自动增长的列必须是索引，如果是组合索引，也必须是组合索引的第一列。

   • 对主键的处理：如果没有显式地在表中指定主键，InnoDB会为每一行自动生成一个6B的ROWID作为默认的主键。

   • InnoDB索引的底层数据结构基于B+Tree

   • 支持外键：只有innodb支持外键。在创建外键时。父表必须有对应的索引，子表在创建外键时，也会自动创建对应的索引。

     CREATE TABLE 表1 (
         列1 数据类型 PRIMARY KEY,
         列2 数据类型,
         FOREIGN KEY (列2) REFERENCES 表2(被引用列)
     );
     
     --注：列2是表1的外键，它引用了表2中的被引用列，表1中的列2的值，必须在表2的被引用列中存在。
     

   • 自动灾难恢复：与其他存储引擎不同，innodb表能够自动从灾难中恢复。

   • innoDB存储引擎中的数据库文件类型包括.frm/ibdata1/ibd格式

   • 实现了缓冲管理：不仅能缓冲索引，也能缓冲数据，且能自动创建散列索引以加快数据的获取

     散列索引：使用哈希函数将索引列映射为一个固定大小的哈希值，查找速度非常快，常用来做等值查询时的索引，但是不适用于范围查询和排序，因为hash值的顺序，和索引列的顺序并不一致。而且散列索引不支持部分匹配，比如说组合索引，匹配到一部分就不能命中索引，如果使用b-tree，根据最左前缀原则，能匹配到几个算几个。

   • 读写处理的效率较差，会更多占用磁盘空间以保留数据和索引。

2. 基本使用

   • 代码

     create table t_test (
         name varchar(20) not null default 'zhangsan',
         sex varchar(2) default '1',
         primary key (name)
     ) engine = InnoDB default chatset=utf-8 row_format=dynamic;
     

3. 使用场景

   Innodb的优点是提供了良好的事务处理，崩溃修复和并发控制能力，缺点是读写效率较差，占用的数据空间相对较大。所以，若需要支持事务，外键，自增的主键等功能，想要更高的并发读取效率，innodb可以考虑。

myisam

1. 特性

   • 不支持事务

   • 不支持外键

   • 使用表级锁，并发性差

   • 同样使用B+Tree，但在具体实现上，与innoDB不同

   • 主机宕机后，myisam表容易损坏，灾难恢复性不佳

   • 只缓存索引，数据的缓存是利用操作系统缓冲区来实现的

   • 每个Myisam在磁盘上存储成3个文件，其中文件名和表名都相同，但是扩展名分别为：frm(存储表定义)，myd（mydata，存储数据），myi（myindex，存储索引）

   • myisam的表支持3种不同的存储方式，

     1. 静态表（固定长度）ROW_FORMAT=FIXED

        1. 特点：在静态存储方式下，MyISAM 表的行格式是固定长度的，不支持变长的 CHAR、VARCHAR、BLOB 和 TEXT 等数据类型。每个列的长度都是固定的，不会根据实际数据长度而变化。

        2. 描述：这种存储方式的优势是更加紧凑，因为每一行的存储空间是固定的，适用于表的结构相对简单且列的长度大致相同的情况。

     2. 动态表 Dynamic

        1. 特点：支持变长的 CHAR、VARCHAR、BLOB 和 TEXT 等数据类型。

        2. 描述：动态存储方式可能导致随机访问时的性能损失。

     3. 压缩表 Compressed

        1. 特点：MyISAM 表的行格式是变长的，并且通过压缩算法对数据进行压缩。这种存储方式可以显著减小表的物理存储空间，降低磁盘 I/O 操作。

        2. 描述：压缩存储方式在节省存储空间方面有优势，但在进行写入和查询时可能会引入一些性能开销，因为需要进行压缩和解压缩操作。

     create table table1 (
         column1 int,
         column2 varchar(25),
     ) engine=MyISAM ROW_FORMAT=FIXED;
     

2. 基本使用

   create table t_test(
       id int(10) unsigned not null auto_increment,
       name varchar(10)  character set utf8,
       age int(10),
       primary key(id)
   ) engine=MYISAM;
   

3. 使用场景

   1. 优点：占用空间小，处理速度快，

      1. 支持高速存储，检索，支持全文搜索：在筛选大量数据的时候，非常迅速，适合选择密集型表

      2. 并发插入特性，允许同时选择和插入数据：适合管理邮件或者web服务器等日志插入密集型的数据表

   2. 缺点：不支持事务的完整性和并发性

      如果对事务的完整性没有要求，或者以select/insert为主，基本上都可以使用这个引擎创建表。

memory

1. 特性

   • 数据在内存中

     使用内存中的数据创建表，而且所有的数据都存储在内存中。

   • 访问非常快

   • 默认使用hash索引，速度比b+tree更快

   • 使用表级锁

   • 只支持固定大小的行

     注：varchar类型的字段，会存储为固定长度的char类型

   • 不支持text, blob字段

   • 服务器重启后，表中的数据会消失，但表结构还在

   • 每个memory表实际对应一个磁盘文件，格式是.frm

2. 基本使用

   create table t_test
   engine=memory
   

3. 使用场景

   1. 存储引擎使用的是hash索引，对等值查找十分高效：

      适用于查找邮编和地区对应的关系表。

   2. 易失性

      可以用于存储，数据分析时建立的临时表

Archive

适合存储大量独立的，作为历史记录的数据。区别于innodb和myisam，archive提供了压缩功能，拥有十分高效的插入速度，但是不支持索引，所以查询性能较差。

Merge

merge存储引擎是一组myisam表的集合，这些myisam表结构必须完全相同，merge表中没有数据，对merge类型的表可以查询，更新，删除的操作，这些操作实际上是对内部的myisam表进行操作。

常用引擎的区别

innoDB 支持事务，支持外键，支持树索引，不支持哈希索引，不支持全文索引，存储限制为64tb，支持数据缓存，支持行级锁，添加修改删除更快，自动灾难恢复较好。

myisam 不支持事务，不支持外键，支持树索引，不支持哈希索引，支持全文索引，存储限制为256tb，不支持数据缓存，支持表级锁，查询速度更快，对自动灾难的恢复一般。

选择引擎主要考虑一下的内容：是否支持事务，是否要使用热备，是否要能够自动灾难恢复，是否需要支持外键。

• 为什么innodb添加删除更快，而myisam查询速度快:

  1. 锁定粒度不一样：innoDB支持行级锁，而myISAM支持表级锁，行级锁允许多个事务同时在表中不同部分进行插入和删除，表级锁，整个表被锁定，阻塞其他写操作，造成高并发写入时的性能瓶颈。所以innoDB适合读写，而myISAM适合查询。

  2. 事务支持：因为innoDB支持事务，所以在高并发，事务要求严格的场景中，innoDB的添加删除更为优越

  3. 聚簇索引：myISAM引擎使用非聚簇索引，使得在特定情况下（例如范围查询），减少了磁盘IO，更快执行某些查询。

     注：只是某些情况更快，也有可能更慢。

     innoDB采用聚簇索引，数据和索引放在了一起。

  4. 索引查找方式不同：innoDB如果建了一个索引，会根据这个索引先找主键key，再通过主键索引去找真实物理地址，而myISAM能通过索引直接找到真实地址

六、mysql索引

1. 作用

   • 加快查询速度：

     索引相当于数据库的目录，可以减少服务器需要扫描的数据量，快速查找出数据库里存储的数据。

   • 帮助服务器避免排序和创建临时表：

     1. 如果查询中的列上有合适的索引，数据库系统可以使用索引提供的有序性，避免对数据进行排序。

     2. 索引的使用也有助于避免创建临时表，因为索引提供了一种更有效的方式来满足查询的需求。

   • 将随机IO变成顺序IO

     索引的有序性使得在磁盘上的访问更加连续，减少了寻道的开销，提高了查询性能。

   注：表中的数据量越大，索引的作用越明显。

2. 缺点

   使用索引会增加一些额外的开销成本，比如：

   1. 空间成本：创建索引会产生索引文件，占用一定的磁盘空间。

   2. 时间成本：查询索引本身需要一些时间，索引越大，查询索引本身花费的时间就越多。

   3. 维护成本：索引文件是一个二叉树类型的文件，进行DML操作的时候，会对索引文件进行修改，当数据大量更新时，需要对索引进行维护，DML操作后的索引性能会下降。

3. 使用场景：

   1. 适合

      1. 频繁查询的字段

         给频繁作为查询条件的字段，添加索引

      2. 唯一性强的字段

         如果索引的值是唯一的，通过该索引可以快速地确定某条记录。

         唯一性太差（区分度低）的字段不适合单独创建索引，即使该字段频繁被作为查询条件。

      3. 长度较小的字段

         尽量为长度较短的列创建索引，索引的字段要尽量小

         对一个char(100)的列进行全文检索需要的时间，比char(10)要多

      4. 频繁排序的字段

         为需要进行排序的列，或者经常order by的列，创建索引，比如经常需要对某列使用<|<=|=|>|>=|between|in等。索引是已经排过序的，可以利用索引的排序，加快排序的查询时间。

      5. 作为关联的列

         与别的表进行关联的字段，即外键，可以建立索引

      6. 高并发场景

         高并发场景下，如果要使用索引，面临单列或者组合索引，尽量使用组合索引。原因如下：

         1. 组合索引可以形成覆盖索引

            组合索引可以更好地满足覆盖索引的需求。覆盖索引是指索引包含了查询中涉及的所有列，从而避免了对数据表的实际行的查找。当查询中的所有列都在组合索引中时，数据库可以直接从索引中获取所需的数据，而无需再次访问数据表，这可以显著提高查询性能。

         2. 可以减少索引数量

            组合索引可以用更少的索引来满足多个列的查询需求，相较于维护多个单列索引，这可以减少索引的维护成本。在高并发环境中，减少索引数量有助于减少锁的竞争，提高系统的并发性。

         3. 减少查询时间

            在高并发场景下，查询的响应时间至关重要。组合索引可以减少查询时间，因为它允许数据库系统直接在索引中定位满足查询条件的数据，而不必浪费时间在多个单列索引之间进行切换。

         4. 支持多列查询

            组合索引能够支持涉及多个列的查询。在高并发环境中，可能需要执行复杂的查询，涉及多个条件的组合。组合索引可以更好地支持这样的查询，提高整体系统性能。

      7. 统计与分组

         如果查询中经常作为统计与分组的字段，可以建立索引

   2. 不适合

      1. 频繁更新的字段

         频繁更新的字段不适合创建索引，因为维护索引也是一笔开支

      2. 数据量小的表

         没必要创建索引。如果数据量比较小，本身进行查询的时间就不多，不用创建索引。创建索引，遍历索引的时间可能比直接查询用的时间还多。

      3. 用不到的字段

         where中用不到字段，不用创建索引。

4. 注意事项

   1. 创建索引的时候尽量满足最左前缀原则，where子句中使用最频繁的一列，一定要在组合索引中放到最左边。

   2. 尽量扩展索引，而不是重新建索引

   3. 索引的列不要参与运算，比如说有个主键索引ID，执行where id+1 > 10，在每次检查索引的时候，都会将索引计算一次，然后再进行比较，这样的成本会很高。

      例如：使用了SELECT * FROM your_table WHERE id + 1 > 10;，这个查询对 id 列进行了运算（加法操作）。在进行查询优化时，数据库系统可能无法直接利用 id 索引来加速查询，因为在索引中存储的是原始的 id 值，而不是 id + 1 的值。因此，在执行这个查询时，数据库可能需要对每一行数据进行运算（计算 id + 1 的值），然后再进行比较。这样的操作会导致数据库无法有效使用索引，因为它不能直接利用索引中的有序性来执行查询，而是需要对每行数据进行计算，这会增加查询的成本。

   4. 删除不再使用或者很少使用的索引

   5. 限制索引的数量，创建的索引越多，占用的磁盘空间越大，在更新表之后，索引也需要同步维护，甚至还需要重构索引，mysql生成执行计划的时候，也需要考虑索引。

5. 索引能加速查询的原理

   1. 描述：类似于字典的目录，通过不断缩小查询的数据范围，筛选出最终需要的结果。

   2. 原理：

      1. 能将随机事件变成顺序的事件

      2. 索引提供了一种固定的查找方式来锁定数据

      3. 拥有特定的数据结构：

         如果索引结构较为复杂，那么使用索引本身，就会占用一定的系统性能，针对这种情况，设计出几种数据结构存储索引。

         例如fulltext，hash，btree，rtree。

         innodb就采用了b+tree数据结构，b+tree会根据索引的物理结构将索引分为聚簇索引，非聚簇索引。

6. 原理详述

   1. 为什么创建完索引查询速度会变快

      传统查询方法：按照表的顺序遍历，不论查找几条数据，mysql都会将表的数据从头到尾遍历一遍。

      索引查询方式：mysql通过b+tree算法生成一个索引文件，查询数据库时，按照索引文件进行遍历（折半查找可以大幅提高查询效率），找到响应的键，从而找到数据。

   2. 索引的数据结构

      几乎没有使用二叉查询树，或者二叉查找树的升级版——红黑树进行查询的。

      大部分都采用B树或者B+树

      • B树：

        1. 概念：

           1. 顺序：是一种自平衡的树，能够保持数据有序。

           2. 时间复杂度：能够让查找数据，访问数据，插入数据，删除数据都在对数时间内完成。

        2. 满足的数据结构：

           • 子节点个数：由于B树是一个一般化的二叉查找树，每个子节点都可以拥有多于两个的子节点

           • 节点的数据结构：B-Tree的每个节点都是指针+键值对组成，key和指针相互间隔，节点的两端是指针

           • 每个非叶子节点由n-1个key和n个指针组成，每个叶子节点最少包含一个Key和两个指针

           • 每个指针要么是Null，要么指向另外的节点，每个叶子节点的指针都是Null

           • 一个节点中的key从左向右递减排列

           • 指针指向的节点，所有Key大于指针左边的key，小于指针右边的key

           • 所有的叶子节点具有相同的深度，等于树高（树的层数）

        3. B树中按key检索数据的算法

           首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点，递归查找，直到找到节点或者找到null指针，返回null就代表查找失败。

           一个度为d的B树，假设其索引是n个key，则其树高的上线为logd((n+1)/2)，检索一个key，其查找节点个数的渐进复杂度为O(logdN)，可以看出B树是一个非常有效率的索引数据结构。

           度：用来约束一个节点Key和指针的个数，每个非叶子结点由n-1个key和n个指针组成，其中d<=n<=2d，由于key的个数至少为1，所以d>=2

      • B+树

        1. 使用场景：B+树是B树的最常见变种，Mysql普遍使用B+树实现索引结构

        2. 与B树的对比：

           • B+树的每个非叶子节点都是由Key+指针组成的，B树是由键值对+指针组成的；

           • B+树的每个非叶子结点的指针数量等于key的数量，B树指针数量等于键值对数量+1

           • B+树的叶子节点只包含键值对，B树的叶子节点同样是键值对+指针；

           • B+树的叶子节点包含所有的key

        3. 在数据库和文件系统中的优化

           增加顺序访问指针，每个叶子节点增加一个指向相邻叶子节点的指针，形成了带有顺序访问指针的B+树，为了提高区间访问的性能。

   3. innoDB & myISAM & b+树

      1. innoDB创建索引的方式：

         innoDB根据主键ID先创建一个主键ID索引树（b+树），其叶子节点存储的是主键的ID和对应的指针（指向了数据文件的数据）。

      2. innoDB创建索引的时机：

         在建表的时候，innoDB就会自动建立好主键ID的索引树。

         因为这个原因，使用innoDB引擎的mysql要求在建表的时候一定要有主键，没有的话就自动给生成主键。

      3. 手动给字段添加索引会发生什么：

         如果给username字段添加索引，那么MYSQL就会给username添加索引树。

         存的内容是：key为username，value存的是主键key。先在username索引树中找到主键key，再去主键索引树中找到数据的真实地址。

         注：为什么不直接在username索引树中存储真实数据的地址，是因为如果建立了多个索引，每个都保存数据的真实地址，会产生大量冗余数据。

      4. 查询性能对比：

         myisam查询性能更好，

         原因是，Myisam直接找到物理地址后，就可以定位数据，而innoDB查到叶子节点后，还需要再查询一次主键索引树，才能定位到具体的数据。

7. 索引的分类

   1. 根据逻辑-功能分类：

      • 主键索引：一张表只能有一个主键索引，不允许重复，不允许有null

      • 唯一索引：索引列的值，必须唯一，不允许有重复，但是允许有null值，一张表允许有多个唯一索引，如果是组合索引，那么列值的组合必须唯一。

      • 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许null值

      • 全文索引：可以查找文本中的关键词，主要用于全文索引

        
        -- 设置全文索引的最小和最大单词长度
        set global innodb_ft_min_token_size = 3;
        set global innodb_ft_max_token_size = 3;
        
        create FULLTEXT INDEX idx_fulltext on table1 (column1);
        
        -- table1是要进行全文检索的表，column是包含想要搜索文字的列，search_query是具体要搜索的值
        select * from table1 where match(column) against ("search_query");
        
        

   2. 根据逻辑-列数分类

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

      • 组合索引：一个组合索引可以有两个或者两个以上的列，查询时遵循“最左前缀”原则，即，在where语句的组合中，要按照创建索引时字段的排列顺序，索引才能生效。

        注：最左前缀匹配原则：如果创建了一个索引 create index idx_1 on table1 (column1, column2)，在查询的时候，如果用的语句是下面这两条，那么是能够命中索引的

        1. where column1 = 'someString'，
        2. WHERE column1 = 'some_value' AND column2 = 'another_value';

        如果是

        • column2 = 'someString'，就命中不到。只能从最左边开始命中。

        如果是

        • WHERE column1 = 'some_value' AND column3 = 'another_value' AND column2 = 'another_value';这种中间隔了个column3的情况，就不一定，执行计划不一定会充分利用索引。

   3. 根据物理分类：不是单独的索引类型，而是一种数据存储方式

      1. 聚簇索引：innodb的主键索引就是聚簇索引。可以理解为将数据与索引放在了一起，找到了索引也就找到了数据。将索引和数据放在同一个B+树中。

      2. 非聚簇索引：数据和索引是分开的，B+树的叶子节点存放的不是数据表的行记录。innoDB中的辅助索引，以及myISAM使用的都是非聚簇索引，每张表最多

      注：每张表最多只能有一个聚簇索引。

      • 查询速度对比：

        1. 聚簇索引更快的情况：

           • 范围查询和排序：当查询涉及到范围查询或者排序，并且这些操作基于聚簇索引的列，由于相邻的数据行，在磁盘上是相邻存储的，可以减少IO操作，从而提高速度。

           • 覆盖索引：如果聚簇索引包含了查询中所需的所有列，也可以避免操作数据库的数据，直接使用聚簇索引，拿到数据就行了，更快。

        2. 聚簇索引更慢的情况：

           • 插入和更新操作：因为新的数据行需要按照聚簇索引的顺序插入，如果聚簇索引不稳定，频繁插入和删除，可能会导致数据页的分裂和合并，影响性能。

           • 随机插入：聚簇索引对于随机插入的性能比较差，因为数据行需要根据索引的顺序，插入到磁盘中，而不是直接插入到表的末尾。

           • 删除操作：删除操作可能导致聚簇索引产生碎片，从而影响性能。

8. 索引失效

   1. like %xxx 模糊查询

      使用关键字like，在后边的字符串采用%开头，存储引擎会直接进行全表扫描

   2. * 通配符使索引失效

      使用*属于全表扫描，不会使用索引

   3. 负向条件查询not | not in | not like | != | <>会使索引失效

      虽然不能使用 <>，但是能够使用单独的<或者>

      != 负向条件查询导致索引失效，对主键索引，不起作用。对主键索引使用负向查询，仍然能命中缓存。

   4. 索引列参与运算，会让索引失效，

      应该尽量避免在where子句中对条件字段使用表达式操作，存储引擎会放弃使用索引，转而使用全表扫描。

   5. 对字段进行null值判断会使索引失效

      尽量避免在where子句中进行null判断，

      select id, title from article where title is null

   6. 使用or会让索引失效

   7. 违反最左前缀，会让索引失效

   8. 数据类型不一致，索引失效

      select id, title form article where title = 4，如果title本身是字符串类型的，传进来的是int类型的4，那么就会命中不了索引

   9. <|>不等号查询不当，会导致索引失效

      在查询的时候，mysql会一直向右匹配，直到索引搜索键，遇到<|>操作符，就会停止匹配，如果权重最高的索引搜索键使用了范围查询<|>，那么别的<|>都无法再次命中索引，也就是说，最多只有一个<|>指定的范围列可以命中索引，如果查询条件中有两个以上的<|>，无法命中全部的索引。

      select id, title from article where id > 1 and title < '4'

   10. order by 使用不当

       order by 如果是对主键进行排序，一定会命中主键索引，如果对别的列进行排序，不一定会命中索引，取决于多个条件，比如说索引有没有覆盖当前排序的列，不同的索引类型也会影响能否使用索引进行排序。

9. 创建索引的方式简介

   1. create index方式

      create index idx_xxx on table1 (id,title)

   2. alter table 方式

      alter table table1 add idx_xxx (id, title)

      如果想通过一条语句完成多个操作，可以使用这种方式。

10. 禁用索引

    1. 引入：对于使用索引的表，在插入数据的时候，会对新插入的数据创建索引记录`，如果插入了大量的数据，建立索引的工作就会影响插入数据的速度。可以在批量插入之前先禁用索引，在插入完成之后再将索引开启。之后数据库会自动为这些数据构建索引。

    2. 操作语句：

       Alter table table1 disable keys;
       
       alter table table1 enable keys;
       

11. 索引的创建与删除

    1. 创建

       1. 主键索引：

          1. 建表时创建索引

             create table table1 (
                 'id' int(11) not null auto_increment,
                 'name' varchar(32),
                 primary key('id')
             );
             

          2. 修改表结构时创建索引

             alter table table1 add constrant t1_pk primary key (id);
             

          注：如果是字符串字段，还可以制定索引的长度，在列命令后边加上索引的长度就可以了

          create table table1 (
              column1 varchar(255),
              index idx_column (column(10))
          )
          

       2. 唯一索引：

          1. 建表时创建

             create table table1 (
                 name varchar(32),
                 unique INDEX index_unique_table1_name(name)
             );
             

          2. 建表后创建

             create unique index index_table1_name on table1(name);
             

          3. 修改表结构时创建

             alter table table1 add unique index index_table1_name(name);
             

       3. 普通索引

          1. 建表时创建

             create table table1 (
                 name varchar(32),
                 index index_table_name (name)
             );
             

          2. 建表后创建

             create index index_table1_name on table1(name);
             

          3. 修改表结构时创建索引

             alter table table1 add index index_table1_name(name);
             

       4. 全文索引

          1. 注：全文索引主要用来查找文本中的关键字，而不是直接与索引中的值进行比较，fulltext索引与别的索引不太一样，更像是一个搜索引擎，而不是简单的where语句的参数匹配。 fulltext索引配合match against使用，而不是使用where like，可以在create table, alter table, create index时使用，不过目前只有char, varchar, text列上可以创建使用全文索引

          2. 建表时创建

             create table 'article' (
                 'id' int(11) not null auto_increment,
                 'title' char(250) not null, 
                 'content' text null，
                 'create_at' int(10) null default null,
                 primary key ('id'),
                 fulltext(contents)
             );
             

          3. 建表后创建

             create fulltext index index_article_content on article()
             

          4. 修改表结构时进行创建

             alter table article add fulltext index index_article_contents(contents);
             

       5. 组合索引

          1. 组合索引是多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用，使用组合索引时遵循最左前缀原则

          2. 在创建表的时候

             create table table1(
                 'id' int(11),
                 'name' varchar(32),
                 index idx_table1_id_name('id', 'name')
             );
             

          3. 在建表后

             create index idx_table1_id_name on table1 (id, name);
             

          4. 修改表结构时

             alter table table1 add index index_table1_id_name (id, name);
             

    2. 删除

       drop index idx_table1_column1 on table1
       
       alter table table1 drop index idx_table1_column1;
       

七、速度优化

limit速度优化

1. 加缓存

   通过添加缓存，减少对数据库的直接操作，可以通过redis，也可以通过elasticSearch

2. 使用延迟关联+子查询

   即，先利用limit得到数据的索引字段，然后再通过原表和索引字段关联，得到需要的数据。

   select a.* from tableA a, (select id from tableB limit 100000, 20) b where a.id = b.id;
   

   原理：

   1. 减少了数据传输：只有子查询的20行数据会被传输到主查询中，而不是整个表B，这可以减少数据传输的开销，尤其是当表B包含了大量的数据。

   2. 减少了比较操作：主查询中的条件a.id = b.id，只需要在子查询选择的20行数据上执行，而不是在整个表B上执行，这减少了比较的操作数量，有助于提高查询性能

   3. 减少索引扫描：如果表B有索引，子查询只需在索引的一小部分上执行，而不是在整个索引上执行，可以减少索引扫描的开销。

3. 记录上次查询的最大id

   如果id是连续的，我们可以把每次查询出的最大id记录下来，比如下面的sql语句，因为知道了上次查询的最大值是100020，所以直接从100020开始查。

   select id, name from users where id > 100020 order by iod asc limit 20;

4. 使用join进行查询

   可以将sql语句改变一下，使用join进行查询，这个执行效率也比直接查询快得多，

   select * from tableName as t1 
   join (select id from tableName order by id desc limit 20000, 10) as t2
   on t1.id <= t2.id order by t1.id desc limit 10
   

   注：使用join看似是使用笛卡尔积，但其实不是的，在实际执行计划中，数据库优化器会尽量避免生成完整的笛卡尔积，而是通过索引和连接条件，以更有效的方式进行连接操作。

5. 从业务上实现

   不做过多的分页，只分前100页，后面的不允许再分页查询了

6. 不使用limit N,M

   可以使用Limit N，将offset转化为where条件

sql查询日志

1. 慢SQL：

   就是查询很慢的sql，mysql作为一种数据库系统，本身提供了一种慢查询的日志记录，用来记录Mysql中超过特定阈值的sql语句，只要某个select语句的执行时间超过了这个日志中long_query_time规定的值，就可以将select看做是慢sql，该语句就会被记录进慢查询日志中。默认情况下，long_query_time是10秒钟，也就是说，只要一条sql执行时间超过10s，就是慢sql。当然也可以根据自己的项目进行更改。

2. 查看慢sql阈值

   mysql中查询慢sql的阈值

   show variables like 'long%'

3. 更改慢sql阈值

   默认请款修改，Mysql是没有开启慢查询查询日志，这个功能。如果想检测慢sql，需要先进行开启。

   show variables like 'slow%'

   变量名slow_query_log，如果是off，就代表没有打开慢日志查询。

   set global slow_query_log = 1就可以进行打开了

   通过这个命令，只是打开当前开启的数据库，重启之后就失效了，如果需要永久生效，需要修改mysql数据库的配置文件my.cnf

   [mysqld]
   # 开启全局的查询日志功能
   slow_query_log=1
   slow_query_log_file=E:\mysql-5.7.26-winx64\data\zahngsanfeng39C2-slow.log
   # 设置全局的日志查询阈值时间
   long_query_time=3
   log_output=FILE
   

   设置之后重启下mysql服务器生效

   开启了之后，就可以进行查看，有多少条sql语句被加入到了慢sql查询日志中

   show GLOBAL STATUS LIKE '%Slow_queries%'

4. 更改慢查询时间阈值

   set global long_query_time=3

   当然也能在my.config文件中进行全局修改配置

5. 开启未使用索引的查询语句日志记录功能

   想提高查询效率，很多情况下都要使用索引，为了知道有没有命中索引，Mysql提供了一个功能，将未使用索引的查询语句记录到日志中，可以开启这个语句对sql进行调优。

慢sql

1. 产生的原因

   • sql语句问题

   • 数据库及表锁定

   • 服务器硬件配置低

   • mysql本身有故障

   • 其他原因

2. 程序员着重需要解决的原因

   1. 数据量过大，需要的I/O次数过多（因为数据量大的原因）

   2. 数据读取的方式有问题，缓存有没有，索引有没有，是否直接从磁盘进行读取的

   3. 数据加工方式不完美，数据有没有进行排序，有没有进行子查询

3. 解决思路

   1. 将数据适当进行缓存

   2. 适当合并I/O：如果分别执行 select c1 from t1与select c2 from t1，与select c1, c2 from t1相比，后者的开销更小。

   3. 合理进行分布式架构：在处理海量数据的时候，考虑将数据和I/O分散到多台主机上进行

慢sql预防

1. 避免索引失效

   1. like %会使索引失效
   2. • 通配符会使索引失效
   3. not | != | <> | not in | not like 等负向条件会使普通索引失效
   4. 索引列参与计算（±*/）会使索引失效
   5. 对字段进行Null值判断，会使索引失效
   6. 使用or连接会让索引失效
   7. 违反最左前缀原则会让组合索引失效
   8. 数据类型不一致会让索引失效
   9. > <范围查询不当会让索引失效
   10. order by 使用不当会让索引失效

2. 优化表结构设计

   • 在满足需求的前提下，尽量使用小的数据类型
   • 尽量使用tinyint, smallint, mediumint作为整数类型，而非int
   • 尽可能使用not null定义字段，因为null会占用4个字节
   • 数字类型默认为0，字符串类型默认为""
   • 尽量少用text类型，非用不可时，最好独立出一张表
   • 尽量使用timeStamp，而非dateTime
   • 单表不要有太多字段，建议在20个字段之内

3. 别的注意事项

   • 用exists代替in，比如select num from tablea where exists (select y from tableb where num = a.num);

   • 避免频繁创建和删除临时表，以减少系统表资源的消耗

   • 用join代替in，join的效率高于in，因为join不需要创建临时表

   • 尽量避免大事务操作，提高系统并发能力

   • 优化分页查询

4. 定位解决慢sql

   1. 治理优先级

      如果存在多个慢sql，时间精力有限，需要有所侧重，应该遵循如下的治理优先级原则

      1. 如果存在主从分离，则先解决master主库，再解决slave从库

      2. 执行次数多的sql优先治理

      3. 如果某个sql语句会高并发地访问某一张表，应该优先治理

   2. 查看慢查询日志

      首先要确认到底是哪里产生了慢sql，然后对症下药，

      show global status like '%slow_queries%';
      

      如果没有产生慢sql，那么value字段的值是0，如果有，value就是对应的条数

      打开之前配置的mysql-slow.log日志文件，可以看到相关的数据。

      除了使用日志来查看慢sql还能使用别的框架查看

   3. ActivePerl

      mysql自带了一个perl脚本文件，可以通过avtivePerl这个工具进行使用，安装了这个perl工具之后，就能在命令行中使用perl脚本了，可以当成命令来使用。

      # 查询得到记录集最多的10个sql
      # 该命令后边跟着自己之前配置的慢sql文件位置即可
      mysqldumpslow.pl -s r -t 10 ..\data\zhangsanfeng39c2=slow.log
      

      mysqldumpslow命令，常用的命令：

      • –help：输出帮助信息
      • -v 输出详细信息
      • -d 调试
      • -s 按照什么排序，默认是at，显示顺序为倒序
      • al 平均锁表时间
      • ar 平均结果行数
      • at 平均查询时间
      • c 次数
      • l 锁表时间
      • r 总结果行数
      • t 总查询时间
      • -r 正序排序 即从小到大排序
      • -t num限制显示的条数
      • -a 显示出数字和字符串 默认数字为N 字符串为’S’
      • -g pattern过滤字符串 ，后接正则表达式 如’10$'以10为结尾的条件

      # 得到访问次数最多的10个sql
      mysqldumpslow.pl -s c -t 10 slow.log
      
      # 得到按照时间排序的前10条里面含有左连接的查询语句
      mysqldumpslow.pl -s t -t 10 -g "left join" slow.log
      

      通过这样的工具执行，就能知道现在有多少条和那几条慢sql了

   4. show profile

      还可以使用另一个工具，show profile，这是mysql提供的用来分析某个sql语句执行所消耗资源情况的命令，可以为sql语句的调优提供量化指标，但是该命令默认是关闭的，需要手动开启该功能。可以查询sql语句在服务器中的执行细节和生命周期。

      1. 查看功能是否开启

         show variables like 'profiling' 如果显示value为off，就代表是关闭的

      2. 开启

         set profiling=ON

      3. 查看sql执行历史记录

         show profiles，这个命令能将之前执行过的所有的sql都记录下来，显示结果包含query_id, duration, query，分别代表查询的id， 执行的时间，执行的语句。

         根据上边的id，执行show profile cpu, block io for query 123：123是上边的id。

         可选查看参数：

         • ALL：所有开销
         • block io：显示io相关开销
         • context switches：显示上下文切换相关开销
         • cpu：显示cpu相关开销
         • ipc：显示发送接收相关开销
         • memory：显示内存相关开销
         • page faults：显示页面错误相关开销
         • source：显示和source_function, source_file, source_line相关开销
         • swaps：显示交换次数相关开销

         如果执行上述命令，如果遇到如下几种情况就要考虑进行优化：

         • converting heap to myisam：查询结果太大，内存不够用
         • create tmp table：创建临时表
         • copy to tmp table on disk：将内存中的临时表复制到磁盘
         • locked：有锁定

      4. explain分析慢sql语句

         通过上边的一系列步骤，就可以知道有多少条慢sql，以及那些慢sql，接下来可以使用explain命令进一步分析，可以得到如下信息：

         • 表的读取顺序

         • 表的读取操作的操作类型

         • 哪些索引可以被使用

         • 哪些索引被实际使用

         • 表之间的引用

         • 每张表有多少行被优化器查询

      5. mysql服务器参数调优

         如果经过上述措施之后，sql的执行效果仍然不理想，比如order by和group by 无法使用索引，可以增大mysql服务器的max_length_for_short_data和sort_buffer_size等参数的设置

explain

1. 作用

   1. 查看表的读取顺序
   2. 查看数据库读取操作的类型
   3. 查看有可能用到那些索引
   4. 查看真正用到那些索引
   5. 查看表之间的引用关系
   6. 查看表中有多少行记录被优化器查询

2. 用法

   1. 语句：explain + sql语句

   2. 包含的内容

      1. id：选择标识符

         id是按照sql语法解析后分层的序号，代表着select查询的序列号，每个select查询都会自动分配一个唯一的标识符，包含一组数字，表示查询中执行select子句或者操作表的顺序，该值可能会重复，这个id值有以下三种情况：

         • id值完全相同，会按从上到下的执行顺序。
         • id值完全不同，如果是子查询，id的序号会递增，id值越大则优先级越高，也会优先被执行
         • id值有相同也有不同的，值相同的部分，会按从上到下的顺序执行，值不同的部分，id值越大，优先级越高，也就越先被执行

      2. select_type：表示查询的类型

         主要是用来区分普通查询，联合查询，子查询等查询方式，有以下几种情况：

         • simple：代表简单select 查询，不使用union及子查询

         • primary：代表最外层的select查询

         • union：代表union中的第二个，或者随后的select查询，不依赖于外部查询的结果集

         • dependent union：代表union中的第二个或者随后的select查询，依赖于外部查询的结果集

         • subquery：代表子查询中的第一个select查询，不依赖于外部查询的结果集

         • dependent subquery：代表子查询中的第一个select查询，依赖于外部查询的结果集

         • derived：用于from 子句里有子查询的情况，mysql会递归执行这些子查询，将结果放到临时表中

         • uncacheable subquery：结果集不能被缓存的子查询，必须重新为外层查询的每一行进行评估

         • uncacheable union：union中的第二个或者随后的select查询，属于不可缓存的子查询

         在开发中，最常用的是以下6种

         1. simple

            简单的select查询，查询中不包含子查询或者union等

            --这个语句查出来的select_type就是simple
            explain select * from pet where name = 'Bowser'
            

         2. primary

            查询中若包含任何复杂的子部分，比如两表做union或者存在子查询，则外层的表操作类型为primary，内层的操作为union。

            --结果包含两条，分别是primary和subquery
            explain select article, dealer, price from shop where price = (select max(price) from shop);
            

         3. subquery

            表示在select 或者where 列表中包含了子查询

            explain select article, dealer, price from shop where price = (select max(price) from shop);
            

         4. derived

            在from 列表中包含的子查询被标记为derived（衍生），mysql会递归执行这些子查询，然后将结果放到临时表中

            explain select s1.article, dealer, s1.price
            from shop s1
            join (
                select article, max(price) as price
                from shop
                group by article
            ) as s2
            on s1.article = s2.article and s1.price = s2.price
            

         5. union

            若第二个select 出现在union之后，则被标记为union，若union包含在from的子查询中，外层的select被标记为derived。

         6. union result

            从union表获取结果的select

            explain
            select owner from pet where name = 'Fluffy'
            union
            select type from event where name = 'fluffy' 
            

      3. table：输出结果集的表

         table 表示我们查询的是那张表，如果sql中表有别名，这里出现的就是别名

      4. partitions：匹配的分区

         匹配的分区

      5. type：表示表的连接类型

         显示查询使用的是那种访问类型

         • system：从系统表中读取一行，这是const连接类型的一个特例
         • const：表示最多有一个匹配行，它将在查询开始时被读取，因为仅有一行，该行的列值，可被优化器认为是常数。const表很快，因为他们只读一次。
         • eq_ref：表示查询条件是“等于”
         • ref：表示查询条件是“不等于”
         • ref_of_null：同ref，包含null值的行
         • index_merge：代表索引联合查询
         • unique_subquery：表示利用唯一索引进行子查询
         • index_subquery：表示用非唯一索引进行子查询
         • range：表示按照索引范围进行扫描
         • index：表示按照索引扫描
         • all：表示进行全表扫描

         这几种类型的性能从最好到差依次为：

         system > const > eq_ref > ref > range > index > all

         一般来说，要保证查询至少到range级别，最好能达到ref级别

         • system：表中只有一行记录（等于系统表），这是const的特例，平时不会出现

         • const：const一般用于比较primary key或者unique索引，只匹配一行数据，索引性能比较高，比如我们根据主键作为查询条件进行查询，mysql优化器会将该查询转换为一个const常量，这表示通过索引一次就找到了结果。

         • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与其陪陪，常见于主键或者唯一索引扫描，也就是说当我们使用了主键或者唯一性索引进行查找的时候，明确知道查找的结果只有一个，这个时候type的值就是eq_ref，如下图中的这种连接类型，每次都进行精确查询，无须过多的扫描，因此查询效率更高，当然列的唯一性是需要根据实际情况决定的

           explain select * from t1, t2 where t1.id = t2.id
           

         • ref：当查找的内容用到了索引，但不是主键索引好unique索引，即索引列的值不是唯一的，会有重复，会返回匹配某个单独值的所有行，当使用索引快速查找到了第一条数据，它仍然不会停止，会进行目标值附近的小范围扫描，这样的好处是他不需要进行全表扫描，因为索引是有序的，即使有重复的值，也是在非常小的范围内进行扫描，本质上是一种索引访问，可能会找到多个符合条件的行。

         • range：当我们在查询中使用了between, and, >，<，in，or时，就会变成range类型，这是一种带有范围的索引扫描，表示只检索指定范围内的行，相对于index的全部索引扫描，它有范围限制，有扫描的起始点，因此性能要强于index

         • index：full index scan，全索引扫描，这是另一种形式的全表扫描。index与ALL都是扫描全表，但index与All的区别是，index只扫描索引树，是先从索引树中读取索引记录，然后再回表取数据；ALL是从硬盘中读取数据。官方文档说是比ALL查询快，因为一般索引文件比数据文件小。

           explain select * from employee order by rec_id;
           

         • all：这是Full Table Scan，即“全表扫描”，会通过遍历全表找到匹配的行。出现ALL通常意味着我们的SQL语句处于一种最原生的状态，有很大的优化空间，ALL是一种非常暴力和原始的查找方法，非常的耗时而且低效。

      6. possible_keys：表示查询时，可能使用的索引。比如某个查询字段，可能关联一个或多个索引，那么这些索引都将被列出，但不一定被查询时实际使用。

      7. key：表示实际使用的索引，若该值为null，则表示没有使用到索引，产生该结果有两种可能性：

         1. 没有创建索引

         2. 索引失效

         若查询中使用饿了覆盖索引，则该索引仅仅出现在key列表中

      8. key_len：索引字段的长度

         表示索引中使用的关键字长度，可通过该列计算出查询时使用的索引的长度。在不损失精确性的情况下，该值越短越好，key_len显示的是索引字段的最大可能长度，而非实际使用的长度。key_len是根据定义计算得到的，而不是通过表内检索得到的，当我们查询时的条件用的越多，结果就会越准确，那么key_len就会越长，能查到相同结果的前提情况下，条件越少越好。

      9. ref：列与索引的比较

         指的是表之间的引用，一般是指 = 后面的内容，代表使用哪个列或者常数，与key一起从表中选择行。通俗的说，就是ref可以显示索引中哪一列或者const常量，在查询中被使用了，用于查找索引列对应的值，常与key一起被使用。

      10. rows：扫描出的行数（估算的行数）

          显示本次查询中，mysql执行查询时大概会扫描多少行，这是一个估计值，该值越小越好，如果值很大，说明索引没有很好发挥作用。

      11. filtered：按表条件过滤的行百分比

          表示该次查询中，复核某个条件的记录数所占的百分比

      12. extra：执行情况的描述和说明

          该列包含mysql解决查询的额外详细信息，一般表示当前使用多个索引类型，该列的值有如下：

          • distinct：表示去重，返回第一个满足条件的值

          • not exists：表示使用not exists查询

          • range checked for each record：表示有索引，但索引的选择率很低

          • using filesort：代表有序索引

          • using index：代表使用了索引扫描

          • using index condition：代表了索引查询

          • usingtemporary：代表进行临时表检索

          • Using where：代表使用where 条件查询

          • using index & using where ：代表使用了索引和条件查询

          • NULL：代表查询的列未被索引覆盖，并且where 筛选条件是索引的前导列

          • Using sort_union：代表进行有序合并查询

          • using union：代表进行了合并查询

          • using intersect：代表索引交叉合并

          • impossible where noticed after reading const tables：代表读取const tables 查询结果为空

          • no tables used：表示没有使用表

          • using join buffer(Block nested Loop)：代表使用join buffer（BNL）算法

          • Using MRR（Multi-Range Read） ：表示使用辅助索引进行多范围读取

          extra 常用的值有using index, using where, using index condition, using index & using where, null等

          extra详解：

          1. using index：当查询的列完全被索引覆盖，并且where中的筛选条件是索引前导列，会出现该值。

          2. using where; using index：有两种情况会出现这种：

             1. 当查询的列被索引覆盖，且where中的查询条件是索引列之一，但不是前导列。这也意味着无法直接通过索引查找来查询到符合条件的数据。

             2. 当查询的列被索引覆盖，并且where中的查询条件表示索引前导列的一个范围，同样意味着无法通过索引查找，来查询到符合条件的数据。

          3. using where：两种情况会出现这个

             1. 当查询的列没有被索引覆盖，并且where中的筛选条件是非索引前导列，extra中为using where

             2. 当查找的列没有被索引覆盖，且where中的筛选条件是非索引列，extra中为using where

             注：using where表示通过索引或者表扫描的方式进行where条件的过滤，也就是说，没有可用的索引查找，这里的type都是all，说明mysql认为全表扫描是一种性能比较低的方式。

          4. using index condition

             1. 查询的列不全在索引中，where条件是一个前导列的范围

             2. 查询列没有被完全的索引覆盖，查询条件完全可以使用到索引。

             注，当出现当前using index condition时，意味着在多个查询列中，有些列无法直接使用索引，会根据能用索引的条件先搜索一遍，然后再匹配无法使用索引的条件。

          5. null

             查询的列未被索引覆盖，并且where筛选条件是索引的前导列。extra出现Null，说明还是用到了索引，但是部分字段没有被索引覆盖，必须通过“回表”实现。没有纯粹地用到索引，也不是完全没用到索引。

          6. using filesort

             mysql中无法利用索引完成的排序被称为“文件排序”，这说明mysql查询时对数据的使用，是一个外部的索引排序，而不是按照索引的默认排序进行的读取。一般在使用order by 关键字的时候，如果待排序的类，不能由该排序列所使用的索引直接完成排序的情况，那么mysql就会使用文件排序。

             出现using filesort时，mysql会将数据在内存中进行排序，排序时使用的内存区域，时通过sort_buffer_size系统变量设置的排序区，这个排序区是每个thread独享的，所以有可能在同一时刻，mysql存在多个sort buffer内存区域。

             mysql中filesort排序算法实际上有两种：

             双路排序：首先根据相应的条件，去除相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer中进行排序。

             单路排序，是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。

             这种文件排序效率比较低，出现这种情况可以进行优化：

             1. 修改代码逻辑，不在Mysql中使用order by，而是在自己的代码中进行排序

             2. 使用Mysql索引，为待排序的列创建索引，直接利用索引的排序

          7. using temporary

             mysql在对查询结果排序时，会使用临时表，用于保存中间结果，常见于order by 和group by分组查询。