高性能MySQL知识点总结（七）——MySQL高级特性

（一）、 7.1 分区表

1. 对用户来说，分区别是一个独立的逻辑表，但是底层由多个物理子表组成。实现分区的代码实际上是一组底层表的句柄对象的封装。对分区表的请求，都会通过句柄对象转换成对存储引擎的接口调用。所以分区对于SQL来说是一个完全封装底层实现的黑盒子，对应用是透明的。
2. MySQL在创建表时使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询就无需扫描所有分区。
3. 分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中，这样就可以将相关的数据存放在一起。
4. 分区表的一些作用：
   1. 表非常大以至于无法全部存放在内存中，或者只在表的最后部分有点热点数据，其他均是历史数据。
   2. 分区表的数据更容易维护。
   3. 分区表的数据可以分布在不同的物理设备上
   4. 可以使用分区表来避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问
   5. 如果需要，还可以备份和恢复独立的分区。
5. 分区表的一些限制：
   1. 一个表最多只能有1024个分区。
   2. 在MySQL5.5中，某些场景中可以直接使用列来进行分区
   3. 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
   4. 分区表无法使用外键约束。

7.1.1 分区表的原理

1. 存储引擎管理分区的各个底层表和管理普通标一样（所有底层表都必须使用相同的存储引擎），分区表的索引只是在各个底层表上各自加上一个完全相同的索引。
2. 分区表上的操作按照下面的操作逻辑进行：
   1. SELECT查询：
   　　　当查询一个分区表的时候，分区表先代开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据。
   ２.INSERT操作：
   　　　当写入一条记录时，分区表先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应底层表。
   3.DELETE操作：
   　　　当删除一条记录时，分区表先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层进行删除操作。
   3.UPDATE操作：
   　　　当更新一条记录时，分区表先打开并锁住所有的底层表，MySQL先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。
3. 虽然每个操作都会“先打开并锁住所有的底层表”，但这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁，例如InnoDB，则会在分区层释放对应表锁。

7.1.2 分区表的类型

1. MySQL支持多种分区表。但是看到最后的根据范围进行分区，每个分区存储落在某个范围的记录，分区表达式可以是列，也可以使包含列的表达式。
2. 例如下面试将每一年的销售额存放在不同分区中：

CREATE TABLE sales(
	order_date DATETIME NOT NULL
	-- Other columns ommitted
)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_cathcal VALUES LESS THAN MAXVALUE);

3. PARTITION分区子句中可以使用各种函数。但有一个要求，表达式返回的值要是一个确定的整数，且不能是一个常数。

7.1.3 如何使用分区表

1. 为了保证大数据量的可扩展性，一般有下面两种策略：
2. 全量的数据扫描，不要任何索引：
   • 可以使用简单的分区方式存放表，不要任何索引，根据分区的规则大致定位需要的数据位置
   • 只要能够使用WHERE条件，将需要的数据限制在少数分区中，则效率是很高的
     3.索引数据，并分离热点
   • 如果数据有明显的“热点”，而且除了这部分数据，其他数据很少被访问到，那么可以将这部分热点数据单独放在一个分区中，让这个分区的数据能够有机会都缓存在内存中。
   • 这样查询就可以只访问一个很小的分区表，能够使用索引，也能够有效使用缓存。

7.1.4 什么情况下会出问题

1. 前面的分区策略都基于两个重要的假设：查询都能过滤掉很多额外的分区、分区本身并不会带来很多额外的代价。但某些场景下是会有问题的。
2. NULL值会使分区过滤无效
   1. 第一个分区是特殊分区，假设安装刚刚PARTITION BY RANGE YEAR(order_date)分区，那么所有的NULL或是非法值都会进入第一个分区。假如想获得‘2012-01-01’~’2012-01-31’之间的数据，数据库并不会只扫描第二分区，同时也会扫描第一分区，因为NULL值存在里面。如果第一分区数据量很大的话，就会造成性能低下。
   2. 其中一个解决办法就是在第一个分区创建一个“无用”的分区，当不存在NULL值或非法值的时候，第一个分区就为空。
   3. 但是在MySQL5.5之后就不需要这个优化技巧了，因为可以直接使用列本身而不是基于列的函数进行分区。
3. 分区列和索引列不匹配
   1. 如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤。
   2. 假设在列a上定义了索引，而在列b上进行分区。因为每个分区都有其独立的索引，所以扫描列b上的索引就需要扫描每一个分区内对应的索引。
   3. 所以应该避免建立和分区列不匹配的索引，除非查询中还同时包含了可以过滤分区的条件。
