MySql

存储引擎

• Perconna Server XtraDB 基于mysql innodb , 阿里基于percona原型加以修改

• MariaDB

• MyISAM 和 InnoDB是针对数据库表的;

SQL性能

可能原因

• 关联太多，子查询啊，各种join (表设计有问题)
• 索引失效
• 服务器配置参数

优化步骤

1. 慢查询的开启 记录

   • 慢查询默认是关闭的（因为对性能还是有一点点影响）
   • 所以’slow_query_log%‘ ，long_query_time 配置一般是临时的，不会配置成永久
   • 使用日志分析工具 mysqldumpslow 命令查看

2. explain + 慢SQL分析

3. Show Profile 查询SQL在Mysql服务器中的执行细节和生命周期情况

   • 默认是关闭的 set profiling = on

   • mysql>show profiles ;

     • show profiles 显示的信息：Query_ID ,Query （记录执行的sql ) ,Duration (执行时长)

     • mysql > show profile cpu ,block io , memory …… for query ‘id‘ (分解步骤详情，完整生命周期分析)

     • show profile for query 结果中 ： 危险 status

       • convert HEAP to MyISAM (查询结果太大，内在不够用，往硬盘上转换了)

       • (create tmp table ,copy to tmp table ,remove tmp table ) 创建临时表

       • copy to tmp table on disk 临时表内存到硬盘

       • locked

   • 全局日志 （general_log )

     select * from mysql.general_log ; 收集sql日志 ，永远不要在生产环境开启这个功能

4. MySql数据库服务器的参数调优

SQL解析

• 从From开始
• 7种join连接

索引数据结构

存储形式&查找

• 以页为单位存储在磁盘上的 ，每页16k

• 表空间->段->n区（区是不连续的）->每个区下面有64个页 64*16 = 1M

• 页之间是双向链表 ，查询时根据key从根节点开始加载页到内存中去

• 页下面还有槽，再根据二分查询找到相应的数据

• 删除时 删除标记位

Why B+树

可能 1. 二叉树 2. 红黑树(平衡树) 3. hash 4 .B树 5. B+树

• 单纯的二叉树不是平衡的，可能两边高度相差很多

• 红黑树在数据量大的时候，树的高度也会越来越大，查询到叶子节点上的数据也就更加慢

  • 平衡树的深度更深，I/O操作多。维护平衡二叉树需要左旋、右旋保持平衡，代价很大。

• hash查询是快 时间复杂度为O(1)，但是不适应范围查询

• B树是叶子节点的高度一致的 ，节点中的数据索引从左到右排列 ，不只是二叉的

  • B代表平衡，而非binary

  • B树叶子和非叶子都存数据，导致非叶子节点指针变少，树的高度增加。I/O操作变多，性能变低。

