编程问题总结-MySQL

• Prepared语句的好处
• mysql server gone away
• MySQL语句的执行顺序
• SQL_CALC_FOUND_ROWS/FOUND_ROWS()
• 两大类触发器
• 主键与唯一索引的区别
• InnoDB行级锁
• 数据库连接池理解
• 外联接
• 为SELECT语句添加一个自动增加的列
• 分析MySQL语句执行时间和消耗资源
• 使用EXPLAIN分析MySQL语句的执行情况
• CASE…WHEN…THEN
• 关系型数据库三个范式
• 导出备份数据之后发送的写操作
• ON DUPLICATE KEY UPDATE
• 慢查询日志格式
• 常见MySQL性能优化技巧
• 覆盖索引
• B树
• GROUP BY注意点
• innodb_buffer_pool_size
• LIMIT
• MyISAM和InnoDB的比较
• MySQL中MyISAM引擎与InnoDB引擎有何区别
• MySQL一行记录最多能有多少个VARCHAR(255)类型的列？
• MySQL事务隔离级别（ISOLATION LEVEL）
• MySQL分区表理解
• MySQL主从同步工作模式理解
• MySQL异步
• MySQL索引类型
• MySQL自增主键出现不连续情况的原因
• ON与WHERE比较
• 临时表
• 查询缓存
• 垂直分区与水平分区
• 分表
• MySQL逻辑架构
• 表锁与行级锁
• 死锁
• 事务日志可以帮助提高事务的效率
• 在事务中混合使用了事务型和非事务型的表
• AUTOCOMMIT
• 显式锁定
• 多版本并发控制（ MVCC）
• 剖析MySQL查询性能
• MySQL数据类型选择的注意点
• 使用完全随机的字符串作为标识列会导致INSERT以及UPDATE语句变得很慢
• MySQL schema设计中的注意点
• 统计计数表设计
• 加快ALTER TABLE操作的速度
• 在有索引的情况下快速导入表数据
• B-Tree索引
• 哈希索引
• 空间数据索引（R-Tree）
• 全文索引
• 索引提高性能的原因
• 高性能的索引策略
• 聚簇索引的优缺点
• InnoDB顺序主键的高并发问题
• 覆盖索引
• 使用索引进行排序的注意点
• 索引和锁
• 维护索引和表
• 常见的导致查询过慢的原因
• MySQL查询使用WHERE条件时不同工作方式的性能问题
• 切分查询
• 分解关联查询
• MySQL查询执行方式理解
• MySQL查询的状态
• MySQL查询语句优化处理
• MySQL执行关联查询的方式
• 排序优化
• MySQL查询优化器的局限性
• MySQL不支持松散索引扫描
• 优化COUNT()查询
• 在同一个查询中统计同一个列的不同值的数量
• 优化关联查询
• 优化GROUP BY和DISTINCT
• 优化LIMIT分页
• 优化UNION查询
• 使用自定义变量
• 分区表
• 合并表
• 视图
• 绑定变量的优势及限制
• 字符集和字符校对
• MySQL配置的工作原理
• MySQL复制机制的工作过程
• EXPLAIN
• 匹配单个字符
• LIKE与REGEXP的区别
• 进行发音类似的匹配
• 子查询
• 返回最后一个AUTO_INCREMENT的值
• mysql_real_escape_string和mysql_escape_string有什么本质的区别，有什么用处，为什么被弃用？
• <=>操作符的用法及其与=操作符的区别
• skip-grant-tables
• 记录锁、间隙锁、Next-key锁
• 为什么不使用MySQL分区表
• MySQL半同步复制

Prepared语句的好处

• 对于在执行大量具有不同数据的相同查询时，可以提高执行速度（MySQL省去了部分语句解析工作）；
• 可以避免SQL注入攻击；

Prepared语句的基本思想是向MySQL发送一个需要执行的查询模板，然后再单独发送数据。因此可以向相同的Prepared语句发送大量相同的数据，对于批处理的插入操作来说是非常有用的。

mysql server gone away

MySQL本身是一个多线程的程序，每个连接过来，会开一个线程去处理相关的query，MySQL会定期回收长时间没有任何query的连接（时间周期受wait_timeout配置影响），由于Swoole是一个长驻内存的服务，建立了一个MySQL的连接，不主动关闭或者是用pconnect（持久连接）的方式，那么这个MySQL连接会一直保存着，然后长时间没有和数据库有交互，就被mysql server主动关闭了，之后继续用这个连接，就报mysql server gone away了。

解决方法

• 修改MySQL的wait_timeout值为一个非常大的值，此方法不太可取，可能会产生大量的sleep连接，导致MySQL连接上限了， 建议不使用；
• 每次query之前主动进行连接检测； 如果是用mysqli，可用内置的mysqli_ping

  <?php
  if (!$mysqli->ping()) {  
  mysqli->connect(); //重连
  }
  

如果是pdo，可以检测mysql server的服务器信息来判断

<?php
try {
  $pdo->getAttribute(\PDO::ATTR_SERVER_INFO);
}catch (\Exception $e) {
  if ($e->getCode() == 'HY000') {
    $pdo = new PDO(xxx);  //重连
  } else {
    throw $e;
  }
}

这个方案有个缺点：额外多一次请求，所以改进方法: 用一个全局变量存放最后一次query的时间，下一次query的时候先和现在时间对比一下，超过waite_timeout再重连，或者也可以用swoole_tick定时检测。

• 被动检测，每次query用try catch包起来，如有mysql gone away异常，则重新连接，再执行一次当前sql.

<?php
try {
  query($sql);
} catch (\Exception $e) {
  if ($e->getCode() == 'HY000') {
    reconnect(); // 重连
    query($sql)
  } else {
    throw $e;
  }
}

• 用短连接，务必每次操作完之后，手动close；

  ​

MySQL语句的执行顺序

MySQL的语句执行一共分为11步，最先执行的总是FROM操作，最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表，这个虚拟的表作为一个处理的输入，只是这些虚拟的表对用户来说是透明的，但是只有最后一个虚拟的表才会被作为结果返回。如果没有在语句中指定某一个子句，那么将会跳过相应的步骤。

• FORM：对FROM的左边的表和右边的表计算笛卡尔积，产生虚表VT1；

• ON：对虚表VT1进行ON筛选，只有那些符合的行才会被记录在虚表VT2中；

• JOIN：如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就会作为外部行添加到虚拟表VT2中，产生虚拟表VT3, 如果from子句中包含两个以上的表的话，那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤，一直到处理完所有的表为止；

• WHERE：对虚拟表VT3进行WHERE条件过滤。只有符合的记录才会被插入到虚拟表VT4中；

• GROUP BY：根据group by子句中的列，对VT4中的记录进行分组操作，产生VT5；

• **CUBE

  ROLLUP**：对表VT5进行cube或者rollup操作，产生表VT6；

• HAVING：对虚拟表VT6应用having过滤，只有符合的记录才会被 插入到虚拟表VT7中；

• SELECT：执行select操作，选择指定的列，插入到虚拟表VT8中；

• DISTINCT：对VT8中的记录进行去重。产生虚拟表VT9；

• ORDER BY：将虚拟表VT9中的记录按照进行排序操作，产生虚拟表VT10；

• LIMIT：取出指定行的记录，产生虚拟表VT11, 并将结果返回；

  ​

SQL_CALC_FOUND_ROWS/FOUND_ROWS()

在很多分页的程序中都这样写:

SELECT COUNT(*) from 'table' WHERE ......;      # 查出符合条件的记录总数
SELECT * FROM 'table' WHERE ...... LIMIT M,N;   # 查询当页要显示的数据

这样的语句可以改成:

SELECT SQL_CALC_FOUND_ROWS * FROM 'table' WHERE ......  LIMIT M, N;
SELECT FOUND_ROWS();

这样只要执行一次较耗时的复杂查询可以同时得到与不带LIMIT同样的记录条数：第二个SELECT返回一个数字，指示了在没有LIMIT子句的情况下，第一个SELECT返回了多少行。

两大类触发器

• DML触发器 是基于表而创建的，可以在一张表创建多个DML触发器。其特点是定义在表或者视图上、自动触发、不能被直接调用。用户可以针对INSERT、UPDATE、DELETE语句分别设置触发器，也可以针对一张表上的特定操作设置。触发器可以容纳非常复杂的SQL语句，但不管操作多么复杂，也只能作为一个独立的单元被执行、看作一个事务。如果在执行触发器的过程中发生了错误，则整个事务都会回滚。

• DDL触发器 是一种特殊的触发器，它在响应数据定义语言(DDL)语句时触发。可以用于在数据库中执行管理任务，例如审核以及规范数据库操作。

  ​

主键与唯一索引的区别

• UNIQUE KEY可空，PRIMARY KEY 不可空不可重复；

• UNIQUE KEY可以在一个表里的一个或多个字段定义（多个UNIQUE KEY可以同时存在），在一个表中只能有一个PRIMARY KEY；

• PRIMARY KEY一般在逻辑设计中用作记录标识，这也是设置PRIMARY KEY的本来用意，而UNIQUE KEY只是为了保证域/域组的唯一性。

  ​

InnoDB行级锁

如果是InnoDB引擎，就可以在事务里使用行锁，比如：

SELECT xx FROM xx [FORCE INDEX(PRIMARY)] WHERE xx FOR UPDATE 

被加锁的行，其他事务也能读取但如果想写的话就必须等待锁的释放（乐观锁）。 **InnoDB的行锁是针对索引加的锁**，不是针对记录加的锁，只有查询能够使用索引时才可以使用行级锁。不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

数据库连接池理解

由于创建连接和释放连接都有很大的开销（尤其是数据库服务器不在本地时，每次建立连接都需要进行TCP的三次握手，再加上网络延迟，造成的开销是不可忽视的），为了提升系统访问数据库的性能，可以事先创建若干连接置于连接池中，需要时直接从连接池获取，使用结束时归还连接池而不必关闭连接，从而避免频繁创建和释放连接所造成的开销，这是典型的用空间换取时间的策略。 连接池仅在超大型应用中才有价值。普通的应用采用MySQL长连接方案，每个php-fpm创建一个MySQL连接，每台机器开启100个php-fpm进程。如果有10台机器，每台机器并发的请求为100。实际上只需要创建1000个MySQL连接就能满足需求，数据库的压力并不大。即使有100台机器，硬件配置好的存储服务器依然可以承受。 达到数百或者数千台应用服务器时，MySQL服务器就需要维持十万级的连接。这时数据库的压力就会非常大了。连接池技术就可以派上用场了，可以大大降低数据库连接数。 基于swoole的AsyncTask模块实现的连接池是完美方案，编程简单，没有数据同步和锁的问题。甚至可以多个服务共享连接池。缺点是灵活性不如多线程连接池，无法动态增减连接，且有一次进程间通信的开销。 node.js/ngx_lua等在多进程的模式下，无法开发出真正的连接池，除非也像swoole_task这样来实现。

