Mysql 索引、字符集、数据类型学习汇总

Mysql 索引、字符集、数据类型学习汇总

索引

mysql索引的建立对于Mysql的高效运行是很重要的,索引可以大大的提高mysql的检索速度。 拿汉语字典的目录页(索引)打比方,我们可以按拼音、笔画、偏旁部首等排序的目录(索引)快速查找到需要的字。 索引分单列索引和组合索引。

单列索引,即一个索引只包含单个列,一个表可以有多个单列索引。 组合索引,即一个索引包含多个列。

优点

  • 索引减少了服务器需要扫描的数据量,从而加快了数据的检索速度,这也是创建索引的最主要原因。
  • 索引可以将随机IO变成顺序IO
  • 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
  • 使用分组和排序子句进行数据检索时,同样可以减少查询中分组和排序的时间

缺点

  • 创建索引和维护索引都需要耗费时间,这种时间随着数据量的增加而增加。
  • 索引需要占用物理空间,除了表数据占用空间外,每个索引还要占用一定的物理空间。
  • 对表中的数据进行增、删、改的时候,索引也要动态维护,时间成本增加。

索引引用准则

  • 在经常需要搜索的列上,可以加快搜索速度。
  • 在作为主键的列上,强制改列的唯一性和组织表中数据的排列结构。
  • 在经常需要根据范围(<, <=, =, > ,>=, between,in)进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的。
  • 在经常需要排序的(order by)de 列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序时间。
  • 在经常使用WHERE子句的列上面创建索引,加快条件的判断速度。
  • 对于那些只有很少数据或者重复至多的数据不应该增加索引。
  • 对于改列修改性能要求远大于检索性能时,不应该创建索引。(修改性能和检索性能时相互矛盾的)

索引类型

  • 普通索引:这是最基本的索引类型,而且他们有唯一性之类的限制。
  • 唯一索引:这种索引和前面的 “普通索引“ 基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。
  • 主键索引:这是一种特殊的唯一索引
  • 全文索引:MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中,全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建,也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集,通过ALTER TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。
联合索引(组合索引)

联合索引遵循最左匹配原则:如果你的SQL语句中用到了联合索引中最左变的索引,那么这条SQL语句就可以利用这个联合索引去进行匹配,当遇到范围查询(>、<、between、like)就会停止匹配。

实例:

联合索引的定义为(MySQL):

ALTER TABLE table_name ADD INDEX (col1,col2,col3);

若多个一条SQL,需要多个用到两个条件

SELECT * FROM user_info WHERE username=’XX’,password=’XXXXXX’;

当索引在检索 password字段的时候,数据量大大缩小,索引的命中率减小,增大了索引的效率。

符合索引的索引体积比单独索引的体积要小,而且只是一个索引树,相比单独列的索引要更加的节省时间复杂度和空间复杂度

当创建(col1,col2,col3)联合索引时,相当于创建了(col)单列索引,(clo1,clo2)联合索引以及(col1,col2,col3)联合索引想要索引生效,只能使用col1col1,col2col1,col2,col3三种组合;当然,col1,col3组合也可以,但实际上只用到了col1的索引,col3并没有用到!

在这里插入图片描述

联合索引相当于一个按照姓氏——名字的一个电话簿,只能先确定姓氏才可以命中索引,下列可以正确命中联合索引的语句( =IN直接的字段都可以乱序,MySQL的查询优化器可以优化成索引识别的形式)

-- 只命中 col1,col2
SELECT * FROM `table_name` WHERE `col1`='XX';
-- 命中col1,col2。col1,col2的顺序可以颠倒
SELECT * FROM `table_name` WHERE `clo1`='XX',`clo2`='XXX'; 
SELECT * FROM `table_name` WHERE `clo2`='XXX', `clo1`='XX';  
-- 命中col1,col2,col3,同理,三个列的顺可以颠倒
SELECT * FROM `table_name` WHERE `col1`='X',`col2`='XX',`col3`='XXX';
SELECT * FROM `table_name` WHERE `col1`='X',`col3`='XX',`col2`='XXX';
SELECT * FROM `table_name` WHERE `col2`='X',`col3`='XX',`col1`='XXX';
复制代码


