
索引
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
)联合索引想要索引生效,只能使用col1
和col1
,col2
和col1
,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 的区别
- 原版utf8标准的字符是字节变长的
- 变长utf8在存储形态上不利于做检索,所以mysql把它做成定长的存储(3个字节)
- 本来3字节的utf8已经可以覆盖这个世界上的所有语言,直到emoji等符号的出现,让字符集大大扩展,导致utf8开始出现一些四字节的符号
- mysql又不能改原来的标准(否则在各种领域都会有兼容性的问题),只能把这种定长四字节的utf8当作一种新的字符集来处理,叫做utf8mb4
如果要在 MySQL中保存 4 字节长度的 UTF-8 字符,就需要使用 utf8mb4 编码,但是要注意只有 5.5.3 版本以后的MySQL才支持(查看版本命令: select version())。为了获取更好的兼容性,建议使用 utf8mb4 而非 utf8. 对于CHAR类型数据,utf8mb4会多消耗一些空间,但根据 MySQL官方建议,可以使用 VARCHAR 替代 CHAR。
数据类型
数值类型
MySQL支持所有标准SQL数值数据类型。
这些类型包括严格数值数据类型(INTEGER
、SMALLINT
、DECIMAL
和NUMERIC
),以及近似数值数据类型(FLOAT
、REAL
和DOUBLE PRECISION
)。
关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值,并且支持MyISAM、MEMORY、InnoDB和BDB表。
作为SQL标准的扩展,MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1 byte | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 bytes | (-32 768,32 767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3 bytes | (-8 388 608,8 388 607) | (0,16 777 215) | 大整数值 |
INT或INTEGER | 4 bytes | (-2 147 483 648,2 147 483 647) | (0,4 294 967 295) | 大整数值 |
BIGINT | 8 bytes | (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) | (0,18 446 744 073 709 551 615) | 极大整数值 |
FLOAT | 4 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) | 单精度浮点数值 |
DOUBLE | 8 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的值 | 小数值 |
###### 日期和时间类型 | ||||
表示时间值的日期和时间类型为DATETIME 、DATE 、TIMESTAMP 、TIME和YEAR 。 |
每个时间类型有一个有效值范围和一个”零”值,当指定不合法的MySQL不能表示的值时使用”零”值。
TIMESTAMP类型有专有的自动更新特性,将在后面描述。
类型 | 大小( bytes) | 范围 | 格式 | 用途 |
---|---|---|---|---|
DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | ‘-838:59:59’/’838:59:59’ | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901/2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
###### 字符串类型 | ||||
字符串类型指CHAR 、VARCHAR 、BINARY 、VARBINARY 、BLOB 、TEXT 、ENUM和SET 。该节描述了这些类型如何工作以及如何在查询中使用这些类型。 |
类型 | 大小 | 用途 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
注意:char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。 |
CHAR
和 VARCHAR
类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY
和VARBINARY
类似于 CHAR
和 VARCHAR
,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
BLOB
是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB
类型:TINYBLOB
、BLOB
、MEDIUMBLOB
和 LONGBLOB
。它们区别在于可容纳存储范围不同。
有 4 种 TEXT
类型:TINYTEXT
、TEXT
、MEDIUMTEXT
和 LONGTEXT
。对应的这 4 种 BLOB
类型,可存储的最大长度不同,可根据实际情况选择。
varchar、text和blob
text
和blob
两种数据类型,它们的设计初衷是为了存储大数据使用的,MySql单行最大数据量为64K。
text
和varchar
是一组既有区别又有联系的数据类型,其联系在于当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允许有默认值
varchar
和text
两种数据类型,能用varchar就用varchar而不用text(存储效率高)
,varchar(M)
的M
有长度限制,之前说过,如果大于限制,可以使用mediumtext(16M)
或者longtext(4G)
。
text
和blob
,就是text
存储的是字符串而blob
存储的是二进制字符串,blob
是用于存储例如图片、音视频这种文件的二进制数据的。
原创文章,作者:睿达君,如若转载,请注明出处:https://zrrd.net.cn/2275.html