• MySql的存储数据结构为B+树 （B树的变种）

  • 树的高度 （h = 3 ）

    1）假设如下：
    数据记录大小1KB -> 叶子节点(页)可以存 16/1 = 16条数据
    关键字和指针bigint 8B + 页指针6B -> 非叶子节点可以存 16384/14 = 1170个对象(关键字-页指针)
    2）高度为2和3的B+树
    高度为2的B+树：1170 * 16 = 18720，约存2w条数据记录。
    高度为3的B+树：1170 * 1170 * 16 = 21902400，约存2千万条数据记录。
    所以：InnoDB中B+树的高度一般为1~3层。mysql查找一页时代表依次IO，通过主键索引只需要1~3次IO。
    3）InnoDB表空间ibd文件中，约定page_no为3的，代表主索引的root page.

    作者：沐兮_d64c
    链接：https://www.jianshu.com/p/544e97672deb

  • B+树把data节点都移到叶子节点上了，非叶子节点上只存储索引 （可以放更多的索引）

  • 叶子节点之前添加了指针（双向），增加了区间访问性能

  • mysql的innoDB默认的一个索引大节点的数据大小为16k show global status like ‘Innodb_page_size‘

    • InnoDB最小存储单位是页。16k，叶子节点和非叶子节点最小单位都是页。B+树中叶子节点存放数据(叶子节点间指针相连，适用于局部性原理)，非叶子节点存放关键字+指针。

    • 16KB的节点大小 可以存多少个索引 ？

      InnoDB中页指针6B，主键bigint占用8B

      索引字段类型（主键）一般为bigint (8个字节）

      再加上相邻两个元素之间保存的是下一个节点的指针（6个字节）

      所以这个大节点能存储的 索引个数 = 16*1024 B / (8+6) = 1170 ;

    • 假如一个data数据大小为1k , 可以存储多少索引元素

      1170*1170 * 16 = 21902400 （2kw）

  • 访问时先加载根节点到内存中去比对 ，根节点一般是常驻内存的

B树，B+树与B*树的优缺点比较

• B树是一种平衡的多路查找(又称排序)树，在文件系统中有所应用。主要用作文件的索引。其中的B就表示平衡(Balance)

• B+树有一个最大的好处，方便扫库，B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了。

• B+树支持range-query(区间查询)非常方便，而B树不支持。这是数据库选用B+树的最主要原因。

• 比如要查 5-10之间的，B+树一把到5这个标记，再一把到10，然后串起来就行了，B树就非常麻烦。B树的好处，就是成功查询特别有利，因为树的高度总体要比B+树矮。不成功的情况下，B树也比B+树稍稍占一点点便宜。

• B树的优势是当你要查找的值恰好处在一个非叶子节点时，查找到该节点就会成功并结束查询，而B+树由于非叶节点只是索引部分，这些节点中只含有其子树中的最大(或最小)关键字，当非终端节点上的关键字等于给点值时，查找并不终止，而是继续向下直到叶子节点。因此在B+树中，无论查找成功与否，都是走了一条从根到叶子节点的路径。

  有很多基于频率的搜索是选用B树，越频繁query的结点越往根上走，前提是需要对query做统计，而且要对key做一些变化。
  另外B树也好B+树也好，根或者上面几层因为被反复query，所以这几块基本都在内存中，不会出现读磁盘IO，一般已启动的时候，就会主动换入内存。 mysql底层存储是用B+树实现的，因为内存中B+树是没有优势的，但是一到磁盘，B+树的威力就出来了。

• B*树 是B+树的变体，在B+树的非根和非叶子结点再增加指向兄弟的指针；B树定义了非叶子结点关键字个数至少为(2/3)*M，即块的最低使用率为2/3（代替B+树的1/2）；

  • B+树的分裂：当一个结点满时，分配一个新的结点，并将原结点中1/2的数据复制到新结点，最后在父结点中增加新结点的指针；B+树的分裂只影响原结点和父结点，而不会影响兄弟结点，所以它不需要指向兄弟的指针；
    B*树的分裂：当一个结点满时，如果它的下一个兄弟结点未满，那么将一部分数据移到兄弟结点中，再在原结点插入关键字，最后修改父结点中兄弟结点的关键字（因为兄弟结点的关键字范围改变了）；如果兄弟也满了，则在原结点与兄弟结点之间增加新结点，并各复制1/3的数据到新结点，最后在父结点增加新结点的指针；
  • 所以，B*树分配新结点的概率比B+树要低，空间使用率更高；

MyISAM & InnoDB

• MyISAM

  • 表锁

  • 主键索引是非聚集索引,聚集型索引并不是一种单独的索引类型，而是一种存储方式，InnoDB 聚集型索引实际上是在同一结构中保存了 B+tree 索引和数据行。当有聚簇索引时，它的索引实际放在叶子页中。

  • . frm , .MYD, .MYI

  • B+树的结构存储的 ，叶子节点中 key为索引字段， value为硬盘文件地址 （存储在.MYI文件中）

  • 再根据地址就从 .MYD文件中找到该记录 （这样就是跨文件查询）

  • MYISAM 不支持事务，也是它查询快的一个原因！

    • 不要维护额外的东西
    • myisam索引直接定位到offset(地址)，innodb定位到叶子节点块再到行

• innoDB

  • 主键索引是聚集索引

• 支持行锁

  • 表名.ibd文件

• 数据和索引放在一块

  • 要维护mvcc
  • 叶子节点中 key为索引字段， value 为其它所有字段 （叶子节点包含了完整的数据记录）

联合索引存储结构

• 辅助索引

索引优化

哪些情况不需要索引

• 记录数太少

• 经常增删改的字段

• 性别，籍贯 等差异率不高的情况 （选择性）

MySql Query Optimizer

• 自动优化

常见分析

• top ,free,vmstat ,iostat

基本原则

• 小表驱动大表的原则
• 索引用于查找和排序

Explain

id

• 表示加载顺序
• id相同时 加载顺序就和记录顺序一致
• id不同 （一般在子查询时，id越大越优先）
• NULL （一般表示union最终操作)

select_type

• simple
  • 简单查询，不包含子查询，union
