MySQL数据库知识点

数据库char（定长）为什么比varchar（变长）效率高

因为varchar每次存储都要有额外的计算，得到长度，但是空间占用比char好。

MySQL 中有哪几种锁？

1、表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
2、行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
3、页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

说一下乐观锁和悲观锁？

乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，
但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。
悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，
这样别人想拿这个数据就会阻止，直到这个锁被释放。

如何提高数据库的查询效率

简化sql语句
适当给字段添加索引
将稳定的并经常查询的数据，缓存到redis中
将数据量大查询，可以使用分页查询

SQL 语言包括哪几部分？每部分都有哪些操作关键字？

SQL 语言包括数据定义(DDL)、数据操纵(DML),数据控制，访问权限(DCL)和数据查询（DQL）四个部分。
DDL：Create Table,Alter Table,Drop Table, Craete/Drop Index 等
DML：insert,update,delete,
DCL：grant,revoke
DQL：select

多个模糊查询

SELECT * FROM magazine WHERE CONCAT(IFNULL(字段1,’’),IFNULL(字段2,’’),IFNULL(字段3,’’)) LIKE ‘%关键字%’

你怎么看到为表格定义的所有索引？

索引是通过以下方式为表格定义的：
SHOW INDEX FROM tablename;

索引的底层实现原理和优化

B+树，经过优化的 B+树 主要是在所有的叶子结点中增加了指向下一个叶子节点的指针，因此 InnoDB 建议为大部分表使用默认自增的主键作为主索引。

索引的创建和删除

普通索引，CREATE INDEX indexName ON mytable(字段);创建表的时候直接指定。
删除索引的语法：DROP INDEX [indexName] ON mytable;

什么情况下设置了索引但无法使用

1、以“%”开头的 LIKE 语句，模糊匹配
2、OR 语句前后没有同时使用索引
3、数据类型出现隐式转化（如 varchar 不加单引号的话可能会自动转换为 int型）

简单描述 MySQL 中，索引，主键，唯一索引，联合索引 的区别，对数据库的性能有什么影响（从读写两方面）

索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)，它们包含对数据表里所有记录的引用指针。
普通索引(由关键字 KEY 或 INDEX 定义的索引)的唯一任务是加快对数据的访问速度。
普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，
在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。
主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯 一标识一条记录，使用关键字 PRIMARY KEY 来创建。
索引可以覆盖多个数据列，如像 INDEX(columnA, columnB)索引，这就是联合索引。
索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

MySQL 支持事务吗？

在缺省模式(默认)下，MySQL 是 autocommit 模式的，所有的数据库更新操作都会即时提交，所以在缺省情况下，MySQL 是不支持事务的。
但是如果你的 MySQL 表类型是使用 InnoDB Tables 或 BDB tables 的话，你的
MySQL 就可以使用事务处理,使用 SET AUTOCOMMIT=0 就可以使 MySQL 允许在非 autocommit 模式，
在非 autocommit 模式下，你必须使用 COMMIT 来提交你的更改，或者用 ROLLBACK 来回滚你的更改。

数据库中的事务是什么?

事务特性：
1、原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。
2、一致性：事务的执行使得数据库从一种正确状态转换成另一种正确状态(外部：银行收付账单，内部：原子性)
3、隔离性：在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，
4、持久性：事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

数据库隔离级别（关于spring的事务隔离级别与数据库的一样，也是下面的四个，只不过多了一个default，oracle默认是读已提交，MySQL默认是可重复读）

1.Read uncommitted (读未提交)：最低级别，任何情况都无法保证。（脏读，幻读，不可重复度）
2.Read committed (读已提交)：可避免脏读的发生。（幻读，不可重复读）
3.Repeatable read (可重复读)：可避免脏读、不可重复读的发生。（幻读）
4.Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

脏读：

一个事务读到另外一个事务还没有提交的数据，我们称之为脏读。（进行存款事务时候，还没有存完，允许查询事务）

不可重复读

在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。
这是由于查询时系统中其他事务修改的提交而引起的。
例如：事务B中对某个查询执行两次，当第一次执行完时，事务A对其数据进行了修改。
事务B中再次查询时，数据发生了改变。

幻读：

是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。
那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样.

MySQL 里记录货币用什么字段类型好

NUMERIC 和 DECIMAL 类型被 MySQL 实现为同样的类型，这在 SQL92 标准允许。他们被用于保存值，
该值的准确精度是极其重要的值，例如与金钱有关的数据。当声明一个类是这些类型之一时，精度和规模的能被(并且通常是)指定。

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

一般情况下，我们创建的表的类型是InnoDB，如果新增一条记录（不重启mysql的情况下），这条记录的id是8；但是如果重启（文中提到的）MySQL的话，这条记录的ID是6。因为InnoDB表只把自增主键的最大ID记录到内存中，所以重启数据库或者对表OPTIMIZE操作，都会使最大ID丢失。
但是，如果我们使用表的类型是MylSAM，那么这条记录的ID就是8。因为MylSAM表会把自增主键的最大ID记录到数据文件里面，重启MYSQL后，自增主键的最大ID也不会丢失。
注：如果在这7条记录里面删除的是中间的几个记录（比如删除的是3,4两条记录），重启MySQL数据库后，insert一条记录后，ID都是8。因为内存或者数据库文件存储都是自增主键最大ID

如何通俗地理解三个范式？

第一范式：1NF 是对属性的原子性约束，要求属性具有原子性，不可再分解（一个字段不可有两个值）；
第二范式：2NF 是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性（字段跟主键相关）；
第三范式：3NF 是对字段冗余性的约束，即任何字段不能由其他字段派生出来， 它要求字段没有冗余（字段不能由字段函数派生）。
范式化设计优缺点:
优点: 可以尽量得减少数据冗余，使得更新快，体积小
缺点:对于查询需要多个表进行关联，减少写得效率增加读得效率，更难进行索引优化

实践中如何优化 MySQL，最好是按照以下顺序优化：

1、SQL 语句及索引的优化
2、数据库表结构的优化
3、系统配置的优化
4、硬件的优化

说说对 SQL 语句优化有哪些方法？（选择几条）

1、Where 子句中：where 表之间的连接必须写在其他 Where 条件之前，
那些可以过滤掉最大数量记录的条件必须写在 Where 子句的末尾.HAVING最后。
2、用 EXISTS 替代 IN、用 NOT EXISTS 替代 NOT IN（子表数据大的用in，外表数据大的用exists）。
3、避免在索引列上使用计算 （在 WHERE 子句中，如果索引列是计算或者函数的一部分，DBMS 的优化器将不会使用索引而使用全表扫描）
4、避免在索引列上使用 IS NULL 和 IS NOT NULL
5、对查询进行优化，应尽量避免全表扫描，首先应考虑在where 及order by 涉及的列上建立索引。
6、应尽量避免在where子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
7、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。