数据库面试题

数据库

char和varchar的区别是什么？

char类型的长度是固定的，varchar的长度是可变的
char类型的效率比varchar的效率稍高
varchar比char节省空间，但是在效率上比char稍差些。既要获得效率即必须牺牲一点空间，这就是设计上的"以空间换时间"
如果一个varchar列经常被修改，每次修改的数据长度不同，会引起“行迁移现象”造成多余的IO，这种情况使用char代替varchar

存储引擎

存储引擎	数据结构	remark
InnoDB	B+树	innodb存储引擎支持自适应hash，用户不可以干扰
MyISAM	B+树
memory存储引擎	hash表也可以作为索引的数据结构	存储在内存中，用于临时表

数据结构：hash表

1，使用hash表必须具备好的hash算法，如果算法不合适会造成hash冲突或者hash碰撞，会导致数据散列不均匀，有可能会退化成链表
2，使用hash表不支持范围查询，当需要范围匹配的时候，必须要挨个对比，效率太低
3，需要大量的内存空间

数据结构：B树

如果采用任何一种类型的二叉树（分支只有2个），都会导致树变高，影响整个io次数，从而影响整体数据访问的效率
InnoDB存储引擎默认读取16kb的数据，也就是一个磁盘块就是16kb

p1、p2、p3表示指针，指向下一层地址
16,24表示键值，也就是索引的值
data表示这一行具体的数据

数据结构：B+树

存储引擎是InnoDB或MyISAM，数据结构采用的B+树
如果采用任何一种类型的二叉树，都会导致树变高，影响整个io次数，从而影响整体数据访问的效率
当需要给11,12,13,15中间插入14时，因为磁盘块已经满了，需要进行页分裂，形成两个磁盘块，并且可能影响非叶子节点

一个表可以包含多个索引列，那么数据文件会不会存储几份？

数据仅仅存储一份，不会造成多份数据的数据冗余。比如已经有主键列和具体数据的B+树，别的索引列叶子节点中，会与主键id绑定，通过id查找具体数据

聚簇索引和非聚簇索引？

叶子节点存放一整行数据叫做聚簇索引，叶子节点指向数据对应内存地址叫做非聚簇索引
在InnoDB存储引擎中，既存在聚簇索引，又存在非聚簇索引
在MyISAM存储引擎中，只有非聚簇索引

聚簇索引优点	聚簇索引缺点
当取出一定范围内数据时，使用聚簇索引比非聚簇索引快	插入速度依赖于插入顺序，有可能需要页分裂，最好主键自增
聚簇索引查找目标数据理论上比非聚簇索引快，因为非聚簇索引对位到主键时还要多一个目标记录寻址多一次IO	更新主键代价大，将导致行移动
使用覆盖索引扫描查询不需要回表查询	二级索引访问需要两次查询，第一次找到主键，第二次根据主键找到行数据

InnoDB辅助索引？

也叫做二级索引，根据索引列构建B+树结构。但是B+树叶子节点中只存了索引列和主键的信息。

回表

从一个索引的叶子节点中获取聚簇索引的id值，根据id再去聚簇索引中获取全量记录。尽量减少回表查询

表：id主键、name普通索引、age、gender
select * from table where name='zhangsan'

索引覆盖

从索引的叶子节点中能获取到全量查询列的过程叫做索引覆盖

表：id主键、name普通索引、age、gender
select id,name from table where name='zhangsan'

比如id是主键，a、b、c是组合索引，d没有索引
select id,a,b,c from table where c=1
查询时不需要回表，在组合索引树中就可以查到全量记录(key(a,b,c)和value(id),就不需要回表再查询主键索引B+树)

最左匹配

表：id主键、name、age、gender   name、age组合索引
select * from table where name='zhangsan' and age=10可以使用组合索引
select * from table where name='zhangsan'可以使用组合索引
select * from table where age=10不可以使用组合索引
select * from table where age=10 and name='zhangsan'可以使用组合索引，mysql优化器会进行优化，选择合适顺序进行执行

索引下推

在没有索引下推之前，sql执行如下：先根据name去存储引擎拿到全量数据，将数据读取到server层，然后在server层按照age做数据过滤
有索引下推之后：
根据name、age两个列去存储引擎筛选数据，将最终的结果返回给客户端

