mysql等数据库高级(new)

Mysql高级

三范式

1NF:原子性，字段不可再分(不能是集合，数组等);

2NF:有主键，唯一性 一个表只有一个事情，说明一个事物，比如库存表里面包含物资资料所有信息，明显它们是属于两个事务

3NF:非主键字段不能相互依赖(关联);

  • 在概念数据模型设计时遵守第三范式，实际设计依据实际业务是否考虑降低范式，降低范式就是增加字段，减少了查询时的关联，提高查询效率

MyISAM与InnoDB 的区别（9个不同点）

区别：

1. InnoDB支持事务，MyISAM不支持(InnoDB每一条SQL语言都默认封装成事务，开动开启，提交)；

2. InnoDB支持外键，而MyISAM不支持

3. InnoDB是聚集索引，使用B+Tree作为索引结构，MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针

img
img

4. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

在InnoDB引擎下，行锁是基于索引项加锁来实现的。当在WHERE筛选条件时没有使用到索引项，那么一定是表级锁。筛选条件时使用到主键或者唯一索引，一定是行级锁。
而当筛选条件时使用到普通索引时，不一定是行级锁，得看看表内是否有大量数据且修改表内大部分数据，如果是的话，mysql会将行锁变为表锁。最后，可以用expland执行计划来查看执行效率怎么样。

t_user(uid, uname, age, sex) innodb;
uid PK
无其他索引
update t_user set age=10 where uid=1;           命中索引，行锁。
update t_user set age=10 where uid != 1;       未命中索引，且会修改大部分数据的时候，表锁。
update t_user set age=10 where name='chackca'; 无索引，表锁。

5、InnoDB表必须有主键（用户没有指定的话会自己找或生产一个主键），而Myisam可以没有

6、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

  • Innodb：frm是表定义文件，ibd是数据文件
  • Myisam：frm是表定义文件，myd是数据文件，myi是索引文件*

如何选择：

  • 是否要支持事务，如果要请选择innodb

  • 如果表中绝大多数都只读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。


扩展

1.树形结构

M阶中M由磁盘页面内存(4kb)大小决定，节点数就是M值，尽量保存一样，一次刚好拿出一个节点存储的所有的数据

  • 二叉树Bin：左子树所有节点(不为null)小于根节点，根节点小于右子树所有节点(不为null)，并且所有节点依次类推，中序遍历递增

  • 平衡二叉树：一个二叉树每个节点左右两个子树的高度差的绝对值不超过1

  • 平衡二叉树查询比较快，因为比红黑树更加平衡(对称)，平衡性更好，每个节点存储信息会更多，但是在增删改的时候，为了平衡，调整频次会更高，影响性能

  • 红黑树插入和删除比较快，调整次数比较低

  • 红黑树，节点只能是黑色和红色，两个红色节点不能连接在一起，根节点是黑色

  • 从两边任何节点到根节点黑子数必须相等

2.innodb底层

选择b+树结构，而不是选择hash结构，作为索引原因，hash冲突，hash对(col>10)范围查找效率低下，没有顺序性

innodb中页大小是16kb，原理近似计算机局部原理
局部性原理：cpu取一个数据的时候，会将这个数据相邻的数据都取出来存入内存中，如果下次需要的数据这个取出数据中，就不需要重新从磁盘读取了，实际是会将这一页取出来，4kb大小，即每次会取4kb数据到内存中

  • 跨页问题：假如定义varchar(65535)，即实际存储数据一行只能是65532字节，而一页为16kb,16448字节，此时就会出现跨页问题

  • 页大小16kb原因：io一次性磁盘加载一页4kb到页缓存(操作系统页大小=4kb)，根据局部性原理，操作系统会多加载3个页，也就是一次io其实加载了16kb数据


SQL分析和优化

定位低效率执行SQL

可以通过以下两种方式定位执行效率较低的 SQL 语句。

  • 慢查询日志 : 设置long_query_time，超过这个时间的sql会被记录到日志中

  • show processlist : 查看当前MySQL在进行的线程的状态、是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。

  • 实时显示正在执行sql
    • 主要state和info： 哪个用户，哪个端口，哪个db，什么操作，已经执行时间，哪句sql，执行状态state(时间很长可能性能问题或者锁表)
1） id列，用户登录mysql时，系统分配的"connection_id"，可以使用函数connection_id()查看
2） user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
3） host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
4） db列，显示这个进程目前连接的是哪个数据库
5） command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接（connect）等
6） time列，显示这个状态持续的时间，单位是秒
7） state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
8） info列，显示这个sql语句，是判断问题语句的一个重要依据

explain分析执行计划

查询SQL语句的执行计划 ：

explain  select * from tb_item where id = 1;


explain  select * from tb_item where title = '阿尔卡特 (OT-979) 冰川白 联通3G手机3';

字段 含义
id select查询的序列号，是一组数字，执行select子句或者是操作表的顺序(越大越先执行(嵌套子查询)，相同则从前往后执行)
select_type 表示 SELECT 的类型，SIMPLE（无连接和子查询）、PRIMARY（主键查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table 输出结果集的表
type 表示表的连接类型(查询性能)(null(select 1)--->system(就一行) ---> const(pk、uki一条) -----> eq_ref(uki关联1条) ------>ref(普通index) -----> range(index范围检索)----->index(遍历索引树) ------>all(全局扫描) )
possible_keys 表示查询时，可能使用的索引
key 表示实际使用的索引
key_len 索引字段的长度(最大可能长度，并非实际值，一般，越短越好，例如：联合索引完全匹配，还是部分匹配)
rows 扫描行的数量(估计查询需要扫描的行数，并非实际值)
extra 执行情况的说明和描述(排序字段尽量使用索引字段，即覆盖索引，避免回表) 
TIP : 
	
    using index ：使用覆盖索引的时候就会出现
    using where：在查找使用索引的情况下，需要回表去查询所需的数据
    using index condition：查找使用了索引，但是需要回表查询数据(查询结果集字段有没有用索引的)
    using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

