mysql

最新推荐文章于 2021-01-18 19:58:39 发布

mjyywd

最新推荐文章于 2021-01-18 19:58:39 发布

阅读量190

点赞数 1

分类专栏： database

database 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySQL支持的存储引擎

Oracle,mssql不开源的DB只支持一种存储引擎，不支持自主选择
mysql支持第三方引擎集成，可以自己定制引擎然后集成到mysql
MySQL支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CVS、BLACKHOLE、FEDERATED等
其中InnoDB和BDB提供事务安全表，其他存储引擎都是非事务安全表。

sql查询优化

使用查询优化器

在执行一条SQL语句时，MySQL并不一定完全按照SQL的逻辑来执行。
会先使用查询优化顺对SQL语句进行分析，并进行查询的优化，使用优化后的逻辑来进行查询。优化的目的主要是为了尽可能地排除不符合条件的索引。
如：有一张表mytable,共三个字段col1,col2,col3。各自建立了索引。
使用以下语句进行查询：

SELECT col1 FROM mytable 
WHERE col3='value2' AND col2='valu1'

假设满足col2='value1’的记录有100条
满足col3='value2’的记录有200条
同时满足col2=‘valu1’ AND col3='value2’的语句有30条
有两种查询方法：

使用col2=‘value1’ 找出100条数据，然后遍历这100条数据找出符合条件的30条
使用col3='value2’找出200条数据，然后遍历200条数据找出符合条件的30条

使用第一种查询方式只需要从查出的100条数据中找出30条，第二种方式需要从200条数据中找出30条。
经过查询优化器优化后，实际的查询过程为：

SELECT col1 FROM mytable 
WHERE col2='valu1' AND col3='value2'

DB如何知道每个查询条件会查出的个数？

在查询前判断是否使用该字段作为索引查询时，优化器会先快速地对该索引进行检查，估算出大致的量。
可以对数据表进行分析（ANALYSE TABLE），生成关于索引值分布情况的统计数据。如果一张表的数据不经常发生变化，不需要经常进行表分析。

explain 语句

explain语句可以提供优化器对SQL语句生成的执行计划。
在SQL语句的开头加上EXPLAIN命令即可查看执行计划。
表结构
在这里插入图片描述

执行语句:

EXPLAIN select * from quotation_eos where time>1234 AND price_buy< 10

执行语句:

EXPLAIN select * from quotation_eos where time>123456789 AND price_buy>10

执行语句:

EXPLAIN select * from quotation_eos where amount_buy<>'asa'

其中rows为DB预估需要检查的数据行个数

索引

优点：加快查询速度
缺点：减慢插入速度

innoDB与MyISAM只支持BTree索引，不能更换

memory支持Btree与Hash索引

使用索引注意

只为搜索，分类或分组的字段建立索引
为高维度的字段建立索引。字段重复值太多，索引的作用很小
字段长度要尽量小，加快索引查询速度
为字符串值的前缀编索引。一个字段的值为字符串，可以只对其前缀加索引。减少磁盘开销。
索引不要过多。会占用磁盘开销。每次写入数据，索引都会重新编排。
索引类型与要进行比较操作的类型要匹配。
如：InnoDB与MyISAM默认使用B+树索引。遇到空间数据会使用R树索引。Memory引擎默认使用散列索引。散列索引通过计算散列函数来查询，但是用于范围查询效果不好。如id<30,weight between 10 and 20。如果多是这种查询，需要建立B树索引。
不要对索引字段的值进行计算或者自动类型转换后查询。会使索引失效。(char(12)与varchar(12)属于不同的类型，比较效率低于cahr(12)与char(12)比较)
like查询时不要在一开始使用通配符
字段值尽量设置为Not null
值的类型较少的字段，如性别，可以设置为enum，mysql内部将其作为数值处理，速度快。
使用联合索引时必须要使用最左边的前缀字段。否则联合索引失效

联合索引与多个单列索引的区别

联合索引

一张表(mytable)中分别有三个字段,从左到右依次为userId,mobile,billMonth。添加了三个字段的联合索引。
分别以不同的字段作为查询条件。

userId为查询条件

EXPLAIN SELECT * FROM `mytable` WHERE userid='2222'

mobile为查询条件