外联接

LEFT OUTER JOIN或LEFT JOIN \ RIGHT OUTER JOIN或RIGHT JOIN \ FULL OUTER JOIN或FULL JOIN。 左外联接的结果集中除了包括满足条件的行外，还包括左表所有的行，左表中没有满足条件的以空值的形式出现。

为SELECT语句添加一个自动增加的列

set @N = 0;
SELECT @N := @N +1 AS number, name, surname FROM gbtags_users;

分析MySQL语句执行时间和消耗资源

SET profiling=1;                          # 启动profiles，默认是没开启的
SELECT * FROM customers;                  # 执行要分析的SQL语句

SHOW profiles;                            # 查看SQL语句具体执行步骤及耗时
SHOW profile cpu,block io FOR QUERY 41;   # 查看ID为41的查询在各个环节的耗时和资源消耗

使用EXPLAIN分析MySQL语句的执行情况

mysql> explain select * from t_online_group_records where UNIX_TIMESTAMP(gre_updatetime) > 123456789;
+----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table                  | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t_online_group_records | ALL  | NULL          | NULL | NULL    | NULL |   47 | Using where |
+----+-------------+------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

重点关注type，rows和Extra。

type 操作的类型，可以用来判断有无使用到索引。结果值从好到坏：

... > RANGE(使用到索引) > INDEX > ALL(全表扫描)

一般查询应达到range级别，具体可能值如下：

• SYSTEM ：CONST的特例，当表上只有一条记录时
• CONST ：WHERE条件筛选后表上至多有一条记录匹配时，比如WHERE ID = 2
• EQ_REF ：参与连接运算的表是内表（两表连接时作为循环中的内循环遍历的对象，这样的表称为内表）。基于索引（连接字段上存在唯一索引或者主键索引，且操作符必须是“=”谓词，索引值不能为NULL）做扫描，使得对外表的一条元组，内表只有唯一一条元组与之对应。
• REF ：可以用于单表扫描或者连接。参与连接运算的表，是内表。 基于索引（连接字段上的索引是非唯一索引，操作符必须是“=”谓词，连接字段值不可为NULL）做扫描，使得对外表的一条元组，内表可有若干条元组与之对应。
• REF_OR_NULL ：类似REF，只是搜索条件包括：连接字段的值可以为NULL的情况，比如 where col = 2 or col is null
• RANGE ：基于索引做范围扫描，为诸如BETWEEN、IN、>=、LIKE类操作提供支持
• INDEX_SCAN ：索引做扫描，是基于索引在索引的叶子节点上找满足条件的数据（不需要访问数据文件）
• ALL ：全表扫描或者范围扫描：不使用索引，顺序扫描，直接读取表上的数据（访问数据文件）
• UNIQUE_SUBQUERY ：在子查询中，基于唯一索引进行扫描，类似于EQ_REF
• INDEX_SUBQUERY ：在子查询中，基于除唯一索引之外的索引进行扫描
• INDEX_MERGE ：多重范围扫描。两表连接的每个表的连接字段上均有索引存在且索引有序，结果合并在一起。适用于作集合的并、交操作。
• FT ：FULL TEXT，全文检索

rows SQL执行检查的记录数

Extra SQL执行的附加信息，如Using index表示查询只用到索引列，不需要去读表等。

CASE…WHEN…THEN

使用CASE来重新定义数值类型：

SELECT id,title,(CASE date WHEN '0000-00-00' THEN '' ELSE date END) AS date
FROM your_table
  
SELECT id,title,
(CASE status WHEN 0 THEN 'open' WHEN 1 THEN 'close' ELSE 'standby' END) AS status
FROM your_table

关系型数据库三个范式

• 第一范式：是对属性的原子性约束，要求属性具有原子性，不可再分解；
• 第二范式：是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；
• 第三范式：是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余（没有冗余的数据库未必是最好的数据库）。

导出备份数据之后发送的写操作

先使用mysqlbinlog导出这部分写操作SQL（基于时间点或位置）：

# 导出2014-09-21 09:59:59之后的binlog
mysqlbinlog --database="test" --start-date="2014-09-21 09:59:59" /var/lib/mysql/mybinlog.000001 > binlog.data.sql

# 导出起始id为123456之后的binlog：
mysqlbinlog --database="test" --start-position="123456" /var/lib/mysql/mybinlog.000001 > binlog.data.sql

导入备份数据

mysql -uroot -p test < mytable.20140921.bak.sql

导入binlog

mysql -uroot -p test < binlog.data.sql

ON DUPLICATE KEY UPDATE

# VALUES用来取插入的值，存在主键冲突时就更新，没有删除操作
INSERT INTO ... ON DUPLICATE KEY UPDATE col=VALUES(col)   

例：更新统计表

select * from player_count where player_id = 1;               # 查询统计表中是否有记录
insert into player_count(player_id,count) value(1,1);         # 没有记录就执行insert操作
update player_count set count = count+1 where player_id = 1;  # 有记录就执行update操作

用ON DUPLICATE KEY UPDATE的做法如下：

insert into player_count(player_id,count) value(1,1) on duplicate key update count=count+1;

慢查询日志格式

User@Host: edu_online[edu_online] @  [10.139.10.167]
Query_time: 1.958000  Lock_time: 0.000021 Rows_sent: 254786  Rows_examined: 254786
SET timestamp=1410883292;
select * from t_online_group_records;

日志显示该查询用了1.958秒，返回254786行记录，一共遍历了254786行记录。及具体的时间戳和SQL语句。 可以使用mysqldumpslow进行慢查询日志分析。

常见MySQL性能优化技巧

• 优化MySQL查询语句，使其使用查询缓存 对于相同的查询MySQL引擎会使用缓存，但是如果在SQL语句中使用函数，如NOW()、RAND()、 CURDATE()等等，则拼凑出的查询不会被认为是相同的查询。 ```php // 查询缓存不开启 $r = mysql_query(“SELECT username FROM user WHERE signup_date >= CURDATE()”);

// 开启查询缓存 $today = date(“Y-m-d”); $r = mysql_query(“SELECT username FROM user WHERE signup_date >= $today”);

* 当只要一行数据时使用LIMIT 1
  这样MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。

* 为搜索字段建索引
  索引并不一定就是给主键或是唯一的字段。如果在表中有某个字段总要会经常用来做搜索，那么就为其建立索引。

* **在JOIN表的时候使用相同类型的列，并将其索引**
  对于那些STRING类型，还需要有相同的字符集才行（两个表的字符集有可能不一样）

* 千万不要ORDER BY RAND()
  You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY 
  would evaluate the column multiple times. 
  当记录数据过多时，会非常慢。

* 避免`SELECT *`

* 使用ENUM而不是VARCHAR
  ENUM实际保存的是TINYINT，但其外表上显示为字符串。如果有一个字段的取值是有限而且固定的，那么，应该使用ENUM而不是VARCHAR。

* **尽可能的使用NOT NULL**
  “NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”
  NULL值需要额外的存储空间，而且在比较时也需要额外的逻辑。

* 把IP地址存成UNSIGNED INT，而不是VARCHAR(15)

* **固定长度的表会更快**
  如果表中的所有字段都是固定长度的，整个表会被认为是`static`或`fixed-length`。

* 垂直分割
  垂直分割是一种把数据库中的大表按列变成几张小表的方法，这样可以降低表的规模、方便使用缓存。
  需要注意的是，这些被分出去的字段所形成的表，不应该会被经常性地去JOIN，否则性能会比不分割时还要差很多。

* 拆分大的DELETE或INSERT语句
  因为这两个操作是会锁表的。如果有一个大的处理，一定把其拆分，使用LIMIT条件是一个好的方法。下面是一个示例：
```php
while (1) {
  // 每次只做1000条
  mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
  if (mysql_affected_rows() == 0) {
    // 没得可删了，退出！
    break;
  }

  // 每次都要休息一会儿
  usleep(50000);
}

• 越小的列会越快 如使用TINYINT而不是INT，使用DATE而不是DATETIME。

• 选择一个正确的存储引擎 MyISAM对于SELECT COUNT(*)这类的计算非常快，但是不支持行锁（写操作会锁表），也不支持事务。 InnoDB的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比MyISAM还慢。

• 持久链接 持久链接的目的是用来减少重新创建MySQL链接的次数。当一个链接被创建了，它会永远处在连接的状态，就算是数据库操作已经结束了。自从Apache开始重用它的子进程后下一次的HTTP请求会重用Apache的子进程，并重用相同的MySQL链接。

• 尽量早做过滤，使JOIN或者UNION等后续操作的数据量尽量小。

• 把能在逻辑层算的提到逻辑层来处理，如一些数据排序、时间函数计算等。

  ​

覆盖索引

对于

SELECT a FROM … WHERE b = …

这种查询，通常的做法是在b字段上建立索引，执行查询时系统会查询b索引进行定位，然后再利用此定位去表里查询需要的数据a。即该过程存在两次查询，一次是查询索引，一次是查询表。 使用Covering Index可以只查询一次索引就完成。建立一个组合索引b,a，当查询时，通过组合索引的b部分去定位，至于需要的数据a，立刻就可以在索引里得到，从而省略了表查询的过程。 如果使用Covering Index，要注意SELECT的方式，只SELECT必要的字段，而不能SELECT *，因为不太可能把所有的字段一起做索引。

可以使用EXPLAIN命令来确认是否使用了组合索引：如果在Extra里出现Using Index，就说明使用的是Covering Index。

实例1：

SELECT COUNT(*) FROM articles WHERE category_id = …

当在category_id建立索引后，这个查询使用的就是Covering Index（即，只查索引，而没有查表）。

实例2： 比如说在文章系统里分页显示的时候，一般的查询是这样的：

SELECT id, title, content FROM article ORDER BY created DESC LIMIT 10000, 10;

通常这样的查询会把索引建在created字段（其中id是主键），不过当LIMIT偏移很大时，查询效率仍然很低，改变一下查询：

SELECT id, title, content FROM article
INNER JOIN (
  SELECT id FROM article ORDER BY created DESC LIMIT 10000, 10
) AS page USING(id)

此时，建立复合索引created, id就可以在子查询里利用上Covering Index，快速定位id。