3.3.2 explain 之 id

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。id 情况有三种 ：

1） id 相同表示加载表的顺序是从上到下。

explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;

2） id 不同id值越大，优先级越高，越先被
执行。

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))

3） id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。

EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id ;

3.3.3 explain 之 select_type

表示 SELECT 的类型，常见的取值，如下表所示：

select_type 含义
SIMPLE 简单的select查询，查询中不包含子查询或者UNION
PRIMARY 查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY 在SELECT 或 WHERE 列表中包含了子查询
DERIVED 在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中
UNION 若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 ： DERIVED
UNION RESULT 从UNION表获取结果的SELECT

  • 前面是primary，后面是subquery

  • DERIVED即from 后包含段
  • 表示从derived + 2(id) 查询

  • union 后部分
  • 两部分union 之和

3.3.4 explain 之 table

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

3.3.5 explain 之 type

type 显示的是访问类型，是较为重要的一个指标，可取值为：

type 含义
NULL MySQL不访问任何表，索引，直接返回结果
system 表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const 表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常亮。const于将 "主键" 或 "唯一" 索引的所有部分与常量值进行比较
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref 非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range 只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
index index 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。 select id from
all 将遍历全表以找到匹配的行 select * from

结果值从最好到最坏以此是：

NULL(select 1 from dual) >..>ref(非唯一性索引扫描)> range(age>20) > index > ALL（select * from tb）

从null到all，效率依次降低，我们需要保证查询效率至少达到 range 级别， 最好达到ref 。

3.3.6 explain 之 key

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

key ： 实际使用的索引， 如果为NULL， 则没有使用索引，多个索引，如果没有走自己想要的那个索引，要想办法。

key_len : 表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。

3.3.7 explain 之 rows

扫描行的数量(无索引扫描所有行)。

3.3.8 explain 之 extra

其他的额外的执行计划信息，在该列展示 。

extra 含义
using filesort 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取， 称为 “文件排序”, 效率低。order by 字段建索引，并且查询显示此字段即可解决=》using index
using temporary 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和 group by； 效率低，group by 之后建索引=》using index
using index 表示相应的select操作使用了覆盖索引， 避免访问表的数据行， 效率不错。

show profile分析SQL

通过profile，我们能够更清楚地了解SQL执行的过程。

首先，我们可以执行一系列的操作，如下图所示：

show databases;

use db01;

show tables;

select * from tb_item where id < 5;

select count(*) from tb_item;

执行完上述命令之后，再执行show profiles 指令， 来查看SQL语句执行的耗时：

通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：


避免索引失效 (爬楼梯)

通过key_len判断是否只走了部分索引还是完全索引

1). 全值匹配 ，对索引中所有列都指定具体值。

改情况下，索引生效，执行效率高。

explain select * from tb_seller where name='小米科技' and status='1' and address='北京市'\G;

2). 最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。

匹配最左前缀法则，走索引：

违法最左前缀法则 ， 索引失效：

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：

3). 范围查询右边的列，不能使用索引 。

根据前面的两个字段name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。

4). 不要在索引列上进行运算操作， 索引将失效。

5). 字符串不加单引号，强制转换，造成索引失效。

由于，在查询是，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

6). 尽量使用覆盖索引，避免select *

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。

如果查询列，超出索引列，也会降低性能。

TIP : 
	
    using index ：使用覆盖索引的时候就会出现

    using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以**不需要回表查询数据**

    using where：在查找使用索引的情况下，需要回表去查询所需的数据

    using index condition：查找使用了索引，但是需要回表查询数据(查询结果集字段有没有用索引的)

7). 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的,导致索引失效 ：

explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'\G;

8). 以%开头的Like模糊查询，索引失效。

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

解决方案 ：

通过覆盖索引来解决 (查询的字段都是索引字段)

9). 如果MySQL评估使用索引比全表更慢，则不使用索引。(北京市有11条，西安市才1条)

10). is NULL ， is NOT NULL 有时索引失效。

  • 数据全是not null，使用isnull 明细没有，所以索引快，走索引
  • 数据全是not null， 用isnotnull 查询所有的数据，用索引还不如全表扫描快

11). in 走索引， not in 索引失效。

12). 单列索引和复合索引。

尽量使用复合索引，而少使用单列索引 。

创建复合索引

create index idx_name_sta_address on tb_seller(name, status, address);

就相当于创建了三个索引 ： 
	name
	name + status
	name + status + address

创建单列索引

create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

数据库只会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引 。(比如address为北京不选择它索引，如果西安，它却使用status(西安市就一条))


3. 优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

5.7.1 优化思路一

先覆盖索引找到主键id，然后根据主键关联回原表查询所需要的其他列内容(全局模糊查询(%%)也可以尝试使用覆盖索引)。

5.7.2 优化思路二

该方案适用于主键自增并且无断层的表(1,2,3,5,7,8,9)，可以把Limit 查询转换成某个位置的查询 。


4. Mysql锁问题

show status like 'Table_locks%';

Table_locks_immediate ： 指的是能够立即获得表级锁的次数，每立即获取锁，值加1。

Table_locks_waited ： 指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加1，此值高说明存在着较为严重的表级锁争用情况。

5.3.1 行锁介绍

行锁特点 ：偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,实现并发度最高。

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

5.3.8 InnoDB 行锁争用情况

show  status like 'innodb_row_lock%';


Innodb_row_lock_current_waits: 当前正在等待锁定的数量

Innodb_row_lock_time: 从系统启动到现在锁定总时间长度

Innodb_row_lock_time_avg:每次等待所花平均时长

Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间

Innodb_row_lock_waits: 系统启动后到现在总共等待的次数

Innodb_row_lock_waits当等待的次数很高，而且每次等待Innodb_row_lock_time_avg的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

5.3.9 总结

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，但是在整体并发处理能力方面要远远更高，由于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势。

但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