• primary
  • 相对于子查询的最外层查询
  • 最后加载的那个
• subquery
• DERIVED 衍生
  • 子查询（临时表）
• union,union result

table

type

• 访问类型（8种值）

• system > const > eq_ref > ref > fulltext > range > index > ALL

  • const 常量

    • primary key | unique key

  • eq_ref

    唯一性索引扫描

  • ref

    非唯一性扫描

  • range

    in, > ,< , between

  • index

    • 全索引文件扫描（遍历索引树，索引文件通常比数据文件小）
    • index是指从索引文件中读取，而不是从硬盘数据文件中读取

  • ALL

    全表扫描

possiable key

• 可能用到的索引，但不一定被查询使用

key

• 结合possiable key ，判断索引是否失效
• 实际使用到的索引

key_len

• 索引中使用的字节数
• 不损失精度的情况下，长度越短越好

ref

• 显示哪些索引列被引用到了，最好是const
• 表之间的引用

rows

• 估算当前表有多少行被优化器查询到

• 根据表统计信息和索引使用情况，大致估算出找到目标记录所需查询的行数

• 越少越好

Extra

• 额外信息
• using filesort , using temporary,using index ,using where ,using joinbuffer ……
  • using index : 覆盖索引

多表联连

• 左连接索引建立在右表
  • select a.id,b.id, b.name ,a.col from a left join b on a.name = b.name ;
  • 索引建立在b表的name 上
  • 左外连接表明左表的数据多 （小表驱动大表的原则）
• 小表驱动大表 （小结果集驱动大结果集）
• 索引建立在需要经常查询的字段中
• 优先优化内部循环
• add joinbuffer

索引失效

最佳左前缀法则

• 可以想象成 【火车头+车厢】或者 1楼2楼3楼 （空中楼阁）
• 火车头（1楼）不能没有 ，中间车厢不能脱轨

索引列上任何操作

• 函数
• 自动（隐式类型转换）或手动类型转换

范围之后全失效

• ‘>‘操作后的列索引全失效 ，自己本身由ref 变成 range

is [ not ] null

• 所以尽量不要在字段中留有null值

like ‘‘%abc‘

• 用覆盖索引解决 （select 的字段包含在所建立的索引中）

• select * 时就用不到覆盖索引了

<>或者!=

字符串不加‘’

• 类型转换问题

少用or连接

Order by ,group by

• 也会用到索引，但是它不是用来查找，而是用来排序

• 不会体现在ref信息中

• 像查询一样如果出现中断会发生 using filesort ,using temporary

  • select * from test where c1 = ‘v1‘ and c2 = ‘v2‘ order by c4 ;
  • select * from test where c1=‘v1‘ and c4=‘v4‘ order by c2,c3;
    • 用到c1的索引，order by 没有中断 不会产生using filesort (内排序)
    • 如果是order by c3,c2 ,就会产生using filesort
    • select * from test where c1=‘v1‘ and c2 = ‘v2‘ and c4=‘v4‘ order by c3,c2; 却不用产生内排序
      • c2是常量了，所以不涉及到排序了

• group by

  • where 优先于having (能写在where条件中的就不要写到having中)

  • 分组之前必排序，所以group by 索引原则也一致

  • 可能会产生 临时表

    1. 如果GROUP BY 的列没有索引,产生临时表.

    2. 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表.

    3. 如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表.

    4. 如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表.

    5. 如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表.

    6. 如果DISTINCT 和 ORDER BY的列没有索引,产生临时表.

    7. 用了 SQL_SMALL_RESULT, mysql就会用内存临时表。

in exists

• IN()只执行一次，它先查出B表中的所有id字段并缓存起来 ，再遍历和检查A.id和缓存的id

• Hash join算法

  1．确认小表是驱动表

  2．确认涉及到的表和连接键分析过了。

  3．如果在连接键上数据不均匀的话，建议做柱状图。

  4．如果可以，调大hash_area_size的大小或pga_aggregate_target的值。

  5．Hash Join适合于小表与大表连接、返回大型结果集的连接。

