MySQL的binlog有几种录入格式

statement模式：记录会修改数据的sql语句保存到数据库中，优点是不需要记录每一行的变化，减少binlog日志，节约了IO，提高了性能。
row模式：不记录sql语句，仅保存哪条数据被修改，这就意味着记录单元是每一行的改动。缺点是日志量太大，保存的信息很多。
mixed模式：一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

MySQL有哪些数据类型

1.整数类型 tinyInt smallInt mediumint int(integer) BIGINT
2.实数类型 float double decimal
3.字符串类型 varchar、char、text、blog

  • varchar 与 char的用法与区别
    • vachar
      存储可变长字符串
      使用额外1或2个字节存储字符串长度。列长度小于255字节时，使用1字节表示，否则使用2字节表示
      存储的内容超出设置的长度时，内容会被截断
    • char
      是定长的，根据定义的字符串长度分配足够的空间。
      会根据需要使用空格进行填充方便比较
      存储很短的字符串，或者所有值都接近同一个长度
      存储的内容超出设置的长度时，内容同样会被截断

使用策略：
对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。
对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。
使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。
尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。

4.枚举类型（ENUM）
5.日期和时间类型 year time date datetime timestamp
尽量使用timestamp，空间效率高于datetime，用整数保存时间戳通常不方便处理。

MySQL存储引擎MyISAM与InnoDB区别

常用的存储引擎有以下：

Innodb引擎：
Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
MyIASM引擎(原本Mysql的默认引擎)：
不提供事务的支持，也不支持行级锁和外键。
MEMORY引擎：
所有的数据都在内存中，数据的处理速度快，但是安全性不高。

索引

什么是索引

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构，有利于快速取数据，保证数据记录的唯一性，实现表与表之间的参照完整性，在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。

索引的创建

创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表)；
修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表)；

-- 增加一个没有建立索引的字段
alter table innodb1 add sex char(1);
-- 按sex检索时可选的索引为null
EXPLAIN SELECT * from innodb1 where sex='男';

where order by join

索引创建的三种方式

1.创建表的时候创建索引

CREATE TABLE user_index2 (
    id INT auto_increment PRIMARY KEY,
    first_name VARCHAR (16),
    last_name VARCHAR (16),
    id_card VARCHAR (18),
    information text,
    KEY name (first_name, last_name),
    FULLTEXT KEY (information),
    UNIQUE KEY (id_card)
);

2.使用ALTER TABLE命令去增加索引

ALTER TABLE table_name ADD INDEX index_name (column_list);

3.使用CREATE INDEX命令创建

CREATE INDEX index_name ON table_name (column_list);

索引的删除

删除索引

根据索引名删除普通索引、唯一索引、全文索引：alter table 表名 drop KEY 索引名

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

删除主键索引：alter table 表名 drop primary key（因为主键只有一个）。这里值得注意的是，如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引）：

什么情况造成索引失效以及怎么解决的

造成索引失效的原因是因为对表进行了全部扫描，我们需要避免全表扫描
1.列与列对比
某个表中，有两列（id和c_id）都建了单独索引，下面这种查询条件不会走索引 设置默认值

select * from test where id=c_id;

2.存在NULL值条件 设置默认值 

当表中存在null值时需要进行默认值设置
select * from test where id is not null;

4.NOT条件
我们知道建立索引时，给每一个索引列建立一个条目，如果查询条件为等值或范围查询时，索引可以根据查询条件去找对应的条目。反过来当查询条件为非时，索引定位就困难了，执行计划此时可能更倾向于全表扫描，这类的查询条件有：<>、NOT、in、not exists

select * from test where id<>500;
select * from test where id in (1,2,3,4,5);
select * from test where not in (6,7,8,9,0);
select * from test where not exists (select 1 from test_02 where test_02.id=test.id);

5.LIKE通配符 模糊搜索
当使用模糊搜索时，尽量采用后置的通配符，例如：name||’%’，因为走索引时，其会从前去匹配索引列，这时候是可以找到的，如果采用前匹配，那么查索引就会很麻烦，比如查询所有姓张的人，就可以去搜索’张%’。相反如果你查询所有叫‘明’的人，那么只能是%明。这时候索引如何定位呢？前匹配的情况下，执行计划会更倾向于选择全表扫描。后匹配可以走INDEX RANGE SCAN。
所以业务设计的时候，尽量考虑到模糊搜索的问题，要更多的使用后置通配符

select * from test where name like 张||'%';

6.条件上包括函数

select * from test where upper(name)='SUNYANG';

select * from test where name=upper('sunyang');
--INDEX RANGE SCAN

7.数据类型的转换
当查询条件存在隐式转换时，索引会失效。比如在数据库里id存的number类型，但是在查询时，却用了下面的形式：

select * from sunyang where id='123';

8.谓词运算

select * from sunyang where id/2=:type_id;
修改后
select * from sunyang where id=:type_id*2;

索引有哪几种类型？

  • 主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

  • 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

    • 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引

    • 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

  • 普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

    • 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引

    • 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

  • 全文索引： 是目前搜索引擎使用的一种关键技术。

    • 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

百万级别或以上的数据如何删除

关于索引：由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

1.所以我们想要删除百万数据的时候可以先删除索引（此时大概耗时三分多钟）
2.然后删除其中无用数据（此过程需要不到两分钟）
3.删除完成后重新创建索引(此时数据较少了)创建索引也非常快，约十分钟左右。
4.与之前的直接删除绝对是要快速很多，更别说万一删除中断,一切删除会回滚。那更是坑了。

B树和B+树的区别

在B树中，你可以将键和值存放在内部节点和叶子节点；但在B+树中，内部节点都是键，没有值，叶子节点同时存放键和值。
B+树的叶子节点有一条链相连，而B树的叶子节点各自独立。
图片说明 

使用B树的好处
B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

使用B+树的好处
由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间

事务

什么是事务

事务是不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行结果必须使数据库从一致性状态变成另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行

事物的四大特性(ACID)介绍一下?

图片说明
原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

脏读 幻读 不可重复度

脏读:

  • 是指在一个事务处理过程中读取了另外一个未提交的事物中数据，导致两次查询结果不一致

不可重复读:

  • 是指在一个事务处理过程中读取了另外一个事务修改并提交的数据，导致两次查询结果不一致

幻读

  • select 某记录是否存在，不存在，准备插入此记录，但是执行insert时发现此记录已存在，无法插入。或者查询时明明数据不存在，删除时却发现删除成功，仿佛出现了幻觉。
    为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

隔离级别 脏读 不可重复读 幻影读
READ-UNCOMMITTED 脏读 不可重复读 幻影读
READ-COMMITTED 不可重复读 幻影读
REPEATABLE-READ 幻影读
SERIALIZABLE

锁

对MySQL的锁了解吗

当数据库有并发事务的时候，可能会产生数据的不一致，这时候需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

就像酒店的房间，如果大家随意进出，就会出现多人抢夺同一个房间的情况，而在房间上装上锁，申请到钥匙的人才可以入住并且将房间锁起来，其他人只有等他使用完毕才可以再次使用。

按照锁的粒度分数据库锁 行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎

  • 行级锁 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
    特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

  • 表级锁
    表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
    特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

  • 页级锁 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
    特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般