《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 开发手册》】