MySQL基础知识

简介

这是一篇关于《MySQL技术内幕（第4版）》的阅读笔记，主要是为了从无到有系统的学习下数据库的知识。

杜波依斯编著的《MySQL技术内幕（第4版）》介绍了MySQL的基础知识及其有别于其他数据库系统的独特功能，包括SQL的工作原理和MySQL API的相关知识；讲述了如何将MySQL与Perl或PHP等语言结合起来，为数据库查询结果生成动态Web页面，如何编写MySQL数据访问程序；详细讨论了数据库管理和维护、数据目录的组织和内容、访问控制、安全连接等。附录还提供了软件的安装信息，罗列了MySQL数据类型、函数、变量、语法、程序、API等重要细节。

数据库的操作

进入数据库

mysql -u root -p

退出

quit

;或\g(go)作为结束符表示终止指令，\G表示为竖排显示数据

创建名为 acgdb 的数据库

create database acgdb;

可以使用character set 和 collate 子句对数据库属性作出明确的设置

create database acgdb character.set utf8 collate utf8_icelandic_ci;

改变数据库的全局特性

ALTER DATABASE [db_name] [CHARACTER SET charset] [COLLATE collation];

使用语句查看现有数据库的定义

show create database acgdb;

删除数据库

drop database acgdb;

此时并没有数据库再使用，可以用下面指令查看

select database();

使用 acgdb

use acgdb;

也可以在进入数据库的时候携带数据库名称

mysql -p -u root acgdb;

数据引擎

MyISAM 和 InnoDB的适用场景
MyISAM适合：

做很多 count 的计算；
插入不频繁，查询非常频繁；
没有事务。

InnoDB适合：

可靠性要求比较高，或者要求事务；
表更新和查询都相当的频繁，并且表锁定的机会比较大的情况。

MyISAM 和 InnoDB的区别

MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持
mysiam表不支持外键
在执行数据库写入的操作（insert,update,delete）的时候，mysiam表会锁表，而innodb表会锁行
当你的数据库有大量的写入、更新操作而查询比较少或者数据完整性要求比较高的时候就选择innodb表。当你的数据库主要以查询为主，相比较而言更新和写入比较少，并且业务方面数据完整性要求不那么严格，就选择mysiam表。因为mysiam表的查询操作效率和速度都比innodb要快

查看元数据

查看服务器的数据库

show databases;

查看给定数据库的 CREATE DATABASE 语句：

show CREATE DATABASE db_name;

列出默认数据库或给定数据库的数据表：

show tables [from db_name];

查看数据表的CREATE TABLE 语句：

show CREATE TABLE tb_name;

查看数据表里的数据列或索引信息：

show COLUMNS FROM tb_name;
show INDEX from tb_name;

查看默认数据库或某给定数据库里的数据表的描述性信息：

show table status [from db_name];

show 支持模糊查询和 where 查询并显示

show columns from student LIKE ‘s%’;
查询学生表中s字符开头的数据列

另外一种获取元数据的方法是访问 INFORMATION_SCHEMA 数据库。

show tables in INFORMATION_SCHEMA;

+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| ...                                   |
+---------------------------------------+

INFORMATION_SCHEMA 数据库记录着 mysql 服务商所有数据库的相关信息。

在命令行中也可以使用 mysqlshow 命令符查看数据库的信息，用法基本与 show 一致。

mysqlshow db_name tb_name;
mysqlshow —keys db_name tb_name;//查看索引信息

联结操作对数据库检索

内联结

基础操作

select t1.,t2. from t1 INNER JOIN t2;

作用是将两张表的数据列拼在一起并显示

INNER JOIN,CROSS JOIN, JOIN作用类似，同时它们还支持另外几种用来表明如何对数据表中的数据列进行匹配的语法变体

用一条 ON 子句代替 WHERE 子句

select t1.,t2. from t1 INNER JOIN t2 ON t1.i1 = t2.i2;
USING 子句，概念上类似于ON子句，但要求被联结的数据列必须是同名的

select mytb1., mytb2. from mytb1 INNER JOIN mytb2 USING (b);

为了避免歧义，在联结操作时数据列最好携带数据表的名字，若是同一张数据表则使用 AS 进行别名

