mysql索引

Mysql（数据库）

聚集索引的行数据（叶节点）和主键B+树存储在一起，辅助索引只存储辅助键（自己的key）和主键（聚集索引的key），聚集索引 聚集索引是按每张表的主键构造的一颗B+树

ACID四大特性实现（重要）

对MySQL来说，逻辑备份日志（binlog）、重做日志（redolog）、回滚日志（undolog）、锁技术 + MVCC就是MySQL实现事务的基础

• 原子性（A）：通过undolog来实现
• 持久性（D）：通过binlog、redolog来实现
• 隔离性（I）：通过(读写锁+MVCC)来实现
• 一致性（C）：MySQL通过原子性，持久性，隔离性最终实现（或者说定义）数据一致性

常见日志文件（重要）

日志文件是记录修改行为而不是记录数据的修改

redolog（固定大小，覆写）

innodb独有

当发生操作时，将数据先记录在redolog中，异常也不会丢失提交的数据，这叫crash-safe（落盘处理）

WAL技术

先写日志，在写磁盘

binlog（恢复数据，追加写）

实现crash-safe能力

• Server 层：它主要做的是 MySQL 功能层面的事情
• 引擎层：负责存储相关的具体事宜

undolog

记录旧数据，当需要回滚时，对DML操作进行逆向操作（删除就添加。。。），也是实现MVCC的重要依赖

slow query log（了解）

需要手动参数开启，用来记录慢sql

字符集和校对

字符集：用来表示字符的编码方式

校对：字符集的排序规则

数据类型

选取数据的原则：选取最小适合的数据类型

整数类型

• int：正常的短整数，int（20）的20是对数的范围没有作用的，只是用来限制宽度
• bigInt：常用于分布式id

属性：unsingend（非负数）使数的正数范围提高一倍

实数类型

• decimal：用于存储比较精细精度（金钱），还可以存储比bigInt还大的整数

一种优化手段：declmal的计数精度的代价较大，可以用bigint来做优化，将小数部分X小数位的倍数，到后面在来做精度回滚的操作

字符串类型（重要）

varchar：可变字符串，在存储时会根据字符串实际长度存储+最大2个字节的最大位数（>255一个字节，<255两个字节），由于可变性可能会导致页分裂而产生碎片，所以不建议存储经常变动的字符串

char：固定字符串，在存储时会存储到一个固定的大小，没有那么大会空格填充，取出时去掉空格填充（会导致尾部空格被截断），按照实际长度取出来，空间固定，不存在碎片问题

BLOB和TEXT

都是为了存储长字符串，分为字符方式和二进制存储方式

• 字符类型（TEXT家族）；由字符集和排序规则
• 二进制类型（BLOB家族）

枚举（Enum）

常用来代替字符串，实际存储位整数，不是字符串

日期和时间类型

• datatime：可存储10001到99999年，使用8个字节，不依赖时区
• timeSimple：使用4个字节，依赖时区，自动更新

MySQL的binlog有几种录入格式？分别有什么区别？

有三种格式，statement，row和mixed。

• statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。
• row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。
• mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。
  此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

innoDB索引（聚簇索引）底层

Innodb不建议使用过长的主键，否则会使辅助索引变得过大

底层结构

底层涉及到B+Tree索引和Hash索引，主要还是B+tree结构（聚簇索引和非聚簇索引）

数据页的结构：页（16kb）是inooDB最小的磁盘管理单位，可存储65535varchar，实际只可以65532

数据页组成：File Header(文件头) Page Header(页头) Infimun + Supremum Records User Records(用户记录，即行记录双向链表连接) Free Space(空闲空间) Page Directory(页目录) File Trailer(文件结尾信息)

Infimum（最小）和Supremum（最大）记录（限定记录的边界）：

Page Directory 页目录：存放了记录的相对位置，有些时候这些记录指针称为Slots(槽)或者目录槽，是一种8>X>4的稀疏槽，槽中记录按照键顺序存放来方便二分查找