行锁升级表锁场景：

  • 当多个事务同时访问同一行数据时，会使用行锁进行保护
  • 如果多个事务同时访问同一张表的不同行数据时，可能会使用表锁进行保护。
  • 需要对整个表进行操作时，如表的创建、删除、重命名，数据备份、迁移等操作。
  • 如果一个事务需要跨越多个索引进行操作，会导致行锁的升级，此时应该考虑使用表锁
  • 如果一个事务需要对大量行进行操作，会导致行锁的升级，此时应该考虑使用表锁。
  • 当MySQL使用行锁时，如果需要锁定的行已经被其他事务锁定，就会发生锁冲突。此时，MySQL会根据锁定的情况，自动升级锁级，从行锁升级至表锁，以保证数据一致性。
    update A set name=XXX where id >0

优化方案：
1.事务隔离级别
MySQL 支持四种事务隔离级别，分别为 读未提交、读已提交、可重复读 和 序列化。设置合适的事务隔离级别，可以在保证数据一致性的前提下，减少锁冲突和死锁的发生。

2.减少事务持有锁的时间
在事务中，尽量减少持有锁的时间，可以减少锁冲突的发生。例如，只在需要更新数据的时候才对数据加锁，更新完成后立即释放锁。

3.使用索引
在查询数据的时候，使用合适的索引可以减少锁冲突的发生。索引能够快速定位到需要的数据，避免全表扫描，从而减少锁的竞争。

索引失效，会全表扫描时，MySQL 会自动对表加上共享锁（Shared Lock），以保证其他事务不会对表进行更改(数据一致性，并发修改安全)
但当使用行级锁进行并发修改时，如果需要更新或者查询的数据行不能通过索引进行定位(会修改很多条)，MySQL会对整个表进行加锁，以保证数据的一致性。
此时，行锁会升级为表锁，可能会导致并发性能下降

4.分批次处理数据
如果需要处理大量的数据，可以将数据分批次处理，每次处理一部分数据，减少事务持有锁的时间，从而减少锁冲突的发生。

5.使用乐观锁
在一些情况下，使用乐观锁可以减少锁冲突的发生。乐观锁不会对数据进行加锁，而是在更新数据的时候检查数据的版本号，如果版本号没有发生改变，则更新数据，否则放弃更新。
综上所述，通过合理使用事务隔离级别、减少持有锁的时间、使用索引、分批次处理数据和使用乐观锁等方式，可以有效地减少锁冲突的发生，提高 MySQL 数据库的并发性能。

扩展
页级，表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
页级锁：介于表锁和行锁之间，一次锁定相邻的一组记录，BDB(嵌入式数据库) 支持页级锁，KV型数据库文件型
间隙锁：锁定一个范围的数据，可能此范围本身没键值

6. 慢查询日志

慢查询日志记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于 min_examined_row_limit 的所有的SQL语句的日志。long_query_time 默认为 10 秒，最小为 0， 精度可以到微秒。

2.3.1 文件位置和格式

慢查询日志默认是关闭的 。可以通过两个参数来控制慢查询日志 ：

# 该参数用来控制慢查询日志是否开启， 可取值： 1 和 0 ， 1 代表开启， 0 代表关闭
slow_query_log=1 

# 该参数用来指定慢查询日志的文件名
slow_query_log_file=slow_query.log

# 该选项用来配置查询的时间限制， 超过这个时间将认为值慢查询， 将需要进行日志记录， 默认10s
long_query_time=10

2.3.2 日志的读取

和错误日志、查询日志一样，慢查询日志记录的格式也是纯文本，可以被直接读取。

1） 查询long_query_time 的值。

2） 执行查询操作

select id, title,price,num ,status from tb_item where id = 1;

由于该语句执行时间很短，为0s ， 所以不会记录在慢查询日志中。

select * from tb_item where title like '%阿尔卡特 (OT-927) 炭黑 联通3G手机 双卡双待165454%' ;

该SQL语句 ， 执行时长为 26.77s ，超过10s ， 所以会记录在慢查询日志文件中。

3） 查看慢查询日志文件

直接通过cat 指令查询该日志文件 ： tail -n 20 slow_query.log 查询最后部分

如果慢查询日志内容很多， 直接查看文件，比较麻烦， 这个时候可以借助于mysql自带的 **mysqldumpslow **工具， 来对慢查询日志进行分类汇总。



7.事务

  • 原子性，一致性，隔离性，持久性

  • mysql默认自动提交的，当然也可以关闭自动提交on->off，开启事务，执行完毕手动提交

  • 隐式提交，当开启事务，执行sql未提交，此时执行create,alter,drop,grant..等待对数据库对象操作都会触发事务提交

  • 保存点，savepoint 保存点名称，如果执行了很多操作，但是不想全部回滚，可以回滚到某个保存点，保存点之前的操作就不回滚了

  • 版本链：表中每条数据都绑定一个唯一的trx_id(隐藏列)，roll_pointer(隐藏列)，而修改数据的trx_id则保存在版本链上，以便需要查找到和回滚(同一个事物中，多次修改)

  • 版本链中roll_pointer(隐藏列)指针链接着修改前数据，通过它可以找到修改前的数据

  • readview(快照)将未提交的事务记录下来，已提交的事务，会将trx_id从readview中移除

    readview 
读已提交
 上次查询：m_ids: [81,82,200,300]
 下次查询：m_ids: [81,82,300]

  • 而可重读会复用readview(快照)，不会将已提交的事务从m_ids排除

MVCC(多版本并发控制)

  • 多版本并发控制，允许多个事务同时访问数据库，互不影响，每个事务都创建一个readView(虚拟的“快照”，保存着数据库某个时刻的数据信息)管理自己的版本链数据
  • 支持读已提交和可重复读(读已提交每次都生成一个readView，可重复读只在第一次生成一个readView，后面都复用)

