MySQL中存在 float, double 等非标准数据类型, 也有 decimal 这种标准数据类型
其区别在于: float, double等非标准类型在DB中保存的是近似值, 而Decimal则以字符串的形式保存数值.
float和double 数据的精确度取决于分配给每种数据类型的存储长度, 其中float分配了4字节, 而double分配了8字节, 采用float和double本来就是不准的
decimal类型是MySQL官方唯一指定能精确存储的类型, 和金钱相关的类型都要存储为decimal
Datetime
Timestamp
CHAR
CHAR(N) 用来存储非二进制字符串, 插入少于N个字符的会自动在尾部加空格, 查询时, 尾部的空格就会被丢弃掉(客户端可以忽略这个, 当作存入和取出的一致就行). CHAR 使用固定长度的空间进行存储, CHAR(4)存储4个字符, 根据编码方式的不同占用不同的字节, GBK 编码不论是中文还是英文, 每个字符占用2个字节, UTF8编码每个字符占用3个字节.
VARCHAR
VARCHAR(N) 用来存储非二进制字符串, 插入少于N个字符的不填补空格, 查询时, 尾部的空格不会被丢弃掉
BINARY
BINARY(N)存储二进制字符串, 插入少于N个字节的会自动在尾部加0x00, 取出时, 所有的字节都保留, 返回定义长度的字节长度, 在比较的时候, 所有的字节都是有效的, 并且0x00 小于 space (space对应的是0x20)
VARBINARY
VARBINARY 在插入不会去填补0x00字节, 查询的时候也不会丢弃任何字节, 在比较的时候, 所有的字节都是有效的,
区别和选择
如果需要存储的字符串的长度跟所有值的平均长度相差不大, 适合用 CHAR, 如MD5; 对于经常改变的值, CHAR 优于 VARCHAR, 原因是固定长度的行不容易产生碎片.
对于很短的列, CHAR 优于 VHARCHAR, 原因是 VHARCHAR 需要额外一个或两个字节存储字符串的长度.
BINARY 和 VARBINARY 是和编码无关的存储, 适合存储二进制数据.
MyISAM
- 较高的插入和查询速度, 不支持事务
- 如果数据表主要用来插入和查询记录, 则MyISAM能提供较高的处理效率
InnoDB
事务型数据库的首选引擎, 支持事务安全表 ACID, 支持行锁定和外键, 是默认的引擎
如果要提供提交、回滚、崩溃恢复能力的事务安全（ACID兼容）能力, 并要求实现并发控制, InnoDB是一个好的选择
InnoDB 和 MyISAM之间的区别:
MEMORY/HEAP
存储引擎将表中的数据存储在内存中，以便快速访问查询和引用其他表数据
[En]
The storage engine stores the data in the table in memory, providing quick access to query and reference other table data
如果只是临时存放数据, 数据量不大, 并且不需要较高的数据安全性, 可以选择将数据保存在内存中的Memory引擎, MySQL中使用该引擎作为临时表, 存放查询的中间结果, 数据的处理速度很快但是安全性不高.
Archive
只允许INSERT和SELECT操作. Archive支持高并发的插入操作, 但是本身不是事务安全的. Archive非常适合存储归档数据, 如记录日志信息可以使用Archive
MyISAM 和 InnoDB 的区别
第一个重大区别是InnoDB的数据文件本身就是索引文件. MyISAM索引文件和数据文件是分离的, 索引文件仅保存数据记录的地址. 而在InnoDB中, 表数据文件本身就是按B+Tree组织的一个索 引结构, 这棵树的叶节点data域保存了完整的数据记录. 这个索引的key是数据表的主键, 因此InnoDB表数据文件本身就是主索引.
InnoDB要求表必须有主键（MyISAM可以没有）, 如果没有显式指定, 则MySQL系统会自动选择一个可以唯一标识数据记录的列 作为主键, 如果不存在这种列, 则MySQL自动为InnoDB表生成一个隐含字段作为主键, 这个字段长度为6个字节, 类型为长整形
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助, 例如知道了InnoDB的索引实现后, 就很容易明白为什么不建议使用过长的字段作为 主键, 因为所有辅助索引都引用主索引, 过长的主索引会令辅助索引变得过大. 再例如, 用非单调的字段作为主键在InnoDB中不是个好主意, 因为 InnoDB 数据文件本身是一棵 B+Tree, 非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整, 十分低效, 而使用 自增字段作为主键则是一个很好的选择
使用场景
- Archive 用来存日志
- memory用来存session
- MyISAM尽量不用
-
其他的都用InnoDB
-
仅仅能满足”=”,”IN”和”
- 其检索效率非常高, 索引的检索可以一次定位, 不像B-Tree 索引需要从根节点到枝节点, 最后才能访问到页节点这样多次的IO访问, 所以 Hash 索引的查询效率要远高于 B-Tree 索引
- 只有Memory存储引擎显式支持hash索引
B+树
一个平衡的多叉树. B+树从根节点到叶子节点的搜索效率基本相当, 不会出现大幅波动
哈希索引
采用一定的哈希算法, 把键值换成新的哈希值, 检索时不需要类似B+树那样从根节点逐级查找, 只需一次哈希算法即可立刻定位到相应的位置, 查询效率要远高于 B-Tree 索引
等值查询哈希索引具有绝对优势（前提是: 没有大量重复键值, 如果大量重复键值时, 哈希索引的效率很低, 因为存在所谓的哈希碰撞问题. Hash 索引在任何时候都不能避免表扫描, 即使取满足某个 Hash 键值的数据的记录条数, 也无法从 Hash 索引中直接完成查询, 还是要通过访问表中的实际数据进行相应的比较, 并得到相应的结果
哈希索引不适用的场景:
MySQL中, 只有HEAP/MEMORY引擎才显式支持哈希索引, 而常用的InnoDB引擎中默认使用的是B+树索引, 不能指定使用哈希索引, 只能通过设置自适应哈希索引间接使用.
- InnoDB不支持HASH索引(但是InnoDB在内部利用哈希索引来实现其自适应哈希索引功能)
- InnoDB会根据表的使用情况自动为表生成hash索引, 不能人为干预是否在InnoDB一张表中创建HASH索引
- 当InnoDB某些索引值被使用的特别频繁时, 会在内存中基于Btree的索引之上再创建一个HASH索引, 这样BTREE索引也具备了HASH索引的一些优点
unique key unique_username using btree(user_name)
这里的using btree 只是显式指定的使用的索引的方式为b+树, 对于innodb来说默认的索引方式也是用b+树, 因此可以不写
聚集索引是一种重新组织磁盘上的实际数据以按一个或多个指定列的值排序的算法。其特点是数据存储的顺序与索引的顺序一致。通常，默认情况下，主键创建聚集索引，并且一个表中只允许有一个聚集索引。
[En]
A clustered index is an algorithm that reorganizes actual data on disk to sort by the value of one or more specified columns. The characteristic is that the order in which the data is stored is consistent with the order of the index. In general, a primary key creates a clustered index by default, and only one clustered index is allowed in a table.
聚集索引的叶子节点是数据节点，而非聚集索引的叶子节点仍然是索引节点，但有一个指向相应数据块的指针。
[En]
The leaf node of the clustered index is the data node, while the leaf node of the non-clustered index is still the index node, but there is a pointer to the corresponding data block.
MyISAM的是非聚簇索引, B+Tree的叶子节点上的data, 并不是数据本身, 而是数据存放的地址. 主索引和辅助索引没啥区别, 只是主索引中的key一定得是唯一的
InnoDB使用的是聚簇索引, 将主键组织到一棵B+树中, 而行数据就储存在叶子节点上, 若使用”where id = 14″这样的条件查找主键, 则按照B+树的检索算法即可查找到对应的叶节点, 之后获得行数据
在MySQL 5.6.9版本前, Innodb的非聚集索引中包含聚集索引的索引键, 但只起到通过非聚集索引定位记录的作用, 但在MySQL 5.6.9之后版本中, 优化器会考虑非聚集索引中包含的聚集索引键来提升查询性能, 并提供优化器选项use_index_extensions来开启或关闭该特性.
假设有表TB1(ID,C1,C2), ID为主键聚集索引, 然后在列C1建立索引IDX_C1(C1):
- 在MySQL 5.6版本前, 索引类似于IDX_C1(C1) INCLUDE(ID);
- 在MySQL 5.6版本中, 索引类似于IDX_C1(C1,ID);
无论是MySQL 5.5还是MySQL 5.6版本中, 非聚集索引上的数据都是先按照非聚集索引键在按照聚集索引键进行排序, 即在非聚集索引键上值相同的记录会按照聚集索引进行排序.
最左前缀匹配原则, 非常重要的原则, mysql会一直向右匹配直到遇到范围查询
=和in可以乱序
尝试选择差异较大的列作为索引
[En]
Try to select a highly differentiated column as the index
查询时, 索引列不要参与计算
B+ Tree, Hash, FullText, R Tree
主要说一下索引的创建, 修改和删除, 以及不同的索引类型: 普通索引, 唯一索引, 全文索引, 空间索引, 单列索引, 多列索引
ALTER TABLE 表名 ADD INDEX 索引名(列名);
CREATE INDEX 索引名 ON 表名(列名);
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名(列名);
CREATE UNIQUE INDEX 索引名 ON 表名(列名);
ALTER TABLE 表名 ADD INDEX 索引名(列名,列名2);
CREATE INDEX 索引名 ON 表名(列名1,列名2);
ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(列名);
CREATE FULLTEXT INDEX 索引号 ON 表名(列名);
ALTER TABLE 表名 ADD SPATIAL INDEX 索引名(列名);
CREATE SPATIAL INDEX 索引号 ON 表名(列名);
参数: Type 连接类型
连接类型(the join type), 描述了找到所需数据使用的扫描方式, 最为常见的扫描方式从快到慢依次为
系统表, 少量数据, 往往不需要进行磁盘IO, 扫描类型为system 说明数据已经加载到内存, 不需要磁盘IO, 这类扫描是速度最快的
常量连接, 主键或者唯一键上的等值查询, const扫描的条件为
主键索引(primary key)或者非空唯一索引(unique not null)等值扫描, eq_ref扫描的条件为: 对于前表的每一行(row), 后表只有一行被扫描.
非主键非唯一索引等值扫描. 对于前表的每一行(row), 后表可能有多于一行的数据被扫描.
range, 范围扫描, 它是索引上的范围查询, 它会在索引上扫码特定范围内的值.
index, 索引树扫描, 需要扫描索引上的全部数据.
全表扫描
参数: Extra
SQL使用了where条件过滤数据.
SQL所需要返回的所有列数据均在一棵索引树上, 而无需访问实际的行记录.
说明确实命中了索引, 但不是所有的列数据都在索引树上, 还需要访问实际的行记录.
说明得到所需结果集, 需要对所有记录进行文件排序. 典型的, 在一个没有建立索引的列上进行了order by, 就会触发filesort, 常见的优化方案是, 在order by的列上添加索引, 避免每次查询都全量排序.
说明使用了临时表(temporary table)来暂存中间结果. 这类SQL语句性能较低, 往往也需要进行优化. 例如 group by和order by同时存在, 且作用于不同的字段时, 就会建立临时表.
mysql innodb的锁是通过锁索引来实现的
select for update, 如果字段没有索引, 即使使用where条件也会进行表级锁
如果有索引, 会锁定对应where条件中索引值的所有行, 可理解为对该索引值进行了索引, 所以即使另一事务查询的是其他行, 因为索引值相同也会被锁住.
有一个索引，并使用不同的索引值来查找数据，但查询的结果是同一行，这可以理解为真正的数据行锁
[En]
There is an index and different index values are used to look up the data, but the result of the query is the same row, which can be understood as a real data row lock
Original: https://www.cnblogs.com/milton/p/15856842.html
Author: Milton
Title: MySQL专题1: 字段和索引
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/507848/
转载文章受原作者版权保护。转载请注明原作者出处！