mysql高级教程笔记

MySQL高级教程

1、存储引擎

1.1、MyISAM和InnoDB

对比项MyISAMInnoDB
主外键 不支持 支持
事务 不支持 支持
行表锁 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 行锁，操作时只锁某一行，不对其他行有影响。适合高并发的操作
缓存 只缓存索引，不缓存真实数据 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间 小 大
关注点 性能 事务
默认安装 Y Y

2、索引优化

2.1、SQL执行顺序

from -> on -> left/where -> group by -> having -> select -> order by -> limit

2.2、索引简介

2.2.1、索引是什么

  • MySQL官方对索引的定义为：索引是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。

  • 数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

  • 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上

  • 我们平时所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。

2.2.2、SQL索引分类

  1. 单值索引：即一个索引只包含单个列，一个表可以有很多个单列索引（最好不超过5个）

  2. 唯一索引：索引列的值必须唯一，但允许有空值

  3. 复合索引：一个索引包含包含多个列

  4. 基本语法

    • 创建：

      • create [UNIQUE] index 索引名 on 表名(字段)

      • alter 表名 add [UNIQUE] index 索引名 on (字段)

    • 删除：drop index [索引名] on 表名

    • 查看：show index from 表名

  5. 哪些情况下需要创建索引：

    • 主键自动建立唯一索引

    • 频繁作为查询条件的字段应该创建索引

    • 查询中与其他表关联的字段，外键关系建立索引

    • 频繁更新的字段不适合创建索引

    • where条件里用不到的字段不建立索引

    • 单键/组合索引的选择问题（在高并发下倾向创建组合索引）

    • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

    • 查询中统计或者分组字段

  6. 哪些情况不要建索引：

    • 表记录太少

    • 经常增删改的表：不仅要维护数据，还要维护索引结构

    • 数据重复且平均分布的表字段，应该只为最经常查询和最经常排序的数据列建立索引。

      • 如果某个数据列包含很多重复的内容，为他建立索引就没有太大的实际效果，例如 性别 等

2.2.3、性能分析（explain查看执行计划）

image-20211220144425161

  1. 能干嘛：

    • 表的读取顺序

    • 数据读取操作的操作类型

    • 哪些索引可以使用

    • 哪些索引被实际使用

    • 表之间的引用

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

  2. explain 之 id：

    select查询的序列号，包含一组数字，表示查询中执行select字句或操作表的顺序，有以下三种情况：

    • id相同：

      执行顺序由上向下

    • id不同：

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

    • id 相同不同，同时存在：

      id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

  3. explain 之 select_type：

    查询的种类，主要用于区别普通查询、联合查询、子查询等的复杂查询。

  4. explain 之 table：

    显示这一行数据是关于哪张表的。

  5. explain 之 type：

    显示查询使用了何种类型，从最好到最差依次是： system > const > eq_ref > ref > range > index > ALL

    • 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 （全表扫描），将遍历全表以找到匹配的行。

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

  6. explain 之 possible_key：

    显示可能应用在这张表中的索引，一个或多个。

    查新涉及到的字段上若存在索引，则该索引将被列出，不一定被查询实际使用。

  7. explain 之 key：

    实际上使用的索引。如果为 NULL ，则没有使用索引。

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

  8. explain 之 key_len：

    表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。

    key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len 是根据表定义计算而得，不是通过表内检索出的。

  9. explain 之 ref：

    显示索引的哪一列被使用了，如果可能的话，是一个常量（const）。哪些列或常量被用于查找索引列上的值。

  10. explain 之 rows：

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

  11. explain 之 Extra：

    包含不适合出现在其他列但又十分重要的额外信息：

    • Using filesort ：

      说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 ​ MySQL中无法利用索引完成的排序操作成为“文件排序”。

    • Using temporary：

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

    • Using index：

      表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错！

      如果同时出现using where，表明索引被用来执行索引键值的查找；

      如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

      • 覆盖索引：

        就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引

        再次读取数据文件，换句话说查询列要被所建的索引覆盖。

        注意：

        如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select * ； 如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

         

