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)联合索引想要索引生效，只能使用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 个字符。