Mysql数据库面试

进入原文

相关知识

操作系统，数据结构与算法 hadoop

离散数学，数字电路，体系结构，编译原理，实战经验

概念

DBA--数据库管理员。

SQL---结构化查询语言(Structured Query Language)简称

数据库是所有软件体系中最核心的存在。

数据库管理系统

DBMS-数据库管理系统，科学有效，数据的维护和获取

作用：管理 数字

  1. 关系型

    Mysql ,Oracle ,sqlServer,DB2,SQLlite

    通过表与表之间，行列之间的关系进行数据存储

    SQL来操作

  2. 非关系型

    redis,MongDB

    对象存储，通过对象的自身属性来决定

    NSQL not only sql

mysql有关权限的表都有哪几个

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别user，db，table_priv，columns_priv和host。下面分别介绍一下这些表的结构和内容：

  • user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
  • db权限表：记录各个帐号在各个数据库上的操作权限。
  • table_priv权限表：记录数据表级的操作权限。
  • columns_priv权限表：记录数据列级的操作权限。
  • host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

数据类型

数值

  1. tinyint 十分小 -----1个字节

  2. smallInt 比较小 ---- 2个字节

  3. mediumint 中等大小---3个字节

  4. int 常用大小 ----4个字节

  5. bigint 较大 ---8个字节

  6. float 浮点数 -------4字节

  7. double 浮点数 ------8字节

  8. decimal 字符串形式的浮点数 ----常用作金融计算

字符串

  1. char ----固定大小的字符串 0-255

  2. varchar---可变长度的字符串 0--65535 ----string

  3. tinytext 微型文本 0-2^8 -1

  4. text 文本串（大文本） 0-2^16 -1

  5. ...很多

日期时间

java.util.Data

  1. data    YYYY-MM-DD

  2. time    HH:mm:ss

  3. datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式

  4. timestamp 时间戳 1970.1.1 到现在的毫秒数

  5. year 年份表示

null

  1. 没有值 未知

  2. 不要使用null进行运算，没有意义，结果为null

数据库的字段属性

Unsigned：无符号的

  1. 无符号的整数

  2. 声明了该列不能为负数

zerofull

  1. 0填充的

  2. 不足的位数，使用0填充 int(3) --005

自增

  1. 默认起始值为1，可以设置起始值及步长等，整数

非空 not null

　　1. 勾选后，不赋值则报错

default 默认值

  1. 设置默认值，不指定时，放入默认值

字符类型使用策略：

  1. 对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。
  2. 对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。
  3. 使用时要注意只分配需要的空间，更长的列排序时会消耗更多内存。
  4. 尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。

MySQL常用引擎

  • Innodb引擎：Innodb引擎提供了对数据库ACID事务的支持。并且还提供了行级锁和外键的约束。它的设计的目标就是处理大数据容量的数据库系统。
  • MyIASM引擎(原本Mysql的默认引擎)：不提供事务的支持，也不支持行级锁和外键。
  • MEMORY引擎：所有的数据都在内存中，数据的处理速度快，但是安全性不高。

MySQL存储引擎MyISAM与InnoDB区别

 

MyISAM

InnoDB
存储结构 每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件 所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB
存储空间 MyISAM可被压缩，存储空间较小 InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复 由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作 免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了
文件格式 数据和索引是分别存储的，数据.MYD，索引.MYI 数据和索引是集中存储的，.ibd
记录存储顺序 按记录插入顺序保存 按主键大小有序插入
外键 不支持 支持
事务 不支持 支持
锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的） 表级锁定 行级锁定、表级锁定，锁定力度小并发能力高
SELECT MyISAM更优  
INSERT、UPDATE、DELETE   InnoDB更优
select count(*) myisam更快，因为myisam内部维护了一个计数器，可以直接调取。  
索引的实现方式 B+树索引，myisam 是堆表 B+树索引，Innodb 是索引组织表
哈希索引 不支持 支持
全文索引 支持 不支持

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

MyISAM索引与InnoDB索引的区别？

  • InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
  • InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
  • MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
  • InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。

InnoDB引擎的4大特性

  • 插入缓冲（insert buffer)

  • 二次写(double write)

  • 自适应哈希索引(ahi)

  • 预读(read ahead)

存储引擎选择

如果没有特别的需求，使用默认的Innodb即可。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。

Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

什么是索引？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。

更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。

索引有哪些优缺点？

索引的优点

  • 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
  • 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点

  • 时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
  • 空间方面：索引需要占物理空间。

索引有哪几种类型？

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
  • 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

  • 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
  • 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

全文索引： 是目前搜索引擎使用的一种关键技术。

  • 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引

索引设计的原则？

  1. 适合索引的列是出现在where子句中的列，或者连接子句中指定的列
  2. 基数较小的类，索引效果较差，没有必要在此列建立索引
  3. 使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间
  4. 不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。

创建索引的原则（重中之重）

索引虽好，但也不是无限制的使用，最好符合一下几个原则

1） 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2）较频繁作为查询条件的字段才去创建索引

3）更新频繁字段不适合创建索引

4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)

5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6）定义有外键的数据列一定要建立索引。

7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8）对于定义为text、image和bit的数据类型的列不要建立索引。

 

 

 

 

 

 

 

 

 

 

posted @ 2021-03-02 13:07  少时也曾爱白衣  阅读(55)  评论(0)  编辑  收藏  举报