第二弹：数据类型与索引

2020-06-09 本文已影响0人 lvyz0207

7、数据类型

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
浮点数类型：FLOAT、DOUBLE、DECIMAL
字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
日期类型：Date、DateTime、TimeStamp、Time、Year
其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等

8、字符串(char,varchar,_text)

char类型存储的字符串末尾不能有空格，varchar不限于此。
char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节
varchar是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)
char类型的字符串检索速度要比varchar类型的快
text类型不能有默认值，索引不起作用
BLOB 保存二进制数据，TEXT 保存字符数据。

MySQL数据类型	含义
date	日期 '2020-05-09'
time	时间 '23:59:59'
datetime	日期时间 '2020-05-09 23:59:59'
timestamp	自动存储记录修改时间

数据类型	字节长度	范围或用法
Bit	1	无符号[0,255]，有符号[-128,127]
TinyInt	1	整数[0,255]
SmallInt	2	无符号[0,65535]，有符号[-32768,32767]
MediumInt	3	无符号[0,2^{24-1]，有符号[-2}23,2^23-1]]
Int	4	无符号[0,2^{32-1]，有符号[-2}31,2^31-1]
BigInt	8	无符号[0,2^{64-1]，有符号[-2}63 ,2^63 -1]
Float(M,D)	4	单精度浮点数。D是精度，如果D<=24则为默认的FLOAT，如果D>24则会自动被转换为DOUBLE型。
Double(M,D)	8	双精度浮点。
Decimal(M,D)	M+1或M+2	未打包的浮点数，用法类似于FLOAT和DOUBLE，

9、索引

帮助MySQL高效获取数据的数据结构
目的:提高查询效率
索引本身一般以索引文件的形式存储在磁盘上

# 创建索引
CREATE INDEX indexName ON mytable(username(length)); 

# 修改表结构创建索引
ALTER table tableName ADD INDEX indexName(columnName)

# 唯一索引
CREATE UNIQUE INDEX indexName ON mytable(username(length)); 
## 有四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

# 删除索引
DROP INDEX [indexName] ON mytable; 
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

10、索引结构

索引是在存储引擎中实现的，InnoDB默认为B+Tree树
主键索引：InnoDB的数据文件本身就是主键索引文件，被称为“聚簇索引”，一个表只能有一个聚簇索引。

辅助索引：非主键索引接口的叶子节点存储的是主键值

hash索引：底层是hash表，是一种key-value的存储结构，无序，查询时造成全表扫描；不支持最左匹配原则，大量键值重复时，造成hash碰撞，索引效率低

full-text全文索引:用于全文索引，替代效率较低的LIKE模糊匹配操作。

R-tree空间索引:用于地理空间数据类型

B树与B+树

B树的每个节点都存储数据，而B+树只有叶子节点才存储数据，所以查找相同数据量的情况下，B树的高度更高，IO更频繁。
同时，MySQL底层对B+树进行进一步优化：在叶子节点中是双向链表，且在链表的头结点和尾节点也是循环指向的。

回表操作：
在辅助索引上检索name，到达其叶子节点获取对应的主键；
使用主键在主索引上再进行对应的检索操作
后续文章会有专文详解B树、B+Tree和B-Tree树

11、索引使用条件

需要索引时：

主键自动建立唯一索引
频繁作为查询条件的字段
查询中与其他表关联的字段，外键关系建立索引
单键/组合索引的选择问题，高并发下倾向创建组合索引
查询中排序的字段，排序字段通过索引访问大幅提高排序速度
查询中统计或分组字段

不需要索引情况时：

表记录太少
经常增删改的表
数据重复且分布均匀的表字段，只应该为最经常查询和最经常排序的数据列建立索引（如果某个数据类包含太多的重复数据，建立索引没有太大意义）
频繁更新的字段不适合创建索引（会加重IO负担）
where条件里用不到的字段不创建索引

**高效索引--覆盖索引
查询列要被所建的索引覆盖，不需要进行回表操作。   
** 索引的最左匹配原则成因：
最左优先，以最左边的为起点任何连续的索引都能匹配上。同时遇到范围查询(>、<、between、like)就会停止匹配。
**索引是建立的越多越好吗？
数据量小的表不需要建立索引，增加索引会产生额外的开销
数据变更需要维护索引，更多的索引需要占用更多的资源
更多索引需要更多的空间   
**为什么要使用索引？   
避免全表扫描，提升检索效率
**什么样的信息能成为索引？   
主键、唯一键和普通键   
**索引的数据结构？   
二叉查找树建立二分查找
B-树、B+树、Hash结构
**密集索引和稀疏索引和区别？   
密集索引每个搜索码都对应一个索引项
稀疏索引只为部分搜索码建立索引项

rose