MYSQL一些摘录

BaichuanWu
192 阅读10分钟

事务隔离

  • 行级锁只在存储引擎层实现
  • 锁
    • 乐观锁：大多是基于数据版本（ Version ）记录机制实现。
    • 悲观锁：一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。
  • 两段锁
    • 加锁阶段：在该阶段可以进行加锁操作。在对任何数据进行读操作之前要申请并获得S锁（共享锁，其它事务可以继续加共享锁，但不能加排它锁），在进行写操作之前要申请并获得X锁（排它锁，其它事务不能再获得任何锁）。加锁不成功，则事务进入等待状态，直到加锁成功才继续执行
    • 解锁阶段：当事务释放了一个封锁以后，事务进入解锁阶段，在该阶段只能进行解锁操作不能再进行加锁操作
  • 事务的隔离级别
    • read uncommited: 问题——脏读:一个事务会读进还没有被另一个事务提交的数据，所以你会看到一些最后被另一个事务回滚掉的数据
    • read commited:问题——不可重复读:一个事务读进一条记录，另一个事务更改了这条记录并提交完毕，这时候第一个事务再次读这条记录时，它已经改变了(解决：如果只有在修改事务完全提交之后才可以读取数据，可以避免该问题)
    • repeatable read:问题——幻读:一个事务用Where子句来检索一个表的数据，另一个事务插入一条新的记录，并且符合Where条件，这样，第一个事务用同一个where条件来检索数据后，就会多出一条记录(解决:如果在操作事务完成数据处理之前，任何其他事务都不可以添加新数据，则可避免该问题)
    • serializable: 读加共享锁，写加排他锁，读写互斥。使用的悲观锁的理论。
  • 死锁:innodb的处理——将持有最少行级排他锁的事务回滚
  • innodb下MVCC只在read commited 和 repeatable read 下适用:

innodb 下 MVCC（参考美团技术blog）

  • repeatable read级别下：
    • SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。（快照读，解决了快照读中出现的幻读）
    • INSERT时，保存当前事务版本号为行的创建版本号
    • DELETE时，保存当前事务版本号为行的删除版本号
    • UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行
  • 快照读：使得select 不用加锁
    • select ……...
  • 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁
    • Next-Key锁：行锁和GAP（间隙锁）的合并
    • 锁住索引和索引两边的区间，（如果有insert会加入到索引两边，因为LOCK存在会阻塞?)

优化

  • 索引优化
  • 查询优化
  • 库表结构优化

索引

  • innodb主键聚集索引，其他索引叶子节点保存主键id;MyISAM索引和数据分离，叶子节点保存数据地址

  • 索引的评定原则

    • 索引将相关记录放在一起
    • 索引中的数据顺序和查找中的排序顺序一致
    • 索引中的列包含了查找的全部列

  • 小：全表；中大：索引；大：分区

  • 优化索引合并(explain 结构extra 中有using union):

    • 避免 select …… and | or ......
    • 因为合并等操作有时性能可能还不如全表扫描
  • MySQL innodb_autoinc_lock_mode间隙锁竞争

索引使用like "xx%",当没有覆盖索引时，type为range, extra 为Using index condition(mysql 5.6及以上)

  • 覆盖索引:一个索引包含所有需要查询的字段(B-Tree索引支持)(避免了大量单行访问会快很多)
    • explain 覆盖索引时 Extra 为 using index
  • type, Extra 汇总
  • where A=1 order by id 当索引为(A)时可以通过索引做排序，因为(A)=(A,id), 为(A,B)时则不可

lock

  • select for update 排他
  • lock in share mode 共享
  • 新版本 where in 的优化？
  • mysql优化器发现需要取出的数据占总数据的20%以上,就会走主键索引而不走辅助索引?
  • 延迟关联，解决分页偏移量过大

查询优化

where 语句时 extra 的效率从高到底

  • 为空，在索引中使用where 过滤不匹配，存储引擎层完成
  • using index 覆盖索引,在索引中过滤不需要的记录，在服务器层完成，无需回表查询
  • using index condition 与以上相比，需要回表查询
  • using where 需要从数据表中读取过滤
  • mysql 半双工
  • mysql_use_result=1 在查询时不会将结果存缓存，一直到传到客户端完毕，服务器资源会被这个查询占用
  • 默认会将结果缓存起来然后传给客户端，资源可以提前释放
  • join ... using 等值传播

对于非索引字段 in 的效率比 or高

  • mysql的优化一个是O(logn), 一个是O(n)

重构查询的方式

  • 切分查询
    • 大的查询可能会锁住更多数据，沾满整个事务
  • 分解关联查询(join...)
    • 尽可能利用缓存
    • 减少锁竞争
    • 减少冗余数据查询（数据库中关联查询可能会重复）
    • 比随机关联效率高
    • 等于是应用层哈希
    • 易于分库，扩展
STOP SLAVE;
SET GTID_NEXT="94b06c13-1f09-11e5-97ea-000c29c8caec:11";
BEGIN; COMMIT;
SET GTID_NEXT="AUTOMATIC";
START SLAVE;

MYSQL关联查询优化

  • 总是从一个表开始循环嵌套，回溯完成所有表关联（全外连接无法）
  • STRAIGHT_JOIN 按照sql 中 JOIN的顺序关联，mysql会优化JOIN的顺序，先关联查询后行数最少的表，减少嵌套和回溯次数
  • optimizer_search_depth限制了MYSQL 优化关联的表数，超过时MYSQL会采取贪婪搜索模式？