4. 选择分区的成本可能很高
   1. 不同类型的分区可能针对不同的场景，使用不正确的分区方式会导致代价很高。
5. 打开并锁住所有底层表的成本可能很高
   1. 当查询访问分区表的时候，MySQL需要打开锁住所有的底层表，这是分区的一个开销，并且无法优化。
   2. 我们可以使用批量操作来代替单个操作来减少开销。
6. 维护分区的成本可能很高
   1. 例如使用重组分区或者类似ALTER语句的时候成本较高。
7. 一些限制：
   1. 所有分区必须使用相同的存储引擎
   2. 分区函数中可以使用的函数和表达式也有一些限制
   3. 某些存储引擎不支持分区
   4. 对于MyISAM的分区表，不能再使用LOCAL INDEX INTO CACHE操作

7.1.5 查询优化

1. 对于访问分区表来说，很重要的一点就是要在WHERE条件中带入分区列，有时候即使看似多余也要带上，这样就可以让优化器能过过滤掉无须访问的分区。如果没有这些条件，就会访问所有分区。

   EXPLAIN SELECT * FROM sales;
   

   

2. 如果添加上WHERE 限制条件
   

3. 一个很重要的原则是：即便在创建分区时可以使用表达式，但在查询时却只能根据列来过滤分区。

（二）、 7.2 视图

1. 在使用SQL语句访问视图的时候，它返回的数据是MySQL从其他表中生成的。视图和表是在同一个命名空间。不过视图和表也有不同。例如，不能对视图创建触发器，也不能使用DROP TABLE命令创建视图。

2. 创建视图：

   CREATE VIEW Oceania AS
   	SELECT * FROM Country WHERE Continent = 'Oceania'
   	WITH CHECK OPTION;
   

3. 使用视图

   SELECT Code,Name FROM Oceania WHERE Name = 'Australia';
   

7.2.1 可更新视图

1. 可更新视图时指可以通过更新这个视图来更新视图涉及的相关表。只要指定了合适的条件，就可以更新，删除甚至向视图中写入数据。

   UPDATE Oceania SET Population = Population *1.1 WHERE Name = 'Australia';
   

2. 如果视图定义中包含了GROUP BY、UNION、聚合函数，就不能被更新了。所有使用临时表算法实现的视图都无法被更新

（四）、 7.4 在MySQL内部存储代码

1. MySQL允许通过触发器、存储过程、函数的形式来存储代码。从MySQL5.1开始，还可以在定时任务中存放代码，这个定时任务也被称为“事件”。
2. 存储过程和存储函数可以接收参数然后返回值，但是触发器和事件不行。
3. 使用外键是有成本的。比如外键通常都要求每次在修改数据时都要在另外一张表中多执行一次查找操作。

7.4.1 存储过程和函数

1. 创建存储过程的语句：

CREATE PROCEDURE insert_many_rows(IN loops INT)
BEGIN
	DECLARE v1 INT;
	SET v1 = loops;
	WHILE v1 > 0 DO	
	INSERT INTO test_table values(NULL,0,'qqq','qqqwww')
	SET V1 = V1 - 1
	END WHILE;
END;

7.4.2 触发器

1. 触发器可以让你在执行INSERT、UPDATE或者DELETE的时候，执行一些特定的操作，可以在MySQL中指定SQL语句执行前出发还是在执行后出发。触发器本身没有返回值，不过它们可以读取或者改变出发SQL语句所影响的数据。
2. 对每一个表的每一个事件，最多只能定义一个触发器。（不能再AFTER、INSERT上定义两个触发器）
3. MySQL只支持“基于行的出发”。也就是说，触发器始终是针对一条记录的，而不是针对整个SQL语句的。
4. 触发器不能保证原子性

7.4.3 事件

1. 事件是MySQL5.1引入的一种新的存储代码的方式。它类似于Linux的定时任务。你可以创建事件，指定MySQL在某个时候执行一段SQL代码，或者每隔一个时间间隔执行一段SQL代码。

2. 事件在一个独立事件调度线程中被初始化，这个线程和处理连接的线程没有任何关系，它不接收任何参数，也没有任何的返回值。可以在MySQL的日志中看到命令的执行日志。还可以在表INFORMATION_SCHEMA.EVENTS中看到各个事件状态，例如这个事件最后一次执行时间。

3. 下面的例子创建了一个时间，它会每周一次针对某个数据库运行一个存储过程：

   CREATE EVENT optimize_somedb ON SCHEDULE EVERY 1 WEEK
   DO
   CALL optimize_tables('somedb');
   

