《MYSQL必知必会》略读有获

前言

《MYSQL必知必会》大概的走了一遍(相对都是比较简单的掌握，在工作中很多已经很熟悉)，还是有些忽略或者不清楚的东西，写此博客以记事。

记录

数据库引擎（除了以下还有其他引擎）

数据库引擎	描述(特性)	比较
ISAM	ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。
MyISAM	MyISAM是MySQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMCHK工具和用来恢复浪费空间的 MyISAMPACK工具。MYISAM强调了快速读取操作，这可能就是为什么MySQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据。	支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。
InnoDB	InnoDB数据库引擎都是造就MySQL灵活性的技术的直接产品，这项技术就是MYSQL+API。在使用MYSQL的时候，你所面对的每一个挑战几乎都源于ISAM和MyISAM数据库引擎不支持事务处理（transaction process）也不支持外来键。尽管要比ISAM和 MyISAM引擎慢很多，但是InnoDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者或者两者，那你就要被迫使用后两个引擎中的一个了。	插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用
MEMORY	MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。MEMORY默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。注意，MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。	所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

数据库可以设置默认引擎，也可以在创建表时指定引擎如下sql:

-- 这里指定的引擎为MyISAM
CREATE TABLE `productnotes` (
  `note_id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_id` char(10) NOT NULL,
  `note_date` datetime NOT NULL,
  `note_text` text,
  PRIMARY KEY (`note_id`),
  FULLTEXT KEY `note_text` (`note_text`)
) ENGINE=MyISAM AUTO_INCREMENT=115 DEFAULT CHARSET=utf8;

