Mysql笔记(-第五章)

December 13, 2017 本文总阅读量次 26410

前记

之前对数据库的使用都是写入，取出。最近想弄个数据库却涉及到太多东西去百度也百度不到自己想要的，然后在别人推荐下就看了《高性能Mysql》（第三版的书出版时5.6还没出来），所以这个文章是《高性能Mysql》读书笔记。由于太多了，所以我看多少就记多少。。。

伪前记(mysql的命令)

看书前温习了下命令，就顺便记下来了

mysql术语

  • 关系型数据库
    • 能够实现一种具有表、列与索引的数据库。
    • 保证不同表的行之间的引用完整性。
    • 能自动更新索引。
    • 能解释 SQL 查询，组合多张表的信息。
  • 数据库（Database）：数据库是带有相关数据的表的集合。
  • 表（Table）：表是带有数据的矩阵。数据库中的表就像一种简单的电子表格。
  • 列（Column）：每一列（数据元素）都包含着同种类型的数据，比如邮编。
  • 行（Row）：行（又被称为元组、项或记录）是一组相关数据，比如有关订阅量的数据。
  • 冗余（Redundancy）：存储两次数据，以便使系统更快速。
  • 主键（Primary Key）：主键是唯一的。同一张表中不允许出现同样两个键值。一个键值只对应着一行。
  • 外键（Foreign Key）：用于连接两张表。
  • 复合键（Compound Key）：复合键（又称组合键）是一种由多列组成的键，因为一列并不足以确定唯一性。
  • 索引（Index）：它在数据库中的作用就像书后的索引一样。
  • 引用完整性（Referential Integrity）：用来确保外键一直指向已存在的一行。

    创建命令

    创建test数据库
    1
    CREATE DATABASE employee;
    链接employee数据库
    1
    use employee;
    创建表
    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE TABLE 表的名字
    (
    列名a 数据类型(数据长度),
    列名b 数据类型(数据长度)，
    列名c 数据类型(数据长度)
    );

    #示例
    CREATE TABLE employee (id int(10),name char(20),phone int(12));
    向表插入数据
    1
    2
    3
    4
    5
    6
    7
    INSERT INTO 表的名字(列名a,列名b,列名c) VALUES(值1,值2,值3);
    #示例
    INSERT INTO employee(id,name,phone) VALUES(01,'Tom',110110110);

    INSERT INTO employee VALUES(02,'Jack',119119119);

    INSERT INTO employee(id,name) VALUES(03,'Rose');

    约束

约束类型 主键 默认值 唯一 外键 非空
关键字 PRIMARY KEY DEFAULT UNIQUE FOREIGN KEY NOT NULL 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE TABLE department
(
  dpt_name   CHAR(20) NOT NULL,              //非空约束
  people_num INT(10) DEFAULT '10',           //默认值约束
  CONSTRAINT dpt_pk PRIMARY KEY (dpt_name)   //主键约束
//CONSTRAINT 可自定义主键名 dpt_pk == dpt_name 均作为该表主键
);

CREATE TABLE employee
(
  id      INT(10) PRIMARY KEY,                     //主键约束 id 作为该表主键
  name    CHAR(20), 
  age     INT(10),
  salary  INT(10) NOT NULL,                        //非空约束
  phone   INT(12) NOT NULL,                        //非空约束
  in_dpt  CHAR(20) NOT NULL,                      //非空约束
  UNIQUE  (phone),                                //唯一值约束,phone值在该表中唯一
  CONSTRAINT emp_fk FOREIGN KEY (in_dpt) REFERENCES department(dpt_name)
//外键约束 in_dpt，并自定义外键名 emp_fk 参考department表 dpt_name 列 
//使用INSERT语句时，该表in_dpt列值必须在department表dpt_name中存在
);
 
CREATE TABLE project
(
  proj_num   INT(10) NOT NULL,
  proj_name  CHAR(20) NOT NULL,
  start_date DATE NOT NULL,
  end_date   DATE DEFAULT '2015-04-01', //默认约束
  of_dpt     CHAR(20) REFERENCES department(dpt_name), //外键约束
  CONSTRAINT proj_pk PRIMARY KEY (proj_num,proj_name)
   //复合主键约束(proj_num,proj_name)，并自定义主键名proj_pk
 );

查询命令

基本SELECT：

1
SELECT 要查询的列名 FROM 表名字 WHERE 限制条件;

