数据库基础复习之数据库优化

一.数据库索引技术
索引是在存储引擎层实现的,而不是在服务器层实现的,所以不同存储引擎具有不同的索引类型和实现。例如MyISAM索引子节点上存储的是hash值,而不像INNoDB一样存数据。
1. B-Tree
1.所有叶节点具有相同的深度,也就是说B-Tree是平衡的;
2.一个节点中的 key从左到右非递减排列;
3.如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1,且不为null,则该指针指向节点的所有key大于等于keyi且小于等于 keyi+1。
查找算法:首先在根节点进行二分查找,如果找到则返回对应节点的data,否则在相应区间的指针指向的节点递归进行查找。由于插入删除新的数据记录会破坏B-Tree的性质,因此在插入删除时,需要对树进行一个分裂、合并、旋转等操作以保持B-Tree性质
2. B+Tree
与 B-Tree 相比,B+Tree有以下不同点:
1.每个节点的指针上限为2d而不是2d+1(d为节点的出度);
2.内节点不存储 data,只存储 key;
3.叶子节点不存储指针。
查找算法:进行查找操作时,首先在根节点进行二分查找,找到一个 key 所在的指针,然后递归地在指针所指向的节点进行查找。直到查找到叶子节点,然后在叶子节点上进行二分查找,找出 key 所对应的 data。
3. 顺序访问指针
一般在数据库系统或文件系统中使用的B+Tree结构都在经典 B+Tree 基础上进行了优化,在叶子节点增加了顺序访问指针,做这个优化的目的是为了提高区间访问的性能。
4. 优势
文件系统及数据库系统普遍采用B-Tree作为索引结构,主要有以下两个原因:
(一)更少的检索次数
平衡树检索数据的时间复杂度等于树高h,而树高大致为O(h)=O(logdN),其中d为每个节点的出度。B+Tree相比于B-Tree 更适合外存索引,因为 B+Tree 内节点去掉了data域,因此可以拥有更大的出度,检索效率会更高,由此可见BTree不会用在mysql中实现,mysql是基于缓存和外存实现的
(二)充分利用计算机的预读特性
为了减少磁盘 I/O,磁盘往往不是严格按需读取,而是每次都会预读。局部性原理:当一个数据被用到时,其附近的数据也通常会马上被使用。预读过程中,磁盘进行顺序读取,顺序读取不需要进行磁盘寻道,并且只需要很短的旋转时间,因此速度会非常快。操作系统一般将内存和磁盘分割成固定大小的块(页),内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小,使得一次 I/O 就能完全载入一个节点,并且可以利用预读特性,相邻的节点也能够被预先载入。
5. 索引
B+Tree索引B+Tree 索引是大多数 MySQL 存储引擎的默认索引类型。因为不再需要进行全表扫描,只需要对树进行搜索即可,因此查找速度快很多。除了用于查找,还可以用于排序和分组。可以指定多个列作为索引列,多个索引列共同组成键。B+Tree 索引适用于全键值、键值范围和键前缀查找,其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找,则无法使用索引。InnoDB 的 B+Tree 索引分为主索引和辅助索引。主索引的叶子节点 data 域记录着完整的数据记录,这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引。
辅助索引也是B+树结构,但其在叶子节点中除了包含键的值外,还包含了一个书签,这个书签用来告诉InnoDB引擎从哪里可以找到与索引相对应的行数据。由于InnoDB引擎是索引组织表,因此,这个书签就是相应的行数据的聚集索引键。辅助索引的叶子节点的 data 域记录着主键的值,因此在使用辅助索引进行查找时,需要先查找到主键值,然后再到主索引中进行查找。

