MySQL

• 整理自《高性能 MySQL》，整理了一些重点内容。
• MySQL 索引背后的数据结构及算法原理
• 20+ 条 MySQL 性能优化的最佳经验

• 数据库为什么分库分表？mysql的分库分表方案

• mysql

• 21分钟MySQL入门教程
• MySQL索引背后的数据结构及算法原理

1. 安装

• debian安装及使用mysql 在Debian中安装MySQL服务器是很方便的，使用apt-get命令即可完成。

  debian:~# apt-get install mysql-server mysql-client
  

  mysql-server是服务器程序，mysql-client是客户端程序。我们可通过客户端程序来管理服务器，也可通过一些开源 的GUI程序来维护服务器，如phpmyadmin，mysqlcc等。推荐使用phpmyadmin这个B/S的管理程序，通过浏览器就可方便高效地管 理网络上的数据库。

  2. 登录

• 登录MySQL的命令是mysql， mysql 的使用语法如下：

  　　mysql [-u username] [-h host] [-p[password]] [dbname]
  

  username 与 password 分别是 MySQL 的用户名与密码，mysql的初始管理帐号是root，没有密码，注意：这个root用户不是Linux的系统用户。MySQL默认用户是root，由于 初始没有密码，第一次进时只需键入mysql即可。

  [root@test1 local]# mysql
  Welcome to the MySQL monitor.　Commands end with ; or \g.
  Your MySQL connection id is 1 to server version: 4.0.16-standard
  Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
  mysql>
  

  出现了“mysql>”提示符，恭喜你，安装成功！ 增加了密码后的登录格式如下：

  mysql -u root -p 
  Enter password: (输入密码)
  

  其中-u后跟的是用户名，-p要求输入密码，回车后在输入密码处输入密码。 注意：这个mysql文件在/usr/bin目录下，与后面讲的启动文件/etc/init.d/mysql不是一个文件。

  3. MySQL的几个重要目录

  　　MySQL安装完成后不象SQL Server默认安装在一个目录，它的数据库文件、配置文件和命令文件分别在不同的目录，了解这些目录非常重要，尤其对于Linux的初学者，因为 Linux本身的目录结构就比较复杂，如果搞不清楚MySQL的安装目录那就无从谈起深入学习。 　　下面就介绍一下这几个目录:
• 数据库目录 　　/var/lib/mysql/
• 配置文件 　　/usr/share/mysql（mysql.server命令及配置文件）
• 相关命令 　　/usr/bin(mysqladmin mysqldump等命令)
• 启动脚本 　　/etc/rc.d/init.d/（启动脚本文件mysql的目录）

  4. 修改登录密码

  　　MySQL默认没有密码，安装完毕增加密码的重要性是不言而喻的。
• 命令

    usr/bin/mysqladmin -u root password 'new-password'
  

  　　格式：mysqladmin -u用户名 -p旧密码 password 新密码
• 例子 　　例1：给root加个密码123456。 　　键入以下命令 ：

  　　[root@test1 local]# /usr/bin/mysqladmin -u root password 123456
  

  　　注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。
• 测试是否修改成功
  • 不用密码登录

    　　[root@test1 local]# mysql
    　　ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
    

    　　显示错误，说明密码已经修改。
  • 用修改后的密码登录

    　　[root@test1 local]# mysql -u root -p 
    　　Enter password: (输入修改后的密码123456)
    　　Welcome to the MySQL monitor.　Commands end with ; or \g.
    　　Your MySQL connection id is 4 to server version: 4.0.16-standard
    　　Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    　　mysql>
    

    　　成功！ 　　这是通过mysqladmin命令修改口令，也可通过修改库来更改口令。

    5. 启动与停止

• 启动 　　MySQL安装完成后启动文件mysql在/etc/init.d目录下，在需要启动时运行下面命令即可。

  　　[root@test1 init.d]# /etc/init.d/mysql start
  

• 停止

  　　/usr/bin/mysqladmin -u root -p shutdown
  

• 自动启动
  • 察看mysql是否在自动启动列表中 　　[root@test1 local]#　/sbin/chkconfig –list PS:系统默认是没有chkconfig这个命令的,需要自己安装,debian下安装很简单,有apt源.
  • 把MySQL添加到你系统的启动服务组里面去 　　[root@test1 local]#　/sbin/chkconfig　– add　mysql
  • 把MySQL从启动服务组里面删除。 　　[root@test1 local]#　/sbin/chkconfig　– del　mysql

    6. 更改MySQL目录

    　　MySQL默认的数据文件存储目录为/var/lib/mysql。假如要把目录移到/home/data下需要进行下面几步：
