面试题----MySQL

数据库设计的三大范式

　　https://www.cnblogs.com/knowledgesea/p/3667395.html

数据库表设计问题

           

　　需要如果查询第一张表，需要使用关联表的某一些数据（经常使用）

　　　　1、我们可以使用延迟加载，对第一张表进行查询，如果需要查询到第二张表，会再去发送一条sql语句进行查询（使用Mybtis可以做到延迟加载，本质就是根据第一条sql数据去查询第二种表）

　　　　　　问题会造成产生1+N条sql语句，比如我们查询到了一个10条员工数据（第一条sql），但是如果10个员工数据都要去查询部门表，就会发送10条sql语句进行查询，造成1+N（N是第一条sql语句查询的数据长度）

　　　　2、我们直接一次性连表查询，将所有的数据查询出来（性能也会有影响）

SELECT  employee.`name`,department.name  FROM employee , department WHERE employee.`dept`=department.`id`
SELECT employee.`name`,department.name FROM employee INNER JOIN department ON employee.`dept` = department.id

　　　　3、我们需要违反数据库设计的第三条范式，直接将部门的name字段添加到员工表中，这会造成数据冗余，但是对于查询效率会大大提高。

状态码

　　linux中设置文件的权限，744,444，等就是利用状态码

　　假如一个银行用户有许多属性（是否是vip，时候绑定银行账号，是否是绑定了邮箱......），如果设计数据库的时候给每一个属性都设计一个字段，会造成数据字段的很多，一个优化方式，设置状态码，数据库中只设置一个字段，我们人为规定绑定vip状态码是001（对于10进制是1），绑定银行卡状态是010（对应10进制是2），绑定邮箱状态是100（对应10进制是4）。

　　现在查出数据库中状态码字段是十进制3（对应的二进制是011），就可以确定该用户绑定了vip，绑定了银行卡，没有绑定邮箱。

　　long类型有8个字节，64位，就可以表示64中属性。int 类型4 个字节，32为，可以表示32中属性。如果太大话，可以用char来表示二进制。

　　需要绑定邮箱

　　　　现在数据库中状态码是011（用户绑定了vip，绑定了银行卡），直接011 || 100，即可

System.out.println(3|4); //7(二进制111)

　　解除绑定邮箱

System.out.println(6^2); //用状态码与异或需要移除的状态码

　　查看是否绑定了银行卡

        System.out.println(7&2); //2,查看是否绑定了银行卡  111绑定了
        System.out.println(5&2); //0,查看是否绑定了银行卡  101没有绑定

　　这种方式的问题：

　　　　索引失去意义，比如说你需要查询所有的vip用户，你只能一条一条的查询所有用户的状态码，进行与运算，判断是否是vip，如果是返回这条记录，继续查看下一条记录（效率极低），有一种方式，我们将vip用户被占用的所有可能性算出来，利用索引看这个状态码在不在里面，如果在，直接返回这条数据，这个方式虽然利用了索引，但是如果数量足够大的话，vip用户被占用的所有可能性就非常多，比如我们直接8个属性，他们直接的组合关系（排除vip顺序），有2**7方中可能性。

　　　　注：只要列参与了运算，索引就可能失去了意义。

　　　　如果解决问题：使用全文索引技术（ES），了解全文检索：https://blog.csdn.net/carolzhang8406/article/details/81660244

数据库表中设置的长度

　　我们设计数据库的时候，指定字段的长度，

　　对于字符串 char 10，表示长度就是10个字符长度，这个字段最大长度只能是10个字符（不管数字汉子或者其他）

　　对于数字，后面的数字表示是显示的长度不一样而已 都是占用字节数是固定的，比如 tinyint 无论后面数字是几，它最大的数一定是127（占一个字节） 

一对一的关联表

　　对于存一对一的表关联我们可以使用外键进行关联。

　　我们还可以不使用外键，将默认的自增id关闭，对关联表增加数据的时候，使用第一张表的id进行添加数据。他们之间直接通过id进行关联。 

关于外联和内联

　　外联：如果关联的表中没有数据，则查询的字段使用null表示

　　内联：如果关联的表中没有数据，返回的数据全部直接是空

数据字段

• timestamp：当修改了数据中的字段的值，timestamp中的日期自动更新

连表查询的注意事项

　 如果连表，自己希望是一对一，并且业务逻辑就一定是一对一的话，就需要在left join后面加上条件判断来确定唯一一条关联的数据

数据库中char和varchar的区别

1. char类型的长度是固定的，varchar的长度是可变的。

   这就表示，存储字符串'abc'，使用char(10)，表示存储的字符将占10个字节（包括7个空字符），取数据的时候，char类型的要用trim()去掉多余的空格

　　　　　　　　　　　　　　使用varchar2(10),，则表示只占3个字节，10是最大值，当存储的字符小于10时，按照实际的长度存储。

