置
顶

pppenger.github.io

目录

知识体系

基础：

• sql语句
• 表结构设计

调优：

• 索引、慢查询优化
• 配置参数调优

核心原理：

• InnoDb存储引擎 （包括隔离级别、事务、锁、缓存池、回滚日志等等）
• Mysqld (包括连接管理、进程管理、查询缓存、查询优化、日志等等)

架构与运维：

• 用户与权限、安全
• 备份与恢复
• 日志
• 分布式与高可用

链接：https://www.zhihu.com/question/34840297/answer/272185020

MySQL基础笔记

https://www.cnblogs.com/luyucheng/p/6223198.html

一、登录参数

-D 打开指定数据库
-h 服务器名称
-p 密码
-P 端口
-u 用户名
-V 输出版本信息并退出
--prompt 提示符 mysql> ，或者登陆后 用prompt命令

提示符： \D 完整的日期 \d 当前数据库 \h 服务器名称 \u 当前用户

二、常用命令（操作/文本处理/日期/数值处理）

https://www.cnblogs.com/luyucheng/p/6223198.html

https://github.com/CyC2018/CS-Notes/blob/master/notes/SQL.md#一基础

1.显示当前服务器版本

xxxxxxxxxx
select version();

2.显示当前时间

xxxxxxxxxx
select now();

3.显示当前用户

xxxxxxxxxx
select user();

4.显示当前用户打开的数据库

xxxxxxxxxx
select database();

5.字符连接

xxxxxxxxxx
select concat('a','b');

6.使用指定分隔符连接

xxxxxxxxxx
select concat_ws('-','a','b');

7.数字格式话

xxxxxxxxxx
select format(20.25,1);

8.转小写

xxxxxxxxxx
select lower('MYSQL');

9.转大写

xxxxxxxxxx
select upper('mysql');

10.左截取

xxxxxxxxxx
select left('mysql',2);

11.右截取

xxxxxxxxxx
select right('mysql',2);

12.获取字符串长度

xxxxxxxxxx
select length('mysql');

13.删除左字符

xxxxxxxxxx
select ltrim(' mysql');

14.删除右字符

xxxxxxxxxx
select rtrim('mysql ');

15.删除左右字符

xxxxxxxxxx
select trim(leading '-' from '-mysql');

16.替换字符

xxxxxxxxxx
select replace('-my-sql','-','+');

17.截取字符

xxxxxxxxxx
select substring('mysql',1,2);

18.进一取整

xxxxxxxxxx
select ceil(3.99);

19.去尾取整

xxxxxxxxxx
select filoor(3.01);

20.整数除法

xxxxxxxxxx
select 5 div 3;

21.取余数

xxxxxxxxxx
select 5 mod 3;

22.幂运算

xxxxxxxxxx
select power(2,8);

23.四舍五入

xxxxxxxxxx
select round(3.5);

24.数字截取

xxxxxxxxxx
select truncate(20.235,2);

25.在范围内

xxxxxxxxxx
[not] between...and...

26.在列出值范围内

xxxxxxxxxx
[not] in()

25.为空

xxxxxxxxxx
is [not] null

26.当前日期

xxxxxxxxxx
select curdate();

27.当前时间

xxxxxxxxxx
select curtime();

28.日期变化

xxxxxxxxxx
select date_add('2015-2-12',interval -365 day);

29.日期差值

xxxxxxxxxx
select datediff('2013-2-12','2015-2-12');

30.日期格式化

xxxxxxxxxx
select date_fromat('2015-2-12','%Y-%m-%d');

31.连接id

xxxxxxxxxx
select connection_id();

32.最后插入记录id

xxxxxxxxxx
select last_insert_id();

33.更改客户端编码

xxxxxxxxxx
set names utf8;

34.被影响的记录总数

xxxxxxxxxx
select row_count();

35.查看状态

xxxxxxxxxx
show status;

36.查看索引

xxxxxxxxxx
show index from A;

37.查看数据库进程

xxxxxxxxxx
show processlist;

38.计算字段

AS取别名

通常需要使用 AS 来取别名，否则输出的时候字段名为计算表达式。

xxxxxxxxxx
SELECT col1 * col2 AS alias
FROM mytable;

CONCAT() 用于连接两个字段。许多数据库会使用空格把一个值填充为列宽，因此连接的结果会出现一些不必要的空格，使用 TRIM() 可以去除首尾空格。

xxxxxxxxxx
SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM mytable;

三、操作数据库

1.创建数据库

xxxxxxxxxx
create {database|schema} [if not exists] db_name [default] character set [=] charset_name
例：CREATE DATABASE test;

2.修改数据库

xxxxxxxxxx
alter {database|schema} db_name [default] character set [=] charset_name
例：ALTER DATABASE test CHARACTER SET utf8;

3.删除数据库

xxxxxxxxxx
drop {database|schema} [if exists] db_name
例：DROP DATABASE test;

4.查看库的信息

xxxxxxxxxx
show create database db_name;
例：SHOW CREATE DATABASE test;

5.查看当前服务器下的数据库列表

xxxxxxxxxx
show {databases|schemas} [like 'pattern'|where expr]
例：SHOW DATABASE test;

6.查看警告信息

xxxxxxxxxx
show warnings;

四、操作数据表

注释：

SQL 支持以下三种注释：

xxxxxxxxxx
# 注释
SELECT *
FROM mytable; -- 注释
/* 注释1
   注释2 */

1.打开数据库

xxxxxxxxxx
use db_name;
例：USE test;

2.创建表

