mysql开发相关

ACID是事务的四个基本特性
原子性:一个事务中所有操作全部完成或失败
一致性:事务开始和结束之后数据完整性没有被破坏
隔离性:允许多个事务同时对数据库修改和读写
持久性:事务结束之后,修改是永久的不会丢失

事务的并发控制可能产生的问题(四种异常情况)
幻读:一个事务第二次查出现第一次没有的结果
非重复读:一个事务重复读两次得到不同的结果
脏读:一个事务读取到另一个事务没有提交的修改
丢失修改:并发写入造成其中一些修改丢失

解决以上4种异常,定义了4种事务隔离级别
读未提交:别的事务可以读取到未提交改变
读已提交:只能读取已经提交的数据
可重复读:同一个事务先后查询结果一样(innodb默认实现可重复读级别)
串行化:事务完全串行化的执行,隔离级别最高,执行效率最低

如何解决高并发场景下的插入重复
高并发场景下,写入数据库会有数据重复问题
使用数据库的唯一索引
使用队列异步写入
使用redis实现分布式锁

乐观锁和悲观锁
悲观锁是先获取锁再进行操作。一锁二查三更新 (select for update)
乐观锁先修改,更新的时候发现数据已经变了就回滚(check and set) 一般通过版本号或时间戳实现
使需要根据响应速度,冲突频率,重试代价来判断使用哪一种

常用字段,含义和区别
字符串 char/varchar/text
数值 tinyint/smallint/bigint/int/float/double length 数据库可见长度
日期和时间 date/datetime/timestamp

常用数据库引擎之间区别
innodb/myisam
myisam不支持事务/innodb支持事务
myisam不支持外键/innodb支持外键
myisam只支持表锁/innodb支持行锁和表锁
myisam支持全文索引/innodb不支持全文索引

mysql索引
索引的原理、类型、结构
索引是数据表中一个或者多个列进行排序的数据结构
索引能够大幅提升检索速度
创建、更新索引本身也会消耗空间和时间

B-Tree
查找结构进化史
线性查找:一个个找;实现简单;太慢
二分查找:有序;简单;要求是有序的,插入特别慢
HASH:查询快;占用空间;不太适合存储大规模数据
二叉树:插入和查询很快;无法存大规模数据,复杂度退化
平衡树:解决bst退化问题,树是平衡的;节点非常多的时候,依然树高很高
多路查找树:一个父亲多个孩子节点(度/阶);节点过多树高不会特别深
多路平衡查找树:B-Tree(每个节点最多m(m>=2)个孩子,称为m阶或者度) 阶的大小是根据磁盘块的4k大小来确定
叶节点具有相同的深度
节点中的数据key从左到右是递增的
B+树
只在叶子节点带有指向记录的指针(可以增加树的度) 根据磁盘块大小来确定一个节点来存储多个阶
叶子节点通过指针相连。实现范围查询

mysql索引类型
普通索引 (create index)
唯一索引,索引列的值必须唯一(create unique index)
多列索引 B+树的key是由多个列组成的
主键索引(primary key),一个表只能有一个
全文索引(fulltext index),innodb不支持, 倒排索引实现 es

什么时候创建索引
建表的时候需要根据查询需求来创建索引
经常用作查询条件的字段(where条件)
经常用作表连接的字段
经常出现在order by，group by之后的字段

创建索引有哪些需要注意的 最佳实践
非空字段 not null，mysql很难对空值做查询优化 建表规范要求索引字段有默认值
区分度高,离散度大,作为索引的字段值尽量不要有大量相同值
索引的长度不要太长(比较耗费时间)

索引什么时候失效(模糊匹配,类型隐转,最左匹配) 索引失效是因为key没法比较
以%开头的LIKE语句,模糊搜索
出现隐式类型转换(在python这种动态语言查询中需要注意)
没有满足最左前缀原则,没法直接比较

什么是聚集索引和非聚集索引(辅助索引)
区别是B+Tree叶节点存的是指针还是数据记录
myisam索引和数据分离,使用的是非聚集索引
innodb数据文件是索引文件,主键索引是聚集索引

如何排查和消除慢查询
慢查询通常是缺少索引,索引不合理或者业务代码实现导致
slow_query_log_file 开启并且查询慢查询日志
通过explain排查索引问题
调整数据修改索引;业务代码层限制不合理访问

