高性能mysql系统设计_高性能mysql 4,5,6章优化总结

针对数据库的优化，我们不能单纯的说从哪一个方面，需要结合数据表的建立，数据类型的选择，索引的设计和sql语句来考虑，我就针对怎么建表，怎么选择数据类型，如何应用B-tree索引，hash索引和覆盖索引的特点来建立高效的索引策略，然后我具体对count()查询，最大最小值查询，关联查询，子查询，GROUP BY，limit分页，Union查询做一些具体的说明，最后我说一下怎样使用切分查询和分解关联查询来重构我们的查询方式，

一、数据表的设计

首先我们要根据范式化和反范式化各自的优缺点，选择一个最佳的设计。

(反范式化设计，一个典型列子就是缓存表，它的特点就是在不同的表中存储相同的列，它很好的避免了关联，但是增加了许多冗余的数据，插入和更新可能会慢一些，而范式化设计的数据表通常比较小，很少有冗余的数据，插入更新也比反范式化设计的表要快，但是通常需要做关联操作。)

二、数据类型的选择：

我主要说3点

1、选择简单的，不超过范围的最小类型，避免使用 NULL(可为NULL的列使得索引，索引统计和值比较都更复杂，还会使用更多的存储空间，在mysql里也需要特殊处理)

Example:

(1)日期时间类型最好使用timestamp而不用datetime存储，datetime使用了8个字节的存储空间，而timestamp只使用4个字节的存储空间，可以使用FROM_UNIXTIME()和UNIX_TIMESTAMP()进行相互转化。

(2)IP地址是一个32位无符号整数，应该unsigned int来存储，不应该使用varchar(15)来存储，可以使用INET_NTOA()和INET_ATON()两个函数来相互转化。

2、注意char和varchar的区别，(varchar适合存储最大长度比平均长度大很多和列的更新少，不容易产生碎片的数据，而char适合存储短的，所有值接近同一个长度的数据，对于经常变更的数据也适合用char存储)

3、对于相似或相关的值尽量使用同种数据类型存储，特别是在关联条件中使用的列。

三、高效的索引策略

为什么要使用索引？

索引大大减少了存储引擎需要扫描的数据量。

素有可以帮助我们进行排序以避免使用临时表(这是由于B-tree索引顺序存储的特性)。

索引可以把随机IO变成顺序IO(虽然数据可能是随机存储的，但是B-tree索引顺序存储的)

索引不是越多越好

索引会增加写操作的成本。

太多的索引会增加查询优化器的选择时间。

只有增加的索引所提升的性能大于所消耗的性能，这样的索引才是有效的。

索引的选择性 = 不重复的索引值 / 表的记录数。

故主键的索引选择性最高，索引的选择性越高，查询的效率也就越高。

也能看出前缀索引的选择性也就不怎么高。

如下图，如果我们创建 前 2 个字符为前缀索引，那么就只能分成两组，而以前3个字符作为前缀索引，其选择性就提高了，但是索引的长度也增加了。所以我们在创建前缀索引的时候即要保证索引尽量小，又要保证索引的选择性不能太差。

MyISAM,InnoDB和memory存储引擎都支持B-tree索引，memory支持hash索引，InnoDB支持聚簇索引，那么我们就可以根据它们所支持的索引类型和数据的存储结构来设计高效的索引策略。

1、首先我说说 hash 索引，因为hash查找非常快，hash索引是基于哈希表实现，存储引擎会对所有的索引列计算一个哈希码，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存每个指向数据行的指针。我们就可以利用它的这个特点来创建一个自定义hash索引提高查询效率。

举个列子，假如现在有一个address表，里面存储大量的url，而且需要根据url进行搜素查找，我需要执行一条这样的查询：

SELECT id FROM address WHERE url=’http://www.mysql.com’;

那么我直接在url上面建立一个索引好不好呢，答案是肯定能提高查询效率，但是由于这个字段太长，直接用它做索引不是最佳选择，我们应该删除url上的索引，再数据表中增加一个字段crc_url,使用CRC32做hash，然后执行下面的查询：