B树

B树是对二叉查找树的改进。它的设计思想是，将相关数据尽量集中在一起，以便一次读取多个数据，减少硬盘操作次数。 特点如下：

• 一个节点可以容纳多个值

• 除非数据已经填满，否则不会增加新的层。也就是说，B树追求"层"越少越好。

• 子节点中的值，与父节点中的值，有严格的大小对应关系。一般来说，如果父节点有a个值，那么就有a+1个子节点。 这种数据结构，非常有利于减少读取硬盘的次数。假定一个节点可以容纳100个值，那么3层的B树可以容纳100万个数据，如果换成二叉查找树，则需要20层。假定操作系统一次读取一个节点，并且根节点保留在内存中，那么B树在100万个数据中查找目标值，只需要读取两次硬盘。 数据库以B树格式储存，只解决了按照”主键”查找数据的问题。如果想查找其他字段，就需要建立索引（index）。所谓索引，就是以某个字段为关键字的B树文件（这里仅指基于B树的索引）。

  ​

GROUP BY注意点

• 在SELECT指定的字段要么就要包含在GROUP BY语句的后面，作为分组的依据；要么就要被包含在聚合函数中；

• HAVING子句的作用是筛选满足条件的组，即在分组之后过滤数据；

  ​

innodb_buffer_pool_size

innodb_buffer_pool_size这个参数主要作用是设置缓存innodb表的索引、数据、插入数据时的缓冲的缓存区大小。 默认值：128M，操作系统内存的70%-80%最佳。 此外，这个参数是非动态的，要修改这个值，需要重启mysqld服务。

如果因为内存不够，MySQL无法启动，就会在错误日志中出现如下报错：

InnoDB: mmap(137363456 bytes) failed; errno 12

LIMIT

返回不多于5行（小于等于）

SELECT prod_name FROM products LIMIT 5;

返回从第6行开始的5行（行号从0开始）

SELECT prod_name FROM products LIMIT 5,5;

返回从第6行开始的5行（LIMIT的一种替代语法）

SELECT prod_name FROM products LIMIT 5 OFFSET 5;

MyISAM和InnoDB的比较

• MySQL默认采用的是MyISAM。
• MyISAM不支持事务，而InnoDB支持。InnoDB的AUTOCOMMIT默认是打开的，即每条SQL语句会默认被封装成一个事务，自动提交，这样会影响速度，所以最好是把多条SQL语句显示放在begin和commit之间，组成一个事务去提交。
• InnoDB支持数据行级锁，MyISAM不支持行锁定，只支持锁定整个表。即MyISAM同一个表上的读锁和写锁是互斥的，MyISAM并发读写时如果等待队列中既有读请求又有写请求，默认写请求的优先级高，即使读请求先到，所以MyISAM不适合于有大量查询和修改并存的情况，那样查询进程会长时间阻塞。
• InnoDB支持外键，MyISAM不支持外键。
• InnoDB的主键范围更大，最大是MyISAM的2倍。
• InnoDB不支持全文索引，而MyISAM支持。全文索引是指对char、varchar和text中的每个词（停用词除外）建立倒排序索引。MyISAM的全文索引其实作用不大，因为它不支持中文分词，必须由使用者分词后加入空格再写到数据表里，而且少于4个汉字的词会和停用词一样被忽略掉。
• MyISAM支持GIS数据，InnoDB不支持。即MyISAM支持以下空间数据对象：Point、Line、Polygon、Surface等。
• 没有where的count()使用MyISAM要比InnoDB快得多。因为MyISAM内置了一个计数器，count()时它直接从计数器中读，而InnoDB必须扫描全表。所以在InnoDB上执行count()时一般要伴随where，且where中要包含主键以外的索引列（因为InnoDB中PRIMARY KEY是和raw data存放在一起的，而其他index则是单独存放，然后有个指针指向PRIMARY KEY。所以只是count()的话使用其他index扫描更快，而PRIMARY KEY则主要在扫描索引同时要返回raw data时的作用较大）。

MySQL中MyISAM引擎与InnoDB引擎有何区别

• 事务处理上方面: MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持 InnoDB提供事务支持事务，外部键等高级数据库功能

• SELECT UPDATE,INSERT，Delete操作 如果执行大量的SELECT，MyISAM是更好的选择 1.如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表 2.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。 3.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用

• 对AUTO_INCREMENT的操作 每表一个AUTO_INCREMEN列的内部处理。 MyISAM为INSERT和UPDATE操作自动更新这一列。这使得AUTO_INCREMENT列更快（至少10%）。在序列顶的值被删除之后就不能再利用。(当AUTO_INCREMENT列被定义为多列索引的最后一列，可以出现重使用从序列顶部删除的值的情况）。 AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置 对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引 更好和更快的auto_increment处理 如果你为一个表指定AUTO_INCREMENT列，在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器，它被用在为该列赋新值。 自动增长计数器仅被存储在主内存中，而不是存在磁盘上 关于该计算器的算法实现，请参考 AUTO_INCREMENT列在InnoDB里如何工作

• 表的具体行数 select count() from table,MyISAM只要简单的读出保存好的行数，注意的是，当count()语句包含 where条件时，两种表的操作是一样的 InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行

• 锁 表锁 提供行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)，另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”

• 构成上的区别： 每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。 .frm文件存储表定义。 数据文件的扩展名为.MYD (MYData)。 索引文件的扩展名是.MYI (MYIndex)。 基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB

  ​

MySQL一行记录最多能有多少个VARCHAR(255)类型的列？

MySQL表中一行的长度不能超过65535字节，VARCHAR(N)使用额外的1到2字节来存储值的长度，如果N<=255，则使用一个字节，否则使用两个字节；如果表格的编码为UTF8（一个字符占3个字节），那么VARCHAR(255)占用的字节数为255 * 3 + 2 = 767，这样，一行就最多只能有65535 / 765 = 85个VARCHAR(255)类型的列。

MySQL事务隔离级别（ISOLATION LEVEL）

• READ UNCOMMITTED 最低的隔离级别，可读取其他事务未提交的数据（事务可以看到其他事务尚未提交的修改），可能造成脏读。

• READ COMMITTED 只能读取已提交的数据，但是不可重复读（避免脏读）

• REPEATABLE READ 可重复读。这是MySQL默认的事务隔离级别。 用户A查询完之后，用户B将无法更新用户A所查询到的数据集中的任何数据（但是可以更新、插入和删除用户A查询到的数据集之外的数据），直到用户A事务结束才可以进行更新，这样就有效的防止用户在同一个事务中读取到不一致的数据。

• SERIALIZABLE 事务串行化，必须等待当前事务执行完，其他事务才可以执行写操作，有多个事务同时设置SERIALIZABLE时会产生死锁：

  ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
  

  这是四个隔离级别中限制最大的级别。因为并发级别较低，所以应只在必要时才使用该选项。

使用事务时设置隔离级别

START TRANSACTION
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
COMMIT
ROLLBACK

MySQL分区表理解

分区是一种粗粒度，简易的索引策略，适用于大数据的过滤场景。对于大数据（如10TB）而言，索引起到的作用相对小，因为索引的空间与维护成本很高，另外如果不是索引覆盖查询，将导致回表，造成大量磁盘IO。 分区表分为RANGE、LIST、HASH、KEY四种类型，并且分区表的索引是可以局部针对分区表建立的。 用RANGE创建分区表：

CREATE TABLE sales (
  id INT AUTO_INCREMENT,
  amount DOUBLE NOT NULL,
  order_day DATETIME NOT NULL,
  PRIMARY KEY(id, order_day)
) ENGINE=Innodb PARTITION BY RANGE(YEAR(order_day)) (
  PARTITION p_2010 VALUES LESS THAN (2010),
  PARTITION p_2011 VALUES LESS THAN (2011),
  PARTITION p_2012 VALUES LESS THAN (2012),
  PARTITION p_catchall VALUES LESS THAN MAXVALUE);

如果这么做，则order_day必须包含在主键中，且会产生一个问题：当年份超过阈值，到了2013，2014时需要手动创建这些分区，更好的方法是使用HASH：

CREATE TABLE sales ( 
  id INT PRIMARY KEY AUTO_INCREMENT,
  amount DOUBLE NOT NULL,
  order_day DATETIME NOT NULL
) ENGINE=Innodb PARTITION BY HASH(id DIV 1000000);

这种分区表示每100W条数据建立一个分区，且没有阈值范围的影响。

如果想为一个表创建分区，这个表最多只能有一个唯一索引（主键也是唯一索引）。如果没有唯一索引，可指定任何一列为分区列；否则就只能指定唯一索引中的任何一列为分区列。查询时需用到分区的列，不然会遍历所有的分区，比不分区的查询效率还低，MySQL支持子分区。 在表建立后也可以新增、删除、合并分区。

MySQL主从同步工作模式理解

复制机制（Replication） master通过复制机制，将master的写操作通过binlog传到slave生成中继日志(relaylog)，slave再将中继日志redo，使得主库和从库的数据保持同步。

slave主动拉取模式下复制相关的3个MySQL线程：

• slave上的I/O线程：向master请求数据；
• master上的Binlog Dump线程：读取binlog事件并把数据发送给slave的I/O线程；
• slave上的SQL线程：读取中继日志并执行，更新数据库；

相关监控命令

show processlist      # 查看MySQL进程信息，包括3个同步线程的当前状态
show master status    # 查看master配置及当前复制信息
show slave status     # 查看slave配置及当前复制信息

MySQL异步

MySQL异步是指将MySQL连接事件驱动化，这样就变成了非阻塞IO。数据库操作并不会阻塞进程，在MySQL-Server返回结果时再执行对应的逻辑。

注意点：

• 异步MySQL并没有节省SQL执行的时间
• 一个MySQL连接同时只能执行1个SQL，如果异步MySQL存在并发那么必须创建多个MySQL连接
• 异步回调程序中，异步MySQL并没有提升性能。异步最大的好处是可以高并发，如果并发1万个请求，那么就需要建立1万个MySQL连接，这会给MySQL-Server带来巨大的压力。

虽然应用层代码使用异步回调避免了自身的阻塞，实际上真正的瓶颈是数据库服务器。异步MySQL还带来了额外的编程复杂度，所以除非是特殊场景的需求，否则不建议使用异步MySQL。如果程序中坚持要使用异步，那么必须是异步MySQL+连接池的形式。超过规定的MySQL最大连接后，应当对SQL请求进行排队，而不是创建新连接，避免大量并发请求导致MySQL服务器崩溃。

MySQL索引类型