字符集 utf8 和 utf8mb4 的区别

  1. 原版utf8标准的字符是字节变长的
  2. 变长utf8在存储形态上不利于做检索,所以mysql把它做成定长的存储(3个字节)
  3. 本来3字节的utf8已经可以覆盖这个世界上的所有语言,直到emoji等符号的出现,让字符集大大扩展,导致utf8开始出现一些四字节的符号
  4. mysql又不能改原来的标准(否则在各种领域都会有兼容性的问题),只能把这种定长四字节的utf8当作一种新的字符集来处理,叫做utf8mb4

如果要在 MySQL中保存 4 字节长度的 UTF-8 字符,就需要使用 utf8mb4 编码,但是要注意只有 5.5.3 版本以后的MySQL才支持(查看版本命令: select version())。为了获取更好的兼容性,建议使用 utf8mb4 而非 utf8. 对于CHAR类型数据,utf8mb4会多消耗一些空间,但根据 MySQL官方建议,可以使用 VARCHAR 替代 CHAR。



数据类型

数值类型

MySQL支持所有标准SQL数值数据类型。

这些类型包括严格数值数据类型(INTEGERSMALLINTDECIMALNUMERIC),以及近似数值数据类型(FLOATREALDOUBLE PRECISION)。

关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型大小范围(有符号)范围(无符号)用途
TINYINT1 byte(-128,127)(0,255)小整数值
SMALLINT2 bytes(-32 768,32 767)(0,65 535)大整数值
MEDIUMINT3 bytes(-8 388 608,8 388 607)(0,16 777 215)大整数值
INT或INTEGER4 bytes(-2 147 483 648,2 147 483 647)(0,4 294 967 295)大整数值
BIGINT8 bytes(-9,223,372,036,854,775,808,9 223 372 036 854 775 807)(0,18 446 744 073 709 551 615)极大整数值
FLOAT4 bytes(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)0,(1.175 494 351 E-38,3.402 823 466 E+38)单精度浮点数值
DOUBLE8 bytes(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)双精度浮点数值
DECIMAL对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值
###### 日期和时间类型
表示时间值的日期和时间类型为DATETIMEDATETIMESTAMPTIME和YEAR

每个时间类型有一个有效值范围和一个”零”值,当指定不合法的MySQL不能表示的值时使用”零”值。

TIMESTAMP类型有专有的自动更新特性,将在后面描述。

类型大小( bytes)范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3‘-838:59:59’/’838:59:59’HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:00/2038 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值,时间戳
###### 字符串类型
字符串类型指CHARVARCHARBINARYVARBINARYBLOBTEXTENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型大小用途
CHAR0-255 bytes定长字符串
VARCHAR0-65535 bytes变长字符串
TINYBLOB0-255 bytes不超过 255 个字符的二进制字符串
TINYTEXT0-255 bytes短文本字符串
BLOB0-65 535 bytes二进制形式的长文本数据
TEXT0-65 535 bytes长文本数据
MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215 bytes中等长度文本数据
LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据
LONGTEXT0-4 294 967 295 bytes极大文本数据
注意:char(n) varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。

CHAR VARCHAR 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARYVARBINARY类似于 CHAR VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB类型:TINYBLOBBLOBMEDIUMBLOBLONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型:TINYTEXTTEXTMEDIUMTEXTLONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。


varchar、text和blob

textblob两种数据类型,它们的设计初衷是为了存储大数据使用的,MySql单行最大数据量为64K。

textvarchar是一组既有区别又有联系的数据类型,其联系在于当varchar(M)的M大于某些数值时,varchar会自动转为text

  • M>255时转为tinytext
  • M>500时转为text
  • M>20000时转为mediumtext

所以过大的内容varchar和text没有区别,同事varchar(M)和text的区别在于:

  • 单行64K即65535字节的空间,varchar只能用63352/65533个字节,但是text可以65535个字节全部用起来
  • text可以指定text(M),但是M无论等于多少都没有影响
  • text不允许有默认值,varchar允许有默认值

varchartext两种数据类型,能用varchar就用varchar而不用text(存储效率高)varchar(M)M有长度限制,之前说过,如果大于限制,可以使用mediumtext(16M)或者longtext(4G)

textblob,就是text存储的是字符串而blob存储的是二进制字符串,blob是用于存储例如图片、音视频这种文件的二进制数据的。

原创文章,作者:睿达君,如若转载,请注明出处:http://zrrd.net.cn/2275.html

发表回复

登录后才能评论
咨询电话
联系电话:0451-81320577

地址:哈尔滨市松北区中小企业总部基地13F

微信咨询
微信咨询
QQ咨询
分享本页
返回顶部