您的位置: 首页  >  文章  >  Mysql优化

Mysql优化

分类: 文章 • 2024-08-30 10:58:04

Mysql优化

MySQL体系架构

  1. 连接层：主要完成一些类似于连接处理，授权认证及相关的方案；

  2. 服务层：主要完成大多数核心服务功能；

  3. 引擎层：负责MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信；

MySQL存储引擎

InnoDB 和 MyISAM区别：

  1. InnoDB支持主外键、事务；
  2. InnoDB是行锁，操作时候只锁一行数据，适合高并发；MyISAM是表锁；
  3. InnoDB不仅缓存索引，还缓存真实数据；MyISAM只缓存索引；
  4. InnoDB需要表空间大；
  5. InnoDB关注事务，MyISAM关注性能（查）；

Mysql索引

  1. 索引的本质
    1. 排好序的快速查找数据结构；
    2. 目前大部分数据库系统及文件系统都采用B Tree或其变种B+Tree作为索引结构
    3. 与B Tree相比，B+Tree有以下不同点
      1）每个节点的指针上限为2d
      2）内节点只存key
      3）为所有叶子节点增加了一个链指针
      4）叶节点不存指针,叶节点指向被索引的数据而不是其他叶节点,在innodb中,指向的是主键,myshaym中指向的是数据的物理地址
    4. 为什么使用B Tree(B+Tree)
      1）索引本身也很大，不可能全存内存，往往以索引文件的形式存在磁盘
      2）索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度
      3）索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数
  2. 索引实现

    MyISAM索引实现

    1. MyISAM引擎使用B+Tree作为索引结构，叶节点data域存放数据记录的地址

      Mysql优化

    2. 在MyISAM中，主索引和辅索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅索引的key可以重复

      Mysql优化

    3. MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录

    4. MyISAM的索引方式也叫做“非聚集”的

    InnoDB索引实现

    1. 重大区别是InnoDB的数据文件本身就是索引文件

    2. MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址

    3. 在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引

      Mysql优化

    4. 可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引

    5. 因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形

    6. 第二个与MyISAM索引的不同是InnoDB的辅索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如

    Mysql优化

    1. 聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

    2. 例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅索引都引用主索引，过长的主索引会令辅索引变得过大。

    3. 再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择

    4. MyISAM会比Innodb的查询速度快

      1. 数据块，INNODB要缓存，MYISAM只缓存索引块， 这中间还有换进换出的减少；

      2. innodb寻址要映射到块，再到行，MYISAM记录的直接是文件的OFFSET，定位比INNODB要快

      3. INNODB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护

        MVCC (Multi-Version Concurrency Control)多版本并发控制

    5. 索引的使用策略及优化

      1. 索引的好处：提高数据检索的效率，降低数据排序的成本

      2. joinl连接索引分析：

        1. 单表分析：创建复合索引要保证后续的索引不失效，如果失效则可以建立从头到开始失效位置的索引；
        2. 双表索引：左连接加在右表的索引，右连接加在左表的索引；
        3. Left Join 条件用于确定如何从右表搜索行，左边数据一定有，所以右边数据一定要建索引。

      3. 总结：

        1. 尽可能减少Join语句的NestedLoop的循环总次数，永远用小结果集驱动大的结果集；
        2. 优先优化NestedLoop的内层循环；
        3. 保证Join语句中被驱动表上Join条件字段已经被索引；
    6. 索引失效

      1. 最好全值匹配：当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到

      2. 最左前缀法则：如果索引了多列，查询从索引的最左前列开始，且不能跳过索引中的列；

      3. 不在索引列上做任何操作（计算，函数，类型转换），会导致索引时校而转向全表扫描；

      4. 存储引擎不能使用索引中范围条件右边的列，即范围条件之后的索引全失效；

      5. 存储引擎不能使用索引中范围条件右边的列，即范围之后全失效；

      6. MySQL在使用不等于的时候无法使用索引会导致全表扫描；is null，is not null 也无法使用索引；

      7. like 以通配符开头（’%aa‘）索引会失效，变成全表扫描；优化：创建复合索引，有条件的话变成覆盖索引

      8. 字符串不加单引号，索引失效；

      9. 少用 or，用它来连接时候会索引失效

    7. Order By 排序

      1. MySQL支持两种排序，index和fileSort，index效率高，它指MySQL扫描索引本身完成排序。
        Order By满足 两种情况使用index：

      2. Order By 语句使用索引最左前列

      3. 使用where子句与Order By子句条件组合满足索引最左前列

      4. 如果不在索引列上，fileSort有两种算法，4.1版本之前双路排序，进行两次IO；之后单路排序，进行一次IO；

      5. Order By时不要select *，只查询所需要的字段；当两种算法的数据超出sort_buffer的容量会创建tmp文件进行合并运算，导致多次IO，所以需要尝试提高sort_buffer_size 和max_length_for_sort_ size。

      6. 为排序使用索引，MySQL能为排序与查询使用相同的索引