聚餐索引两个隐藏列:

  • trx_id：记录每次对某条聚簇索引记录进行修改的事务id

  • roll_pointer：对聚簇索引记录修改，将老版本写入undolog日志，此指针指向这条记录上个版本的位置(回滚数据)

  • readView记录活动的事务id(未提交的事务id，排序数组)，开启事务，就创建readview

    1,2,3 [4,6]7,8  //事务id是递增的，在readview左边(事务已提交)直接获取，在readview中或右表示未提交不能获取，应该通过pointer指针找到上一个版本链上数据获取(读快照)

锁

锁是与事务关联的，普通sql是自动开启事务和关闭事务的

  • mvcc控制，一个事务插入一笔数据未提交，其他事务是无法发现的
  • 写锁之后不能再加读锁，但是直接读取select * from t1 不涉及锁，不发生锁冲突，那么也可以访问
  • 间隙锁，锁定本身并且上下间隙的锁

解决幻读：1.mvcc复用readview【快照】防止幻读；2.间隙加锁


读写分离和分库分表

读写分离

读多写少，读写分离，主从同步，binlog同步

  • 流程：应用数据插入到master，此时生成一个binlog日志文件，io线程将master中binlog拷贝到slave这边来，之后slave中的sql 线程将同步的数据写入到slave数据库

  • 读写分离：查询只访问从库(可以有多个slave)，不访问主库

  • 强制路由：但是在高并发情况下生成订单可能出现，创建订单(master) -》查询订单(slave)-》支付费用；由于网络问题，导致slave没有及时导致无法获取订单信息，此时可以用强制路由解决

    /*master*/ select * from tb where id = 1   -- 注解，指定访问主库

分库分表

垂直拆分

拆分字段或模块

优点

  • 按业务拆分，清晰明了
  • 数据维护简单，不同业务放到不同机器上

缺点

  • 单表数据大，写读压力大(双 11)
  • 受某种业务决定也会影响数据库性能(某一天，新用户奖红包50元钱)
  • 表关联无法直接进行，只能通过代码实现
水平拆分

拆分内容

优点

  • 单表性能提高
  • 提供系统稳定性和负载能力
  • 切分表结构相同，程序改造少

缺点

  • 数据扩容困难，维护难度大，计算规则需要重新分配，保存数据到哪个库
  • 分布式事一致性很难关联join,union all将数据合并起来，需要java代码
  • 分布式唯一ID
  • 分布式事务 ACID
  • 跨库查询

  • proxy跨语言(mycat)，通过网络通讯(不涉及语言)，mycat只支持mysql，但不能跨数据库，多一层性能差
  • jdbc只支持java语言(Apache ShardingSphere),java应用中嵌套jar包，支持跨数据库

常见面试题

utf8mb4 设置原因

mysql的utf-8设计的时候考虑不用存那么多数据，设置0-3字节，而java的utf8是0-4字节，后面mysql发现了问题，比如不能存表情包等较长的信息，所以设置了个utf8mb4存4个字节，这样就避免出现java存utf8 ok，到mysql中却变成乱码


查询1000000之后10条数据怎么优化？

方案一：如果id是连续的，可以这样，返回上次查询的最大记录(偏移量)，再往下limit
select id，name from employee where id>1000000 limit 10.

方案二：order by + 索引（id为索引）
select id，name from employee order by id limit 1000000，10

方案四：覆盖索引找到所有的id,然后关联查出所有的数据，能走索引
SELECT a.* FROM employee a, (select id from employee where 条件 LIMIT 1000000,10 ) b where a.id=b.id


一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 MySQL 数据库，又插入了一条数据，此时 id 是几？

  • 表类型如果是 MyISAM ，那 id 就是 8。
  • 表类型如果是 InnoDB，只会把自增主键的最大id 记录在内存中，那id就是 6（数据连续）。

char 和 varchar 的区别是什么？

  • char(n) ：固定长度类型，比如订阅 char(10)，当你输入"abc"三个字符的时候，它们占的空间还是 10 个字节，其他 7 个是空字节。

chat 优点：效率高；缺点：占用空间；适用场景：存储密码的 md5 值，固定长度的，使用 char 非常合适。

  • varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。

所以，从空间上考虑 varchar 比较合适；从效率上考虑 char 比较合适，二者使用需要权衡。

float 和 double 的区别是什么？

  • float 最多可以存储 8 位的十进制数，并在内存中占 4 字节。
  • double 最可可以存储 16 位的十进制数，并在内存中占 8 字节。

MySQL 的内连接、左连接、右连接有什么区别？

内连接关键字：inner join；左连接：left join；右连接：right join。

内连接是把匹配的关联数据显示出来；左连接是左边的表全部显示出来，右边的表显示出符合条件的数据；右连接正好相反。

优化in和exists

没用到索引，当in中() 数据多，可能就不会走索引
SELECT DISTINCT user_name,town_name FROM t_farmers WHERE id IN 
(SELECT DISTINCT farmer_id FROM t_farmers_images WHERE create_time>='2017-07-05') 

将in子语句转化成嵌套sql,join关联，覆盖索引找到所有的id,然后关联查出所有的数据，能走索引
SELECT DISTINCT b.user_name,b.town_name FROM (SELECT DISTINCT farmer_id FROM t_farmers_images WHERE create_time>='2017-08-18') a
LEFT JOIN t_farmers b ON a.farmer_id=b.id

MySQL 索引是怎么实现的？

目前主流的数据库引擎的索引都是 B+ 树实现的，B+ 树的搜索效率，可以到达二分法的性能，找到数据区域之后就找到了完整的数据结构了，所有索引的性能也是更好的。


什么是索引下推？

一种数据库优化技术，在索引层就进行了条件过滤和排序，大大提升性能，避免传统哪样先将所有数据先拿到再进行过滤，


mysql存储引擎为啥不用二叉树，b树数据结构？

