老大说：你行吗？我说：你让我负责，我就肯定行。（当时年轻气盛，当然现在还是很年轻。）老大说：正好，下午有个数据库访问慢，你跟我去解决一下怎么样？我：犹豫了3秒，坚定的回答：没问题。（因为当时把SQL索引依然练得如火纯情的地步，要不我可不敢随便就拦下来。）

老大又发问了：你跟我说说，数据库访问慢怎么优化？

我当时回答：从慢查询里发现，执行慢的SQL语句，分析这个语句 做适当的索引（前缀索引，唯一索引，B-Tree数据结构来存储数据，哈希索引基于哈希表实现）InnoDB引擎没有哈希索引。

只有：Memory引擎支持哈希指针。

    NDB集群引擎也支持唯一哈希索引。


索引的优点

索引大大减少了服务器需要扫描的数据量

索引可以帮助服务器避免排序和临时表

索引可以将随机I/O变为顺序I/O



今天来补充更好解决大并发，大表 访问慢 的问题，今天说说没有说到的“分区”：

分区表

         对用户来说：分区表是一个独立的逻辑表，但是底层有多个物理子表组成。

实现分区的代码实际上是一组底层表的句柄语句（Handler Object）的封装。对分区表的请求，都会通过句柄对象转化对存储引擎的接口调用。分区对于SQL层来说是一个完全封装实现的黑盒子，对应用是透明的，但是从底层的文件系统来看就很容易发现，每一个分区表都有一个使用#分割命名的表文件。

         MySQL实现分区表的方式-对底层表的封装-意味着索引也是按照分区的子表定义的，而没有全局索引。

         MySQL在创建表时使用PARTITIONBY字句定义每一个存放的数据。在执行查询的时候，优化器会根据分区定义过滤的那些没有我们需要数据的分区，这样查询就无需扫描所有分区-只需要查找包含需要数据的分区就可以了。

         分区的目的：将数据按照一个粗糙的粒度在不同的表中。可以将相关的数据放到一起，想一次性删除整个分区也会变得很方便。

以下场景，分区有非常大的作用：

         表非常大以至于无法全部放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据。

         批量删除大量数据可以清除整个分区方式。可以对一个独立分区进行优化、检查、修复等操作。

         分区表的数据可以分布在不同的物理设备上，高效利用多个硬件设备。

         使用分区表来避免某些特殊的瓶颈。InnoDB的单个索引互斥访问，ext3文件系统的innodb锁竞争。

         备份和恢复独立的分区，在非常大的数据集场景下效果很好。

分区表的限制

一个表最多只能1024个分区。

MySQL5.1中，分区表达式必须是整数，或者返回证书的表达式。在MySQL5.5中，某些场景中可以使用列来进行分区

如果分区字段有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来

分区表中无法使用外键约束

 

每个操作都会”先打开并锁住所有的底层表”并不能说分区表在处理过程中是锁住全表的。存储引擎能够自己实现行级锁（比如innodb）

分区表类型

MySQL支持多种分区表。最多的是根据范围进行分区。分区表达式科研室列，也可以是包含列的表达式。

mysql> CREATE TABLE seles(

   -> order_date DATETIME NOT NULL

   -> )engine=InnoDB PARTITION BY RANGE(YEAR(order_date))(

   -> PARTITION p_2010 VALUES LESS THAN (2010),

   -> PARTITION p_2011 VALUES LESS THAN (2011),

   -> PARTITION p_2012 VALUES LESS THAN (2012),

   -> PARTITION p_catchall VALUES LESS THAN MAXVALUE );

Query OK, 0 rows affected (1.16 sec)

PARTITION 分区字句中可以使用各种函数。但有一个要求，表达式返回的值要是一个确定的整数，且不能是一个常数。这里我们使用函数YEAR(),也可以使用任何其他的函数。如TO_DAYS().

MySQL还支持键值，哈希和列表分区。

系统通过子表可降低索引的互斥访问的竞争。频繁地访问，这会导致互斥量的竞争。使用哈希分区可以将数据切成多个小片，大大降低互斥量的竞争关系。

