数据库三大设计范式
-
第一范式 1NF：数据表的所有字段都是不可拆分的原子值。
比如：地址可以拆分为 国家、省份、市区、街道、门牌号等。不应该写到一起。方便统计。
-
第二范式 2NF：满足 1NF 前提下，主键外的每一列都必须完全依赖于主键。如果不完全依赖，只可能是联合主键的情况。
create table myorder( product_id int, customer_id int, product_name varchar(20), customer_name varchar(20), primary key(product_id, customer_id) );除主键外的其他列，只依赖于主键的部分字段。pName之和pId有关，cName之和cId有关。
拆表。
create table myorder( order_id int primary key, product_id int, customer_id int, ); create table product( id int primary key, name varchar(20), ); create table customer( id int primary key, name varchar(20), ); -
第三范式 3NF：满足 2NF 前提下，除主键列外其他列之间不允许有传递依赖关系。
create table myorder( order_id int primary key, product_id int, customer_id int, customer_phone varchar(15) );cPhone和cID是唯一相关的，产生了冗余。
事务管理
**事务定义：**一个最小的不可分割的工作单元。能够保证一个业务的完整性。
存储引擎（表类型？）：在mysql的数据用不同的技术存储在文件（内存）中。
- show engines查看。
- 最多是innodb，myisam，memory等。innodb支持事务，myisam，memory不支持。
基本术语：
- 事务（transaction）指一组 SQL 语句；
- 回退（rollback）指撤销指定 SQL 语句的过程；
- 提交（commit）指将未存储的 SQL 语句结果写入数据库表；
- 保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。
事务四大特征ACID
A：原子性atomicity：事务是最小的单位，不可分割。同一事务的sql语句必须保证同时成功或同时失败。
C：一致性consistency：数据库在事务执行前后都保持一致性状态，事务开始和结束之间的中间状态不会被其他事务看到。事务的修改必须满足相关的数据规则，保证数据完整性。事务结束后，所有内部数据结构（B树索引和双向链表）必须是正确的。所有事务对同一个数据的读取结果都是相同的。
I：隔离性isolation：保证事务执行能不被其他并发操作影响独立运行，即事务内部的操作和使用的数据对并发的其他事务是隔离的。
D：持久性durability：事务一旦结束提交，会永久的改变数据库，即使出现系统故障也能保持。
事务隔离性：隔离级别越高，性能越差。
- 更新丢失，a、b同时修改一张表，只保留了最后一次结果。在一个事务未提交前，另一个事务不能访问同一文件。
- read uncommitted：读未提交的——脏读。b读到a 已修改但未提交的数据。违反一致性
- read committed：读已提交的——不可重复读。b读到a 已修改并提交的数据。导致b认为前后不一致。违反隔离性。
- repeatable read：可以重复读——幻读。默认级别。b读到a 提交的新增数据。a插入数据，导致b两次读到的数据不同。b读不到a已经提交的数据，导致插入操作出错。违反隔离性。
- serializable：串行化。当表被事务a操作时，其他事务b的写操作会卡住（会等待超时），进入排队状态。当事务a结束后，事务b的写操作才会执行。效率很低。
查询隔离级别：8.0用transaction_isolation； 5.0用tx_isolation
- 系统级别：select @@global.transaction_isolation;
- 会话级别：select @@transaction_isolation;
- 修改系统：set global transaction isolation level read uncommitted；
- 修改会话：set session transaction isolation level read uncommitted；
事务一致性：
- 强一致性：读操作可以立即读到提交的更新操作。
- 弱一致性：提交的更新操作，不一定立即会被读操作读到，此种情况会存在一个不一致窗口，指的是读操作可以读到最新值的一段时间。
- 最终一致性：是弱一致性的特例。事务更新一份数据，最终一致性保证在没有其他事务更新同样的值的话，最终所有的事务都会读到之前事务更新的最新值。如果没有错误发生，不一致窗口的大小依赖于：通信延迟，系统负载等。
- 其他一致性变体还有：
- 单调一致性：如果一个进程已经读到一个值，那么后续不会读到更早的值。
- 会话一致性：保证客户端和服务器交互的会话过程中，读操作可以读到更新操作后的最新值。
事务原子性
-
实现事务的原子性，要支持回滚操作，在某个操作失败后，回滚到事务执行之前的状态。
-
回滚实际上是一个比较高层抽象的概念，大多数DB在实现事务时，是在事务操作的数据快照上进行的（比如，MVCC），并不修改实际的数据，如果有错并不会提交，所以很自然的支持回滚。
-
在其他支持简单事务的系统中，不会在快照上更新，而直接操作实际数据。可以先预演一边所有要执行的操作，如果失败则这些操作不会被执行，通过这种方式很简单的实现了原子性。
自动提交：
show variables like '%autocommit%';
set autocommit=0;
START TRANSACTION
# sql语句。只针对增删改查，create、drop不行。
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT
注意：
- 不能回退 SELECT 语句，回退 SELECT 语句也没意义；
- 不能回退 CREATE 和 DROP 语句。
- truncate不能回滚，delete可以。
- MySQL 的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。
- 设置 autocommit 为 0 可以取消自动提交；autocommit 标记是针对每个连接而不是针对服务器的。
- 如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。
案例
比如a给b转账100：
update user set money=money-100 where name='a';
update user set money=money+100 where name='b';
要求两个语句必须同时成功或者同时失败。
- mysql默认开启事务（自动提交）select @@autocommit； – 1
- 开始事务时，执行sql语句后立即生效，不能回滚。 rollback；–无效
- 关闭事务：set autocommit=0；
- 关闭事务时，执行sql语句后，需要手动提交才不能回滚。commit；
- 在没有commit前，rollback会回滚所有语句。所以转账时，可以两条语句都执行完毕后再commit。
**手动开启事务：**在最开始，输入begin； 或者 start transaction；此后可以rollback；
逻辑架构
应用程序——连接池——缓存（读信息）和缓冲（写信息）——读不到信息，到sql接口——解析器——优化器，生成执行计划——存储引擎——返回客户端，并在缓存存一份。
启动缓存：
- 修改配置文件：vim /etc/my.cnf
- 新增 query_cache_type=1 （修改字符为：character_set_server=utf8）
- esc —— :wq
- 重启mysql：systemctl restart mysqld
- 检查一下是否active：systemctl status mysqld
- 开启profile：
- show variables like ‘%profiling%’
- set profiling=1
- 执行语句 select。。。
- 查看profiles
- show profiles； ——显示语句id，耗时，命令
- show profile cpu.block io for query 语句id； ——status。两次查看可以看到第二次查询时，直接在缓存中查找。（时间变短）
- 想要一样，必须p（sql语句）、v（查询结果）键值对完全一致。
存储引擎
show engines； ——最常用 InnoDB 和 MyISAM
分类：
- InnoDB ：mysql的默认事务型引擎，用来处理大量短期事务。优先使用。
- MyISAM：提供大量特性，如全文索引、压缩、空间函数GIS等。不支持事务和行级锁。崩溃后无法安全恢复
- Archive：档案存储引擎支持insert和select操作。适合日志和数据采集类应用。比MyISAM表小75%，比InnoDB 表小83%。
- Blackhole：没有实现任何存储机制，它会丢弃所有插入数据，不做任何操作。服务器会记录Blackhole表的日志，可以用于复制数据到备库，或者简单地记录到日志。这种应用方式会有很多问题。
- CSV：可以作为普通的csv文件作为MySQL表来处理，不支持索引。可以作为数据交换机制。用于报表系统，文本。
- Memory：用于快速访问数据，且数据不会修改，不在意重启数据丢失。
- Federated：访问其他MySQL数据库的一个代理，很多问题，默认禁用。
InnoDB 和 MyISAM的区别：
| MyISAM | InnoDB | |
|---|---|---|
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，即使操作一条记录也会锁住整个表，不适合高并发 | 行锁，操作时只锁住某一行，不对其他行有影响，适合高并发（会出现死锁） |
| 缓存 | 只缓存索引，不缓存真实数据 | 缓存索引和真实数据，对内存要求高，内存大小对性能有决定性影响 |
| 关注 | 节省资源、消耗少、简单业务 | 并发写、事务、更大资源 |
| 默认安装 | 是 | 是 |
| 默认使用 | 否 | 是 |
| 系统表 | 是 | 否 |
详细版：
| MyISAM | Innodb | |
|---|---|---|
| 存储结构 | 每张表被存放在三个文件：frm-表格定义、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件 | 所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB |
| 存储空间 | MyISAM可被压缩，存储空间较小 | InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引 |
| 可移植性、备份及恢复 | 由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作 | 免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了 |
| 文件格式 | 数据和索引是分别存储的，数据.MYD，索引.MYI | 数据和索引是集中存储的，.ibd |
| 记录存储顺序 | 按记录插入顺序保存 | 按主键大小有序插入 |
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的） | 表级锁定 | 行级锁定、表级锁定，锁定力度小并发能力高 |
| SELECT | MyISAM更优 | |
| INSERT、UPDATE、DELETE | InnoDB更优 | |
| select count(*) | myisam更快，因为myisam内部维护了一个计数器，可以直接调取。 | |
| 索引的实现方式 | B+树索引，myisam 是堆表 | B+树索引，Innodb 是索引组织表 |
| 哈希索引 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
MyISAM索引与InnoDB索引的区别？
- InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
- InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效。
- MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
- InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会非常高效。
InnoDB引擎的4大特性
- 插入缓冲（insert buffer)
- 二次写(double write)
- 自适应哈希索引(ahi)
- 预读(read ahead)
存储引擎选择
如果没有特别的需求，使用默认的Innodb即可。
MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。
Innodb：更新（删除）操作频率也高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。
索引优化
性能下降
- 数据太多——分库分表
- 关联太多表，太多join——sql优化
- 没有充分利用索引
- 服务器调优及各个参数设置——调整my.cnf
索引（Index）：
- 帮助MySQL高效获取数据的数据结构。 可以简单理解为排好序的快速查找数据结构
- 索引本身也很大，不可能全部存到内存中，往往以索引文件的形式存到磁盘上
- **优点：**提高数据检索效率，降低IO成本。索引对数据进行排序，降低排序成本，CPU消耗。
- **缺点：**提高了查询速度，降低更新、插入、删除的速度（需要同步更新索引信息）。索引也是一张表，保存了主键与索引字段，并指向实体表的记录，占用空间。
索引结构：平衡树（二叉树在极端情况下变成链表，平衡树会旋转）
- BTree：
- B+Tree：发生的IO次数更低。
- 聚簇索引（数据行和相邻键值聚簇的存储在一起）和非聚簇索引
索引分类：
- 单值：一个索引只包含单个列，可以有多个
- 唯一：索引列的值必须唯一，允许空值。可以有多个null
- 主键：设定为主键，数据库会自动建立索引，InnoDB为聚簇索引
- 复合：一个索引包括多个列
索引语法：
- 查看：show index from 表名;
- 创建：create [unique] index [索引名] on 表名(字段名);
- 修改：alter table 表名 add unique 索引名(列);
- 删除：drop index [索引名] on 表名
什么时候用索引：
- 主键自动建立唯一索引
- 频繁查询的字段应该创建索引
- 查询与其他表关联的字段应该创建索引，外键
- 组合索引比单键索引性价比高。MySQL只会选择一个索引，组合索引才能都用起来。
- 排序字段应该建立索引
- 统计或者分组字段
不用索引
- 记录很少
- 经常删改
- where中用不到的字段
- 过滤性不好的字段。如性别
explain
模拟优化器执行SQL语句，分析查询语句或表结构的性能瓶颈。
- 功能:
- 表的读取顺序
- 哪些索引可用
- 数据读取操作的操作类型
- 哪些索引被引用
- 表之间的引用
- 每张表多少行被物理查询
- 怎么做
- explain sql语句（select…）
- 总：id一个最好。type，查看是否为all或者range。key_len，索引长度，越长越好。rows：越短越好。extra，order、group、union等。
- id：id一样从上到下，id不一样（子查询），id越大优先级越高，先执行。每一个id号表示一次独立的查询，一个sql的查询次数越少越好。
- select_type：
- simple：简单查询，不包含子查询或union
- primary：主要查询，查询中包含复杂的子部份，最外层标记为primary
- derived：衍生查询，在from中包含的子查询标记为derived，MySQL会递归执行这些子查询，并把结果存到临时表中。
- subquery：子查询，在select或where中包含子查询，用=连接。
- dependent subquery：依赖子查询，在select或where中包含子查询，用in连接。
- uncacheable subquery：不可用缓存的子查询，sql一模一样就可以用缓存，若sql不可能一样，则标记为uncacheable 。如包含系统变量
- union：联合查询，在第二个select出现在union后，标记为union。若union包含在from子句的子查询中，外出标记为derived
- union result：联合查询结果，从union表获取的结果。
- table：显示表名 或（临时表）
- partitions：代表分区表的命中情况，非分区表，该项为null
- type：访问类型排列。显示查询使用的类型，system>const>eq_ref>ref>range>index>all。最差应该达到range，最好是到ref。
- system：表中只有一行记录（等于系统表），一般不会出现
- const：通过索引一次就找到，用于比较primary key或unique索引。（索引值为常量，id=1）
- eq_ref：唯一性扫描索引，对每一个索引键，表中只有一条记录与之匹配。（索引值为主键或唯一键）
- ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上是一种索引访问，但可能会找到多个符合条件的行，属于查找和扫描的混合体（用=连接，且不是主键或唯一键）
- range：范围查询，只检测给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引。一般在where中出现between、<、>、in等查询。
- index：全索引扫描，只遍历索引树。通常索引文件比数据文件小，所以index比all快。index和all都是读全表，但是index是从索引读取，all从硬盘读取。一般是使用了 覆盖索引 或者 利用索引进行了排序分组。
- all：全表扫描，效率极低，必须建立索引
- index_merge：查询需要多个索引组合。（用到or连接）
- ref_or_null：某个字段需要关联条件，也需要null的情况。（or连接，且其中一个为null）
- index_subquery：子查询用到索引，不需要关联扫描
- unique_subquery：子查询中唯一索引。（主键或唯一键）
- possible_keys：显示可能应用在这张表中的索引，一个或多个 。查询涉及到的字段若存在索引，则将给索引列出。但是不一定被查询使用。
- key：实际使用的索引。如果为null，没有用索引。查询中若使用了覆盖索引，仅显示在key列表中，possible_keys没有。
- key_len：索引长度。where后面的筛选条件命中索引的长度，越小越好。如，int长度4字节，加上null，5字节。varchar(20)长度20字节，gbk*2，utf8*3，动态字符最后加2字节。允许为空的字符加1字节。
- ref：显示索引的哪一列被使用。哪些列或常量被用于查找索引列上的值。（没多少意义）
- rows：MySQL认为执行查询时必须检查的行数，越小越好。模拟值。
- filtered：存储引擎返回的数据中server层过滤后，剩下满足查询记录数量的比例。百分比。
- extra：包含不适合在其他列中显示但十分重要的额外信息。（order by、group by）
- using filesort：文件排序，order by没用上索引。
- using temporary：新建临时表保存中间结果。group by没用上索引。group by包含order by，更慢。
- using index：使用了覆盖索引，避免访问了表的数据行，效率不错。同时出现using where表明索引被用来执行索引键值的查找。没有出现using whereusing where表明索引只是用来读取数据而非利用索引执行查找。
- using where：使用了where过滤
- using join buffer：union没用上索引。关联字段增加索引（on后面）。
- impossible where：逻辑错误。
- select tables optimized away：没有group by的情况下，基于索引优化min/max操作或者对于MyISAM存储引擎优化count(*)操作。查询执行计划生成的阶段即完成优化。
- distinct：优化distinct操作，在找到第一匹配的元组后，停止再找相同值的操作。
覆盖索引：covering index
- select的数据列只用从索引中取得，不必读取数据行。MySQL可以利用索引返回select列表中的字段，不必根据索引再次读取数据文件。查询列被索引覆盖。
- 注意：
- 要用覆盖索引，select列表中不要用*，而只取需要的列。
- 如果将所有字段一起做成索引，会导致索引文件过大，查询性能下降。
单表使用索引和常见索引失效
1、改变顺序不影响用到索引，优化器会优化。但是缺少了索引开头的字段，后面的就用不了。
2、最佳左前缀法则。索引建立平衡树是分层的。先建立age树，后建立deptid树。deptid树的建立是在age相同的情况下的。
3、函数或计算索引失效。如 name like '张%'能用到， left(name, 1)='张'用不到索引
4、范围查询后面的字段失效。如：where name='abc' and deptid>4 and age=35; # age失效。建立索引时范围查询应该放到后面。如日期、时间、能比较的字段等。
5、不等于索引失效。如：where name<>'abc'。
6、 is not null索引失效。 is null可以用。
7、模糊查询首字符未知，索引失效，索引创建根据字符顺序的。如： like '%abc%'; 可以用覆盖索引优化。 like是范围，但是不会导致后面的失效。
8、自动或手动类型转换索引失效。如：name=123，如果 name是 varchar，匹配 int失效。
9、用 or之后索引失效。
# 没有索引，全表扫描all
explain select sql_no_cache * from emp where emp.age=30; # sql_no_cache不走cache
create index idx_age on emp(age)； # 为age创建索引，type变为ref，key_len，rows变化。
explain select sql_no_cache * from emp where emp.age=30 and drptid=4; # 增加条件
create index idx_age_deptid on emp(age, deptid)； # 联合索引，ref
一般性建议：
- 单键索引尽量选择当前query过滤性更好的索引。性别过滤性不好，学号、身份证、手机号好。跟业务不相关的自增最好。
- 选择组合索引时，当前query中过滤性最好的字段在索引字段顺序越靠前越好
- 选择组合索引时，尽量选择可以包含当前query中where字句中更多字段的索引
- 选择组合索引时，如果某个字段可能出现范围查询，应该放到最后
- 避免索引失效
关联查询优化
- 关联查询分为：驱动表、被驱动表。驱动表必须全表扫描，建立索引无效。被驱动表建立索引有效。
- 驱动表和被驱动表会换顺序，在inner join中，会根据是否有索引更换，优化查询，left join就没办法了。
- straight_join指定驱动表和被驱动表，不可以换，以免大表中有主键，导致mysql选择大表作为被驱动表。使用时，必须明确表的数量级关系不会变。
- 小表为驱动表，大表为被驱动表。提高效率。
- 虚拟表（子查询的结果）不能建立索引，不能放到被驱动表上。
- 能直接关联尽量直接关联，不要用子查询。子查询多趟查询。
子查询优化：进行不要用not in或not exists。
- 一个表有，另一个表没有，用关联查询代替
# 查询非ceo信息，emp表有信息和id，dept有ceo的id。
select * from emp a
where a.id not in (
select b.ceo from dept b
where b.ceo is not null
);
# 优化：不用子查询，is not null改为is null
select * from emp a
left join dept b
on a.id=b.ceo
where b.id is null
# 小表驱动大表，in 和 exists
select * from A where id in （ select id from B）# 当A表大于B表时，in好
select * from A where exists（ select 1 from B where A.id=B.id） # A表小于B表时，exists好
1、 exists(subquery) 返回 True或 False，子查询列表可以为1或其他常数。代码会忽略select清单。
2、 exists子查询往往可以用条件表达式、其他子查询、join代替。
3、 exists子查询实际执行可能会被优化，需要实际检验确定效率问题。
排序分组优化
index(age，birth) # 查看是否using filesort
1、where age>20 order by age; # 没有
2、where age>20 order by age,bitrh; # 没有
3、where age>20 order by bitrh; # 有
4、where age>20 order by bitrh,age; # 有
# 结论：范围查询会破坏索引的排序功能
order by用到索引的条件：
- 必须有过滤条件，where或者limit。
- 排序字段的顺序必须和索引一致，因为order by调整顺序的结果不一样，不能更换顺序。
- 升降序必须只选一个。都升序或都降序。
group by和order by几乎一样。但是group by即使没有过滤条件，也可以直接用索引。
filesort有两种算法
- 双路排序：MySQL4.1之前，扫描两次磁盘。从磁盘读行指针和order列，在buffer对他们排序，然后扫描已经排序好的列表。按照列表的值重新从磁盘中读取数据输出。
- 单路排序：从磁盘读取需要的所有列，在buffer对order列排序，扫描排序后的列表输出。避免二次读取数据，把随机IO变成顺序IO，效率高。但是会占用更多空间，而且一旦超出sort_buffer容量，会导致多次IO，性能更差。
- 增加sort_buffer_size
- 增加max_length_for_sort_data
提高order by的速度：
- select * 只query需要的字段。
- query字段大小总和小于max_length_for_sort_data，且排序字段不是text|blob类型时，才用单路排序，否则用多路排序。
- 两种算法都可能超出sort_buffer容量，超出后，会创建tmp文件进行合并排序，导致多次I/O，但单路排序风险更大，需要提高sort_buffer_size。
- 尝试提高sort_buffer_size。这个参数针对每个进程的1M-8M之间调整。
- 尝试提高max_length_for_sort_data。会增加用改进算法的概率。如果太高，数据总容量超出sort_buffer_size的概率增大，导致高磁盘I/O活动和低处理器效率。1024-8192之间调整。
**覆盖索引：**不要用select *，写出需要的具体字段。
# 组内排序：
找emp员工表中年龄第二大的人。
set @rank=0;
set @last_deptid=0;
select a.deptid, a.name, a.age
from (
select t.*, if(@last_deptid=deptid, @rank:=@rank+1, @rank:=1) as rk,
@last_deptid:=deptid as last_deptid
from emp t
order by deptid, age desc
) a
where a.rk=2;
慢查询日志
使用
- 文件在 /var/lib/mysql/主机名-slow.log
- MySQL提供的日志记录，用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_quey_time值的sql。long_quey_time默认为10，指10s。
- 默认关闭：show variables like ‘%slow_query_log%’;
- 开启：set global slow_query_log=1;
- 修改配置文件永久生效。my.cnf 的 [mysqld]下增加 slow_query_log=1 和 slow_query_log_file=/var/lib/mysql/主机名-slow.log
- 阈值时间：show variables like ‘long_quey_time%’;
- 修改阈值：set long_quey_time=0.1;
- 查看记录：cat /var/lib/mysql/主机名-slow.log
- 查询当前有多少慢查询记录：show global status like ‘%Slow_queries%’
日志分析工具
-
mysqldumpslow。 # 用来进行筛选，分析等。
-
mysqldumpslow --help # 查看帮助
-
mysqldumpslow -s c -t 3 -a 日志文件 # 计算c个数、t时间，t显示top3，a显示数字和字符
-
s：何种方式访问，c：访问次数，I：锁定时间，r：返回记录，t：查询时间，al：平均锁定时间，ar：平均返回记录数，at：平均查询时间，t：返回前面多少条数据，g：搭配正则表达式，大小写不敏感等。
-
可以配合管道和more
show profile
- mysql提供的分析会话中语句执行的资源消耗情况。用于sql调优的测量。
- 默认关闭，并保存最近15次运行结果。show variables like ‘%profiling%’;
- 开启功能：set profiling=on；
- 查看结果：show profiles； # 展示query_id，耗时、sql代码
- 诊断sql：show profile cpu, block io for query query_id； # sql的完整生命周期和过程
- converting HEAP to MyISAM查询结果太大，内存不够用，往磁盘搬了。
- Creating tmp table 创建临时表
- 拷贝数据到临时表
- 用完再删除
- Copying to tmp table on disk 把内存的临时表复制到磁盘。
- locked
全局查询日志
- 启用：set global general_log=1; set global log_output=‘TABLE’;
- 查看：select * from mysql.general_log；# mysql 系统表
- 永远不要在生产环境打开
进程
- 展示进程列表：show processlist;
- 杀掉进程：kill 进程id
数据锁
锁是计算机协调多个进程或线程并发访问某一资源的机制。
数据库中，除了传统计算资源（cpu、ram、io等）争用外，数据也是一种用户共享资源。保证数据并发访问一致性、有效性是数据库必须解决的问题。
分类
- 数据操作类型
- 读锁（共享锁 Shared）S锁：对同一份数据，多个读操作可以同时进行、互不影响。
- 写锁（互斥锁 Exclusive）X锁：当前写操作没完成前，阻断其他写锁和读锁。
- 数据操作粒度：行锁、表锁
三锁：（开销、加锁速度、死锁、粒度、并发性能）
-
引擎
- InnoDB实现行级锁定，性能损耗比表级锁定高一些。但是在高并发情况下，行级锁定就有明显优势。
- MyISAM在查询时，会自动给涉及的表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
-
cap：强一致性、高可用性、分区容错性。p必须保证，ca只能选一个
-
表锁：偏读
- 特点：偏向MyISAM存储引擎，开销小，加锁快，无死锁，粒度大，发生锁冲突概率最高，并发度最低
- 建表：create table 表名 (字段名 数据类型 键)engine myisam；# 指定存储引擎
- 增加表锁：lock table 表名 read/write，表名2 read/write，…；
- 读锁：本会话，可读被锁表，不能读其他表。不能写操作。其他会话，可读被锁表，可读其他表，写操作进入阻塞状态。
- 写锁：本会话，可读被锁表，不能读其他表，可写被锁表。其他会话，读写操作都会进入阻塞状态。
- 查看表锁：show open tables； # in_use 为1表示锁，0表示m
- 删除表锁：unlock tables；
- MyISAM在查询时，会自动给涉及的表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
- 分析表锁定：show status like ‘table%’;
- table_locks_immediate：立即释放表锁次数。
- table_locks_waited：需要等待的表锁数，表示表级锁的争用情况。
- MyISAM的读写锁调度是写优先，不适合作为主表的引擎。写锁后，其他线程不能操作，如果有大量更新操作会使查询阻塞。
- 如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎，
因为InnoDB是行锁而MyISAM是表锁，对于高并发写入的应用InnoDB效果会好些。
-
行锁：偏写
- 特点：偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，粒度小，发生锁冲突概率最低，并发度最高。
- InnoDB对比MyISAM最大不同：InnoDB支持事务（transaction），并采用行级锁。
- 建表：create table 表名 (字段名 数据类型 键)engine innodb；# 指定存储引擎
-
关闭自动提交：set autocommit=0；
-
更新某一行但不提交，其他会话该行被锁定，进入阻塞。
-
无索引导致行锁升级为表锁。比如，本会话执行时，出现了索引失效（函数、类型转换等），导致整张表都被锁起来了。
-
间隙锁：当使用范围条件检索时，范围内已有数据的索引项加锁，范围内不存在的记录叫做“间隙GAP”。innodb也会对这个间隙加锁，称之为间隙锁Next-Key锁。其他会话操作间隙锁也会进入阻塞。
-
单独一行加锁。
- select … for update。# 锁定一行，其他会话不能读写。
- select … lock in share mode; # 锁定一行，其他会话不能读。
-
分析表锁定：show status like ‘innodb_row_lock%’;
- innodb_row_lock_current_waits：当前锁定数量
- innodb_row_lock_waits：总共等待次数
- innodb_row_lock_time：总锁定时间
- innodb_row_lock_time_avg：平均等待时间
- innodb_row_lock_time_max：最长的一次等待时间
-
优化建议:
- 数据检索尽可能用索引完成，避免行锁升级为表锁
- 尽量缩小锁的范围
- 尽量少的检索条件，避免间隙锁
- 尽量控制事务大小，减小锁定的资源和时间
- 尽量低事务隔离级别
-
-
页锁：
- 开销和加锁时间、粒度、并发度在表锁和行锁中间，会出现死锁。
封锁协议
1. 三级封锁协议
一级封锁协议：事务 T 要修改数据 A 时必须加 X 锁，直到 T 结束才释放锁。可以解决丢失修改问题，因为不能同时有两个事务对同一个数据进行修改，那么事务的修改就不会被覆盖。
二级封锁协议：在一级的基础上，要求读取数据 A 时必须加 S 锁，读取完马上释放 S 锁。可以解决脏读问题，因为如果一个事务在对数据 A 进行修改，根据 1 级封锁协议，会加 X 锁，那么就不能再加 S 锁了，也就是不会读入数据。
三级封锁协议：在二级的基础上，要求读取数据 A 时必须加 S 锁，直到事务结束了才能释放 S 锁。可以解决不可重复读的问题，因为读 A 时，其它事务不能对 A 加 X 锁，从而避免了在读的期间数据发生改变。
2. 两段锁协议
加锁和解锁分为两个阶段进行。
可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。串行执行的事务互不干扰，不会出现并发一致性问题。
事务遵循两段锁协议是保证可串行化调度的充分条件。例如以下操作满足两段锁协议，它是可串行化调度。
lock-x(A)...lock-s(B)...lock-s(C)...unlock(A)...unlock(C)...unlock(B)
但不是必要条件，例如以下操作不满足两段锁协议，但它还是可串行化调度。
lock-x(A)...unlock(A)...lock-s(B)...unlock(B)...lock-s(C)...unlock(C)
多版本并发控制
Multi-Version Concurrency Control, MVCC，是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现读已提交和可重复读这两种隔离级别。而读未提交隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。
基本思想
-
实际场景中读操作往往多于写操作。读和读没有互斥关系。读和写操作是互斥的。
-
MVCC 利用了多版本的思想，写操作更新最新的版本快照，而读操作去读旧版本快照，没有互斥关系。
-
在 MVCC 中事务的修改操作（DELETE、INSERT、UPDATE）会为数据行新增一个版本快照。
-
脏读和不可重复读原因：事务读取到其它事务未提交的修改。MVCC 规定只能读取已经提交的快照。
Undo日志
MVCC 的多版本指的是多个版本的快照，快照存储在 Undo 日志中，该日志通过回滚指针 ROLL_PTR 把一个数据行的所有快照连接起来。
快照中除了记录事务版本号 TRX_ID 和操作之外，还记录了一个 bit 的 DEL 字段，用于标记是否被删除。
ReadView
MVCC 维护了一个 ReadView 结构，主要包含了当前系统未提交的事务列表 TRX_IDs {TRX_ID_1, TRX_ID_2, …}，还有该列表的最小值 TRX_ID_MIN 和 TRX_ID_MAX。
在进行 SELECT 操作时，根据数据行快照的 TRX_ID 与 TRX_ID_MIN 和 TRX_ID_MAX 之间的关系，从而判断数据行快照是否可以使用：
- TRX_ID < TRX_ID_MIN，表示该数据行快照时在当前所有未提交事务之前进行更改的，因此可以使用。
- TRX_ID > TRX_ID_MAX，表示该数据行快照是在事务启动之后被更改的，因此不可使用。
- TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX，需要根据隔离级别再进行判断：
- 提交读：如果 TRX_ID 在 TRX_IDs 列表中，表示该数据行快照对应的事务还未提交，则该快照不可使用。否则表示已经提交，可以使用。
- 可重复读：都不可以使用。因为如果可以使用的话，那么其它事务也可以读到这个数据行快照并进行修改，那么当前事务再去读这个数据行得到的值就会发生改变，也就是出现了不可重复读问题。
在数据行快照不可使用的情况下，需要沿着 Undo Log 的回滚指针 ROLL_PTR 找到下一个快照，再进行上面的判断。
快照读与当前读
1. 快照读
MVCC 的 SELECT 操作是快照中的数据，不需要进行加锁操作。
SELECT * FROM table ...;
2. 当前读
MVCC 其它会对数据库进行修改的操作（INSERT、UPDATE、DELETE）需要进行加锁操作，从而读取最新的数据。可以看到 MVCC 并不是完全不用加锁，而只是避免了 SELECT 的加锁操作。
INSERT;
UPDATE;
DELETE;
在进行 SELECT 操作时，可以强制指定进行加锁操作。以下第一个语句需要加 S 锁，第二个需要加 X 锁。
SELECT * FROM table WHERE ? lock in share mode;
SELECT * FROM table WHERE ? for update;
视图
- 将查询sql封装成一个虚拟表
- 虚拟表只保留sql逻辑，不保存查询结果
- 作用
- 封装复杂sql语句，提高复用性
- 逻辑放到数据库上，更新不需要发布新程序，更灵活
- 适用于很多地方公用一组查询结果。报表
- 创建修改视图：create or replace view 视图名 as select …
主从复制
- slave会从master读取binlog来进行数据同步
- 三步骤
- master数据改变，并记录到二进制日志binary log中。这个记录过程称为二进制日志事件binary log events
- slave的IO线程将master的二进制日志事件拷贝到它的中继日志relay log
- slave的SQL线程重做中继日志的事件，并将改变应用到自己的数据库中。MySQL复制是异步、串行化的。
- 复制的基本规则
- 每个slave只有一个master
- 每个slave只能有唯一一个服务器ID
- 每个master可以有多个slave
- 一主一从配置
- 主从的MySQL版本一致，同一网段
- 配置到 [mysqld] 结点下，my.ini 和 my.cnf 配置文件。
- 主机配置：
- [必选]：主服务器唯一ID：server-id=1
- [必选]：启用二进制文件：log-bin=路径/data/mysqlbin
- [可选]：启用错误日志：log-err=路径/data/mysqlerr
- [可选]：根目录：basedir=路径
- [可选]：临时目录：tmpdir=路径
- [可选]：数据目录：datadir=路径/Data
- 读写都可以：read-only=0
- [可选]：不复制的数据库：binlog-ignore-db=mysql
- [可选]：要复制的数据库：binlog-do-db=数据库
- binlog_format：
- statement：记录所有写操作sql。若sql存在函数，可能造成主从复制不一致
- row：记录每一行的改变。若sql改变的行数特别多，那就每一行都要记录。
- mixed：有函数用row，没有函数用statement。系统变量无法复制。
- 从机配置：
- [必选]：从服务器唯一ID：server-id=2
- [可选]：启用二进制日志：log-bin=mysql-bin
- 重启数据库：service mysql stop。 service mysql start
- 主从机关闭防火墙：service iptables stop 。或开放端口
- 主机授权从机：
- grant replication slave on *.* to ‘用户’@‘从机数据库ip’ identified by ’密码‘；
- 刷新命令：flush privileges；
- 查看主机状态：show master status；# file：二进制文件，position：起始接入点。忽略数据库、复制数据库。
- 从机配置主机
- change master to master_host=’主机ip‘, master_user=‘用户名’, master_password=‘密码’, master_log_file=‘mysqlbin.数字’, master_log_pos=数字；
- 启动：start slave；
- 查看从机状态：show slave status； # Slave_IO_Running 和 Slave_SQL_Running 必须同时为Yes。
- 停止：stop slave；
批量数据脚本
- show variables like ‘log_bin_trust_function_creators’; # MySQL二进制日志，主从复制
- 在主从复制过程中，函数执行可能结果不一致，如当前时间函数，MySQL默认禁止使用函数。
- set global log_bin_trust_function_creators=1；#允许使用函数
- mysqld重启，参数失效。永久方法，widows下的my.ini[mysqld]中加上，linux下 /etc/my.cnf下的my.cnf[mysqld]加上，log_bin_trust_function_creators=1
- 随机生成字符串函数
- 随机生成数字函数
- 创建插入数据的存储过程
随机生成字符串，n个字符
delimiter $$
create function rand_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcd...xyzABCD...XYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i<n do
set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1));
set i=i+1;
end while;
end $$
删除函数： drop function rand_string
随机生成数字，起始数字到结束
delimiter $$
create function rand_string(from_num int, to_num int) returns varchar(255)
begin
declare i int default 0;
set i=floor(from_num+rand()*(to_num-from_num+1));
return i;
end $$
创建插入数据的存储过程，起始值，最大数据
delimiter $$
create procedure insert_emp(start_id int, max_num int)
begin
declare i int default 0;
set autocommit=0;
repeat
set i=i+1
insert into emp(emp_no, name, age, dept_id) values ((start_id+i), rand_string(6), rand(30, 50), rand_num(1, 10000)); # 随机生成6位姓名，30-50岁，1-1w部门的员工。
until i=max_num
end repeat;
commit;
end $$
调用：
delimiter ;
call insrt_emp(1, 100000); # 创建十万员工
批量删除表的某些索引
查询索引：show index from 表名;
取出索引名：在information_schema 的statistics表内：select index_name from information_schema.statistics where table_name=‘emp’ and index_name<>‘primary’ and seq_in_index=1;
删除索引
delimiter $$
create procedure 'proc_drop_index'(dbname, varchar(200), tablename varchar(200))
begin
declare done int default 0;
declare ct int default 0;
declare _index varchar(200) default '';
declare _cur cursor for select index_name from information_schema.statistics where table_schema=dbname and table_name=tablename and index_name<>'primary' and seq_in_index=1; # 创建游标
declare continue handler for not found set done=2;
open _cur;
fetch _cur into _index; # 取出游标的一个值给index
while _index<>'' do # index不为空
set @str=concat('drop idnex', _index, "on", tablename); # 拼写drop语句字符串
prepare sql_str from @str; # 把字符串预编译为sql语句
execute sql_str; # 执行sql
deallocate prepare sql_str;
set _index='';
fetch _cur into _index;
end while;
close _cur;
end $$
扫一扫
141
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
