MySQL面试
优化查询
1.使用索引
尽量避免全表扫描，在经常用来搜索的字段上建立索引。
2.优化SQL语句
通过EXPLAIN来查看SQL语句执行效果，explain select * from
避免使用select * from ，用具体的字段代表*
查询尽可能使用limit，减少返回的行数
避免在where子句中使用函数、运算，否则会放弃索引而使用全表扫描。
尽量避免使用or，or可能会导致放弃索引，可以使用union all
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
3 垂直拆分
按列进行拆分，如果表中一些列常用，而另外一些不常用，就可以将他们拆分开来。
水平拆分：
一个表中数据量特别多时，将该表水平地拆分多个表。
4 硬件优化
选择多核和主频高的 CPU； 使用更大的内存，将更多内存分配给MySQL
5 使用查询缓存。
更新不频繁的表。
如果访问量特别大（自己项目出现大数据量）
除了上面的优化，还可以主从复制、读写分离、负载均衡
通过设置主从数据库实现读写分离，主数据库负责“写操作”，从数据库负责“读操作”，根据压力情况，从数据库可以部署多个提高“读”的速度，借此来提高系统总体的性能。
mysql 主(称master)从(称slave)复制的原理：
(1)master 将数据改变记录到二进制日志(binary log)中,也即是配置文件log-bin 指定
的文件(这些记录叫做二进制日志事件，binary log events)
PS：从图中可以看出，Slave 服务器中有一个I/O 线程(I/O Thread)在不停地监听Master
的二进制日志(Binary Log)是否有更新：如果没有它会睡眠等待Master 产生新的日志事件；
如果有新的日志事件(Log Events)，则会将其拷贝至Slave 服务器中的中继日志(Relay
Log)。
(2).slave 将master 的二进制日志事件（binary log events）拷贝到它的中继日志(relay
log)
(3).slave 重做中继日志中的事件,将Master 上的改变反映到它自己的数据库中。，所
以两端的数据是完全一样的。
数据库三级范式
第一范式：确保每一列的原子性，字段不可再分。
第二范式：满足第一范式。非主键字段依赖于主键字段。一个表只能说明一个事物。
表:学号, 姓名, 年龄, 课程名称, 成绩, 学分;
这个表明显说明了两个事物:学生信息, 课程信息。
第三范式：任何非主键属性不能依赖于其他非主键属性
学号, 姓名, 年龄, 所在学院, 学院地点，学院联系电话，主键为"
学号“；
存在依赖传递: (学号) → (所在学院) → (学院地点, 学院电话)
drop 、truncate、delete
drop直接删掉表 truncate删除表中数据，再插入时自增长id又从1开始 ；delete删除表中数据，可以加where字句，delete的自增长id不会变，从上一个最大值开始
当你不再需要该表时， 用 drop；当你仍要保留该表，但要删除所有记录时， 用 truncate；当你要删除部分记录时（always with a WHERE clause), 用 delete.
事务的四个特性
事务是由一系列SQL语句组成的一系列操作
原子性：一个事务的所有操作，要么全部成功，要么都不成功。
一致性：事务执行前后保证数据的完整性。比如转账，总金额不变
隔离性：一个事务的执行，不能受其他事务的干扰。
持久性：一个事务一旦执行成功，它对数据的修改就是永久性的。
并发读问题
脏读：读到另一个事务未提交的数据。
不可重复读：对一个事物两次读取不一致，另一个事物做了修改。
幻读：对一张表两次查询不一致，另一个事务插入或删除了记录，出现幻行、。
解决并发问题
加锁。乐观锁、悲观锁、行锁、表锁。看锁的文章
四大隔离级别，等级由低到高
读未提交数据：可以读未提交的数据。脏读、不可重复读、幻读
读已提交数据：避免脏读。可能有不可重复读、幻读
可重复读：避免了脏读和不可重复读。同一事物多次读取同样记录结果一致。但是不能解决幻读。因为另一个事务插入记录，会出现幻行。MySQL默认该隔离级 别。同时，InnoDB引擎使用间隙锁解决了幻读问题。
串行化：强制事物串行执行，没有并发，也就没有问题。
count(*) 跟count(1) 的结果一样，返回记录的总行数，都包括
对NULL 的统计，而count(column) 是不包括NULL 的统计
索引相关问题
索引的优缺点
优点：
1.大大加快数据的检索速度。
2.创建唯一索引，保证表中每一行数据唯一。
缺点：
1. 占用物理内存
2.对表中数据进行增加删减时，索引需要动态维护。
尽量选择区分度高的字段作为索引。比如性别，区分度就不高。
索引有哪些
唯一索引：在表上一个或者多个字段组合建立的索引，这个或者这些字段组合值在表中是不可重复的，比如主键就可以作为唯一索引。
主键索引（主索引）：这是唯一索引的特例。一个表只能有一个主索引。
组合索引：基于多个字段的索引。 创建索引 create index idx1 on table1(col1,col2,col3) 查询 select * from table1 where col1= A and col2= B and col3 = C
索引的创建
CREATE UNIQUE INDEX index_name ON table_name (column_list)
ALTER TABLE table_name ADD INDEX index_name (column_list)
索引的原理
https://www.cnblogs.com/xiaolovewei/p/8648683.html
mysql 中MyIsam 与InnoDB 的区别，至少5 点。
Mysql 数据库中，最常用的两种引擎是innordb 和myisam。InnoDB 是Mysql 的默认存储引擎。
1.MyISAM 不提供事务支持。InnoDB 提供事务支持事务。
2.MyISAM 不支持外键，InnoDB 支持外键。
3.锁MyISAM 只支持表级锁，InnoDB 支持行级锁和表级锁，默认是行级锁，行锁大幅度提高了多用户并发操作的性能。innodb 比较适合于插入和更新操作比较多的情况，而myisam 则适合用于频繁查询的情况。
4.MyISAM 支持全文索引， InnoDB 不支持全文索引
5.MyISAM：允许没有主键的表存在。InnoDB：如果没有设定主键，就会自动生成一个6 字节的主键(用户不可见)
一张表,里面有ID 自增主键,当insert 了17 条记录之后,删除了第15,16,17 条记录,再把Mysql 重启,再insert 一条记录,这条记录的ID 是18 还是15 ？
如果表的类型是MyISAM，那么是18。因为MyISAM 表会把自增主键的最大ID 记录到数据文件里，重启MySQL 自增主键的最大ID 也不会丢失。如果表的类型是InnoDB，那么是15。InnoDB 表只是把自增主键的最大ID 记录到内存中，所以重启数据库会导致最大ID 丢失。
数据库索引失效的几种情况。
1.对于组合索引，不是使用的第一部分，则不会使用索引。
2.or 语句前后没有同时使用索引。要想使用or，又想让索引生效，只能将or 条件中的每个
列都加上索引。
3.如果列类型是字符串，那一定要在条件中使用引号引起来，否则不会使用索引。
4.如果mysql 估计使用全表描述比使用索引快，则不使用索引。
5.在索引列上做运算或者使用函数。
6.以“%”开头的LIKE 查询，模糊匹配。
行锁、表锁
MyISAM使用的是表锁，会给整张表加锁，不会出现死锁。
InnoDB使用行锁和表锁，默认行锁，行锁会产生死锁。
InnoDB行锁是通过给索引加锁来实现的。也就是说，并不是给记录加锁，而是索引加锁，如果没有使用索引，那么InnoDB还是会使用表锁。
虽然访问不同的行，但是如果使用的是相同的索引，那么还是会出现冲突。
行锁的死锁
当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。
发生死锁后，InnoDB一般都可以检测到，将持有最少行级锁的事务回滚。
有多种方法可以避免死锁，这里只介绍常见的三种
1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
InnoDB事务的实现
隔离性：通过锁实现
原子性和一致性：通过回滚日志（undo log）实现。所有事务进行的修改都会先记录到这个回滚日志中，然后在对数据库中的对应行进行写入。当出现错误或者需要rollback时，只会按照日志逻辑地将数据库中的修改撤销掉看
持久性：通过重做日志(redo Log)实现。重做日志由两部分组成，一是内存中的重做日志缓冲区，因为重做日志缓冲区在内存中，所以它是易失的，另一个就是在磁盘上的重做日志文件，它是持久的。当我们在一个事务中尝试对数据进行修改时，它会先将数据从磁盘读入内存，并更新内存中缓存的数据，然后生成一条重做日志并写入重做日志缓存，当事务真正提交时，MySQL 会将重做日志缓存中的内容刷新到重做日志文件，再将内存中的数据更新到磁盘上。在发生错误后，数据库重启时会从重做日志中找出未被更新到数据库磁盘中的日志重新执行以满足事务的持久性。
聚簇索引和非 聚簇索引
聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。
union 与union all的区别
UNION
SELECT country FROM apps
UNION ALL
SELECT country FROM apps
内连接和外链接
内连接 则是只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合条件的记录不会出现在结果集中，即内连接只连接匹配的行。
外连接 其结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个表中
的所有数据行，这三种情况依次称之为左外连接，右外连接，和全外连接。
左外连接，也称左连接，左表为主表，左表中的所有记录都会出现在结果集中，对于那些在右表中并没有匹配的记录，仍然要显示，右边对应的那些字段值以NULL来填充。右外连接，也称右连接，右表为主表，右表中的所有记录都会出现在结果集中。左连接和右连接可以互换，MySQL目前还不支持全外连接。
视图
视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，视图是从一个或几个基本表导出的表。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
主键和外键
主键--唯一标识一条记录，不能有重复的，不允许为空
外键--表的外键是另一表的主键, 外键可以有重复的, 可以是空值
主键--主键只能有一个
外键--一个表可以有多个外键
NULL是什么意思
答：NULL这个值表示UNKNOWN(未知):它不表示“”(空字符串)。对NULL这个值的任何比较都会生产一个NULL值。您不能把任何值与一个 NULL值进行比较，并在逻辑上希望获得一个答案。
char和varchar的区别？
答：是一种固定长度的类型，varchar则是一种可变长度的类型，它们的区别是：
char(M)类型的数据列里，每个值都占用M个字节，如果某个长度小于M，MySQL就会在它的右边用空格字符补足．（在检索操作中那些填补出来的空格字符将被去掉）在varchar(M)类型的数据列里，每个值只占用刚好够用的字节再加上一个用来记录其长度的字节（即总长度为L+1字节）．
视图
视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据。作为一个select语句保存在数据字典中的。
通过视图，可以展现基表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。
存储过程和函数
存储过程和函数是事先经过编译并存储在数据库中的一段SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程和函数的区别在于函数必须有返回值，而存储过程没有。
触发器简介
触发器是一个特殊的存储过程，执行存储过程需要使用CALL语句来调用，但是触发器的执行不需要用CALL语句调用，也不需要手工启动，只要的当一个预定义的事件发生时，就会被MYSQL自动调用。比如当对fruits表进行INSERT，DELETE或UPDATE操作时就会激活它。触发器可以查询数据表，而且可以包含复杂的SQL语句，主要用于复杂的业务规则或要求。
外键约束
一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。通过外键与其他表建立关系。
主键、外键和索引的区别
|
|
主键 |
外键 |
索引 |
|
定义： |
唯一标识一条记录，不能有重复的，不允许为空 |
表的外键是另一表的主键, 外键可以有重复的, 可以是空值 |
该字段没有重复值，但可以有一个空值 |
|
作用： |
用来保证数据完整性 |
用来和其他表建立联系用的 |
是提高查询排序的速度 |
|
个数： |
主键只能有一个 |
一个表可以有多个外键 |
一个表可以有多个惟一索引 |
