精华内容
下载资源
问答
  • 数据库索引结构

    2021-01-20 14:01:45
    概述本文主要描述基本的数据结构:LSM-Trees和B-tree,其中LSM-Trees构成了leveldb、rocksdb等的基础,B-tree是大多数关系型数据库的基础。我们先来看一个最简单的数据库的雏形:write() {echo "$1,$2" >>file...

    概述

    本文主要描述基本的数据结构:LSM-Trees和B-tree,其中LSM-Trees构成了leveldb、rocksdb等的基础,B-tree是大多数关系型数据库的基础。我们先来看一个最简单的数据库的雏形:

    write() {

    echo "$1,$2" >>file

    }

    read(){

    grep "^$1," file |sed -e "s/^$1,//" | tail -n 1

    }

    这个数据库的write功能是很简单但是很强大,因为它只需要append-only。与write类似,好多数据库内部使用append-only的log,大型数据库可能需要解决并发控制、磁盘加载和容错处理。

    这个数据库的read性能很差,每次查询都需要O(n)的时间。加索引可以解决这个问题。在本文我们会分析几种常见的索引,基本思想都是保存额外的metadata来辅助查询。如果你想通过多种方式来查询,你可能需要做不同的索引。

    索引一般来源于数据库的primary数据,这个不会影响数据库的内容,但是会带来额外的开销,尤其是对write操作,因为每次write都需要更新索引。所以索引是一个读写的折中:索引可以增加度的性能,但是会减少写的性能。这一般交给使用者来决定怎么加索引。

    哈希索引

    最简单的索引结构是key-value结构,类似于大多数语言里的HashMap结构。这种结构将数据的key和offset保存在内存里,真是数据可以存储在disk中,从而突破内存大小的限制。Bitcask就使用了这一结构,一次读根据索引只会有一次磁盘查询。如果数据刚好在缓存里,一次读根本不需要访问磁盘。Bitcask适合key总数小但是更新频繁的情况,否则内存无法承载所有索引。

    append-only结构存在超过磁盘大小的风险。一个解决方法是将log拆分成小的segment,当segment大小超过设置时再起一个新的segment来写。然后,我们可以对这些segment做compaction,旨在去除重复的key,保持每隔key的最新值。如下所示:

    segment 1

    a:1b:2c:3d:delete

    segment 2

    a:2b:1c:4d:5

    merged segment 1 and segment 2

    a:1b:2c:3

    compaction可以使segment更小,我们可以同时合并多个compaction。另外,segment是不可更改的文件,compaction不会影响正常的读写操作。当compaction在后台线程进行,使用旧的segment进行读。一旦合并操作完成,我们将读操作切换到新的segment,这个时候旧的segnent可以被删除了。

    这个时候,每个segment在内存中都有自己的hash table结构了。对于一个查询,我们先从最新的segment的hash table结构查询,如果没有再查询次新的hash table。由于合并过程使得segment个数变少,所以我们并不需要查询过多的文件。但是在实际生产中,还有许多细节要考虑:文件格式。csv并不是最好的格式,二进制格式会更快和更简单。二进制格式会首先编码value的大小,然后跟一个原始的字符串。

    删除数据。由于采用了append-only的方式,删除数据需要特殊标记,在compaction的时候再真正删除。

    宕机恢复。如果数据库重启,内存中的哈希索引需要丢失重建,如果从原始数据重建,由于数据量大会很慢,从而导致回复很慢。所以,需要定期的对索引做快照到disk。

    部分写操作。如果数据写了一半数据库宕机了,需要通过checksums来检查数据的完整性,对不完整的数据进行丢弃。

    并发控制。由于写操作具有严格的时序性,所以常见的实现方式是单线程来完成。由于segment不可更改,所以可以多线程共同读。

    为什么使用append-only而不是原地更新呢?原因如下:appending和compaction是顺序操作,远比随机写快得多,尤其是在磁性旋转盘硬盘驱动器上,在flash-based solid state drivers(SSDs)也是如此。

    并发控制和宕机恢复会很简单。比如,不使用append-only,宕机时你正在写,会导致写一半的状况,使得数据部分新部分旧。

    合并旧的segment避免了文件碎片。

    但是,此时的hash table也会有一些限制:hash table必须呆在内存里。当key数量过多时,无法全部保存在内存里。原则上,你可以将hash table保存在disk上,但是实际操作上比较困难,这会需要很多随机IO,当磁盘满了时很难扩充,hash碰撞也会比难处理。

    scan会很困难。比如扫描000-999之间的数据,实际上需要查看所有的hash table。

    下面我们会介绍不受这些约束的索引结构。

    SSTables和LSM-Trees

    在基于log结构的segment中,数据是一系列的key-value结构,这些数据对没有顺序可言。现在,我们要求这些数据对有序,这样的数据格式被称为SSTable(Sorted String Table)。另外,每个key在segment中要求只出现一次,compaction可以确保做到。SStable有很多优势,如下:合并segment简单且高效,即使是在文件大于内存的情况下。可以采用一种类似mergesort的算法(同时开始读取输入文件的第一个key,将最小的key拷贝到输出文件,repeat),从而产生一个新的segment。如果key出现在多个文件中,将使用最新的值。

    对于读操作,不需要在内存中记录所有的key,只需要记录一些粗力度的索引即可。比如,在一个segment中要查找一个key,可以先定位到具体的block(block是对segment进一步的划分),然后遍历即可。(如果所有的key-value大小一样,可以进行二分查找,但是实际情况每条数据大小并不相同)

    Block可以进行压缩,减少磁盘大小和磁盘IO。

    构建SSTable

    对于有序segment,依靠append-only是无法构建的,但是可以首先在内存中构建(采用红黑树、AVL树)。SSTable的构建过程如下:当来了一个write操作,将数据插入到内存的平衡树结构中(memtable)。

    当memtable大小超过设置(一般MB),将memtable写出到磁盘的SSTable。这可以很高效地做到,因为memtable是有序的。这个SSTable会成为最新的SSTable。在写memtable到SSTable的同时,可以令起一个实例来写memtable。

    当来了一个read操作,先在memtable中查询,再按照新旧顺序在SSTable中查询。

    后台有一个合并的compaction在合并文件。

    为了防止宕机丢memtable的数据,每个写操作需要append到一个disk上的log文件,用于宕机恢复。当memtable写到SSTable后,就可以将对应的log删除了。

    LSM-tree的由来

    构建SSTable的算法被使用在levelDB和RocksDB中,一种可嵌套key-value存储引擎。levelDB可以在Riak中替代Bitcask,类似的存储引擎被应用到Cassandra和HBase中,二者都是受谷歌BigTable论文的启发。

    最开始,这种索引结构出现在Partrick O'Neil et al.的Log-Structured Merge-Tree(LSM-Tree)中,所以后续的基于这种合并、compaction的有序文件都被称作LSM存储引擎。

    Lucene是Elasticsearch和Solr使用的全文本搜索技术,虽然全文本索引比较复杂,但是思想类似,它会保存一个key-list的结构,其中list中是所有包含key的文档ID。

    性能优化

    实际生产中有很多细节需要考虑以提升性能。例如,LSM-tree的算法在查找不存在的key时会很慢,因为需要遍历所有文件。针对这种问题,存储引擎使用了Bloom filters来优化,这是一种逼近所有内容的内存结构,它可以告诉你key是否存在。

    针对SSTable的compaction和合并,也有许多策略,最常见的是按照size-tiered和level。levelDB和RocksDB使用level的compaction,HBase使用size-tiered的,Cassandra都支持。在size-tiered中,新的和小的SSTable被合并成老的和大的SSTable。在level中,key 范围被分成更小的SSTable,老的数据被移到单独的level,从而允许增量compaction和减少磁盘空间。

    B-Trees

    前面讨论了基于log结构的索引,它们被广泛地接受,但是B-Tree还是当前使用最广泛的索引系统。自1970年被引入以来,它被广泛的应用在各种关系型和非关系型数据库中。LSM-Tree将数据库分成了更小的segment,采用顺序写的方式。B-Tree则相反,它将数据库切分成固定大小的blocks(或者叫page),一般是4KB大小(可能更大)。这种设计更贴合硬件系统,因为磁盘也是被组织成固定大小的block。

    B-Tree中每一页都有一个地址,允许其他页引用,类似于指针,但是存储在磁盘上。我们可以使用这些页引用构建一个树形页,如下图所示:

    如果要查询一个key,从根节点开始。页里包含了key和到子页的引用,每个引用负责的内容由它前后的key来指定。比如,我们要查找key=40的内容,先在根页中定位到30-60的索引,进入子页继续查询,就能查到key=40的内容。

    如果要更新一个已经存在的key,先定位到包含key的页,改变key的value,再将页写回到磁盘(任何引用都不会失效)。

    如果要插入一条新数据,需要首先找到能够包含新数据的页,并把新数据插入。如果没有足够的空间来插入,这个页会切分成两个页,同时更新父页。如下所示,插入F:

    这种算法确保了树的平衡性:n个key的树会有一个O(n)的深度。大部分数据库会有三到四层深度,比如一个4层树,每页大小4KB,每页引用为500,可以存储256TB的数据。

    使B-Tree可信赖

    B-Tree的写需要覆盖disk的旧页,地址不会变,其他页对它的引用也不会变。可以认为overwriting是一个硬件操作,在一般的磁盘中,先将磁盘头移到正确的位置,等待旋转盘上的正确位置出现,然后overwriting合适的sector。至于SSDs,会更复杂一些,因为SSDs必须擦除和重写存储芯片的一大块。LSM-trees则没有这个问题。

    有些操作可能需要同时更新多个页,比如上面提高的的插入分裂操作,这种操作必须确保原子性,否则宕机恢复后会出现孤立页的情况。B-Tree采用了一种额外的数据结构来保证——write-ahead log(WAL,或者叫做redo log),这是一种append-only的文件,需要在具体操作前写入。当出现宕机恢复时,这个log可以确保将B-Tree恢复到一致性的状态。

    并发访问也会造成问题。B-Tree采用了一个叫做latches的轻量锁来保证并发访问时的一致性。

    B-Tree优化

    B-Tree历史悠久,有很多优化,如下所示:一些数据库使用copy-on-write技术来取代WAL。一个更改页被写到另外的地方,同时在父页中创建一个针对此页的新版本。这种方法也可以用于并发控制。

    我们可以不存储完整的key,可以只存储key的缩写,只要能区分查找边界即可。

    一般来说,页可以存储在磁盘上的任意空间。对于跨页的scan,这种存储方式很低效。一些数据库尝试将相邻页存在一起。但是实际上做起来比较困难,随着树的增长。LSM-trees则没有这个问题。

    额外的数据结构允许子页指向父页,可以方便的过渡到相邻页。

    B-Tree中的fractal tree借鉴了日志结构的思想来减少磁盘seek。

    B-Tree与LSM-Tree的对比

    尽管B-Tree实现起来更自然,但是LSM-Tree也有一定的性能优势。总的来说,LSM-Tree写很快,B-Tree读很快。

    LSM-Tree的优势

    B-Tree每写一次数据需要额外再写一次WAL,每次更改需要重写整个页。当宕机恢复时,有可能还需要重写一次。LSM-Tree的compaction也会写多次数据。对数据库的一次写导致的多次写磁盘,被称作write amplification。在SSDs中,write amplification更需要关注,因为SSDs的block写是有寿命次数的。在写频繁的操作中,过大的write amplification会影响写性能。LSM-Tree相比B-Tree,能承载更高的写入量,一方面是因为具备更低的write amplification,另外一方面是因为顺序写更快,这种效果在非SSDs的普通磁盘上更加明显。

    LSM-Tree可以更好地被压缩,从而节省磁盘IO和磁盘容量。B-Tree在页变更时可能会导致磁盘碎片,导致有些空间不可利用。

    SSDs的某些固件采用了日志结构的算法来讲随机写转变成顺序写,更低的write amplification和更少的磁盘碎片显然对SSDs更有利。

    LSM-Tree的不足

    LSM-Tree的不足主要在于compaction可能会影响系统的性能。磁盘资源有限,很容易发生请求被阻塞在磁盘昂贵的compaction操作中。对吞吐量和平均反应时间的影响通常很小,但是在更高的百分位数上会很高,相比B-Trees的可预测性。

    当写入量很大时,compaction可能会对此造成影响。磁盘的写入带宽是有限的,这时需要初始写入(logging和flush)、compaction共享带宽。随着数据量的变大,compaction可能需要更多的磁盘带宽。

    如果写入量很大,但是compaction配置的不合理,会发生compaction跟不上写入量的情况,有可能会导致disk耗尽。通常, LSM-Tree不会限制写入吞吐,如果compaction跟不上写入量,需要用户检查并进行相应的配置。

    B-Trees的优势是key只存在一个地方,而LSM-Tree则可能多处存在相同的key。对于强事务性的数据库来说,选择B-Trees更好一些,更加容易加锁。

    B-Trees可以更好的保持数据一致性,所以不会轻易退出历史舞台。但是对一些新的数据库,LSM-Tree则变得越来越有吸引力。

    展开全文
  •    索引模型就是索引的实现形式(也可以理解为索引的数据结构),常见的索引模型有下面三种: 哈希表(散列表) 键值对形式(类似 Java 中的 HashMap) 优点:新增速度快; 缺点:无序,区间查询速度很慢(全表

    一、概述

        简单来说,索引的出现是为了提高查询效率,就像书的目录一样。MySQL 的索引是在「存储引擎」层实现的,因此没有统一的标准,同一种类型的索引,在不同存储引擎之间实现可能也不同。本文主要分析 InnoDB 存储引擎的索引结构,MySQl使用的就是InnDB引擎。

    二、索引模型

       索引模型就是索引的实现形式(也可以理解为索引的数据结构),常见的索引模型有下面三种:

    1. 哈希表(散列表)
      键值对形式(类似 Java 中的 HashMap)
      优点:新增速度快;
      缺点:无序,区间查询速度很慢(全表扫描)。
      适用场景:只有等值查询的情况(例如 Memcached 等一些 NoSQL 引擎)。

    2. 有序数组
      优点:等值查询和范围查询速度都很快。
      缺点:更新成本太高(插入的记录在中间时,需要移动后面的所有记录,可类比在数组中间位置插入元素的操作)。
      适用场景:静态存储引擎(比如不再修改的历史数据)。

    3. 搜索树(N 叉树)
      优点:读写快,适配磁盘的访问模式。
      B+ 树就是其中的一种,也是 InnoDB 存储引擎的索引模型。

    三、InnoDB 记录的存储结构

      1)数据页

    在 InnoDB 引擎中,会将数据划分为若干个「页」,「页」是磁盘和内存之间交互的基本单位,页的大小一般为 16KB。即:一般情况下,一次最少从磁盘中读取 16KB 的数据到内存中,一次至少把内存中 16KB 的数据刷新到磁盘中。

    向一个数据页中插入记录的过程如图所示:
    在这里插入图片描述

    数据页中分为几个部分,其中 User Records 部分为存储记录的空间(其他部分存储数据页的其他信息,这里暂不详述),插入过程大致如下:

    1. 未插入记录时,User Records 部分不存在;
    2. 当插入记录时,会从 Free Space 部分划分出空间存储记录;
    3. 当 Free Space 空间用完时,也就是该数据页的空间用完了,需要分配新的数据页存储(页分裂)。

      2)记录的结构

    在 InnoDB 引擎中,一条记录的存储结构如图所示:
    在这里插入图片描述

    PS: 其中橙色部分 (c1, c2, c3) 是表中的列,且 c1 为主键,下图亦是如此。
    也就是说,数据页中记录的数据,除了一条记录本身,还有变长字段列表、NULL 值列表、记录头信息等其他信息,这样才是在数据页中的一条完整记录。

    数据页中多条记录之间的关系示意图:
    在这里插入图片描述

    即,每个页中保存了许多条记录,并且每条记录指向下一条记录(根据主键顺序,类似单链表结构)。此外还记录了该页中的最小和最大记录(也是根据主键顺序)。

    不仅如此,这些记录还会几条(1~8)分为一个组,并且把组内最大的主键值提取到一个槽(slot)中,用来实现快速(二分)查找,示意图如下:
    在这里插入图片描述

      3)页内查找记录

    以上面的数据页为例,若要查找主键值为 5 的记录,过程如下(二分查找):

    1. 计算中间槽的位置:(0+4)/2=2,因此查找槽 2,而它对应记录的主键为 8,5<8,重新计算;
    2. 重新计算,(0+2)/2=1,查找槽 1,对应记录的主键值为 4,5>4,因此查找的记录在槽 2 中;
    3. 遍历槽 2 对应的分组,查找主键为 5 的记录。

    因此在一个数据页中查找指定主键值的记录过程大致分为两步:

    1. 通过二分查找确定记录所在的槽;
    2. 遍历该槽所在组中的各个记录(通过记录的 next_record)。
      由于槽内数据很少(不超过 8 条),因此遍历的成本较低。

    四、聚簇索引&二级索引

      1)聚簇索引(主键索引)

    在 InnoDB 存储引擎中,聚簇索引也称为「主键索引」,表都是根据主键顺序组织存放的,这种存储方式的表称为索引组织(Index Organized Table)表(索引即数据,数据即索引)。一张表只能有一个主键索引。

    聚簇索引的示意图如下(该结构就是一棵 B+ 树):
    在这里插入图片描述

    图中结构分为三层,其中上面的两层(即非叶子节点,页 33、页 30 和页 32)为索引层,保存的是索引信息;第三层(叶子节点)为数据层。在主键索引中,叶子节点保存的是完整的记录(以数据页为单位)。

    PS: 存储节点的空间可能是不连续的,但是,同一层的节点是有前后顺序的,它们之间以「双向链表」的形式连接。

    在索引树中查找一条记录的大致过程如下(仍以查找主键值为 5 的记录为例):

    1. 先查找根节点,即页 33,页 30 中的主键范围是 [1, 320),而页 32 中主键大于等于 320,因此定位到 页 30;
    2. 再查找页 30,同样的方法定位到页 28;
    3. 根据上面「页内查找记录」的方式在页 28 中查找。

      2)二级索引

    InnoDB 中,二级索引的叶子节点存储的是主键的值。二级索引也称为「非聚簇索引」、「非主键索引」。一张表可以有多个二级索引。其中,以单列作为二级索引的又称「单列索引」,以多列作为索引的又称「联合索引」或者「组合索引」。

    二级索引的示意图如下:
    在这里插入图片描述

    该结构与聚簇索引类似,也是一棵 B+ 树。
    与聚簇索引的不同之处主要在于第三层,也就是叶子节点,在二级索引中,叶子节点保存的是主键的值。

    二级索引中的查找过程与聚簇索引中查找类似。
    不同的是,由于二级索引保存的是索引列和主键列,若查找的数据包含索引和主键之外的内容,则需要先找出主键值,然后再根据主键的值到聚簇索引中查找完整记录,该过程称为「回表」。

    值得注意的是,上述查找都是在有索引的情况下进行的,如果没有索引呢?则会进行全表扫描,这样当数据量较大时,效率会非常低。这也是索引出现的主要原因。

      3)联合索引

    两个或更多个列上的索引被称作联合索引(复合索引)。联合索引可减少索引开销,以联合索引 (a,b,c) 为例,建立这样的索引相当于建立了索引 a、ab、abc 三个索引—— Mysql 从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分,而且当最左侧字段是常量引用时,索引就十分有效,这就是最左前缀原则。由最左前缀原则可知,组合索引是有顺序的,那么哪个索引放在前面就比较有讲究了。对于组合索引还有一个知识点——索引下推,假设有组合索引(a,b,c)有如下

      select * from table where a = xxx and b = xxx
    

    这个 sql 会进行两次筛选第一次查出 a=xxx 数据 再从 a=xxx 中查出 b=xxx 的数据。使用索引下推和不使用索引下推的区别在于不使用索引下推会先查出 a=xxx 数据的主键然后根据查询出的主键回表查询出全行数据,再在全行数据上查出 b=xxx 的数据;而索引下推的执行过程是先查出 a=xxx 数据的主键,然后在这些主键上二次查询 b=xxx 的主键,然后回表。

    索引下推的特点:

    innodb 引擎的表,索引下推只能用于二级索引
    索引下推一般可用于所查询字段不全是联合索引的字段,查询条件为多条件查询且查询条件子句字段全是联合索引。

      4)区别与联系(InnoDB 存储引擎)

    1. 聚簇索引和二级索引都需要占用磁盘空间,每一个索引都对应一棵索引树;
    2. 二者都是 B+ 树结构,数据都存储在叶子节点(非叶子节点不保存数据);
    3. 聚簇索引的叶子节点保存的是完整记录,二级索引保存的是主键的值;
    4. 在一张表中,聚簇索引只能有一个,二级索引可以有多个(即多个索引树)。

    根据这几点比较也可以发现,索引虽然可以提高查找效率,但也有缺点。如果有多个索引,当修改数据时索引也要同步进行更新,这样会降低操作的效率;而且索引也会占用磁盘空间。因此,索引并非越多越好。

      5)InnoDB 引擎主键选择

    在 InnoDB 中,每张表都有个主键(Primary Key),如果在建表时没有显式地定义主键,则 InnoDB 引擎会按照如下方式选择或创建主键:

    1. 首先判断表中是否有非空的唯一索引(Unique NOT NULL),若有,则该列即为主键(当表中有多个非空唯一索引时,InnoDB 存储引擎将选择建表时第一个定义的非空唯一索引为主键);

    2. 若不符合上述条件,InnoDB 存储引擎自动创建一个 6 字节大小的隐藏列(row_id)作为主键。

    因此,建表时最好显式指定主键。

      6)InnoDB 索引优缺点

    主要优缺点如下(可通过上述存储结构分析理解):

    优点

    1. 可以提高数据检索效率,降低数据库 IO 成本;
    2. 对记录进行排序,降低 CPU 消耗(被索引的列会自动进行排序),可以提高排序和分组查询的效率。

    缺点

    1. 索引会占用磁盘空间;
    2. 降低更新效率(更新操作会同步更新索引)。

      7)InnoDB 索引使用场景

    需要创建索引的场景

    1. 主键自动建立唯一索引;
    2. 频繁作为查询条件的字段应该创建索引;
    3. 多表关联查询中,关联字段应该创建索引(ON 两边都要创建);
    4. 查询中排序的字段,应该创建索引;
    5. 统计或者分组。

    不需要使用索引的场景

    1. 表记录太少;
    2. 频繁更新;
    3. 查询字段使用频率不高。

    PS: 这里只是概括了一些常见的优缺点和使用场景,可以根据前面对索引的结构和特点的分析对比理解。

      8)小结

    简单来说,索引可以理解为书的目录。
    索引的主要作用是为了提高查找效率;但索引也有缺点,并非越多越好,需要根据实际情况决定如何创建合适的索引。

    五、MySQL性能优化

      1)SQL优化的几个步骤

    1. 通过show status 命令了解各种 SQL 的执行效率
    show [session | global] status;
    

    可以根据需要加上参数来显示session级(当前连接,默认)和global级(自数据库上次启动至今)的统计结果。

    show status like 'Com_%';  ---显示当前连接所有统计参数的值。
    

    Com_xxx表示每个xxx语句执行的次数,通常需要注意的是下面几个参数:Com_select/Com_insert/Com_update/Com_delete。
    2. 定位执行效率较低的 SQL 语句
    通过show processlist命令实时查看当前 SQL 的执行情况;
    通过慢查询日志定位出现的问题。
    3. 通过explaindesc分析低效 SQL 的执行计划
    可以参考上篇文章Mysql 探索之 Explain 执行计划详解
    4. 通过show profile 分析 SQL。
    show profile 能帮我们了解时间都耗费到哪里去了。
    通过show profiles我们能够更清楚了解 SQL 执行的过程;
    5. 通过trace分析优化器如何选择执行计划
    MySQL5.6提供了对 SQL 的跟踪trace,能帮我们了解为什么优化器选择执行 A 计划而不是 B 计划,进一步理解优化器的行为。
    6. 确定问题并采取相应的优化措施。
    7.执行计划参数问题:
    在查询 sql 之前加上 explain 可查看该条 sql 的执行计划,如:

     EXPLAIN SELECT id,name FROM table where id = 123
    

    这条 sql 会返回这样一个表:
    在这里插入图片描述这个表便是 sql 的执行计划,我们可以通过分析这个执行计划来知道我们 sql 的运行情况。现对各列进行解释:

    1)id:查询中执行 select 子句或操作表的顺序。

    2)select_type:查询中每个 select 子句的类型(简单 到复杂)包括:

    SIMPLE:查询中不包含子查询或者UNION;
    PRIMARY:查询中包含复杂的子部分;
    SUBQUERY:在SELECT或WHERE列表中包含了子查询,该子查询被标记为SUBQUERY;
    DERIVED:衍生,在FROM列表中包含的子查询被标记为DERIVED;
    UNION:若第二个SELECT出现在UNION之后,则被标记为UNION;
    UNION RESULT:从UNION表获取结果的SELECT被标记为UNION RESULT;
    3) type:表示 MySQL 在表中找到所需行的方式,又称“访问类型”,包括:

    ALL:Full Table Scan, MySQL 将遍历全表以找到匹配的行;
    index:Full Index Scan,index 与 ALL 区别为 index 类型只遍历索引树;
    range:索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于 between < > 等查询;
    ref:非唯一性索引扫描,返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找;
    eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描;
    onst 和 system:当 MySQL 对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于 where 列表中,MySQL 就能将该查询转换为一个常量,system 是 const 类型的特例,当查询的表只有一行的情况下, 使用 system;
    NULL:MySQL 在优化过程中分解语句,执行时甚至不用访问表或索引。
    4)possible_keys:指出 MySQL 能使用哪个索引在表中找到行,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用。

    5)key:显示 MySQL 在查询中实际使用的索引,若没有使用索引,显示为 NULL。

    6)key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。

    7)ref:表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值。

    8)rows:表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值。

    9)Extra:其他重要信息 包括:

    Using index:该值表示相应的 select 操作中使用了覆盖索引;
    Using where:MySQL 将用 where 子句来过滤结果集;
    Using temporary:表示 MySQL 需要使用临时表来存储结果集,常见于排序和分组查询;
    Using filesort:MySQL 中无法利用索引完成的排序操作称为“文件排序”。

      2)MySQL 常用的 SQL 语句优化方法

    1. 应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
    2. 对查询进行优化,应尽量避免全表扫描,首先应考虑在 whereorder by 涉及的列上建立索引。
    3. 应尽量避免在where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描。如:
    select id from t where num is null
    
     可以在 `num` 上设置默认值 0,确保表中 `num` 列没有 null 值,然后这样查询:
    
     select id from t where num=0
    
    1. 避免在where子句中使用or来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描。
    2. 前导模糊查询将导致全表扫描
    select id from t where name like%c%
     下面使用索引
    
     select id from t where name like ‘c%
    1. not in 也要慎用,否则会导致全表扫描;对于连续的数值,能用between 就不要用 in 了,尽量使用exists代替in
    2. 如果在 where 子句中使用参数,也会导致全表扫描。因为 SQL 只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:
       select id from t where num=@num
    
     可以改为强制查询使用索引:
    
      select id from t with(index(索引名)) where num=@num
    
    1. 应尽量避免在 where 子句中对字段进行表达式与函数或其他表达式运算操作,这将导致引擎放弃使用索引而进行全表扫描。如:
    select id from t where num/2=100,应改为:select id from t where num=100*2
    
    select id from t where substring(name,1,3)='abc';name以abc开头的id,应改为:select id from t where name like 'abc%'
    
    select id from t where datediff(day,createdate,'2005-11-30')=0'2005-11-30′生成的 id,应改为:select id from t where createdate>=’2005-11-30′ and createdate<'2005-12-01'
    
    1. Update 语句,如果只更改 1、2 个字段,不要 Update 全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。
    2. 在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。
    3. 并不是所有索引对查询都有效,SQL 是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL 查询可能不会去利用索引。如一表中有字段 sex,male、female 几乎各一半,那么即使在 sex 上建了索引也对查询效率起不了作用。
    4. 索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insertupdate 的效率,因为 insertupdate 时有可能会重建索引。一个表的索引数较好不要超过 6 个。
    5. 应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。
    6. 尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
    7. 任何地方都不要使用 select * from t ,用具体的字段列表代替*,不要返回用不到的任何字段。
    8. 对于多张大数据量(这里几百条就算大了)的表JOIN,要先分页再JOIN,否则逻辑读会很高,性能很差。
    9. 尽量使用表变量来代替临时表。
    10. 考虑使用临时表暂存中间结果。临时表并不是不可使用,适当地使用它们可以使某些查询更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。将临时结果暂存在临时表,后面的查询就在tempdb中查询了,这可以避免程序中多次扫描主表,也大大减少了程序执行中共享锁阻塞更新锁,减少了阻塞,提高了并发性能。但是,对于一次性事件,较好使用导出表。
    11. 在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert
    12. 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。
    13. 避免频繁创建和删除临时表,以减少系统表资源的消耗。
    14. 尽量避免使用游标,因为游标的效率较差。与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。
    15. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF
    16. 尽量避免向客户端返回大数据量。
    17. 尽量避免大事务操作,提高系统并发能力。
    18. where子句替换Having子句
      避免使用 having 子句,having 只会在检索出所有记录之后才会对结果集进行过滤,这个处理需要排序,如果能通过 where 子句限制记录的数目,就可以减少这方面的开销。on、where、having 这三个都可以加条件的子句,on 是最先执行,where 次之,having 最后。
    19. 使用 Truncate 替代 delete
      当需要删除全表的记录时使用Truncate替代delete。在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE 会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用 TRUNCATE 时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短。
    20. 使用表的别名:
      当在 SQL 语句中连接多个表时, 请使用表的别名并把别名前缀于每个 Column 上.这样一来,就可以减少解析的时间并减少那些由 Column 歧义引起的语法错误。
    21. 使用union all 替换 union
      当 SQL 语句需要union两个查询结果集合时,这两个结果集合会以 union all 的方式被合并,然后再输出最终结果前进行排序。如果用 union all 替代料 union,这样排序就不是不要了,效率就会因此得到提高. 需要注意的是,UNION ALL 将重复输出两个结果集合中相同记录。
    22. where 替代 order by
      ORDER BY 子句只在两种严格的条件下使用索引:①ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序;②ORDER BY中所有的列必须定义为非空;
    	低效: (索引不被使用)SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE
    	高效: (使用索引)SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0
    
    1. 避免索引列的类型转换:
      假设 EMP_TYPE 是一个字符类型的索引列.SELECT … FROM EMP WHERE EMP_TYPE = 123这个语句被转换为:SELECT … FROM EMP WHERE EMP_TYPE='123'; 因为内部发生的类型转换, 这个索引将不会被用到! 为了避免 ORACLE 对你的 SQL 进行隐式的类型转换, 最好把类型转换用显式表现出来. 注意当字符和数值比较时, ORACLE 会优先转换数值类型到字符类型。
    2. 优化 Group by
     	提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉。下面两个查询返回相同结果但第二个明显就快了许多。
    	低效:SELECT JOB , AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER'
    	高效:SELECT JOB , AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP by JOB
    
    1. 避免使用耗费资源的操作:
      带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的 SQL 语句会启动 SQL 引擎执行耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序. 通常, 带有UNION, MINUS , INTERSECT的 SQL 语句都可以用其他方式重写. 如果你的数据库的 SORT_AREA_SIZE 调配得好, 使用 UNION , MINUS, INTERSECT 也是可以考虑的, 毕竟它们的可读性很强。
    2. 在运行代码中,尽量使用PreparedStatement来查询,不要用Statement

      3)MySQL 常用的索引优化方法

    1. 尽量少 join

    MySQL 的优势在于简单,但这在某些方面其实也是其劣势。MySQL 优化器效率高,但是由于其统计信息的量有限,优化器工作过程出现偏差的可能性也就更多。对于复杂的多表 Join,一方面由于其优化器受限,再者在 Join 这方面所下的功夫还不够,所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离。但如果是简单的单表查询,这一差距就会极小甚至在有些场景下要优于这些数据库前辈。

    1. 尽量少排序

    排序操作会消耗较多的 CPU 资源,所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL 的响应时间。对于 MySQL 来说,减少排序有多种办法,比如:上面误区中提到的通过利用索引来排序的方式进行优化;减少参与排序的记录条数;非必要不对数据进行排序。

    1. 尽量避免 select *

    很多人看到这一点后觉得比较难理解,上面不是在误区中刚刚说 select 子句中字段的多少并不会影响到读取的数据吗?是的,大多数时候并不会影响到 IO 量,但是当我们还存在 order by 操作的时候,select 子句中的字段多少会在很大程度上影响到我们的排序效率,此外,上面误区中不是也说了,只是大多数时候是不会影响到 IO 量,当我们的查询结果仅仅只需要在索引中就能找到的时候,还是会极大减少 IO 量的。

    1. 尽量用 join 代替子查询

    虽然 Join 性能并不佳,但是和 MySQL 的子查询比起来还是有非常大的性能优势。

    1. 尽量少 or

    当 where 子句中存在多个条件以“或”并存的时候,MySQL 的优化器并没有很好的解决其执行计划优化问题,再加上 MySQL 特有的 SQL 与 Storage 分层架构方式,造成了其性能比较低下,很多时候使用 union all 或者是union(必要的时候)的方式来代替or会得到更好的效果。

    1. 尽量用 union all 代替 union

    union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作,这就会涉及到排序,增加大量的 CPU 运算,加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候,尽量使用 union all 而不是 union。

    1. 尽量早过滤

    这一优化策略其实最常见于索引的优化设计中(将过滤性更好的字段放得更靠前)。在 SQL 编写中同样可以使用这一原则来优化一些 Join 的 SQL。比如我们在多个表进行分页数据查询的时候,我们最好是能够在一个表上先过滤好数据分好页,然后再用分好页的结果集与另外的表 Join,这样可以尽可能多的减少不必要的 IO 操作,大大节省 IO 操作所消耗的时间。

    1. 避免类型转换

    这里所说的“类型转换”是指 where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换

    1. 优先优化高并发的 SQL,而不是执行频率低某些“大”SQL

    对于破坏性来说,高并发的 SQL 总是会比低频率的来得大,因为高并发的 SQL 一旦出现问题,甚至不会给我们任何喘息的机会就会将系统压跨。而对于一些虽然需要消耗大量 IO 而且响应很慢的 SQL,由于频率低,即使遇到,最多就是让整个系统响应慢一点,但至少可能撑一会儿,让我们有缓冲的机会。

    1. 从全局出发优化,而不是片面调整

    SQL 优化不能是单独针对某一个进行,而应充分考虑系统中所有的 SQL,尤其是在通过调整索引优化 SQL 的执行计划的时候,千万不能顾此失彼,因小失大。

    1. 尽可能对每一条运行在数据库中的 SQL 进行 explain

    优化 SQL,需要做到心中有数,知道 SQL 的执行计划才能判断是否有优化余地,才能判断是否存在执行计划问题。在对数据库中运行的 SQL 进行了一段时间的优化之后,很明显的问题 SQL 可能已经很少了,大多都需要去发掘,这时候就需要进行大量的 explain 操作收集执行计划,并判断是否需要进行优化。

      4)MySQL 数据库的表结构优化

    1. 数据库操作中最为耗时的操作就是 IO 处理,大部分数据库操作 90% 以上的时间都花在了 IO 读写上面。所以尽可能减少 IO 读写量,可以在很大程度上提高数据库操作的性能。我们无法改变数据库中需要存储的数据,但是我们可以在这些数据的存储方式方面花一些心思。下面的这些关于字段类型的优化建议主要适用于记录条数较多,数据量较大的场景,因为精细化的数据类型设置可能带来维护成本的提高,过度优化也可能会带来其他的问题:

    数字类型:非万不得已不要使用DOUBLE,不仅仅只是存储长度的问题,同时还会存在精确性的问题。同样,固定精度的小数,也不建议使用DECIMAL,建议乘以固定倍数转换成整数存储,可以大大节省存储空间,且不会带来任何附加维护成本。对于整数的存储,在数据量较大的情况下,建议区分开 TINYINT / INT / BIGINT 的选择,因为三者所占用的存储空间也有很大的差别,能确定不会使用负数的字段,建议添加 unsigned 定义。当然,如果数据量较小的数据库,也可以不用严格区分三个整数类型。

    int类型只增主键字段=>4 字节=>每个字节 8 位=>32 位,在 CPU 加载一条指令的时候,4 字节是和 CPU 寄存器的运算有关,如:64 位,由于之前的系统一般都是 32 位的,所以在运算 4 字节的数据是刚好的,效率最高,而现今我们系统基本都是 64 位的时候,其实没有更好的利用好 CPU 运算,所以在设计表字段建议,使用 8 字节的主键bigint,而不是直接使用 int 来做主键。

    字符类型:非万不得已不要使用 TEXT 数据类型,其处理方式决定了他的性能要低于 char 或者是 varchar 类型的处理。定长字段,建议使用 CHAR 类型,不定长字段尽量使用 VARCHAR,且仅仅设定适当的最大长度,而不是非常随意的给一个很大的最大长度限定,因为不同的长度范围,MySQL 也会有不一样的存储处理。char(10) 不管该字段是否存储数据,都占 10 个字符的存储空间,char(10) 同时存在一个坑,就是存储 abc 数据后改数据库字段的值为“abc 7 个空格 ”,在精准查询(where)就必须带上后面的 7 个空格。varchar 不存的时候不占空间,存多长数据就占多少空间。

    时间类型:尽量使用TIMESTAMP类型,因为其存储空间只需要 DATETIME 类型的一半。对于只需要精确到某一天的数据类型,建议使用 DATE 类型,因为他的存储空间只需要 3 个字节,比 TIMESTAMP 还少。不建议通过 INT 类型类存储一个 unix timestamp 的值,因为这太不直观,会给维护带来不必要的麻烦,同时还不会带来任何好处。

    ENUM & SET:对于状态字段,可以尝试使用 ENUM 来存放,因为可以极大的降低存储空间,而且即使需要增加新的类型,只要增加于末尾,修改结构也不需要重建表数据。如果是存放可预先定义的属性数据呢?可以尝试使用 SET 类型,即使存在多种属性,同样可以游刃有余,同时还可以节省不小的存储空间。

    LOB类型`:强烈反对在数据库中存放 LOB 类型数据,虽然数据库提供了这样的功能,但这不是他所擅长的,我们更应该让合适的工具做他擅长的事情,才能将其发挥到极致。在数据库中存储 LOB 数据就像让一个多年前在学校学过一点 Java 的营销专业人员来写 Java 代码一样。

    字符编码:字符集直接决定了数据在 MySQL 中的存储编码方式,由于同样的内容使用不同字符集表示所占用的空间大小会有较大的差异,所以通过使用合适的字符集,可以帮助我们尽可能减少数据量,进而减少 IO 操作次数。① 纯拉丁字符能表示的内容,没必要选择 latin1 之外的其他字符编码,因为这会节省大量的存储空间;② 如果我们可以确定不需要存放多种语言,就没必要非得使用 UTF8 或者其他 UNICODE 字符类型,这回造成大量的存储空间浪费;③MySQL 的数据类型可以精确到字段,所以当我们需要大型数据库中存放多字节数据的时候,可以通过对不同表不同字段使用不同的数据类型来较大程度减小数据存储量,进而降低 IO 操作次数并提高缓存命中率。

    适当拆分:有些时候,我们可能会希望将一个完整的对象对应于一张数据库表,这对于应用程序开发来说是很有好的,但是有些时候可能会在性能上带来较大的问题。当我们的表中存在类似于 TEXT 或者是很大的 VARCHAR 类型的大字段的时候,如果我们大部分访问这张表的时候都不需要这个字段,我们就该义无反顾的将其拆分到另外的独立表中,以减少常用数据所占用的存储空间。这样做的一个明显好处就是每个数据块中可以存储的数据条数可以大大增加,既减少物理 IO 次数,也能大大提高内存中的缓存命中率。

    1. 上面几点的优化都是为了减少每条记录的存储空间大小,让每个数据库中能够存储更多的记录条数,以达到减少 IO 操作次数,提高缓存命中率。下面这个优化建议可能很多开发人员都会觉得不太理解,因为这是典型的反范式设计,而且也和上面的几点优化建议的目标相违背。

    适度冗余:为什么我们要冗余?这不是增加了每条数据的大小,减少了每个数据块可存放记录条数吗?确实,这样做是会增大每条记录的大小,降低每条记录中可存放数据的条数,但是在有些场景下我们仍然还是不得不这样做:① 被频繁引用且只能通过 Join 2 张(或者更多)大表的方式才能得到的独立小字段:这样的场景由于每次 Join 仅仅只是为了取得某个小字段的值,Join 到的记录又大,会造成大量不必要的 IO,完全可以通过空间换取时间的方式来优化。不过,冗余的同时需要确保数据的一致性不会遭到破坏,确保更新的同时冗余字段也被更新。

    尽量使用 NOT NULL:NULL 类型比较特殊,SQL 难优化。虽然 MySQL NULL 类型和 Oracle 的 NULL 有差异,会进入索引中,但如果是一个组合索引,那么这个 NULL 类型的字段会极大影响整个索引的效率。很多人觉得 NULL 会节省一些空间,所以尽量让 NULL 来达到节省 IO 的目的,但是大部分时候这会适得其反,虽然空间上可能确实有一定节省,倒是带来了很多其他的优化问题,不但没有将 IO 量省下来,反而加大了 SQL 的 IO 量。所以尽量确保 DEFAULT 值不是 NULL,也是一个很好的表结构设计优化习惯。

    六、总结

    数据库最常用的优化方式有:SQL 语句和索引、数据库表结构、系统配置、硬件。
    优化效果:SQL 语句和索引 > 数据库表结构 > 系统配置 > 硬件,但成本从低到高。
    数据库的优化方法小结:

    1. 设计符合范式的数据库
    2. 选择合适的存储引擎
    3. SQL 语句优化
    4. 索引优化:高分离字段建立索引
    5. SQL 表结构、字段优化
    6. 数据库参数优化:IO 参数、CPU 参数
    7. 分库分表:垂直切分与水平切分
    8. 分区:将表的数据按照特定的规则放在不同的分区,提高磁盘的 IO 效率,提高数据库的性能
      主从复制与读写分离:三个主要线程与 bin-log 文件、relay_log 文件,主数据库负责写操作,从数据库 负责读操作
    9. 负载均衡
    10. 数据库集群
    11. 硬件

    参考:不才陈某 码猿技术专栏、 WriteOnRead

    展开全文
  • MySQL数据库索引详解

    2021-01-18 21:45:26
    一、什么是索引索引是一个排序的列表,在这个列表中存储着索引值和包含这个值的数据所在行的物理地址,在数据十分庞大的时候,索引可以大大加快查询速度,这是因为使用索引后可以不用全表扫描来定位某行的数据,...

    一、什么是索引:

    索引是一个排序的列表,在这个列表中存储着索引值和包含这个值的数据所在行的物理地址,在数据十分庞大的时候,索引可以大大加快查询速度,这是因为使用索引后可以不用全表扫描来定位某行的数据,而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

    索引通过不断缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机事件变成顺序事件,也就是说,有个这个机制我们可以总是用同一种查找方式来锁定数据;

    索引实际就是一张表,该表保存了主键和索引字段,并指向实体表的记录,所以索引也是占了一大部分空间,不可能存储在内存中,因此索引往往都是以文件形式存储在我们的硬盘上。

    在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址,在数据十分庞大的时候,索引可以大大加快查询的速度,这是因为使用索引后可以不用扫描全表来定位某行的数据,而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。

    索引通过不断地缩小想要获取数据的范围来筛选出最终想要的结果,同时把随机事件变成顺序事件,也就是说,有个这个机制我们可以总是用同一种查找方式来锁定数据;

    索引实际就是一张表,该表保存了主键和索引字段,并指向实体表的记录,所以索引也是占了一大部分空间,不可能存储在内存中,因此索引往往都是以文件形式存储在硬盘上。

    二、索引的分类:

    常见索引有主键索引、唯一索引、普通索引、全文索引和组合索引。

    1、主键索引

    主索引,根据pk_clolum(length)建立索引,不允许重复,不允许空值;

    ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col');

    2、唯一索引

    用来建立索引的列的值必须是唯一的,允许空值;

    ALTER TABLE ‘table_name' ADD UNIQUE index_name('col');

    3、普通索引

    用表中的普通列构建的索引,没有任何限制;

    ALTER TABLE 'table_name' ADD INDEX index_name('col');

    4、全文索引

    用大文本对象的列构建的索引;

    ALTER TABLE 'table_name' ADD INDEXD index_name('col');

    5、组合索引

    用多个列组合构建的索引,这多个列中的值不允许有空值;

    ALTER TABLE 'table_name' ADD INDEX index_name('col');

    三、SQL查询计划详解:

    我们可以在select语句前添加explain来查询MySQL的执行计划,下面是对explain的具体说明:

    id

    SELECT识别符。这是SELECT的查询序列号

    select_type

    查询类型

    SIMPLE:简单的select(不使用UNION或子查询)

    PRIMARY:最外面的select

    UNION:UNION中第二个或后面的SELECT语句

    DEPEDENT UNION:UNION中第二个或后面的SELECT语句,取决于外面的查询

    UNION RESULT:UNION的结果

    SUBQUERY:子查询中的第一个SELECT

    DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询

    DERIVED:导出表的SELECT(FROM字句的子查询)

    table

    输出行所引用的表

    type

    联接类型

    system:表仅有一行(=系统表)

    const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次

    eq_ref:对于每个来自前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型

    ref:对于每个来自前面的表的行组合,所有有匹配索引值的行将从这张表中读取

    ref_or_null:该联接类型同ref,但是添加了MySQL可以专门搜索包含NULL值的行

    index_merge:该联接类型表示使用了索引合并优化方法

    unique_subquery:该类型替换了下面形式的IN子查询的ref:value In (SELECT primary_key FROM single_table WHERE some_expr)unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高

    index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:value IN (SELECT key_column FROM single_table WHERE some_expr)

    rang:只检索指定范围的行,使用一个索引来选择行

    index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小

    ALL:对于每个来自先前的表的行组合,进行完整的表扫描

    possible_keys

    指出MySQL能使用哪个索引在该表中找到行

    key

    显示MySQL实际使用的键(索引)。如果没有选择索引,键是NULL

    key_len

    显示MySQL决定使用的键长度。如果没有选择索引,则长度为NULL

    ref

    显示使用哪个列或常数与key一起从表中选择行

    rows

    显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数

    filtered

    显示了通过条件过滤出的行数的百分比估计值

    Extra

    MySQL解决查询的详细信息

    Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行

    Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的行组合在该表内检查更多的行

    range checked for each record(index map:#):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用

    Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行

    Using index:从只使用索引树中的信息而不需要进一步索引读取实际的行来检索表中的列信息

    Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果

    Using where:WHERE字句用于限制哪一个匹配下一个表或发送到客户

    Using sort_union(...),Using union(...),Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描

    Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查询GROUP BY 或DISTINCT查询的所有列,而不要额外索引硬盘访问实际的表

    type显示的是访问类型,是较为重要的一个指标,其中结果值从好到坏依次是:system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

    一般我们在查询的时候至少保证到range级别,最后达到ref。

    四、索引失效

    1、前导模糊查询不能利用索引(like '%xxx' 或like '%xxx%')

    2、使用or连接

    3、使用不等号(!= 或者<>)

    4、空值判断(is null 或者is not null)

    5、varchar类型在条件查询时未加引号,导致隐式转换

    6、查询所有(select *)

    7、在索引列进行操作(计算、函数、类型转换),会导致索引失效转向全表扫描

    8、使用not in 或者not exists

    9、如果是左外连接或右外连接查询时,两张表的关联字段编码格式不一样

    10、如果联合索引KEY 'name_index' ('name','code') Using BTREE没有用到第一个索引字段,则不会走索引

    11、使用between and

    12、当变量是times类型,而字段是date类型,索引失效;或相反情况也一样失效

    13、时间类型和varchar类型的索引比较

    展开全文
  • 本篇总结的是 《如何查看MySQL数据库状态及信息》,后续会每日更新~ 关于《Redis入门到精通》、《并发编程》、《Java全面入门》、《鸿蒙开发》等知识点可以参考我的往期博客 相信自己,越活越坚强,活着就该...
    • 备战2022春招或暑期实习,本专栏会持续输出MySQL系列文章,祝大家每天进步亿点点!文末私信作者,我们一起去大厂
    • 本篇总结的是 《如何查看MySQL数据库状态及信息》,后续会每日更新~
    • 关于《Redis入门到精通》、《并发编程》、《Java全面入门》、《鸿蒙开发》等知识点可以参考我的往期博客
    • 相信自己,越活越坚强活着就该逢山开路,遇水架桥!生活,你给我压力,我还你奇迹!

    目录

    1、简介

    2、正文

    2.1 查看所有数据库

    2.2 查看正在使用的数据库

    2.3 查看当前数据库中的表

    2.5 显示授权用户及其权限

    2.6 查看服务器错误或警告信息

    2.7 查看建库语句和建表语句

    2.8 查看数据库端口

    2.9 查看数据库索引大小

    2.10 查看数据库大小

    2.11 查看最大连接数

    2.12 查看当前线程相关信息

    2.13 查看文件存储路径

    2.14 查看数据库编码

    2.15 帮助指令


    1、简介

    现如今我们操作数据库,都是使用现成的数据库管理工具,比如Navicat、SQLyog等等。这些工具能够提供可视化操作界面,大大的提升了我们的操作效率,但是这样往往让我们忘却了很多数据库语句,比如建表语句、查询数据库信息等。这篇文章我们学习几个非常有用的语句,便于在没有可视化工具的时候给到你帮助。

    2、正文

    2.1 查看所有数据库

    mysql> SHOW DATABASES;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | liziba             |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)
    

    image.png

    2.2 查看正在使用的数据库

    此时未选择数据库,显示null

    mysql> SELECT DATABASE();
    +------------+
    | DATABASE() |
    +------------+
    | NULL       |
    +------------+
    1 row in set (0.00 sec)
    

    选择数据库liziba

    mysql> USE liziba;
    Database changed
    mysql> SELECT DATABASE();
    +------------+
    | DATABASE() |
    +------------+
    | liziba     |
    +------------+
    1 row in set (0.00 sec)
    

    image.png

    2.3 查看当前数据库中的表

    mysql> SHOW TABLES;
    +------------------+
    | Tables_in_liziba |
    +------------------+
    | user             |
    +------------------+
    1 row in set (0.00 sec)
    

    image.png

    2.4 查看表的列信息

    mysql> SHOW COLUMNS FROM user;
    +-------+--------------+------+-----+---------+----------------+
    | Field | Type         | Null | Key | Default | Extra          |
    +-------+--------------+------+-----+---------+----------------+
    | id    | bigint(20)   | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(255) | NO   |     | NULL    |                |
    | age   | int(11)      | NO   |     | NULL    |                |
    | sex   | smallint(6)  | NO   |     | NULL    |                |
    +-------+--------------+------+-----+---------+----------------+
    4 rows in set (0.00 sec)
    

    image.png

    2.5 显示授权用户及其权限

    查看所有用户的授权信息:

    mysql> SHOW GRANTS;
    

    image.png

    查看具体用户的授权信息:

    SHOW GRANTS FOR 'root'@'localhost';
    

    2.6 查看服务器错误或警告信息

    查看错误信息,这里我将ERRORS打成ERROES,服务器会产生一条错误日志

    mysql> SHOW ERROES;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ERROES' at line 1
    mysql>
    mysql>
    mysql> SHOW ERRORS;
    +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Level | Code | Message                                                                                                                                                  |
    +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ERROES' at line 1 |
    +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    

    image.png

    mysql> SHOW WARNINGS;
    +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Level | Code | Message                                                                                                                                                    |
    +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WARRINGS' at line 1 |
    +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 s
    

    image.png

    2.7 查看建库语句和建表语句

    查看liziba数据库的建库语句

    mysql> SHOW CREATE DATABASE liziba;
    +----------+-----------------------------------------------------------------+
    | Database | Create Database                                                 |
    +----------+-----------------------------------------------------------------+
    | liziba   | CREATE DATABASE `liziba` /*!40100 DEFAULT CHARACTER SET utf8 */ |
    +----------+-----------------------------------------------------------------+
    1 row in set (0.00 sec)
    

    image.png

    查看user 表的建表语句,注意提前使用use xxx

    mysql> SHOW CREATE TABLE user;
    +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                                                                                                                                                     |
    +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | user  | CREATE TABLE `user` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
      `name` varchar(255) NOT NULL COMMENT '用户名',
      `age` int(11) NOT NULL COMMENT '年龄',
      `sex` smallint(6) NOT NULL COMMENT '性别',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.01 sec)
    

    image.png

    2.8 查看数据库端口

    如果你忘记了端口,可以查看端口信息(前提是你能的登录上来,哈哈哈!)

    mysql> SHOW VARIABLES LIKE 'port';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | port          | 3306  |
    +---------------+-------+
    1 row in set, 1 warning (0.00 sec)
    

    image.png

    2.9 查看数据库索引大小

    首先我们使用数据库information_schema,这个数据库中记录了表的相关信息

    mysql> use information_schema;
    Database changed
    

    查看tables表列,可以看到记录的表相关信息属性,其中index_length列,记录了表的索引大小单位是B,table_schema列记录了当前表所属的数据库。

    image.png

    我们可以根据table_schema = 'liziba',查看当前数据库下索引的大小

    mysql> SELECT CONCAT(ROUND(SUM(INDEX_LENGTH)/(1024*1024), 4), 'mb') AS 'Database Index Size'
        -> FROM tables
        -> WHERE table_schema = 'liziba';
    +---------------------+
    | Database Index Size |
    +---------------------+
    | 0.0000mb            |
    +---------------------+
    1 row in set (0.00 sec)
    

    2.10 查看数据库大小

    除了计算索引大小,我们也可以统计数据库的大小,只需要将表数据内存和索引数据内存加起来即可。

    mysql> SELECT CONCAT(((ROUND(SUM(data_length), 4) + ROUND(SUM(index_length), 4)) / (1024 * 1024)), ' mb') AS 'Database Size'
        -> FROM tables
        -> WHERE table_schema = 'liziba';
    +---------------+
    | Database Size |
    +---------------+
    | 0.01562500 mb |
    +---------------+
    1 row in set (0.00 sec)
    

    image.png

    image.png

    2.11 查看最大连接数

    151 是默认的最大连接数

    mysql> SHOW VARIABLES LIKE '%max_connections%';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | max_connections        | 151   |
    | mysqlx_max_connections | 100   |
    +------------------------+-------+
    2 rows in set, 1 warning (0.00 sec)
    

    image.png

    2.12 查看当前线程相关信息

    注意这里使用STATUS,可以查看线程相关状态,VARIABLES查看的是配置参数。如下查询可以看到连接数、创建了多少线程、正在运行的线程等信息

    mysql> SHOW STATUS LIKE 'Threads%';
    +-------------------+-------+
    | Variable_name     | Value |
    +-------------------+-------+
    | Threads_cached    | 1     |
    | Threads_connected | 3     |
    | Threads_created   | 4     |
    | Threads_running   | 2     |
    +-------------------+-------+
    4 rows in set (0.00 sec)
    

    image.png

    2.13 查看文件存储路径

    mysql> SHOW VARIABLES LIKE '%datadir%';
    +---------------+------------------------------+
    | Variable_name | Value                        |
    +---------------+------------------------------+
    | datadir       | E:\mysql-8.0.15-winx64\data\ |
    +---------------+------------------------------+
    1 row in set, 1 warning (0.00 sec)
    

    image.png

    2.14 查看数据库编码

    查看数据库编码很有作用,我们有时候在同步数据的时候会因为编码不同导致数据出现异常,我们可以通过如下方式查看数据库的编码格式。

    mysql> SHOW VARIABLES LIKE 'collation%';
    +----------------------+--------------------+
    | Variable_name        | Value              |
    +----------------------+--------------------+
    | collation_connection | gbk_chinese_ci     |
    | collation_database   | utf8_general_ci    |
    | collation_server     | utf8mb4_0900_ai_ci |
    +----------------------+--------------------+
    3 rows in set, 1 warning (0.00 sec)
    

    2.15 帮助指令

    可以使用HELP指令查看指令。
    比如HELP SHOW;

    mysql> HELP SHOW;
    Name: 'SHOW'
    Description:
    SHOW has many forms that provide information about databases, tables,
    columns, or status information about the server. This section describes
    those following:
    
    SHOW {BINARY | MASTER} LOGS
    SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
    SHOW CHARACTER SET [like_or_where]
    SHOW COLLATION [like_or_where]
    SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
    SHOW CREATE DATABASE db_name
    SHOW CREATE EVENT event_name
    SHOW CREATE FUNCTION func_name
    SHOW CREATE PROCEDURE proc_name
    SHOW CREATE TABLE tbl_name
    SHOW CREATE TRIGGER trigger_name
    SHOW CREATE VIEW view_name
    SHOW DATABASES [like_or_where]
    SHOW ENGINE engine_name {STATUS | MUTEX}
    SHOW [STORAGE] ENGINES
    SHOW ERRORS [LIMIT [offset,] row_count]
    SHOW EVENTS
    SHOW FUNCTION CODE func_name
    SHOW FUNCTION STATUS [like_or_where]
    SHOW GRANTS FOR user
    SHOW INDEX FROM tbl_name [FROM db_name]
    SHOW MASTER STATUS
    SHOW OPEN TABLES [FROM db_name] [like_or_where]
    SHOW PLUGINS
    SHOW PROCEDURE CODE proc_name
    SHOW PROCEDURE STATUS [like_or_where]
    SHOW PRIVILEGES
    SHOW [FULL] PROCESSLIST
    SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
    SHOW PROFILES
    SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
    SHOW SLAVE HOSTS
    SHOW SLAVE STATUS [FOR CHANNEL channel]
    SHOW [GLOBAL | SESSION] STATUS [like_or_where]
    SHOW TABLE STATUS [FROM db_name] [like_or_where]
    SHOW [FULL] TABLES [FROM db_name] [like_or_where]
    SHOW TRIGGERS [FROM db_name] [like_or_where]
    SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
    SHOW WARNINGS [LIMIT [offset,] row_count]
    

    HELP STATUS;

    mysql> HELP STATUS;
    Many help items for your request exist.
    To make a more specific request, please type 'help <item>',
    where <item> is one of the following
    topics:
       FLUSH
       SHOW
       SHOW ENGINE
       SHOW FUNCTION STATUS
       SHOW MASTER STATUS
       SHOW PROCEDURE STATUS
       SHOW SLAVE STATUS
       SHOW STATUS
       SHOW TABLE STATUS
    

     

    👇🏻 关注公众号 获取更多资料👇🏻 

    展开全文
  • 索引是一种能提高数据库查询效率的数据结构。它可以比作一本字典的目录,可以帮你快速找到对应的记录。索引一般存储在磁盘的文件中,它是占用物理空间的。正所谓水能载舟,也能覆舟。适当的索引能提高查询效率,过多...
  • 《MySQL死锁分析的两个工具》中,举了一个强制类型转换导致死锁的例子,有朋友询问是不是类型转换都不能命中...cellvarchar(3) primary key )engine=innodb default charset=utf8; insert into t1(cell) values ..
  • 数据库建表规约,索引创建及失效分析
  • 我们在开始一个项目之前, 一般先进行需求分析, 根据项目需求建立对应的数据库, 再进行代码编写. 而数据库建立的好坏也会影响到系统的运行, 因此在设计数据库的时候就应该考虑进行优化.st的特例;const:使用唯一索引...
  • 阿里云数据库ClickHouse二级索引功能近日已正式发布上线,主要弥补了ClickHouse在海量数据分析场景下,多维度点查能力不足的短板。在以往服务用户的过程中,作者发现绝大部分用户对ClickHouse单表查询性能优化问题...
  • 我相信你一定听说过,如果两张表字符编码不一致,索引字段在进行join时会导致索引失效,但一定是这样的吗?本文就来一起仔细分析一下这个问题。 准备 我们先准备两个表,一个采用utf8的字符集,一个采用utf8mb4的...
  • Oracle数据库中的索引详解以下文字资料是由(历史新知网www.lishixinzhi.com)小编为大家搜集整理后发布的内容,让我们赶快一起来看一下吧!一 ROWID的概念存储了row在数据文件中的具体位置 位编码的数据 A Z a z + 和...
  • 编码:把人类可以识别的信息转化为机算计认识的0和1解码:把机算计存储的0和1转化为人类可以识别的信息乱码:编码解码的过程,导致一些数据不能解析ASCII:美国gbk 中国,一个汉字占用2个字节,#范围大于utf8gbk2312#...
  • 1、MySQL 索引使用有哪些注意事项呢? 可以分为三种情况来看:索引在哪种情况会失效、索引不适合哪些场景和索引规则 索引在哪种情况会失效 在索引列上使用mysql得函数或者做运算会导致索引失效 sql条件数据类型转换...
  • MYSQL数据库编码是相当灵活,可以随意定义到数据库的默认编码,表的默认编码,字段的编码。但从数据的本质来讲,无论任何编码都是一堆字节数据。那mysql是根据什么配置来确定我们数据的编码,帮我们正确去储存数据...
  • 持久性(Durability):事务一旦提交,就会被持久化到数据库中 隔离性(Isolation):事务的隔离性是多个用户并发访问数据库时。数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要...
  • 索引

    2021-03-20 09:22:51
    文章目录索引索引相关语句创建索引查看索引删除索引key_len的计算适用原则explain分析查询语句select_typetypekeypossible_keysrefrows数值类型代替字符串考虑使用ENUM类型 索引 索引相关语句 创建索引 CREATE ...
  • mysql的索引与优化

    2021-01-19 00:33:19
    本文基于mysql5.6与mariadb文档.1 索引1.1 概述索引无非就是把一列或一些列的数据的使用一种数据结构独立存储起来,用于快速查询各行.通常这个数据结构是树,主要用到B树.使用WHERE语句配合=,>,BETWEEN,IN等算子...
  • 需要复杂技巧及高深知识来解决所遇到的问题的情形并不多,...在下面两种情形下,系统会自动为表的列建立索引:当表的列被指定为 primary key 时;当表的列有 unique constraint 时。当一个表中有 foreign key 存在时...
  • Mysql索引总结

    2021-02-02 15:41:25
    索引每个InnoDB表都具有一个特殊的索引为聚簇索引,如果表上有定义主键,则该主键就是聚簇索引,如果未定义主键,mysql会取第一个唯一索引(unique)而且只含非空列(NOT NULL)作为主键,InnoDB使用它作为聚簇索引,...
  • MySQL如何设计索引

    2021-04-21 06:36:13
    MySQL改善性能最好的方式,就是通过数据库中合理地使用索引,换句话说,索引是提高 MySQL 数据库查询性能的主要手段。在下面的章节中,介绍了索引类型、强制索引、全文索引。MySQL 索引可以分为单列索引、复合索引、...
  • http://blog.csdn.net/seusoftware/archive/2010/04/24/5524414.aspx引用一、综述命名和编码过程中,定义有意义的名称,以易于理解、方便书写为原则。(1)避免使用中文,尽量使用全拼音或全英文,以方便国际化;(2)...
  • 一、索引:就是用来提高搜索性能的 只有我们数据量非常大的时候,索引可以展现出它的优势来!注意:索引,我们在添加了以后,不用刻意的去使用它,它会自动生效1. 常规索引(index):没有任何限制,就是普通的索引1&...
  • 简介 在建表语句中,我们可以指定数据表... PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 引擎 MySQL5.5开始,Innodb已经成为MySQL的默认引擎,之前是MyISAM 区别: MyISAM是非事务安全的,而InnoDB是事
  • 一、 ROWID的概念存储了row在数据文件中的具体位置:64位 编码的数据,A-Z, a-z, 0-9, +, 和 /,row在数据块中的存储方式SELECT ROWID, last_name FROM hr.employees WHERE department_id = 20;比 如:...
  • /*创建数据库*/CREATE DATABASE `mybank`;/*创建表*/USE mybank;CREATE TABLE `bank`(`customerName` CHAR(10), #用户名`currentMoney` DECIMAL(10,2) #当前余额);/*插入数据*/INSERT INTO `bank` (`customerName`,`...
  • 但是,这不是意味着数据库索引仅仅是数据库设计和运维者的事情,对于一个程序员如果对数据库已有的索引有所了解,还是可以大大优化程序员数据库的查询和修改语句执行效率的,以免你的低效查询语句称...
  • 优美的音乐节奏带你浏览这个效果的编码过程 坚持每一天,是每个有理想青年的追求 追寻年轻人的脚步,也许你的答案就在这里 如果你迷茫 不妨来瞅瞅这里 我们这里有一张用户表,建表语句如下 CREATE TABLE `t_user`( ...
  • mysql索引实践小总结

    2021-01-14 18:42:13
    – 需要明确的前置条件 在utf8编码情况下,其他编码类型,如gbk 只不过是*2 -- 基本数据类型对应的索引长度为基本数据类型字节长度,如int 为4 -- varchar(n)类型 对应的索引长度为:n*3+2 -- char(n)类型 对应的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 28,180
精华内容 11,272
关键字:

数据库索引编码primary