• 普通索引：最基本的索引，没有任何限制，MyISAM中默认的BTREE类型的索引，也是大多数情况下用到的索引。

// 直接创建索引
CREATE INDEX index_name ON table(column(length))

// 修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))

// 创建表的时候同时创建索引
CREATE TABLE `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT ,
  `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
  `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
  `time` int(10) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`),
  INDEX index_name (title(length))
)

// 删除索引
DROP INDEX index_name ON table

• 唯一索引 索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一，创建方法和普通索引类似。

// 创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))

// 修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

// 创建表的时候直接指定
CREATE TABLE `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT ,
  `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
  `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
  `time` int(10) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`),
  UNIQUE indexName (title(length))
);

• 全文索引 仅可用于MyISAM表。可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE或CREATE INDEX被添加。对于较大的数据集，将资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

// 创建表的适合添加全文索引
CREATE TABLE `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT ,
  `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
  `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
  `time` int(10) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`),
  FULLTEXT (content)
);

// 修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)

// 直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content)

• 单列索引、多列索引 多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

• 组合索引

  ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10));
  

  建立这样的组合索引，相当于分别建立了下面两组组合索引：

title,time
title

没有time这样的组合索引，因为MySQL组合索引执行最左前缀匹配，简单的理解就是只从最左面的开始组合。如：

SELECT * FROM article WHREE title='测试' AND time=1234567890;   # 使用title,time索引
SELECT * FROM article WHREE utitle='测试';                      # 使用title索引
SELECT * FROM article WHREE time=1234567890;                   # 未使用索引    

MySQL只对以下操作符才使用索引：

<、<=、=、>、>=、between、in、以及某些时候的like(不以通配符%或_开头的情形)。

理论上每张表里面最多可创建16个索引。

MySQL自增主键出现不连续情况的原因

造成自增主键不连续的原因

• INSERT语句不管是否成功，都会增加AUTO_INCREMENT值。
• 进行了DELETE相关操作。
• ROLLBACK相关操作。

修复自增主键，使其连续

INSERT INTO th_page2(site,url,title,title_index,content,tag,created_at,updated_at,deleted_at)
SELECT site,url,title,title_index,content,tag,created_at,updated_at,deleted_at FROM th_page ORDER BY tag;

DROP TABLE th_page;

ALTER TABLE th_page2 RENAME th_page;

ON与WHERE比较

执行连接操作时，可先用ON先进行过滤，减少连接操作的中间结果，然后用WHERE对连接产生的结果再一次过滤。但是，如果是左/右连接，在ON条件里对主表的过滤是无效的，仍然会用到主表的所有记录，连接产生的记录如果不满足主表的过滤条件那么从表部分的数据会置为NULL。

临时表

explain中出现Using temporary表示查询过程中需要创建临时表来存储中间数据（应该通过合理的索引来避免）。当临时表难以避免时，应该尽量减少临时表本身的开销。可以配置tmp_table_size来设置存储临时表的内存空间大小，一旦不够，MySQL将会启用磁盘来保存数据。

查询缓存

query_cache_size = 5678765456
query_cache_type = 1
query_cache_limit = 134567

MySQL的缓存过期策略是，当数据表有更新操作时，缓存即失效。

垂直分区与水平分区

• 垂直分区 对于数据库写操作频繁的站点，仅仅采用主从复制和读写分离效果并不好，因为从服务器将花费更多的时间用来同步数据，因此用来出来查询请求的时间就变少了，这时增加从服务器所获得的回报也将越来越小。

垂直分区：将不同业务的数据库（不需要JOIN查询）分不到不同的服务器上。

• 水平分区 水平分区：将同一个数据表中的记录通过特定的算法进行分离，分别保存在不同的数据表中，从而可以部署在不同的数据库服务器上。

比如原本有一个博客表，现在可以按照用户ID的奇偶性将所有用户划分为两部分，并分别存储到不同的数据库服务器上。当然在查询时需要额外传递用户ID参数用于进行奇偶性判断。

<?php
  // http://api.xxx.com/blog_post.php?post_id=123&user_id=321

  $db = new DataAccess($user_id);
  $db->selectDb("db_blog");
  $sql = "select * from tpl_posts where post_id=" . $post_id";
  $result = $db->query($sql);

分表

实际上在考虑水平分区之前一般会对数据库进行分表，比如按user_id%10将原来的表分为10个表。 分表属于单台数据库的优化策略，当已经实现了分表策略时，将可以更容易地实现水平分区，因为数据已经是分离的，只需要迁移到其他服务器。

分表算法和分区算法可能不一致（比如将分表得到的10个表分布在2台机器上），因此需要在应用程序中维护一份映射关系：

<?php 
  $db = new DataAccess($user_id);  // 分区逻辑
  $db->selectDb("db_blog");
  $tbl_name = getTblName($user_id); // 用户ID
  $sql = "select * from " .$tbl_name. " where post_id=" . $post_id";
  $result = $db->query($sql);
?>

显然用于分区的字段不能是auto_increment自增类型。

常见的分区算法有：

• 1.哈希，如user_id%10，这种方法不太友好，当扩展节点数时，所有数据需要重新分区；
• 2.范围，扩展性较好，但是会造成各个分区的工作量存在较大差异，比如老用户所在分区压力相对较大；
• 3.映射关系，将分区映射关系存到数据库中，当确定在哪个分区时，需要通过查询数据库（当然需要使用缓存）来获得答案；这种方案最可控；

MySQL逻辑架构

           客户端
 ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ 
+--------------------------+
-        连接/线程处理       -   // 连接处理、授权认证、安全
+--------------------------+
     ↓                ↓
+---------+      +---------+
- 查询缓存 -   ←  -  解析器  -   // 查询解析、优化、缓存、内置函数、存储过程、触发器、视图
+---------+      +---------+
                      ↓
+--------------------------+
-          优化器           -
+--------------------------+

+--------------------------+
-         存储引擎          -   // 数据的存储和提取
+--------------------------+

• 服务器通过API与存储引擎进行通信，这些API屏蔽了不同存储引擎之间的差异。存储引擎API包含几十个底层函数，不同存储引擎不会去解析SQL，而只是简单地响应上层服务器的请求。

• 每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个线程中执行，该线程只能轮流在某个CPU核心中运行。MySQL基于线程池来管理线程（创建、缓存、销毁）。

• 当客户端连接到MySQL服务器时，服务器会基于用户名、主机信息、密码等进行认证。一旦客户端连接成功，服务器会继续验证该客户端是否具有执行某个特定查询的权限。

• MySQL会解析查询并创建查询解析树，然后对其进行各种优化，如重写查询、决定表的读取顺序、选择合适的索引等。用户可以通过使用关键字提示优化器，从而影响它的决策过程。

• 优化器不关心表使用的是什么存储引擎，但存储引擎对于优化查询是有影响的：优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。

• 对于SELECT语句，在解析查询之前，服务器会先检查查询缓存，如果找到对应的查询，就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

• 在MySQL中，索引是在存储引擎层而不是服务器层实现的，并没有统一的索引标准，不同的存储引擎索引工作的方式是不一样的。

• MySQL无法利用多核特性来并行执行查询。

  ​

表锁与行级锁

• 表锁：会锁定整张表，在对表进行写操作之前，需要先获得写锁，获得写锁后将会阻塞其他用户对该表的读写操作。只有没有写锁时，其他用户才能获取读锁，读锁之间是不相互阻塞的。写锁比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面。 虽然不同的存储引擎都有自己的锁实现，MySQL自身仍然会在服务器层使用表锁并忽略存储引擎的锁机制，例如当执行ALTER TABLE时，服务器会使用表锁。

• 行级锁：行级锁只在存储引擎层实现，MySQL服务器层没有实现。 加锁本身也需要消耗资源，锁策略就是在锁的开销和安全性之间寻求平衡。每种MySQL存储引擎都可以实现自己的锁策略和锁粒度。

  ​

死锁

当多个事务试图以不同的顺序锁定资源时，就可能产生死锁。（两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环）对于事务型的系统，死锁发生后，只有部分或者完全回滚其中一个事务，才能打破死锁。InnoDB目前处理死锁的方式是：在检测到死锁循环依赖后，将持有最少行级排它锁的事务进行回滚。

事务日志可以帮助提高事务的效率

存储引擎在修改表的数据时只需要修改表数据的内存拷贝，同时把该修改行为持久化到硬盘中的事务日志中，相比于将修改的数据本身持久化到磁盘，事务日志采用的是追加的方式，因此是在磁盘上的一小块区域内顺序地写入，而不是随机的I/O操作。事务日志持久化后，内存中被修改的数据在后台可以慢慢刷回磁盘，如果在数据没有写回磁盘时系统崩溃了，存储引擎在重启时能够自动恢复这部分数据。目前大多数存储引擎都是这样实现的。

在事务中混合使用了事务型和非事务型的表

如果在事务中混合使用了事务型和非事务型的表，当事务需要回滚时，非事务型表上的变更将无法撤销，这将导致数据库处于不一致的状态。在非事务型表上执行事务相关操作时，MySQL通常并不会报错，只会给出一些警告。

​

AUTOCOMMIT

MySQL默认采用自动提交模式，如果不是显式地开始一个事务，则每个查询都会被当做一个事务执行提交操作。可以在当前连接中设置AUTOCOMMIT变量来禁用自动提交模式(禁用后，需要显式地执行COMMIT提交或者ROLLBACK回滚)。对于非事务型的表，修改AUTOCOMMIT不会有任何影响，这类表相当于一直处于AUTOCOMMIT启用的状态。 此外，有一些命令，例如ALTER TABLE，在执行之前会强制执行COMMIT提交当前的活动事务。

显式锁定

MySQL也支持LOCK TABLES和UNLOCK TABLES语句，这是在服务器层实现的，但它们不能代替事务处理，如果应用到事务，还是应该选择事务型存储引擎。 （建议：除了在事务中禁用了AUTOCOMMIT时可以使用LOCK TABLE之外，其他任何时候都不要显式地执行LOCK TABLES，不管使用的是什么存储引擎，因为LOCK TABLE和事务之间相互影响时，问题会变得非常复杂）

InnoDB也支持通过特定的语句进行显式锁定，这些语句不属于SQL规范，如：

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

多版本并发控制（ MVCC）

可以认为MVCC是行级锁的一个变种，但是它在很多情况下都避免了加锁操作，因此开销更低（更高并发）。其实现原理是通过保存数据在某个时间点的快照来实现的，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。 InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的，这两个列一个保存行的创建版本号，一个保存行的过期版本号，每开始一个新的事务，系统版本号就会自动递增。事务开始时刻的版本号会作为事务的版本号用来和查询到的每行记录的版本号进行比较。 MVCC只在REPEATABLE和READ COMMITED两个隔离级别下工作，其他两个隔离级别和MVCC不兼容。