SELECT id FROM address WHERE url=’http://www.mysql.com’ AND url_crc = CRC32(“http://www.mysql.com”);

这样做性能会非常高，因为mysql优化器会使用这个选择性更高而且体积更小的基于url_crc列的索引来完成查找，只需要根据哈希值做快速的整数比较就可找到索引条目，然后返回对应的列，哈希值我们可以用触发器来维护，

CREATE TRIGGER address_hash_crc_ins BEFORE INSERT ON address FOR EACH ROW BEGIN SET NEW.crc_url = crc32(NEW.url);

CREATE TRIGGER address_hash_crc_upd BEFORE UPDATE ON addressFOR EACH ROW BEGIN SET NEW.crc_url = crc32(NEW.url);

当然InnoDB有一个特殊的功能叫“自适应哈希索引，当InnoDB注意到某些索引使用的非常频繁是就会在内存中基于B-tree索引在创建一个哈希索引，让B-tree索引页具有hash索引的一些优点”。

当然虽然哈hash索引查找速度非常快。但是也有它的局限性由于它不是按照索引值顺序存储的，所以它无法用于排序操作，也不支持部分索引列匹配查找，因为hash索引始终是使用索引列的全部内容来计算hash值的，哈希索引只支持等值比较查询。

2、在说一下InnoDB支持的聚簇索引，聚簇索引的数据实际上是保存在索引的叶子页中，我先说说MyISAM和InnoDB的数据存储结构：

             

举个列子：

假如现在有一个这样的查询：

SELECT * FROM questions WHERE question_userid=1 AND question_title LIKE‘%PHP试题%’;

当数据量很大的时候，返回的行有比较少时，查询速度回很慢，我们需要重写查询并巧妙的设计索引，我需要先添加一个多列索引 userid_title(question_userid,question_title)，我将查询改成这样：

SELECT * FROM questions JOIN(SELECT question_id FROM questions WHERE question_userid=1 AND question_title LIKE ‘%PHP试题%’) AS t1 ON(t1.question_id = questions.question_id);

使用这种延迟关联的查询方式，查询的第一阶段mysql可以使用覆盖索引，然后根据这些question_id值在外层查询匹配获取需要的所有值。

3、B-tree索引

Innodb中，B-tree索引指向的是主建。

MyIsam中，B-tree索引指向的是数据的物理地址。

a、B-tree索引的特点

B-tree索引能够加快数据的查询速度。

B-tree索引更适合范围查找(因为B-tree索引是顺序存储的)。

b、在什么情况下可以使用B-tree索引

全值匹配的查询(如：order_num='OD-20190914001')

匹配最左前缀的查询(如：roder_num和order_date创建了一个复合索引，且order_num在前，如果只查order_num还是可以用的)

匹配列前缀查询(如：order_num like 'OD-20190914%')