SQL语句编写
内链接(inner join) 两个表都存在匹配时,才会返回匹配行
将左表和右表能够关联起来的数据连接后返回
类似于求两个表的"交集"
select * from A inner join B on a.id=b.id;

外连接(left/right join) 返回一个表的行,即使另一个没有匹配
左连接返回左表中所有记录,即使右表中没有匹配的记录
右连接返回右表中所有记录,即使左表中没有匹配的记录
没有匹配的字段会设置成NULL
select * from A left join B on A.id=B.id;

全连接(full join) 只要某一个表存在匹配就返回
oracle里面有full join,但是在mysql中没有full join。我们可以使用union来达到目的。
select * from t1 left join t2 on t1.id = t2.id union select * from t1 right join t2 on t1.id = t2.id;

MyISAM和InnoDB搜索引擎的特点
存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。所以存储引擎也可以称为表类型。

MyISAM：
特点：
（1）不支持事务：MyISAM存储引擎不支持事务，所以对事务有要求的业务场景不能使用
（2）表级锁定：其锁定机制是表级索引，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能
（3）读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读
（4）只会缓存索引：MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据
适用场景：
（1）不需要事务支持（不支持）
（2）并发相对较低（锁定机制问题）
（3）数据修改相对较少（阻塞问题）
（4）以读为主
（5）数据一致性要求不是非常高
InnoDB：
特点：
（1）具有较好的事务支持：支持4个事务隔离级别，支持多版本读
（2）行级锁定：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响
（3）读写阻塞与事务隔离级别相关
（4）具有非常高效的缓存特性：能缓存索引，也能缓存数据
（5）整个表和主键以Cluster方式存储，组成一颗平衡树
（6）所有Secondary Index都会保存主键信息
适用场景：
（1）需要事务支持（具有较好的事务特性）
（2）行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成
（3）数据更新较为频繁的场景
（4）数据一致性要求较高
（5）硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘 IO

char 和varchar字符串类型的区别？
char类型：定长，存入字符长度大于设置长度时报错，存入字符长度小于设置长度时，会用空格填充，达到设置字符长度，简单粗暴，浪费空间，存取速度快。
Varchar类型：varchar类型存储数据的真实内容，不会用空格填充，会在真实数据前加1-2Bytes的前缀，该前缀用来表示真实数据的bytes字节数，变长，精准，节省空间，存取速度慢。
虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡。

foreign key外键关联（一对多）实例。
create table press(
id int primary key auto_increment,
name varchar(20)
);

create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
foreign key(press_id) references press(id)
on delete cascade
on update cascade

);

mysql索引相关介绍。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

联合索引命中规则是：最左匹配规则，如下联合索引（姓名，年龄，性别）：

诸如select * from user where name= ‘zzz’ and sex=’male’ and age=18查询语句，当对name、sex、age分别建立列索引和建立（name、sex、age）组合索引时，查询的结果是不一样的，组合索引效率高于多个列索引，因为在执行多个列索引的时候，mysql只会选择其中一个效率最高的。但是通过组合索引就直接锁定那一条信息了。由于组合索引的最左匹配原则，上述组合索引相当于分别建立（name），（name、sex）,（name、sex、age）这样的三个索引，只有查询条件与这三个顺序相一致的才会用到组合索引。

MySQL在以下操作场景下会使用索引
1) 快速查找符合where条件的记录
2) 快速确定候选集。若where条件使用了多个索引字段，则MySQL会优先使用能使候选记录集规模最小的那个索引，以便尽快淘汰不符合条件的记录。
3) 如果表中存在几个字段构成的联合索引，则查找记录时，这个联合索引的最左前缀匹配字段也会被自动作为索引来加速查找。
例如，若为某表创建了3个字段(c1, c2, c3)构成的联合索引，则(c1), (c1, c2), (c1, c2, c3)均会作为索引，(c2, c3)就不会被作为索引，而(c1, c3)其实只利用到c1索引。
4) 多表做join操作时会使用索引（如果参与join的字段在这些表中均建立了索引的话）。
5) 若某字段已建立索引，求该字段的min()或max()时，MySQL会使用索引。
6) 对建立了索引的字段做sort或group操作时，MySQL会使用索引。

mysql中随着数据量的增大，查询速度会越来越慢，请给出简易的优化方案。
1.合理的添加索引（mysql默认只会btree类型索引）；
mysql常见的索引：
普通索引INDEX：加速查找