数字符号条件限定，如(其中数字符号可以有:数学符号 (=,<,>,>=,<=)

1
SELECT name,age FROM employee WHERE age>25;

非数字条件限定

1
2
3
4
5
6
7
8
9
10
11
12
#小于25或大于30
SELECT name,age FROM employee WHERE age<25 OR age>30;
#大于25且小于30
SELECT name,age FROM employee WHERE age>25 AND age<30;
#该列数据的所有dpt3和dpt4
SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt IN ('dpt3','dpt4');
#该列数据的除了dpt1和dpt3
SELECT name,age,phone,in_dpt FROM employee WHERE in_dpt NOT IN ('dpt1','dpt3');
#适配类似的数据_代表一个未知符，如果是两个就两个_
SELECT name,age,phone FROM employee WHERE phone LIKE '1101__';
#适配类似的数据%代表不定个未知符
SELECT name,age,phone FROM employee WHERE name LIKE 'J%';

对结果排序

1
2
SELECT name,age,salary,phone FROM employee ORDER BY salary ASC/DESC;
#其中ASC为升序，DESC为将序

内置函数和计算

函数名 COUNT SUM AVG MAX MIN
作用 计数 求和 求平均值 求最大

其中 COUNT 函数可用于任何数据类型(因为它只是计数)，而 SUM 、AVG 函数都只能对数字类数据类型做计算，MAX 和 MIN 可用于数值、字符串或是日期时间数据类型。

1
2
SELECT MAX(salary) AS max_salary,MIN(salary) FROM employee;
#从employee取出最大和最小值，AS是把最大值呈现在列名max_salary中(可以说AS是改了列名)

关联表查询
显示of_dpt和count_project，而of_dpt存放在project的关联表employee里面

1
2
3
SELECT of_dpt,COUNT(proj_name) AS count_project FROM project
WHERE of_dpt IN
(SELECT in_dpt FROM employee WHERE name='Tom');

连接查询
各员工所在部门的人数，其中员工的 id 和 name 来自 employee 表，people_num 来自 department 表

1
2
3
4
SELECT id,name,people_num
FROM employee,department
WHERE employee.in_dpt = department.dpt_name
ORDER BY id;

也可以写为

1
2
3
4
SELECT id,name,people_num
FROM employee JOIN department
ON employee.in_dpt = department.dpt_name
ORDER BY id;

修改和删除

删除

1
2
DROP DATABASE test_01; #库
DROP TABLE test_01;  #表

修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#重命名表名字
RENAME TABLE 原名 TO 新名字;
ALTER TABLE 原名 RENAME 新名;
ALTER TABLE 原名 RENAME TO 新名;
#增加一列
ALTER TABLE 表名字 ADD COLUMN 列名字 数据类型 约束;
或： ALTER TABLE 表名字 ADD 列名字 数据类型 约束;
#增加的列在指定位置后面
ALTER TABLE employee ADD test INT(10) DEFAULT 11 AFTER test;
#增加的列在第一个位置
ALTER TABLE employee ADD test INT(10) DEFAULT 11 FIRST;
#删除列
ALTER TABLE 表名字 DROP COLUMN 列名字;
或： ALTER TABLE 表名字 DROP 列名字;
#重改列
ALTER TABLE 表名字 CHANGE 原列名 新列名 数据类型 约束;
#更新列的值
UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;
#删除
DELETE FROM 表名字 WHERE 条件;

12月21日更

MySQL架构与基础

MySQL架构

MySQL架构
正方形内是MySQL的核心服务功能层，包括查询解析，分析，优化，缓存以及所有与的内置函数(日期，时间，数学和加密函数等)，所有跨存储功能都在这一层实现：存储过程，触发器，视图等。
存储引擎。负责MySQL中数据的存储和提取。每个存储引擎都有它的优势和劣势。服务器通过API与存储引擎进行通信。这些接口屏蔽了不同的存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎API包括几十个底层函数，用于执行诸如“开始一个事务，或者：“根据主键提取一行记录”等操作，但存储引擎不会取解析SQL（InnoDB除外，它会解析外键定义）。不同存储引擎之间也不会相互通信

MySQL锁策略

为了并发读取数据时造出数据损坏，数据库也加了锁。理想是锁定方式是，只对会修改的数据片进行精确的锁定，任何时候，在给定的资源上，锁定的数据了越小，则系统的并发程度越高。
读锁 由于可以多个用户读取一个文件，所以读锁是共享的，相互不阻塞。多个客户在同一时刻可以同时读取一个资源，而互不干扰。

写锁 写锁是排他的，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源

表锁 表锁是MySQL中最基本的锁策略，并且是开销最小的策略。它会锁定整张表，也就是一个用户在对表进行写操作前，需要先获得写锁，这时会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获得读锁，读锁之间是不互相阻塞的。
之中，写锁比读锁有更高优先级，因此一个写锁请求可能会被插入到读锁队列的前面（写锁可以插入到队列中读锁前面，反之毒素哦哦则不能）
尽管存储引擎可以管理自己的锁，MySQL本身还是会使用各种有效的表锁来实现不用的目的。例如，服务器会为诸如ALTER TABLE之类的语句使用表锁，而忽略存储引擎的锁机制。

行级锁 行级锁可以最大程度的支持并发处理（同时也带来最大的锁开销）。
行级锁只在存储引擎层实现。

事务

事务就是一组原子性的SQL查询（原子性:不可切分的最小单位）或者说一个独立的工作单元。如果数据库引擎能够成功的对数据库应用该组查询的全部语句，就执行该组查询，如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有语句都不会执行。

栗子：
假设银行的数据库有两张表：支票表和储蓄表。现在要从用户jane的支票账户转移200美元到她的账户

  1. 检查支票账户的余额高于200美元。
  2. 从支票账户余额中减去200美元
  3. 在存蓄账户余额中增加200美元
    这三个操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。

可以用START TRANSACTION语句开始一个事务，然后要么使用commit提交事务将修改的数据持久保留，要么使用ROLLBACK撤销所有的修改。事务的样本如下:

1
2
3
4
5
START TRANSACTION;
SELECT balance FROM checking WHERE customer_id = 10233276;
UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
COMMIT；

为了事务能完好的执行，事务拥有ACID特性，ACID表示原子性，一致性，隔离性，持久性。

  • 原子性：一个人事务必须被视为一个不可分割的最小工作单元，整个十五中的所有操作要么成功要么失败回滚，对于一个事务来说，不可能只执行其中一个操作
  • 一致性：数据库总是从一个一致性的状态切换到另外一个一致性的状态。
  • 隔离性：通常来说(有隔离等级)一个事务所做的修改在最终提交以前，对其他事务是不可见的
  • 持久性：一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。持久性是个有点模糊的概念，因为实际上持久性也分很多不同的级别。

由于ACID特性也会增加系统开销，增加了安全性，需要数据库系统做更多的额外工作。所以要根据业务来选择是否使用带有事务处理的存储引擎，即使存储引擎不支持事务，也可以通过LOCK TABLES 语句为应用提供一定程度的保护。

隔离级别

隔离级别 脏读可能性 不可重复读可能性 幻读可能性 加锁读
未提交读 yes yes yes no
提交读 no yes yes no
可重复读 no no yes no
可串行化 no no no yes

ps脏读:事务中的修改，即使没有提交，对其他事务也都是可以见的，事务可以读取未提交的数据。

死锁

死锁是值两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务视图以不同的顺序锁定资源时，就可能产生死锁。
栗子：
下面两个事务同时处理StockPrice表：
事务1

1
2
3
START TRANSACTION；
UPDATE StockPrice SET close = 45.50 WHERE stock_id =4 and date = '2002-05-01'
UPDATE StockPrice SET close = 19.80 WHERE stock_id =3 and date = '2002-05-02'

事务2

1
2
3
START TRANSACTION；
UPDATE StockPrice SET hight = 20.12 WHERE stock_id = 3 and date = '2002-05-02'
UPDATE StockPrice SET hight = 47.20 WHERE stock_id = 4 and date = '2002-05-01'

如果凑齐，两个事务都执行了第一条update语句，更新一行数据，同时也锁定了该行数据，接着每个事务都尝试取执行第二条update语句，却发现该行已被对方锁定，然后两个事务都等待对方释放锁，同时又持有对方需要的锁，则陷入死循环。除非有外部因素介入才能解除死锁。
为了防止死锁，数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统，越能检测死锁的循环依赖，并立即返回一个错误，保证不会出现由于死锁出现了非常慢的查询。
还有一种解决方式，就是查询时间达到锁等待超时的设定后放弃锁请求。
InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。
锁的行为和顺序是存储引起相关的。以同样的顺序执行语句，有些存储引擎会产生死锁，有些则不会。死锁产生有双重原因：有些是因为真正的数据冲突，这种情况通常很难避免，但有些则完全是由于存储引擎的实现方式导致的。死多发生后，只有部分或者完全回滚其中一个事务，才能打破死锁，对于事务性的系统，这是无法避免的。

事务日志

使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上以小块区域内的顺序I/o需要在磁盘的多个地方移动磁头，所以采用事务日志的港式相对来说要快的多。事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大所属存储引擎都是这样实现的，我们通常称为预写式日志，修改数据需要写两次磁盘。

MYSQL中的事务

MySQL提供了两种事务型的引擎:INnoDB\NDB Cluster。

MySQL默认采用自动提交模式。也就是每个查询都被当做一个事务执行提交操作。可以通过设置变量来启用或禁用，1为启用，0为禁用。

1
2
SHOW VARIABLES LIKE 'AUTOCOMMIT'； 
SET AUTOCOMMIT = 1;

修改autocommit对非事务性的表，比如myisam或者内存吧，不会有任何影响。另外还有一些命令，在执行之前会强制执行commit提交当前的活动事务，典型的例子，在数据定义语言中，如果是会导致大量数据改变的操作，比如ALTER TABLE,就是如此，另外还有LOCK TABLES等其他语句也会导致同样的结果。

不要在事务中混合使用存储引擎

事务是由下层的存储引擎实现的。所以在同一个事务中，使用多种存储引擎是不可靠的。
如果在十五中混合使用了事务型和非事务型的表，在正常提交的情况下不会有什么问题。
但如果该事务需要回滚，非事务型的变更就无法撤销，这回导致数据库处于不一致的状态，这种情况很难修复，事务的最终结果将无法确定。所以，为每张表选择合适的存储引擎非常重要。

多版本并发控制(MVCC)

基于提升并发性能的考虑，数据库一般都实现了多版本并发控制，可以认为MVCC是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。不同的存储引擎的MVCC实现是不同的，典型的有乐观和悲观并发控制。
MVCCD实现是通过保存数据在某个时间点的快照来实现的。也就是说，不管需要执行多长时间，每个事务看到的数据都是已知的。根据书屋开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的(以空间换取时间？？？)

InnoDB的MVCC是通过每行记录后面保存两个隐藏的列来实现的，这两个列，一个保存了行的创建时间，一个保存行的过期时间或删除时间（这里应该说是系统版本号)。每开始一个新的事务，系统版本号会自动递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较

  • SELECT
    InnoDB会根据以下两个条件检查每行记录:

    • InnoDB只查找版本早于当前事务版本的数据行，也就是，行的系统版本号小鱼或等于事务的系统版本号，这样可以确保事务读取的行，要么是在事务开始之前已经存在，要么是事务自身插入或者修改过的。
    • 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
      只有符合上述两个条件的记录，才能返回作为查询结果

  • INSERT
    InnoDB为新插入的每一行保存当前系统版本号作为行版本号

  • DELETE
    InnoDB为删除的每一行保存当前系统版本号作为删除标识

  • UPDATE
    InnoDB为插入每一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

    保存这两个额外系统版本号，使大多数读操作都可以不用加锁，这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。但是mvcc只能在‘未提交读’，‘提交读’两个隔离级别下工作。

