高性能MySQL笔记(一)

1. 概览

 

InnoDB的数据存储在表空间中，采用MVCC来支持高并发，并且实现了四个标准隔离级别，默认的级别是REPEATABLE READ, 并且通过间隙锁来防止幻读。

InnoDB是通过聚簇索引建立表的。聚簇索引对主键的查询有很高的性能，但是由于二级索引必须包含主键列，所以主键要尽量小。InnoDB 的行为是很复杂的，其内部也做了很多的优化，比如自动在内存中创建hash索引加速读操作的自适应hash索引等。

 

MylSAM在5.1之前都是默认的存储引擎，里边提供了大量的特性，例如例如全文索引、压缩、空间函数等。但是MyISAM不支持事务以及行级锁，并且在崩溃之后无法安全修复数据。

 

• 存储：MyISAM会将文件分为 .MYD和 .MYI文件。
• 加锁：MyISAM是对整一张表进行加锁
• 修复：可以通过手动工具修复。
• 索引：即使是BLOB和TEXT也可以创建索引。同时也支持全文索引。
• 压缩表：对于产生之后就不需要修改的数据，可以用MyISAM进行表压缩。压缩之不仅能减少磁盘IO，还能提高查询性能。
• 性能：由于MyISAM设计简单，数据以紧密格式存储，所以在一些特定的场合性能很好。

 

Memory引擎，比MyISAM还要快，数据都是存储在内存当中的，不需要进行I/O，但是重启之后会丢失，支持hash索引。

 

2、挑选引擎

 

一般来说，都选InnoDB，并且不建议混合使用多种引擎。因为会涉及到事务以及备份的问题。

 

3、创建高性能的索引

 

索引（key）是存储引擎中使用的一种快速查找记录的数据结构，索引对性能有很关键的表现。当数据库中的数据越大，索引的作用就会越明显——索引可以将查询的性能提高几个数量级。

 

3.1索引基础

 

和字典中的索引类似，MySQL中，存储引擎会先去索引里边找，找到索引之后会根据索引去找对应的记录的位置。

 

3.1.1索引的类型

 

B-Tree

如果没有特殊指名，那么一般说的索引都是B-Tree索引。故名思意，就是用B-Tree数据结构来存储出局，大多数的MySQL引擎都支持这种索引。而InnoDB使用的是B+Tree。

B-Tree存储的值都是按照顺序存储的，并且每一个叶子页到根的距离都是相同的。如下图所示：

​​

 

上图从技术层面来说，其实是B+Tree，和B-Tree的不同主要是B+Tree会将所有的叶子页组成一个链表，便于顺序查询。

另外InnoDB引擎有一个特殊的功能，称为“自适应哈希索引”。当InnoDB注意到某些索引值被频繁使用时，会再内存中中基于原有的B+Tree索引上增加一个hash索引。

 

hash索引

对于每一行数据，存储引擎都会对所有的索引列计算一个hash code，并且在hash 表中保存每一个数据行的指针。

 

 

3.2高性能索引的策略

 

3.2.1 独立的列

 

有一些不当的查询会让MySQL没有使用到已有的索引。如果查询的列不是独立的，那么MySQL就不会使用索引。独立的列例如：

 

select * from aaa where id += 5;

 

独立的列指的是“不是表达式的一部分，也不是函数参数”。

 

3.2.2 前缀索引和索引选择性

 

选择索引的时候可以避开一些很大的字段，可以节省索引的空间，从而提高效率。但是一些特殊的情况，必须使用如BOLB、TEXT以及很长的varChar 等类型，就需要用到前缀索引。

 

选择适合的索引的准则是，前缀索引的长度要使得所有的数据可以均匀分布，所以使用之前要对数据有所了解。添加的SQL如下：

 

ALTER TABLE aaa.bbb add key (字段名(前缀长度))

 

3.2.3多列索引

 

千万不要把where后边的所有条件都加上单列索引。因为查询这个表的SQL可能有很多种条件，于其花精力在where上，不如直接对数据库中的索引列顺序进行优化，或者是创建一个全覆盖索引。

MySQL在5.0之后就引入了一种称为“索引合并”的策略，可以在一定层度上使用多个单列索引来制定行。这种算法有三种变种：OR、AND以及这两种的混合。

索引合并有时候会是一种优化的结果，但是更多的时候的表现非常糟糕：

• 当服务器对多个索引做相交操作时（多个AND），通常意味着一个包含所有相关列的多列索引，而不是多个单列索引。
• 当服务器需要对多个索引做联合操作（多个OR），通常需要耗费大量CPU和内存资源在算法的缓存，排序和合并操作上。
• 优化器不会把上述的东西计算到“查询成本”之中，优化器只是关心随机页面读取。也就是上边设计的“精细”的方法不会被算上使用的时间，这样会消耗比数值上更多的资源。

 

3.2.4 选择适合的索引列顺序

 

下边的内容都是围绕着B-tree索引（包含B+Tree）来说明的。

在多列B-Tree索引中，索引的顺序意味着索引首先按照最左列进行排序，然后时第二列……所以可以满足ORDERBY、GROUP BY和DISTINCT等查询需求。

如何选择多列索引有一个经验法则：将选择性最高的列放到索引的最前列。这样做的性能通常很好，这个时候索引的作用只是对where的优化。