唯一索引：
-主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
-唯一索引UNIQUE:加速查找+约束（不能重复）

联合索引：
-PRIMARY KEY(id,name):联合主键索引
-UNIQUE(id,name):联合唯一索引
-INDEX(id,name):联合普通索引
2、避免使用select *
3、创建表时尽量用char代替varchar
4、表的字段顺序，固定长度的优先
5、组合索引代替多个单列索引
6、使用连接（join）代替子查询
7、使用explain优化神器

关系型数据库中，表与表之间有左连接、内连接、外连接，分别指出他们的含义及区别？
1、 交叉连接：不使用任何匹配条件生成笛卡尔积
select * from employee,department

2、内连接：只连接匹配的行
selcet employee.name,employee.age,department.name from employee inner join department on employee.dep_id = department.id
3、外连接之左连接：优先显示左表全部内容
selcet employee.name,employee.age,department.name from employee left join department on employee.dep_id = department.id
4、外连接之右连接：优先显示右表全部内容
selcet employee.name,employee.age,department.name from employee right join department on employee.dep_id = department.id
5、全外连接：显示左右两个表全部记录(mysql不支持full join)，实现方式如下：
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id

1.关系型数据库的三范式
范式就是规范,就是关系型数据库在设计表时，要遵循的三个规范。要想满足第二范式必须先满足第一范式，要满足第三范式必须先满足第二范式。
第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。列数据的不可分割
二范式（2NF）要求数据库表中的每个行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。(主键)
满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。(外键)
反三范式,有的时候为了效率，可以设置重复或者可以推导出的字段.订单（总价）和订单项（单价）

2.事务的四大特征
事务是并发控制的单位，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。
一个转账必须 A账号扣钱成功，B账号加钱成功，才算正真的转账成功。
事务必须满足四大特征:原子性,一致性,隔离性持久性/持续性
原子性：表示事务内操作不可分割。要么都成功、要么都是失败.
一致性：要么都成功、要么都是失败.后面的失败了要对前面的操作进行回滚。
隔离性：一个事务开始后，不能后其他事务干扰。
持久性/持续性：表示事务开始了，就不能终止。

3.mysql数据库最大连接数
100
为什么需要最大连接数?特定服务器上面的数据库只能支持一定数目同时连接,这时候我们一般都会设置最大连接数(最多同时服务多少连接)。
在数据库安装时都会有一个默认的最大连接数为100

4.mysql的分页语句
为什么需要分页?在很多数据是,不可能完全显示数据.进行分段显示.Mysql是使用关键字limit来进行分页的 limit offset,size 表示从多少索引去多少位.

5.触发器的使用场景？
触发器，需要有触发条件，当条件满足以后做什么操作。

6.存储过程的优点
1、存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次，因此使用存储过程可以大大提高数据库执行速度。
2、通常，复杂的业务逻辑需要多条 SQL 语句。这些语句要分别地从客户机发送到服务器，当客户机和服务器之间的操作很多时，将产生大量的网络传输。如果将这些操作放在一个存储过程中，那么客户机和服务器之间的网络传输就会大大减少，降低了网络负载。
3、存储过程创建一次便可以重复使用，从而可以减少数据库开发人员的工作量。
4、安全性高，存储过程可以屏蔽对底层数据库对象的直接访问，使用 EXECUTE 权限调用存储过程，无需拥有访问底层数据库对象的显式权限。

7.jdbc调用存储过程
加载驱动
获取连接
设置参数
执行
释放连接

8.简单说一下你对jdbc的理解
9.写一个jdbc的访问oracle的列子
10.jdbc中preparedStatement比Statement的好处

11.数据库连接池的作用
1、限定数据库的个数，不会导致由于数据库连接过多导致系统运行缓慢或崩溃
2、数据库连接不需要每次都去创建或销毁，节约了资源
3、数据库连接不需要每次都去创建，响应时间更快。

12.ORM是什么？ORM框架是什么？
对象关系映射（Object Relational Mapping，简称ORM）模式是一种为了解决面向对象与关系数据库存在的互不匹配的现象的技术。可以简单的方案是采用硬编码方式(jdbc操作sql方式)，为每一种可能的数据库访问操作提供单独的方法。这种方法存在很多缺陷，使用
使用ORM框架(为了解决解决面向对象与关系数据库存在的互不匹配的现象的框架)来解决.
Hibernate,ibatis(mybatis),

