vlambda博客
学习文章列表

《MySQL常见知识点》学习笔记 NO.002

011.char 和 varchar 的区别

① 区别一,定长和变长

  • char 表示定长,长度固定,varchar表示变长,即长度可变。

  • char如果插入的长度小于定义长度时,则用空格填充;

  • varchar小于定义长度时,还是按实际长度存储,插入多长就存多长。

  • 因为其长度固定,char的存取速度还是要比varchar要快得多,方便程序的存储与查找;但是char也为此付出的是空间的代价,因为其长度固定,所以会占据多余的空间,可谓是以空间换取时间效率。varchar则刚好相反,以时间换空间。


② 区别之二,存储的容量不同

  • 对 char 来说,最多能存放的字符个数 255,和编码无关。

  • 而 varchar 呢,最多能存放 65532 个字符。varchar的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532字节。


012. Unix 和 MySQL 时间戳之间进行转换

  • UNIX_TIMESTAMP:MySQL 时间戳转换为 Unix 时间戳

  • FROM_UNIXTIME:Unix 时间戳转换为 MySQL 时间戳。


013.MyISAM 表类型存储位置及其存储格式

每个 MyISAM 表格以三种格式存储在磁盘上:

  • “.frm”文件 存储表定义

  • 数据文件具有“.MYD”(MYData)扩展名

  • 索引文件具有“.MYI”(MYIndex)扩展名


014.MySQL 里记录货币用什么字段类型好

    MySQL数据库中金额字段采用DECIMAL数据类型,因为decimal数据类型不存在精度损失,常用于银行帐目计算。float、double类型都可能会存在精度损失。

例子:

    salary DECIMAL(9,2)

    说明:在这个例子中,9(precision)代表将被用于存储值的总的小数位数,而2(scale)代表将被用于存储小数点后的位数。因此,在这种情况下,能被存储在 salary 列中的值的范围是从-9999999.99 到9999999.99。


015.创建索引注意事项

①非空字段:

应该指定列为 NOT NULL,除非你想存储 NULL。在 mysql 中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用 0、一个特殊的值或者一个空串代替空值;

②取值离散大的字段:

离散程度指的是变量各个取值之间的差异程度),字段的唯一值越多字段的离散程度高;

③ 索引字段越小越好:

数据库的数据存储以页为单位一页存储的数据越多一次 IO 操作获取的数据越大效率越高。


016.使用索引查询一定能提高查询的性能吗?

    使用索引查询不一定能提高查询性能,因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。


017.百万级别或以上的数据如何删除

①关于索引说明:

  • 由于索引需要额外的维护成本,因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的 IO,会降低增/改/删的执行效率。

  • 所以,在我们删除数据库百万级别数据的时候,查询 MySQL 官方手册得知删除数据的速度和创建的索引数量是成正比的。

② 如何删除百万数据

  • 先删除索引(此时大概耗时三分多钟)

  • 然后删除其中无用数据(此过程需要不到两分钟)

  • 删除完成后重新创建索引(此时数据较少了)创建索引也非常快,约十分钟左右。

说明:此步骤与之前的直接删除绝对是要快速很多,而且可以避免直接删除万一删除中断,一切删除会回滚的坑。


018.最左前缀匹配原则

① 概述

  • 最左前缀匹配原则即在MySQL建立联合索引时会遵守最左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。

② 详细

  • 最左前缀匹配原则指的是: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 的顺序可以任意调整。

  • = 和 in 可以乱序,比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql 的查询优化器会帮你优化成索引可以识别的形式。

③深入学习:https://zhuanlan.zhihu.com/p/144853595


019.聚簇索引与非聚簇索引

① 聚簇索引:

  • 将数据存储和索引放在一起、并且是按照一定的顺序组织的,找到索引也就找到了数据。

  • 数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻的存放在磁盘上的。

② 非聚簇索引:

  • 这就有点类似一本书的目录,比如要找到第三章第一节,那就现在目录里面查找,找到对应的页码后再去对应的页码看文章。


020.单表达到多大量开始分库分表

    单表行数超过 500 万行或者单表容量超过 2GB,才推荐进行分库分表。 如果预计三年后的数据量根本达不到这个级别,请不要在创建表时就分库分表。【参考《阿里巴巴 java 开发手册》】