剖析MySQL查询性能

• 可以通过设置long_query_time为0来捕获所有的查询，目前查询的相应时间单位已经精确到微秒级。慢查询日志是开销最低、精度最高的测量查询时间的工具。对CPU的开销很少，但是可能消耗大量的磁盘空间。
• 也可以通过tcpdump抓取TCP包，然后使用pt-query-digest –type=tcpdump选项来解析并分析查询。
• 剖析单条查询

  SET profiling = 1;
  SELECT * ...
  SHOW PROFILES;               # 所有查询的统计
  SHOW PROFILE FOR QUERY 1;    # 单条查询的详情，给出查询执行的每个步骤及花费的时间
  

  也可以不使用SHOW PROFILE命令，而是直接查询INFOMATION_SCHEMA中对应的表，这样可以自定义输出数据的格式（按特定字段排序等）。

可以使用SHOW STATUS命令返回查询计数器（但无法给出消耗了多少时间）。最有用的计数器是句柄计数器、临时文件、表计数器等。

FLUSH STATUS;
SELECT * FROM ...
SHOW STATUS WHERE Variable_name LIKE 'Handler%' OR Variable_name LIKE 'Created';

SHOW STATUS本身也会创建一个临时表，而且也会通过句柄操作访问此临时表，也会影响到SHOW STATUS结果中对应的数字。

MySQL数据类型选择的注意点

• 更小的通常更好；
• 简单就好：整型操作比字符型操作代价低；使用整型存储IP地址；
• 尽量避免NULL：可为NULL的列需要更多的存储空间，在索引优化时也更复杂（需要记录额外的字节）；（InnoDB使用单独的bit存储NULL值，这对于稀疏数据有很好的空间效率）
• TIMESTAMP只使用DATATIME一半的存储空间，且会根据时区自动更新，但是它允许的范围要小很多（1970年开始）。
• MySQL中的INTEGER、BOOL等只是基本类型的别名，如果用这些名字建表，SHOW CREATE TABLE仍然显示基本类型。
• MySQL可以为整数类型指定宽度，例如INT(11),但这实际并不会限制值的合法范围，作用只是规定了MySQL的一些交互工具用来显示字符的个数，对于存储和计算来说，INT(1)和INT(20)是相同的（都是11位）。其他整型数同理。
• TINYINT的存储空间为8位，存储值范围为-128~127，TINYINT UNSIGNED的存储范围是0~255；
• VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于等于255，则只使用1个字节，否则使用2个字节，所以一个VARCHAR(10)的列需要11个字节的存储空间，VARCHAR(1000)的列则需要1002个字节。
• 比较适合使用VARCHAR类型的情况：（1）字符串的最大长度比平均长度大很多；（2）列的更新很少；（更新导致长度变化，会有额外的更新操作）；（3）使用了像UTF-8这样的复杂字符集，每个字符使用不同的字节数进行存储；
• InnoDB可以把过长的VARCHAR存储为BLOB；
• MySQL把BLOB和TEXT值当做独立的对象处理，当其太大时，InnoDB会使用专门的“外部”存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值。

使用完全随机的字符串作为标识列会导致INSERT以及UPDATE语句变得很慢

原因可能有：

1. 插入值会随机地写到索引的不同位置，使得INSERT语句更慢，会导致页分裂，磁盘随机访问，产生聚簇索引碎片；
2. SELECT会更慢，因为逻辑上相邻的行会分布在磁盘和内存的不同地方；
3. 随机值导致缓存对所有类型的查询语句效果都很差；（局部性原理失效）

MySQL schema设计中的注意点

• 存储引擎API需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个操作的代价非常高，而转换的代价依赖于列的数量。

• 对于MySQL来说，EAV（实体-属性-值）设计模式是一个糟糕的设计模式，MySQL限制了每个关联操作最多只能有61张表。经验表明，单个查询最好在12个表以内做关联。

• 完全的范式化和完全的反范式化schema都是实验室里才有的东西，在实际应用中经常需要混用。

• 缓存表：存储那些可以比较简单地从schema其他表获取数据的表（存在逻辑上冗余的数据）；

• 汇总表：保存使用GROUP BY语句聚合数据的表；

• MySQL原生不支持物化视图，但是可以通过插件实现；

  ​

统计计数表设计

使用随机方式实现计数器表：如下的网站点击统计计数器表存在并发问题：

CREATE TABLE hit_counter(
  cnt int unsigned not null
) ENGINE=InnoDB;

当网站发生点击时：

UPDATE hit_counter SET cnt = cnt + 1;

要获得更高的并发更新性能，可以将数据器保存在多行中，每次随机选择一行进行更新，修改计数器表如下：

CREATE TABLE hit_counter(
  slot tinyint unsigned not null primary key,
  cnt int unsigned not null
)ENGINE=InnoDB;

当发生更新操作时，选择一个随机的行进行更新：

UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;

如下获取统计结果：

SELECT SUM(cnt) FROM hit_counter;

加快ALTER TABLE操作的速度

常规的方法是建另一张结构符合要求的表，并插入旧表的数据，然后切换主从，或者切换新旧表。 修改表的.frm文件是很快的，因此可以为想要创建的表结构创建一个新的.frm文件，然后用它替换掉已经存在的表的.frm文件。

在有索引的情况下快速导入表数据

常规的方法：

1. 先删除索引：如ALTER TABLE t.data DISABLE KEYS; # 对唯一索引无效

2. 导入数据

3. 再创建索引：如ALTER TABLE t.data ENABLE KEYS; hack方法是直接修改.MYD、.frm、.MYI等文件。

​

B-Tree索引

意味着所有的索引都是按顺序存储的，并且每一个叶子页到根的距离相同。 B-Tree索引可应用于如下类型的查询：

• 1.全值匹配：和索引中的所有列进行匹配；
• 2.匹配最左前缀：如当索引有多列时，只使用索引的第一列；
• 3.匹配列前缀：只使用索引第一列的值的开头部分；
• 4.匹配范围值：只使用索引的第一列；
• 5.精确匹配某一列，并范围匹配另一列：只使用索引的第一列及第二列的开头部分；
• 6.只访问索引的查询：查询只需要访问索引，而无须访问数据行，即覆盖索引；
• 7.可用于ORDER BY查询；

B-Tree索引的限制：

• 1.如果不是按照索引的最左列开始查找，则无法使用索引；

• 2.不能跳过索引中的列；

• 3.如果查询中有某个列的范围查询（包括LIKE等），则其右边所有列都无法使用索引优化查找；

  ​

哈希索引

基于哈希表实现，对于每一行数据存储引擎都会对所有的索引列计算一个哈希码，只有精确匹配索引所有列的查询才有效。 只有Memory引擎显式支持哈希索引。 InnoDB有一个特殊的功能：自适应哈希索引，当InnoDB注意到某些索引值被使用得非常频繁时，就会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。

空间数据索引（R-Tree）

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

全文索引

查找的是文本中的关键词，而不是直接比较索引中的值。适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。

索引提高性能的原因

• 1. 减少服务器需要扫描的数据量；
• 1. 避免排序和临时表；

• 1. 将随机I/O变为顺序I/O；

     ​

高性能的索引策略

• 1.如果查询中的列不是独立的，而是表达式的一部分，MySQL将不会使用索引；

  # 如下情况下不会使用索引
  SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
  

• 2.索引的选择性越高则查询的效率越高；（索引选择性 = 列取值基数/表记录总数）

  # 计算列的选择性
  SELECT COUNT(DISTINCT city)/COUNT(*) FROM sakila.city_demo;
  

• 3.可以使用前缀索引来替代对很长的字符列做索引；

# 计算不同前缀长度的索引的选择性
SELECT 
COUNT(DISTINCT LEFT(city,3))/COUNT(*) AS sel3,
COUNT(DISTINCT LEFT(city,4))/COUNT(*) AS sel4,
...
COUNT(DISTINCT LEFT(city,7))/COUNT(*) AS sel7,
FROM sakila.city_demo;

# 添加前缀索引
ALTER TABLE sakila.city_demo ADD KEY(city(7));

MySQL不能使用前缀索引做ORDER BY和GROUP BY查询，也无法使用前缀索引做覆盖扫描。

• 4.在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能：

（1）当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的多列索引； （2）当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要消耗大量的CPU和内存资源用在缓存、排序和合并操作上。而且优化器不会把这些计算到“查询成本”中，优化器只关心随机页面读取，这会导致查询的成本被低估，甚至该执行计划还不如直接走全表扫描。这样的查询通常通过改写成UNION查询来优化：

# 假设actor_id和film_id这两列上都有独立的索引
SELECT film_id,actor_id FROM sakila.film_actor
WHERE actor_id = 1 OR film_id = 1;

改写成：

SELECT film_id,actor_id FROM sakila.film_actor
WHERE actor_id = 1
UNION ALL
SELECT film_id,actor_id FROM sakila.film_actor
WHERE actor_id <> 1 AND film_id = 1;

• 5.多列索引的列顺序非常重要，在一个多列的B-Tree索引中，索引总是先按照最左列进行排序，其次是第二列，等等。 经验：一般的选择是将选择性最高的列放到索引最前列，但是避免随机IO和排序通常更为重要，所以要优先结合值的分布情况及频繁执行的查询场景来考虑。

  ​

聚簇索引的优缺点

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB的聚簇索引实际上是在同一个结构中保存了B-Tree索引（主键）和数据行。 聚簇表示数据行和键值紧凑地存储在一起，一个表只能有一个聚簇索引。 如果没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

聚簇索引的优点：

• 1.可以将相关数据保存在一起； 例如根据用户ID来聚集数据，这样只要从磁盘读取少数的数据页就能获取某个用户的全部记录，如果没有使用聚簇索引，则每条记录都可能导致一次磁盘I/O。
• 2.数据访问更快： 因为索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找更快。
• 3.使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点：

• 1.如果数据全部都放在内存中，则访问顺序就不那么重要了，聚簇索引也就没什么优势了；
• 2.插入速度严重依赖于插入顺序；如果不是按照主键顺序插入数据，在插入完成后最好执行OPTIMIZE TABLE重新组织表；
• 3.更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置；
• 4.当行的主键要求必须将一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，即造成“页分裂”，导致表占用更多的磁盘空间。
• 5.聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续时；
• 6.非聚簇索引会更大，因为在非聚簇索引的叶子节点中包含了引用行的主键列；
• 7.非聚簇索引访问需要两次索引查找，而不是一次；（非聚簇索引的叶子节点保存的不是指向行的物理位置的指针，而是行的主键值），对于InnoDB，自适应哈希索引能够减少这样的重复工作。

