高性能MySql 4至6章读书笔记

第四章 Schema设计

选择优化的数据类型

更小的通常更好，但是要确保没有低估需要存储的值的范围，因为在schema中的多个地方增加数据类型范围是一个十分耗时的操作。

简单就好

简单的数据类型的操作通常需要更少的cpu时间。

尽量避免NULL

可为NULL的列使得索引、索引比统计和值比较都更为复杂。

当然也有例外，InnoDB使用单独的位存储NULL值， 所以对于稀疏数据有很好的空间效率。

选择标识符

一旦选定一种类型，要确保在所有的关联表中都使用同样的类型。类型之间需要精确匹配，包括像UNSIGNED这样的属性。尽量只用整型定义标识符。

注意可变长字符串

其在临时表和排序时可能导致悲观的按最大长度分配内存

范式与反范式

范式是好的，但是反范式有时也是必须的，并且能带来好处。

第五章 创建高性能索引

B-Tree 索引的查询类型

• 全值匹配： 指的是和索引的所有列进行匹配
• 匹配最左前缀： 查找索引前几列进行匹配
• 匹配列前缀: 只匹配某一列的值的开头部分
• 匹配范围值： 查找索引某一范围的值
• 精确匹配某一列并范围匹配另外一列
• 只访问索引的查询：覆盖索引

B-Tree 索引的限制

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

• 不能跳过索引中的列

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

  例如查询 索引为key(last_name, fisrt_name, dob)

  where last_name = 'a' and first_name like 'J%' and dob = '1877-12-23'

索引的优点

1. 大大减少服务器需要扫描的数据量
2. 帮助服务器避免排序和临时表
3. 将随机I/O变为顺序I/O

高性能索引-独立的列

如果查询中列不是独立的，则mysql不会使用索引

select actor_id from sakila.actor where actor_id + 1 = 5;

高性能索引-前缀索引和索引选择性

有时候需要索引很长的字符列，通常可以索引开始部分的字符，同时也会降低索引的选择性。

索引的选择性指的是，不重复的索引值和数据表的记录总数的比值。索引的选择性越高表示查询效率越高，因为选择性高的索引可以过滤掉更多的行。

前缀索引是一种能使索引更小，更快的有效方法，但是也有其缺点：前缀索引无法做order by 和 group by，也无法使用前缀索引做覆盖索引。

高性能索引-多列索引

最容易遇到的困惑是多列索引的顺序，正确的顺序依赖于使用索引的查询，同时需要考虑如何更好的满足排序和分组需要。对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放在索引的最前列。只有不需要考虑排序和分组时，将选择性跟高的列放在最前面通常是最好的，但是考虑问题需要更全面，避免随机I/O和排序更加重要。

高性能索引-覆盖索引

如果一个索引包含所需要查询的字段的值，我们就可以称之为“覆盖索引”

覆盖索引的好处：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那mysql就会极大的减少数据访问量。
• 因为索引是按照列值顺序存储的，所以对于I/O密集型范围查询回避随机从磁盘读取每一行数据的I/O要小的多
• 由于Innodb的聚簇索引，覆盖索引对Innodb表特别有用，可以避免对主键索引的二次查询。

覆盖索引的陷阱：

select * from products where actor = 'SEAN CARREY' and title like '%APOLLO%';

• 没有索引能够覆盖这个查询，因为查询从表中选择了所有的列
• mysql不能再索引中执行like操作，只能做最左前缀匹配

高性能索引-使用索引扫描来做排序

mysql有两种方式可以生成有序的结果：通过排序操作；或者使用索引顺序扫描。mysql可以使用同一个索引既满足排序，有用于查找行。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，mysql才能使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能用索引做排序。ORDER BY子句和查询的限制是一样的：需要满足索引的最左前缀的要求。

有一种情况下ORDER BY子句可以不满足索引的最左前缀要求：

select rental_id, staff_id from sakila.rental where rental_date = '2005-05-25'
order by inventory_id, customer_id;

索引为key(rental_date, inventory_id, customer_id)，前导列为常量的时候，如果where子句或者join子句中对这些列指定了常量，就可以弥补ORDER BY的不足。

where rental_date > '2005-12-25' order by inventory_id, customer_id;
where rental_date = '2005-12-25' and inventory_id in (1, 2) order by cusomter_id;

对于索引上是范围查询，mysql无法使用之后的索引列

高性能索引-使用索引扫描减少锁

索引可以让查询锁定更少的行，如果你的查询从不访问那些不需要的行，那么就会锁定更少的行。但这只有当innoDB在存储引擎层能够过滤掉所有不需要的行是才有效。如果索引无法过滤掉无效的行，那么innoDB检索到数据并返回给服务器层后，innoDB已经锁定这些行了（mysql 5.6后没有这个问题）。