xxxxxxxxxx
create table [if not exists] tbl_name(
    age tinyint(2) unsigned not null auto_increment primary key
);
例：CREATE TABLE user(
    id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,//主键自增
    name VARCHAR(20) NOT NULL UNIQUE KEY,//唯一
    price DECIMAL(8,2) UNSIGNED DEFAULT 0.00,//默认
    cid INT(10) UNSIGNED,
    KEY cid(cid),
    FOREIGN KEY (cid) REFERENCES cate (id) ON DELETE CASCADE//外键 （删除时执行CASCADE）
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.查看数据列表

xxxxxxxxxx
show tables [from db_name] [like 'pattern'|where expr]
例：SHOW TABLES;

4.查看表信息

xxxxxxxxxx
show create table tbl_name;
例：SHOW CREATE TABLE user;

5.查看表结构

xxxxxxxxxx
（1）show columns from tbl_name;
例：SHOW COLUMUNS FROM user;
（2）desc tbl_name;

6.添加单列

xxxxxxxxxx
alert table tbl_name add [column] col_name column_definition（类型定义）[first|after col_name];
例：ALTER TABLE user ADD num INT(10) UNSIGNED;

7.添加多列

xxxxxxxxxx
alter table tbl_name add [column] (col_name column_definition,..);
例：ALTER TABLE user ADD num INT(10) UNSIGNED, time INT(10) UNSIGNED;

8.删除列

xxxxxxxxxx
alter table tbl_name drop [column] col_name,drop [column] col_name,...
例：ALTER TABLE user DROP num,DROP time;

9.修改列定义

xxxxxxxxxx
alter table tbl_name modify [column] col_name column_definition [first|after col_name]  
例：ALTER TABLE user MODIFY num TINYINT(10) AFTER name;

10.修改列名称

xxxxxxxxxx
alter table tbl_name change [column] old_col_name new_col_name column_definition [first|after col_name]
例：ALTER TABLE user CHANGE price money INT(10);

11.修改表名称

​x
（1）alter table tbl_name rename [to|as] new_tbl_name
 例：ALTER TABLE user RENAME goods;
​
（2）rename table tbl_name to new_tbl_name [,tbl_name2 to new_tbl_name2]
 例：RENAME TABLE user TO goods;

12.插入记录

xxxxxxxxxx
一、普通插入
（1）insert [into] tbl_name [(col_name,..)] {values|value} ({expr|default},...),(...),...;
例：INSERT user (id,name,price) VALUES (DEFAULT,tom',20);
二、（未知）更改插入
（2）insert [into] tbl_name set col_name={expr|default},..
例：INSERT user SET name='tom';
三-1、插入检索出来的数据
（3）insert [into] tbl_name [(col_name,...)] select ...
例：INSERT user SELECT id,name FROM goods;
三-2、将一个表的内容插入到一个刚要创建的新表
    CREATE TABLE newtable AS SELECT * FROM mytable;

13.更新记录

xxxxxxxxxx
update [low_priority] [ignore] tbl_name set col_name1={expr1|default} [,col_name2={expr2|default}].. [where where_condition]
例：UPDATE user SET num = num + id;

14.删除记录

xxxxxxxxxx
delete from tbl_name [where where_condition]
例：DELETE FROM user WHERE id=3;

15.查找记录

DISTINCT：相同值只会出现一次。它作用于所有列，也就是说所有列的值都相同才算相同。

xxxxxxxxxx
SELECT DISTINCT col1, col2
FROM mytable;

LIMIT：

限制返回的行数。可以有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数。

返回前 5 行：

xxxxxxxxxx
SELECT *
FROM mytable
LIMIT 5;
​
​
SELECT *
FROM mytable
LIMIT 0, 5;

返回第 3 ~ 5 行：

xxxxxxxxxx
SELECT *
FROM mytable
LIMIT 2, 3;

组合查询

使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。

每个查询必须包含相同的列、表达式和聚集函数。

默认会去除相同行，如果需要保留相同行，使用 UNION ALL。

只能包含一个 ORDER BY 子句，并且必须位于语句的最后。

xxxxxxxxxx
SELECT col
FROM mytable
WHERE col = 1
UNION
SELECT col
FROM mytable
WHERE col =2;

各种查询：

xxxxxxxxxx
select select_expr [,select_expr..]
[
  from tbl_name
  [where where_condition]
  [group by {col_name|position} [asc|desc],...]
  [having where_condition]
  [order by {col_name|expr|position} [asc|desc],...]
  [limit {[offset,] row_count|row_count offset of_set}]
]

16.多表更新

xxxxxxxxxx
UPDATE user INNER JOIN cate ON user.cid=cate.id SET user.cname=cate.name;

17.创建表的同时将查询结果写入数据表（在插入记录时介绍过）

xxxxxxxxxx
CREATE TABLE cate (
 id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(10) NOT NULL
)
SELECT cid,cname FROM user GROUP BY cid;

18.修改表引擎

xxxxxxxxxx
alter table tbl_name engine [=] engine_name
例：ALTER TABLE user ENGINE = MyISAM;

19.修改分隔符

xxxxxxxxxx
delimiter $$

20.删除表

xxxxxxxxxx
drop table A;

21.截断表

xxxxxxxxxx
truncate A;

五、排序：

• ASC ：升序（默认）
• DESC ：降序

可以按多个列进行排序，并且为每个列指定不同的排序方式：

xxxxxxxxxx
SELECT *
FROM mytable
ORDER BY col1 DESC, col2 ASC;

六、约束：

保证数据的完整性和一致性； 列级约束（针对一个字段），表级约束（针对多个字段）

1.主键约束：primary key

（1）每个表只存在一个 （2）保证记录的唯一性 （3）自动为not null （4）添加主键约束：

xxxxxxxxxx
 alter table tbl_name add [constraint [symbol]] primary key [index_type] (index_col_name,...)
 例：ALTER TABLE user ADD PRIMARY KEY (id);

（5）删除主键约束：

xxxxxxxxxx
 alter table tbl_name dro p primary key
 例：ALTER TABLE user DROP PRIMARY KEY;

2.唯一约束：unique key

（1）每个表可存在多个 （2）保证记录的唯一性 （3）可以存一个null （4）添加唯一约束：

xxxxxxxxxx
 alter table tbl_name add [constraint [symbol]] unique [index|key] [index_name] [index_type] (index_col_name,...)
 例：ALTER TABLE user ADD UNIQUE (name);

（5）删除唯一约束：

xxxxxxxxxx
 alter table tbl_name drop {index|key} index_name
 例：ALTER TABLE user DROP INDEX name;

（6）查看约束：

xxxxxxxxxx
 show indexes from tbl_name\G;
 例：SHOW INDEXS FROM user\G;

3.默认约束：default （1）添加删除默认约束：

xxxxxxxxxx
 alter table tbl_name alter [column] col_name {set default literal | drop default}
 例：ALTER TABLE user ALTER num SET DEFAULT 0;
     ALTER TABLE user ALTER num DROP DEFAULT;

4.非空约束：not null

5.外键约束：foreign key

子表-父表

设置了外键的表为子表。被设置为外键的表为父表。

外键的使用需要满足下列的条件：

\1. 两张表必须都是InnoDB表，并且它们没有临时表。

\2. 建立外键关系的对应列必须具有相似的InnoDB内部数据类型。

\3. 建立外键关系的对应列必须建立了索引。

\4. 假如显式的给出了CONSTRAINT symbol，那symbol在数据库中必须是唯一的。假如没有显式的给出，InnoDB会自动的创建。

保证数据的完整性和一致性； ​ 实现1对1、多对1关系。 （1）cascade：从父表删除或更新且自动删除或更新子表中的匹配行 （2）set null：从父表删除或更新并设置子表中的外键列为null。如果使用该选项，必须保证子表没有指定not null （3）restrict： 拒绝删除或者更新父表被引用的外键列。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。【当父表的外键列没有被引用时还是可以更改的，其他列随时可以更改】 （4）no action：标准sql的关键字，在mysql中与restrict相同 （5）添加外键约束

xxxxxxxxxx
 alter table tbl_name add [constraint [symbol]] foreign key [index_name] (index_col_name,...) reference_definition
 例：ALTER TABLE user ADD FOREIGN KEY (cid) REFERENCES cate (id)

外键约束使用最多的两种情况无外乎：

1）父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败；

2）父表更新时子表也更新，父表删除时子表匹配的项也删除。

前一种情况，在外键定义中，我们使用ON UPDATE CASCADE ON DELETE RESTRICT；后一种情况，可以使用ON UPDATE CASCADE ON DELETE CASCADE。

（6）删除外键约束：

xxxxxxxxxx
alter table tbl_name drop foreign key symbol
 例：ALTER TABLE user DROP FOREIGN KEY cid;

七、过滤符

不进行过滤的数据非常大，导致通过网络传输了多余的数据，从而浪费了网络带宽。因此尽量使用 SQL 语句来过滤不必要的数据，而不是传输所有的数据到客户端中然后由客户端进行过滤。

xxxxxxxxxx
SELECT *
FROM mytable
WHERE col IS NULL;

下表显示了 WHERE 子句可用的操作符

应该注意到，NULL 与 0、空字符串都不同。

AND 和 OR 用于连接多个过滤条件。优先处理 AND，当一个过滤表达式涉及到多个 AND 和 OR 时，可以使用 () 来决定优先级，使得优先级关系更清晰。

IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。

NOT 操作符用于否定一个条件。

八、分组（GROUP BY，ORDER BY）

把具有相同的数据值的行放在同一组中。

可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。

指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。

xxxxxxxxxx
SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col;

GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。

xxxxxxxxxx
SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;

WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。

xxxxxxxxxx
SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;

分组规定：

• GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
• 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
• NULL 的行会单独分为一组；
• 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。

九、子查询

嵌套在内部，始终出现在括号内; 可以包含多个关键字或条件，如distinct，group by，order by，limit，函数等; 外层可以是：select，insert，update，set，do

1.比较运算符：=,>,<,<=,>=,<>

xxxxxxxxxx
select * from t1 where col_name1 >= ANY (select col_name2 from t2);

（1）any（some） ：符合任一个 （2）all：符合所有

2.[NOT] IN/EXISTS

xxxxxxxxxx
select * from t1 where col_name1 NOT IN ALL (select col_name2 from t2);

十、通配符

通配符也是用在过滤语句中，但它只能用于文本字段。

• % 匹配 >=0 个任意字符；
• _ 匹配 ==1 个任意字符；
• [ ] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。

使用 Like 来进行通配符匹配。

xxxxxxxxxx
SELECT *
FROM mytable
WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本

不要滥用通配符，通配符位于开头处匹配会非常慢。

十一、表连接

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。

连接可以替换子查询，并且比子查询的效率一般会更快。

可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。

1.内连接(交集)：

inner join

内连接又称等值连接，使用 INNER JOIN 关键字。

xxxxxxxxxx
SELECT A.value, B.value
FROM tablea AS A INNER JOIN tableb AS B
ON A.key = B.key;

可以不明确使用 INNER JOIN，而使用普通查询并在 WHERE 中将两个表中要连接的列用等值方法连接起来。

自连接

自连接可以看成内连接的一种，只是连接的表是自身而已。

一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。

子查询版本

xxxxxxxxxx
SELECT name
FROM employee
WHERE department = (
      SELECT department
      FROM employee
      WHERE name = "Jim");

自连接版本

xxxxxxxxxx
SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim";

自然连接

自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。

内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。

xxxxxxxxxx
SELECT A.value, B.value
FROM tablea AS A NATURAL JOIN tableb AS B;

2.外链接：

外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。

（1）左连接(左全及右符合)：left join

customers 表：

orders 表：

结果：

（2）右连接(右全及左符合)：right join

十二、函数和自定义函数

1.函数

各个 DBMS 的函数都是不相同的，因此不可移植，以下主要是 MySQL 的函数。

汇总

AVG() 会忽略 NULL 行。

使用 DISTINCT 可以汇总不同的值。

xxxxxxxxxx
SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable;

2.自定义函数

xxxxxxxxxx
create function fun_name
returns
{string|int|real|decimal}
routine_body
​
例（无参数）：
CREATE FUNCTION f1() RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y-%m-%d');
​
例（参数）：
CREATE FUNCTION f2(num1 INT , num2 INT)
RETURNS DECIMAL(10,2)
RETURN (num1+num2)/2;
​
例（复合）：
CREATE FUNCTION addusers(username VARCHAR(20))
RETURNS INT
BEGIN
INSERT user (name) VALUES (username);
RETURN LAST_INSERT_ID();
END
$$

十三、存储过程

存储过程: procedure 概念类似于函数,就是把一段代码封装起来, 当要执行这一段代码的时候,可以通过调用该存储过程来实现. 在封装的语句体里面,可以用if/else, case,while等控制结构. 可以进行sql编程.
存储过程与自定义函数的区别 1.存储过程复杂，函数针对性强 2.存储过程返回多个值，函数返回一个值 3.存储过程独立执行，函数可作为其他sql语句的组成部分出现

使用存储过程的好处：

• 代码封装，保证了一定的安全性；
• 代码复用；
• 由于是预先编译，因此具有很高的性能。

xxxxxxxxxx
创建：
create
[definer = {user|current_user}]
procedure sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
​
proc_parameter:
[in|out|inout] param_name type
​
调用：
call sa_name[()];
​
修改：
alter procedure sp_name [characteristic ...]
comment 'string'
| {contains sql|no sql|reads sql data| modifies sql data} | sql security {definer|invoker}
​
删除：
drop procedure [if exists] sp_name;
​
例（无参数）：
CREATE PROCEDURE sp1() SELECT VERSION();
​
例（IN）：
CREATE PROCEDURE sp2(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM user WHERE id=p_id ;
END
​
例（IN OUT）：
CREATE PROCEDURE sp3(IN p_id INT UNSIGNED,OUT userNums INT )
BEGIN
DELETE FROM user WHERE id=p_id ;
SELECT COUNT(id) FROM users INTO userNums;
END
​
例（IN OUT）：
CREATE PROCEDURE sp4(IN p_age INT UNSIGNED,OUT deleteUsers INT ,OUT userCounts INT)
BEGIN
DELETE FROM user WHERE age=p_age ;
SELECT ROW_COUNT() INTO deleteUsers;
SELECT COUNT(id) FROM users INTO userCounts;
END

十四、游标

在存储过程中使用游标可以对一个结果集进行移动遍历。

游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

使用游标的四个步骤：

1. 声明游标，这个过程没有实际检索出数据；
2. 打开游标；
3. 取出数据；
4. 关闭游标；

xxxxxxxxxx
delimiter //
create procedure myprocedure(out ret int)
    begin
        declare done boolean default 0;
​
        declare mycursor cursor for
        select col1 from mytable;
        # 定义了一个 continue handler，当 sqlstate '02000' 这个条件出现时，会执行 set done = 1
        declare continue handler for sqlstate '02000' set done = 1;
​
        open mycursor;
​
        repeat
            fetch mycursor into ret;
            select ret;
        until done end repeat;
​
        close mycursor;
    end //
 delimiter ;

十五、视图的定义:

视图是由查询结果形成的一张虚拟表. 视图的创建语法: Create [algorethm=temptable] view 视图名 as select 语句;

1:可以简化查询 2:可以进行权限控制 3:大数据分表时可以用到

Merge: 当引用视图时,引用视图的语句与定义视图的语句合并. Temptable:当引用视图时,根据视图的创建语句建立一个临时表 Undefined:未定义,自动,让系统帮你选.

十六、触发器:

作用: 监视某种情况并触发某种操作.

触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。

触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。

INSERT 触发器包含一个名为 NEW 的虚拟表。

xxxxxxxxxx
CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW SELECT NEW.col into @result;
​
SELECT @result; -- 获取结果

DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。

UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。

MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。

xxxxxxxxxx
创建触发器的语法
Create trigger triggerName
After/before insert/update/delete on 表名
For each row #这句话是固定的
Begin
Sql语句;  #一句或多句,insert/update/delete范围内
End;
​
create trigger t1
after
insert
on order
for each row
begin
update goods xxx
end$

十七、事务的ACID特性

原子性(Atomicity)：原子意为最小的粒子，或者说不能再分的事物。 数据库事务的不可再分的原则即为原子性。 组成事务的所有查询必须： 要么全部执行，要么全部取消（就像上面的银行例子）。 一致性(Consistency)：指数据的规则,在事务前/后应保持一致 隔离性(Isolation)：简单点说，某个事务的操作对其他事务不可见的. 持久性(Durability)：当事务完成后，其影响应该保留下来，不能撤消

十八、字符集

基本术语：

• 字符集为字母和符号的集合；
• 编码为某个字符集成员的内部表示；
• 校对字符指定如何比较，主要用于排序和分组。

除了给表指定字符集和校对外，也可以给列指定：

xxxxxxxxxx
CREATE TABLE mytable
(col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci )
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

可以在排序、分组时指定校对：

xxxxxxxxxx
SELECT *
FROM mytable
ORDER BY col COLLATE latin1_general_ci;

十九、权限管理

MySQL 的账户信息保存在 mysql 这个数据库中。

xxxxxxxxxx
USE mysql;
SELECT user FROM user;

创建账户

新创建的账户没有任何权限。

xxxxxxxxxx
CREATE USER myuser IDENTIFIED BY 'mypassword';

修改账户名

xxxxxxxxxx
RENAME myuser TO newuser;

删除账户

xxxxxxxxxx
DROP USER myuser;

查看权限

xxxxxxxxxx
SHOW GRANTS FOR myuser;

授予权限

账户用 username@host 的形式定义，username@% 使用的是默认主机名。

xxxxxxxxxx
GRANT SELECT, INSERT ON mydatabase.* TO myuser;

删除权限

GRANT 和 REVOKE 可在几个层次上控制访问权限：

• 整个服务器，使用 GRANT ALL 和 REVOKE ALL；
• 整个数据库，使用 ON database.*；
• 特定的表，使用 ON database.table；
• 特定的列；
• 特定的存储过程。

xxxxxxxxxx
REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;

更改密码

必须使用 Password() 函数进行加密。

xxxxxxxxxx
SET PASSWROD FOR myuser = Password('new_password');

数据库优化方案

https://zhuanlan.zhihu.com/p/54937777

一般来说，咱们优化有很多种的方案或者方向，现在总结一下：

1. 业务优化
2. 表结构优化（数据库优化）
3. 表索引优化（数据库优化）
4. SQL优化

一、业务优化

一把来说，跟业务相关的优化，都是业务优化。业务上的问题一般需要产品修改需求。

主要优化方面：减少不必要的业务需求，优化设计方案。

二、表结构优化

• 选择合适的字段类型
• 精确字段长度
• 适当利用冗余字段（减少连表查询、减少计算）;
• 把字段定义为NOT NULL并且提供默认值
• 禁止存储大文件或者大照片
• 禁止使用外键

xxxxxxxxxx
以下来自https://www.cnblogs.com/luyucheng/p/6323477.html：
### 选择合适的数据类型
​
（1）使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob
（2）使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数
（3）使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar
（4）尽可能使用not null定义字段
（5）尽量少用text，非用不可最好分表

三、表索引优化

• 在合适的字段上建索引（离散性不高、查询频率不高、更新频率高的字段不适合建索引）；
• 组合索引区分度高的字段放在前面（最左匹配原则）
• 单表索引不能过多(在5个以内)

xxxxxxxxxx
以下来自https://www.cnblogs.com/luyucheng/p/6323477.html：
### 选择合适的索引列
​
（1）查询频繁的列，在where，group by，order by，on从句中出现的列
（2）where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列
（3）长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
（4）离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高：

xxxxxxxxxx
mysql> SELECT COUNT(DISTINCT column_name) FROM table_name;

注意：什么是离散型？

离散性指的就是数据重复（相似）情况，数据字段的数据重复情况越少，离散型高；重复情况越多，离散性越低。

例如：性别，离散性基本上是最低的，因为只有两种情况，以后可能出现第三种情况。

四、SQL优化

SQL优化的情况很多，我这是简单介绍几种优化方案：

• 只取需要的列，避免SELECT *
• 禁止使用属性隐式转换
• 禁止在WHERE条件的属性上使用函数或者表达式
• 避免使用or查询
• 联表查询用小结果集驱动大结果集
• 禁止负向查询(NOT、!=、<>、!<、!>、NOT IN、NOT LIKE)，以及%开头的模糊查询

xxxxxxxxxx
以下来自https://www.cnblogs.com/luyucheng/p/6323477.html：
​
（1）使用limit对查询结果的记录进行限定
（2）避免select *，将需要查找的字段列出来
（3）使用连接（join）来代替子查询
（4）拆分大的delete或insert语句

注意：为什么不提倡 使用 *

• 不能使用索引（查询索引字段）
• 数据量大，网络传输会变慢（查询少量字段的时候）
• 需要进行SQL解析
• join buffer缓存的数据量会减少（查询少量字段的时候）
• 出现覆盖索引情况

以下来自https://www.cnblogs.com/luyucheng/p/6323477.html：

5、使用命令分析

（1）SHOW查看状态 1.显示状态信息

xxxxxxxxxx
mysql> SHOW [SESSION|GLOBAL] STATUS LIKE '%Status_name%';

session（默认）：取出当前窗口的执行 global：从mysql启动到现在 （a）查看查询次数（插入次数com_insert、修改次数com_insert、删除次数com_delete）

xxxxxxxxxx
mysql> SHOW STATUS LIKE 'com_select';

（b）查看连接数(登录次数)

xxxxxxxxxx
mysql> SHOW STATUS LIKE 'connections';

（c）数据库运行时间

xxxxxxxxxx
mysql> SHOW STATUS LIKE 'uptime';

（d）查看慢查询次数

xxxxxxxxxx
mysql> SHOW STATUS LIKE 'slow_queries';

（e）查看索引使用的情况：

xxxxxxxxxx
mysql> SHOW STATUS LIKE 'handler_read%';

handler_read_key：这个值越高越好，越高表示使用索引查询到的次数。 handler_read_rnd_next：这个值越高，说明查询低效。 2.显示系统变量

xxxxxxxxxx
mysql> SHOW VARIABLES LIKE '%Variables_name%';

3.显示InnoDB存储引擎的状态

xxxxxxxxxx
mysql> SHOW ENGINE INNODB STATUS;

（2）EXPLAIN（解释）分析查询

xxxxxxxxxx
mysql> EXPLAIN SELECT column_name FROM table_name;

explain查询sql执行计划，各列含义： id：查询顺序，越大的越先（复合语句有用） table：表名； type：连接的主要类型 -const：主键、索引； -eq_reg：主键、索引的范围查找； -ref：连接的查找（join） -range：索引的范围查找； -index、 -all：全表扫描；（证明语句需要优化） possible_keys：可能用到的索引； key：实际使用的索引； key_len：索引的长度，越短越好； ref：索引的哪一列被使用了，常数较好； rows：mysql认为必须检查的用来返回请求数据的行数； extra：（主要） 出现以下两个需要优化： -Using filesort 文件排序（额外排序）。表示MySQL会对结果采用一个外部索引（外部方式）排序，而不使用到已有表索引，此排序可能发生在内存或者磁盘上。 -Using temporary 使用了临时表。常见于排序order by和分组查询group by。

（3）PROFILING分析SQL语句

1.开启profile。查看当前SQL执行时间

xxxxxxxxxx
mysql> SET PROFILING=ON; 
mysql> SHOW profiles;

2.查看所有用户的当前连接。包括执行状态、是否锁表等

xxxxxxxxxx
mysql> SHOW processlist;

（4）PROCEDURE ANALYSE()取得建议 通过分析select查询结果对现有的表的每一列给出优化的建议

xxxxxxxxxx
mysql> SELECT column_name FROM table_name PROCEDURE ANALYSE();

（5）OPTIMIZE TABLE回收闲置的数据库空间

xxxxxxxxxx
mysql> OPTIMIZE TABLE table_name;

对于MyISAM表，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）。 对于InnoDB表，OPTIMIZE TABLE被映射到ALTER TABLE上，这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。 只需在批量删除数据行之后，或定期（每周一次或每月一次）进行一次数据表优化操作即可，只对那些特定的表运行。 （6）REPAIR TABLE修复被破坏的表

xxxxxxxxxx
mysql> REPAIR TABLE table_name;

（7）CHECK TABLE检查表是否有错误

xxxxxxxxxx
mysql> CHECK TABLE table_name;

6、慢查询

开启慢查询

https://www.cnblogs.com/luyucheng/p/6265594.html

如何排查慢查询？

https://mp.weixin.qq.com/s/6TBmfdZTHGypwe4C0gS_Wg：

导致慢查询的原因通常有缺少索引，索引失效，或者查询条件不够合理，所谓排查就是找出具体是哪个原因

1.show_query_log_file 开始并查看查询日志查看慢查询的语句（找出发生慢查询的语句，考虑是否修改）

2.通过explain查看索引的运行状况（观察索引是否失效，排查索引问题）

3.改进业务代码层的不合理访问，尝试修改索引等等。

7、分区

https://www.cnblogs.com/luyucheng/p/6290567.html

MySQL分区和分表

8、配置优化

MySQL配置优化

https://www.cnblogs.com/luyucheng/p/6340076.html

索引

一、索引的数据结构

https://blog.csdn.net/qq_36098284/article/details/80178336

1、二叉树

二叉树具有以下性质：左子树的键值小于根的键值，右子树的键值大于根的键值。 如下图所示就是一棵二叉查找树， 对该二叉树的节点进行查找发现深度为1的节点的查找次数为1，深度为2的查找次数为2，深度为n的节点的查找次数为n，因此其平均查找次数为 (1+2+2+3+3+3) / 6 = 2.3次

二叉查找树可以任意地构造，同样是2,3,5,6,7,8这六个数字，也可以按照下图的方式来构造：

但是这棵二叉树的查询效率就低了。因此若想二叉树的查询效率尽可能高，需要这棵二叉树是平衡的，从而引出新的定义——平衡二叉树，或称AVL树。

2、平衡二叉树（AVL树）：O（logn）

平衡二叉树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1。

如果在AVL树中进行插入或删除节点，可能导致AVL树失去平衡，AVL树失去平衡之后，可以通过旋转使其恢复平衡。

缺点：旋转平衡次数多，树的深度大，IO次数会很多。

3、红黑树

参考：https://www.cnblogs.com/myseries/p/10662710.html

红黑树的规则： 1）每个结点要么是红的，要么是黑的。 2）根结点是黑的。 3）每个叶结点（叶结点即指树尾端NIL指针或NULL结点）是黑的。 4）如果一个结点是红的，那么它的俩个儿子都是黑的。 5）对于任一结点而言，其到叶结点树尾端NIL指针的每一条路径都包含相同数目的黑结点。

　现在想想，我的理解是平衡树（AVL）更平衡，结构上更加直观，时间效能针对读取而言更高，但是维护起来比较麻烦！！！（插入和删除之后，都需要rebalance）。但是，红黑树通过它规则的设定，确保了插入和删除的最坏的时间复杂度是O(log N) 。

设计红黑树的目的，就是解决平衡树的维护起来比较麻烦的问题，红黑树，读取略逊于AVL，维护强于AVL，每次插入和删除的平均旋转次数应该是远小于平衡树。

4、平衡多路查找树（B-Tree）

一棵m阶的B-Tree有如下特性：

1. 每个节点最多有m（m>=2）个孩子。
2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
3. 若根节点不是叶子节点，则至少有2个孩子
4. 所有叶子节点都在同一层
5. 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
6. 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
7. ki(i=1,…n)为关键字，且关键字升序排序。
8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

1. 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
2. 比较关键字29在区间（17,35），找到磁盘块1的指针P2。
3. 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
4. 比较关键字29在区间（26,30），找到磁盘块3的指针P2。
5. 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
6. 在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

5、B+-Tree：O(log（n,m）)

n为存放的key总数，m为节点的度

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

1. 非叶子节点只存储键值信息。
2. 所有叶子节点之间都有一个链指针。
3. 数据记录都存放在叶子节点中

将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

说明：

1.通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点。

2. 而且所有叶子节点（即数据节点）之间是一种链式环结构。

3.因此可以对B+Tree进行两种查找运算：**

3.1一种是对于主键的范围查找和分页查找

3.2 另一种是从根节点开始，进行随机查找。**

6、为什么mysql索引使用b+树而不使用红黑树?

原文：https://blog.csdn.net/buyulian/article/details/77996253 AVL 数和红黑树基本都是存储在内存中才会使用的数据结构，那磁盘中会有什么不同呢？

这就要牵扯到磁盘的存储原理了

操作系统读写磁盘的基本单位是扇区，而文件系统的基本单位是簇(Cluster)。

也就是说，磁盘读写有一个最少内容的限制，即使我们只需要这个簇上的一个字节的内容，我们也要含着泪把一整个簇上的内容读完。

那么，现在问题就来了

一个父节点只有 2 个子节点，并不能填满一个簇上的所有内容啊？那多余的内容岂不是要浪费了？我们怎么才能把浪费的这部分内容利用起来呢？哈哈，答案就是 B+ 树。

由于 B+ 树分支比二叉树更多，所以相同数量的内容，B+ 树的深度更浅，深度代表什么？代表磁盘 io 次数啊！数据库设计的时候 B+ 树有多少个分支都是按照磁盘一个簇上最多能放多少节点设计的啊！

https://www.cnblogs.com/myseries/p/10662710.html：

　　b+树就是为文件存储而生的。如果数据库文件存储在主存中我认为两种结构的查询速度差距不是很大，因为主存的查找速度非常快。而数据库文件实际存储在磁盘中，定位一行信息需要查找该行文件所在柱面号，磁盘号，扇区号，页号这个阶段是很耗费时间的。每一次的定位请求意味着要做一次IO操作，也意味着成倍的时间消耗。因此减少IO查询的次数是提高查询性能的关键。而IO的查询次数就是索引树的高度，高度越低查询的次数越少。同样的结点次数红黑树的高度最多为2log(n+1)，而B+树的高度最多为(logt (n+1)/2)+1,随着t增大高度会更小，IO次数也会减少。

7、Hash结构

哈希索引基于哈希表实现，只有精确索引所有列的查询才有效。

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据的指针。

MySQL中，只有Memory存储引擎显示支持hash索引，是Memory表的默认索引类型，尽管Memory表也可以使用B-Tree索引。

Memory存储引擎支持非唯一hash索引，这在数据库领域是罕见的：如果多个值有相同的hash code，索引把它们的行指针用链表保存到同一个hash表项中。

8、聚簇索引& 辅助索引

InnoDB才具有聚簇表，MyISAM没有，因为InnoDB的主键索引和数据是存储在一个文件中的，辅助索引查询的时候通过先查询主键，最后才查询出数据； MyISAM的主键索引和辅助键索引都使用一个地址指向真正的数据表数据和索引树是不同的文件。辅助索引无需通过主键。所以MyISAM无论是主键索引还是其他任何索引都不属于聚簇索引。

链接：https://zhuanlan.zhihu.com/p/40820574

来源：知乎

什么是聚簇索引呢？

简单说，聚簇索引就是用来存储行数据的位置的。

什么样的字段才可以作为聚簇索引？（聚簇索引选取规则）

那当然是要具有唯一性的字段，比如：

• 主键
• 唯一索引（unique index）所在字段

这两个都没有？没关系，mysql会给你建一个rowid字段，用它作为聚簇索引：

除了聚簇索引，mysql中的其他索引，都叫二级索引（secondary index），有时也翻译为“辅助索引”。

回到本小节开头的问题，虽然id不在复合索引里头，但是mysql里所有的二级索引的叶子节点，都会存储聚簇索引的信息，而id是主键，所以所有的叶子节点，都会有id的信息，因此还是可以走覆盖索引。

附：Innodb如何通过索引查找数据

先通过索引，找到聚簇索引的值，再到聚簇索引，找到其他数据

聚集索引和非聚集索引的优缺？

聚集索引进行范围查询会比较快（数据存储和索引放在一起，就都是连续的），但也意味着每次更新数据数据记录也要重新排列，非聚集索引比较适合比较离散的数据的查询，插入删除数据的代价比较小。

二、创建语句

xxxxxxxxxx
CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

1.unique|fulltext为可选参数，分别表示唯一索引、全文索引 2.index和key为同义词，两者作用相同，用来指定创建索引 3.col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择 4.index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值 5.length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度 6.asc或desc指定升序或降序的索引值存储

三、索引类型

1.普通索引

是最基本的索引，它没有任何限制。它有以下几种创建方式： （1）直接创建索引

xxxxxxxxxx
CREATE INDEX index_name ON table(column(length))

（2）修改表结构的方式添加索引

xxxxxxxxxx
ALTER TABLE table_name ADD INDEX index_name ON (column(length))

（3）创建表的时候同时创建索引

xxxxxxxxxx
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

（4）删除索引

xxxxxxxxxx
DROP INDEX index_name ON table

2.唯一索引

与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式： （1）创建唯一索引

xxxxxxxxxx
CREATE UNIQUE INDEX indexName ON table(column(length))

（2）修改表结构

xxxxxxxxxx
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

（3）创建表的时候直接指定

xxxxxxxxxx
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

3.主键索引（主键）

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

xxxxxxxxxx
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

4.组合索引（联合索引）

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合（最左匹配原则）

xxxxxxxxxx
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

最左匹配原则：

1、mysql按照索引定义的顺序一直向右匹配知道遇到范围查询（>、<、between、like）就停止匹配，例：a=3 and b=4 and c>5 and d=5如果建立（a，b，c，d）顺序的索引，d是用不到索引，如果建立（a，b，d，c）的索引可以用到，a，b，d顺序可以任意调整。

2、=和in可以乱序，比如a=1 and b=2 and c=3 建立（a，b，c）索引可以任意顺序，mysql的查询优化器会自动帮你优化成索引可以识别的形式

5.全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。 （1）创建表的适合添加全文索引

xxxxxxxxxx
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

（2）修改表结构添加全文索引

xxxxxxxxxx
ALTER TABLE article ADD FULLTEXT index_content(content)

（3）直接创建索引

xxxxxxxxxx
CREATE FULLTEXT INDEX index_content ON article(content)

6.覆盖索引

覆盖索引（covering index）的原理很简单，就像你拿到了一本书的目录，里头有标题和对应的页码，当你想知道第267页的标题是什么的时候，完全没有必要翻到267页去看，而是直接看目录。

同理，当你要select的字段，已经在索引树里面存储，那就不需要再去检索数据库，直接拿来用就行了。

还是上面的例子，你给a、b、c三个字段建了复合索引，那么对于下面这条sql，就可以走覆盖索引:

xxxxxxxxxx
select b,c from foo where a = "xxx";

explain一下，你就会发现extra字段是“Using index”，或者使用explain FORMAT=JSON … ，输出一个json结果的结果，看“using_index”属性，你会发现是“true”，这都意味着使用到了覆盖索引。

四、缺点

1.虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。 2.建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。 索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

五、注意事项

使用索引时，有以下一些技巧和注意事项： 1.索引不会包含有null值的列 只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。 2.使用短索引 对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。 3.索引列排序 查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。 4.like语句操作 一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。 5.不要在列上进行运算 这将导致索引失效而进行全表扫描，例如

xxxxxxxxxx
SELECT * FROM table_name WHERE YEAR(column_name)<2017;

6.不使用not in和<>操作

存储引擎

https://zhuanlan.zhihu.com/p/52648590

https://www.cnblogs.com/luyucheng/p/6306512.html

一、InnoDB

1、简介

InnoDB是默认的事务型存储引擎，也是最重要，使用最广泛的存储引擎。在没有特殊情况下，一般优先使用InnoDB存储引擎。

2、存储

文件存储形式：使用InnoDB时，会将数据表分为.frm 和 idb两个文件进行存储。

数据存储形式：基于聚簇索引建立，聚簇索引对主键查询有很高的性能。不过它的二级索引(secondary index，非主键索引)中必须包含主键列，所以如果主键列很大的话，其他的所有索引都会很大。因此表上的索引较多的话，主键应当尽可能的小。

3、颗粒度

InnoDB采用MVCC(多版本并发控制)来支持高并发，InnoDB实现了四个隔离级别，默认级别是REPETABLE READ，并通过间隙锁策略防止幻读的出现。它的锁粒度是行锁。

4、事物

InnoDB是典型的事务型存储引擎，并且通过一些机制和工具，支持真正的热备份。

适合在以下几种情况下使用： 1.更新和查询都相当的频繁，多重并发 2.要求事务，或者可靠性要求比较高 3.外键约束，MySQL支持外键的存储引擎只有InnoDB

二、MyISAM

1、简介

MyISAM是MySQL中常见的存储引擎，曾经是MySQL的默认存储引擎。MyISAM是基于ISAM引擎发展起来的，增加了许多有用的扩展。

2、存储

MyISAM采用的是索引与数据分离的形式，将数据保存在三个文件中.frm.MYD,.MYIs。

• frm文件存储表的结构；
• MYD文件存储数据，是MYData的缩写；
• MYI文件存储索引，是MYIndex的缩写。

基于MyISAM存储引擎的表支持3种不同的存储格式。包括静态型、动态型和压缩型。

• 静态型是MyISAM的默认存储格式，它的字段是固定长度的；
• 动态型包含变长字段，记录的长度不是固定的；
• 压缩型需要用到myisampack工具，占用的磁盘空间较小。

3、优缺点

优点：在于占用空间小，处理速度快。

缺点：不支持事务的完整性和并发性。

4、事物

不支持事物

适合在以下几种情况下使用： 1.做很多count的计算 2.查询非常频繁

事务

https://zhuanlan.zhihu.com/p/52694611

https://www.cnblogs.com/luyucheng/p/6297480.html

事务就是一组对数据库的一系列的操作，要么同时成功，要么同时失败。

一、事务的特性（ACID）

1. 原子性：事务是整个操作，不可分割，要么都成功，要么都失败。
2. 一致性：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
3. 隔离性：事务操作是相互隔离不受其他影响。
4. 持久性：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

• 事物的隔离性是通过数据库的锁来实现的。锁的种类多种多样，到最后锁的颗粒度都是表锁跟行锁。意思是：到最后锁的要么是数据表，要么锁的是索引行。
• 事物的原子性是业务来说的，一个业务对数据库的操作是一组的，不能进行分割，如果分割了，就满足不了业务。
• 而事物的一致性是通过MVCC来实现的，事物的数据库操作是记录undo日志的，如果成功了都会成功，如果不成功就都会失败，一起回滚。
• 持久性则是MVCC操作成功后，操作日志都会被清空，无法进行再次进行回滚

二、高并发事务带来的问题

1. 数据丢失：两个事务同事更新一条数据，第一个事务物先提交，第二个事务后提交，第二个异常回滚，造成第一个事务更新操作也被回滚的情况。

1. 第二类数据丢失：（不可重复读的特殊情况）两个事务都操作同一行，然后两个事务进行写操作，并提交。第一事务做的更改会丢失。

1. 脏读：第二事务查询到第一个事务未提交更新的数据。
2. 不可重复读：一个事务内两次读取同一行数据，结果得到不同的结果。由于隔离性，事务不互相影响，如果这一个事务内两次查询中间正好另一个事务更新该数据，两次结果相异不可信任。
3. 虚读（幻读）：一个事务在执行过程中读取到了另一个事务已提交的插入数据；即在第一个事务开始时读取到一批数据，但此后另一个事务又插入了新数据并提交，此时第一个事务又读取这批数据但发现多了一条，即好像发生幻觉一样。

注意：

• 不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。

• 虚读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而虚读针对的是一批数据整体（比如数据的个数）。不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

   

三、事务的隔离级别

为了解决上面的事务并发的问题，提供了数据库四中事务隔离级别：

级别从高到低依次是：

1. Serializable 串行化：避免脏读、不可重复读，幻读的发生。
2. Repeatable Read 可重复读：可避免脏读、不可重复读的发生。
3. Read Commited 可读已提交：可避免脏读的发生。
4. Read UnCommited 可读未提交：最低级别，任何情况都会发生。

Mysql默认隔离级别是：可重复读：Repeatable read。

oracle只支持seralizable和Read committed两个级别。默认的是Read committed级别。

锁

https://zhuanlan.zhihu.com/p/52694611

https://www.cnblogs.com/luyucheng/p/6297752.html

事务的级别和数据库中的锁都是为了解决高并发造成数据不一致的问题。

一、锁的分类：

1. 锁模式分类：悲观锁、乐观锁
2. 范围锁：行锁、表锁，页锁
3. 算法锁：临键锁、间隙锁、记录锁；
4. 属性锁：共享锁（S、读）、排他锁（X、写）;
5. 状态锁：意向共享锁(表锁)、意向排他锁(表锁)。

二、具体定义

乐观锁和悲观锁都是为了解决并发控制问题。

乐观锁：可以认为是一种在最后提交的时候检测冲突的手段。他是应用系统层面和数据的业务逻辑层次上的，利用程序处理并发问题。乐观锁的实现大部分都是基于版本控制实现的。

悲观锁：一种避免冲突的手段。悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作。悲观锁是由数据库进行实现的，要用的话直接调用数据库相关语句即可。

共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。

共享锁（读锁、S锁）：共享锁指的就是对于多个不同的事务，对同一个资源共享同一个锁。主要是select

排他锁（写锁、X锁）：对于多个不同的事务，对同一个资源只能有一把锁。与共享锁类型，在需要执行的语句后面加上for update就可以了。

行锁：某一行加上锁，也就是一条记录加上锁，是数据库机制上的锁。行级锁分为共享锁和排他锁两种。

表锁：给表加上锁，也就是直接锁住一张表，操作未完，不允许其他操作进行更改。

页锁：介于行锁跟表锁之间。

记录锁：他专门用来封锁某条索引记录。

间隙锁：他专门用来封锁索引记录区间。间隙锁只会锁定区间不锁定自己

临键锁：他是记录锁跟间隙锁的组合，他即封锁记录，有包含索引空间。临键锁会封锁索引记录本身，以及索引记录之前的区间。

例如：

select * from table where id=1 for update; 是记录锁，封锁的是 id=1的这条索引记录。

select * from table where id between 1 and 10 for update；是间隙锁，封锁的是Id[1.10]这个区间的索引

意向锁：意向锁是放置在资源层次结构的一个级别上的锁，以保护较低级别资源上的共享锁或排它锁。意向锁是表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。

InnoDB 中的两个表锁：

意向排他锁：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的 IS 锁。如果需要对记录 A 加共享锁，那么此时 InnoDB 会先找到这张表，对该表加意向共享锁之后，再对记录 A 添加共享锁。

意向共享锁：类似上面，表示事务准备给数据行加入排他锁，也就是说事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。如果需要对记录 A 加排他锁，那么此时 InnoDB 会先找到这张表，对该表加意向排他锁之后，再对记录 A 添加排他锁。

xxxxxxxxxx
1、意向锁是 InnoDB 自动加的，不需要用户干预。
2、共享锁跟排他锁都是行锁，锁的都是行记录。意向共享锁跟意向排他锁都是表锁，锁的都是表记录。

三、行锁与表锁的区别

• 锁的粒度就是指锁的生效范围。他们的名字代表了颗粒度的大小。
• 表级锁：开销小，加锁快；不会出现死锁；锁粒度大，发生锁冲突的概率最高，并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁粒度最小，发生锁冲突的概率最低，并发度最高。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁粒度界于表锁和行锁之间，并发度一般。

四、手动加锁

• 添加排他锁（写锁、X锁）：select ... for update

• 添加共享锁（读锁、S锁）：select ... in share mode

   

注意：

InnoDB 的锁，与索引类型，事务的隔离级别相关。InnoDB 到底是行锁还是表锁取决于你的 SQL 语句。如果查询没有命中索引，也将退化为表锁。InnoDB 的行锁是实现在索引上的，而不是锁在物理行记录上。所以如果访问没有命中索引，也无法使用行锁，将要退化为表锁

完善附加：https://zhuanlan.zhihu.com/p/53923661 数据库是如何加锁的？

MVCC

Multi-Version Concurrency Control 多版本并发控制

https://zhuanlan.zhihu.com/p/53921376

锁的实现了并发操作么？ 通过以上，咱们明白的数据库的并发访问控制是通过锁来实现的。仔细来看这是并发么？不是，通过锁实现的其实是，读的时候不能写（允许多个线程同时读，即共享锁，S锁），写的时候不能读（一次最多只能有一个线程对同一份数据进行写操作，即排它锁，X锁）。这样的加锁访问，其实并不算是真正的并发，或者说它只能实现并发的读，因为它最终实现的是读写串行化，这样就大大降低了数据库的读写性能。加锁访问其实就是和MVCC相对的LBCC，即基于锁的并发控制（Lock-Based Concurrent Control），是四种隔离级别中级别最高的Serialize隔离级别。为了提出比LBCC更优越的并发性能方法，MVCC便应运而生。

面试题：

https://www.cnblogs.com/diffrent/p/8854995.html