select mytb1.col1, m.col2 from mytb1 AS m where mytb1.col1 > m.col1;

外联结（左联结和右联结）

LEFT JOIN 和 RIGHT JOIN
LEFT JOIN 的工作情况：用来联结的两张数据表，当来自左数据表的某个数据航与来自由数据表的某个数据行匹配时，那两个数据行内容就会被选取为一个输出数据行，与内联结一样；如果来自左数据表的某个数据行在右数据表找不到匹配，它也会被选取为一个输出行，此时与它联结的是一个来自右数据表的“假”数据行，这个“假”数据行的所有数据列都包含NULL值。RIGHT JOIN 同理。
简单来说，左联结时，左数据表的的数据全部输出，右数据表只输出匹配的数据，其他数据为 NULL 值；右联结时，右数据表的的数据全部输出，左数据表只输出匹配的数据，其他数据为 NULL 值。

select t1., t2. from t1 LEFT JOIN t1 ON t1.i1 = t2.i2;
select t1., t1. from t2 RIGHT JOIN t1 ON t1.i1 = t2.i1;
这两句输出相同

子查询进行多数据表检索

MySQL支持子查询，也就是把一条SELECT语句用括号括起来嵌入另一个SELECT语句。

select * from score where event_id IN (select event_id from grade_event where category = ‘T’);
从 grade_event 数据表里找出对应于考试（‘T’）的event_id,再用它们选取考试的成绩。

子查询可以返回不同类型的信息

标量子查询将返回一个值，而比较操作符=、<>、>、>=、<和<=只能和标量子查询配合使用，若返回多个值将出错
可以用IN和NOT_IN操作符来测试某给定值是否包含在子查询的结果集里。主查询里的数据行与子查询所返回的任何一个数据匹配数据，IN 操作符的比较结果为true；反之，都不匹配的话NOT IN操作符的比较结果是true
可以用ALL、ANY、和 SOME 操作符把某给定值与子查询的结果集进行比较。其中 SOME 和 ANY 是同义词。 IN 等价于 = ANY ; NOT IN 等价于 <>ALL
EXISTS 和 NOT EXISTS 用于测试某个子查询是否返回了数据行。

有相当一部分的子查询可以改写为联结查询。

select from table1 where column1 in (select column2a from table2 where column2b = value);
这种选取匹配值的子查询可以改写
select table1. from table1 INNER JOIN table2 ON table1.column1 = table2.column2a where column2b = value;

UNION 语句进行多数据表检索

select i from t1 UNION select i from t2 UNION select i from t3;

UNION 结果集里的数据列名字来自第一个 SELECT 语句里的数据列的名字。UNION中的第二个和再后面的 select 语句必须选取个数相同的数据列，但各有关数据列不必有同样的名字或数据类型。数据列是一一对应的，根据位置而不是根据名字进行匹配的。
在默认情况下，UNION 将从结果集里剔除重复的数据行，UNION DISTINCT = UNION;若不想剔除，则使用 UNION ALL。

若想对UNION语句输出的数据进行处理，如排序，限制个数等，需要在每个查询语句中加上括号。

(select i, c from t1) UNION (select i,d from t3) order by c;

使用视图

视图是一个虚拟的数据表，它们的行为与数据表一样但不包含任何数据。从实现效果来看，更像是数据库语句的快捷方式。
比如下面这个语句：

select last_name, first_name, city, state from president;

每次查询都要写出一大串数据列，这时候就可以使用视图来代替，可以实现同样的效果。

CREATE VIEW vpres AS SELECT last_name, first_name, city, state FROM president;
SELECT * FROM vpres;

在默认的情况下，视图里的数据列的名字与 select 语句离列出的输出数据列相同。如果你想明确地改用另外的数据列名字，需要在定义视图时再视图名字后面用括号列出新名字。

CREATE VIEW vpres2 (ln, fn) AS SELECT last_name, first_name FROM president;
SELECT ln, fn FROM vpres2;

视图内部可以加表达式，联结操作等，亦可以执行更新操作。

涉及多个数据表的删除和更新操作

select 语句同样在 delete 语句中可以使用。

