mysql 基本操作 选择合适的引擎 选择索引、视图语法（第三章）

如何选择合适的存储引擎：

MyISAM：默认的 MySQL 插件式存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。MyISAM 是在 Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

InnoDB：用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。

外键约束：

在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包 RESTRICT、

CASCADE、SET NULL 和 NO ACTION。其中 RESTRICT 和 NO ACTION 相同，是指限制在子表有关联记录的情况下父表不能更新；CASCADE 表示父表在更新或者删除时，更新或者删除子表对应记录；SETNULL 则表示父表在更新或者删除的时候，子表的对应字段被 SET NULL。选择后两种方式的时候要谨慎，可能会因为错误的操作导致数据的丢失。

 

不同的存储引擎对 CHAR和 VARCHAR 的使用原则有所不同，这里简单概

括如下：

1.    MyISAM 存储引擎：建议使用固定长度的数据列代替可变长度的数据列

2.    InnoDB 存储引擎：建议使用 VARCHAR 类型。对于 InnoDB 数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR 列不一定比使用可变长度 VARCHAR 列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于CHAR 平均占用的空间多于 VARCHAR，因此使用 VARCHAR 来最小化需要处理的数据行的存储总量和磁盘 I/O 是比较好的

TEXT 与 BLOB

一般在保存少量字符串的时候，我们会选择 CHAR 或者 VARCHAR；而在保存较大文本时，通常会选择使用 TEXT 或者 BLOB，二者之间的主要差别是 BLOB 能用来保存二进制数据，比如照片；而 TEXT 只能保存字符数据

BLOB 和 TEXT 值会引起一些性能问题，特别是在执行了大量的删除操作时。

删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用OPTIMIZE TABLE 功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。

索引的设计和使用：

目的是：提高查询的操作

类型：MyISAM和 InnoDB 存储引擎的表默认创建的都是 BTREE 索引

暂不支持函数索引，支持前缀索引，即对索引字段的前N个字符创建索引，前缀索引长度跟存储引擎相关，myisam:1000个字符，innodb，最长767个字符，

全文本索引：FULLTEXT,可用于全文搜索，只有MYISAM支持，并且仅限于char和varchar

创建索引：CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name 
    [USING index_type] 
    ON tbl_name (index_col_name,...) 
index_col_name: 
    col_name [(length)] [ASC | DESC] 

例如：要为 city 表创建了 10 个字节的前缀索引，语法是：

create index cityname on city (city(10));

删除索引：

DROP INDEX index_name ON tbl_name

设计索引的原则：

1.    搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列

2.    使用惟一索引。考虑某列中值的分布。索引的列的基数越大，索引的效果越好。

3.    使用短索引。如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做

4.    不要过度索引。占用额外的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。

BTREE和HASH索引的区别

Hash：= 或<=> ,order by 都不能使用索引，只能使用整个关键字来搜索一行

Btree索引：<、> 、 >=  <= between !=  like 都可以使用相关索引

视图

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

优势：

1.简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

 2. 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

3. 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响

视图操作：

创建、修改视图

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]

例子：

CREATE OR REPLACE VIEW staff_list_view AS  
    -> SELECT s.staff_id,s.first_name,s.last_name,a.address  FROM staff AS s,address AS a

视图定义的限制：在from后不能有子查询，如果有，那么现将子查询内容定义成一个视图，然后再对该视图再创建视图就可实现类似功能。

以下视图是不可更新的:

1.   包含以下关键字的 SQL 语句：聚合函数（SUM、MIN、MAX、COUNT 等）、DISTINCT、GROUP BY、HAVING、UNION 或者 UNION ALL

2.   常量视图

3.   SELECT 中包含子查询。

4.   JION。

5.   FROM 一个不能更新的视图

6.   WHERE 字句的子查询引用了 FROM 字句中的表

WITH[CASCADED | LOCAL] CHECK OPTION决定了是否允许更新数据使记录不再满足视图的条件

1.LOCAL 是只要满足本视图的条件就可以更新；

2.CASCADED 则是必须满足所有针对该视图的所有视图的条件才可以更新。

如果没有明确是 LOCAL还是 CASCADED，则默认是 CASCADED。

删除视图：

DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]

查看视图定义：

show create view staff_list \G