MySQL基准测试

为什么需要基准测试

基准测试可以完成一下工作：

  • 验证基于系统的一些假设，确认这些假设是否符合实际情况。

  • 重现系统中的某些异常行为，以解决这些异常。

  • 测试系统当前的运行情况。如果不清楚系统当前的性能，就无法确认某些优化的效果如何。也可以利用历史的基准测试结果来分析诊断一些无法预测的问题。

  • 模拟比当前系统更高的负载，以找出系统随着压力增加而可能遇到的扩展行瓶颈。

  • 规划未来的业务增长。基准测试可以评估在项目未来的负载下，需要什么样的硬件，需要多大容量的网络，以及其他相关资源。这由于主降低系统升级和重大变更的风险。

  • 测试应用适应可变环境的能力。

  • 测试不同的硬件、软件和操作系统配置。看哪个更适合。

  • 证明新采购的设备是否配置正确。

    基准测试的策略

    基准测试有两种主要的策略:一是针对整个系统的整体测试，另外是单独测试MySQL。分别被称为集成式以及单组件式基准测试。针对整个系统做集成式测试，而不是单独测试MySQL的原因主要有以下几点:

  • 测试整个应用系统，包括web服务器、应用代码、网络和数据库是非常有用的，因为用户关注的并不止是MySQL本身的性能，而是应用整体的性能。

  • MySQL并非总是应用的瓶颈，通过整体的测试可以揭示这一点。

  • 只有对应用做整体测试，才能发现各部分之间的缓存带来的影响。

  • 整体应用的集成式测试更能揭示应用的真实表现而单独组件的测试很难做到这一点。

    有时候单独测试MySQL的原因：

  • 需要比较不同的schema或查询的性能

  • 针对应用中某个具体的问题的测试。

  • 为了避免漫长的基准测试，可以通过一个短期的基准测试，做快速的”舟求循环“，来检测出某些调整的效果