InnoDB顺序主键的高并发问题

在高并发情况下，在InnoDB中按主键顺序插入可能会导致明显的争用，当前主键的上界会成为“热点”，导致锁竞争。 此外AUTO_INCREMENT锁机制（表锁）也会导致竞争。可以通过修改innodb_autoinc_lock_mode来优化： innodb_autoinc_lock_mode = 0 (“traditional” lock mode：全部使用表锁) innodb_autoinc_lock_mode = 1 (默认)(“consecutive” lock mode：可预判行数时使用新方式，不可时使用表锁) innodb_autoinc_lock_mode = 2 (“interleaved” lock mode：全部使用新方式，不安全，不适合replication)

覆盖索引

覆盖索引：索引包含，或者说覆盖所有需要查询的字段的值。 MySQL只能使用B-Tree索引做覆盖索引。 使用了覆盖索引的查询，在EXPLAIN的Extra列可以看到Using index的信息。

使用索引进行排序的注意点

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能使用索引对结果做排序。 如果查询关联了多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。 有一种情况下ORDER BY子句可以在不满足索引的最左前缀要求的情况下使用索引进行排序：前导列在where中被设置为常量。

假设有如下索引：

PRIMARY KEY(rental_id),
UNIQUE KEY rental_date (rental_date,inventory_id,customer_id),
KEY idx_fk_inventory_id(inventory_id),
KEY idx_fk_customer_id(customer_id),
KEY idx_fk_staff_id(staff_id),

以下查询可以使用索引做排序：

# 因为索引前导列在WHERE中被设置为常量
...WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC;  

# ORDER BY使用的两列就是索引的最左前缀
...WHERE rental_date > '2005-05-25' ORDER BY rental_date,inventory_id;  

以下查询不能使用索引做排序：

# 排序方向不同
...WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC,customer_id ASC; 

# 使用了不在索引中的列
...WHERE rental_date = '2005-05-25' ORDER BY inventory_id,staff_id; 

# WHERE和ORDER BY中的列无法组合成索引的最左前缀
...WHERE rental_date = '2005-05-25' ORDER BY customer_id; 

# 在索引列的第一列上使用的是范围条件，所以无法使用索引的其余列
...WHERE rental_date > '2005-05-25' ORDER BY inventory_id,customer_id; 

# 在前置索引列上使用了范围查找
...WHERE rental_date = '2005-05-25' AND inventory_id IN(1,2) ORDER BY customer_id; 

索引和锁

InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。 Extra中的”Using where”表示在存储引擎层未能实现过滤，InnoDB检索到数据并返回服务器层以后，MySQL服务器才能应用WHERE子句，这种情况下会对这些不满足WHERE条件的数据行也加锁（因为这种情况下存储引擎的操作是从索引的开头开始获取满足条件的记录）。

即使使用了索引，InnoDB也可能锁住一些不需要的数据，如果不能使用索引查找和锁定行，MySQL会做全表扫描并锁住所有的行，而不管是不是需要。

执行：

SET AUTOCOMMIT = 0;
BEGIN;
SELECT actor_id FROM sakila.actor WHERE actor_id < 5 AND actor_id <> 1 FOR UPDATE;

如上查询语句在存储引擎层的操作是“从索引的开头开始获取满足条件actor_id < 5的记录”，此时再执行如下语句，将被挂起：

SET AUTOCOMMIT = 0;
BEGIN;
SELECT actor_id FROM sakila.actor WHERE actor_id = 1 FOR UPDATE;

InnoDB在二级索引上使用共享锁（读锁），但在访问主键时使用排它锁（写锁）。

维护索引和表

当碰到古怪的问题，比如不应该发生的主键冲突等等，可以通过CHECK TABLE来检查是否发生了表损坏。该命令通常能够找出大多数表和索引的错误。 可以执行REPAIR TABLE来修复损坏的表。 也可以通过一个不做任何数据操作的ALTER操作来重建表，以达到修复的目的：

ALTER TABLE innodb_tbl ENGINE=INNODB;

常见的导致查询过慢的原因

• 查询不需要的记录；如误以为MySQL会只返回需要的数据，实际上却是先返回全部结果集，再进行计算。
• 多表关联时返回全部列，如:

SELECT * FROM sakila.actor
  INNER JOIN sakila.film_actor USING(actor_id)
  INNER JOIN sakila.film USING(film_id)
  WHERE sakila.film.title = '***';

该查询将会返回三个关联表的全部数据列，正确的方式应该是只取需要的列：

SELECT sakila.actor.* FROM sakila.actor...;

• 总是取出全部列：SELECT * FROM …
• 重复查询相同的数据，而不是使用缓存；

MySQL查询使用WHERE条件时不同工作方式的性能问题

从好到坏依次为：

• 1. 在索引中使用WHERE条件来过滤不匹配的记录，这是在存储引擎层完成的；
• 1. 使用索引覆盖扫描（Using index）返回记录，直接从索引中过滤不需要的记录并返回命中的结果，在MySQL服务器层完成，无需再回表查询记录；

• 1. 从数据表中返回数据，然后过滤不满足条件的记录（Using Where），MySQL需要先从数据表读出记录然后过滤；

     ​

切分查询

将大查询切分成小查询，以避免一个大的语句一次锁住很多数据、占满整个事务日志，如：

DELETE FROM messages WHERE created < DATA_SUB(NOW(),INTERVAL 3 MONTH);

可以如下优化：

rows_affected = 0
do{
  rows_affected = do_query('DELETE FROM messages WHERE created < DATA_SUB(NOW(),INTERVAL 3 MONTH) LIMIT 10000')  // 一次删除10000行
}while rows_affected > 0

每次删除数据后都暂停一下再做下一次删除，这样可以将服务器上原本一次性的压力分散到一个很长的时间段中，从而避免长时间持有锁。

分解关联查询

当查询关联多个表时，可以对每一个表进行一次单表查询，然后在应用程序中对数据进行关联操作，如：

SELECT * FROM tag
  JOIN tag_post ON tag_post.tag_id = tag.id
  JOIN post ON tag_post.post_id = post.id
WHERE tag.tag='mysql'

可以分解为：

SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id in (123,456,789);

好处：

1. **方便缓存**；

2. 可以减少对锁的竞争；

3. 更容易对数据库进行拆分，更容易做到高性能和可扩展；

4. 能够使用IN替代关联查询，从而让MySQL按照ID顺序进行查询，这会比随机的关联更高效； ​

​

MySQL查询执行方式理解

MySQL客户端和服务器端的通信协议是半双工的，所以无法将一个消息分成小块来独立发送。一旦一端开始发送消息，另一端要接收完整消息才能响应它。 客户端使用一个单独的数据包将查询传给服务器，数据包的大小限制取决于max_allowed_packet配置。 服务器响应的数据一般由多个数据包组成，当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果，而不能简单地只取前面几条需要的结果。 大部分连接MySQL的库函数都可以获得全部结果集并缓存到内存里，也可以逐行获取需要的数据，MySQL通常要等所有数据都已经发送给客户端后才能释放连接。 如下PHP代码：

$link = mysql_connect('localhost','user','password');
$result = mysql_query('SELECT * FROM HUGE_TABLE',$link);
while($row = mysql_fetch_array($result)){
  // ...
}

看起来好像是只有当需要获取数据时才通过循环从服务器取出数据，实际上在调用mysql_query时PHP就已经将整个查询结果缓存到内存中，while循环只是从缓存中逐行取出数据。用mysql_unbuffered_query代替mysql_query则不会缓存结果。

MySQL查询的状态

每一个MySQL连接在任意时刻都有一个状态，用来表示MySQL当前正在做什么，可以通过SHOW FULL PROCESSLIST命令来查看状态。 具体的状态Sleep、Locked、Query等等，略。

MySQL查询语句优化处理

查询优化处理的作用是将SQL转换成一个执行计划，MySQL会按照这个执行计划和存储引擎进行交互，这个过程包含多个子阶段：

• 1.语法解析器和预处理：通过关键字解析SQL语句，生成一个解析树，并进行语法验证。预处理器则会检查数据表和列是否存在，并验证权限。
• 2.查询优化器：一条查询可以有多种执行方式，优化器的作用是找到这其中最好的执行计划。优化基于查询成本来进行成本估算，可以如下查看查询成本：

  SELECT SQL_NO_CACHE COUNT(*) FROM...;
  SHOW STATUS LIKE 'Last_query_cost';  // 返回值表示优化器认为查询需要随机查找的页数
  

  优化器的成本估算并不等于实际执行查询的成本，所以最终选择的不一定是最优执行计划。（比如优化器并不会考虑其他并发的查询）

MySQL能够执行的常见的优化包括：

• 1.重新定义关联表的顺序；
• 2.将外连接转化为内连接；
• 3.使用等价变换规则，例如5=5 AND a>5会被改写为a>5
• 4.优化COUNT()、MIN()、MAX()：如要找某一列的最小值，只要查询对应B-Tree索引最左端的记录
• 5.预估并转化为常数表达式：当检测到一个表达式可以转化为常数时，就会一直把该表达式作为常数进行优化处理;
• 6.覆盖索引扫描；
• 7.子查询优化：转换子查询的形式，减少多个查询多次对数据进行访问；
• 8.提前终止查询；如下查询在优化阶段就已经终止：

  SELECT film.film_id FROM sakila.film WHERE film_id = -1;
  

• 9.等值传播：如果两个列通过等式关联（USING），MySQL会把其中一个列的WHERE条件传递到另一个列上；

• 10.对IN的优化；在MySQL中，IN列表中的数据会先进行排序，然后通过二分查找法确定列表中的值是否满足条件；而不是像其他DB那样转换为OR查询。 可以通过执行EXPLAIN EXTENDED之后再执行SHOW WARNINGS来查看重构出的查询。

  ​

MySQL执行关联查询的方式

MySQL认为任何一个查询都是一次关联，包括单表查询，甚至子查询（读取临时表也是一次关联）。 当前MySQL执行关联查询的策略很简单：对任何关联查询都执行嵌套循环关联操作。 多表关联查询时，优化器会通过评估不同关联顺序的成本来选择一个代价最小的关联顺序。关联顺序的可能值是关联表数量的阶乘，优化器实际使用贪婪搜索方式查找最优关联顺序。

排序优化