DELETE t1, t2 FROM t1 INNER JOIN t2 ON t1.id = t2.id;
此时表示两张表同时删除被检索到的数据。

可以使用 USING 子句来联结各有关数据表以确定哪些数据行需要被删除。

DELETE FROM t1, t2 USING t1 INNER JOIN t2 ON t1.id = t2.id;

事务处理

事务是作为一个不可分割的逻辑单元而被执行的一组SQL语句，如有必要，它们的执行效果可以被撤销。
事务以 START TRANSACTION 作为开始语句，以 COMMIT 提交结束。

START TRANSACTION;
INSERT INTO t SET name = ‘cpacm’;
INSERT INTO t SET name = ‘Wallace’;
COMMIT;

当事务过程中发生了一个错误时，可以用 ROLLBACK 语句把它删除。

ERROR 1062 (23000):Duplicate entry ‘Wallcae’ for key 1
ROLLBACK;

也可以设置自动提交模式，当自动提交模式设为0时，其后所有的任何语句都将成为当前事务的一部分直到你发出一条 COMMIT 或 ROLLBACK。

SET autocommit = 0;
SET autocommit = 1;

一些语句会隐形的提交事务，如：

ALTER TABLE
CREATE INDEX
DROP ...
LOCK TABLES
RENAME TABLE
SET autocommit = 1
TRUNCATE TABLE
UNLOCK TABLES

可以使用事务保存点对一个事务进行部分回滚。

START TRANSACTION;
…
SAVEPOINT my_save;
…
ROLLBACK TO SAVEPOINT my_save;

外键

利用外键关系可以在某个数据表里声明与另一个数据表里的某个索引相关联的索引。

父表

CREATE TABLE parent (par_id INT NOT NULL, PRIMARY KEY (par_id))

子表

CREATE TABLE child (
par_id INT NOT NULL,
child_id INT NOT NULL,
PRIMARY KEY (par_id, child_id),
FOREIGN KEY (par_id) REFERENCES parent (par_id)
ON DELETE CASCADE
ON UPDATE CASCADE
)

这个例子在定义外键时使用 ON DELETE CASCADE 子句，它指定当parent数据表里的某个数据行被删除时，MySQL将自动从child数据表里把有匹配par_id值的数据行也删掉。ON UPDATE CASCADE子句表明：如果parent数据表里的某个数据行的par_id值被改变了，MySQL将自动地把child数据表里的所有匹配的par_id值也改成新值。即级联操作。

FULLTEXT索引

要想对某个数据表进行全文搜索，必须事先为它创建一个 FULLTEXT 索引。

这种索引只能在MyISAM数据表里建造。FULLTEXT 索引只能由 CHAR、VARVHAR和TEXT这几种组成。
全文搜索将忽略常见的单词，常见表示“至少在一半的数据行中出现”。
忽略常见的休止单词，如the、after和other等
少于设置的长度也会被忽略。

ALTER TABLE apothegm
ADD FULLTEXT (phrase),
ADD FULLTEXT (attrbution),
ADD FULLTEXT (phrase, attribution);

自然搜索

把搜索字符串解释为一系列单词并查找包含这些单词的数据行。

SELECT * FROM apothegm WHERE MATCH(phrase) AGAINST(‘hardsoft’ IN NATURAL LANGUAGE MODE);

match 里面是将要被搜索的数据列，against里面是要搜索的关键字，数据库默认模式为自然搜索，所以 IN NATURAL LANGUAGE MODE 可以省略不写。

布尔模式 (IN BOOLEAN MODE)

把搜索字符串解释为一系列单词，但允许使用一些操作符来‘修饰’这些单词一表明特定的要求，如某给定单词必须出现（或不出现）在匹配数据行里，某个数据行必须包含一个精确的短语。

查询扩展模式（WITH QUERY EXPANSION）

进行两边搜索，第一遍自然语言搜索，然后将第一遍的结果加上原来的搜索单词进行第二次搜索。

全文搜索配置

修改最少单词长度设置： ft_min_word_len 设置为3
修复现有FULLTEXT索引的现有数据表： REPAIR TABLE tb1_name QUICK;