为什么不是平衡二叉树

  • 平衡二叉树可是每个节点只存储一个键值和数据的，如果是B树，可以一个节点存储更多的数据，树的高度也会降低，因此磁盘io次数就降下来啦，查询效率就快啦。

  • 范围查找更快，比如找大于2，从根节点找一次2，之后只需要从叶子节点往右就可以了，而二叉树还需要遍历右子树

那为什么不是B树而是B+树呢

1）B+树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。innodb中页的默认大小是16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更小，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少，数据查询的效率也会更快。
2）B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

Page 结构

1)文件头/尾：文件头和文件尾，通过他们可以将所有数据页串联起来，形成一条双向链表，进行查找数据

2)记录数据部分：有最小记录，最大记录，用户记录，还有一个空闲空间，用户新的数据插入

3)数据目录部分

Buffer Pool

Buffer pool：mysql缓存池，简称BP，默认128M，缓存数据和索引数据，提高查询性能，减少IO

在资源竞争不是很激烈的时候，命中缓存也会很快，Buffer Pool，但是资源紧张的时候，会快速被其他数据覆盖(慢sql的时候，出现的误判)


组成：

  • 缓存数据页(Page)，16KB
  • 控制块(缓存数据页进行描述：表空间，数据页编号，对应在BP中地址，多申请6M信息)

如何判断一个在BufferPool中缓存了？
通过一个hash表(k,v) ，存储格式：表空间号+数据页号 对应控制块

key存在即直接拿取缓存页，如果不存在再按照这样格式存进去

为什么写缓冲器，仅使用普通索引？

Change Buffer：CB,存储变更sql(增删改)，针对二级索引(辅助索引)页更新优化
作用：在更新操作，如果请求的辅助索引没在缓冲池，不会立刻刷新进去
而是记录缓存变更，等后面读取才更新合并到BP中

Mysql lru算法改进原因

Mysql缓存 lru算法：最近被查询的数据(热点数据)优先缓存，很久没查询的数据最先过期(链表，热数据在head,否 在tail)

缺陷：只要做了一次全表查询，热点数据就全部被覆盖，排序到后面了

head（热点数据） tail
|--------------------------------------|

改进型lru算法

热数据区 midpoint 冷数据区
|-------------------||-------------------|

将链表分为冷热数据区，数据插入从中间midpoint插入：

1.如果数据很快被访问，page向前移，否则会逐渐向后移淘汰
2.如果数据页在lru链表超过1s，将向前移动，短于1s，位置不变(全表扫描对频繁某个数据访问会很短时间<1s，剔除掉全表扫描影响)

ACID怎么保证

  • 原子性，undolog日志保证，记录了需要回滚的日志，事务回滚撤销执行sql
  • 一致性，其他三大特性保证
  • 隔离性(幻读)，由MVCC事务或间隙锁保证
  • 持久性，内存+redo log保证，更新数据同时在内存和redo log记录操作，宕机时直接从redo log恢复

binlog 和 undolog、redolog日志区别

  • 1.redolog 是innodb引擎独有的(持久化)，binlog是mysql的server层实现(主从同步log)，都可以使用
  • 2.redolog是物理日志(row级别:详细记录每一行数据，所以能恢复)，binlog是逻辑日志(statament：记录执行sql)
  • 3.redolog是循环写的，用完空间会覆盖日志，binlog是追加写的，写到一定大小会切换到下一个空间，不会覆盖日志
    -- 通过write pos(日志当前记录位置)和check point(写进磁盘的位置,最大位置)实现，当当前位置大于写进磁盘位置，即已经写满了，会覆盖
  • 4.redolog服务器宕机事务数据自动恢复，binlog作为主从同步和数据恢复，没有事务恢复能力
  • 5.undolog，记录事务执行时原始sql，如果事务需要回滚，直接从Undolog中恢复即可，同时实现mvcc（多版本控制）

binlog日志不同模式的区别？

1.statement：每一条会修改数据的sql都会记录在binlog中。

优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。

缺点：主从上执行保证结果相同会有些问题，必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题(如sleep()函数)

2.row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。

优点： binlog中记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行数据修改的细节。

缺点:比如一条update语句，修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，IO性能

3.mixedlevel: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式


索引有哪几种类型？

  • 主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键。
  • 唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
  • 普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值。
  • 全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索。
  • 覆盖索引：查询列要被所建的索引覆盖，不必读取数据行
  • 组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并

InnoDB为什么推荐使用自增bigint作为主键id？

自增ID可以保证每次插入时B+索引是从右边往后的，可以避免B+树频繁分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差，储存也小写。
bigint：索引占用空间减少，一页16kb固定，同一层可以存储更多索引，b+树高低不深，减少磁盘io(递归)，bigint雪花算法，同时保证表合并主键冲突问题


覆盖索引、回表等这些，了解过吗？

覆盖索引： 查询列要被所建的索引覆盖，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

 create index IDX_USER_ID on ORDER_DTL(C1."user_id") include ("id", C1."money") 覆盖索引，指定列数据放到索引上，一次查询即可，避免二次回表

回表：二级索引(非聚簇索引)无法直接查询所有列的数据，所以通过二级索引查询到聚簇索引后，再查询到想要的数据，这种通过二级索引查询出来的过程，就叫做回表。

sql注意点？

  • 联合索引违反最左原则
  • 范围查找，之后不走索引
  • 全局模糊查询，或左边%模糊查询
  • 查询条件列进行运算
  • 参数值强转类型
  • union
  select username,tel from user
  union
  select departmentname from department
  • or 其中一个条件没走索引
    SELECT * FROM user WHERE id=1 OR salary=5000 -->优化：union all 或分开两次查询
  • is null 可能数据都不满足，mysql默认会走全表，因为比走索引还快
    SELECT * FROM user WHERE age IS NOT NULL -->优化：age>0 -- !=，<>，is null，is not null经常被认为让索引失效
  • in ,not in 失效
  • inner join 、left join、right join，优先使用inner join ，如果实在要用left join，建议小表关联大表, 左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少；
  • 清除表所有的数据： truncate table 替代delete

B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据？