2.2.4、索引优化

  1. 索引失效：

    • 全值匹配我最爱

      复合索引的所有值都匹配时，索引不会失效。

    • 最佳左前缀法则

      如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。（例如，1、12、123索引不会失效）

    • 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

    • 存储引擎不能使用索引中范围条件右边的列

      即where条件中的索引列中出现了范围（in、between and、<、>），则后面的索引列全部失效 例如：select * from staff where name = 'July' and age > 25 and pos = 'manager';(索引为idx_name_age_pos) 此时，只有name 和 age会走索引，而pos会索引失效

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

      例如：select name，age，pos from staff where name = 'July' and age > 25 and pos = 'manager';(索引为idx_name_age_pos) 此时，只有name会走索引，而age和pos会索引失效

    • MySQL在使用不等于（ != 或者 <> ) 的时候无法使用索引会导致全表扫描

    • is null ，is not null也无法使用索引

    • like以通配符开头（‘%abc...’）MySQL索引失效会变成全表扫描的操作

      • 解决 like '%字符串%' 索引失效的问题： 推荐使用覆盖索引来解决。select 的查询列可以为主键、复合索引列，其余索引失效。

    • 字符串不加单引号索引失效

    • 少用or，用它来连接时会索引失效

  1. 总结：

    假设index（a，b，c）

    where 语句索引是否被使用
    where a = 3 Y,使用到 a
    where a = 3 and b = 5 Y,使用到 a，b
    where a = 3 and b = 5 and c = 4 Y,使用到 a，b，c
    where b = 3 、where b = 3 and c = 4、where c = 4 N
    where a = 3 and c = 5 Y,使用到a，但是c不可以，b中间断了
    where a = 3 and b > 4 and c = 5 Y,使用到 a 和 b ，c不能用在范围之后，b断了
    where a = 3 and b like 'kk%' and c = 4 Y，使用到a，b，c
    where a = 3 and b like '%kk' and c = 4 Y,使用到 a
    where a = 3 and b like '%kk%' and c = 4 Y,使用到 a
    where a = 3 and b like 'k%Kk%' and c = 4 Y，使用到a，b，c
2.2.5、查询截取分析
2.2.5.1、查询优化

  1. 永远小表驱动大表，类似于嵌套循环Nested Loop

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

      当B表的数据集必须小于A表的数据集时，用 in 优于 exists

    • select * from A where exists (select 1 from B where B.id = A.id)

      当A表的数据集必须小于B表的数据集时，用 exists 优于 in

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

    知识补充：Exists

    1. select ... from table where exists (subquery)

      该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。

    1. 提示：

      • exists (subquery)只返回TRUE或者FALSE，因此子查询中的select * 也可以是select 1 或者其他，官方说是实际执行时会忽略select清单，因此没有区别。

      • exists 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可以实际检验以确定是否有效率问题。

      • exists 子查询往往也可以使用条件表达式、其他子查询或者join来代替，何种最优解需要具体问题具体分析。

     

  2. order by 关键字优化：

    1. order by 字句，尽量使用 index 方式排序，避免使用 FileSort 方式排序

      • MySQL支持两种方式的排序，FileSort 和 Index ，Index 效率高，它值mysql扫描索引本身完成排序。FileSort 方式效率较低。

      • order by 满足两种情况，会使用 Index 方式排序

        • order by 语句使用索引最左前列

        • 使用where字句与order by 子句条件组合满足索引最左前列

    2. 尽可能在索引列上完成排序操作，遵照索引的最佳左前缀原则

    3. 如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

      • 优化策略：

        • 增大sort_buffer_size参数的设置

        • 增大max_length_for_sort_data参数的设置

    4. 为排序使用索引：

      1. MySQL两种排序方式：文件排序和扫描有序索引排序

      2. MySQL能为排序和查询使用相同的索引

        例如，key a_b_c (a,b,c)

        • order by 能使用索引最左前缀：

          • order by a

          • order by a,b

          • order by a,b,c

        • 如果where使用索引的最左前缀定义为常量，则order by能使用索引：

          • where a = const order by b,c

          • where a = const and b = const order by c

          • where a = const order by b,c

          • where a = const and b > const order by b,c

        • 不能使用索引进行排序：

          • order by a ASC, b DESC, c DESC <!--排序不一致-->

          • where g = const order by b,c <!--丢失a索引列-->

          • where a = const order by c <!--丢失b索引列-->

          • where a = const order by a,d <!--d不是索引列的一部分-->

          • where a in (...) oeder by b,c <!-- 对于排序来说，多个相等条件查询也是范围查询 -->

  3. group by 关键字优化：

    1. group by 实质是先排序后进行分组，遵照索引的最佳左前缀原则

    2. 当无法使用索引列时，增大sort_buffer_size参数的设置 + 增大max_length_for_sort_data参数的设置

    3. where 高于 having，能写在 where 限定的条件就不要去 having 限定了

    4. 其余优化同order by 优化

       