测试的指标

  • 吞吐量
    吞吐量指的是单位时间内的事务处理数。非常适用于多用户的交互式应用。常用的测试单位是每秒实物数(TPS),有些也采用每分钟事务数(TPM)
  • 响应时间或者延迟
    这个指标用于测试任务所需的整体时间。根据具体的应用，测试的时间单位可能是微妙，毫秒，秒，分钟。根据不同的时间单位可以计算出平均响应时间、最小响应时间、最大响应时间和所占百分比，最大响应时间通常意义不大，因为测试时间越长，最大响应时间也可能越大。而且其结果通常不可重复，每次是都可能得到不同的最大响应时间。因此进行多次测试，再取测试后的响应时间的聚集范围。
    例如：如果95%的响应时间都是5毫秒，则表示任务在95%的时间段内都可以在5毫秒之内完成。
  • 并发性
    WEB服务器的并发性更准确的度量指标应该是在任意时间有多少同时发生的并发请求，但是web服务器的并发会导致数据库的高并发（需要用到数据库的话，且有很多个MySQL数据库服务器查询链接）。
    换句话说，并发性基准测试需要关注的是正在工作中的并发操作，或者是同时工作中的线程数或者连接数。当并发性增加是，需要测量吞吐量是否下降，响应时间是否边长。
    数据库的并发性测量，可以通过sysbench指定32、64或者128个线程测试，然后在测试期间记录MySQL数据库的threads_running状态值。
  • 可拓展性
    可扩展性指的是，给系统增加一倍的工作，在理想情况下就能获得两倍的结果(即吞吐量增加一倍)或者说给系统增加一倍的资源，就可以获得两倍的吞吐量（理想情况下）。

基准测试方法

在测试时，避免以下错误来避免错误导致测试结果无用或者不精确：

  • 使用真实数据的子集而不是全集。
  • 例如应用需要处理几百gb的数据，但测试只有1gb数据，或者只使用当前数据进行测试，却希望模拟未来业务大幅度增长后的情况。
  • 使用错误的数据分布，例如使用均匀分布的数据测试，而系统的真实数据有很多热点区域(随机生产的测试数据通常无法模拟真实的数据分布)
  • 使用不真实的分布参数，例如假定所有用户的个人信息都会被平均地读取
  • 在多用户场景中，只做单用户的测试
  • 在单服务器测试分布式应用
  • 与真实用户行为不匹配。
  • 例如真实用户在请求一个页面后会阅读一段时间，而不是不停顿地一个接一个点击相关链接
  • 反复执行同一个查询，真实的查询是不尽相同的，这可能会导致缓存命中率降低，而反复执行同一个查询在某种程度上，会全部或者部分缓存结果。
  • 没有检查错误。
    果测试的结果无法得到合理的解释，比如一个本应该很慢的查询突然变快了，就应该检查是否有错误产生，否则可能只是测试了MySQL检测语法错误的产生。否则可能只是测试了MySQL检测语法错误的速度。所以基准测试完成后，一定要检查以下错误日志。
  • 忽略了系统预热的过程
    例如系统重启后马上测试，有时候需要了解系统重启后需要多长时间才能达到正常的性能容量，要特别留意预热的时长。反过来说，如果要想分析正常的性能，需要注意，若基准测试在重启以后马上启动，则缓存是冷的、还没有数据，这时即使测试的压力相同，得到的结果也和缓存已经装满数据时的不同的
  • 使用默认的服务器配置
  • 测试时间太短。基准测试需要持续一定的时间。

测试工具（待补充）

略

服务器性能剖析

性能优化简介

性能的定义为完成某件任务所需的时间度量。
注:

  • 性能优化并不是指降低cpu利用率
    虽然出bug会提高cpu利用率，但是同一时间内，能消耗更多的性能去加快查询速度，这是提升了性能。

通过性能剖析进行优化

步骤:测量任务所花费的时间，然后对结果进行统计和排序，将重要的任务排到前面
性能剖析有两种：

  • 基于执行时间的分析
    研究的是什么任务的执行时间最长
  • 基于等待的分析
    判断任务在什么地方被阻塞的时间最长

理解性能剖析

MySQL的性能剖析将最重要的任务展示在前面，但有时候没显示出来的信息也很重要。但是还是有很多需要的信息是缺失的。

  • 不值得优化的查询
    一些只占总响应时间比重很小的查询是不值得优化的。
    如果优化的成本大于收益，就应停止优化
  • 异常情况
    有些任务即使没有出现在性能剖析输出的前面也需要优化。比如某些任务执行次数很少，但每次执行都非常慢，严重影响用户体验，因为其执行频率低，所以总的响应时间占比并不突出。
  • 未知的未知
    一款好的性能剖析工具会显示可能的”丢失的时间“。丢失的时间指的是任务的总时间和实际测量到的时间之间的差。例如，如果处理器的cpu时间是10秒，而剖析到的任务总时间是9.7秒，那么就有300好秒的丢失时间。这可能是有些任务没有测量到，也可能是由于测量的误差和精度问题的缘故
  • 掩藏的细节
    有些时候不能相信平均值，例如有两次查询响应的时间是1秒。另外的所有响应时间缺失几十微妙，这时候平均值是无法发现的，但是这两次响应值可能代表一些会发生的异常

剖析MySQL查询

剖析服务器负载

捕获MySQL的查询到日志文件中

MySQL的慢查询日志的功能（最初只是捕获比较”慢“的查询）。现在可以通过设置long_query_time为0来捕获所有的查询。
慢查询日志开销是最低的、精度最高的测量查询时间的工具（一般不会带来额外的i/o开销，cpu密集型场景i\o开销的影响会较大），但是慢查询可能消耗大量的磁盘空间
Percona Server的慢查询日志比官方的记录了更多细节且有价值的信息，如查询执行计划、锁、i/o活动等
有时因为权限不够的原因，无法在服务器上记录查询。可以使用两种替代的技术。

  • 通过– processlist选项不断查看SHOW FULL PROCESSLIST的输出，记录查询第一次出现的时间和消失的时间。但是一些执行较快的查询可能在两次执行的间隙就执行完成了，从而无法捕获到。
  • 通过tcp网络包，根据MySQL的客户端/服务端通信协议进行解析。可以先通过tcpdump将网络包数据保存到磁盘，然后使用pt-query-digest的–type=tcpdump选项来解析并分析查询，此方法的精度比较高，并且可以捕获所有查询，还可以解析更高级的协议特性，如二进制协议。
分析查询日志

虽然生成了日志，但是打开整个慢查询日志进行分析很浪费时间。所以:
首先应该生成一个剖析报告，如果需要，则可以查看日志中需要特别关注的部分。生产剖析
报告的工具——pt-query-digest，可以将查询报告保存到数据库中，以及追踪工作负载时间的变化。
详细介绍在书和链接

剖析单挑语句