B+tree—->数据页—–>数据页加载到内存—->二分查找Page Directory——>目录槽指针指向行记录（不一定精确）[—–>遍历双向链表得到行记录（精确）]

B+tree索引

hash索引

Hash索引 InnoDB中自适应哈希索引使用的是散列表的数据结构，并且DBA无法干预。 其实这一部分的原理，非常简单，在此就不做过多介绍了

MyISAM索引（非聚簇索引）

MyISAM的索引（存储数据地址）和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小

主索引

key唯一，通过B+tree直接找到数据的地址

辅助索引

key不唯一，可以重复，和主索引的查找基本没什么差异

回表

先通过辅助索引来查找主索引的key，在通过主索引的key找到数据行，这就叫回表

hash索引

memory存储引擎的索引，只有全索引匹配才能生效

关联查询的索引使用

只有当多表的字符集是一样的时候才可以利用索引来优化联合查询，否则会导致全表扫描

反范式化

都放一张表：数据比内存大时比联合查询快得多

现实混用范式化和饭范式化

一般很少极端的用一种，现实都是混用的，合理设计避免反范式化的插入和删除问题，由可以高效的查询数据

缓存表和汇总表

• 缓存表常用存储读取速度慢的数据
• 常用ordy by聚合的表数据存储

数据备份

innoDB

InnoDB导出SQL来备份，拷贝数据文件、备份 binlog，或者用 mysqldump，支持灾难恢复（仅需几分钟）

MyISAM

MyISAM应对错误编码导致的数据恢复速度快。MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便，遇到数据崩溃，基本上很难恢复，所以要经常备份数据

锁

innoDB

支持行锁和表锁，InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的

覆盖索引（Cover Index）

如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index)，也就是平时所说的不需要回表操作

InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效

简介：覆盖索引覆盖所有需要查询的字段（即，大于或等于所查询的字段）。MySQL可以通过索引获取查询数据，因而不需要读取数据行

覆盖索引的好处：

1. 索引大小远小于数据行大小。因而，如果只读取索引，则能极大减少对数据访问量。
2. 索引按顺序储存。对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少。
3. 避免对主键索引的二次查询。二级索引的叶子节点包含了主键的值，如果二级索引包含所要查询的值，则能避免二次查询主键索引（聚簇索引，聚簇索引既存储了索引，也储存了值）。

什么是最左前缀原则？什么是最左匹配原则

• 顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边

• 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整

• =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

B树和B+树的区别

• 在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。

• B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。

  

InnoDB存储引擎的锁的算法有三种

Record lock：单个行记录上的锁

Gap lock：间隙锁，锁定一个范围，不包括记录本身

next-key lock：record+gap 锁定一个范围，包含记录本身

相关知识点：

• innodb对于行的查询使用next-key lock
• next-locking keying为了解决Phantom Problem幻读问题
• 当查询的索引含有唯一属性时，将next-key lock降级为record key
• Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生

存储过程

存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需要创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快

触发器

作用：在DML语句时，执行一些特定的操作，和语句一起具有原子性，可以用来做一些sql日志等

• Before Insert
• After Insert
• Before Update
• After Update
• Before Delete
• After Dele

SQL语句

数据定义语言DDL（Data Ddefinition Language）CREATE，DROP，ALTER

主要为以上操作 即对逻辑结构等有操作的，其中包括表结构，视图和索引。

数据查询语言DQL（Data Query Language）SELECT

这个较为好理解 即查询操作，以select关键字。各种简单查询，连接查询等 都属于DQL。

数据操纵语DML（Data Manipulation Language）INSERT，UPDATE，DELETE

数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK

主要为以上操作 即对数据进行操作的，对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。而查询是较为特殊的一种 被划分到DQL中。

delete、truncate、drop的区别

