MySQL复习总结

1.char 和 varchar的区别？

        char的长度是不可变的，排除掉所存储的字符，其余位置使用空格进行填充，检索char类型的数据时会对尾部的空格进行删除，一个英文字符占用一个字节，一个汉字占用两个字节；

        varchar的长度是可变的，varchar的长度变成存储内容的长度，一个英文字符占用两个字节，一个汉字也占用两个字节；

2.什么是三大范式？

        第一大范式：列的原子性：确保每列都保持原子性；

        第二大范式：数据的唯一性：必须要有标识列（主键自增列），确保表中的每列都和主键相关；

        第三大范式：依赖性：在第二范式的基础上，表添加的关联列必须是主表的主键列；

3.什么是索引

        索引是对数据库表中一列或者多列的值进行排序的数据结构，用于快速访问数据库表中的特定信息；

4.索引的几种类型

        物理结构分：

                        聚集索引：索引的键值和逻辑顺序跟表中相应行的物理顺序一直，即每张表中只可以有一个聚集索引，也就是常说的主键索引；

                        非聚集索引：就是逻辑顺序跟物理顺序不一致；

       

        应用上分：

                        普通索引：MySQL中的基本索引类型，没有什么限制，纯粹为了提高查询效率；

                        唯一索引：索引列中的值必须是唯一的，允许列中的属性有空值；

                        主键索引：也叫聚集索引，不允许有空值，由数据库帮我们创建；

                        组合索引：组合表中多个字段创建的索引，遵循最左前缀匹配规则；

                        全文索引：MyISAM引擎上才能使用，只支持Char、VarChar、Text类型字段上使用；

5.什么样的列适合建索引？

        经常作为查询的列应该创建索引；

        经常需要ORDER BY（排序）、GROUP BY（分组）、DISTINCT（消重）、和UNION（消重）等操作的字段；

6.索引的优缺点

      优点：

                创建索引可以大大的提高系统的性能，提高数据的检索；

                通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

                可以加速表和表之间的连接，在实现数据完整的参考性方面特别有意义；

                使用索引可以查询的过程中，使用优化隐藏器，提高系统的性能；

        缺点：

                创建和维护索引需要耗费时间，时间随着数据量的增加而增加，这样就降低了数据的维护速度；

                索引需要占用物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间；

7.什么是索引下推？

        如果存在某些被索引列的判断条件时，MySQL服务器将这一部分判断条件传给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器；

        索引下推好处：可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数；

8.有哪些常见的存储引擎？

        MyISAM        InnoDB

9.MyISAM和InnoDB的区别？

        InnoDB支持事物，MyISAM不支持；

        InnoDB支持外键，MyISAM不支持，因此将一个含有外键的InnoDB表转成MyISAM表会失败；

        InnoDB和MyISAM都支持B+Tree数据结构的索引，但InnoDB是聚集索引，MyISAM是非聚集索引；

        InnoDB支持表、行（默认）级锁，而MyISAM支持表级锁；

        InnoDB必须有唯一索引，如果没有指定，就会自动寻找一个隐藏列Row_id来默认承当主键，而MyISAM可以没有主键；

10.InnoDB四大特性

        插入缓冲

        二次写

        自适应哈希索引

        预读

11.InnoDB为什么推荐使用主键自增

        自增ID可以保证每次插入时B+树索引都是从右边开始扩展的，如果使用字符串索引和随机主键，会使得数据随机插入，效率比较差；

12.什么是数据库事务

        把所有命令作为一个整体一起像系统提交或撤销请求，也就是这一组命令要么都执行，要么都不执行；因此事务是一个不可被分割的工作逻辑单元；

13.什么是事务的四大特性

        原子性：一个事务中的所有操作，要么全部完成，要么全部不完成（回滚）；

        一致性：在事务开始之前和执行之后，数据库的完整性没有被破坏；

        隔离性：防止多个事务并发执行时由于交叉执行而导致数据的不一致；

        持久性：即事务完成之后，对数据的修改是永久性的，即使系统故障也不会数据丢失；

14.事务的并发问题

        脏读、幻读、不可重复读；

                脏读：一个事务读取另一个事务尚未提交的数据；解决方法：读已提交隔离级别！！！

                幻读：一个事务两次读取的数据量不一致；解决方法：可串行化隔离级别！！！

                不可重复读：一个事务中两次读取的数据的内容不一致；解决方法：可重复读隔离级别！！！

               

               /**不可重复读侧重于修改，幻读侧重于新增和修改**/

15.MySQL的四种隔离级别

        读未提交、读已提交、可重复读、可串行化；

        

 /**串行化的隔离级别最高，读未提交隔离级别最低，级别越高，执行的效率越低**/

16.数据库锁的作用以及有哪些锁

        锁的粒度划分：表锁、行锁、以及页锁；

                行级锁：是锁的粒度最小的一种锁，表示只对当前操作的行进行加锁；

/**        行级锁开销大，加锁慢，且会出现死锁。但锁定粒度最小，发生锁冲突的概率最低，并发度也最高        **/

                表级锁：是锁的粒度最大的锁，表示对当前操作的整张表进行加锁，操作简单，资源消耗较少，被大部分MySQL引擎支持；

                页级锁：粒度介于行锁和表锁之间，一次锁定相邻的一组记录；