4. 如果一个定时时间执行需要很长的时间，那么有可能会出现这样的情况，即前面一个事件还未执行完成，下一个时间点的事件又开始了。MySQL本身不会防止这种并发，所以需要用户自己编写这种情况下的防并发代码。你可以使用函数GET_LOCK()来确保当前总是只有一个事件在被执行：

   CREATE EVENT optimize_somedb ON SCHEDULE EVERY 1 WEEK
   DO 
   BEGIN 
   	DECLARE CONTINUE HANLDER FOR SQLEXCEPTION
   	BEGIN END;
   	IF GET_LOCK('SOMEDB',0) THEN	
   		DO CALL optimize_tables('somedb')
   	END IF;
   	DO RELEASE_LOCK('somedb');
   

5. 虽然事件的执行是和连接无关的，但是它仍然是线程级别的。MySQL中有一个事件调度线程，必须在MySQL配置文件中设置，或者使用下面的命令来设置：

   SET GLOBAL event_scheduler := 1;
   

6. 该选项一旦设置，该线程就会执行各个用户指定的时间中的各段SQL代码。你可以通过观察MySQL的错误日志来了解事件的执行情况。

（四）、 7.5 游标

1. MySQL在服务器提供制只读的、单向的游标，而且只能在存储过程或者更底层的客户端API中使用。

（五）、 7.9 字符集和校对

7.9.1 MySQL如何使用字符集

1. 每种字符集都可能有多种校对规则，并且都有一个默认的校对规则。每个校对规则都是针对某个特定的字符集的，和其他的字符集没有关系。校对规则和字符集总是一起使用的，所以后面我们将这样的组合也统称为一个字符集。
2. MySQL的设置可以分为两类：创建对象时的默认值、在服务器和客户端通信时的设置。

创建对象时的默认设置

1. MySQL服务器有默认的字符集和校对规则，每个数据库也有自己的默认值，每个表也有自己的默认值，这是一个逐层继承的默认设置，最终最靠底层的默认设置将影响你创建的对象。

服务器和客户端通信时的设置

1. 当服务器和客户端通信的时候，它们可能使用不同的字符集。这时，服务器端将进行必要的翻译转换工作：
   • 服务器总是假设客户端是按好character_set_client设置的字符来传输数据和SQL语句的
   • 当服务器收到客户端的SQL语句时，它先将其转换成字符集character_set_connection。它还使用这个设置来决定如何将数据转换成字符串。
   • 当服务器端返回数据或者错误信息给客户端时，它会将其转换成character_set_result。
     

MySQL如何比较两个字符串的大小

1. 如果比较的两个字符串的字符集不同，MySQL会先将其转成同一个字符集再进行比较。如果两个字符集不兼容的话，则会抛出错误。

7.9.2 选择字符集和校对规则

（六）、7.9 全文索引

1. 如果你希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。
2. 全文索引可以支持各种字符内容的搜索（包括CHAR、VARCHAR和TEXT类型），也支持自然语言搜索和布尔搜索。

7.10.1自然语言的全文索引

1. 自然语言搜索引擎将计算每一个文档对象和查询的相关度。相关度是基于匹配的关键词个数，以及关键词在文档中出现的次数。

2. 在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会搜索，即使不在停用词列表中出现。如果一个词语在超过50%的记录中都出现了，那么自然语言将不会搜索这类词语。

3. 全文索引的语法和普通查询略有不同。可以根据WHERE子句中的MATCH AGAINST来区分查询是否使用全文索引。

4. 下面看一个实例，可以看到数据表film_text在字段title和description上建立了全文索引：

   SHOW INDEX FROM sakila.film_text;
   

   

5. 下面是一个使用自然语言搜索的一个查询：

   SELECT film_id, title, RIGHT(description, 25),
   	MATCH(title,description) AGAINST('factory casualties') AS relevance
   FROM sakila.film_text
   WHERE MATCH(title,description) AGAINST('factory casualties')
   

   

6. 可以看到，只有一条记录同时包含全部的两个关键词，有三个查询结果只包含关键字“casualties”。第四列展示的是相似度，根据相似度进行排序。