2.char类型的效率比varchar的效率稍高

3.varchar 与 varchar2的区别

varchar2是oracle开发的一个数据类型。

工业标准的varchar可以存储空字符串，oracle的varchar2还可以存储NULL值，如果想要有向后兼容的能力建议使用varchar2

4.varchar2比char节省空间，但是在效率上比char稍差些。既要获得效率即必须牺牲一点空间，这就是设计上的"以空间换时间"

varchar2虽然比char节省空间，但是一个varchar2列经常被修改，而且每次修改的数据长度不同，这会引起“行迁移的现象”，

而这造成的多余的I/O，是数据库设计中尽量避免的，在这种情况下使用char代替varchar2会更好些。

总结：1. 如果一个字段经常被修改，而且每次修改的数据长度不同，为了效率应当考虑用char定长代替varchar2变长。（列如一个用户的名字经常被修改）

　　    2. 设计的时候尽量考虑  用空间换时间。

提高查询数据库性能相关的有什么方式？

　　加索引的方式：索引就是加速查询 ：通过不断的缩小数据的范围筛选出最终想要的结果
　　索引又分为几大类？
　　索引分类
　　　　1.普通索引index :加速查找
　　　　2.唯一索引
　　　　　　主键索引：primary key ：加速查找+约束（不为空且唯一）
　　　　　　唯一索引：unique：加速查找+约束 （唯一）
　　　　3.联合索引
　　　　　　-primary key(id,name):联合主键索引
　　　　　　-unique(id,name):联合唯一索引
　　　　　　-index(id,name):联合普通索引
　　　　4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
　　　　5.空间索引spatial :了解就好，几乎不用

数据的存储

• 对于信息不变的数据，比如姓名，性别等等------>MySQL
• 对于评论的数据------------------------------------------>MongDB
• 对于图片--------------------------------------------------->分布式文件系统(Hadoop,HDFS)
• 商品关键词------------------------------------------------>搜索引擎(ISearch,ES)
• 热点高频信息--------------------------------------------->Redis,Memcache,Tair
• 商品交易--------------------------------------------------->支付宝,外部交易系统

MySQL索引使用B+tree数据结构的原因

二叉树

• 对于自增主键，存在极端情况

红黑数

• 对于自增主键，虽然能进行自动旋转平衡，但是树的高度太高，并且存在回旋查找的问题（进行范围查找的时候，比如查询id>5,就需要回旋查询数据）

哈希表

• 可以使用，但是使用的少(设置索引的时候可以指定索引算法(hash和btree选择一个))
• hash表的效率非常高，但是不支持范围查询，比如where id>10;所以使用少

Btree（多路二叉树）

• B树的最大特点就是一个节点可以存多个值。解决树的高度问题，但是依然存在回旋查找的问题

B+tree

　　叶子节点[最下面一排]进行的从小到大排序（单向链表），叶子节点存放（key，value（数据的地址）），非叶子节点存放（key），像9既是叶子节点也是非叶子节点。

　　彻底解决了回旋查找的问题，范围查找速度非常快

　　mysql官方给一个节点设置默认大小为16k，非叶子结点不存放date值，就可以存放更多的索引元素

　                            　

MySQL索引如何存null

　　索引的叶子结点不存放null，在非叶子结点，值的最前面存放null（可以吧null当作最小的数据），如果是联合索引，如果第二个索引值为null，则在第一个索引相同块的最前面存放null。mysql建议所有的字段都设置不为null，如果为null，就设置默认值。

MySQL中Date目录

  mysql每一个表都可以有不同的存储引擎

MyISAM存储引擎

mysql一个数据库对呀data中一个文件夹，该文件夹存放数据库的表

文件夹中包括xx.frm文件，xx.MYD文件，xx.MYI文件

frm文件保存表结构相关信息

MYD文件存放表中的所有的数据行。

MYI文件保存表的索引

MYI的叶子点中date存放的是MYD所在行磁盘文件指针。

                                        

 

 

InnerDB存储引擎

文件夹包含连个文件，xx.frm，xx.ibd文件

ibd文件把索引和行数据合并到一起存储

 

                                                    

 

                                          

 

聚集索引

innerDB的主键索引就是一个聚集索引

聚集索引：将索引和数据（data）聚集在一个文件中（ibd文件）

非聚集索引：例如MyISAM存储引擎的主键索引，索引和数据文件是分离的（MYI和MYD）

为什么innerDB存储引擎的表必须要有主键

• 因为innerDB的表中的数据存储需要介于主键索引来维护的。即主键索引使用的是聚集索引
• 如果自己没有创建主键，并且表中没有定义唯一索引，innerDB表会自动生成一个隐藏的rowID字段

MyISAM和InnerDB不同

结构上

　　聚合索引和非聚合索引

功能上　　