InnoDB与MyISAM区别？

InnoDB	MyISAM
InnoDB支持事务，对于InnoDB每一条SQL语言都默认封装成事务，自动提交	MyISAM不支持事务
InnoDB支持外键，对一个包含外键的InnoDB表转为MyISAM会失败	MyISAM不支持外键
InnoDB存储引擎中，既存在聚簇索引，又存在非聚簇索引，索引列可以是主键，也可以是唯一键，也可以是6字节的rowid(int是4字节)	MyISAM存储引擎中，只有非聚簇索引
数据文件是和索引文件在一起ibd	数据文件myd和索引文件myi是分离的
InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描	而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快
锁粒度：InnoDB支持表锁和行锁	MyISAM只支持行锁

索引的优缺点

建立索引的优点：
1、索引能够提高数据检索的效率，降低数据库的IO成本。
2、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
3、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
4、加速两个表之间的连接，一般是在外键上创建索引

建立索引的缺点：
1、需要占用物理空间,建立的索引越多需要的空间越大
2、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
3、当对数据进行增删改时，索引也需要动态的维护。

索引查询失效？

索引查询失效情形
like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效
对索引列使用函数，比如select * from user where length(name)=6，因为索引保存的是索引字段的原始值，而不是使用函数计算的值
对索引进行表达式计算，比如select * from user where id+1<10，因为索引保存的是索引字段的原始值，而不是使用表达式计算的值
or语句前后没有同时使用索引，比如 select * from user where name=‘张三’ or age=18，只有1个列是索引列是没有意义的，需要or前后都是索引列
对索引隐式类型转换，mysql遇到字符串与数字进行比较时，就自动将字符串转为数字进行比较，比如select * from user where name=10(name本来是一个varchar类型)，相当于使用内置函数 select * from user where CAST(name AS signed int)=10
联合索引，最左匹配原则，因为联合索引非第一个字段是局部有序的，而不是全局有序
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引
在索引列上使用 IS NULL 或 IS NOT NULL操作，在索引字段上使用not，<>，!=，它的处理只会产生全表扫描
优化器去计算一下对应的二级索引数量占所有记录数量的比值而已
当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效

特例：explain select station,degree,no from RADAR_IMG1 where station like ‘%914%’;当查询字段都是索引字段时索引覆盖，不进行回表，也会用到索引

or前后的条件查询字段都使用索引，会引起索引合并。
也就是比如where first_name = 'xyz' or last_name = 'xyz'，
先根据first_name二级索引查询到对应主键id，再根据last_name二级索引查询到对应主键id，取交集，再根据id进行回表查询具体字段。 

什么列适合、不适合建立索引？

什么列适合建立索引	什么列不适合创建索引
经常需要搜索的列	查询中很少使用
主键列上，强制该列的唯一性和组织表中数据的排列结构	只有很少数据值的列，比如男女
在经常用在连接列上，这些列主要是一些外键，可以加快连接速度	text，image，bit数据类型的列
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的	修改远远大于检索性能的
在经常需要排序的列上创建索引，因为索引已经排序 order by
在经常使用在WHERE子句中的列上面创建索引

索引类型

索引类型	含义	语法
普通索引	基于普通字段建立的索引，没有任何限制	create index <索引名> on tablename(字段名);
唯一索引	索引字段的值必须唯一，但允许有空值	create unique index <索引名> on tablename(字段名);
主键索引	特殊的唯一索引。在创建或修改表时追加主键约束，每个表只能有一个主键
复合索引	多个列上建立索引，复合索引可以代替多个单一索引，相比多个单一索引复合索引开销更小	create index <索引名> on tablename(字段名1,字段名2,…);
全文索引	mysql5.6之后InnoDB与MyISAM都支持全文索引，大量的文本数据检索，使用全文索引，查询速度比like快很多倍	create fulltext index <索引名> on tablename(字段名); select * from table_name where match(name) against(‘keyword’); select * from table_name where match(name) against(‘keyword*’ in boolean mode);

buffer pool

