MySQL进阶详解教程，看过的都说好

1.索引

1.1概述

​ 索引是对数据库表中一个或多个列的值进行排序的结构。所有MySQL列类型都可以被索引，对相关列使用索引是提高SELECT操作性能的最佳途径。

1.2分类

• **B-Tree索引：**最常见的索引类型，大部分引擎都支持B树索引。

• **HASH索引：**只有Memory引擎支持，使用场景简单。

• **R-Tree索引（空间索引）：**空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型。

• **Full-text（全文索引）：**全文索引也是 MyISAM 的一个特殊索引类型，主要用于全文索引。

  

1.3设计索引的原则

• 搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在WHERE子句中的列，或连接子句中指定的列，而不是出现在SELECT关键字后的选择列表中的列。
• 使用唯一索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。
• 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。较小的索引涉及的磁盘 IO 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL 也可以在内存中容纳更多的值。这样就增加了找到行而不用读取索引中较多块的可能性。
• 利用最左前缀。在创建一个n列的索引时，实际是创建了MySQL可利用的n个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
• 不要过度索引。个额外的索引都要占用额外的磁盘空间，并降低写操作的性能。

1.4 B-TREE索引

​ B-Tree索引是最常见的索引，构造类似二叉树，能根据键值提供一行或者一个行集的快速访问，通常只需要很少的读操作就可以找到正确的行。不过，需要注意B-Tree索引中的B 不代表二叉树（binary），而是代表平衡树（balanced）。B-Tree索引并不是一棵二叉树。

1.5 MySQL中能够使用索引的典型场景

• 匹配全值（Match the full value），对索引中所有列都指定具体值，即是对索引中的所有列都有等值匹配的条件。
• 匹配值的范围查询（Match a range of values），对索引的值能够进行范围查找。
• 匹配最左前缀（Match a leftmost prefix），仅仅使用索引中的最左边列进行查找，
• 仅仅对索引进行查询（Index only query），当查询的列都在索引的字段中时，查询的效率更高；
• 匹配列前缀（Match a column prefix），仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找。
• 能够实现索引匹配部分精确而其他部分进行范围匹配（Match one part exactly and match a range on another part）。
• 如果列名是索引，那么使用 column_name is null就会使用索引（区别于Oracle）。
• Index Condition Pushdown（ICP），Pushdown表示操作下放，某些情况下的条件过滤操作下放到存储引擎。

1.6存在索引但不能使用索引的典型场景

• 以%开头的LIKE查询不能够利用B-Tree索引，执行计划中key的值为NULL表示没有使用索引.
• 数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串。
• 复合索引的情况下，假如查询条件不包含索引列最左边部分，即不满足最左原则Leftmost，是不会使用复合索引的。
• 如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。
• 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

1.7索引使用情况与优化

1.7.1查看索引使用情况

​ 如果索引正在工作，Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。

​ Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，Handler_read_rnd_next的值较高，则通常说明表索引不正确或写入的查询没有利用索引。

​ 语法：show status like ‘Handler_read%’;

1.7.2定期分析表和检查表

​ 分析表的语法如下：

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] . .

​ 本语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得SQL能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。在分析期间，使用一个读取锁定对表进行锁定。这对于MyISAM、BDB和 InnoDB表有作用。对于MyISAM表，本语句与使用myisamchk -a相当.

​ 检查表的语法如下：

CHECK TABLE tbl_name [, tbl_name] . . [option] . . option = {QUICK | FAST |MEDIUM | EXTENDED | CHANGED}

​ 检查表的作用是检查一个或多个表是否有错误。CHECK TABLE对MyISAM和InnoDB表有作用。对于MyISAM表，关键字统计数据被更新。CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在，

​ 优化表的语法如下：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] . .

​ 如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（含有VARCHAR、BLOB或TEXT列的表）进行了很多更改，则应使用OPTIMIZE TABLE命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但OPTIMIZE TABLE命令只对MyISAM、BDB和InnoDB表起作用。

​ 对于InnoDB引擎的表来说，通过设置innodb_file_per_table参数，设置InnoDB为独立表空间模式，这样每个数据库的每个表都会生成一个独立的ibd文件，用于存储表的数据和索引，这样可以一定程度上减轻InnoDB表的空间回收问题。另外，在删除大量数据后，InnoDB表可以通过 alter table但是不修改引擎的方式来回收不用的空间.

2.视图

2.1概念

​ 视图（View）是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

​ 视图的特点：

• 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
• 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单地实现。
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

2.2视图操作

​ 视图的操作包括创建或者修改视图、删除视图，以及查看视图定义。

2.2.1创建或者修改视图

​ 创建视图需要有CREATE VIEW的权限，并且对于查询涉及的列有SELECT权限。如果使用CREATE OR REPLACE或者ALTER修改视图，那么还需要该视图的DROP权限。

​ 创建视图的语法为：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

​ 修改视图的语法为：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

​ 视图的可更新性和视图中查询的定义有关系，以下类型的视图是不可更新的。

​ 包含以下关键字的SQL语句：

• 聚合函数（SUM、MIN、MAX、COUNT等）、DISTINCT、GROUP BY、HAVING、UNION或者UNION AL