查询在概念上是集合操作，在实际设备上操作时查询是要占用时间的，有时候长得让人无法忍受，所以我们要寻找能够加快查询的方法。

使用索引

通常能造成查询速度最大差异的是索引的正确使用。所以优先使用索引来最大程度地改进性能，然后再看是否还有其他技术可以采用。

索引的优点

一个没有索引的数据表就是一个无序的数据行集合，如果想要找某一行数据就必须要检查数据表的每一个数据行。而添加索引之后，就不用再一行一行地搜索整个数据表来寻找匹配项，可以直接通过扫描索引来查找。而且索引值是经过分类的，比如说当我们读取到14的值时就知道不会有与13相匹配的内容。由此可知，索引可以提高搜索效率的一个原因就是我们知道匹配数据行在什么位置结束，从而跳过其余部分，另外一个原因则是定位算法的使用，它可以不用从索引开始位置经过线性扫描就能直接找到第一个匹配项。

MySQL 使用索引的几种方式：

一是在查询操作中把与 WHERE 子句所给出的条件相匹配的数据行尽快找出来；二是在关联操作中把与其他数据表里的数据行相匹配的数据行尽快找出来。
对于使用MIN()和MAX()函数的查询，如果数据列带索引，那么它的最小值和最大值能够被迅速找到而不是通过逐行检查的方法来查找。
MySQL经常使用索引来迅速地完成 ORDER BY 子句和 GROUP BY 子句的分类和分组操作。
可以使用索引来避免为一个查询整体读取数据行。

索引的缺点

索引加快了检索速度，但却降低了在带索引的数据列里插入、删除以及修改数值的速度。因为在写入一条数据行时还要求所有所以都要做出改变。其次，索引要占据磁盘空间，索引越多空间占用越大。所以如果不需要某个特定的索引来加快查询速度就不要创建它。

如何挑选索引

尽量为用来搜索、分类或分组的数据列编制索引。
综合考虑各数据列的维度势。即索引里面独一无二的值越多，使用的效果就越好。
对短小的值进行索引。比较“小”的数据类型。
为字符串值的前缀编索引。
充分利用最左边的前缀。
适可而止，不要建立过多的索引。
让索引的类型与你打算进行的比较操作的类型保持匹配。散列索引或者B树。散列索引精确比较操作速度快，但用来查找一个范围的比较操作里表现不佳；B树则在两方面表现都可以。
创建一个B树索引
1
2
3
4
5
6
CREATE TABLE lookup
(
id INT NOT NULL,
name CHAR(20),
PRIMARY KEY USING BTREE (id)
) ENGINE = MEMORY;
利用“慢查询”日志找出性能低劣的查询。

MySQL的查询优化程序

数据库自带查询优化程序，可以使用 EXPLAIN 查看一个查询语句的详细信息。

EXPLAIN SELECT * FROM tb1_name WHERE FALSE\G

挑选数据类型

尽量使用数值操作，少使用字符串操作。
如果“小”类型够用就不要选用“大”类型。
选择最适用于你的存储引擎的格式。
尽量把数据列声明为 NOT NULL。
考虑使用 ENUM 类型
利用 PROCEDURE ANALYSE()来分析数据表

SELECT * FROM tb1_name PROCEDURE ANALYSE();
对容易产生碎片的数据表进行整理。定期使用 OPTIMIZE TABLE 语句有助于防止数据表查询性能的降低。
把数据压缩到 BLOB 或 TEXT 数据列里。
使用人造索引。
尽量避免对很大的 BLOB 或 TEXT 进行检索。
把 BLOB 或 TEXT 数据列剥离到单独一个数据表里。

有效加载数据

批量加载的效率比单数据行加载的效率高；
加载有索引的数据表比加载无索引的数据表快一些；
较短的 SQL 语句的数据加载快。

调度和锁定问题

MySQL 的默认调度策略：

写入比读取有着更高的优先权。
对数据表的写操作必须按照“写”请求先来后到的顺序一个接一个地进行。
对同一个数据表进行的读操作可以同时进行。

系统管理员所完成的优化

在内存中访问数据比从磁盘上访问数据块。
在内存中尽可能长地保存数据可以减少磁盘活动量。
保留索引的信息要比保留数据行的内容更加重要。