Mysql索引过多有什么影响 mysql索引占空间吗

转载

技术领航者之声 2023-06-24 16:50:30

文章标签 Mysql索引过多有什么影响 MySQL mysql 数据主键 文章分类 MySQL 数据库

1.索引是什么？有什么作用以及缺点
2.索引的目的是什么
3.索引对数据库系统的负面影响
4.建立索引的原则
5.主键、外键和唯一索引的区别
6.MySQL底层实现，MySQL有什么引擎
7.InnoDB和MyISAM区别，InnoDB替代了MyISAM，那么MyISAM是否一无是处。
8.什么是事务，事务特性
9.如何设计一个高并发的系统（对于mysql来讲）
10.锁的优化策略
11.优化SQL的方法
12.谈谈三大范式，什么时候使用反范式设计
13.说几个mysql中你常用的函数答：
14.varchar(100)和varchar(200)的区别
15.varchar(20)和int(20)中的20含义一样吗
16 .如何开启慢日志查询？
17.数据库版本升级从多少升到多少？两个版本区别是什么？
18.主从原理是什么

索引和B+Tree结构

1.索引是什么？有什么作用以及缺点

答：索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。也可以理解为索引就是一本书的目录，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引那么需要的空间就会更大。

2.索引的目的是什么

答：为了高效的查找得到我们所需要的数据，减少分组和排序时间，提高我们的mysql的性能

3.索引对数据库系统的负面影响

答：虽然索引对于数据库的查询提高了效率，但一定程度上增加了空间的占用，同时写入的速度降低了不少，和原有写入数据相比较，多了一步去维护索引的操作。

4.建立索引的原则

答：选择唯一性索引，为经常需要查询、排序、分组和联合操作的字段建立索引，限制索引的数目，最左前缀匹配原则（非常重要的原则），尽量选择区分度高的列作为索引，字段尽力设置不为null，索引列上不计算。

5.主键、外键和唯一索引的区别

答：定义：

主键：唯一标识一条记录，不能有重复的，不允许为空

外键：表的外键是另一表的主键, 外键可以有重复的, 可以是空值

索引：该字段没有重复值，但可以有一个空值

作用：

主键：用来保证数据完整性

外键：用来和其他表建立联系用的

索引：是提高查询排序的速度

个数：

主键：主键只能有一个

外键：一个表可以有多个外键

索引：一个表可以有多个唯一索引

显示所有大图

6.MySQL底层实现，MySQL有什么引擎

答：mysql底层采用B+tree的存储结构，也就是只有叶子节点携带真实数据，每个节点大小为16Kb，大致三层的B+tree就可以存2000W左右的数据，大大的减少了磁盘的IO。我们常见的存储引擎有InnoDB和MyISAM。

7.InnoDB和MyISAM区别，InnoDB替代了MyISAM，那么MyISAM是否一无是处。

答：InnoDB支持事务，支持行锁，在磁盘上只存两个文件，一个是索引文件，另一个是数据文件，在B+Tree的主键索引上，叶子节点携带全部数据，MyISAM不支持事务，不支持行锁，磁盘上存了三个文件，一个是索引文件，另一个是数据文件，还有一个存放的对应关系文件，从查询的角度来说，InnoDB没有MyISAM的单条查询速度高，MyISAM采用Hash存储回行得到数据的查询过程，单MyISAM对于范围查询不是很友好。因此我们可以看出InnoDB用的更广一些，但同时MyISAM对于非范围查询的高效还是有很大用处的，而且MyISAM对于表内的总数查询，维护了单独的数据，也是很高效的。

8.什么是事务，事务特性

答：事务是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。在我们的mysql里也是如此，也就是我们的ACID原则。A原子性，把一系列的动作视为一个最小的操作（原子操作）C一致性，从一个状态到另一个状态是一致的，I隔离性：事务与事务之间是不可见相互隔离的，D持久性：一旦事务提交，则所做修改就会被永久保存到数据库中。可以简单说一下可重复读的MVCC机制，面试官也懵圈。

9.如何设计一个高并发的系统（对于mysql来讲）

答：这个后面会结合别的技术来说，只讲mysql不太好说。大致就是我们首先应该考虑到的是读写分离操作（过几天博客里会详细说这个），再就是我们常见的分库分表操作，水平切分垂直切分。还可以加入缓存redis操作。合理使用索引，explain进行sql优化。

10.锁的优化策略

答：优化，也就是最小力度的锁我们的数据，也就是行锁，InnoDB的行锁其实是加在索引字段的，避免行锁的升级为表锁，再就是我们尽量避免间隙锁，尽量避免我们的范围修改，如果真的必须范围修改，那么我应该尽可能的缩小到最小的范围。

11.优化SQL的方法