EXPLAIN SELECT * FROM `mytable` WHERE mobile='13281899972'

billMonth作为查询字段

EXPLAIN SELECT * FROM `mytable` WHERE billMonth='2018-04'

userId与mobile为查询字段

EXPLAIN SELECT * FROM `mytable` WHERE userid='2222' AND mobile='13281899972'

mobile and userid

EXPLAIN SELECT * FROM `mytable` WHERE  mobile='13281899972' AND userid='2222'

userid or mobile

EXPLAIN SELECT * FROM `mytable` WHERE userid='2222' OR mobile='13281899972'

userid and billMonth

EXPLAIN SELECT * FROM `mytable` WHERE userid='2222' AND billMonth='2018-04'

mobile and billMonth

EXPLAIN SELECT * FROM `mytable` WHERE mobile='13281899972' AND billMonth='2018-04'

没有使用到最左边的字段，所以联合索引无效

单列索引

分别对三个字段建立单列索引

userid and mobile and billMonth

EXPLAIN SELECT * FROM `mytable` WHERE  userid='2222' AND mobile='13281899972' AND billMonth='2018-04'

使用到的字段只有一个，mobile与billMonth字段没有使用到索引
2. userid or mobile

EXPLAIN SELECT * FROM `mytable` WHERE  userid='2222' OR mobile='13281899972'

使用or时两个字段的索引都使用上了

数据类型的选择

尽量使用数值操作，不要使用字符串操作。（枚举类型除外，mysql内部会用数值代表枚举类型）
例如IP地址以字符形式保存，查询时效率很低。如果将数字保存在int型每一位中，使用一个整形数字表示ip，效率会大大提高。
短的类型够用，就不要使用长类型。短类型节约磁盘开销，且索引速度快。

不同引擎的数据选择

MyISAM

使用固定长度类型，不用可变长度类型。
固定长度类型索引速度快，但是占用更多空间。
可变长度类型索引速度慢，但是占用空间少。可变长度类型的空间长度为数据长度加一到两个字节用于保存数据长度信息。
其次可变长度类型，更容易形成碎片，需要经常运行optimize table 进行优化。

Memory

memory引擎统一使用固定长度类型。所以定长与可变长度类型没有区别。

innoDB

对固定长度与可变长度数据不进行区分。数据行的标头存储数据位置的指针，所以每一个数据行都是定长的。
影响性能的因素主要是数据量大小。
使用char()类型会更加占用空间，varchar()类型破坏数据行的固定长度结构。所以使用可变长度更加适合。

分析数据表，给出优化建议

SELECT * FROM quotation_eos PROCEDURE ANALYZE();

此时表中需要有实际的数据。系统根据数据存储的数据的长度来确定优化方案。
输出优化方案后，可以通过alter table来修改表结构。

可以选择将数据压缩到一个字段里

如考试题目，转换为一个json,xml字符串保存到text字段里，需要扩展内容时，不需要添加新的字段，直接修改json,xml的格式就可以。
但是要注意碎片
尽量避免对text字段进行索引
可以考虑将text数据单独保存到一张表中。在使用MyISAM引擎时有利形成固定长度的数据。

写数据时的优化

批量写入性能优于单条依次写入。避免了索引重复修改

调度与锁定问题

默认的调度策略

MyISAM

写比读有更高的优先权
对于写按照先来后到顺序进行
读操作可以同时进行

所以在写的过程中，不可以进行读操作

在读取时也会自动锁定表，不允许写入，但是允许读取。

对比

	MyISAM	InnoDB
锁定范围	整张表	要修改的数据行
并发性	低	高
死锁	不存在	可能发生

在事务过程中，只有在必要时才会取得锁定。事务没有获得所有的必要锁定。可能会出现死锁情况。

1. 查询A获取了x行的锁定，此时事务B获得了y行的锁定。
2. A试图获取y行的锁定，进行操作，此时A会等待事务B释放y行的锁。
3. B试图获得x行的锁，进行操作，B会等待A操作完成释放x的锁。
4. 相互等待，导致死锁。

索引

二叉查找树

二叉树具有以下性质：

左子树的键值小于根的键值，右子树的键值大于根的键值。

对于每一个根结点，左边结点的数据全部小于根结点，右边的数据全部大于根结点。