在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引。 在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。

当查询使用聚簇索引(id)时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。

id自增，相对非聚簇索引，少一次IO(数据在叶子节点)，适合范围查找，但id不能更新(一般不能更新id，顺序可能被打乱需要重新分裂排序B+树)

非聚簇索引一定会回表查询吗？

不一定，如果查询语句的字段全部命中了索引，那么就不必再进行回表查询（哈哈，覆盖索引就是这么回事）。

举个简单的例子，假设我们在学生表的上建立了索引，那么当进行select age from student where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

说一下数据库的事务隔离？

MySQL 的事务隔离是在 MySQL. ini 配置文件里添加的，在文件的最后添加：

transaction-isolation = REPEATABLE-READ

  • 读未提交（Read Uncommitted），最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）。
  • 读已提交（Read Committed），一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读）。
  • 可重复读（Repeatable Read），默认级别，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，事务未结束，数据变化不能影响到当前事务（会造成幻读）。
  • 串行化（Serializable），代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。

脏读 ：表示一个事务能够读取另一个事务中还未提交的数据。比如，某个事务尝试插入记录 A，此时该事务还未提交，然后另一个事务尝试读取到了记录 A。
不可重复读 ：是指在一个事务内，多次读同一条数据，结果不一样。
幻读 ：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。

说一下 MySQL 的锁

img

MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

如果按锁粒度划分，有以下3种：
表锁： 开销小，加锁快；锁定力度大，发生锁冲突概率高，并发度最低;不会出现死锁。
行锁： 开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。
页锁： 开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

从锁的类别上来讲，有共享锁和排他锁。
共享锁: 又叫做读锁。当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。
排他锁: 又叫做写锁。当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

说一下乐观锁和悲观锁？

悲观锁(关闭自动提交)：一个事务持有悲观锁后，其他任何事务都不能对数据进行修改啦，只能等待锁被释放。

乐观锁：它允许多个事务同时对数据进行变动，通过版本号进行控制，只有大于当前版本号的事务才执行成功(别的事务未修改过)。实现方式：乐观锁一般会使用版本号机制或CAS算法实现。

乐观锁实现：大多数基于数据版本（Version）记录机制实现
应用：mq重复消费问题

悲观锁和乐观锁是数据库用来保证数据并发安全防止更新丢失的两种方法：
场景适用
1.响应速度：如果需要非常高的响应速度，建议采用乐观锁方案，成功就执行，不成功就失败，不需要等待其他并发去释放锁
2.冲突频率：如果冲突频率非常高，建议采用悲观锁，保证成功率，如果冲突频率大，乐观锁会需要多次重试才能成功，代价比较大(1000个商品抢购，使用乐观锁就不明智了，不能每次只允许某一个抢购成功，其他999次失效)
3.重试代价：如果重试代价大，建议采用悲观锁

select for update有什么含义，会锁表还是锁行还是其他。

select查询语句是不会加锁的，但是select for update除了有查询的作用外，还会加锁呢，而且它是悲观锁哦。至于加了是行锁还是表锁，这就要看是不是用了索引

select * from t where id = 666 for update;

  • 非索引，行锁升级为表锁
  • 查询条件没用索引/主键的话就是表锁，否则就是是行锁。

MySQL 问题排查都有哪些手段？

  • 使用Show profiles 查看分析当前会话SQL语句执行的资源消耗情况
  • 使用 explain 命令查询 SQL 语句执行计划。
  • 开启慢查询日志，查看慢查询的 SQL。

死锁

  • 事务占用资源基础上还相互等待对方释放资源

  • 不同表相同行记录资源

    事务A 事务B
    更新A表 更新B表 (这一步，未提前事务前提下(释放资源)，继续操作)
    更新B表(等待) 更新A表(等待)

  • 同表不同记录资源

    事务A 事务B
    更新A表 where a=1 更新A表 where a=2 (这一步，未提前事务前提下(释放资源)，继续操作)
    更新A表(等待) where a=2 更新A表(等待) where a=1

  • 不同索引冲突，范围锁定或间隙锁(gap锁冲突)

    事务A 事务B
    锁定 id>8 锁定 id<8 (这一步，未提前事务前提下(释放资源)，继续操作)
    更新id=1(等待B) 更新id=9(等待A) 死锁

    解决方案：
    1.固定顺序访问表和行
    2.大事务拆小，锁定资源更小

死锁检测：

简单解决方法：

  • 设置阈值，其中一个事务超过等待阈值自动释放，等待超时
  • innodb wait-for graph算法，检测的出闭环，直接报错，一个执行回滚，一个完成

避免措施：

解决死锁思路，一般就是切断环路，尽量避免并发形成环路。

  • 在不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

  • 同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

  • 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

  • 如果业务处理不好可以用分布式事务锁或者使用乐观锁

  • 死锁与索引密不可分，解决索引问题(间隙锁，行锁，表锁)，需要合理优化你的索引

SQL语句执行顺序

img

一条SQL语句在MySQL中如何执行的

img

总结：权限-》缓存-》语法分析(是否报错)-》执行计划生成，索引选择-》数据库引擎接口权限-》获取查询结果

  • 先检查该语句是否有权限，如果没有权限，直接返回错误信息

  • 如果有权限，在 MySQL8.0 版本以前，会先查询缓存。

  • 如果没有缓存，分析器进行词法分析，提取 sql 语句select等的关键元素。

  • 然后判断sql 语句是否有语法错误，比如关键词是否正确等等。

  • 进行权限校验，如果有权限就会调用数据库引擎接口，返回执行结果。

一条sql执行过长的时间，你如何优化，从哪些方面入手

  • 涉及多表和子查询，优化Sql结构，去除冗余字段，垂直分表

  • 优化索引，看是否可以适当添加索引，是否合理，索引失效等等

  • 数量大的表，水平分表

  • 数据库主从分离，读写分离

  • explain分析sql语句，查看执行计划，优化sql

  • 慢日志查询