定位到需要优化的单条查询后，就可以针对这个查询捉取更多的信息，确认为什么会花那么多时间取执行，以及如何取优化。以下是如何方便的测量查询执行的各部分花费了多少时间

  • 使用SHOW PROFILE
    该命令默认是禁用的（我现在用的时候好像默认启用- -？），但可以通过服务器变量在会话（连接）级别动态地修改

    1
    mysql> SET profiling = 1;

    然后在服务器执行语句时，该工具都会测量其耗费的时间和其他一些查询执行状态变更相关的数据显示如：997rows in set (0.17sec)
    输入命令：

    1
    mysql>SHOW PROFILES;

    会返回一张表，记录了时间和所输入的命令

    1
    mysql> SHOW PROFILE FOR QUERY 1;

    命令可以剖析报告给出了查询执行的每个步骤及其花费的时间，但是输出是按照执行顺序排序的，而不是按花费了多少时间….也不嫩用ORDER BY之类的命令重新排序。
    也可以按如下查询
    enter description here
    这样就可以找到消耗时间的排行了，再去接着优化。

  • 使用SHOW STATUS
    该命令会返回一些计数器，既有服务器级别的全局计数器，也有基于某个链接的会话级别的计数器。
    如果执行SHOW GLOBAL STATUS则可以查看服务器级别的从服务器启动时开始计算的查询次数统计。
    由于返回的结果只是计数器，可以显示某些活动如读索引的频繁程度，无法给出消耗了多少时间。但可以根据计数器来猜测那些操作待机较高或者消耗时间较多

  • 使用慢查询日志
    查看慢查询日志是，其标题部分一般有如下输出:
    Query 1: 0 QPS, 0x concurrency, ID oxEE758C5EoD7EADEE at byte 3214 ___
    表明可以通过这里的字节偏移值(3214)直接跳转到日志的对应部分，如以下命令：

    1
    tail -c +3214 /path/to/query.log | head -n100

  • 使用Performance Schema
    待查找5.6有无新特性

诊断间歇性问题

列出的案例:

  • 应用通过curl从一个运行得很慢的外部服务来获取汇率报价的数据
  • memcached缓存中的一些重要条目过去，导致大量请求落到mysql以重新生产缓存条目。
  • DNS查询偶尔会有超时现象
  • 可能是由于互斥锁争用，或者内部删除查询缓存的算法效率太低的缘故，MySQL的查询缓存有时候会导致服务有短暂的停顿。
  • 当并发度超过某个阀值时，InnoDC的扩展性限制导致查询计划的优化需要很长的时间。

单条查询问题还是服务器问题

  • 使用SHOW GLOBAL STATUS
    这个方法实际上就是以较高的频率如一秒一次执行命令捕获数据，问题出现时，通过计数器（如:Threads_running,Threads_connected,Questions和Queries）的”尖刺“或”凹陷“来发现。这方法简单且对服务器影响小。
    enter description here
    这个命令是每秒捕获一次SHOW GLOBAL STATUS的数据，且输出给awk计算并输出每秒的:Threads_running,Threads_connected,Questions和Queries这几个数据的趋势对于服务器级别偶尔停顿的敏感性高。
  • 使用SHOW PROCESSLIST
    这个方法通过不停的捕获show processlist的输出，观察是否有大量线程处于不正常的状态或者有其他不正常的特征。可以使用以下命令查询(sort,uniq,sort计算某个列值出现的次数):
    1
    mysql -e 'SHOW PROCESSLIST\G' |grep State: |sort |uniq -c |sort -rn

    Schema与数据类型优化

    选择优化的数据类型

    存储类型的选择
  • 更小的通常更好
    一般情况下，应尽量吃用可以正确存储数据的最小数据类型。如果无法确定哪个数据类型是最好的，就选择认为不会超过方位的最小类型。
  • 简单就好
    可以使用int就别用float，且使用MySQL内建的类型来存储日期和时间。（存储ip地址用int）
  • 尽量避免使用null
    通常情况下，最好指定列为NOT NULL,除非真的需要存储Null值。不过在InnoDB时影响不大

整数类型

如果存储整数，可以使用这几种整数类型:TINYINT,SMALLINT.MEDIUMINT,INT,BIGINT,分别使用8,16,24,32,64位存储空间。
整数类型有可选的的UNSIGNED属性，表示不允许负值。
在TINYINTZHONGZHONG，不使用UNSIGNED属性:存储范围-128128
使用UNSIGNED属性:存储范围0255
此外，MySQL可以为整数类型指定宽度，例如INT(11)，对大多数应用是没意义的:它不会限制值的合法范围，只是规定一些MySQL交互工具用来显示字符的个数。

实数类型

有浮点和DECIMAL，浮点有FLOAT和DOUBLE，其中FLOAT占用4个字节存储，DOUBLE占用8个字节且相比FLOAT有更大的范围和更高的精度。
因为需要额外的空间和计算开销，所以尽量只在对小数进行精确计算时才使用DECIMAL。例如存储财务数据。但在数据了比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以响应的倍数即可。这样可以避免浮点存储计算不精确和DECIMAL精确计算代价高的问题

字符串类型

  • VARCHAR(InnoDB/MYISAM引擎下的说明)
    存储可变长字符串，比CHAR节省空间，但是，如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储。
    VARCHAR需要使用额外字节记录字符长度，如果列的最大长度小于或等于255，则使用1个字节，否则使用2个字节。
    适用VARCHAR的情况：

    • 字符串列的最大长度比平均长度大很多
    • 列的更新很少
    • 使用了像UTF-8这样复杂的字符串（每个字符串都使用不同的字节数进行存储）
      在存储IP地址时，不应该使用VARCHAR(15)来存储，因为它本身是32位的二进制。MySQL提供了INET_ATON()和INET_NTOA()在这两种表示方法之间转换

  • CHAR(InnoDB/MYISAM引擎下的说明)
    CHAR类型是定长的，MySQL总是根据定义的字符串长度分配足够的空间，当存储CHAR值是，MySQL会删除所有的末尾空格。
    使用CHAR的情况

    • 适合存储所有制都接近同一个长度（如MD5）
    • 经常变更的数据（定长的CHAR不容易产生碎片）
    • 非常短的列如(Y/N)

  • BLOB和TEXT类型
    BLOB和TEXT都是为了很大的数据而设计的字符串数据类型，分别采用存储二进制和字符串方式存储

  • 使用枚举代替字符串类型

  • 枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或两个字节中。在内部会将每个值在列表中的未知保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的”查找表”。
    栗子：

    1
    2
    3
    4
    mysql> CREATE TABLE enum_test(
        ->    e ENUM('fish','apple','dog')NOT NULL
        ->);
    mysql> INSERT INTO enum_test(e) VALUES('fish'),('dog'),('apple');

    其中存储顺序是按照

    1
    mysql> INSERT INTO enum_test(e) VALUES('fish'),('dog'),('apple');

    进行排序的，而数字是按照

    1
    e ENUM('fish','apple','dog')NOT NULL

    绑定的
    可以输入

    1
    mysql>SELECT e + 0 FROM enum_test;

    查看对应的数字，而

    1
    mysql> SELECE e FROM enum_test ORDER BY e;

    可以查看按照e排序的表。

    枚举不好的地方是，字符串列表是固定的，天界或删除字符串必须使用ALTER TABLE。枚举把每个枚举值保存为整数，并且必须进行查找才能转换为字符串，所以枚举列有一些开销，所以枚举的列表要比较小。

