MySQL优化总结

程序员文章站 2024-02-15 18:55:52

...

这篇博客主要就mysql的优化进行问题从不同方面进行了总结。基础知识通常意义上，数据库也就是数据的集合，具体到计算机上数据库可以是存储器上一些文件的集合或者一些内存数据的集合。MySql数据库是开放源代码的关系型数据库。目前，它可以提供的功能有：

这篇博客主要就mysql的优化进行问题从不同方面进行了总结。

基础知识

通常意义上，数据库也就是数据的集合，具体到计算机上数据库可以是存储器上一些文件的集合或者一些内存数据的集合。MySql数据库是开放源代码的关系型数据库。目前，它可以提供的功能有：支持sql语言、子查询、存储过程、触发器、视图、索引、事务、锁、外键约束和影像复制等。MySql也是客户/服务器系统并且是单进程多线程架构的数据库。MySql区别于其它数据库系统的一个重要特点是支持插入式存储引擎。

存储引擎

根据存储数据及为数据建立索引和更新、查询技术的不同可以将mysql的存储分为不同的存储引擎，其中最主要的存储引擎有MyISAM、InnoDB、MEMORY等，其中最常用的有MyISAM和InnoDB两种，可以通过下面的命令查看自己的MySQL支持哪些存储引擎：

mysql> SHOW ENGINES;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)

MyISAM:这种引擎是mysql最早提供的。这种引擎又可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种：
- 静态MyISAM：如果数据表中的各数据列的长度都是预先固定好的，服务器将自动选择这种表类型。因为数据表中每一条记录所占用的空间都是一样的，所以这种表存取和更新的效率非常高。当数据受损时，恢复工作也比较容易做。
- 动态MyISAM：如果数据表中出现varchar、xxxtext或xxxBLOB字段时，服务器将自动选择这种表类型。相对于静态MyISAM，这种表存储空间比较小，但由于每条记录的长度不一，所以多次修改数据后，数据表中的数据就可能离散的存储在内存中，进而导致执行效率下降。同时，内存中也可能会出现很多碎片。因此，这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理。
- 压缩MyISAM：以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先时行解压缩。
但是，不管是何种MyISAM表，目前它都不支持事务，行级锁和外键约束的功能。
MyISAM Merge：这种类型是MyISAM类型的一种变种。合并表是将几个相同的MyISAM表合并为一个虚表。常应用于日志和数据仓库。
InnoDB：InnoDB表类型可以看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能。
memory(heap)：这种类型的数据表只存在于内存中。它使用散列索引，所以数据的存取速度非常快。因为是存在于内存中，所以这种类型常应用于临时表中。
archive：这种类型只支持select 和 insert语句，而且不支持索引。常应用于日志记录和聚合分析方面。

索引

索引用到的数据结构

关于存储引擎用的数据结构其中最重要的就是B树与B+树，可以参考JULY的这篇从B 树、B+ 树、B* 树谈到R 树。
可以看到，B树与B+树的最大的区别其实就是：B树的所有信息都存在字节点中，而B+树的所有信息都存储在叶子节点中。
B+树比B树更适合做文件索引和数据库索引，原因是：

B+-tree的磁盘读写代价更低 B+-tree的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。举个例子，假设磁盘中的一个盘块容纳16bytes，而一个关键字2bytes，一个关键字具体信息指针2bytes。一棵9阶B-tree(一个结点最多8个关键字)的内部结点需要2个盘快。而B+ 树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候，B 树就比B+ 树多一次盘块查找时间(在磁盘中就是盘片旋转的时间)。
B+-tree的查询效率更加稳定由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。读者点评
有人觉得这两个原因都不是主要原因。数据库索引采用B+树的主要原因是 B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。

索引的分类

MySQL数据库可以建立不同的数据库，主要类型有：

普通索引:就是普通的INDEX。索引的列可以重复。
唯一索引:UNIQUE INDEX ,索引的列值必须使唯一，可以有空值，如果是组合索引，则列值的组合必须使唯一的。主键索引是一种特殊的唯一索引，不允许为空值，一个表只能有一个主键。
全文索引:FULLTEXT索引，把CHAR、VARCHAR或TEXT列作为索引，仅可以用于MyISAM表中。
组合索引（最左前缀）:对个列组合成为一个索引。这个索引其实只是上面几种的一个特殊情况。

存储引擎对索引的利用

关于存储引擎及其使用的数据结构可以看下这个博客：浅谈mysql索引背后的数据结构及算法。
这篇博客讲的很清楚了，但是这里我还是要总结一下重点，便于自己记忆。

MyISAM与InnoDB使用的都是B+树作为索引
MyISAM使用B+树的方式如下：
- 对于主键索引：
- 对于辅助索引：可见它主要是MyISAM的叶子节点存储的是数据的地址，索引文件与数据是分离的，当查询时先从索引文件中找到数据的地址，然后再根据地址去取出数据的值。主索引与普通索引的查询方法是一致的。
InnoDB使用B+树的方式如下：
- 对于主键索引：
- 对于辅助索引：可以看出与MyISAM不同，InnoDB的数据文件同时也是索引文件，对于主键索引的使用，就是直接从索引文件的叶子节点中找出数据。但是普通索引所有的叶子节点存储的都是主键的值，对于普通索引只能先通过索引文件找出主键的值，然后再根据主键的值从主键索引文件中找出数据。 ps:有点疑问：前面说选择B+Tree作为存储引擎数据结构的原因第一条是索引文件比较小，可以放到同一个磁盘上，减少磁盘的读取次数，所以效率比较高，但是这个把索引文件和数据融到了一起，是不是也会有这个问题呢？