Mysql 架构

主从复制架构

img

1.主库的更新事件(update、insert、delete)被写到binlog

2.从库发起连接，连接到主库(心跳)。

3.此时主库创建一个binlog thread，把binlog推送从库

4.从库创建一个I/O线程，读取binlog内容并写入到relay log，同时创建sql线程，将内容写入db

分布式架构(水平分库)：MySQL也可以部署在分布式环境中，将数据分散到多个节点中，通过分片、负载均衡等技术来实现高可用性和扩展性。

  • 分库分表模式：每个分片数据不一样，节点宕机，会导致该节点数据查询不出来
  • 分片算法：
    • 范围法：比如按照1个月进行分片，结构简单，但数据分布不均匀，局部负载压力大
    • 哈希法：hash运算存放数据，数据分片均匀，节点扩展复杂(重新需要hash放数据)，数据迁移难(无规则)

集群架构MySQL Cluster ：MySQL也支持集群架构，通过将多个MySQL节点组成一个集群，共同处理用户的请求，提供高可用性和可扩展性。

  • SQL节点：负责与WEB应用程序交互，承接来自上层的SQL命令，
  • 数据节点：用于存储数据，每个数据节点都会存储所有的数据，这样当一个数据节点宕机后，还会有其他的数据节点可以存储数据，系统仍然可以使用。
  • 管理节点：起到联系并管理整体架构的作用。

存储节点使用的存储引擎为NDB引擎。NDB引擎是一种分布式的基于内存的引擎，因此作为MySQL集群Cluster架构中的存储节点，内存一定要足够大。

优点：
1、基于内存存储，没有磁盘I/O瓶颈，速度快。
2、扩展性好，增加节点即可实现数据库集群Cluster的扩展。
3、冗余性好，单个节点故障，集群还可以提供服务。

缺点：
1、由于存储基于内存，因此在设备断电后数据即丢失。
2、存储空间大小受内存大小限制。
3、多个节点分布式架构整体速度受会网络速度影响。


索引计算

mysql索引就是b+树数据结构，树节点都是放在磁盘上，查找一次就与磁盘交互一次，磁盘io读取

hash数据结构问题：指针冲突，范围查找

假设高度为3的b+树能存放多少索引？

计算节点(索引节点)数量，一页(一层)大小：16kb

假设索引类型bigint，占8个字节，占位符为6字节 ，16kb/（8+6）=1170个索引

然后下一级最多也可以是1170，叶子节点存放数据，假设1kb，则16kb/1kb=16个索引

可以存放 1170 * 1170 * 16 = 2千万多的索引，查找索引都是在内存中，而且折半查找，只有查询数据才需要读取磁盘，所以效率非常高

elasticSearch

倒排索引原理:

关键词文档id映射
** 简单描述，词项字典(词项+id有序int数组) ，结果表(id，content) 通过关键词找到id，然后通过id查结果表中数据**

1.通过词项字典(分词,倒排表id)；倒排表(id有序int数组，压缩算法for,rbm)，词项索引(FST压缩)，实现倒排索引功能
2.通过关键词匹配次数(相关度)进行倒叙，进行排名
3.可能字典树中存在大量的词(比如10亿)，通过前缀树（Prefix Tree）对重复的前缀进行去重(复用)，如果出度不同，产生另外的分支
4.可能某个词项对应到大量的id，此时es底层会采用For 和 RBM算法压缩(类似clickhouse)


MySQL（B+Trees）不适合做全文检索的原因

1.只能对键进行精确匹配或范围查询，无法进行分词、匹配

2.如果键值很长（例如一段文本），则会占用很多空间，导致B+树高度增加，更多磁盘io。

3.性能无法保证并且索引会失效

因此，如果要做全文检索，建议使用专门的全文索引技术，例如MySQL的全文索引或Elasticsearch等。

es几十亿数据性能优化

1.文件系统缓存(垂直拆分):es数据会写到磁盘文件，系统会自动将里面的数据缓存到缓存中，如果给filesystem cache更多的内存，内存容纳所有的索引数据文件，性能高很多；
**所以将需要查询和热点的字段放入es中，其他结果字段放在mysql,clickhouse,hbase，保证es存储的数据大小接近的filesystem cache(64g)的内存容量，那么每次查询都是从内存中获取
--》垂直拆分(s->ms)

2.冷热分离(水平拆分)：
热数据和冷数据分别在不同索引中存放，确保热数据在被预热之后，尽量留在系统缓存，没被冷数据给冲刷掉，就可以确保热数据的访问性能是很高的。

3.数据预热
将一些热点数据，比如电商中苹果手机，每隔1分钟主动访问一次，刷到filesystem cache里去，这样用户直接在缓存中查到热点数据，性能高很多。

4.分页性能优化
es的分页是较坑的？举个例子，假如你每页是10条数据，你现在要查询第100页，如果你有个5个shard，那么就有5000条数据，接着协调节点对这5000条数据进行一些合并、处理，再获取到最终第100页的10条数据(mycat这些就是这样处理的，同理是否mysql优化也是通过游标一样优化)。
1）不允许深度分页/默认深度分页性能很惨，默认翻的越深，性能就越差
2）类似于头条中，下拉刷头条，刷出来一页一页的，你可以用scroll api，scroll会一次性给你生成所有数据的一个快照,用scroll不断通过游标获取下一页数据，这个性能是很高的。
缺点是，只适用于连续翻页，不适合随机跳页，类似微博

clickhouse

ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。主要用于WEB流量分析。ClickHouse的全称是Click Stream,Data WareHouse。
ClickHouse非常适用于商业智能领域，除此之外，它也能够被广泛应用于广告流量、Web、App流量、电信、金融、电子商务、信息安全、网络游戏、物联网等众多其他领域。

ClickHouse缺点：对业务库操作不友好

1.不支持事务
2.不擅长根据主键按行粒度进行查询（虽然支持），故不应该把ClickHouse当作Key-Value数据库使用。
3.不擅长按行删除数据（虽然支持）