MYSQL排序优化

  • 当不能通过索引生成排序结果时，MYSQL使用文件排序(内存或磁盘中)
    • 排序数量小于排序缓冲区时，会在内存中直接快排
    • 否则会将数据分块，每个块快排后把结果存在磁盘，最后将各个排好序的块合并
  • 两次传输排序；单次传输排序
  • 关联查询如果排序字段在第一张表， MYSQL在关联处理第一张表就排序(using filesort)；除此之外，MYSQL会将关联结果存到一张临时表(using filesort;using temporary)
  • 查询执行引擎
  • MYSQL将结果返回客户端是一个增量，逐步返回的过程
  • group by 松散索引和紧凑索引扫描

优化关联查询

  • 关联查询时没有特殊情况只需要在第二张表的关联字段建立索引
  • group by 和 order by 的表达式只涉及一个表中的列，MYSQL才能用索引优化
  • 子查询创建的临时表有没有索引？

延迟关联优化limit,避免丢弃大量不必要查询

select film.film_id, film.description from sakila.film join (select film_id from sakila.film order by title limit 50,5) as lim using (film_id);

分区表

  • 抽象出一层分区表
  • 访问分区表总会打开并锁住所有底层表
  • 可以理解分区是索引的最初形态。以代价非常小的方式将数据定位到一块区域

视图（用例：掩盖一些私有数据）

  • 合并算法(尽可能)
  • 临时表算法（EXPLIAN:DERIVED）（无法在原表和视图中一一映射时）
  • 一一对应时可修改视图，从而修改原表

外键（innodb）

外键的成本

  • 修改时多一次在另一张表的查询虽然强制了索引(会加锁确保创建完成后外键对应数据存在)

触发器

  • innodb下的触发器是在操作同一个事务中的有原子性(myIsam无原子性)

游标

通过临时表实现的 临时表不支持BLOG和TEXT类型如果游标结果返回这些列需要创建临时磁盘

字符

如果索引一个utf-8编码的列，mysql会默认是3个字节，索引的长度会变成原来的 1/3

分布式(XA)事务

内部XA事务，跨存储引擎 外部XA事务

缓存命中

  • query_cache_type:ON, OFF, DEMAND
  • query_cache_size: 1024的倍数(缓存总内存空间)
  • query_cache_limit: 最大查询结果
  • query_cache_min_res_unit: 分配内存块最小单位(影响内存碎片)

MYSQL配置

如果MYSQL运行时修改了配置变量全局值，则修改在当前会话和其他已存在会话不起作用，因为会话变量是连接时初始的

a、设置方法 要想设置一个GLOBAL变量的值，使用下面的语法： mysql> SET GLOBAL sort_buffer_size=value; mysql> SET @@global.sort_buffer_size=value;

要想设置一个SESSION变量的值，使用下面的语法：
   mysql> SET SESSION sort_buffer_size=value;
   mysql> SET @@session.sort_buffer_size=value;
   mysql> SET sort_buffer_size=value;
   LOCAL是SESSION的同义词。
如果设置变量时不指定GLOBAL、SESSION或者LOCAL，默认使用SESSION。

b、检索设置
要想检索一个GLOBAL变量的值，使用下面的语法： mysql> SELECT @@global.sort_buffer_size; mysql> SHOW GLOBAL VARIABLES like 'sort_buffer_size';

要想检索一个SESSION变量的值，使用下面的语法：
   mysql> SELECT @@sort_buffer_size;
   mysql> SELECT @@session.sort_buffer_size;
   mysql> SHOW SESSION VARIABLES like 'sort_buffer_size';
这里，LOCAL也是SESSION的同义词。

c、其他注意事项
当你用SELECT @@var_name搜索一个变量时(也就是说，不指定global.、session.或者local.)， MySQL返回SESSION值（如果存在），否则返回GLOBAL值。 对于SHOW VARIABLES，如果不指定GLOBAL、SESSION或者LOCAL，MySQL返回SESSION值。

mysql配置内存:

  • 略
  • innodb缓冲池：
    • 缓存索引，行数据，锁，其他内部数据结构等
    • 延迟写入合并多个写入操作
  • myiam键缓存：只缓存索引
  • 线程缓存:
    • thread_cache_size 指定了MYSQL可以保持在缓存中的线程数
    • 有新的链接创建时，在缓存中删除这个线程然后把这个线程分配给链接 表缓存

innodb I/O

  • innodb通过日志将随机IO转换为顺序IO,一旦日志写到磁盘，事务就持久化了(数据文件的不同步具体怎么解决（只持久化到了日志文件没有到数据文件）？双写缓存？)
  • 日志写到底部时会重新跳到开头继续写，但是不会覆盖还没应用到数据文件的日志记录
  • 一个后台线程不断地刷新变更到数据文件，可以批量，使得数据写入更加顺序
  • 日志文件太小，导致写的等待刷新到数据文件，太大的话，崩溃时恢复工作量会大
  • innodb_flush_log_at_trx_commit控制日志缓冲刷新的频繁程度
    • 0:每秒钟刷新一次，日志提交时不做任何操作
    • 1(默认，最安全的):每次事务提交都需要刷新到持久化存储（除非磁盘或者操作系统是伪刷新，都能保证数据不会丢失）
    • 2:类似0但是进程挂了2不会丢失数据，服务区挂了或者断电依然会丢失数据
  • innodb打开文件的方式：略
  • innodb 两个重要的配置项：innodb_buffer_pool_size;innodb_log_file_size

操作系统和硬件优化

  • 内存交换区对于进程是无法识别的，使Mysql 缓存是去意义

复制

avatar
文章
阅读
粉丝