2.2.5.2、慢查询日志

 

2.2.5.3、批量数据脚本

 

2.2.5.4、show profile

  1. 是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。

  2. 默认情况下，参数处于关闭状态，并保存最近15次的运行结果

  3. 分析步骤：

    1. 是否支持，看看当前的mysql版本是否支持

    2. 开启功能，默认是关闭，使用前需要开启

    3. 运行SQL

    4. 查看结果，show profiles

    5. 诊断SQL， show profile cpu,block io ... for query id （id为上一步前面的问题SQL数字号码）

    6. 日常开发需要注意的结论

      • converting HEAP to MyISAM :查询结果太大，内存都不够了，往磁盘上搬了。

      • creating tmp table ：创建临时表

        • 拷贝数据到临时表

        • 用完再删除

      • copying to tmp table on disk ：把内存中临时表复制到磁盘，危险！！！

      • locked ：锁住了

2.2.5.5、全局查询日志

只允许在测试环境使用，永远不要在生产环境开启这个功能！！！

 

3、mysql锁机制

3.1、概述

  1. 定义：

    锁是计算机协调多个进程或线程并发访问某一资源的机制。

  1. 锁的分类：

    1. 从对数据操作的类型来分：

      • 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

      • 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

    2. 从对数据操作的粒度来分：

      1. 表锁

      2. 行锁

     

    1. MySQL的三锁：

      1. 表锁（偏读）：

        1. 特点：

          偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

        2. 语法：

          • 给表加锁

            lock table table_name [read/write] , table_name [read/write] ...;

          • 释放锁

            unlock tables;

          • 查看正在被锁定的表

            show open tables;

        3. 结论：

          • 对MyISAM表的读操作（加读锁），该线程不能对表进行修改，自己和其他线程只能读取该表。

          • 对MyISAM表的写操作（加写锁），该线程可以对这个表进行读写，其他线程对该表的读和写都受到阻塞。

          • 简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁会堵塞写和读

        4. 表锁分析：

          1. 语句：

            • show status like 'table%';

          2. 会有两个状态记录MySQL内部级表级锁定的情况，两个状态值都是从系统启动后开始记录，出现一次对应的事件则数量加1两个变量说明如下：

            • Table_locks_immediate：产生表级锁定的次数；

            • Table_locks_waited：出现表级锁定争用而发生等待的次数；此值高则说明存在着较严重的表级锁争用情况

          3. 此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

             

      1. 行锁（偏写）：

        1. 特点：

          偏向InnoDB引擎，开销大，加锁慢；会出现死锁；锁粒度最小，发生锁冲突的概率最低，并发度也最高。

          InnoDB与MyISAM的最大两个不同点：一是支持事务；二是采用了行级锁。

        2. 索引失效会使行锁变成表锁。

        3. 间隙锁：

          • 定义：

            当我们用范围条件而不是相等条件检索数据，并请求共享或排它锁时，innodb会给符号条件的已有数据记录的索引项加锁；对于值在条件范围内但并不存在的记录，叫做间隙（GAP）,InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。

          • 危害：

            因为Query执行过程中，通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。

            间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

        4. 如何锁定一行：

          select xxx... for update 锁定某一行后，其他的操作会被阻塞，直到锁定行的会话提交commit。

        1. 行锁分析：

          1. 语句：

            show status like 'InnoDB_row_lock%'

          1. 对各个状态量的说明如下：

            • InnoDB_row_lock_current_waits：当前正在等待锁定的数量；

            • 【*】InnoDB_row_lock_time：从系统启动到现在锁定总时间长度；

            • 【*】InnoDB_row_lock_time_avg：每次等待所花平均时间；

            • InnoDB_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；

            • 【*】InnoDB_row_lock_waits：系统启动后到现在总共等待的次数；

        2. 优化建议：

          1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

          2. 合理设计索引，尽量缩小锁的范围

          3. 尽可能较少检索条件，避免间隙锁

          4. 尽量控制事务大小，减少锁定资源量和时间长度

          5. 尽可能低级别事务隔离

 

 

 

 

 

 

 

 

 

 

 

 

posted @ 2021-12-22 15:59  唯美食不可辜负  阅读(194)  评论(0)    收藏  举报