日期和时间类型

MySQL可以使用许多类型来保存日期和是兼职，如year和date.MySQL能存储的最小时间粒度为秒。但是可以使用微妙级别的粒度进行临时运算，且提供了DATETIME和TIMESTAMP两种日期类型

  • DATETIME
    保存的范围为：1001～9999年，精度为秒，格式为YYYYMMDDHHMMSS,与时区无关，使用8个字节的存储空间

  • TIMESTAMP
    同unix时间戳保存了1979/1/1以来的秒数（如果还是32位的话，就到2038年）。只使用4个字节存储空间。
    FROM_UNIXTIME()把unix时间戳转换为日期
    UNIX_TIMSTAMP()把日期转换为unix时间戳
    如果在插入时没有指定第一个TIMESTAMP列的值，MySQL则设置这个列的值为当前时间。在插入一行记录是，MySQL默认也会更新第一个TIMESTAMP列的值(除非在UPDATE语句中明确指定了值。)

    位数据类型

  • BIT
    可以使用BIT列在一列中存储一个或多个true/false值，BIT列的最大长度是64个位。
    MySQL把BIT当做字符串类型，而不是数字类型。当检索是，结果是一个包含二进制的字符串而不是ASCII码。

  • SET
    如果需要保存很多true/false值，可以考虑合并这些列到一个SET数据类型，它在MySQL内部是以一系列打包的位的集合来表示的。有效利用了存储空间。不过，它的主要缺点是该表列的定义的代价比较高，需要ALTER TABLE。

MySQL schema设计中的陷阱

  • 太多的列
    MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的，而且转换的代价依赖于列的数据。所以可以拆分为多个表，再关联起来。
  • 太多的关联
    如果希望查询执行的快速且并发性好，单个查询最好在12个表以内做关联
  • 全能的枚举
    在MySQL中，当需要在枚举中增加一个新的数据时。就需要做一次ALTER TABLE操作。

范式和反范式

在范式化的数据库中，每个事实数据会出现并且只出现一次，相反，在反范式化的数据库中，信息是冗余的。栗子：

EMPLOYEE DEPARTMENT HEAD
Jones Accounting Jones
Smith Engineering Smith
Browm Accounting Jones
Green Engineering Smith

假如Say Browm接任Accounting部门的领导。需要修改多行数据来反映这个变化。
在没有雇员信息情况下就无法表示一个部门。如果我们删除了所有Accounting部门的雇员，我们就失去了关于这个部门本身的所有记录。所以我们需要对这个表进行范式化，拆分雇员和部门项。
拆分后的表如下:
雇员表:
| EMPLOYEE | DEPARTMENT |
| ——– | ———– |
| Jones | Accounting |
| Smith | Engineering |
| Browm | Accounting |
| Green | Engineering |
部门表:
| DEPARTMENT | HEAD |
| ———– | —– |
| Accounting | Jones |
| Engineering | Smith |

范式的优点和缺点

优点:

  • 范式化的更新操作通常比反范式化要快
  • 当数据较好地范式化时，就只有很少或者有没重复数据，所以只需要修改更少的数据。
  • 范式化的表通常更小，可以更好地放在内存里，所以操作会更快。
  • 很少有多余的数据意味这检错列表数据时更少需要DISTINCT或者GROUP BY才能获得一份唯一的部门表。
    缺点:通常需要关联，稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。

缓存表和汇总表

提升性能最好的方法除了在同一张表中保存衍生的冗余数据外，也可以通过创建一张完全独立的汇总表或缓存表。
缓存表:表示存储那些可以比较简单地从schema其他表获取(但每次获取的速度比较慢)的数据表（如逻辑冗余的数据）。
汇总表:保存的是使用GROUP BY 语句聚合数据的表。

汇总表栗子：如果一个网站需要计算之前24小时内发送的消息数，可以这样设计:
以每小时汇总表为基础，把前23个完整的小时的统计表中的计数全部加起来，最后再加上开始阶段和结束阶段不完整的小时内的计数。

而缓存表则不一样，对优化搜索和检索查询语句很有效。这些查询语句经常需要特殊的表和索引结构。栗子:如果需要很多不同的索引组合来加速各种类型的查询时，可以创建一张只包含主表中部分列的缓存表。这时可以使用不同的存储引擎，例如主表使用InnoDB ，缓存表用MyISAM将会得到更小的索引空间。

在重建表时，可以考虑通过使用”影子表”来实现，也就是创建一个新表，填充数据后与真实表做切换。栗子，原始表为my_summary
命令:

1
2
3
4
mysql> DROP TABLE IF EXISTS my_summary_new,my_summary_old;
mysql> CREATE TABLE my_summary_new LIKE my_summary;
-- populate my_summary_new as desired
mysql> RENAME TABLE my_summary TO my_summary_old, my_summary_new TO  my_summary;

计数器表

一般设计的计数器表只有一行数据，对于事务来说，这条记录上都有一个全局的互斥锁，会使得事务只能串行执行，如果要获得更高的并发更新性能，也可以将计数器保存在多行中，每次随机选择一行更新，要获得结果时，则使用聚合查询。
创建命令：

1
2
3
4
mysql> CREAT TABLE hit_counter(
    ->    slot tinyint unsigned not null primary key,
    ->    cnt int unsigned not null
    ->)   ENGINE=InnoDB;