用于InnoDB访问时缓存表以及索引数据。缓冲池允许直接从内存访问常用数据，从而加快处理速度。
在专用服务器上，通常多达80%的物理内存分配给缓冲池，缓冲区默认大小128M。
缓冲池是以页为单位的链表，清除不常用缓存
如何在缓冲区查找数据页？
mqsql中维护着一张hash表的数据结构，key是表空间号+数据页号，value是对应控制块。
缓冲区由缓冲数据页Page（Page页默认大小16kb）和对缓冲数据页进行描述的控制块组成。
写缓冲区，仅适用于非唯一普通索引页？
如果在索引设置唯一性，在进行修改时，InnoDB必须要做唯一性校验，因此必须查询磁盘，做一次IO操作。会直接将记录查询到BufferPool中，然后在缓冲区修改，不会在changeBuffer操作。
change buffer记录了非唯一索引页的增删改的缓冲变更，等未来数据被读取时，再合并到缓冲区中
普通的LRU算法？
最近最少使用，就是末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰
当全表扫描或者select *查询时，很大可能将真正的热数据淘汰掉
改进型LRU算法？

缓冲池的3/8用于存储冷数据区，5/8用于存储热数据区
列表的中点，是新的子列表的尾部，旧的子列表的头部，当InnoDB预读页到缓冲池时，会初始化的将插入到中点
访问冷的子列表的页，会使这个页变热，该页会被移动到热数据区的头部
一个未使用的页到达冷数据区尾部时会被驱逐出缓冲区
老数据页的数据什么时候会被转到热数据区呢？
1，如果该数据页在LRU链表中存在超过1s，就将其移动到链表头部（链表指整个LRU链表）
2，如果该数据页在LRU链表中存在时间短于1s，其位置不变（由于全表扫描有一个特点，就是它对某个页的频繁访问总耗时会很短）
3，1s这个时间由参数 innodb_old_blocks_time控制的

自适应hash索引？

自适应hash索引底层是hash表，针对B+树查询的优化
自适应hash只适合搜索等值查询select * from table_name where name=‘zhangsan’;
可以根据索引的键值，快速找到满足条件的叶子节点。

自增id、uuid、雪花算法

自增id	uuid	雪花算法
递增，聚簇索引性能更好	全局唯一	64位比特位long类型的唯一id
节约空间	更加安全	0+系统时间+机器码+序列号
不利迁移	无序，聚簇索引会导致页分裂
不利扩展	相对浪费空间

count(*)、count(1)、count(字段名)区别？

count(*)	返回整个表行数，MyISAM会单独记录表中总行数直接返回，InnoDB会根据非聚簇索引返回条数，次之主键索引，次之全表扫描
count(1)	与count(*)相同
count(字段名)	返回当前列中非null项的条数。如果当前列存在索引就使用索引，如果不存在就全表扫描

mysql建表注意事项

1，命名规范，见名知意
2，选择合适字段类型

整型int，bigint；浮点型float，double；字符型varchar，char
考虑数据长度，比如某个字段长度不会超过10个字符，使用char(10)类型代替varchar(50)类型，以节省空间
比如存储的值太大，字段类型修改为text，同时抽出单独一张表，用主键与之对应
注意精度和小数位数：对于需要精确数值计算的字段(如货币和百分比)，应该选择带有精度和小数位的字段类型(如decimal)
考虑数据完整性：日期型数据应该使用date或者datetime类型，确保输入的日期格式的正确

3，主键设计要合理
主键设计，不要与业务相关联，因为业务会发生变化。应当使用自增id，保持主键连续性。比如使用优化的雪花id等
4，选择合适字段长度
varchar和char类型表示字符长度，其他类型表示的长度都是字节长度。
5，优先考虑逻辑删除，而不是物理删除
因为物理删除数据恢复困难。物理删除会导致索引树重构。
6，每个表都需要添加通用字段
(id,create_time,creator,update_time)必须,(update_by,remark)非必须
7，一张表字段不宜过多
尽量不超过20个。超出的话优先考虑拆分，也就是通常的查询表，详情表
8，定义字段尽可能not null
not null可以防止空指针，null值需要额外的空间，导致比较运算更为复杂，使优化器难以优化sql，null值也可能导致索引失效
9，合理添加索引
高频使用的查询条件、区分度高的字段优先、不要建立过多索引(不超过5个)、联合索引
10，反范式设计，通过业务字段冗余来减少表关联

第三范式：对字段的冗余性，要求任何字段不能由其他字段派生出来
比如订单表中，可以多出一列总价列，从而避免需要关联产品表中单价