/**        开销和加锁时间介于表锁和行锁之间，会出现死锁        **/

        锁的性质划分：

                共享锁：S锁，又称读锁，用于所有的只读数据操作；

/**        S锁并非独占，允许多个事务并发对同一资源进行加锁，但加S锁的同时不允许加X锁，即资源不能被修改，S锁通常读取完之后立即释放，无需等待事务结束；

共享锁写法：LOCK TABLE table_name [ AS alias_name ] READ        **/

                排他锁：X锁，又称写锁，表示对数据进行写操作；

/**        X锁只允许一个事务对同一资源加锁，且直到事务结束才会放锁，其他任何事务必须等到X锁释放才能对该页进行访问 ；

排他锁写法：LOCK TABLE table_name [AS alias_name] [ LOW_PRIORITY ] WRITE  **/

                更新锁：U锁，用来预定 要对资源进行施加X锁，允许其他事务读，但不允许在施加U锁或X锁；

/**        当被读取的页将要被更新时，U锁一直到事务结束才能被释放        **/

                

        

        主观上分：

                乐观锁：是一种乐观思想，认为资源是不会被修改的，所以不加锁进行读取数据，只有当更新时给数据库增加一列version版本号列，判断所读资源是否被修改，如果数据被修改了则进行自旋操作，没被修改则直接执行操作；

        

                悲观锁：是一种悲观的思想，每次读取数据都会认为被其他事务所修改，所以每次操作都会进行加锁操作；

17.什么是快照读和当前读

        快照读：读取的是快照数据，不加锁的简单的select都属于快照读；

        

        当前读：读的是最新数据，而不是历史数据，加锁的select，或者对数据的增删改查都会进行当前读；

18.什么是MVCC以及实现

        就是“多版本并发控制”，可以做到读写互相不阻塞，主要用于解决不可重复读和幻读问题时提高并发效率

        

        实现：简单来说就是保存数据的历史版本，可以通过比较版本号决定是否显示出来；

19.MySQL有哪些常见的日志

        重做日志：物理日志

                作用：确保事务的持久性；

        回滚日志：逻辑日志

                作用：保证数据的原子性；

        二进制日志：逻辑日志

                作用：用于主从同步或数据同步中，也可用于数据库时间点的还原；

        错误日志：

                作用：记录MySQL的启动和停止，以及服务器在运行过程中发生的错误的相关信息；

20.什么是主从复制

        用来建立一个与数据库完全一样的数据库环境；

21.主从复制的作用

        读写分离：使数据库能支持更大的开发；

        

        高可用，做数据的热备，作为后背数据库，主数据库服务故障后，可切换到从数据库继续工作，以免数据丢失；

22.主从复制的架构

        一主一从或一主多从：

                主库请求压力非常大时，可以通过配置多个从库来分摊压力，从而达到降低主库的压力，而且主库宕机时，可将一个从库切换为主库继续提供服务；

        

        主主复制：

                两个数据库互为主从，当主库宕机时，从库会复制新的主库上的数据，那么无论主库的角色怎么切换，原来的主库都不会脱离复制环境；

        

        多主一从；

        

        联机复制：

                联机架构只是在一主多从的基础上，在主库和各个从库之间添加了一个二级主库，作用是从来将一级主库的日志推给各个从库，以达到降低主库的推送压力；

23.SQL

        数据操作语言（DML）：INSERT、DELETE、UPDATE、SELECT；

        

        数据定义语言（DDL）：CREATE、DROP、ALETR；

24.常见的聚合函数

        sum（列名）：求和；

        max（列名）：最大值；

        min（列名）：最小值；

        avg（列名）：平均值；

        first（列名）：第一条记录；

        last（列名）：最后一条记录；

        count（列名）：统计记录数（不计算值为null的记录）；count（*）：统计所有！

25.关联查询

        内连接：只返回匹配的行，如Inner、Join、Union Join；

        

        外连接：返回一个表的全集，如Left、Right；

26.Union和Union All的区别

        Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

        

        Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

27.Drop和Delete和Truncate的区别

        Drop：，不支持回滚，删除表的全部或部分数据，速度慢，需要逐行删除；

        

        Delete：，支持回滚，删除表中数据，一行一行删；

        

        Truncate：不支持回滚，只删除数据，不删除表的结构；

28.SQL语句优化

        尽量避免使用子查询；

        

        能用select 列名，绝不用select *；

        

        根据项目需求加索引；

29.MySQL数据库优化

        1.创建索引：对于经常查询的列需要进行创建索引，不加索引的查询是查全表，加了索引则变成索引表，所以会快，但是会导致修改数据的时候降低效率，因为不光要修改当前表的还要修改索引表；

        2.复合索引：

比如有一条语句是这样的: select * from users where area='beijing' and age=22;

如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area.age,salary)、(area.age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减。

；

        3.索引不会包含有null值的列：

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL；

        4.不使用not in 和<>操作符：都会导致索引失效，not in 可以使用not exists代替，<>可以使用 or ；