更新命令(创建100行):

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

获得结果:

1
mysql> SELECT SUM(cnt) FROM hit_counter;

如果希望减少表的行数，可以写一个周期执行的任务，合并所有结果到0号槽并删除其他槽:

1
2
3
4
5
6
7
8
9
mysql> UPDATE daily_hit_counter as c
    ->    INNER JOIN(
    ->        SELECT day,SUM(cnt) AS cnt,MIN(slot) AS mslot
    ->        FROM daily_hit_counter
    ->        GROUP BY day
    ->    ) AS xUSING(day)
    -> SET c.cnt = IF(c.slot = x.mslot, x.cnt,0), 
    ->     c.slot = IF(c.slot = x.mslot,0,c/slot);
mysql> DELETE FROM daily_hit_counter WHERE slot <> 0AND cnt = 0;

加快ALTER TABLE 操作的速度

一般来说ALTER TABLE都是重建表，操作的性能都比较差，特别是大表
但有也有例外，例如，有两种方法可以改变或删除一个列的默认值(一种方法很快，一种很慢)。假如要修改电影的默认租赁限期，从三天改到五天，很慢的方式:

1
2
mysql> ALTER TABLE sakila.film
    -> MODIFT COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5;

该方式拷贝了整张表到一张新表，甚至列的类型、大小和可否为NULL属性都没改变。

快的方式:

1
2
mysql> ALTER TABLE sakila.film
    -> ALTER COLUMN rental_duration SET DEFAULT 5;

该方式会直接修改.frm文件而不涉及表数据。所以这个操作会很快。

只修改.frm文件

如果是这些操作则可能不需要重建:

  • 移除(不是增加)一个列的AUTO_INCREMENT属性
  • 增加、移除、或更改ENUM和SET常量。如果移除的是已经有行数据用到其值的常量，查询将会返回一个空字符串值

修改方法(为表结构创建一个新的.frm文件，然后用它替换掉已经存在的那张表.frm文件):

  1. 创建一张有相同结构的空表，并进行所需的修改(如增加enum常量)
  2. 执行FLUSH TABLES WITH READ LOCK。这将会关闭所有正在使用的表，并且禁止任何表被打开。
  3. 交换.frm文件
  4. 执行UNLOCK TABLES 来释放第二步的读锁

快速创建MyISAM索引

为了高效地载入数据到MyISAM表中，有一个常用的技巧是先禁用索引，载入数据，然后再启用索引。这样之所以能加快的原因是构建索引的工作被延迟到数据完全载入后，这个时候已经可以通过排序来构建索引了。
操作步骤:

  1. 用需要的表结构创建一张表，但是不包括索引
  2. 载入数据到表中以构建.MYD文件
  3. 按照需要的结构创建另外一张空表，这次要包含索引，这回创建需要的.frm和.MYI文件
  4. 获取读锁并刷新
  5. 重命名第二张表的.frm和.MYI文件，让MySQL认为是一张表的文件
  6. 释放读锁
  7. 使用REPAIR TABLE来重建表的索引。该操作会通过排序来构建所有索引，包括唯一索引

创建高性能的索引

索引基础

索引原理栗子:

1
mysql> SELECT first_name FROM sakila.actor WHERE actor_id = 5;

如果在actor_id列上建有索引，则MySQL将使用该索引找到actor_id为5的行，也就是，MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。

索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列，创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的

索引类型

在MySQL中，索引是在存储引擎层而不是服务器层实现的，所以，并没有同一的索引标准，不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。

  • B-Tree索引
    所有值都是按顺序存储的，并且每一个叶子页到根的距离相同。
    innoDB的B-tree结构
    存储引擎根据索引的根节点开始进行搜索。
    B-Tree索引的查询类型适用于全键值、键值范围或键前缀查找。其中键前缀查找只使用于根据最左前缀的查找。前面所述的索引对如下类型的查询有效:

    • 全值匹配
      全值匹配指的是和索引中的所有列进行匹配，例如可用于查找姓名为CUba Allen、出生与1960-01-01的人

    • 匹配最左前缀
      可用于查找所有姓为Allen的人，即只使用索引的第一列

    • 匹配列前缀
      匹配某一列的值的开头部分，如查找所有以J开头的姓的人

    • 匹配范围值
      例如查找姓位于Allen和Barrymore之间的人

    • 精确匹配某一列并范围匹配另外一列
      如查找所有姓为Allen且名字是字母K开头的人

    • 只访问索引的查询
      查询只需要访问索引而无需访问数据行

      B-Tree索引的限制

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

    • 不能跳过索引中的列

    • 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。
      如“WHERE last_name=’Smith’ AND first_name LIKe ‘J%’ AND dob = ‘1976-12-23’”这个查询只能使用索引的前两列，由于like是范围条件。

  • 哈希索引
    哈希索引基于哈希表实现，只有精确到匹配索引所有列的查询才有效。对于每一行数据，存储引擎会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样，哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
    在MySQL中只有Memory引擎显示支持哈希索引(注:经查阅，NDB也支持，而InnoDB支持的方式略不同，不能人为干与，只能自动创建)
    由于索引本身只需存储对应的哈希值所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快，不过哈希索引也有它的限制:

    • 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
    • 哈希索引数据并不是按照索引值顺序存储的，所以就无法用于排序
    • 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
    • 哈希索引只支持等值比较查询，=、IN()、<=>
    • 访问哈希索引的数据非常快，除非有很多哈希冲突。当出现冲突时，存储引擎必须遍历链表中所有的行指针，逐步进行比较，直到找到所有符合条件的行

关于innoDB的哈希索引:当innoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，但这是一个完全自动的、内部的行为，用户无法控制或配置，但可以完全关闭该功能

- 创建自定义哈希索引
如果引擎不支持哈希索引，则可以模拟像InnoDB一样创建哈希索引。
思路就是在用B-Tree查找时，它使用哈希值而不是键本身进行索引查找，需要做的就是在查询的WHERE子句中手动指定使用哈希函数。栗子（使用B-Tree存储URL）：

1
mysql> SELECT id FROM url WHERE url="http://www.mysql.com";

改用为：

1
2
mysql> SELECT id FROM url WHERE url="http://www.mysql.com"
    -> AND url_crc=CRC32("http://www.mysql.com");