其他的分区技术包括：

         根据键值进行分区，来减少innoDB的互斥量竞争。

         使用数学摸函数来进行分区，然后将数据轮训放入不同的分区。

         假设表有一个自增的主键列id，希望根据时间将最近的热点数据集中存放。那么必须将时间戳包含在主键当中才行，而这和主键本身的意义相矛盾。这种情况下也可以使用这样的分区表达式来实现相同的目的。另一个方面比起时间范围分区还避免了一个问题，就是当超过一定阀值，如果使用时间范围分区就必须新增分区。

如何使用分区表

         首先，数据量巨大，不能在每次查询使用扫描全表。索引在空间和维护上消耗，也不希望使用索引。即使真的使用索引，会发现数据不是按照想要的方式聚集的，有大量的碎片产生，导致一个查询成千上万的随机I/O，应用程序随之僵死。

两条路：让所有查询都只在数据表上做顺序扫描，或者将数据表和索引全部缓存在内存里。

         数据量超大时候：B-Tree索引就无法起作用了。除非是索引覆盖查询。索引维护（磁盘空间，I/O操作）的代价也非常高。有些系统，如Infobright，就完全放弃B-Tree索引，选择了一些粗粒度但是消耗更少的方式来检索数据，大量数据之索引对应的一小块元数据。

         分区无需额外的数据结构记录每个分区有哪些数据-分区不需要精确定位每条数据的位置，也就无需额外的数据结构-所以代价非常低。

保证大数据量的可扩展性，一般有下面两个策略：

         全量扫描数据，不要任何索引。

可以使用简单的分区方式存放表，不用任何索引，根据分区的规则大致定位需要的数据位置。只要能够使用where条件，将需要的数据限制在少数分区中，则效率是很高的。内存相对小，数据很快会被挤出内存，所以缓存起不了任何作用。这个策略适用于以正常的方式访问大量数据的时候。

         索引数据，并分离热点

如果数据有明显的热点，除了这部分数据其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会缓存在内存中。这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效使用缓存。

什么情况下会出现问题

         以上介绍了两个条件，使用分区。

1全量扫描数据，不要任何索引。

2索引数据，并分离热点

查询都能够过滤掉很多分区，分区本身并不会带来很多额外的代价。事实证明，这两个假设在某些场景下，会有问题。下面介绍可能会遇到的问题

NULL值会使用分区过滤无效

分区列和索引列不匹配

选择发呢去的成本可能很高

打开锁住所有底层表的成本可能很高

维护分区的成本可能很高

 

分区的限制

所有分区都必须使用相同的存储引擎

分区函数中可以使用的函数和表达式也有一些限制

某些存储引擎不支持分区

对于MyISAM的分区表，不能在使用LOAD INDEX INTO CACHE操作

对于MyISAM表，使用分区表时需要打开更多的文件描述符。

 

一个分区对于存储引擎来说都是一个独立的表。这样即使分区只占用一个表缓存数目，文件描述符也会有多个。即使配置了合适的表缓存，以确保不会超过操作系统的单个进程可以打开的文件描述符个数，对于分区表而言，还是会出现超过文件描述符限制的问题。

所有的软件都有bug，分区表在MySQL5.1中引入，在后面5.1.40和5.1.50之后修复了很多分区表的bug。在MySQL5.6做了更多的增强，引入ALTER TABLE EXCHANGE PARTITION.

wKioL1Y8JRvzdPlOAAE1RYJHjQA554.jpg

wKiom1Y8JOShw9xlAADc9i4jPhE865.jpg


查询优化

         引入分区给查询优化带来了新的思路（同时也带来了新bug）。分区最大的优点：优化器可以根据分区函数来过滤一些分区。根据粗细度索引的优势，通过分区过滤通常可以让查询扫描更少的数据（在某些场景下）

         对于访问分区表来说，在WHERE条件中带入分区列，有时候即使看似多余的也要带上，这样就可以让优化器能够过滤掉无须访问的分区。如果没有这些条件，MySQL就需要让对应的存储引擎访问这个表所有分区，如果表非常大的话，就可能非常慢。

 

使用EXPLAIN PARTITION 可以观察优化器是否执行了分区过滤。

 