答：设置一个主键索引，需主要主键索引一般没有真正业务含义，使用int类型自动增长的，而且不能为null，非主键索引字段优先考虑区分度高的业务情况和最左前缀原则，设置为null。如果真的数据量不大，不建议加索引，反而会影响效率的。选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置NOTNULL，使用连接(JOIN)来代替子查询，适用联合(UNION)来代替手动创建的临时表。学会使用explain进行SQL分析，实在不行可以打开trace进行分析SQL情况，用完记得关闭。

12.谈谈三大范式，什么时候使用反范式设计

答：第一范式（1NF）：确保每列保持原子性即列不可分

第二范式（2NF）：属性完全依赖于主键，也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

第三范式（3NF）：属性和主键不能间接相关（减少数据冗余，这样就可以通过主外键进行表之间连接）

比如我们表比较多，需要关联时，但我们的A表只需要关联B表的一个字段，而且每次都需要关联查询你，这时我们可以采用A表放置一个冗余字段来存B表的那个字段。这个操作其实就是一个反范式的。

13.说几个mysql中你常用的函数答：

答：sum、count 、avg、min、max

14.varchar(100)和varchar(200)的区别

答：占用内存空间大小肯定是不一致的，但是占用我们磁盘的大小是一致的，我们存储字符串"abc"，完全是一样的磁盘空间，但是对于varchar(100)来说，接收到的字符串长度太长了就会报错的。后面的数字代表可存储的字节数。

15.varchar(20)和int(20)中的20含义一样吗

答：显然不一致， int(M) M表示的不是数据的最大长度，只是数据宽度，并不影响存储多少位长度的数据；varchar(M) M表示的是varchar类型数据在数据库中存储的最大长度，超过则不存；

16 .如何开启慢日志查询？

答：有2种方式，一是修改mysql的配置文件，二是通过set global语句来实现。slow_query_log = ON，打开日志，long_query_time = 2，设置时间，2秒就算是慢查询，然后重启mysql服务即可，进入mysql控制台，输入SET GLOBAL slow_query_log = 'ON';SET GLOBAL long_query_time = X;不需要重启服务就可以得到慢查询日志。

总结一下：

只要我们熟知，我们mysql的底层是B+tree的，B+tree是什么样子的，很多面试题都是围绕这个东西来出题的，为什么不为空，空不好找B+tree的位置啊等等。再就是我们要学会使用Explain来分析sql，有的时候即使有覆盖索引，mysql也不一定去走的，他内部底层会判断走这个索引的代价。可以自行尝试。

17.数据库版本升级从多少升到多少？两个版本区别是什么？

mysql5.7 : 2015年发布，mysql5.7查询性能得以大幅提升，比 MySQL 5.6 提升 1 倍降低了建立数据库连接的时间。

mysql5.6 : 2013年2月发布，mysql5.6版本其中InnoDB可以限制大量表打开的时候内存占用过多的问题InnoDB性能加强。如大内存优化等InnoDB死锁信息可以记录到 error 日志，方便分析InnoDB提供全文索引能力。

mysql5.5 : 2010年12月发布mysql5.5版本默认存储引擎更改为InnoDB 多个回滚段（Multiple Rollback Segments）,之前的innodb版本最大能处理1023个并发处理操作，现在mysql5.5可以处理高达128K的并发事物改善事务处理中的元数据锁定。例如，事物中一个语句需要锁一个表，会在事物结束时释放这个表，而不是像以前在语句结束时释放表。增加了INFORMATION_SCHEMA[ˈski:mə]]表，新的表提供了与InnoDB压缩和事务处理锁定有关的具体信息。

mysql5.1 : 20o8年发布的MySQL 5.1 的版本，基本上就是一个增加了崩溃恢复功能的MyISAM，使用表级锁，但可以做到读写不冲突，即在进行任何类型的更新操作的同时都可以进行读操作，但多个写操作不能并发。

mysql-5.0 : mysql-5.0版本之前，myisam默认支持的表大小为4G。从mysql-5.0以后，myisam默认支持256T的表单数据。myisam只缓存索引数据。 2005年的5.0版本又添加了存储过程、服务端游标、触发器、查询优化以及分布式事务功能。

mysql-4.1 : 2002年发布的4.0 Beta版，至此MySQL终于蜕变成一个成熟的关系型数据库系统。 2002年mysql4.1版本增加了子查询的支持，字符集增加UTF-8，GROUP BY语句增加了ROLLUP，MySQL.user表采用了更好的加密算法。支持每个innodb引擎的表单独放到一个表空间里。innodb通过使用MVCC(多版本并发控制)来获取高并发性，并且实现sql标准的4种隔离级别，同时使用一种被称成next-key locking的策略来避免幻读(phantom)现象。除此之外innodb引擎还提供了插入缓存(insert buffer)、二次写(double write)、自适应哈西索引(adaptive hash index)、预读(read ahead)等高性能技术。