7. 在MATCH(）函数中指定的列必须和全文索引中指定的列完全相同，否则就无法使用全文索引。这是因为全文索引不会记录关键字是来自哪一列的。

7.10.2 布尔全文索引

1. 在布尔搜索中，用户可以在查询中自定义某个被搜索的词语的相关新。布尔搜索通过停用词列表过滤掉那些“噪声”词，除此以外，布尔搜索还要求搜索关键词长度必须大于ft_min_word_len，同时小于ft_max_word_len，搜索的返回结果是未经排序的。
2. 当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。
   
3. 现在我们需要搜索既包含词“factory”又包含“casualties”的记录。使用布尔搜索查询，我们可以指定返回结果必须包含这两个单词。

SELECT film_id, title, RIGHT(description,25)
	FROM sakila.film_text
	WHERE MATCH(title,description)
	AGAINST('+factory +casualties' IN BOOLEAN MODE);

（七）、7.12 查询缓存

1. MySQL拥有一种不同的缓存类型：缓存完整的SELECT查询结果，也就是“查询缓存”。
2. MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存，MySQL会立即返回结果，跳过解析、优化和执行阶段。
3. 查询缓存系统会跟踪查询中涉及的每个表，如果这些表发生变化，那么和这个表相关的所有缓存数据都将失效。但是这种机制其实实现代价很小
4. 查询缓存对应用程序是完全透明的。应用程序无须关系MySQL是通过查询缓存返回的结果还是实际执行返回的记过。事实上，这两种执行的结果是完全相同的。

7.12.1 MySQL如何判断缓存命中

1. MySQL判断缓存命中的方法很简单：缓存存放在一个引用表中，通过一个哈希值引用，这个哈希值包含了如下因素，即查询本身、当前要查询的数据库、客户端协议的版本等。
2. 任何字符上的不同、例如空格、注释——任何的不同——都会导致缓存的不命中。
3. 当查询语句中有一些不确定的数据时，则不会缓存。例如包含函数NOW()或者CURRENT_DATE()的查询不会被缓存。事实上，如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表、或者任何包含列级别权限的表，都不会被缓存。
4. 在事务提交之前，这个表的相关查询也是无法被缓存的。

7.12.2 查询缓存如何使用内存

1. 查询缓存是完全存储在内存中的。
2. MySQL用于查询缓存的内存被分成一个个的数据块，数据块是变长的。每一个数据块中，存储了自己的类型、大小和存储的数据本身，还外加指向前一个和后一个数据块的指针。
3. 当有查询结果需要缓存的时候，MySQL先从大的空间块中申请一个数据块用于存储结果，这个数据块需要大于参数query_cache_min_res_unit的配置，即使查询结果远远小于此。
   

7.12.3 什么情况下查询缓存能发挥作用

1. 理论上，可以通过观察打开或者关闭缓存时候的系统效率来决定是否需要开启查询缓存
2. 对于那些需要消耗大量资源的查询通常都是非常适合缓存的。例如一些汇总查询。这些都是执行消耗大，但是返回的结果集却很小，非常适合缓存。
3. 这里推荐查看“命中和写入”的比率来查看缓存是否对系统有好处。根据经验来看，当这个壁纸大于3：1时通常查询缓存是有效的，不过这个比率最好能够达到10：1。如果你的应用没有达到这个比率，建议禁用查询缓存。
4. 如果查询缓存空间长时间都有剩余，那么建议缩小；如果经常由于空间不足而导致查询缓存失效，那么则需要增大查询缓存。

7.12.4 如何配置和维护查询缓存

1. 下面是一些参数配置：
   1. query_cache_type:
      　 是否打开查询缓存。可设置成OFF、ON或DEMAND。DEMAND表示只有在查询语句中明确写明SQL_CACHE的语句才放入查询缓存。这个变量可以是会话级别的也可以使全局级别的。
   2. query_cache_size
      　查询缓存使用的内存空间，单位是字节。这个值必须是１０２４的整数倍
   3. query_cache_min_res_unit
      　在查询缓存中分配内存块时的最小单位。
   4. query_cache_limit
      　ＭySQL能够缓存的最大查询结果。如果查询结果大于这个值，则不会被缓存。如果超出，MySQL则增加状态值Qcache_not_cached，并将结果结果从查询缓存中删除。
   5. query_cache_wlock_invalidate
      　如果某个数据表被其他的连接锁住，是否仍然从查询缓存中换回结果，这个参数默认是ＯＦＦ。
2. 选择合适的query_cache_min_res_unit可以帮你减少由碎片导致的内存空间浪费。设置合适的值可以平衡每个数据块的大小和每次存储结果时内存块申请的次数。
3. 可以通过参数Ucache_free_blocks来观察碎片。这个参数反映了查询缓存中空闲块的多少
4. 可以使用命令FLUSH QUERY CACHE完成碎片整理。
5. 可以通过将query_cache_size设置为0来关闭查询缓存。