• exists()会执行A.length次 (A为外表），它并不缓存exists()结果集，因为exists()结果集的内容并不重要 (true,false)

• in是在内存里遍历比较( In-Memory Hash Join),也可能On-Disk Hash Join ，而exists需要查询数据库，所以当B表数据量较大时，exists效率优于in。

• 由于exists是用loop的方式，所以，循环的次数对于exists影响最大，所以，外表要记录数少，内表就无所谓了，而in用的是hash join，所以内表如果小，整个查询的范围都会很小，如果内表很大，外表如果也很大就很慢了，这时候exists才真正的会快过in的方式。

• 如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；
  而not extsts 的子查询依然能用到表上的索引。
  所以无论那个表大，用not exists都比not in要快。

  也就是说，in和exists需要具体情况具体分析，not in和not exists就不用分析了，尽量用not exists就好了。

MySql锁机制

锁定义

数据库中的共享资源

• 除了cpu,raw ,

锁分类

表级锁（MyISAM)

• 冲突高，并发低，适合读

• 读锁

  如果session1获取表t_a的读锁 lock table t_a read;

  • 当前session可以查询该表，但是不能查询其它未锁定的表

    • 当前session查询其它未锁定的表提示Table ‘t_b‘ was not locked with LOCK TABLES

  • 当前session不能更新锁定的表

    Table ‘t_a‘ was locked with READ lock and can‘t be updated

  • 其他sessioin也可以查询该表，也可以查询和更新其它未锁定的表

  • 其他session插入更新会一直等待 （就是所谓的读阻塞）

  • 直到session1 unlock tables ,其他session的更新操作才会提交

• 加写锁

  • mysql > lock talbe t_a write ;

  • 当前session可以查询和更新锁定表

  • 当前session不能查询其它未锁定的表

  • 其他sessioin可以查询和更新其它未锁定的表

  • 其它session不可以读和写session1写锁定的表 ，会阻塞

  • 写锁会把其它session的读和写都阻塞

行级锁（InnoDB)

• 粒度小，冲突低，并发高 ，偏写
• 事务 （锁+并发）
  • acid
  • 事务隔离级别
• 更新同一条记录->写写阻塞，commit之后恢复正常
• select * from t_a where id = ‘1‘ for update [time] 手工锁定一行

锁升级

• 索引失效导致行级锁升级为表锁

间隙锁

• 当用键值范围条件检索时，InnoDB会给符合条件的已有数据的索引项加锁; (宁可错杀，也不放过)
• 对于键值在范围内但不存在的记录称为 “间隙（GAP）”
• 危害：不存在的键值被无辜锁定

Mysql半同步复制

• mysql5.5之前一直采用的是异步复制。主库的事务执行不会管备库的同步进度，如果备库落后，主库不幸crash，那么就会导致数据丢失。

• MySQL在5.5中引入了半同步复制，主库在应答客户端提交的事务前需要保证至少一个从库接收并写到relay log中。那么半同步复制是否可以做到不丢失数据呢？

对于异步复制，主库将事务Binlog事件写入到Binlog文件中，此时主库只会通知一下Dump线程发送这些新的Binlog，然后主库就会继续处理提交操作，而此时不会保证这些Binlog传到任何一个从库节点上。

对于全同步复制，当主库提交事务之后，所有的从库节点必须收到，APPLY并且提交这些事务，然后主库线程才能继续做后续操作。这里面有一个很明显的缺点就是，主库完成一个事务的时间被拉长，性能降低。

对于半同步复制，是介于全同步复制和异步复制之间的一种，主库只需要等待至少一个从库节点收到并且Flush Binlog到Relay Log文件即可，主库不需要等待所有从库给主库反馈。同时，这里只是一个收到的反馈，而不是已经完全执行并且提交的反馈，这样就节省了很多时间。

MySql常见问题

日期时间类型

• 字符串存储日期

  • 性能问题
  • 比较，处理，范围都是麻烦事儿

• datatime

  • 未存储服务器时区信息(不知道写入时对应的时区信息），只存储了本地时间
  • 时间范围（1000-00-00 00:00:00 到 9999-12-31 23:59:59）

• timestamp

  • 时间范围（1970-01-01 00:00:01 到 2038-01-19 03:14:07）

  • 1 月 (30.44 天) 2629743 s

    1年 (365.24 天) 31556736 s

    31556736 x 68(年) + 18(天）x86400 = 2147413248

  • MySQL的timestamp类型，存储的是一个整型int数据，由于int是有大小范围的，最多存大约21亿（2147483648）的数据，所以timestamp存储的数据被限制在了1970~2038年之内。

• unix stamp (绝对时间戳)

  • 1970-01-01 00:00:01 到现在的秒数 bigint表示 unsigned

  • 前端可以自定义格式

  • mysql : unix_timestamp

Mysql双活体系建设

伪双活

同城双活

异地双活

mysql学习总结