-- 这里指定的是InnoDB
CREATE TABLE `orders` (
  `order_num` int(11) NOT NULL AUTO_INCREMENT,
  `order_date` datetime NOT NULL,
  `cust_id` int(11) NOT NULL,
  PRIMARY KEY (`order_num`),
  KEY `fk_orders_customers` (`cust_id`),
  CONSTRAINT `fk_orders_customers` FOREIGN KEY (`cust_id`) REFERENCES `customers` (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=20010 DEFAULT CHARSET=utf8;

在开发中我们主要使用的引擎是InnoDB，以前没啥概念，看了比较之后似乎大概就知道了为什么。主要是事务的支持，和并发的处理。如果需求不需要对事务的支持或者仅仅是想高效读，那么可以选择其他引擎。也是体现了需求和选择的问题。

事务控制

常在才Spring全家桶中开发，使用事务(@Transactional注解或者配置使用)控制数据的准确性。从某些感觉上来说，可能大家都误以为是Spring控制了事务，其实应该说Spring使用了数据库引擎的事务进行控制调用。

sql中事务控制：

-- 开始事务
START TRANSACTION；
-- 执行体sql

-- 提交事务
COMMIT;

-----------------------------------------------
-- 开始事务
START TRANSACTION；
-- 执行体sql

-- 回滚至事务前
ROLLBACK;

-----------------------------------------------
-- 开始事务
START TRANSACTION；

-- 执行体sql1

-- 设置保留点
SAVEPOINT change;

-- 执行体sql2

-- 回滚至change保留点处(sql1执行操作成功，sql2执行回滚)
ROLLBACK TO change;

当COMMIT或ROLLBACK语句执行后，事务会自动管理

全文搜索

到看了这本书才知道原来除了正则匹配，like匹配还有个全文搜索。真的是程序员…才疏学浅,以后可以换一种操作了。以下是我搜索的博客测试的总结like与全文搜索的效率比较:

LIKE搜索的耗时随着记录数的增加而线性增长，但对于10万行记录以下的表（这里共100000*50个单词）搜索时间基本上能保持在1秒以内，所以like搜索的性能也不是特别差。由不同词汇量生成的文本对LIKE搜索的性能影响不大，不同词汇量对应的搜索时间基本上在一个很小的时间范围内变化。

FULLTEXT搜索耗时也随表中记录数的增长而线性增加。对于10万行记录以下的表（这里共100000*50个单词）搜索时间基本上能保持在0.01秒以内。由不同词汇量生成的随机文本对FULLTEXT搜索性能有相对来说比较显著的影响。每行记录中含同样的单词数，这样，较大的词汇量倾向于生成冗余度更低的文本，相应的搜索耗时倾向于更少。这可能与FULLTEXT索引建立单词索引的机制有关，较大的词汇量倾向于生成范围广但相对较浅的索引，因而能快速确定文本是否匹配。

与LIKE搜索相比，FULLTEXT全文搜索的性能要强很多，对于10万行记录的表，搜索时间都在0.02秒以下。因此可以将基于FULLTEXT索引的文本搜索部署于网站项目中的文本搜索功能中。但是，正如上述提到的，无论是LIKE搜索还是FULLTEXT搜索，其性能都会随着记录数的增长而下降，因此，若网站项目中的文本搜索数据库记录数庞大的一定规模后，可能需要考虑使用MySQL数据库全文搜索以外的文本搜索解决方案了。

基本语法:

-- 必须设置note_text为全文检索
CREATE TABLE `productnotes` (
  `note_id` int(11) NOT NULL AUTO_INCREMENT,
  `prod_id` char(10) NOT NULL,
  `note_date` datetime NOT NULL,
  `note_text` text,
  PRIMARY KEY (`note_id`),
  FULLTEXT KEY `note_text` (`note_text`)
) ENGINE=MyISAM AUTO_INCREMENT=115 DEFAULT CHARSET=utf8;

-- MATCH(字段名) AGAINST(搜索值表达式)
SELECT * FROM productnotes where MATCH(note_text) AGAINST('order')

具体详细用法参考MySQL中文全文检索:

LIKE的另一种搜索语法

-- _作为占位符,匹配 xa%的字符
SELECT * FROM productnotes WHERE note_text LIKE '_a%'

批量插入

平常不怎么去这么写批量插入，在mybatis一次性插入get到咯，没必要分开去save平添连接性能消耗

INSERT INTO `mysqlcrashcourse`.`productnotes` (`note_id`, `prod_id`, `note_date`, `note_text`) VALUES
('102', 'OL1', '2005-08-18 00:00:00', 'Can shipped full, refills not available.\r\nNeed to order new can if refill needed.')，
('102', 'OL1', '2005-08-18 00:00:00', 'Can shipped full, refills not available.\r\nNeed to order new can if refill needed.');

存储过程有点摘抄(主要面试经常也会问到)

优点

1. 通过把处理封装在容易使用的单元中，简化复杂操作
2. 由于不要求反复建立一系列处理步骤，这保证了数据的完整性，如果开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
3. 简化对变动的管理。如果表名、列名或业务逻辑的变化，只需要更改存储过程的代码。使用它的人员圣职都不需要知道这些变化。
4. 提高性能。因为使用存储过程比使用单独的sql语句要快。
5. 存在一些职能用在单个请求中的MYSQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

缺点

1. 一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。
2. 存在控制存储过程的安全访问，如许多管理员限制存储过程的创建权限，只允许用户使用存储过程，但是不允许他们创建存储过程
3. 由于过于依赖SQL所以数据库中的迁移不太好(数据库版本、数据库种类都可能有影响)(自己总结的这条)

书文末学性能优化总结(几条个人认为比较实用的)

1. 绝不要检索比需求还要多的数据。换言之，不要用SELECT * 搜索出不需要的字段。(IO的消耗可能比sql查询本身更加消耗性能)
2. 导入数据时，应该关闭自动提交。你可能还想删除索引，然后在导入完成后再重建他们。
3. 包含一些列复杂的OR条件可以尝试使用UNION进行连接，可以看到性能改进。
4. 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果你有些表，他们手机数据且不经常被搜索，则在有必要之前不要索引他们。
5. LIKE很慢、一般来说，最好使用FULLTEXT而不是LIKE
6. 使用EXPLAIN语句让MYSQL节食他如何执行一条select语句

随便说说

程序员使用数据库时候很多初级只是知道简单的增删改查，很少去注意性能和思考很多为什么。这些都是需要改善优化的，多思考，看看书多少还是有些收获的。