这样的查询速度很快，不过需要自己来维护哈希值。可以使用触发器实现，下面的案例演示了触发器如何在插入和更新时维护url_crc列。首先创建下表:

1
2
3
4
5
6
CREATE TABLE pseudohash(
    id int unsigned NOT NULL auto_increment,
    url varchar(255) NOT NULL,
    url_crc int unsigned NOT NULL DEFAULT 0,
    PRIMARY KEY(id)
);

然后创建触发器。先临时修改以下语句分隔符，这样就可以在触发器定义中使用分好:

1
2
3
4
5
6
7
8
9
10
DELIMITER//

CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN
SET NEW.url_crc=crc32(NEW.url);
END;
//

CREATE TRIGGER pseudohash_crc_UPD BEFORE UPDATE ON pseudohash FOR  EACH ROW BEGIN SET NEW.uel_crc=crc32(NEW.url);
END;
//

如果出现哈希冲突。查询时应该使用类似于:

1
mysql> SELECT word,crc FROM words WHERE crc = CRC('gnu')AND word = 'gnu';

而不是单单：
    
1
mysql> SELECT word,crc FROM words WHERE crc = CRC('gnu');
  • 空间数据索引
    MyISAM表支持空间索引，可以用作地理数据存储，这种索引无需前缀查询，空间索引会从所有维度来索引数据，必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据，但MySQL支持并不完善
  • 全文索引
    是一种特殊类型的索引，查找的是文本中的关键词，而不是直接比较索引中的值，比较类似于搜索引擎坐的事情而不是简单的WHERE条件匹配。
    在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于MATCHI AGAINST操作，而不是普通的WHERE条件操作。

索引的优点

索引有三个优点:
1.索引大大减少了服务器需要扫描的数据量
2.索引可以帮助服务器避免排序和临时表
3.索引可以将随机I/O变为顺序I/O

高性能的索引策略

不要索引独立的列

独立的列，MySQL是不会使用索引的
独立的列是指索引列不能是表达式的一部分，也不能是函数的参数，例如：

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

或者

1
mysql> SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <=10;

前缀索引和索引选择性

索引的选择性：不重复的索引值和数据表记录总数(#T)的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL子啊查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

诀窍在于选择足够长的前缀以保证较高的选择性，同时又不能太长(以便节约空间)。换句话说，前缀的“基数”应该接近于完整列的“基数”,一般计算合适的前缀长度就是计算完整列的选择性:

1
mysql>SELECT COUNT(DISTINCT ???)/COUNT(*) FROM ???

得出的数与

1
mysql>SELECT COUNT(DISTINCT LEFT(???, 3))/COUNT(*) As sel3 FROM ???

相近即可，其中3最前缀的数字的多少。

选择合适的索引列顺序

在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP和DISTINCT等子句的查询需求
以下面的查询为例:

1
SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 584

要使用什么索引可以用下面的查询预测以下，看看各个WHERE条件的分支对应的数据基数有多大:

1
2
3
4
mysql<SELECT SUM(staff_id=2),SUM(customer_id = 584) FROM payment\G
***********************1.row*****************************
SUM(staff_id=2):7992
SUM(customer_id = 584):30

这样可以发现选用customer_id在前面比较好，但是不能依赖与具体值，所以可以这样:

1
2
3
4
5
6
7
8
mysql> SELECT COUNT(DISTINC staff_id)/COUNT(*) AS staff_id_selectivity,
    >COUNT(DISTINC customer_id)/COUNT(*) AS customer_id_selectivity,
    >COUNT(*)
    >FROM payment\G
***********************1.row*****************************
staff_id_selectivity：0.0001
customer_id_selectivity:0.0373
COUNT(*):16049

可以看出customer_id的选择性更高，所以答案是将其作为索引列的第一列。

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，具体细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页中。聚簇表示数据行和相邻的键值紧凑地存储在一起，因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
enter description here
图展示了聚簇索中的记录是如何存放的。注意到，叶子页包含了行的全部数据，但是节点页只包含了索引列。InnoDB将通过主键聚集数据，这就是说图中“被索引的列”就是主键列。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能会相聚甚远。

聚集的数据有一些重要的优点:

  • 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O
  • 数据访问更快。因为聚簇索引将索引和数据保存在同一个B-Tree
  • 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
    也有一些缺点:
  • 聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，也没有优势
  • 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE 命令重新组织一下表。
  • 更新更新聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作，页分裂会导致表占用更多的磁盘空间。
  • 聚簇索引可能导致权标扫面变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
  • 二级索引可能不想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
  • 二级索引访问需要两次索引查找，而不是一次（因为二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值，然后根据这个值取聚簇索引中查找对应的行）。

覆盖索引

如果一个索引包含所有需要查询的字段的值，我们就称为覆盖索引

覆盖索引是非常有用的能够极大地提高性能，如果查询只需要扫描索引而无须回表，会带来很多好处:

  • 索引条目通常远小于数据行大小，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量。
  • 由于索引是按照列值顺序存储的所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/o要少得多。
  • 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用，InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询。

由于B-Tree索引能够存储索引列的值，所以只有它才能称为覆盖索引。当发起一个被索引覆盖的查询时，在EXPLAIN的Extra列可以看到“Using index”的信息。假如表有一个多列索引(store_id,film_id)。MySQL如果只需要访问这两列，就可以使用这个索引做覆盖索引。

使用索引扫描来做排序

MySQL有两种方式可以生产有序的结果:通过排序操作和按索引顺序扫描，如果EXPLAIN出来的tyre列的值为index，则说明使用了索引扫描来做排序。

扫描索引本事很快的，但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行，这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I、O密集型的工作负载时。所以要设计MySQL可以使用同一个索引既能满足排序又能用于查找行。
Ps:只有当索引的列顺序和ORDER BY子句的顺序完全已知，并且所有列的排序方向都一样时，MySQL才能都使用索引来对结果做排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的:需要满足索引的最左前缀的要求；否则MySQL都需要执行排序操作，而无法利用索引排序。

索引案例学习

案例直接看书

支持多种过滤条件

in()与where的诀窍使用，见书。

避免多个范围条件

优化排序

维护索引和表

当需要找到并修复损坏的表，维护准确的缩影统计信息，减少碎片时，看书。

查看评论