Mysql Explain详解

EXPLAIN列的解释
  1. table：显示这一行的数据是关于哪张表的
  2. type：这是重要的列，显示连接使用了何种类型。
    1）从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL
    2）type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    一般来说，得保证查询至少达到range级别，最好能达到ref。
    3）system:表仅有一行(=系统表)。这是const联接类型的一个特例。
    4）const:表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
    5）eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
    6）ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
    7）range:只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
    8）index:虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的
    9）ALL:将遍历全表以找到匹配的行
  3. id:
    1）id相同，执行顺序由上至下
    2）id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
  4. select_type:
    1）SIMPLE 简单的select查询，查询中不包含子查询或者UNION
    2）PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
    3）SUBQUERY 在SELECT或WHERE列表中包含了子查询
    4）DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中(在from里面的)
    5）UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
    6）UNION RESULT 从UNION表获取结果的SELECT
  5. possible_keys和keys
    1）possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。(在where字句中使用)
    2）key :实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效)
    3）查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中
  6. key_len:
    1）表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
    2）varchr(10)变长字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)varchr(10)变长字段且不允许NULL = 10 *( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)
  7. ref:显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。
  8. rows:根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好
  9. Extra:
    1）using filesort(九死一生): 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”
    2）using temporary(十死无生):使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
    3）using index(发财了):表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
    4）using where:表明使用了where过滤
    5）using join buffer:表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
    6）impossible where:where子句的值总是false，不能用来获取任何元组
    7）select tables optimized away:在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
    8）distinct:优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

​

作用
  1. 表的读取顺序；
  2. 数据读取操作的操作类型；
  3. 哪些索引可以使用；
  4. 那些索引被实际使用；
  5. 表之间的引用；
  6. 每张表有多少行被优化器查询；

MYSQL查询截取分析

​ 慢查询日志

  1. 响应时间超过long_query_tine的SQL，被记录到慢查询日志中。
  2. SHOW VARIABLES LIKE ‘%slow_query_log%’ ; 查看是否开启，默认没开启
  3. set global slow_quary_log = 1; 开启，仅本数据库有效，重启MySQL之后失效。
  4. show variables like ‘%long_query_time%’; 查看当前多少秒算慢
  5. set global long_query_time = 3; 设置慢的阙值时间
  6. show global status like ‘%Slow_queries%’; 查看当前数据库有多少条慢SQL
  7. show variables like ‘slow_query_log_file’;用于指定慢查询日志的存放路径，缺省情况是host_name-slow.log文件
  8. linux: mysql配置文件 my.cnf; windows: mysql配置文件my.ini
  9. mysqldumpslow工具: mysqldumpslow --help