13.ibatis和hibernate有什么不同
14.hibernate对象状态及其转换

15.hibernate的缓存
Hibernate中的缓存分一级缓存和二级缓存。
一级缓存就是Session级别的缓存，在事务范围内有效是,内置的不能被卸载。二级缓存是SesionFactory级别的缓存，从应用启动到应用结束有效。是可选的，默认没有二级缓存，需要手动开启。
保存数据库后，在内存中保存一份，如果更新了数据库就要同步更新。
什么样的数据适合存放到第二级缓存中？ 　　
1)很少被修改的数据 　帖子的最后回复时间　
2)经常被查询的数据 电商的地点
2) 不是很重要的数据，允许出现偶尔并发的数据 　　
3) 不会被并发访问的数据 　　
4) 常量数据
扩展：hibernate的二级缓存默认是不支持分布式缓存的。使用memcahe,redis等中央缓存来代替二级缓存。

16.数据库优化方面的事情
定位：查找、定位慢查询
优化手段：
a)创建索引:创建合适的索引，我们就可以现在索引中查询，查询到以后直接找对应的记录。
b)分表：当一张表的数据比较多或者一张表的某些字段的值比较多并且很少使用时，采用水平分表和垂直分表来优化
c)读写分离：当一台服务器不能满足需求时，采用读写分离的方式进行集群。
d)缓存:使用redis来进行缓存

17.如果查询和定位慢查询
在项目自验项目转测试之前，在启动mysql数据库时开启慢查询，并且把执行慢的语句写到日志中，在运行一定时间后。通过查看日志找到慢查询语句。
要找出项目中的慢Sql时
1、关闭数据库服务器(关闭服务)
2、把慢查询记录到日志中
3、设置慢查询时间
4、找出日志中的慢查询SQL 使用explain 慢查询语句，来详细分析语句的问题.

18.数据库优化之数据库表设计遵循范式
三范式

19.选择合适的数据库引擎
MyISAM存储引擎
如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. 比如 bbs 中的 发帖表，回复表.
INNODB存储引擎:
对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表.
Memory 存储
我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快.
问 MyISAM 和 INNODB的区别(主要)
1. 事务安全 myisam不支持事务而innodb支持
2. 查询和添加速度 myisam不用支持事务就不用考虑同步锁，查找和添加和添加的速度快
3. 支持全文索引 myisam支持innodb不支持
4. 锁机制 myisam支持表锁而innodb支持行锁(事务)
5. 外键 MyISAM 不支持外键， INNODB支持外键. (通常不设置外键，通常是在程序中保证数据的一致)

20.选择合适的索引
索引（Index）是帮助DBMS高效获取数据的数据结构。
分类：普通索引/唯一索引/主键索引/全文索引
普通索引:允许重复的值出现
唯一索引:除了不能有重复的记录外，其它和普通索引一样(用户名、用户身份证、email,tel)
主键索引：是随着设定主键而创建的，也就是把某个列设为主键的时候，数据库就会給改列创建索引。这就是主键索引.唯一且没有null值
全文索引:用来对表中的文本域(char，varchar，text)进行索引， 全文索引针对MyIsam
explain select * from articles where match(title,body) against(‘database’);【会使用全文索引】

21.使用索引的一些技巧
索引弊端
1.占用磁盘空间。
2.对dml(插入、修改、删除)操作有影响，变慢。
使用场景：
a: 肯定在where条件经常使用,如果不做查询就没有意义
b: 该字段的内容不是唯一的几个值(sex)
c: 字段内容不是频繁变化.

具体技巧：
1.对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引。
alter table dept add index my_ind (dname,loc); // dname 左边的列,loc就是右边的列
explain select * from dept where dname=‘aaa‘\G 会使用到索引
explain select * from dept where loc=‘aaa‘\G 就不会使用到索引
2. 对于使用like的查询，查询如果是’%aaa’不会使用到索引而‘aaa%’会使用到索引。
explain select * from dept where dname like ‘%aaa‘\G不能使用索引
explain select * from dept where dname like ‘aaa%‘\G使用索引.
所以在like查询时，‘关键字’的最前面不能使用 % 或者 _这样的字符.，如果一定要前面有变化的值，则考虑使用 全文索引->sphinx.
3.如果条件中有or，有条件没有使用索引,即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段,都必须单独使用时能使用索引.
4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。
expain select * from dept where dname=’111’;
expain select * from dept where dname=111;（数值自动转字符串）
expain select * from dept where dname=qqq;报错
也就是，如果列是字符串类型，无论是不是字符串数字就一定要用 ‘’ 把它包括起来.
5.如果mysql估计使用全表扫描要比使用索引快，则不使用索引。 表里面只有一条记录