• home目录下建立data目录 　　cd /home 　　mkdir data
• 把MySQL服务进程停掉： 　　mysqladmin -u root -p shutdown
• 把/var/lib/mysql整个目录移到/home/data 　　mv /var/lib/mysql　/home/data/ 　　这样就把MySQL的数据文件移动到了/home/data/mysql下
• 找到my.cnf配置文件 　　如果/etc/目录下没有my.cnf配置文件，请到/usr/share/mysql/下找到*.cnf文件，拷贝其中一个到/etc/并改名为my.cnf)中。命令如下： 　　 [root@test1 mysql]# cp /usr/share/mysql/my-medium.cnf　/etc/my.cnf
• 编辑MySQL的配置文件/etc/my.cnf 　　为保证MySQL能够正常工作，需要指明mysql.sock文件的产生位置。 修改socket=/var/lib/mysql/mysql.sock一行中等号右边的值为：/home/mysql/mysql.sock 。操作如下： 　　 vi　 my.cnf　　　 (用vi工具编辑my.cnf文件，找到下列数据修改之) 　　 # The MySQL server 　　　 [mysqld] 　　　 port　　　= 3306 　　　#socket　 = /var/lib/mysql/mysql.sock（原内容，为了更稳妥用“#”注释此行） 　　　 socket　 = /home/data/mysql/mysql.sock　　　（加上此行）
• 修改MySQL启动脚本/etc/rc.d/init.d/mysql 　　最后，需要修改MySQL启动脚本/etc/rc.d/init.d/mysql，把其中datadir=/var/lib/mysql一行中，等号右边的路径改成你现在的实际存放路径：home/data/mysql。 　　[root@test1 etc]# vi　/etc/rc.d/init.d/mysql 　　#datadir=/var/lib/mysql　　　　（注释此行） 　　datadir=/home/data/mysql　　 （加上此行）
• 重新启动MySQL服务 　　/etc/rc.d/init.d/mysql　start 　　或用reboot命令重启Linux 　　如果工作正常移动就成功了，否则对照前面的7步再检查一下。

  7. MySQL的常用操作

  　　注意：MySQL中每个命令后都要以分号；结尾。
• 显示数据库 　　mysql> show databases; 　　+----------+ 　　| Database | 　　+----------+ 　　| mysql　　| 　　| test　　 | 　　+----------+ 　　2 rows in set (0.04 sec) 　　Mysql刚安装完有两个数据库：mysql和test。mysql库非常重要，它里面有MySQL的系统信息，我们改密码和新增用户，实际上就是用这个库中的相关表进行操作。
• 显示数据库中的表 　　mysql> use mysql; （打开库，对每个库进行操作就要打开此库，类似于foxpro ） 　　Database changed

　　mysql> show tables; 　　+-----------------+ 　　| Tables_in_mysql | 　　+-----------------+ 　　| columns_priv　　| 　　| db　　　　　　　| 　　| func　　　　　　| 　　| host　　　　　　| 　　| tables_priv　　 | 　　| user　　　　　　| 　　+-----------------+ 　　6 rows in set (0.01 sec)

• 显示数据表的结构： 　　describe 表名;
• 显示表中的记录： 　　select from 表名; 　　例如：显示mysql库中user表中的纪录。所有能对MySQL用户操作的用户都在此表中。 　　Select from user;
• 建库： 　　create database 库名; 　　例如：创建一个名字位aaa的库 　　mysql> create databases aaa;
• 建表： 　　use 库名； 　　create table 表名 (字段设定列表)； 　　例如：在刚创建的aaa库中建立表name,表中有id(序号，自动增长)，xm（姓名）,xb（性别）,csny（出身年月）四个字段 　　use aaa; 　　mysql> create table name (id int(3) auto_increment not null primary key, xm char(8),xb char(2),csny date); 　　可以用describe命令察看刚建立的表结构。 　　mysql> describe name;

　　+-------+---------+------+-----+---------+----------------+ 　　| Field | Type　　| Null | Key | Default | Extra　　　　　| 　　+-------+---------+------+-----+---------+----------------+ 　　| id　　| int(3)　|　　　| PRI | NULL　　| auto_increment | 　　| xm　　| char(8) | YES　|　　 | NULL　　|　　　　　　　　| 　　| xb　　| char(2) | YES　|　　 | NULL　　|　　　　　　　　| 　　| csny　| date　　| YES　|　　 | NULL　　|　　　　　　　　| 　　+-------+---------+------+-----+---------+----------------+

