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表，目前它都不支持事务，行级锁和外键约束的功能。

索引

索引用到的数据结构

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

1. B+-tree的磁盘读写代价更低 B+-tree的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。 举个例子，假设磁盘中的一个盘块容纳16bytes，而一个关键字2bytes，一个关键字具体信息指针2bytes。一棵9阶B-tree(一个结点最多8个关键字)的内部结点需要2个盘快。而B+ 树内部结点只需要1个盘快。当需要把内部结点读入内存中的时候，B 树就比B+ 树多一次盘块查找时间(在磁盘中就是盘片旋转的时间)。

2. B+-tree的查询效率更加稳定 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。 读者点评

3. 有人觉得这两个原因都不是主要原因。数据库索引采用B+树的主要原因是 B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。

索引的分类

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

存储引擎对索引的利用

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

1. MyISAM与InnoDB使用的都是B+树作为索引

2. MyISAM使用B+树的方式如下：

3. 对于主键索引：
4. 对于辅助索引： 可见它主要是MyISAM的叶子节点存储的是数据的地址，索引文件与数据是分离的，当查询时先从索引文件中找到数据的地址，然后再根据地址去取出数据的值。主索引与普通索引的查询方法是一致的。

5. InnoDB使用B+树的方式如下：

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

存储引擎适用的场景

只有了解存储引擎的原理才能更好的进行优化，其实优化就是根据其原理把数据库的性能提升到尽可能的高。

原文地址：MySQL优化总结, 感谢原作者分享。