Show Profile

  1. 是MySQL提供可以用来分析当前会话中语句执行的资源情况，可以用于SQL的调优的测量。默认关闭，并保存最近15次结果；

  2. show variables like “%pro%”; 显示信息 help profile; 获取profile的帮助

  3. set profiling = on ; 开启

  4. show profiles; 查看执行过的sql

  5. SHOW PROFILE cpu,block io FOR QUERY 87; 查看这个执行sql 的生命周期相关信息。

  6. mysql可以通过变量tmp_table_size和max_heap_table_size来控制内存表大小上限，如果超过上限会将数据写到磁盘上，从而会有物理磁盘的读写操作，导致影响性能。

  7. tmp_table_size规定了内部内存临时表的最大值,每个线程都要分配。max_heap_table_size 这个变量定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。

  8. converting HEAP to MyISAM （语句写的不好,取数据太多） 查询结果太大，内存不够用了往磁盘上搬

    每次取出的结果或者中间结果过大。当然也可以能是内部内存临时表设置的过小，可以将tmp_table_size和max_heap_table_size 设置大一点。

  9. Creating tmp table （(如group时储存中间结果,说明索引建的不好）创建临时表，拷贝数据到临时表，用完再删除；

    1）group by的列和order by的列不同时。
    2）distinct和order by一起使用
    3）使用了SQL_SMALL_RESULT
    4）group by的列没有索引
    5）多表联查时order by ,group by 包含的列不是第一张表的列.

  10. Copying to tmp table on disk（索引不好,表字段选的不好） 把内存中临时表复制到磁盘，危险！

  11. locked。如果在show profile诊断结果中出现了以上4条结果中的任何一条，则sql语句需要优化。

​ 全局查询日志

  1. 不可以在生产环境中开启这个功能。
  2. set global general_log = 1; set global log_output = ‘TABLE’;
  3. 此后，你所有编写的sql语句都会记录到mysql库中的general_log表；

MySQL锁机制

概述：
  1. MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。
  2. 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
  3. 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
  4. 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般
MYISAM表锁
  1. 表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。
  2. 表共享读锁：MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
  3. 表独占写锁： MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；
  4. 加锁：lock table table_name read; lock table table_name write; 释放锁：unlock tables;
  5. 查询表级锁争用情况：show status like ‘table%’;
InnoDB锁
  1. InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。
  2. 并发事务带来的问题
    1. 更新丢失（Lost Update）：两个或多个事务选择同一行进行修改，然后都进行提交，最后的更新会覆盖其他的更新
    2. 脏读（Dirty Reads）：一个事务a正在对一条记录进行修改，另一个事务b在a没有执行完之前执行读操作
    3. 不可重复读（Non-Repeatable Reads）：事务a读取数据后，事务b更新了该数据，当事务a再次读取该数据时，就会发现数据已经发生了改变
    4. 幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据
  3. 事务隔离级别
    1. 四个隔离级别：
      1. READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
      2. READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
      3. REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
      4. SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
    2. 更新丢失”通常应该是完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
    3. “脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决
    4. 数据库实现事务隔离的方式，基本可以分为以下两种。
      1. 一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
      2. 另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（ＭultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。
      3. 在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。
  4. 两种类型锁
    1. **共享锁（s）：又称读锁。**允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。多个事务只能读数据不能改数据。
    2. **排他锁（Ｘ）：又称写锁。**允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。
    3. mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。
    4. 为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
      1. 意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
      2. 意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。
    5. InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
    6. 查看是否自动提交：show variables like ‘autocommit’;设置属性：set session autocommit=0;
    7. 间隙锁：当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的 索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 （Next-Key锁）。

MySQL主从复制

  1. slave会从master读取binlog来进行数据同步。mysql主从是异步复制过程。
    MySQL复制过程分为三步：

    1. master开启bin-log功能，日志文件用于记录数据库的读写增删。
    2. 需要开启3个线程，master IO线程，slave开启 IO线程 SQL线程。
    3. Slave 通过IO线程连接master，并且请求某个bin-log，position之后的内容。
    4. MASTER服务器收到slave IO线程发来的日志请求信息，io线程去将bin-log内容，position返回给slave IO线程。
    5. slave服务器收到bin-log日志内容，将bin-log日志内容写入relay-log中继日志，创建一个master.info的文件，该文件记录了master ip 用户名 密码 master bin-log名称，bin-log position。
      slave端开启SQL线程，实时监控relay-log日志内容是否有更新，解析文件中的SQL语句，在slave数据库中去执行。

  2. 概念：mysql的主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库，主数据库一般是实时的业务数据操作，从数据库常用的读取为主。

  3. bin-log日志内容写入relay-log中继日志，创建一个master.info的文件，该文件记录了master ip 用户名 密码 master bin-log名称，bin-log position。
    slave端开启SQL线程，实时监控relay-log日志内容是否有更新，解析文件中的SQL语句，在slave数据库中去执行。

  4. 概念：mysql的主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库，主数据库一般是实时的业务数据操作，从数据库常用的读取为主。

参考文档

相关推荐