• 增加记录 　　例如：增加几条相关纪录。 　　mysql> insert into name values('','张三','男','1971-10-01'); 　　mysql> insert into name values('','白云','女','1972-05-20'); 　　可用select命令来验证结果。 　　mysql> select * from name; 　　+----+------+------+------------+ 　　| id | xm　 | xb　 | csny　　　 | 　　+----+------+------+------------+ 　　|　1 | 张三 | 男　 | 1971-10-01 | 　　|　2 | 白云 | 女　 | 1972-05-20 | 　　+----+------+------+------------+
• 修改纪录 　　例如：将张三的出生年月改为1971-01-10 　　mysql> update name set csny='1971-01-10' where xm='张三';
• 删除纪录 　　例如：删除张三的纪录。 　　mysql> delete from name where xm='张三';
• 删库和删表 　　drop database 库名; 　　drop table 表名；

8. 增加MySQL用户

　　格式：grant select on 数据库. to 用户名@登录主机 identified by "密码" 例1、增加一个用户user_1密码为123，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MySQL，然后键入以下命令： 　　mysql> grant select,insert,update,delete on . to user_1@"%" Identified by "123"; 例1增加的用户是十分危险的，如果知道了user_1的密码，那么他就可以在网上的任何一台电脑上登录你的MySQL数据库并对你的数据为所欲为了，解决办法见例2。 　　例2、增加一个用户user_2密码为123,让此用户只可以在localhost上登录，并可以对数据库aaa进行查询、插入、修改、删除的 操作（localhost指本地主机，即MySQL数据库所在的那台主机），这样用户即使用知道user_2的密码，他也无法从网上直接访问数据库，只能 通过MYSQL主机来操作aaa库。 　　mysql>grant select,insert,update,delete on aaa. to user_2@localhost identified by "123"; 　　用新增的用户如果登录不了MySQL，在登录时用如下命令： 　　mysql -u user_1 -p　-h 192.168.113.50　（-h后跟的是要登录主机的ip地址） 我们知道，在ms sql server中或access中， 若要查询前10条记录，使用top 10即可， 但在mysql中 不支持这个写法，它用limit 10。 我们可以利用MySQL中 SELECT支持的一个子句——LIMIT——来完成这项功能。 LIMIT可以实现top N查询，也可以实现M至N（某一段）的记录查询，具体语法如下：

SELECT * FROM MYTABLE
ORDER BY AFIELD 
LIMIT offset, recnum

其中offset为从第几条（M+1）记录开始，recnum为返回的记录条数。例：

select * from mytable
order by afield 
limit 2, 5

即意为从第3条记录开始的5条记录。

9. 备份与恢复

• 备份 　　例如：将上例创建的aaa库备份到文件back_aaa中 　　[root@test1 root]# cd　/home/data/mysql　(进入到库目录，本例库已由val/lib/mysql转到/home/data/mysql，见上述第七部分内容) 　　[root@test1 mysql]# mysqldump -u root -p --opt aaa > back_aaa
• 恢复 　　[root@test mysql]# mysql -u root -p ccc < back_aaa

  10. 常见问题：

• "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' "错误 解决方法： 这是没启动mysql的守护进程，执行service mysqld start就行了
• 如果你想连接你的mysql的时候发生这个错误： ERROR 1130: Host '210.13.92.66' is not allowed to connect to this MySQL server 请按照如下方法解决:

  login as: root ///登陆系统
  [email protected]'s password: ///输入密码
  Last login: Tue Apr 15 14:06:54 2008 from 210.13.92.66
  [root@myserver ~]# /usr/local/mysql/bin/mysql -uroot -hlocalhost -p ///登入mysql
  Enter password: ///输入mysql对应用户的密码
  Welcome to the MySQL monitor. Commands end with ; or \g.
  Your MySQL connection id is 431
  Server version: 5.0.58-enterprise-gpl-log Source
  Type 'help;' or '\h' for help. Type '\c' to clear the buffer. ///登入成功的提示信息
  mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'rootpasswd' WITH GRANT OPTION; ///输入这段指令并回车后允许所有用root用户并且输入rootpasswd密码的主机登入该mysql Server 如果将'%'换成'10.1.1.1'那么只有10.1.1.1的主机可以登陆.
  Query OK, 0 rows affected (0.07 sec)
  mysql> \q ///退出mysql
  Bye
  [root@myserver ~]#
  

  另外还有一种方法转自其他地方 改表法。可能是你的帐号不允许从远程登陆，只能在localhost。这个时候只要在localhost的那台电脑，登入mysql后，更改 "mysql" 数据库里的 "user" 表里的 "host" 项，从"localhost"改称"%" mysql -u root -pvmwaremysql>use mysql;mysql>update user set host = '%' where user = 'root';mysql>select host, user from user; 直接在cmd 下进入mysql\bin 下 运行 GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION GUI 安装

  sudo apt-get install mysql-workbench
  

