《高性能MySQL》第五章创建高性能索引(篇一)

事物隔离级别

• READ UNCOMMITTED (未提交读）
  在 READ UNCOMMITTED 级别，事务中的修改，即使没有提交，对其他事务也都是可见 的。事务可以读取未提交的数据，这也被称为脏读(Dirty Read)。这个级别会导致很多问题，从性能上来说，READ UNCOMMITTED 不会比其他的级别好太多，但却缺乏 其他级别的很多好处，除非真的有非常必要的理由，在实际应用中一般很少使用。

• READ COMMITTED (提交读）
  大多数数据库系统的默认隔离级别都是READCOMMITTED (但MySQL不是）。READCOMMITTED满足前面提到的隔离性的简单定义：一看见已
  经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读(nonrepeatableread), 因为两次执行同样的查询，可能会得到不一样的结果。

• REPEATABLE READ (可重复读）
  REPEATABLE READ解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读(Phantom Read)的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行(Phantom Row)。InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC, Multiversion Concurrency Control)解决了幻读的问题。本章稍后会做 进一步的讨论。
  可重复读是 MySQL 的默认事务隔离级别。

• SERIALIZABLE (可串行化）
  SERIALIZABLE 是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读 的问题。简单来说，SERIALIZABLE 会在读取的每一行数据上都加锁，所以可能导致 大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只有在非常需 要确保数据的一致性而且可以接受没有井发的情况下，才考虑采用该级别。

1.索引的优点

• 索引大大减少了服务器需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机I/O变为顺序I/O

2.独立的列

• 无法使用索引的例子无法用到xxx.id的索引

select xxx from xxx where xxx.id+1=5

MySQL无法自动解析这个方程式,养成习惯：始终将索引列单独放在比较符的另一侧。

3.前缀索引和索引的选择性

有时候索引很长的字符列会让索引变的大企且慢，通常可以索引开始的部分字符，可以大大节约索引空间，从而提供索引效率。
（1）找出最常见值的列表，然后和最常见的前缀列表进行比较
count(distinct left(最常见的字符,截取的长度)/count(*) 接近0.031
add key(字符(长度))
缺点：无法用前缀索引做order by和group by 也无法做覆盖扫描

4.多列索引

• 常见错误: 为每个列创建独立的索引或者按照错误的顺序创建多列索引

5选择合适的索引列顺序

• 以下面的查询为例

select * from payment where staff_id=2 and customer_id = 584

• 结论：
  先看各个where条件的分支对应的数据基数有多大

sum(staff_id=2):7992
sum(customer_id=684):30

• customer id 的选择性更高，所以答案是将其作为索引列的第一列，
  mysal> ALTER TABLE payment ADD KEY(customer_id, staff_ id);

• 这样做有一个地方需要注意，查询的结果非常依赖于选定的具体值。如果按上述办法优化，可能对共他一些条什值的查询不公平，服务器的整体性能可能变得更糟，或者其他某些查询的运行变得不如预期。如果是从诸如 pt-query-digest 这样的工具的报告中提取“最差”查询，那么再按上述办法选定的索引顺序往往是非常高效的。如果没有类似的具体查询来运行，那么最好还是按经验法则来做，因为经验法则考虑的是全局基数和选择性，而不是某个具体查询

6.聚簇索引

• 聚簇索引 并不是一种单独的索引类型， 而是一种数据存储方式。 具体的细节依赖千其实现方式， 但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中。 术语 “聚簇＂表示数据行和相邻的键值紧凑地存储在一起注 8。因为无法同时把数据行存放在两个不同 的地方，所以一个表只能有一个聚簇索引。因为是存储引擎负责实现索引， 因此不是所有的存储引擎都支持聚簇索引。 本节我们主要关注InnoDB, 但是这里讨论的原理对千任何支持聚簇索引的存储引擎都是适用的。

• 图下展示了聚簇索引中的记录是如何存放的。 注意到， 叶子页包含了行的全部数据，但是节点页只包含了索引列。 在这个案例中， 索引列包含的是整数值。
  

• 聚集的数据有一些重要的优点：
  
  

7.覆盖索引

• 通常大家都会根据查询的 WHERE 条件来创建合适的索引， 不过这只是索引优化的一个方面。 设计优秀的索引应该考虑到整个查询， 而不单单是 WHERE 条件部分。 索引确实是一 种查找数据的高效方式， 但是 MySQL 也可以使用索引来直接获取列的数据， 这样就不 再需要读取数据行。 如果索引的叶子节点中已经包含要查询的数据， 那么还有什么必要再回表查询呢？如果一个索引包含（或者说覆盖）所有需要查询的字段的值， 我们就称 ＜亟］之为 ＂覆盖索引” 。
  覆盖索引是非常有用的工具， 能够极大地提高性能。 考虑一下如果查询只需要扫描索引
  而无须回表， 会带来多少好处：
  由千 InnoDB 的聚簇索引， 覆盖索引对 lnnoDB 表特别有用。 InnoDB 的二级索引在 叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询， 则可以避免对主 键索引的二次查询。

8.使用索引扫描来做排序

• MySQL 有两种方式可以生成有序的结果：通过排序操作 1 或者按索引顺序扫描注 13 I 如果 EXPLAIN 出来的 type 列的值为 “index”, 则说明 MySQL 使用了索引扫描来做排序（不 要和 Extra 列的 “Using index” 搞混淆了）。
  扫描索引本身是很快的， 因为只需要从一条索引记录移动到紧接着的下一条记录。 但如 果索引不能覆盖查询所需的全部列， 那就不得不每扫描一条索引记录就都回表查询一次对应的行。 这基本上都是随机1/0, 因此按索引顺序读取数据的速度通常要比顺序地全 表扫描慢， 尤其是在1/0密集型的工作负载时。MySQL 可以使用同一个索引既满足排序， 又用千查找行。 因此， 如果可能， 设计索引 时应该尽可能地同时满足这两种任务， 这样是最好的。

9.排序操作 索引顺序扫描

• 如果用explain 出来的列为type的值为index 说明用了索引扫描来做排序
  只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向(倒叙或正序)都一样时。 还有一个最坐前缀

where date=xxx order by id= xxx
查询索引的第一列提供了常量 第二列进行排序 就形成了最左前缀

where date> xxx order by date,id
order by 使用的两列就是索引的最左前缀

• 不能使用索引做排序的查询

where date> xxx order by date desc,id asc
不同排序方向
或者引用了不在索引的列
多个查询条件in() 对于排序来说 是一种范围条件

10.冗余和重复索引

• 重复是指相同列按照相同顺序创建相同类型的索引
• 有时也会不经历创建出同个列不同类型索引也应该避免
  unique(id)
  index(id)

冗余索引。关键字