匹配范围值得查询(如： > 或

精确匹配左前列并范围匹配另一列的情况。

只访问索引的查询(覆盖索引，即只需要查询索引，效率相当高)。

c、B-tree索引的适用限制

如果不是按照索引的最左列开始查找，则无法使用索引。

使用索引时不能跳过索引中的列(如有三个列组成的复合索引(abc),如果跳过b列，只查询ac列是无法使用到索引的)

Not in 和 <> 操作无法使用索引。

如果查询中有某个列的范围查询，则其右边所有列都无法使用索引。

四、索引优化策略

1、索引列上不能使用表达式或函数

2、前缀索引

索引的宽度是有限制的，InnoDB最大是767个字节，Myisam是1000个字节。

所以对字符串类型支持前缀索引。建立前缀索引一定要指明宽度。

#这里的 n 就是指定的列的宽度，即作为前缀

CREATE INDEX index_name ON table_name(col_name(n));

3、联合索引

4、覆盖索引

a、覆盖索引的定义

可以使用B-tree直接获取我们所需数据的一种方法，B-tree索引在叶子节点上存储了关键字的值，故我们可以通过索引关键字直接获取所需查询的数据，这样也就没必要读取数据行的信息了，这种包含了所有查询字段全不值得索引就叫覆盖索引。这里的全部值包括select语句中的字段，where子句中的字段和orderby，group by子句中的字段。

b、优点

可以优化缓存，减少磁盘IO操作(索引比较小，可以保存更多的索引，且不需要从磁盘读数据)

可以减少随机IO，变随机IO为顺序IO(因为B-tree索引是顺序存储的)

可以避免对Innodb主键索引的二次查询(Innodb的二级索引的叶子节点中保存的是行的主键值，通常情况下，如果是利用二级索引来查询数据的话，在查询到相应的行之后，还需要通过主键来进行二次查询，才能获取到我们所需行的数据，而在覆盖索引中，二级索引的键值就可以获取我们查询所需的所有数据了，这样就避免了对主键的二次查询)。

可以避免MyISAM表进行系统调用(覆盖索引对MyISAM存储引擎的表带来的好处可能更大，因为MyISAM村粗引擎只将索引缓存在内存中，而数据是依靠操作系统 来缓存的， 因此正常情况下我们如果需要访问数据，都需要进行一次系统调用，而系统调用的性能通常会比较差，如果能通过索引来获取全部所需数据就可以避免这种系统调用的产生)。

c、无法使用覆盖索引的情况

存储引擎不支持覆盖索引(memory存储引擎就不支持)。

查询中使用了太多的列(覆盖索引之所以能提高查询性能，主要就是索引的大小要比行的大小小的多，如果索引本身很大，就没有使用索引的必要了)。

使用了双%的查询(mysql底层api所限制的，mysql是通过提前数据行的值，然后在内存中进行过滤，所以是无法使用索引的)。

d、示例：

使用mysql官方的 sakila数据库。

#film表的language_id有一个索引，我们执行下面的查询

explainselect language_id from film where language_id=1\G;

再来看一个使用Innodb二级索引的情况

#actor表中只有 last_name 字段有一个索引，主键为 actor_id，我们执行下面的查询

explainselect actor_id,last_name from actor where last_name='Joe'\G;

5、使用索引扫描来优化排序

InnoDB存储引擎中表数据的逻辑顺序是和主键的顺序一致的。因此我们也可以利用主键来进行排序。

EXPLAIN SELECT * FROM rental WHERE rental_date>'2005-01-01' ORDER BY rental_id\G;

而MyISAM存储引擎使用主键排序就是没有效果的。

rental表中有一个复合索引如下：

我们执行如下查询

EXPLAIN SELECT * FROM rental WHERE rental_date='2005-01-01' ORDDER BYinventory_id,customer_id\G;

EXPLAINSELECT * FROM rental WHERE rental_date='2005-01-01' ORDER BY inventory_id DESC,customer_id DESC\G;

如果我们调整了其中一个排序字段的顺序，那就用不到索引了。

EXPLAIN SELECT * FROM rental WHERE rental_date='2005-01-01' ORDER BY inventory_id DESC,customer_id\G;

EXPLAIN SELECT * FROM rental WHERE rental_date='2005-01-01' ORDER BY customer_id,inventory_id\G;

如果在联合索引的最左列启用了范围查询，那么后面列的索引页就失效了，页就不能使用索引来进行排序了。

EXPLAIN SELECT * FROM rental WHERE rental_date>'2005-01-01' ORDDER BY inventory_id,customer_id\G;

6、模拟hash索引优化查询

使用前缀索引使得索引的选择性变得很差，如何不使用前缀索引而对一个长字符串进行精确查询呢？当然我们可以使用B-tree索引来模拟hash索引 的方式来进行优化。

上面的查询即在 time_md5列进行了过滤然后又在title列过滤，是为了避免产生hash冲突导致查询结果不准确。

限制

只能处理键值得全值匹配查找。

所使用的Hash函数决定着索引键的大小。

7、利用索引优化锁

优点：

索引可以减少锁定的行数

索引可以加快处理速度，同时也加快了锁的释放。

示例：

我们先将 actor 表 last_name 字段的索引删除。

然后在启用一个事务，并给 actor 表加一个排他锁。

重新打开一个新的 session 终端，进入数据库，启用事务，并查询另一个名字。

我们会发现由于没有索引，虽然第一次查询只需要查询 last_name='WOOD' 的的几行，但是也会给全表加一个锁。session 2 还是被 session 1 给阻塞了。

如果我们给 last_name 列增加一个索引，那么 session 1 就不会阻塞 session 2了，也就是说增加索引可以让表锁定更少的行。从而提升了查询的性能。

8、索引的维护和优化

a、删除重复和冗余的索引，例如下面这几种：

b、如何检测索引是否是冗余的？

可以使用 pt-duplicate-key-checker 这个工具(需要下载)。下面是一个该工具的检查示例：

c、查找未被使用的索引。

SELECT object_schema,object_name,index_name,b.`TABLE_ROWS` FROM performance_schema.table_io_waits_summary_by_index_usage a JOIN information_schema.tables b ON a.`OBJECT_SCHEMA`=b.`TABLE_SCHEMA` AND a.`OBJECT_NAME`=b.`TABLE_NAME` WHERE index_name IS NOT NULL AND count_star=0 ORDER BY object_schema,object_name;

我目前发现这条sql用不了，待后续验证。

d、更新索引信息及减少索引碎片

analyze tabletable_name;

optimizetable table_name;

五、创建高效的sql语句，

1、优化count

如果是MyISAM存储引擎，而且没有任何的where条件，我们直接使用count(*)，mysql会利用存储引擎的特点直接获取这个值，还有一个方法就是利用反正特性，比如有这样一个查询：

SELECT COUNT(*) FROM questions WHERE id>5;

此时将语句改写成：

SELECT(SELECT COUNT(*) FROM questions) - COUNT(*) FROM questions WHERE id<5;

这样就可以大大减小需要扫描的行数，查询优化器会直接将子查询当做一个常数来处理，而且查询小于5的数据很少很多，如果只是需要一个粗略值的话可以直接使用EXPLAIN中优化器估算出的一个行数就可以当做这个近似值。如果需要分别查询单选题题，判断题各有多少道，可以这样写：

SELECT COUNT(question_type=1 OR NULL) AS ‘单选题’, COUNT(question_type=2 OR NULL) AS ‘多选题’ FROM qustions;

2、优化min()

SELECT MIN(question_id) FROM questions WHERE question_userid=1;

改写成：

SELECT question_id FROM questions USE INDEX(PRIMARY) WHERE question_userid=1 LIMIT 1;

这里使用了一个优化器提示USE INDEX()来告诉优化器使用主键索引来查询记录，由于数据存储的时候是按照主键值从小到大存储的，那么满足条件的第一条记录一定是最小值。

3、优化关联查询

如果 A 与B通过c列关联，即 A JOIN B,则只需要在B表的c列上建立索引就好，除非有其它理由，否则只需要在关联表的第二张表上创建索引就好。如果在B表上也建索引，那么这种冗余的索引只会带来额外的负担，还要注意它们的关联字段的数据类型尽量用通一种数据类型，如果查询语句中有ORDER BY或者GROUP BY，那么最好只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程。

4、优化GROUP BY

当我们需用分组做分组排序的时候，这个时候我们创建索引就应该尽量让它满足既能用于查找行又能用于排序操作，这样就不用创建临时表来做额外的排序操作了，那么优化GROUP BY 最好的方法就是使用松散索引扫描，如果不能满足松散索引扫描的话，也尽量使用紧凑索引扫描，避免使用临时表来排序。

(1)使用松散索引扫描(当mysql完全利用索引扫描来实现GROUP BY的时候，并不需要扫描所有满足条件的索引键就可以完成操作的出结果)

假设有一个表 t1 有4个列 (c1,c2,c3,c4),给它建立了一个多列索引 (c2,c3,c4);

做类似这样查询 SELECT c2,MAX(c3) FROM t1 GROUP BY c2,c3;

例子：

SELECT question_userid,question_type FROM questions GROUP BY question_userid，question_type;

(2)使用紧凑索引扫描：

SELECT c2,MAX(c3) FROM t1 WHERE c2=const GROUP BY c3,c4;

SELECT question_userid,question_type FROM questions WHERE question_userid=1 GROUP BY question_type;

紧凑索引扫描和松散索引扫描的区别在于，紧凑索引扫描需要在扫描索引的时候，读取所有满足条件的索引键，然后在根据读取的数据来完成GROUP BY操作，它需要访问WHERE条件中所限定的所有索引键之后才能得出结果。这里的GROUP BY并不是一个连续的索引，但是WHERE条件中的question_userid是个常数，弥补了缺失的索引，因此使用紧凑索引扫描。

(3)使用临时表

如果这两种中条件都打不到的话，就只能使用临时表来进行排序了，如果数据量小可以直接在内存里进行，数据量大的话可能还需要借助磁盘来完成，如果我们自己清楚临时表的数据量大小，可以使用优化器提示SQL_SMALL_RESULT 或SQL_GIG_RESULT告诉优化器在哪儿排序。如果我们只需要对结果分组而不需要排序，可以加一句ORDER BY NULL来避免GROUP BY默认的按照分组字段进行排序。

5、优化UNION

除非确实需要去除重复的行，否则一定要使用UNION ALL , 因为如果没有ALL，mysql会给临时表加上DISTINCT做唯一性检查，这样做代价非常高，

Mysql在做UNION查询时，无法将限制条件从外层“下推”到内层，例如：

(SELECT c1,c2 FROM t1 ORDER BY c2) UNION ALL (SELECT c3,c4 FROM t2 ORDER BY c4) LIMIT 20;

假设从t1表中查出了500条记录，从t2表中查出了800条记录，那么mysql会将这1300条记录放入临时表在取出20条。因为它无法将limit下推到内层，我们可以这样优化一下：

(SELECT c1,c2 FROM t1 ORDER BY c2 LIMIT 20) UNION ALL (SELECT c3,c4 FROM t2ORDER BY c4 LIMIT 20) LIMIT 20;

这样临时表就只有40条记录了，再取出20条。

6、优化limit 分页

对于limit的优化，最常用的地方就是分页，假设我们有很多页，有例如limit 1000,20这样的查询，那么mysql需要查询1020条记录，然后将前面的1000条都扔掉，然后返回最后20条，这样做代价太高，我们可以这样来优化： 尽可能使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联在返回所需的列：

例如：

SELECT question_name,question_type FROM questions ORDER BY question_inserttime LIMIT 1000,20;

可以这样改：

SELECT question_name,question_type INNOR JOIN(SELECT question_id FROM questions ORDER BY question_inserttime LIMIT 1000,20) AS t1 USEING(question_id);

这里使用了延迟关联，先使用索引覆盖扫描快速找到对应的20个question_id,然后做一次关联操作返回所需的列。

7、子查询

对于子查询最好是使用关联查询来代替。

当然除了这些查询语句优化外，还可以重构查询方式，比如切分查询和分解关联查询；

六、重构查询方式

1、切分查询

所谓的切分查询，就是将一个大查询分而治之，将一个大查询切分成小查询，每个查询的功能完全一样，只完成一小部分，每次只完成一小部分查询结果。

例如我们有一个很大的message表，我们需要定期去删除里面的数据，需要执行这样一条sql:

DELETE FROM message WHERE create_time < DATE_SUB(NOW(),INTVAL 1 MONTH);

我们可以将它改成这样：

$row_effect = 0;do{$sql = “DELETE FROM message WHERE create_time execte($sql);

}while $row_effect >0

2、分解关联查询

分解关联查询就是将多表关联查询切分成单个查询，这样做可以让缓存命中率更高，而且执行单个查询可以减少锁的竞争。

七、安装演示数据库

安装mysql官方的 sakila 演示数据库。

http://downloads.mysql.com/docs/sakila-db.tar.gz

tar-zxf sakila-db.tar.gz

mysql-uroot -p < sakila-schema.sql

mysql-uroot -p < sakila-data.sql

八、慢查询日志

存储慢查询日志需要大量的存储空间。

慢查询日志会记录所有满足条件的SQL，包括查询语句和SQL语句以及以及回滚的SQL。

1、慢查询日志相关配置

//启动或停止记录慢查询日志

slow_query_logON|OFF

//指定慢查询日志的存储路径及文件,默认会存放在数据目录下，建议将日志目录和数据目录分开。

slow_query_log_file//指定记录慢查询日志SQL执行时间的阈值。默认是秒为单位(10s)，目前可以支持毫妙。改为 0.001s较为合理。

log_query_time//是否记录未使用索引的SQL

log_queries_not_using_indexes

如果只想在某个时刻来启动慢查询日志，可以通过脚本来 set global 控制这个 slow_query_log 参数。

2、启用慢查询日志

SET GLOBAL slow_query_log=on;

慢查询日志记录的内容(经过处理后的)。

3、常用慢查询日志分析工具

a、mysql官方提供的 mysqldumpslow工具，并且会随着mysql一起安装。

该工具会汇总所有除查询条件外其它完全相同的sql，并将分析结果按照参数中所指定的顺序输出。

mysqldumpslow -s r -t 10 mysql-slow.log

mysqldumpslow工具返回的结果示例：

b、pt-query-digest是一款更全面的慢查询日志分析工具。不过这个工具需要单独安装。

使用方法

九、实时获取存在性能问题的SQL。

1、如何实时获取有性能问题的SQL。

最简单有效的方法就是利用infomation_schema.processlist表。

//这个时间根据实际情况进行修改SELECT `id`,`user`,`host`,`DB`,`command`,`time`,`state`,`info` FROM information_schema.PROCESSLIST WHERE TIME >= 60;

我们可以通过一个脚本定时的执行这个语句对mysql的查询情况进行监控，以达到实时获取有性能问题的SQL语句。

十、SQL解析预处理及生产执行计划

1、mysql处理查询请求的整个过程

2、查询缓存对性能的影响

mysql在进行一个查询时，会优先检查这个查询是否命中查询缓存中的数据，mysql是通过一个对大小写敏感的哈希查找来实现的，由于hash查找只能进行全值匹配，因此哪怕有一个字节的不同也不会命中查询缓存，因此要写命中查询缓存并不容易。如果命中了缓存，在返回查询结果之前还会检查用户权限，如果权限没有问题，mysql就会跳过所有阶段，直接从查询缓存中返回查询结果。

就算是更新了不涉及所查询的字段，查询缓存也会失效。而且每次检查查询缓存是否命中时都会对缓存加锁，故对于一个读写频繁的系统来说，查询缓存很可能会降低查询处理的效率。所以在这种情况下，建议就不要启用查询缓存了。

3、查询缓存的一些参数

//设置查询缓存是否可用, DEMAND表示只有在查询语句中使用 SQL_CACHE 或者 SQL_NO_CACHE 来控制是否需要缓存

query_cache_type=ON|OFF|DEM//设置查询缓存的内存大小，必须设置为1024的整数倍

query_cache_size//设置查询缓存可用存储的最大值

query_cache_limit//设置数据表被锁后是否返回缓存中的数据，默认是关闭的

query_cache_wlock_invalidate//设置查询缓存分配的内存块的最小单位

query_cache_min_res_unit

如果我们事先就知道某条SQL不会命中查询缓存，我么可以在SQL语句中加上 SQL_NO_CACHE ，这样是可以提高查询效率的。

mysql是依赖存储引擎提供的统计信息来评估并生成执行计划的。但是往往这个统计信息都是一个估算值，并不一定都是正确的。

4、造成mysql生成错误执行计划的原因