mysql数据库方面

mysql数据库方面

1.数据库三大范式
1）每个列都不可以再拆分。
2）在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
2）在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

2.存储引擎
存储引擎是基于表，而不是基于库。
1）5.5默认的是使用InnoDB:支持事务，行锁（适合高并发），支持外键。
应用场景：对事物的完整性，一致性要求比较高，数据除了插入和更新外还有大量的修改和删除。
2）5.5之前默认MySAM:不支持事务（插入速度和读写速度比较快），表锁。
应用场景：如果是以插入和读写为主，只有少量的更新和删除，可以用MySAM。
MEMORY:是基于内存存储，缺陷就是对表的大小有控制。

3.MyISAM 和 InnoDB区别
1）InnoDB支持事务，MyISAM不支持。
2）MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用。
3）InnoDB支持外键，MyISAM不支持。
4）MyISAM支持全文类型索引，而InnoDB不支持全文索引。
5）InnoDB中不保存表的总行数，select count() from table时，InnoDB需要扫描整个表计算有多少行，但MyISAM只需简单读出保存好的总行数即可。注：当count()语句包含where条件时MyISAM也需扫描整个表。
6）对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引。
7）清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表。MyisAM使用delete语句删除后并不会立刻清理磁盘空间，需要定时清理。
8）现在一般都选用InnoDB，主要是MyISAM的全表锁，读写串行问题，并发效率锁表，效率低，MyISAM对于读写密集型应用一般是不会去选用的。
9）应用场景：
MyISAM不支持事务处理等高级功能，但它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
InnoDB用于需要事务处理的应用程序，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

4.索引优缺点
1）优点：
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
2）缺点：
时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率。
空间方面：索引需要占物理空间。

5.索引有哪几种类型
1）主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
2）唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
3）普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
4）全文索引：是目前搜索引擎使用的一种关键技术。

6.索引的设计原则
1）什么表需要建立索引
使用频率较高，数据量比较大的表，我们需要建立索引。
2）对于哪些字段建立索引
出现在where子句的列，或者连接子句中指定的类。
3）使用唯一索引，区分度越高，查询你效率就会越高。
4）索引不是多多益善，索引越多，索引维护的代价越大，索引过多MySql也会犯索引选择困难症。
5）尽可能使用短字段的索引，索引创建之后也是使用磁盘来存储的，短字段会提升I/O效率。

7.创建索引原则
1）较频繁作为查询条件的字段才去创建索引
2） 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询。
3）更新频繁字段不适合创建索引
4）若是不能有效区分数据的列不适合做索引列
5）定义有外键的数据列一定要建立索引。
6）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
7）对于定义为text、image和bit的数据类型的列不要建立索引。

8.SQL优化步骤
1）查看sql执行频率：（分析出数据库插入和查询的频次）
show global status like ‘Com______’; 可以获取到整个数据库全局的sql执行频率
2）定位低效率执行SQL
慢查询日志：用此功能记录sql执行时间超过指定时间的sql语句，查询结束后才能发现执行效率低的sql。
show processlist:查询实时的sql语句的执行情况。
3）通过EXPLAIN分析执行计划
在每条sql执行时，在语句前面加一个explain，我们会获取到一条数据，包含一些字段：select_type查询类型，输出的表，索引字段等等。

9.sql优化
1）大批量出入数据
主键顺序插入，因为InnoDB类型的表是按主键的顺序保存的。
关闭唯一性校验，导完再开，可以提高导入效率。
手动提交事务，导入数据前现改为手动提交，导完后再打开自动提交.
2)优化insert语句
一张表中多个insert直接改为一条sql语句，一次加完.
在事务中插入。
按主键顺序插入.。
3）group by 优化
groupBy底层对我们语句进行了排序，我们要做的就是在后拼接orderby NUll，不排序，然后就也要创建索引。
4）子查询优化
用多表连接替代子查询。
5）or优化
用union替换or。
6）limit优化
select * from t ,(select * from tb_item order by id limit 2000,10) a where t.id = a. id。
执行分页查询时，可以先把要分页的那段数据的id查询出来，存在表a中，然后根据两个表的id获取需要的数据。

10.应用优化
1）使用数据库连接池，对于访问数据库来讲，建立连接的代价是非常昂贵的，因为我们频繁的创建关闭连接是比较耗费资源的，所以我们用数据库连接池，提高访问的性能。
2）减少对sql的访问，能一次去获取的数据就不要分两次。
3）搭建MySQL集群，采用分布式数据库架构，负载均衡，主从复制，实现读写分离，Master负责增删改，而其他服务器负责查询。

11.事务（脏读，不可重复读，幻读）
1）脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
2）不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
3）幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

12.事务隔离级别
1）READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
2）READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
3）EPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
4）SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
Mysql 默认采用的 REPEATABLE_READ（可重复读）隔离级别。

按照锁的粒度划分数据库锁
1）行级锁
行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
2）表级锁
表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
3）页级锁
页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

按照锁的类别划分数据库锁
1）共享锁: 又叫做读锁。 当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个
2）排他锁: 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

整理借鉴了很多大佬写的，在此无法一一说明，这只是个人用来查漏补缺的文章，如果对你有帮助我很高兴。