22.数据库优化之分表
分表分为水平(按行)分表和垂直(按列)分表

根据经验，Mysql表数据一般达到百万级别，查询效率会很低，容易造成表锁，甚至堆积很多连接，直接挂掉；水平分表能够很大程度较少这些压力。
按行数据进行分表。

如果一张表中某个字段值非常多(长文本、二进制等)，而且只有在很少的情况下会查询。这时候就可以把字段多个单独放到一个表，通过外键关联起来。
考试详情，一般我们只关注分数，不关注详情。
水平分表策略：
1.按时间分表
这种分表方式有一定的局限性，当数据有较强的实效性，如微博发送记录、微信消息记录等，这种数据很少有用户会查询几个月前的数据，如就可以按月分表。
2.按区间范围分表
一般在有严格的自增id需求上，如按照user_id水平分表：
table_1 user_id从1~100w
table_2 user_id从101~200w
table_3 user_id从201~300w
3.hash分表*****
通过一个原始目标的ID或者名称通过一定的hash算法计算出数据存储表的表名，然后访问相应的表。

23.数据库的读写分离
主从同步
数据库最终会把数据持久化到磁盘，如果集群必须确保每个数据库服务器的数据是一直的。能改变数据库数据的操作都往主数据库去写，而其他的数据库从主数据库上同步数据。
读写分离
使用负载均衡来实现写的操作都往主数据去，而读的操作往从服务器去。

24.数据库优化之缓存
在持久层(dao)和数据库(db)之间添加一个缓存层，如果用户访问的数据已经缓存起来时，在用户访问时直接从缓存中获取，不用访问数据库。而缓存是在操作内存级，访问速度快。

作用：减少数据库服务器压力，减少访问时间。

25.sql语句优化小技巧
DDL优化:
1 、通过禁用索引来提供导入数据性能 。 这个操作主要针对有数据库的表，追加数据
//去除键
alter table test3 DISABLE keys;
//批量插入数据
insert into test3 select * from test;
//恢复键
alter table test3 ENABLE keys;
2、 关闭唯一校验
set unique_checks=0 关闭
set unique_checks=1 开启
3、修改事务提交方式(导入)（变多次提交为一次）
set autocommit=0 关闭
//批量插入
set autocommit=1 开启

DML优化（变多次提交为一次）
insert into test values(1,2);
insert into test values(1,3);
insert into test values(1,4);
//合并多条为一条
insert into test values(1,2),(1,3),(1,4)

DQL优化
Order by优化
1、多用索引排序
2、普通结果排序（非索引排序）Filesort
group by优化
是使用order by null,取消默认排序
子查询优化
在客户列表找到不在支付列表的客户
#在客户列表找到不在“支付列表”的客户 , 查询没买过东西的客户
explain
select * from customer where customer_id not in (select DISTINCT customer_id from payment); #子查询 -- 这种是基于func外链

explain
select * from customer c left join payment p on(c.customer_id=p.customer_id) where p.customer_id is null -- 这种是基于“索引”外链
Or优化
在两个独立索引上使用or的性能优于
1、 or两边都是用索引字段做判断，性能好！！
2、 or两边，有一边不用，性能差
3、 如果employee表的name和email这两列是一个复合索引，但是如果是 :name=‘A‘ OR email=‘B‘ 这种方式，不会用到索引！
limit优化
select film_id,description from film order by title limit 50,5;
select a.film_id,a.description from film a inner join (select film_id from film order by title limit 50,5)b on a.film_id=b.film_id

26.批量插入几百万条数据
1、变多次提交为一次
3、使用批量操作
省出的时间可观。
像这样的批量插入操作能不使用代码操作就不使用，可以使用存储过程来实现。

mysql开发相关

标签：memory   redis   mysql索引   加速度   交叉连接   user   角度   原则   建立