InnoDB：
　　支持事务处理等
　　不加锁读取
　　支持外键
　　支持行锁
　　不支持FULLTEXT类型的索引（5.7之后支持全文索引）
　　不保存表的具体行数，扫描表来计算有多少行（如果select count(*) where 条件，效率和MYISAM一样了）
　　DELETE 表时，是一行一行的删除
　　跨平台可直接拷贝使用
　　表格很难被压缩

MyISAM：
　　不支持事务，回滚将造成不完全回滚，不具有原子性
　　不支持外键
　　不支持外键
　　支持全文搜索
　　保存表的具体行数,不带where时，直接返回保存的行数
　　DELETE 表时，先drop表，然后重建表
　　MyISAM 表被存放在三个文件 。frm 文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是MYI (MYIndex)引伸
　　跨平台很难直接拷贝
　　表格可以被压缩

如何选择

• innodb提供事务，可以崩溃恢复，最低行锁
• myisam不支持事务，不能崩溃恢复，表锁，
• 读写分离，读库可以选择myisam

MyISAM适合读，InnoDB适合写

InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”

主键索引和非主键索引（辅助索引）

　　MyISAM的主键索引和非主键索引差不多数据结构

　　innerDB的非主键索引，叶子节点的value存储的是主键值，设计原则：保证一致性（完整的数据只需要维护一份）和节省存储空间，

　　

Mysql全文索引

https://blog.csdn.net/weixin_42529699/article/details/89487138 

DBA推荐使用整形的自增主键

插入数据，字符串比较大小耗时

B+树的节点是有存储限制的。随机插入很容易造成已经插入满的节点数据进行分裂，然后进行平衡，效率低，自增主键是往后插入的，可以有效减小数据插入时候树的分裂平衡

联合索引的数据结构

　　先根据第一个字段进行排序，在根据第二个字段进行排序。 

MySql联合索引命中规则

　　博客：https://blog.csdn.net/u013276277/article/details/78150411

 补充：

MySQL联合索引遵循最左前缀匹配规则，即从联合索引的最左列开始向右匹配，直到遇到匹配终止条件。例如联合索引(col1, col2, col3), where条件为col1=`a` AND col2=`b`可命中该联合索引的(col1,col2)前缀部分, where条件为col2=`b` AND col3=`c`不符合最左前缀匹配，不能命中该联合索引。 

2) 匹配终止条件为范围操作符(如>, <, between, like等)或函数等不能应用索引的情况。例如联合索引(col1, col2, col3), where条件为col1=`a` AND col2>1 AND col3=`c`, 在col2列上为范围查询，匹配即终止，只会匹配到col1，不能匹配到(col1, col2, col3). 

3) where条件中的顺序不影响索引命中。例如联合索引(col1, col2, col3), where条件为col3=`c` AND col2=b AND col1=`a`, MySQL优化器会自行进行优化，可命中联合索引(col1, col2, col3). 

MySQL优化

表设计优化

• 设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
• 选择合适的表字段数据类型和存储引擎，适当的添加索引。
• 字段设置，char和varchar选择

其他设计优化

• 添加缓存机制，比如memcached。
• mysql库主从读写分离。

适合的存储引擎

• Hash表，innerDB，MyISAM。

查询优化

• 一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%a%” 不会使用索引而like “aaa%”可以使用索引。
• 不使用NOT IN和<>操作
• 对频繁查询多个联合字段（经常组合查询），后期尝试加入组合索引。
• 最左匹配原则。(A,B,C) 这样3列，mysql会首先匹配A，然后再B，C.如果用(B,C)这样的数据来检索的话，就会找不到A使得索引失效。如果使用(A,C)这样的数据来检索的话，就会先找到所有A的值然后匹配C，此时联合索引是失效的。
• 把最常用的，筛选数据最多的字段放在左侧(最前面)。 
• SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE

联合索引的建立

　　如果联合字段中某一个字段很容易定位到某几条数据（能晒掉大部分的数据），可以不需要建立联合索引。但是注意筛选数据最多的字段放在左侧(最前面)。

联合失效

　　查询字段通过索引字段，但是没有用到索引的效果。

　　联合索引没有使用最佳做前缀原则，失效原理：更联合索引的排序有关

　　where a>1 and b=1，b索引失效，失效原理：确定了a的区间。但是区间中b的顺序是无效的，只能在a的区间一个一个的比较b的值。b的索引就失效了。

　　where a like "%xx"，a索引失效，失效原理：类似最佳做前缀原则，如果like "a%"，就不会失效

　　or in !=，都失效。

　　是否可以迅速定位到值，而不需要一个一个的比较。

联合索引拆分单索引

　　多个单列索引在多条件查询时只会生效第一个索引！所以多条件联合查询时最好建联合索引！

^{查询字段设计到多个索引，MySQL如何选择}

 　　当一个表有多条索引可走时, Mysql 根据查询语句的成本来选择走哪条索引；