mysql> mysql> EXPLAIN PARTITIONSSELECT * FROM seles\G

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: seles

  partitions: p_2010,p_2011,p_2012,p_catchall

        type: ALL

possible_keys: NULL

         key: NULL

     key_len: NULL

         ref: NULL

        rows: 4

       Extra: NULL

1 row in set (0.00 sec)

我们可以观察：partitions 行的变化以及rows的区别。

mysql> EXPLAIN PARTITIONS SELECT * FROMseles WHERE order_date > '2011-01-01'\G  

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: seles

  partitions: p_2012,p_catchall

        type: ALL

possible_keys: NULL

         key: NULL

     key_len: NULL

         ref: NULL

        rows: 2

       Extra: Using where

1 row in set (0.00 sec)

 

MySQL优化器已经很善于过滤分区。它能够将范围条件转化为离散的值列表，并根据列表中的每个值过滤分区。然而优化器不是万能的，下面查询的WHERE条件理论可以过滤分区，实际却是不行的。

mysql> EXPLAIN PARTITIONS SELECT * FROMseles WHERE YEAR(order_date) = 2010 \G

*************************** 1. row***************************

          id: 1

 select_type: SIMPLE

       table: seles

  partitions: p_2010,p_2011,p_2012,p_catchall

        type: ALL

possible_keys: NULL

         key: NULL

     key_len: NULL

         ref: NULL

        rows: 4

       Extra: Using where

1 row in set (0.00 sec)

 

可以发现这条语句用了YEAR()这个函数。

 

MySQL只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的值过滤分区。即使表达式就是分区函数也不行。

mysql> EXPLAIN PARTITIONS SELECT * FROMseles WHERE order_date BETWEEN '2010-01-01' AND '2010-12-31' \G

*************************** 1. row***************************

          id: 1

  select_type:SIMPLE

       table: seles

  partitions: p_2011

 

这种是可以的。


下面继续介绍索引。


索引是最好的解决方案吗？

索引并不总是最好的工具。只有当索引帮助存储引擎快速查找出记录带来的好处大于其打来的额外工作时，索引才是有效的。

小表：全表扫描更有效。

中到大型的表：索引就非常有效了。

特大型的表：建立和使用索引的代价逐渐将随之增长。这种情况下，需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录的匹配。例如：分区技术。


维护索引和表

即使用正确的类型创建了表并加上了合适的索引，工作也没有结束；还需要维护表和索引用来确定它们都正常工作。维护表有三个目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。


找到损坏的表

损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题，严重时甚至还会导致数据库崩溃。

如果遇到一些古怪问题：例如不该发送的错误 可以运行：CHECK TABLE来检查是否发送表损坏。CHECK TABLE通常能找出大多数表和索引的错误。

MariaDB [test]> check table dong;

+-----------+-------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+-----------+-------+----------+----------+

| test.dong | check | status | OK |

+-----------+-------+----------+----------+

可以使用REPAIR TABLE命令来修复损坏的表，也可以通过不做任何操作（no-op）的ALTER操作来重建表，列入修改表的存储引擎为当前引擎。


减少索引和数据的碎片

B-Tree索引可能会碎片化，这回降低查询的效率。碎片化的索引可能会以很差或者无序的方式存储在磁盘上。

B-Tree需要随机磁盘访问才能定位到叶子页，所以随机访问是不可避免的。

数据存储的碎片化比索引更加复杂。

行碎片化；行间碎片化；剩余空间碎片化；

MiSAM表：这三类碎片化都可能发生。InnoDB不会出现短小行碎片；

OPTIMIZE TABLE或者导出在导入方式来重新整理数据。

MyISAM:通过排序算法重建索引的方式来消除碎片。

InnoDB:在线”添加和删除索引的功能”，可以通过先删除，在重新创建索引来消除索引的碎片化。


如何判断一个系统创建的索引是合理的呢？

响应时间来对查询进行分析。找出那些消耗最长时间的查询或者那些给服务器带来最大压力的查询。检查这些查询的：schema、SQL和索引结构，判断是否有查询扫描了太多的行，是否做了很多额外的排序或者使用了临时表，是否使用随机I/O访问数据，或者是有太多回表查询那些不再索引中列的操作。