• 都可以删除数据
• delete DML语句，触发trigger，删表（不带where）MyISAM立即释放空间（可回滚）
• truncate DDL语句，不触发，快速清空一个表。并且重置auto_increment的值（不可逆）
• drop DDL语句，触发trigger，立即释放空间（不可逆）
• delete是删目录，truncate是删内容，drop是烧书

插入操作（多样插入）

正常insert插入

如果没有插入，有就报错

插入或更新

如果没有就插入，有就更新

-- 用户陈哈哈充值了30元买会员
INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark) 
   VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', '充会员') 
   ON DUPLICATE KEY UPDATE  total_amount=total_amount + 30, last_transTime='2020-06-11 20:00:20', last_remark ='充会员';
 
-- 用户陈哈哈又充值了100元买瞎子至高之拳皮肤
INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark) 
   VALUES (null, 'chenhaha', 100, '2020-06-11 20:00:20', '购买盲僧至高之拳皮肤') 
   ON DUPLICATE KEY UPDATE total_amount=total_amount + 100, last_transTime='2020-06-11 21:00:00', last_remark ='购买盲僧至高之拳皮肤';

插入或删除

如果没有就插入，有就先删除重置为默认在插入

replace into （删除所有重复）与 insert on deplicate udpate （删除一条）比较

1、在没有主键或者唯一索引重复时，replace into 与 insert on deplicate udpate 相同。

2、在主键或者唯一索引重复时，replace是delete老记录，而录入新的记录，所以原有的所有记录会被清除，这个时候，如果replace语句的字段不全的话，有些原有的比如c字段的值会被自动填充为默认值（如Null）。

3、细心地朋友们会发现，insert on deplicate udpate只是影响一行，而REPLACE INTO可能影响多行

插入或忽略

如果没有正常插入，有就忽略不做插入

insert ignore into 

批量插入

<insert id="insertListUser" parameterType="java.util.List">
    INSERT INTO `db`.`user_info`
        ( `id`,
          `username`,
          `password`,
          `price`,
          `hobby`) 
     values
    <foreach collection="list" item="item" separator="," index="index">
        (null,
        #{item.userName},
        #{item.password},
        #{item.price},
        #{item.hobby})
    </foreach>

</insert>

大量数据插入性能过慢怎么办

1. 将大量数据分批插入

2. 就数据分流到从库

3. 插入前关闭索引，插入后开启索引

   ALTER TABLE user_info DISABLE KEYS #关闭表索引
   ALTER TABLE user_info ENABLE KEYS #开启表索引
   

char和varchar的区别

char

长度固定，使用空格填充，存取速度快，是以空间换时间的，最多255个字符,存储会删除末尾的空格

varchar

长度不固定，根据数据的长度存储，存取慢，是以时间换空间的，最多65532个字符,不会删除末尾的空格

varchar（50）

varchar（50）{最多可存储50个字符，早期是代表字节}和（150）存取hello时所占用的空间时一样的，但是越大排序时会消耗更多内存

int（20）

代表最宽长度为20，但是原来的存储范围不会变的， 只会影响zerofill补0

隐式转换

类比类型向高精度转换

非类比类型,两个参数都会被转换为浮点数再进行比较,非数字转换为0,数字转换为double

sql优化

explain sql语句

通过性能分析sql执行，来人为的来筛选出不符合的sql语句来进行优化

开启慢查询日志

通过开启一个参数命令，来记录不符合的慢sql到一个日志文件中，然后着重的优化这些日志记录的sql就行

如果要存储用户的密码散列，应该使用什么字段进行存储？

密码散列，用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率

数据库优化

优化原则：减少系统瓶颈，减少资源占用，增加系统的反应速度

数据库结构优化

• 水平拆分（特征）和垂直拆分（业务）
• 增加中间表
• 适当的添加冗余字段

主从复制

数据转移

基本流程：master binlog（二进制日志）—->slave new thread —–>relaylog（中继日志）——>sql thread（读取事件，执行数据复制）