查找深度为n的结点，只需要n次。

查找数据5时，只需要3次，查找3时只需要2步。

问题：

二叉查找树可以任意地构造，同样是2,3,5,6,7,8这六个数字，也可以按照下图的方式来构造：

这种情况下查找效率与线性查找没有区别。效率极低。所以需要二叉平衡树（AVL树）

二叉平衡树

二叉平衡树是要棵二叉查找树，同时任何一个结点的两个子树高度差不大于1。

AVL树避免不会出现线性构造的树。不会出现效率过低的查询。

在插入或者删除结点时，会造成AVL树不平衡。根结点的左右子树高度差大于1。此时需要对树进行旋转，使其高度差小于等于1。

造成不平衡的情况共四种，如图：

四种情况分别为：

LL： 根节点的左孩子（Left Child）的左孩子（Left Child）还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡。
RR： 根节点的右孩子（Right Child）的右孩子（Right Child）还有非空节点，导致根节点的右子树高度比左子树高度高2，AVL树失去平衡。
LR： 根节点的左孩子（Left Child）的右孩子（Right Child）还有非空节点，导致根节点的左子树高度比右子树高度高2，AVL树失去平衡。
RL： 根节点的右孩子（Right Child）的左孩子（Left Child）还有非空节点，导致根节点的右子树高度比左子树高度高2，AVL树失去平衡。

旋转方法

LL：

    1.将根节点的左孩子作为新根节点。

    2.将新根节点的右孩子作为原根节点的左孩子

    3.将原根节点作为新根节点的右孩子。

RR:

将根节点的右孩子作为新根节点。
将新根节点的左孩子作为原根节点的右孩子。
将原根节点作为新根节点的左孩子。

LR：

围绕根节点的左孩子进行RR旋转。
围绕根节点进行LL旋转。

RL：

围绕根节点的右孩子进行LL旋转。
围绕根节点进行RR旋转。

平衡多路查找树(B-Tree)

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。

磁盘

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来。默认的块大小为4K。

DB读取磁盘时，一次性读取整数块磁盘块。读取是会以页为单位，InnoDB引擎默认的页大小为16K。对于4K块大小的磁盘，一次性会读取4块。

Ｂ树结构

特性：

每个节点最多有m个孩子。（ｍ为树的阶数）
除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。（Ceil为身上取整）
若根节点不是叶子节点，则至少有2个孩子
所有叶子节点都在同一层，且不包含其它关键字信息
每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
ki(i=1,…n)为关键字，且关键字升序排序。
Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

查找过程

模拟查找关键字29的过程：

1. 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
2. 比较关键字29在区间（17,35），找到磁盘块1的指针P2。
3. 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
4. 比较关键字29在区间（26,30），找到磁盘块3的指针P2。
5. 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
6. 在磁盘块8中的关键字列表中找到关键字29。

B+树

是在B树上的一种优化。是innoDB与MyISAM的实际使用的索引。

特性

具有B树的特性，同时有以下几点不同：

非叶子结点只存储键值信息
所有叶子节点之间都有一个链指针
实际数据全部存放在叶子结点

结构

由于实际数据不存放在非叶子结点，所以叶子结点可以保存更多key值，大大减少树的高度。
所有数据都保存在叶子结点，所以可以实现范围查找与分页查找。B树的数据并不全部保存在叶子结点，所以无法实现范围与分页查找。

更新操作

往DB中添加数据时会更新B+树索引。更新时树的结构会进行更新。

更新操作

哈希索引

Memory引擎中默认使用hash索引。
其原理与HashMap原理相似。对主键取Hash，得到数据保存的位置。
hash索引查询速度快于B+树,但是有以下局限：

Hash 索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询
Hash 索引无法被用来避免数据的排序操作
Hash 索引不能利用部分索引键查询。对于组合索引，不能只使用其中一部分进行查询。
存在Hash冲突的情况。冲突较多时，效率并不高。

mjyywd

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql

MySQL支持的存储引擎Oracle,mssql不开源的DB只支持一种存储引擎，不支持自主选择mysql支持第三方引擎集成，可以自己定制引擎然后集成到mysqlMySQL支持的存储引擎包括MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CVS、BLACKHOLE、FEDERATED等其中InnoDB和BD...
复制链接

扫一扫