MergeTree(合并树)表引擎

MergeTree在写入一批数据时，数据总会以数据片段的形式写入磁盘(不可修改)。为了避免片段过多，ClickHouse会定期合并数据片段，属于相同分区的数据片段会被合成一个新的片段。

特点：
1.存储的数据按照主键排序：允许创建稀疏索引，从而加快数据查询速度
2.支持分区，可以通过PRIMARY KEY语句指定分区字段。
3.支持数据副本
4.支持数据采样

稀疏索引：只对部分数据进行索引(排除空值或者默认值)，而对于未被索引的数据则不进行索引。相对于其他索引方式，稀疏索引可以减小索引的大小，提高索引的效率，例如采用位图索引、哈希索引等。

ClickHouse性能高效的原因

1.列式存储，存储数据连续的，顺序读取即可（10,20），而行式（10，A，20，B），计算统计性能就远胜于行式数据存储方式
age name
10 A
20 B
2.数据库一列数据类型形同，列式数据存储有比行式存储高达10倍以上的压缩比，节省了大量的磁盘及内存空间，可以有效降低服务器成本
3.clikhouse支持分布式分片数据存储的集群模式。数据以分片的行式，存储在多台服务器节点上面，因此ck可以利用集群服务器的规模计算能力，快速的做出数据统计结果的响应
4.向量化执行引擎(相当于并行处理)：SIMD（Single Instruction Multiple Data）即单条指令操作多条数据，它是通过数据并行以提高性能的一种方式，可以简单理解为在寄存器层面对程序中的数据做并行处理，大大提高性能。

clickhouse后台优化，调优

1. 根据查询特点分区

ClickHouse 支持分区表，将表分成多个分区，可以提高查询性能。例如，将按日期分区的订单表，按月份或季度分区会更好一些，可以减少跨分区查询的次数。

2. 合理选择数据类型

支持多种数据类型，例如 Int8、Int16、Int32、Int64、Float32、Float64等。选择恰当的数据类型可以减少数据的存储和查询时间。例如，如果数据只有 0 和 1 两种取值，可以使用 UInt8 类型来保存，可以大大减少存储空间。

3. 使用合适的压缩算法

ClickHouse 支持多种不同的压缩算法，例如 LZ4、ZSTD、Brotli 等。选择恰当的压缩算法可以减少数据的存储空间并提高查询性能。例如，LZ4 算法适用于压缩一些短小的数据块，而 ZSTD 算法则适用于压缩一些较长的数据块。

4. 合理配置缓存

ClickHouse 有多种缓存，例如操作系统缓存、数据块缓存、索引缓存、查询缓存等。适当配置这些缓存可以提高查询性能。例如，可以将操作系统的页缓存调整到合适的大小，以充分利用系统内存。

5. 使用分布式集群

ClickHouse 支持分布式集群，可以将数据分散到多个节点上，提高查询性能和可伸缩性。
6. 定期维护表和索引

ClickHouse 的表和索引需要定期维护，以保持查询性能。例如，可以使用 optimize table 命令进行表的优化，使用 optimize index 命令进行索引的优化。

7. 合理使用分区键和排序键

ClickHouse 的分区键和排序键对查询性能有很大影响。适当选择分区键和排序键可以提高查询性能。例如，如果查询时经常按照日期和订单号进行过滤和排序，可以将日期和订单号作为分区键和排序键，以加速查询。

8.sql优化，比如聚合外推，谓词下推，自动优化where转prewhere

Prewhere 和 where 语句的作用相同，用来过滤数据。不同之处在于 prewhere 只支持MergeTree族系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤，之后再读取 select 声明的列字段来补全其余属性，而Where过滤器是在数据读取阶段之后执行，当查询列明显多于筛选列时使用 Prewhere 可十倍提升查询性能

需要注意点：
1.Prewhere过滤器只能使用到查询语句中的列，而Where过滤器可以使用到查询语句中的所有列
2.由于Prewhere过滤器是在数据读取之前执行的，因此如果对查询的列进行了操作，如对日期进行了格式化，那么Prewhere过滤器将无法使用索引进行优化，这时候需要使用Where过滤器来进行过滤。
3.Prewhere过滤器只能用于查询语句中的单表查询，不能用于多表关联查询。

hive和clickhouse区别

  1. 数据模型不同：Hive是基于Hadoop的分布式数据仓库，数据模型是基于关系型数据库的表格模型。而ClickHouse是基于列式存储的数据仓库，数据模型是基于列式存储的数据模型。
  2. 性能不同：ClickHouse的性能比Hive(mapreduce作为计算引擎，基于磁盘，效率不高)更高效，可以在秒级别内处理数亿级别的数据。ClickHouse采用了多个优化技术，如数据压缩、向量化计算、数据分区等，以提升查询速度。
  3. 数据存储方式不同：Hive使用HDFS作为底层存储，ClickHouse可以使用多种存储方式，如本地磁盘、HDFS、S3等。
  4. 数据处理方式不同：Hive采用批处理方式（达到一定量才能进行计算），适用于离线数据分析和数据挖掘（数据量大，但查询要求不高的场景），而ClickHouse采用实时处理方式，适用于实时分析和数据仓库（快速查询大量数据的场景）

clickhouse去重，使用幂等性：分布式锁等实现，当并发量太大，用分布式锁性能不够，使用布隆过滤器不精准去重

posted @   pandazou  阅读(2)  评论(0)  编辑  收藏  举报
相关博文：
·  高级面试题(new)
·  Spring 面试题(new)
·  Mysql面试题 mysql执行流程 explant 一般有用 看5 速
·  Mysql面试题
·  一招带你吃透MySQL高级
阅读排行：
· winform 绘制太阳，地球，月球 运作规律
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· AI与.NET技术实操系列（五）：向量存储与相似性搜索在 .NET 中的实现
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
点击右上角即可分享
微信分享提示
AI IDE Trae