11. mysql约束之alter总结

ALTER TABLE

• MODIFY 修改
• ADD 增加
• DROP 删除

• 非空 NOT NULL
• UNIQUE KEY 唯一
• DEFUALUT 默认
• AUTO_INCREMENT (必须与主键一起使用)

实例演示：

<1> 创建数据表tb1

mysql> create table tb1(id int);
Query OK, 0 rows affected (0.04 sec)

mysql> desc tb1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default  | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |        |
+-------+---------+------+-----+---------+-------+

<2> 为tb1添加非空约束 not null

mysql> alter table tb1
-> modify id int not null
-> ;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

注意： 删除非空约束使用同样方法

<3> 为tb1添加唯一键 unique key

mysql> alter table tb1
-> add unique key (id)
-> ;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

注意 ： not null + unique key 具有 primary key的功能，当表中没有primary key 时， not null + unique key = primary key

但是我们运行show create table tb1\G 查看详细信息时可以依然看到为 nunque key。

mysql> show create table tb1\G
********************** 1. row **********************
Table: tb1
Create Table: CREATE TABLE `tb1` (
  `id` int(11) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

<4> 为tb1添加主键 primary key（在一张表中只能有一个主键）

mysql> alter table tb1
-> add primary key (id)
-> ;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

注意： 虽然此处表结构表面上未发生变化，但是我们运行show create table tb1\G 查看详细信息就可以发现已经添加上主键了。

mysql> show create table tb1\G
*************************** 1. row ***************************
Table: tb1
Create Table: CREATE TABLE `tb1` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

<5> 删除主键 primary key

mysql> alter table tb1
-> drop primary key
-> ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb1\G
*************************** 1. row ***************************
   Table: tb1
Create Table: CREATE TABLE `tb1` (
  `id` int(11) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

注意： 因为主键是非空且唯一的，所以删除时直接删除主键即可，不用跟具体的列名。

<5> 删除唯一键 unique key

mysql> alter table tb1
-> drop key id
-> ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb1\G
*************************** 1. row ***************************
   Table: tb1
Create Table: CREATE TABLE `tb1` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> desc tb1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

注意：add操作是需要在列名后加（），drop不需要

<6> 把id这一列设置为自增长

mysql> desc tb1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | NO   |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table tb1
-> modify id int primary key auto_increment
-> ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb1;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id| int(11)     | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)

<7> 添加外键 foreign key

首先，我们来创建tb2

mysql> create table tb2(
-> s_id int primary key,
-> name varchar(20) not null
-> );
Query OK, 0 rows affected (0.03 sec)

其次，我们假设后期需要用到tb2关联tb1，我们添加foreign key

mysql> alter table tb2
-> add constraint tb2_1_fk foreign key (s_id) references tb1 (id)
-> ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

注意： constraint tb2_1_fk 表示将外键名称定为tb2_1_fk，如果我们在添加时不定义，系统就会自动分配外键名称。

最后我们查询表详细信息

mysql> show create table tb2\G
*************************** 1. row ***************************
   Table: tb2
 Create Table: CREATE TABLE `tb2` (
  `s_id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`s_id`),
  CONSTRAINT `tb2_1_fk` FOREIGN KEY (`s_id`) REFERENCES `tb1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

<8> 删除外键 foreign key

mysql> alter table tb2
-> drop foreign key tb2_1_fk;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tb2\G
*************************** 1. row ***************************
   Table: tb2
Create Table: CREATE TABLE `tb2` (
  `s_id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

<9> 设置初始默认值 default

mysql> alter table tb2
-> add age int not null default 18;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc tb2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| s_id  | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
| age   | int(11)     | NO   |     | 18      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

12. 存储引擎

12.1. 1. InnoDB

InnoDB 是 MySQL 默认的事务型存储引擎，只有在需要 InnoDB 不支持的特性时，才考虑使用其它存储引擎。

采用 MVCC 来支持高并发，并且实现了四个标准的隔离级别，默认级别是可重复读。

表是基于聚簇索引建立的，它对主键的查询性能有很高的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够自动在内存中创建哈希索引以加速读操作的自适应哈希索引、能够加速插入操作的插入缓冲区等。

通过一些机制和工具支持真正的热备份。

12.2. 2. MyISAM

MyISAM 提供了大量的特性，包括全文索引、压缩、空间函数（GIS）等。但 MyISAM 不支持事务和行级锁，而且崩溃后无法安全恢复。

只能对整张表加锁，而不是针对行。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

可以包含动态或者静态的行。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

如果表在创建并导入数据以后，不会再进行修改操作，那么这样的表适合采用 MyISAM 压缩表。

对于只读数据，或者表比较小、可以容忍修复操作，则依然可以继续使用 MyISAM。

MyISAM 设计简单，数据以紧密格式存储，所以在某些场景下性能很好。

12.3. 3. InnoDB 与 MyISAM 的比较

事务

InnoDB 是事务型的。

备份

InnoDB 支持在线热备份。

崩溃恢复

MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。

并发

MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。

其它特性

MyISAM 支持全文索引，地理空间索引。

13. 数据类型

13.1. 1. 整型

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用 8, 16, 24, 32, 64 位存储空间，一般情况下越小的列越好。

INT(11) 中的数字只是规定了交互工具显示字符的个数，对于存储和计算来说是没有意义的。

13.2. 2. 浮点数

FLOAT 和 DOUBLE 为浮点类型，DECIMAL 为高精度小数类型。CPU 原生支持浮点运算，但是不支持 DECIMAl 类型的计算，因此 DECIMAL 的计算比浮点类型需要更高的代价。

FLOAT、DOUBLE 和 DECIMAL 都可以指定列宽，例如 DECIMAL(18, 9) 表示总共 18 位，取 9 位存储小数部分，剩下 9 位存储整数部分。

13.3. 3. 字符串

主要有 CHAR 和 VARCHAR 两种类型，一种是定长的，一种是变长的。

VARCHAR 这种变长类型能够节省空间，因为只需要存储必要的内容。但是在执行 UPDATE 时可能会使行变得比原来长，当超出一个页所能容纳的大小时，就要执行额外的操作。MyISAM 会将行拆成不同的片段存储，而 InnoDB 则需要分裂页来使行放进页内。

VARCHAR 会保留字符串末尾的空格，而 CHAR 会删除。

13.4. 4. 时间和日期

MySQL 提供了两种相似的日期时间类型：DATATIME 和 TIMESTAMP。

DATATIME

能够保存从 1001 年到 9999 年的日期和时间，精度为秒，使用 8 字节的存储空间。

它与时区无关。

默认情况下，MySQL 以一种可排序的、无歧义的格式显示 DATATIME 值，例如“2008-01-16 22:37:08”，这是 ANSI 标准定义的日期和时间表示方法。

TIMESTAMP

和 UNIX 时间戳相同，保存从 1970 年 1 月 1 日午夜（格林威治时间）以来的秒数，使用 4 个字节，只能表示从 1970 年 到 2038 年。

它和时区有关。

MySQL 提供了 FROM_UNIXTIME() 函数把 UNIX 时间戳转换为日期，并提供了 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。

默认情况下，如果插入时没有指定 TIMESTAMP 列的值，会将这个值设置为当前时间。

应该尽量使用 TIMESTAMP，因为它比 DATETIME 空间效率更高。

14. 索引

索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。

索引能够轻易将查询性能提升几个数量级。

对于非常小的表、大部分情况下简单的全表扫描比建立索引更高效。对于中到大型的表，索引就非常有效。但是对于特大型的表，建立和使用索引的代价将会随之增长。这种情况下，需要用到一种技术可以直接区分出需要查询的一组数据，而不是一条记录一条记录地匹配，例如可以使用分区技术。

14.1. 1. 索引分类

1.1 B-Tree 索引

B-Tree 索引是大多数 MySQL 存储引擎的默认索引类型。

因为不再需要进行全表扫描，只需要对树进行搜索即可，因此查找速度快很多。

可以指定多个列作为索引列，多个索引列共同组成键。B-Tree 索引适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。

除了用于查找，还可以用于排序和分组。

如果不是按照索引列的顺序进行查找，则无法使用索引。

1.2 哈希索引

基于哈希表实现，优点是查找非常快。

在 MySQL 中只有 Memory 引擎显式支持哈希索引。

InnoDB 引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B-Tree 索引之上再创建一个哈希索引，这样就让 B-Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

限制：哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能影响并不明显；无法用于分组与排序；只支持精确查找，无法用于部分查找和范围查找；如果哈希冲突很多，查找速度会变得很慢。

1.3. 空间索引（R-Tree）

MyISAM 存储引擎支持空间索引，可以用于地理数据存储。

空间索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

1.4 全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较索引中的值。

使用 MATCH AGAINST，而不是普通的 WHERE。

14.2. 2. 索引的优点

• 大大减少了服务器需要扫描的数据量；

• 帮助服务器避免进行排序和创建临时表；

• 将随机 I/O 变为顺序 I/O。

14.3. 3. 索引优化

3.1 独立的列

在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。

例如下面的查询不能使用 actor_id 列的索引：

SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

3.2 前缀索引

对于 BLOB、TEXT 和 VARCHAR 类型的列，必须使用前缀索引，只索引开始的部分字符。

对于前缀长度的选取需要根据 索引选择性 来确定：不重复的索引值和记录总数的比值。选择性越高，查询效率也越高。最大值为 1 ，此时每个记录都有唯一的索引与其对应。

3.3 多列索引

在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 file_id 设置为多列索引。

SELECT file_id, actor_ id FROM sakila.film_actor
WhERE actor_id = 1 OR film_id = 1;

3.4 索引列的顺序

让选择性最强的索引列放在前面，例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;

   staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
               COUNT(*): 16049

3.5 聚簇索引

聚簇索引并不是一种索引类型，而是一种数据存储方式。

术语“聚簇”表示数据行和相邻的键值紧密地存储在一起，InnoDB 的聚簇索引的数据行存放在 B-Tree 的叶子页中。

因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

优点

1. 可以把相关数据保存在一起，减少 I/O 操作；
2. 因为数据保存在 B-Tree 中，因此数据访问更快。

缺点

1. 聚簇索引最大限度提高了 I/O 密集型应用的性能，但是如果数据全部放在内存，就没必要用聚簇索引。
2. 插入速度严重依赖于插入顺序，按主键的顺序插入是最快的。
3. 更新操作代价很高，因为每个被更新的行都会移动到新的位置。
4. 当插入到某个已满的页中，存储引擎会将该页分裂成两个页面来容纳该行，页分裂会导致表占用更多的磁盘空间。
5. 如果行比较稀疏，或者由于页分裂导致数据存储不连续时，聚簇索引可能导致全表扫描速度变慢。

3.6 覆盖索引

索引包含所有需要查询的字段的值。

14.4. 4. B-Tree 和 B+Tree 原理

4. 1 B-Tree

为了描述 B-Tree，首先定义一条数据记录为一个二元组 [key, data]，key 为记录的键，data 为数据记录除 key 外的数据。

B-Tree 是满足下列条件的数据结构：

• 所有叶节点具有相同的深度，也就是说 B-Tree 是平衡的；
• 一个节点中的 key 从左到右非递减排列；
• 如果某个指针的左右相邻 key 分别是 key_i 和 key_i+1，且不为 null，则该指针指向节点的所有 key 大于 key_i 且小于 key_i+1。

在 B-Tree 中按 key 检索数据的算法非常直观：首先从根节点进行二分查找，如果找到则返回对应节点的 data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到 null 指针，前者查找成功，后者查找失败。

由于插入删除新的数据记录会破坏 B-Tree 的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持 B-Tree 性质。

4.2 B+Tree

与 B-Tree 相比，B+Tree 有以下不同点：

• 每个节点的指针上限为 2d 而不是 2d+1；
• 内节点不存储 data，只存储 key，叶子节点不存储指针。

4.3 带有顺序访问指针的 B+Tree

一般在数据库系统或文件系统中使用的 B+Tree 结构都在经典 B+Tree 基础上进行了优化，在叶子节点增加了顺序访问指针，做这个优化的目的是为了提高区间访问的性能。

4.4 为什么使用 B-Tree 和 B+Tree

红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用 B-/+Tree 作为索引结构。

页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为 4k），主存和磁盘以页为单位交换数据。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。为了减少磁盘 I/O，磁盘往往不是严格按需读取，而是每次都会预读。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次 I/O 就可以完全载入。B-Tree 中一次检索最多需要 h-1 次 I/O（根节点常驻内存），渐进复杂度为 O(h)=O(logdN)。一般实际应用中，出度 d 是非常大的数字，通常超过 100，因此 h 非常小（通常不超过 3）。而红黑树这种结构，h 明显要深的多。并且于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，效率明显比 B-Tree 差很多。

B+Tree 更适合外存索引，原因和内节点出度 d 有关。由于 B+Tree 内节点去掉了 data 域，因此可以拥有更大的出度，拥有更好的性能。

15. 查询性能优化

15.1. 1. Explain

用来分析 SQL 语句，分析结果中比较重要的字段有：

• select_type : 查询类型，有简单查询、联合查询和子查询

• key : 使用的索引

• rows : 扫描的行数

15.2. 2. 减少返回的列

慢查询主要是因为访问了过多数据，除了访问过多行之外，也包括访问过多列。

最好不要使用 SELECT * 语句，要根据需要选择查询的列。

15.3. 3. 减少返回的行

最好使用 LIMIT 语句来取出想要的那些行。

还可以建立索引来减少条件语句的全表扫描。例如对于下面的语句，不适用索引的情况下需要进行全表扫描，而使用索引只需要扫描几行记录即可，使用 Explain 语句可以通过观察 rows 字段来看出这种差异。

SELECT * FROM sakila.film_actor WHERE film_id = 1;

15.4. 4. 拆分大的 DELETE 或 INSERT 语句

如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

DELEFT FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);

rows_affected = 0
do {
    rows_affected = do_query(
    "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

16. 分库与分表

16.1. 1. 分库与分表的原因

随着时间和业务的发展，数据库中的表会越来越多，并且表中的数据量也会越来越大，那么读写操作的开销也会随着增大。

16.2. 2. 实现方式

2.1 垂直切分

将表按功能模块、关系密切程度划分出来，部署到不同的库上。例如，我们会建立商品数据库 payDB、用户数据库 userDB 等，分别用来存储项目与商品有关的表和与用户有关的表。

2.2 水平切分

把表中的数据按照某种规则存储到多个结构相同的表中，例如按 id 的散列值、性别等进行划分。

2.3 切分的选择

如果数据库中的表太多，并且项目各项业务逻辑清晰，那么垂直切分是首选。

如果数据库的表不多，但是单表的数据量很大，应该选择水平切分。

16.3. 3. Merge 存储引擎

该存储引擎支持分表。

16.4. 4. 分库与分表存在的问题

4.1 事务问题

在执行分库分表之后，由于数据存储到了不同的库上，数据库事务管理出现了困难。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

4.2 跨库跨表连接问题

在执行了分库分表之后，难以避免会将原本逻辑关联性很强的数据划分到不同的表、不同的库上。这时，表的连接操作将受到限制，我们无法连接位于不同分库的表，也无法连接分表粒度不同的表，导致原本只需要一次查询就能够完成的业务需要进行多次才能完成。

4.3 额外的数据管理负担和数据运算压力

最显而易见的就是数据的定位问题和数据的增删改查的重复执行问题，这些都可以通过应用程序解决，但必然引起额外的逻辑运算。

16.5. 5. 分表与分区的不同

分表，就是讲一张表分成多个小表，这些小表拥有不同的表名；而分区是将一张表的数据分为多个区块，这些区块可以存储在同一个磁盘上，也可以存储在不同的磁盘上，这种方式下表仍然只有一个。

17. 故障转移和故障恢复

故障转移也叫做切换，当主库出现故障时就切换到备库，使备库成为主库。故障恢复顾名思义就是从故障中恢复过来，并且保证数据的正确性。

提升备库或切换角色

提升一台备库为主库，或者在一个主-主复制结构中调整主动和被动角色。

虚拟 IP 地址和 IP 托管

为 MySQL 实例指定一个逻辑 IP 地址，当 MySQL 实例失效时，可以将 IP 地址转移到另一台 MySQL 服务器上。

中间件解决方案

通过代理，可以路由流量到可以使用的服务器上。

在应用中处理故障转移

将故障转移整合到应用中可能导致应用变得太过笨拙。