高性能索引-避免多个范围条件

下面的查询：

where last_online > date_sub(now(), interval 7 day) and age bwtween 18 and 25

这个查询有一个问题：它有两个范围条件,last_online和age列，mysql可以使用last_online的索引或者是age列的索引，但是无法同时使用它们。

高性能索引-延迟关联优化分页

如果一个查询匹配结果有上百万行的话会怎样？

select * from profiles where sex = 'm' order by rating limit 10;

即使有索引，如果用户界面需要翻页，并且翻页到比较靠后的地方也会非常慢，如：

select * from profiles where sex = 'm' order by rating limit 1000000, 10;

无论如何创建索引，这种查询都是个严重的问题，mysql需要花费大量时间来扫描需要丢弃的数据。其中一个解决的办法是限制能够翻页的数量。

优化这类索引的另一个比较好的策略是使用延迟关联，通过使用覆盖索引返回需要的主建，再根据这些主建回主表获得需要的行，这样可以减少mysql扫描需要丢弃的行数。

select * from profiles innner join 
(
    select id fomr profiles p where p.sex = 'm' order by rating limit 1000000, 10

) as t using (id);

第六章 慢查询优化

优化数据访问

查询性能低下最基本的原因是访问数据太多。某些查询可能不可避免的需要筛选大量数据，单这并不常见。大部分性能低下的查询都可以通过减少访问的数据量的方式进行优化。对于低效的查询，我们发现通过下面几个步骤来分析总是很有效：

1. 确认应用程序时候检索大量超过需要的数据。
2. 确认mysql服务层是否在分许大量超过需要的数据行。

第一种情况可以使用limit和选择需要的列来解决。在确定查询只返回需要的数据之后，接下来应该看看查询为了返回结果是否扫描了过多的数据，对于mysql有三个衡量查询开销的指标：

• 响应时间
• 扫描的行数
• 返回的行数

没有那个指标能完美地衡量查询的开销，但它们大致反映了mysql内部查询时需要访问多少数据，并可以大概推算出查询运行的时间。

在评估查询开销的时候，需要考虑一下从表中找到某一行数据的成本。mysql有好几种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果，也有些访问方式可能无需扫描就能返回结果。

在explain语句中的type列反应了访问类型。访问类型有很多种，从全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等。这里列的这些，速度是从慢到快，扫描行数也是从大到小。如果查询没有办法找到合适是访问类型，那么解决的最好办法通常是增加一个合适的索引。

一般mysql能够使用如下三种方式应用where条件，从好到坏依次为：

• 从索引中使用where条件吗来过滤不匹配的记录，这是在存储引擎层完成的。
• 使用索引覆盖扫描来返回记录（extra出现using index），直接从索引中过滤不需要的数据并返回命中结果。这是在mysql服务层完成的，但无需再回表查询记录。
• 从数据表中返回数据，然后过滤不满足条件的记录（extra出现using where）。这是在mysql服务器层完成，mysql需要从数据表中读出记录然后过滤。

如果发现查询需要扫描大量的数据但只返回少数的行，那么可以使用下面的技巧去优化它：

• 使用覆盖索引，把需要用的列都放到索引中
• 改变库表结构。例如使用单独的汇总表
• 重写复杂的查询， 让mysql优化器能够以更加高效的方式执行这个查询

重构查询方式-切分查询

有时候对于一个大查询我们需要分而治之，将大查询分成小查询。删除旧数据是一个很好的例子，如果用一个大的语句一次性完成，则可能一次需要锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞恨到重要的查询。

重构查询方式-分解关联查询

例如下面这个查询：

select * from tag
join tag_post on tag_post.tag_id = tag.id
join post on tag_post.post_id = post.id
where tag.tag = 'mysql';

可以分解成下面这个查询来代替：

select * from tag where tag = 'mysql';

select * from tag_post where tag_id = 1234;

select * from post where post.id in (123,456,567);

用分解关联查询的方式重构查询有如下的优势：

• 让缓存效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。
• 将查询分解后，执行单个查询可以减少锁的竞争。
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和高扩展。
• 查询本身效率可能提升，使用in代替关联查询，可能比随机关联更高效。
• 减少冗余记录的查询
• 相当于在应用层实现了哈希关联

重构查询方式-优化关联查询

• 确保on或者using子句上的列上有索引。
• 确保任何group by和 order by 的表达式中只涉及到一个表中的列，这样mysql才有可能使用索引来优化这个过程