MySQL数据库

关系型数据库

1、索引模块：

• 为啥用索引：快速查询数据。
• 索引结构：
  • 二叉树：二分查找
  • B Tree
  • B+ Tree
  • HASH结构

参考链接: https://blog.csdn.net/wl044090432/article/details/53423333?utm_medium=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control&dist_request_id=1328603.11577.16149284984438891&depth_1-utm_source=distribute.pc_relevant_t0.none-task-blog-BlogCommendFromMachineLearnPai2-1.control

1.1 二叉树索引

• 时间复杂度：O（logn）
• 缺点：
  • 1、数据增删，有可能变成（线性树）链表。O(n)
  • 2、数据增删，I/O太频繁。

1.2 B树

• 查找效率Olog(n)
• 特征：前四条限定孩子树和深度。第五条限定节点关键字和大小。
  • 根节点至少两个孩子。
  • 树种每个节点最多m个孩子。(m>2)
  • 除了根节点和叶子节点，其他每个节点至少有ceil(m/2)个孩子。
    • ceil（取上限函数,整数）ceil(3/2)=2。
  • 所有叶子结点都是同一层。
  • 第五点：下图

优势：
- 增删节点，会采取(分裂合并)策略，不会变成线性树。

1.3 B+树

• B+树比B树多4条特点：
  

1.4 Hash索引：

1.4.1 Hash索引优势

• Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。
  

1.4.2 缺点：

1.5 BitMap:

• 位图索引适合只有几个固定值的列，如性别、婚姻状况、行政区等等，而身份证号这种类型不适合用位图索引。
• 此外，位图索引适合静态数据，而不适合索引频繁更新的列。举个例子，有这样一个字段busy，记录各个机器的繁忙与否，当机器忙碌时，busy为1，当机器不忙碌时，busy为0。
• 这个时候有人会说使用位图索引，因为busy只有两个值。好，我们使用位图索引索引busy字段！假设用户A使用update更新某个机器的busy值，比如update table set table.busy=1 where rowid=100;，但还没有commit，而用户B也使用update更新另一个机器的busy值，update table set table.busy=1 where rowid=12; 这个时候用户B怎么也更新不了，需要等待用户A commit。
• 原因：用户A更新了某个机器的busy值为1，会导致所有busy为1的机器的位图向量发生改变，因此数据库会将busy＝1的所有行锁定，只有commit之后才解锁。
  

1.6 如何指定索引数据类型：

• InnoDB引擎：
  • Hash索引自动适应，会根据表的使用情况自动为表生成哈希索引，不能人为干预。

2、分库分表sharding

2.1、为啥要分库分表

• (1)数据库单张表存放有上限。MySql 2个G或者500W条数据。
• (2)读写会变慢。特别是没命中索引，权标扫描的话，耗时更长了。
• (3)连接数问题。MYSQL的连接数是有最大限制的，由参数max_connections决定。这个参数设置没有固定的值，主要看业务场景，需要注意的是每个链接都需要占用内存，以及有昂贵的线程切换开销。
• 如果数据量过大就需要用路由因子分表。

2.2、如何分库分表

• 分库分表又分为垂直拆分和水平拆分，垂直拆分是把各个字段分拆到不同的多张表，本质并未解决数据量过大的问题。水平拆分则根据主键等信息将很多条记录，拆分多多张表上，一条记录仅存在一个分区上。
  参考链接：https://zhuanlan.zhihu.com/p/137368446
• 设置路由因子：通常选择一个不变的字段，这个字段要跟具体的数据无关。

X、常见面试题

1、慢SQL优化：

参考：https://www.cnblogs.com/zjxiang/p/9157398.html

1.1原因

• 定义：执行比较耗时的SQL。超过2秒就是慢查询。
• 原因：
  • 数据量很大，或者走全表扫描。
  • 建立了索引，SQL有问题没有走索引。
    

1.2 如何定位：

（1）设置开启：SET GLOBAL slow_query_log = 1;　　　#默认未开启，开启会影响性能，mysql重启会失效
（2）查看是否开启：SHOW VARIABLES LIKE '%slow_query_log%';
（3）设置阈值：SET GLOBAL long_query_time=3;
（4）查看阈值：SHOW 【GLOBAL】 VARIABLES LIKE 'long_query_time%';　　#重连或新开一个会话才能看到修改值
（5）通过修改配置文件my.cnf永久生效，在[mysqld]下配置：
　　[mysqld]
　　slow_query_log = 1;　　#开启
　　slow_query_log_file=/var/lib/mysql/atguigu-slow.log　　　#慢日志地址，缺省文件名host_name-slow.log
　　long_query_time=3;　　  #运行时间超过该值的SQL会被记录，默认值>10
　　log_output=FILE

• 获取慢SQL信息

查看慢查询日志记录数：SHOW GLOBAL STATUS LIKE '%Slow_queries%';

1.3 分析原因:

1.3.1 explain执行计划

通过explain分析慢SQL。

1.3.2 Show Profile分析

• Show Profile也是分析慢SQL的一种手段，但它能获得比explain更详细的信息，能分析当前会话中语句执行的资源消耗情况，能获得这条SQL在整个生命周期的耗时，相当于执行时间的清单，也很重要。
• 1、默认关闭。开启后，会在后台保存最近15次的运行结果，然后通过Show Profile命令查看结果。

开启：set profiling = on;
查看：SHOW VARIABLES LIKE 'profiling%';

2、通过Show Profile能查看SQL的耗时

3、通过Query_ID可以得到具体SQL从连接 - 服务 - 引擎 - 存储四层结构完整生命周期的耗时。