一、常用指令
- SHOW DATABASES;
- SHOW CREATE DATABASE database_name;
- DROP DATABASE database_name;
- USE database_name;
- CREATE DATABASE database_name;
- SHOW VARIABLES LIKE 'storage_engine'; '%char%'
- DESCRIBE(DESC) database_name;
- ALTER TABLE <旧表名> RENAME [TO] <新表名>;
- ALTER TABLE <表名> MODIFY <字段名> <数据类型>;
- ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
- ... ...
二、数据类型
- 整数类型
TINYINT(1字节)、SMALLINT(2字节)、MEDIUMINT(3字节)、INT/INTEGER(4字节)、BIGINT（5字节） - 浮点数和定点数类型
MySQL中使用浮点数和定点数来表示小数，浮点类型有两种：单精度浮点类型(FLOAT)和双精度浮点类型(DOUBLE)。定点类型只有一种：DECIMAL。浮点类型和定点类型都可以用（M,N）来表示，其中M称为精度，表是总共的位数，N称为标度，表示小数的位数。
FLOAT(4字节)、DOUBLE(8字节)、DECIMAL(M+2个字节)
注：在MySQL中，定点数以字符串形式存储，在对精度要求比较高的时候（比如货币、科学数据等）使用DECIMAL的类型比较好，另外，两个浮点数进行减法和比较运算时也容易出问题，所以在使用浮点类型时需要注意，并尽量避免做浮点数比较。 - 日期与时间类型
YEAR(1字节)、TIME(3字节)、DATE(3字节)、DATETIME(8字节)、TIMESTAMP(4字节) - 文本字符串类型
| 名称 | 描述 | 存储空间 |
|---|---|---|
| CHAR(M) | 定长非二进制字符串 | M字节，1<= M <=255 |
| VARCHAR(M) | 变长非二进制字符串 | L+1字节，其中L<= M，1<= M <=255 |
| TINYTEXT | 非常小的非二进制字符串 | L+1字节，L<2^8 |
| TEXT | 小的非二进制字符串 | L+1字节，L<2^8 |
| MEDIUMTEXT | 中等大小的非二进制字符串 | L+3字节，L<2^24 |
| LONGTEXT | 大的非二进制字符串 | L+4字节，L<2^32 |
| ENUM | 枚举类型，只能有一个枚举字符串值 | 1或2字节，取决于枚举值的数目，最大65535 |
| SET | 一个设置，字符串对象可以有0或多个SET成员 | 1，2，4或8字节，取决于成员数量，最多64 |
- 二进制字符串类型
BIT、BINARY(M)、VARBINARY(M)、TINYBLOB(M)、BLOB(M)、MEDIUMBLOB(M)、LONGBLOB(M)
三、索引
- 索引的分类
1.1 普通索引和唯一索引
普通索引是MySQL中基本索引类型，允许在定义索引的列中插入重复值和空值。
唯一索引要求索引列的值必须唯一，但允许有空值。主键索引是一种特殊的唯一索引，不允许空值。
1.2 单列索引和组合索引
单列索引即一个索引只包含单个列，一个表可以有多个单列索引。
组合索引指的是在表的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。
1.3 全文索引
全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文所以可以在CHAR、VARCHAR或者TEXT类型的列上创建。MySQL中只有MyISAM存储引擎支持全文索引。
1.4 空间索引
空间索引只能在村塾引擎为MyISAM的表中创建，不常用。 - 索引的设计原则
（1）索引数量不能太多，一个表中如果有大量的索引，不仅占用磁盘空间，而且会影响INSERT、DELETE、UPDATE等语句的性能。
（2）避免对经常更新的表进行过多的索引，并且索引中的列尽可能少。而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
（3）数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
（4）在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引，否则不但不会提高查询效率，反而会严重降低数据更新效率。
（5）当唯一性是某种数据本身的特征时，指定唯一索引，使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
（6）在频繁进行排序或分组的列上建立索引，如果待排序的列有多个，可以建立组合索引。
四、性能优化
- 查询优化
1.1 分析查询语句
EXPLAIN [EXTENDED] SELECT select_option;
分析查询结果：
| 名称 | 描述 |
|---|---|
| id | SELECT识别符，是SELECT的查询序列号 |
| select_type | select语句的类型，有以下几种取值： SIMPLE 简单查询，不包括链接查询和子查询； PRIMARY 主查询，或者最外层的查询； UNION 链接查询的第2个或后面的查询语句； |
| table | 查询的表 |
| type | 表的连接类型，按照最佳类型到最差类型列出： 1. system 仅有一行的系统表，const类型的特例； 2. const 数据表最多只有一个匹配行，用于常数值比较主键或唯一索引； 3. eq_ref 对于每个来自前面的表的行的组合，从该表中读取一行。用于一个索引的所有部分都在查询中使用并且索引是UNIQUE或PRIMARY KEY时； 4. ref |
| possible_keys | 指出MySQL能使用哪个索引在该表中查找行 |
| key | 表示查询实际使用到的索引 |
| key_len | 表示MySQL选择的索引字段按字节计算的长度，通过key_len可以确定MySQL将实际使用一个多列索引中的几个字段 |
| ref | 表示使用哪个列或常数与索引一起来查询记录 |
| rows | 显示MySQL在表中进行查询时必须检查的行数 |
| extra | 表示MySQL在处理查询时的详细信息 |
1.2 引起索引失效的查询
（1）使用LIKE关键字的查询语句：在使用LIKE关键字查询的语句中，如果匹配字符串的第一个字符为"%"，索引失效，只有"%"不在第一个位置，索引才会起作用。
（2）使用多列索引的查询语句：MySQL可以为多个字段建立索引，一个索引可以包括16个字段。对于多列索引，只有查询条件中使用了这些字段中的第1个字段，索引才会被使用。
（3）使用OR关键字的查询语句：查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才会使用索引。
1.3 子查询的优化
子查询是指SELECT语句的嵌套查询，一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要很多步骤才能完成的SQL操作。子查询虽然可以使查询语句很灵活，但执行效率不高。这是因为执行子查询时，MySQL需要为内层查询语句的查询结果在内存中建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这个临时表。因此速度会减慢。
再MySQL中，可以使用连接（JOIN）查询来代替子查询，连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能会更好。
- 数据库结构优化
合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。数据库结构的设计，需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。常用的规则有以下几点：
（1）将字段很多的表分解成多个表
（2）增加中间表：针对需要经常联合查询的表
（3）增加冗余字段
（4）优化插入记录的速度：影响插入速度的主要使索引、唯一性校验、一次插入记录条数等。
对于InnoDB引擎的表，常见的优化方法如下：
a. 禁用唯一性检查：插入之前使用set unique_checks=0禁用，完成之后再设置为1开启，MyISAM同样适用；
b. 禁用外键检查：set foreign_key_checks=0;
c. 禁止自动提交：set autocommit=0;
（5）分析表、检查表和优化表 - MySQL服务器优化
3.1 优化服务器硬件
（1）配置较大的内存，内容的速度比磁盘I/O快得多，可以通过增加系统的缓冲区容量，使数据在内存停留的时间更长，减少磁盘I/O；
（2）配置高速磁盘系统，以减少读盘的等待时间，提高响应速度；
（3）合理分布磁盘I/O，把磁盘I/O分散在多个设备，以减少资源竞争，提高并行操作能力；
（4）配置多处理器，MySQL是多线程的数据库，多处理器可以同时执行多个线程；
3.2 优化MySQL参数
MySQL服务的配置参数都在my.cnf或者my.ini文件的[MySQLd]组中，常见参数如下：
（1）key_buffer_size：表示索引缓冲区大小，通常取决于内存的大小。索引缓冲区被所有的线程共享，适当增加索引缓冲区可以得到更好处理的索引（对所有读和多重写），但如果太大，导致操作系统频繁换页，反而会降低系统性能。
（2）table_cache：表示同时打开的表的个数，同时打开的表太多会影响系统性能。
（3）query_cache_size：表示查询缓冲区的大小，该参数需要和query_cache_type配合使用。当query_cache_type=0时，所有的查询都不使用查询缓冲区，但MySQL不会释放所配置的查询缓冲区；当query_cache_type=1时，所有的查询都将使用查询缓冲区，除非再查询语句中指定SQL_NO_CACHE；当query_cache_type=2时，只有再查询语句中使用SQL_CACHE关键字，查询才会使用查询缓冲区，使用查询缓冲区可以提高查询的速度，这种方式只适用于修改操作少且经常执行相同的查询操作的情况。
（4）sort_buffer_size：表示排序缓冲区的大小，值越大，进行排序的速度就越快。
（5）read_buffer_size：表示每个线程连续扫描时为扫描的每个表分配的缓冲区大小。
（6）innodb_buffer_pool_size：表示InnoDB类型的表和索引的最大缓存，值越大查询速度就越快，但是太大会影响操作系统性能。
（7）max_connections：表示数据库的最大连接数，并不是越大越好，会浪费内存的资源，过多的连接会导致MySQL服务器僵死。
（8）innodb_flush_log_at_trx_commit：表示何时将缓冲区的数据写入日志，并且将日志文件写入磁盘中。该参数时InnoDB引擎的重要参数。共有三个值：0，1，2。当值为0时表示每隔1秒将数据写入日志文件并将日志文件写入磁盘；值为1时表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘；值为2时表示每次提交事务时将数据写入日志文件，每隔1秒将日志文件写入磁盘；默认为1，安全性最高，但最耗时；如果设置为2，日志仍然每秒都写入硬盘，所以出现故障也不会丢失超过1~2秒的更新。
（9）back_log：表示再MySQL暂时停止回答新请求之前的短时间内，多少个请求可以被存在堆栈中。即表示对到来的TCP/IP连接的侦听队列的大小，只有期望在一个短时间内有很多连接时，才需要增加该参数的值
（10）thread_cache_size：表示可以复用的线程的数量，如果有很多新的线程，为了提高性能可以增大该值
（11）wait_timeout：表示服务器在关闭一个连接时等待行动的秒数，默认为28800。