当不能使用索引生成排序结果时，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，这两种情况在MySQL中都称为文件排序（filesort）。 具体排序算法略（分部分，快排）。

MySQL查询优化器的局限性

使用WHERE … IN(SELECT …)查询，性能会非常糟

SELECT * FROM sakila.film
  WHERE film_id IN ( SELECT film_id FROM sakila.film_actor WHERE actor_id = 1);

上面的查询并不会优化成如下的方式（以便利用IN()的高效列表查询）：

SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result:1,2,3,4,5...
SELECT * FROM sakila.film
  WHERE film_id IN ( 1,2,3,4,5... );

实际上MySQL会将外层表压到子查询中：

SELECT * FROM sakila.film
  WHERE EXISTS(SELECT * FROM sakila.film_actor 
                 WHERE actor_id = 1
                 AND film_actor.film_id = film.film_id);

MySQL会先对film表进行全表扫描（因为此时在子查询中用到了外层表的film_id字段），然后根据返回的film_id逐个执行子查询。如果外层的表是一个非常大的表，这个查询的性能将会非常糟糕。

可以如下改写：

SELECT film.* FROM sakila.film
  INNER JOIN sakila.film_actor USING(film_id)
WHERE actor_id = 1;

MySQL不支持松散索引扫描

即无法按照不连续的方式扫描一个索引，即使需要的数据是索引某个片段中的少数几个，MySQL仍然要扫描这个片段中的每一个条目。 5.6以后的版本，关于松散索引扫描的一些限制会通过索引条件下推的方式解决。

优化COUNT()查询

如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数（不含NULL）。 COUNT(*)并不会扩展成所有列，而是直接统计所有的行数。 MyISAM的COUNT()函数非常快（通过存储引擎直接获得值）有一个前提条件，即不能有任何WHERE条件。

在同一个查询中统计同一个列的不同值的数量

SELECT 
SUM(IF(color = 'blue', 1, 0)) AS blue,
SUM(IF(color = 'red', 1, 0)) AS red
FROM items;

或者

SELECT 
COUNT(color = 'blue' OR NULL) AS blue,
COUNT(color = 'red' OR NULL) AS red
FROM items;

优化关联查询

• 1.确保ON或者USING子句中的列上有索引；

• 2.一般只需要在关联顺序中的第二个表的相应列上创建索引；

• 3.确保GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程； 尽可能使用关联查询代替子查询。

  ​

优化GROUP BY和DISTINCT

MySQL使用同样的方式优化这两种查询，在内部处理的时候相互转化这两类查询。它们都可以使用索引来优化。 当无法使用索引时，GROUP BY使用两种策略来完成：临时表或者文件排序。 通常使用查找表的标识列分组的效率会比其他列更高。 尽可能将WITH ROLLUP功能转移到应用程序中处理。

优化LIMIT分页

对于偏移量非常大的查询应该尽可能地使用索引覆盖扫描进行优化：

SELECT film_id,descriptionn FROM sakila.film ORDER BY title LIMIT 50000,5;

上面的语句将查询50000条记录，然后只返回最后5条。最好改写成下面形式：

SELECT film.film_id, film.descriptionn 
FROM sakila.film
  INNER JOIN(
    SELECT film_id FROM sakila.film
    ORDER BY title LIMIT 50000,5
) AS lim USING(film_id);

也可以在应用程序中记录上次查询位置，或者计算值的上下边界（值需要连续）来优化。

优化UNION查询

MySQL总是通过创建并填充临时表的方式来执行UNION查询。一般会将WHERE、LIMIT、ORDER BY等子句下放到UNION的各个子查询中以便优化器可以充分利用这些条件进行优化。

使用自定义变量

SET @last_week := CURRENT_DATE - INTERVAL 1 WEEK;
SELECT ... WHERE col <= @last_week;

使用自定义变量的查询无法使用查询缓存； 不能在使用常量或者标识符的地方使用自定义变量，例如表名、列名、LIMIT子句； 自定义变量的生命周期是一次连接（使用连接池或者持久化连接就会有坑）； 自定义变量是动态类型的；

分区表

分区表是一个独立的逻辑表，但是底层由多个物理子表组成（意味着可以分布在不同的物理设备上）。对分区表的请求都会通过句柄对象转化成对存储引擎的接口调用，对应用来说是透明的。 MySQL中索引也是按照分区的子表定义的，而没有全局索引。

一个表最多只能有1024个分区。 如果分区表字段中有主键或者唯一索引的列，那么所有的主键列和唯一索引列都必须包含进来。 分区表中无法使用外键约束。

分区表上操作的逻辑：

• SELECT：分区层先打开并锁住所有的底层表，优化器先判断是否可以过滤部分分区，然后再调用对应的存储引擎接口访问各个分区的数据；
• INSERT：分区层先打开并锁住所有的底层表，然后确定哪个分区接收这条记录，再将记录写入对应底层表；
• DELETE：分区层先打开并锁住所有的底层表，然后确定数据对应的分区，最后对相应底层表进行删除操作；
• UPDATE：分区层先打开并锁住所有的底层表，先确定需要更新的记录在哪个分区，然后取出数据并更新，再判断更新后的数据应该放在哪个分区，最后对底层表进行写入操作，并对原数据所在的底层表进行删除操作。

虽然每个操作都会先打开并锁住所有的底层表，但这并不意味着分区表在处理过程中是锁住全表的，如果存储引擎能够实现行级锁，则会在分区层释放对应表锁。

分区表达式要返回一个整数：

CREATE TABLE sales(
  order_date DATETIME NOT NULL,
  ...
)ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date))(
  PARTITION p_2010 VALUES LESS THAN(2010),
  PARTITION p_2011 VALUES LESS THAN(2011),
  PARTITION p_2012 VALUES LESS THAN(2012),
  PARTITION p_catchall VALUES LESS MAXVALUE
);

在数据量超大时，B-Tree索引就无法起作用了，除非是索引覆盖查询，否则需要根据索引扫描的结果回表查询所有符合条件的记录，如果数据量巨大，将产生大量随机I/O，从而导致数据库的响应时间大到不可接受的程度。

分区可能遇到的问题：

1. NULL值会使分区过滤无效； 在旧版本中可以创建一个“无用”的第一个分区来处理特殊值的情况。5.5以后的版本可以直接基于列进行分区：

   PARTITION BY RANGE COLUMNS(order_date);
   

2. 分区列和索引列不匹配；
3. 选择分区的成本可能很高；
4. 打开并锁住所有底层表的成本可能很高；
5. 维护分区的成本可能很高；
6. 所有分区都必须使用相同的存储引擎；
7. 某些存储引擎不支持分区；

对于访问分区表，很重要的一点是要在WHERE条件中带入分区列（有时看似多余也要带上），这样就可以让优化器能够过滤掉无须访问的分区。假如没有这些条件，MySQL就需要让对应存储引擎访问这个表的所有分区。

可以通过EXPLAIN查看查询是否使用了分区过滤：

EXPLAIN PARTITIONS SELECT * FROM sales_by_day WHERE day > '2011-01-01';

如果结果中partitions部分没有展示所有的分区，说明有分区过滤。 虽然在创建分区时可以使用表达式，但在查询时只能根据列来过滤分区。

合并表

合并表是一种早期的、简单的分区实现，和分区表相比有一些不同的限制，并且缺乏优化。 合并表允许用户单独访问各个子表。 是一种将被淘汰的技术。

视图

MySQL中视图有两种实现方式：合并算法、临时表算法。 如果视图中包含GROUP BY、DISTINCT、聚合函数、UNION、子查询等，只要无法在原表记录和视图记录中建立一一映射，都将使用临时表算法来实现视图（未来版本中可能改变）。

可以通过EXPLAIN查看视图的实现方式：

EXPLAIN SELECT * FROM <VIEW>;

如果结果中select_type为DERIVED，则是采用临时表算法实现的。

也可以在创建视图的时候指定算法：

CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM sakila.actor;

可更新视图：指可以通过更新这个视图来更新视图涉及的相关表（更新、删除、写入）。被更新的列必须来自同一个表中。

所有使用临时表算法实现的视图都无法被更新。

绑定变量的优势及限制

优势

• 1.在服务器端只需要解析一次SQL语句；
• 2.某些优化器的工作只需要执行一次，因为它会缓存一部分执行计划；
• 3.以二进制的方式只发送参数和句柄比每次都发送ASCII码文本效率更高。而且可以分块传输。
• 4.更安全；

限制

• 1.绑定变量是会话级别的；
• 2.如果只执行一次SQL，那么使用绑定变量开销更大；
• 3.绑定变量SQL总数的限制是一个全局限制；

字符集和字符校对

字符集是一种从二进制编码到某类字符符号的映射，校对是一组用于某个字符集的排序规则。 每种字符集都可能有多种校对规则，并且都有一个默认的校对规则。 只有基于字符的值才有字符集的概念，对于其他类型的值，字符集只是一个设置，指定用哪一种字符集来做比较或其他操作。 MySQL服务器、每个数据库、每个表都有自己的字符集默认值。最靠底层的设置将影响最终创建的对象。

当服务器和客户端通信时，它们可能使用不同的字符集，这时服务器端将进行必要的翻译转换工作：

1. 服务器端总是假设客户端按照character_set_client设置的字符来传输数据和SQL语句；
2. 当服务器收到客户端的SQL语句时，先将其转换成字符集character_set_connection，也会使用这个设置来决定如何将数据转换成字符串；
3. 当服务器端返回数据或错误信息给客户端时，会将其转换成character_set_result； 即：

   客户端 -->SQL
     --> 从character_set_client转换成character_set_connection
    --> 处理SQL语句
      --> 从character_set_connection转换成character_set_result
        --> 查询结果
   

   可以通过SET NAMES或SET CHARACTER SET来改变设置。

MySQL配置的工作原理

MySQL从命令行参数或者配置文件中获取配置信息。服务器会读取配置文件的内容，删除所有注释和换行，然后和命令行选项一起处理。

在不同的操作系统上MySQL配置文件的位置不同，可以使用如下方式查看当前使用的配置文件的路径：

$ which mysqld
/usr/local/bin/mysqld

$ /usr/local/bin/mysqld --verbose --help | grep -A 1 'Default options'
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf

MySQL配置文件中下划线和横线等价。

不同的配置项有不同的作用域（服务器、会话、对象）。除了在配置文件中设置变量，有很多变量也可以在服务器运行时修改（MySQL关闭时可能会丢失）。

如果在服务器运行时修改了变量的全局值，这个值对当前会话和其他任何已经存在的会话都不起作用。