11，避免使用mysql保留字
12，不搞外键关联，一般在代码中维护

可能导致性能问题，每次插入、更新、删除都需要进行约束检查，可能会导致额外的开销和延迟。
限制数据库灵活性和可扩展性
可能导致死锁和死循环
增加数据库维护和管理成本

13，字段注释
每个字段含义注释清楚，包括枚举类型，比如01待支付，02已支付，03已发货
14，时间类型的选择

date：日期值，格式yyyy-mm-dd
time：时间值，格式hh:mm:ss
datetime:日期时间值，格式yyyy-mm-dd hh:mm:ss,与时区无关，推荐使用
timestamp：时间戳，格式yyyymmddhhmmss，范围1970-01-01 00:00:00到2038-01-19 03:14:07，跟时区有关
year：年份值

15，sql编写的一些优化经验

1，避免使用select *语句。应该只选择需要的列，以减少网络传输和提高查询性能。
2，使用索引来提高查询速度。
3，避免使用外键约束
4，使用limit 1来限制查询结果只有一条记录
5，避免在where字句中使用or，使用union来连接查询
6，优化limit深分页问题，可以使用offset偏移量来替代limit
	 select * from user where uid >=(select uid from user order by uid limit 10000,10)
7，使用where条件限制要查询的数据，避免返回多余的行
8，避免在索引列上使用mysql内置函数、表达式操作，可能导致索引失效
9，避免在where子句中使用!=或<>操作符，也可能导致索引失效
10，联合索引，最左匹配，与where字句中字段出现顺序无关
11，order by列上建立索引，避免sortfile
12，插入过多数据时，考虑批量插入
13，使用索引覆盖，避免回表
14，explain分析sql的执行计划

慢查询

1，扫描多余的数据，避免使用select * from tableName
2，跨多表扫描数据，可以减少join，并适当反范式化，增加冗余数据，以空间换时间
3，索引没有建立，索引优化不当，或未应用到最佳索引（联合索引使用最左匹配）
4，索引数据类型简单且合适、占用空间少、尽可能避免空值（not null），性能最佳。
5，数字类型只需比较一次，字符串会逐字比较；索引不要超过6个，会导致写性能降低。
6，重复数据多的字段不适合建立索引，如性别；随机字符串不适合建立聚簇索引（数据移动）
7，联合索引使用最左匹配
8，索引失效：避免在where字句中使用or（改用union all）、in或not in（改用exists）、is null、!=或<>操作符、对字段进行表达式与函数操作，否则将导致引擎放弃使用索引而进行全表扫描
9，缓存失效：sql大小写敏感、超过query_cache_size大小、内存不足、缓存超时、数据修改

-- 不会使用同一缓存
select name from users where id = 1;
SELECT name FROM users WHERE id = 1;

从explain执行计划入手
永远用小的结果集驱动大的结果集
尽可能在索引中完成排序
只获取自己需要的列
尽可能减少join和子查询
合理设计并利用索引

多表查询

内连接
内连接时当连接字段为null,会被认为此数据不合法,所以内连接时不显示emp表id=3的数据。
# 隐式内连接
SELECT t1.name,t1.age,t2.name FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id`;
# 显式内连接
语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件
SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;

左外连接
语法：select 字段列表 from 表1 left join 表2 on 条件;
	查询的是左表所有数据以及其交集部分,主表是表1
	左外连接以左表为主,显示没有dept_id的wangwu
SELECT 	t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;

右外连接
语法：select 字段列表 from 表1 right join 表2 on 条件；
	查询的是右表所有数据以及其交集部分,主表是表2。
	右外连接以右表为主，显示没有emp的部门3
SELECT 	* FROM emp t1 RIGHT JOIN dept t2 ON t1.`dept_id` = t2.`id`;

子查询
查询中嵌套查询，称嵌套查询为子查询。
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
子查询的结果是多行单列的
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME in ('财务部','市场部'));
子查询的结果是多行多列的
SELECT t1.`id`,t1.`dname`,t1.`loc` , t2.total
FROM dept t1,(SELECT dept_id,COUNT(id) total FROM emp GROUP BY dept_id) t2
WHERE t1.`id` = t2.dept_id;