哈希索引:InnoDB 引擎有一个特殊的功能叫“自适应哈希索引”,当某个索引值被使用的非常频繁时,会在 B+Tree 索引之上再创建一个哈希索引,这样就让 B+Tree 索引具有哈希索引的一些优点,比如快速的哈希查找。哈希索引能以 O(1) 时间进行查找,但具有以下限制:无法用于排序与分组;只支持精确查找,无法用于部分查找和范围查找,在有大量重复键值情况下,哈希索引的效率也是极低的,因为存在所谓的哈希碰撞问题基于哈希表来实现,一般用于精确匹配查询,它为每行数据的所有列都计算出一个哈希码,然后将哈希码存入索引中,同时保存指向每行数据的地址,这样就能精确查找。如果是等值查询,那么哈希索引明显有绝对优势。
全文索引:MyISAM 存储引擎支持全文索引,用于查找文本中的关键词,而不是直接比较是否相等。查找条件使用 MATCH AGAINST,而不是普通的 WHERE。全文索引一般使用倒排索引实现,它记录着关键词到其所在文档的映射。
索引的优点:1.减少了服务器需要扫描的数据行数。2.帮助服务器避免进行排序和创建临时表(B+Tree 索引是有序的,可以用来做 ORDER BY 和 GROUP BY 操作);3.将随机 I/O 变为顺序 I/O(B+Tree 索引是有序的,也就将相邻的数据都存储在一起)。
6.索引优化
1. 独立的列
在进行查询时,索引列不能是表达式的一部分,也不能是函数的参数,否则无法使用索引。例如下面的查询不能使用 actor_id 列的索引:
SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
2. 多列索引
当查询条件有多个字段时,单列索引和多列索引有很大的区别。如果使用多列索引,where条件中字段的顺序非常重要,需要满足最左前缀列。最左前缀:查询条件中的所有字段需要从左边起按顺序出现在多列索引中,查询条件的字段数要小于等于多列索引的字段数,中间字段不能存在范围查询的字段(<,like等),这样的sql可以使用该多列索引。例如下面的语句中,最好把 actor_id 和 film_id 设置为多列索引。
SELECT film_id, actor_ id FROM sakila.film_actorWHERE actor_id = 1 AND film_id = 1;
实例:现在我们想查出满足以下条件的用户id:SELECT `uid` FROM people WHERE lname`=’Liu  AND `fname`=’Zhiqun’ AND `age`=26 
A、单列索引: ALTER TABLE people ADD INDEX lname (lname);  将lname列建索引,这样就把范围限制在lname=’Liu’的结果集1上,之后扫描结果集1,产生满足fname=’Zhiqun’的结果集2,再扫描结果集2,找到 age=26的结果集3,即最终结果。由于建立了lname列的索引,与执行表的完全扫描相比,效率提高了很多,但我们要求扫描的记录数量仍旧远远超过了实际所需要的。 B、多列索引:ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);
  为了提高搜索效率,我们需要考虑运用多列索引,由于索引文件以B-Tree格式保存,所以我们不用扫描任何记录,即可得到最终结果。在mysql中执行查询时,只能使用一个索引,如果我们在lname,fname,age上分别建索引,执行查询时,只能使用一个索引,mysql会选择一个最严格(获得结果集记录数最少)的索引。
注意:项目里面查询使用了两个条件,因此如果要优化,必须使用多列索引,而且username字段要比applicationname字段的选择性强,同时使用最频繁,因此它应该位于最左边。C.最左前缀:最左优先,上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引,(lname,fname)组合索引以及(lname,fname,age)组合索引
最左前缀原则:联合索引(a,b,c),可以认为这三列都是排好序的,在a下b是有序的,在b下c是有序的,也就是说a=1返回的结果中b有序,此时可以利用索引查找b,b=1的结果中c有序,可以c索引来查找,但是一旦这顺序打破那么就只能是从左边起索引依此生效,也就是说如果要查a=1,c=2的记录那么就只有a生效,因为b没有生效,所以c不会生效。mysql创建复合索引的规则是首先对复合索引最左边的字段的数据进行排序,在此基础上,再对后面的字段进行排序,这样第一个字段是绝对有序的,后面的字段就是无序的了,一般情况下第二个字段进行条件判断是用不到索引的,可能出现type是index类型的,这就是mysql最左前缀的原因
3. 索引列的顺序
让选择性最强(where子句中使用最频繁的一列,且离散度大的)的索引列放在前面,索引的选择性是指:不重复的索引值和记录总数的比值。最大值为 1,此时每个记录都有唯一的索引与其对应。选择性越高,查询效率也越高。例如下面显示的结果中 customer_id 的选择性比 staff_id 更高,因此最好把 customer_id 列放在多列索引的前面。
SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)FROM payment;
staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049
4. 前缀索引
对于 BLOB、TEXT 和 VARCHAR 类型的列,必须使用前缀索引,只索引开始的部分字符,因为MySQL不允许索引这些列的完整长度。对于前缀长度的选取需要根据索引选择性来确定。缺点:mysql无法使用其前缀索引做ORDER BY和GROUP BY,也无法使用前缀索引做覆盖扫描。
5. 覆盖索引
索引包含所有需要查询的字段的值。即只需扫描索引而无须回表。
具有以下优点:
1.索引条目通常远小于数据行大小,只需要读取索引,则mysql会极大地减少数据访问量。 2.因为索引是按照列值顺序存储的,所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。 3.一些存储引擎如myisam在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系统调用。 4.innodb的聚簇索引,覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询)。
5.覆盖索引必须要存储索引列的值,而哈希索引、空间索引和全文索引不存储索引列的值,所以mysql只能用B-tree索引做覆盖索引。
二叉搜索树:每个节点最多拥有两个子节点、左子树的节点小于根节点、右子树的点大于根节点。
为何要有B树与B+树?
数据的处理一般是在内存中,因此在计算时间复杂度的时候一般考虑的是查找元素所需的比较次数,但是如果操作的数据集很大,大到内存也无法处理,那么需要将数据存储到外存中,对数据的处理需要不断从硬盘等存储设备中调入或调出内存页面,因此此时时间复杂度的计算考虑的是访问外部存储设备时间,尽量减少访问外部存储设备的次数可以有效提高查找效率(减少磁盘操作)。
B树就是为了减少磁盘操作次数的算法,B树与二叉树不同之处在于:
1)其每个节点有多个子节点,这样可以减少整个树的深度,进而减少磁盘IO操作
2)同时每个节点都有多个关键字,可以将有较大关联关系的关键字放在一个节点(或者说一个磁盘块中),这样可以减少查找时磁头来回移动的次数(磁盘查找耗时最多的是不同磁盘块的定位),同时还可以一次就将一个磁盘加载到内存。
聚集索引:表数据文件本身就是按B+Tree组织的一个索引结构(它的物理存放顺序和逻辑顺序一一对应),这棵树的叶节点data域就是数据页,因此保存了完整的数据记录。innodb主键索引是是用聚集索引来组织表,因此一个表中必须要有一个主键索引,如果没有设置聚集索引,默认使用主键来作为聚集索引。辅助索引,一个表中除聚集索引外,其它均为辅助索引(即二级索引),innodb中二级索引的叶子节点指向聚集索引,聚集索引来找到相应的行。
优点:1)适合范围查询(因为索引值都顺序排列的)。
2)查询更快(因为索引和数据放在一起,这样就不需要在进行磁盘查找了)。
缺点:1)不适合在经常需要变更的列上建立聚集索引,变更之后需要维护物理顺序。
非聚集索引:叶节点的data域存放的是指向数据记录的地址,数据存储在一个地方,索引存储在另一个地方,索引带有指针指向数据的存储位置,因此键值逻辑顺序与物理顺序不是一一对应的。
索引建立的原则:
1)查询频繁字段
2)区分度高字段
3)能覆盖所有查询条件的字段
使用索引来进行排序
mysql有两种方式可以生成有序的结果,一种是通过排序操作(Using filesort),另一种是按照索引来排序(Using index)。对于覆盖索引来说,查询完之后结果就是有序的,因此利用这一特点可以用来排序,这比使用排序操作更加快。
减少索引长度
1)压缩索引,myisam能够使用前缀压缩来减少索引的大小,其原理是首先完整保存索引块中第一个值,然后将其他值与第一个值比较得到相同前缀的字节数以及剩余的不同后缀部分,将这部分数据保存即可,这样就不用保存完整的索引值,这样可以减少索引的大小,让更多的索引加入内存中,压缩虽然使得索引大小减少了,但是每个索引前缀都依赖于第一个值,正序扫描效果较好,但是倒序或者随机查找效果就不好了。
2)使用索引前缀,如果索引列很长,可以只索引索引的前面部分,至于索引前面部分的多少字符则需要根据区分度来判断(比如url)。
使用如下语句可以检测出区分度
SELECT COUNT(DISTINCT LEFT(‘created’,10))/COUNT(*);//值越小,区分度越高
alter table city_demo add key (city(6));
注:如果左前缀不易区分,但是右边的容易区分(比如url),可以将列的内容倒过来存储,并建立前缀索引。
3)使用伪哈希函数来降低索引长度,方法就是另外创建一列,此列保存需要索引的列的hash值,此后就可以直接索引此列即可。
去掉冗余索引、重复索引以及未使用索引
所谓重复索引当然就是重复建在某一列的索引,而所谓冗余索引是指,多列索引的部分索引相同,那么相同部分的索引即为冗余索引。
二.SQL性能优化
A.数据处理分类
数据处理大致可以分成两大类:在线事务处理OLTP(on-line transaction processing)、在线分析处理OLAP(On-Line Analytical Processing)。OLTP是传统的关系型数据库的主要应用,主要是基本的、日常的事务处理,例如银行交易。OLAP是数据仓库系统的主要应用,支持复杂的分析操作,侧重决策支持,并且提供直观易懂的查询结果。OLTP 系统强调数据库内存效率,强调内存各种指标的命令率,强调绑定变量,强调并发操作;OLAP 系统则强调数据分析,强调SQL执行市场,强调磁盘I/O,强调分等。   
OLTP两个限制:磁盘子系统限制;CPU瓶颈(逻辑读总量与计算性函数或者是过程上);OLTP比较常用的设计与优化方式为Cache技术与B-tree索引技术。
OLTP:是指INSERT/UPDATE/DELETE 操作多于 SELECT(增删改多于查)
OLAP: 是指SELECT 操作多于 INSERT/UPDATE/DELETE(查多于增删改)
如何判断系统是OLTP还是OLAP应用?
1)自己估算查询操作与增删改的比较
2)使用show status来查看数据库的统计参数
show global status like ‘%com%’;会显示增删查改所有的数据量。
B.查询语句优化
首先使用 Explain 进行分析,然后选择优化策略
Explain 用来分析 SELECT 查询语句,可通过Explain 结果来优化查询语句。
比较重要的字段有:
  • select_type : 查询类型,有简单查询、联合查询、子查询等
  • key : 使用的索引
  • rows : 扫描的行数
注:explain的使用
explain主要看type字段:
null,system,const,eq_ref,ref,range,index,all
1)null:查询不需要访问表或索引
2)system:系统查询
3)const:常量查询,即在整个查询过程中最多只有一行匹配(主键查询)
4)eq_ref:唯一键索引
5)ref:非唯一索引访问
6) range:以范围的形式扫描
7) index:按索引次序扫描,先读索引,再读实际的行,结果还是全表扫描,主要优点是避免了排序。因为索引是排好的
8)all : 即全表扫描
1.保证不查询多余的列与行,热点数据实现缓存。
  • 只返回必要的列:最好不要使用 SELECT * 语句。
  • 只返回必要的行:使用 LIMIT 语句来限制返回的数据。
  • 缓存重复查询的数据:使用缓存可以避免在数据库中进行查询,特别在要查询的数据经常被重复查询时,缓存带来的查询性能提升将会是非常明显的。
2. 减少服务器端扫描的行数。
最有效的方式是使用索引来覆盖查询。主要是减少IO操作,如果是覆盖索引,可以不访问硬盘,查询效率更高。
3.重构查询方式
1). 切分大查询
一个大查询如果一次性执行的话,可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。
方式1:DELET FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);
方式2:
rows_affected = 0
do {
rows_affected = do_query(
“DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000”)
} while rows_affected > 0
2). 分解大连接查询
将一个大连接查询分解成对每一个表进行一次单表查询,然后将结果在应用程序中进行关联。
优点:1.让缓存更高效。对于连接查询,如果其中一个表发生变化,那么整个查询缓存就无法使用。而分解后的多个查询,即使其中一个表发生变化,对其它表的查询缓存依然可以使用。
2.分解成多个单表查询,这些单表查询的缓存结果更可能被其它查询使用到,从而减少冗余记录的查询。
3.减少锁竞争;
4.在应用层进行连接,可以更容易对数据库进行拆分,从而更容易做到高性能和可伸缩。
5.查询本身效率也可能会有所提升。
例如下面的例子中,使用 IN() 代替连接查询,可以让 MySQL 按照 ID 顺序进行查询,这可能比随机的连接要更高效。
SELECT * FROM tab
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag=’mysql’;
SELECT * FROM tag WHERE tag=’mysql’;
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);
4.其他方式优化
1)索引优化查询(大部分性能问题都能通过索引来解决)
1)检查索引是否被使用(就是检查有没有建立索引,explain语句可以进行检测)。
2)检查索引是否有必要使用(判断该列的选择性,选择性太小不用)。
3)检查优化器是否使用了索引(使用explain来分析)。
4)索引是否可以被进一步优化(比如说使用索引覆盖以及调整索引顺序)。
2)SPJ的查询优化
所谓SPJ是指:基于选择、投影、连接三种基本操作相结合的查询
其基本原则是对于选择、投影操作来说尽量将操作下推 ,所谓下推是指将操作下移到子查询中或者基表查询中这样做的好处是减少连接操作前的结果的行数或列数(也就是说减少连接操作前的数据量或者说减少中间数据量),进而减少连接操作所需的IO操作数以及数据操作量;对于连接操作来说就是要在不影响功能实现的前提下,通过调整表的连接顺序来提高查询效率。
3)子查询优化
所谓子查询是指一个查询是另一个查询一部分(也就是说查询语句中嵌套查询语句)
1)子查询合并,就是说对同一表的多个子查询合并在一次子查询中
这样可以把多次表扫描、多次连接减少为单次表扫描和单次连接。
2)子查询上拉,就是将一些子查询置于外层查询中,作为连接关系与外层查询连接(就是将子查询尽量优化为关联查询)。
5.查询条件优化
SQL查询语句中,对元组进行过滤和连接的表达式,形式上是出现在WHERE/JOIN-ON/HAVING的子句中的表达式称为条件。
1)条件下推,把与单个表相关的条件,放到对单表进行扫描的过程中执行。
例子:SELECT * FROM A, B WHERE A.a=1 and A.b=B.b;
执行顺序:1.扫描A表,并带有条件A.a=1,把A表作为嵌套循环的外表
2.扫描B表,执行连接操作,并带有过滤条件A.b=B.b
说明:数据库系统都支持条件下推,且无论条件对应的列对象有无索引,系统自动进行优化,不用人工介入。
2)条件化简,去除表达式中冗余的括号。
优点:可以减少语法分析时产生的AND和OR树的层次。—减少CPU的消耗
示例:((a AND b) AND (c AND d))
化简为a AND b AND c AND d
6.连接优化
连接消除,去掉不必要的连接中间表对象,则减少了连接操作。
例: SELECT a.*, c.* FROM a JOIN b ON (a1=b1) JOIN c ON (b1=c1);
相当于:SELECT A.*, C.* FROM A JOIN C ON (a1= c1);
非SPJ的查询优化
1)group by分组的下移:所谓下移指的是如果GROUPBY 操作可能较大幅度减小关系元组的个数,则把分组操作提前到子表中执行。
2 ) order by优化:
a.利用索引避免排序
b.排序下推。排序操作尽量下推到基表中,有序的基表进行连接后的结果符合排序的语义,这样能避免在最终的大的连接结果集上执行排序操作(在大表中进行排序会占用更多的内存)
3) COUNT()优化:
a. 简单优化:即逆向思维,正难则反
b. 使用近似值:有时候一些业务场景并不需要完全精确的count值,因此可以用近似值来代替,而explain估算出的行数就是一个不错的选择。
4)limit分页查询的优化
在分页查询时,在偏移量非常大的时候,例如可能是LIMIT 10000,20这样的查询,这时MySQL需要查询10020条记录后只返回最后20条,前面10000条记录都将被抛弃,这样的代价非常高。如果所有的页面被访问的频率都相同,那么这样的查询平均需要访问半个表的数据。要优化这种查询,可以有如下方法,当然关键还是使用索引。
a.限制分页数量,即超过多少页之后就不准查了。
b.延迟关联,所谓延迟关联是指将语句改写成先查询分页数据所对应的id,然后再做关联查询查出所需要的列,这样可以使用索引覆盖来扫描而不是查询所有列。
c. 记录上一次获取数据的位置或者直接计算分页的起点(要求主键递增的且没有空的),将LIMIT查询转换为已知的位置的查询,让MySQL通过范围扫描获得到对应的结果。
注:count(*)会使用索引来全表扫描。
三.数据库设计
1.字段设计
1 )在满足要求的前提下,尽量使用一些简单的数据类型以及更短的数据类型长度, (整型>date、time>char、varchar>blob)
主要考虑:整型、time运算快,节省空间;char、varchar要考虑字符集的转换以及排序需考虑校对集(在联表查询时更慢),速度慢;blob无法使用内存临时表。
2)尽量避免设计成可为null类型的列,因为可为null类型的列对于mysql来说优化更难,同时磁盘占据的空间其实更大。在innodb的compact行记录格式中要用单独的一个字节来标记为null的字段位置,在计划建索引的列上要设置为not null。查询时也要避免查询条件为null;
2.综合考虑范式化与反范式化
所谓范式是指我们在设计数据库时的一些指导原则。在设计过程中要混用范式与反范式,不能只遵循范式或反范式,当然适当的反范式化是必须的,这样加快查询速度。
1NF:强调的是列的原子性,即一个列不能再分成其它几列。
2NF:首先强调必须满足1NF,其次满足:一是表必须包含主键;而是非主键的列不能部分依赖主键,必须完全依赖。
3NF:首先遵循前面两个范式,同时任何非主属性不依赖其它非主属性(即非主属性之间不能相互依赖)。
范式化设计的优点:避免数据库冗余(在范式化设计中一个数据只出现一次,相反在反范式设计中数据可能会被冗余存储在不同的表中),减少数据库的空间且范式级别越高数据冗余越少。注:所谓的减少数据冗余是指通过新建一张表来代替原来那些数据重复出现的冗余字段。
缺点:等级越高设计出的表就越多,这样查询时就可能出现多表联合查询使得查询性能降低
反范式化:就是不满足范式化的模型,可以有适当的数据冗余,减少联合查询,实际就是使用空间来换取时间。
缺点:除了数据冗余外,还有插入删除问题,即如果要修改某个冗余字段同步修改其它表相同的冗余字段,这个操作比较麻烦(其实就是更新操作代价大,需要更新多个表中的冗余信息)。
3.使用缓存表和汇总表
所谓缓存表是指可以使用一张表来存储那些可以比较简单的从同一个databases其它表中得到的数据但是这些获取比较慢。
本站所有文章均由网友分享,仅用于参考学习用,请勿直接转载,如有侵权,请联系网站客服删除相关文章。若由于商用引起版权纠纷,一切责任均由使用者承担
极客文库 » 数据库基础复习之数据库优化

Leave a Reply

欢迎加入「极客文库」,成为原创作者从这里开始!

立即加入 了解更多