不同配置项可能有不同的单位值。

在配置文件中不能使用表达式。

可以为配置项赋予默认值DEFAULT，这将使配置项使用上一级的配置。

常用变量： key_buffer_size：为键缓冲区分配指定空间（使用时才会分配）； table_cache_size：可以缓存的表的数量，当线程打开新表时会检查，如果已满，则删除不常使用的表； thread_cache_size：缓存的线程数； query_cache_size：用来缓存查询； read_buffer_size； read_rnd_buffer_size； sort_buffer_size;

应该始终通过监控来确认生产环境中变量的修改（基准测试是不够的，不是真实的工作负载）。

不要根据比率来调优，比如如果键缓存的命中率应该高于某个百分比，如果命中率过低，则应该增加缓存的大小。这是非常错误的意见。缓存命中率跟缓存是否过大或过小没有关系。

没有适合所有场景的最佳配置文件。

配置文件示例

[mysqld]
datadir                  = /var/lib/mysql
socket                   = /var/lib/mysql/mysql.sock
pid_file                 = /var/lib/mysql/mysql.pid
user                     = mysql
port                     = 3306
default_storage_engine   = InnoDB

# InnoDB
innodb_buffer_pool_size  = <value>  # 缓存行、自适应哈希索引、插入缓存、锁等
innodb_log_file_size     = <value>
innodb_file_per_table    = 1
innodb_flush_method      = 0_DIRECT
 
# MyISAM
key_buffer_size          = <value>

# Logging
log_error                = /var/lib/mysql/mysql-error.log
slow_query_log           = /var/lib/mysql/mysql-slow.log

# Other
tmp_table_size           = 32M
max_heap_table_size      = 32M
query_cache_type         = 0
query_cache_size         = 0
max_connections          = <value>
thread_cache             = <value>
table_cache              = <value>
open_files_limit         = 65535

[client]
socket                   = /var/lib/mysql/mysql.sock
port                     = 3306

很大的缓冲池会有一些问题，例如预热和关闭（涉及脏数据写回）都会花费很长的时间。

MySQL复制机制的工作过程

一台主库的数据可以同步到多台备库上，备库本身也可以被配置成另外一台服务器的主库。（通过在主库上记录二进制日志，在备库上重放日志的方式来实现异步的数据复制） 复制通常不会增加主库的开销，主要是启用二进制日志带来的开销。每个备库也会对主库增加一些负载，尤其是备库请求从主库读取旧的二进制日志文件时。

总的来说复制有三个步骤：

1. 在主库上把数据更改记录到二进制日志中；
2. 备库将主库上的日志复制到自己的中继日志中；
3. 备库读取中继日志的事件，将其重放到备库数据上；

即，主库上的数据更改会记录到二进制日志中，而备库上的I/O线程会不断地去请求主库的二进制日志，并把结果记录到备库上的中继日志中。然后备库上的SQL线程会不断地读中继日志并回放。

5.0以前只支持基于语句的复制（逻辑复制），备库实际是把主库上执行过的SQL再执行一遍。

EXPLAIN

EXPLAIN命令用于查看查询优化器选择的查询计划。被标记了EXPLAIN的查询会返回关于执行计划中每一步的信息，而不是执行它。(实际上，如果查询在from子句中包括子查询，那么MySQL实际上会执行子查询) EXPLAIN只是一个近似结果。

EXPLAIN的结果

• id 标识SELECT所属的行，如果在语句中没有子查询或者联合查询，那么只会有一行（因为只有1个SELECT），且id值为1.

• select_type
  1. SIMPLE 意味着该查询不包含子查询和UNION，如果查询有任何复杂的子部分，则最外层部分标记为PRIMARY（id为1的查询）。
  2. SUBQUERY 包含在SELECT列表中的子查询（即不是位于FROM子句中的查询）；
  3. DERIVED 包含在FROM子句中的子查询；
  4. UNION 在UNION查询中的第二个和随后的SELECT被标记为UNION；
  5. UNION RESULT 用来从UNION的匿名临时表检索结果的SELECT；

• table 表示正在访问哪个表（包括匿名临时表，比如derived1），可以在这一列从上往下观察MySQL的关联优化器为查询选择的关联顺序。

• type 访问类型，决定如何查找表中的行，从最差到最优排列如下：
  1. ALL 全表扫描；
  2. index 按索引次序全表扫描，主要优点是避免了排序，缺点是当随机访问时开销非常大；如果在Extra列中有Using index，说明MySQL正在使用覆盖索引，即只扫描索引的数据。
  3. range 有限制的索引扫描（带有between或where >等条件的查询）； 注意，当使用IN()、OR()来查询时，虽然也显示范围扫描，但是其实是相当不同的访问类型，在性能上有重要的差异。
  4. ref 索引查找，返回所有匹配某个值的行，这个值可能是一个常数或者来自多表查询前一个表里的结果值；
  5. eq_ref 也是索引查找，且MySQL知道最多只返回一条符合条件的记录（使用主键或者唯一性索引查询），MySQL对于这类访问优化的非常好；
  6. const、system 当MySQL能对查询的某部分进行优化并将其转换为一个常量时，它就会使用这些访问类型；
  7. NULL 意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引；

• possible_keys 显示查询可以使用哪些索引。

• key 显示MySQL决定采用哪个索引来优化对表的访问。

• key_len 显示MySQL在索引里使用的字节数。

• ref 显示之前的表在key列记录的索引中查找值所用的列或常量。

• rows MySQL估计为了找到所需的行而要读取的行数。

• filtered 在使用EXPLAIN EXTENED时才会出现，查询结果记录数占总记录数的百分比。

• Extra

• 1.Using index：表示将使用覆盖索引；

• 2.Using where：意味着MySQL服务器将在存储引擎检索后再进行过滤；

• 3.Using temporary：意味着MySQL在对查询结果排序时会使用一个临时表；

• 4.Using filesort：意味着MySQL会对结果使用一个外部索引排序，而不是按索引次序从表里读取行；

• 5.Range checked for each record …：意味着没有好用的索引；

  ​

匹配单个字符

SELECT prod_id, prod_name FROM products WHERE prod_name LIKE ‘_ ton anvil’; 注意：把通配符置于搜索模式的开始处，搜索起来是最慢的。

LIKE与REGEXP的区别

LIKE匹配整个列，而REGEXP则在列值内进行匹配。 比如有一个记录的prod_name为“JetPack 1000”，则使用REGEXP ‘1000’，将能匹配到该记录，而使用LIKE ‘1000’将匹配不到。 MySQL中的正则表达式不区分大小写，即大小写都会匹配。为了区分大小写，可使用BINARY关键字，如：

WHERE prod_name REGEXP BINGARY 'JetPack .000'。

进行发音类似的匹配

SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y Lie');

子查询

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
                    FROM orderitems
                    WHERE prod_id = 'TNT2');

子查询总是从内向外处理，对于能嵌套的子查询的数目没有限制。

返回最后一个AUTO_INCREMENT的值

SELECT last_insert_id();

mysql_real_escape_string和mysql_escape_string有什么本质的区别，有什么用处，为什么被弃用？

mysql_real_escape_string需要预先连接数据库，并可在第二个参数传入数据库连接（不填则使用上一个连接） 两者都是对数据库插入数据进行转义，但是mysql_real_escape_string转义时，会考虑数据库连接的字符集。 它们的用处都是用来能让数据正常插入到数据库中，并防止sql注入，但是并不能做到100%防止sql注入。

再问：为什么不能100%防止？ 因为客户端编码以及服务器端编码不同，可能产生注入问题，但是其实这种场景不多见。 继续答：被弃用的原因是官方不再建议使用mysql_xx的数据库操作方式，建议使用pdo和mysqli，因为不管从性能跟安全来看，mysqli都比mysql要好。

<=>操作符的用法及其与=操作符的区别

• 和=号的相同点 像常规的=运算符一样，两个值进行比较，结果是0（不等于）或1（相等），即：’A’<=>’B’得0和’a’<=>’a’得1。

• 和=号的不同点 ‘a’ <=> NULL得0，NULL<=>NULL得1。和=运算符正相反，=号运算符规则是 ‘a’=NULL 结果是NULL 甚至NULL = NULL 结果也是NULL。 总之，当进行相等比较的值中可能存在NULL值时，<=>的可移植性更好。

  ​

skip-grant-tables

mysql启动参数 –skip-grant-tables，在启动mysql时不启动grant-tables授权表，用于忘记管理员密码后登陆并重置密码：

• 杀掉原来进行着的mysql：

  service mysqld stop
  

• 以命令行参数启动mysql：

  /usr/bin/mysqld_safe --skip-grant-tables &
  

• 修改管理员密码：

  use mysql;
  update user set password=password('yournewpasswordhere') where user='root';
  flush privileges;
  exit;
  

• 杀死mysql，重启mysql

  ​

记录锁、间隙锁、Next-key锁

innodb的记录锁有三种类型：

• 记录锁：是加在索引记录上的。
• 间隙锁：对索引记录间的范围加锁，或者加在最后一个索引记录的前面或者后面
• Next-key锁：记录锁和间隙锁的组合，间隙锁锁定记录锁之前的范围 间隙锁主要是防止幻读，用在Repeated-Read（简称RR）隔离级别下。在Read-Commited（简称RC）下，一般没有间隙锁（有外键情况下例外，此处不考虑）。间隙锁还用于statement based replication 间隙锁有些副作用，如果要关闭，一是将会话隔离级别改到RC下，或者开启 innodb_locks_unsafe_for_binlog（默认是OFF）。 间隙锁只会出现在辅助索引上，唯一索引和主键索引是没有间隙锁。间隙锁（无论是S还是X）只会阻塞insert操作。

为什么不使用MySQL分区表

MySQL常见的水平切分方式是分库分表或者分区表。

• 分库分表：把一个很大的库（表）的数据分到几个库（表）中，每个库（表）的结构都相同，但他们可能分布在不同的mysql实例，甚至不同的物理机器上，以达到降低单库（表）数据量，提高访问性能的目的。 分库分表往往是业务层实施的，分库分表后，为了满足某些特定业务功能，往往需要rd修改代码。
• 分区表：所有数据还在一个表中，但物理存储根据一定的规则放在不同的文件中。这个是mysql支持的功能，业务rd代码无需改动。

不使用分区表而使用分库分表的原因

• 分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁

• 一旦数据量并发量上来，如果在分区表实施关联，就是一个灾难

• 自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控

  ​

MySQL半同步复制

从MySQL5.5开始，MySQL以插件的形式支持半同步复制。 主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。