MySQL不权威总结
欢迎阅读
本文并非事无巨细的mysql学习资料，而是选择其中重要、困难、易错的部分进行系统地总结梳理，适合有一定mysql使用经验的同学。为了避免枯燥和一叶障目，每一块知识只进行较为抽象的总结，涉及到具体的技术细节会附上相关资料供需要时再具体研究
架构
mysql整体架构分为两层，服务层和存储引擎，服务层负责数据库实例的基本功能（如多线程的连接管理、权限管理、配置管理）和部分粗粒度的查询操作（如查询缓存、SQL解析与优化、部分查询结果的过滤与排序）。存储引擎负责数据存储和读取，索引、事务、锁大部分功能基本都实现在引擎层
查询缓存
mysql的查询缓存比较鸡肋，如果两个查询在任何字符上有所不同（哪怕是注释、大小写），或者包含任何函数、变量，或者相关表发生变化，都会导致缓存失效。官方并不推荐使用查询缓存，并会在后续版本中移除该功能
存储引擎
本质上来说存储引擎的功能就是维护文件的读写，由于提供了标准API，mysql服务层可以方便切换不同的存储引擎，以适应不同场景
myisam与InnoDB比较
mysql有多种存储引擎，常用的是InnoDB和MyISAM，最新版本默认存储引擎为InnoDB，关于两者的区别主要是
- 锁，InnoDB支持行锁、表锁，myisam支持表锁
- 事务，InnoDB支持事务，myisam不支持
- 索引
- InnoDB支持外键，myisam不支持
- InnoDB数据与索引组织在一块（聚簇索引），myisam数据与索引分离，且可以压缩，内存中可以加载更多索引
- myisam支持空间索引、全文索引，InnoDB则需要5.6以上版本
- myisam允许表中不设置主键，InnoDB如果未显式设置主键则会自动选择非null唯一索引作为主键，无则自动创建不可见的6字节rowid作为主键
- 大小，myisam整体文件要小一些
- 安全，InnoDB崩溃后可以自动修复数据
- 读写性能，myisam写性能高于InnoDB，但高并发情况下差别不大；两者读性能无明显差异
存储引擎选择
经过长期优化，InnoDB的读性能并不弱于myisam，全文索引、空间索引也主键支持，而且在安全性、维护性方面也更加优秀。除非是特殊场景，如内存表、分布式、数据归档等可以针对性选择适应的存储引擎，大部分时候使用InnoDB就可以了
表结构
整数
| schema | byte | bit | min ~ max | min ~ max unsigned |
|---|---|---|---|---|
| tinyint | 1 | 8 | -128~127 | 0~255 |
| smallint | 2 | 16 | -32768~32767 | 0~65535 |
| mediumint | 3 | 24 | -8388608~8388607 | 0~16777215 |
| int | 4 | 32 | -2147483648~2147483647 | 0~4294967295 |
| bigint | 8 | 64 | -92...(19位)~92...(19位) | 0~18...(20位) |
整数的表示范围为-2^(N-1) ~ 2^(N-1)-1，如果为无符号整数（unsigned）则0 ~ 2^(N)-1
int(1) int(11)区别
不会影响存储空间和存储范围, 只是规定了mysql的一些交互工具用来显示字符的个数
字符串
- char 定长字符串
- varchar 变长字符串
- text 长文本
字节长度
char(8) varchar(8)都表示8个字符, 跟编码无关。varchar实际占用字节计算方式为：varchar长度 * 编码字节 + 长度记录 + null标志（如果允许null的话），varchar中记录长度的字节在小于255为1, 大于255为2。而char则会自动补空格或者截断
varchar最大字节为65535，即当采用latin编码时最多存65533个字符, utf8时则为2万多。char上限为255字节，text同样为65535，其实当varchar长度大于500时，系统会隐式转换为text
text同族tinytext为256Byte，mediumtext为16MB，longtext为4GB
字符集
不同的字符集表示的字符数量和占用字节都不同。比如常用的utf8可以表示所有字符，占用大小为14字节，其中常用的为13字节，坑爹的是mysql中utf8是utf8mb3的别名，即1~3字节，utf8mb4才是1~4字节，如表情
字符比较规则 collation
utf8_general_ci是一种通用的比较规则，_ci表示不区分大小写
时间
mysql中提供了多种时间类型, year date time datetime timestamp等
datetime
表示范围1001年到9999年
timestamp
表示范围 1970年1月1日(格林尼治标准时间)至 2038年
mysql提供了from_unixtime()把时间戳转换日期, 和unix_timestamp()转换日期为时间戳
可以设置插入或更新数据时的行为
默认情况下尽量使用timestamp, 如果需要处理毫秒级数据可以使用bigint进行hack
枚举
enum（枚举）和set（集合）数据类型并不常用，虽然在恰当的场景可以提高性能，但由于数据调整时需要进行DDL成本较高，而且在排序时容易产生疑惑，所以一般使用char进行替代
SQL
mysql最核心的作用就是提供SQL交互，即增删改查，本小节主要总结mysql的SQL用法，性能优化问题会单独总结，因为我觉得先会跑，再求快。而且很多SQL的用法本身并没问题，所谓的“优化”只是mysql目前还不够聪明导致的，可能在后续迭代中自然而然的消失，所以我觉得SQL使用和优化最后还是分开好一些
count
-
count(col) 统计某个列
值不为null的行数 -
count(express) 统计表达式
值不为null的行数 - count(*) 统计查询结果集行数
曾经有一些广为流传的“神话”，比如count(col)这样指定具体某一列的性能要好于count(*)、myisam的count非常快
其实count(*)会直接明确的统计行数，指定一个列反而需要会慢一些（可能需要检测null等）。而myisam只记录了全表的行数，所以count只要包含查询条件，速度与其他存储引擎差别并不大
distinct
distinct的作用是对结果集去重，只能用在列首，作用于所有列，也可以与count一起使用（count(disctinc col)）
select disctinct col1, col2 where id > 123
group by
group by的作用是对结果集进行聚合，经常与聚合函数count min max sum avg group_concat、聚合结果过滤having一起使用
SQL的执行顺序
理解group先要理解SQL的执行顺序，from > where > group（含聚合）> having > order > limit > select，即先确定查询的表，再筛选结果集，再执行聚合，再having过滤聚合结果，最后order排序。明白这个，几个易错地方就很好理解了
- 聚合函数min max等只是作用于分组内
- 由于where执行在聚合函数前，所以where条件不能包含诸如count(*)>1，having、order则可以
- order的排序并不影响聚合，而是对聚合结果的排序，
所以想通过使用order影响聚合首行行不通，聚合的首行其实是物理存储的首行 - select最后才执行，
所以想通过min、max来影响聚合首行也是行不通的
group_concat可以获取聚合中的某一列所有结果的拼接，个别场景非常好用
union
union的作用是将多个查询的结果合并，要求查询的列名、列数要一致。默认会进行去重，导致filesort，除非明确需要, 否则最好使用Union All
另一个易错的地方是使用union时limit并不是针对union后的结果集，而是单个查询
join
表连接查询是常用操作，简单说就是两个表求笛卡尔积，然后按where条件过滤（当然，真实情况mysql不会真的先求笛卡尔积，而是边连接边过滤，尽可能少的扫描数据）。连接分为内连接和外连接，使用关键字on来限定连接条件
内连接
内连接指当连接生成的行不满足连接条件时，过滤该行，所以内连接中on与where的效果一样，以下是内连接语法
- select * from t1, t2
- select * from t1 inner join t2
- select * from t1 cross join t2
外连接
外连接指当连接生成的行不满足连接条件时，只保留驱动表，被连接表的字段为null。分为左外连接和右外连接
- select * from t1 left join t2
- select * from t1 right join t2
优化
连接查询可以依次连接多个表，最多61个，不过建议不要超过12个，而且最好使用小表驱动大表，连接列最好有索引
索引
索引的目的为加速查询，用好mysql的一个难点就是针对业务场景设计高效的索引。索引类型有很多种哈希索引、BTree索引、全文索引、空间索引，本小节直接以最常用的BTree索引为例说明
索引数据结构
B+Tree
如图所示，多数数据库索引使用的数据结构是B+Tree
- 叶子节点存储着key和data，以及下一个叶子节点
- 非叶子节点分为多层帮助key逐级定位
这是一个非常抽象的示意图，不过已经可以揭示索引本质，帮助理解许多索引的使用和优化了。但如果要更深入的理解mysql查询执行原理和优化，就需要大致了解索引的真实物理结构，如下图所示
- 数据是维护在以页为单位的数据结构中
- 页分为多种，这里能看到存储节点的目录项记录页，和存储叶子节点的用户记录页
- 每个页中数据是一个有序单链表，页间是一个有序的双链表
- 页中记录了每个页中节点的最大、最小值，方便检索
那么一次真实索引查询的大概过程就是
- 在目录项记录页中依次检查每个页的最大值和最小值，判断数据是否在该页中，若不在则链表找下一页
- 逐层定位到数据所在的用户记录页
- 定位到用户记录页后，遍历页中单链表，根据主键找到数据节点
为什么不使用二叉树、红黑树、B-Tree
- 树的高度决定查询需要的随机IO次数
- 二叉树出度为2，会导致树的高度过大，增加顺序IO次数，降低性能；而且二叉树容易出现不平衡，导致平均查询性能下降
- 红黑树虽然平衡，但出度依旧为2
- B-Tree是data保存在内部节点，这就导致页存储的内部节点数量减少，单页能够维护的出度变小，树高增加
为什么以页为基本单位？
这主要是目前计算机硬盘物理特性决定的，系统每次加载数据最少要读取一个页，而且随机IO的性能要比顺序IO差两个数量级，所以mysql巧妙的以页为单位维护数据，尽可能提高IO性能
索引的优点
- 由于目录项页不存实际数据，所以记录数量非常大，一般情况树的深度不会超过4，也就是一次查询最多需要4次随机IO
- 对于范围查询，只需要确定上下边界，顺序IO即可
- 因为索引中数据是有序的，所以order、group都可以利用索引，指数级降低耗时
索引相关概念
聚簇索引
即数据和索引存储在一起，InnoDB的数据表本身就是一个聚簇索引，所以它也只能有一个聚簇索引。优点是找到索引就找到了数据，不需要回表
缺点是
- 假如能够将所有数据放入内存中, 则聚簇索引便无意义了, 因其提供的I/O优势不存在
- 插入速度严重依赖插入顺序，无序插入的话会频繁导致裂页操作
- 更新代价高，可能导致数据在页中交换
- 由此导致产生的二级索引会比想象的大, 因为其必须包含主键索引
- 二级索引查找需要两次索引查找, 因为二级索引中保存的是主键而不是数据
主键索引、一级索引、二级索引
主键索引也叫一级索引，在InnoDB中就是聚簇索引，二级索引指非主键索引，他们同样也是B+Tree数据结构，不同的是叶子节点中存的是主键key，而不是实际数据，定位数据后需要多一次回表即到聚簇索引中查询
回表需要随机IO，性能较差，如果二级索引需要回表的量过大的话，可能导致mysql认为全表扫描成本更低
前缀索引
索引列为字符串时，可以设置索引列的长度，用来平衡索引选择性和索引大小。比如加入索引一个uuid，其实只需要设置字符串的前6位就能获取较好的选择性，没必要把全部列纳入索引
前缀索引虽然可以减小索引长度，放入更多索引至内存。但会导致不能使用索引进行order by和group by，无法使用覆盖索引
前缀索引长度的选择需要考虑索引选择性, 选择性很差还不如不使用。试探索引选择性:
mysql> SELECT
mysql> count(distinct left(city,3))/count(*) as sel3,
mysql> count(distinct left(city,4))/count(*) as sel4,
mysql> count(distinct left(city,5))/count(*) as sel5
mysql> FROM table;
覆盖索引
覆盖索引指索引列包含了查找的全部列，包括where条件和select的列
联合索引
联合索引就是多列索引
三星索引
- 索引将相关的记录放到一起则获得一星
- 索引中的定义顺序和查找中的排序顺序一致则获得二星
- 索引中列包含了查询中需要的全部列则获得三星
MyISAM索引比较
myisam中索引与数据是分开存储的，即主键索引、二级索引叶子节点中存放的都是实际数据地址，而不是实际数据
事务、锁
事务基本概念
ACID
事务要满足ACID四个特性
- 原子性 (atomicity)
- 一致性 (consistency)，事务结束后数据库依然保持约束的一致性，如唯一索引不能重复
- 隔离性 (isolation)，事务之间要相互隔离
- 持久性 (durability)，事务执行结束后就永久保存，不会丢失
保存点
对于一个长事务，可以不断设置保存点，防止回滚导致成本太大。由于mysql不支持事务嵌套，一般也通过保存点来模拟事务嵌套
语法
BEGIN / START TRANSACTION
update...
insert...
SAVEPOINT save_fin_1
update...
ROLLBACK TO save_fin_1 # 回退
update...
SAVEPOINT save_fin_2
RELEASE SAVEPOINT save_fin_2 # 删除
COMMIT / ROLLBACK
自动提交
本来不想写这段，但看到各种事务相关的文章里都人云亦云的加一个所谓的关闭自动提交，就忍不住提一下。autocommit=ON的意思是每一条语句都是一个独立事务，包括增删改查，显式开启事务后自动关闭
隐式提交
mysql中一些操作会导致当前事务隐式提交
- 事务中再开启事务
- 执行DDL
- 加载数据，如LOAD DATA
- 主从操作
redo日志
要实现事务的持久性，则要在事务提交之前，现将数据写入硬盘。mysql为了提高IO性能，数据库的写操作会先写入内存中的数据页，再异步刷新到硬盘。这样每当事务提交，相关的页就需要刷新到硬盘，写性能压力会非常大，redo日志便是为了解决该问题
其实不需要把整个页刷新，只需要把该页的位级别diff刷新到硬盘即可，这就是redo日志。redo日志很小，大约几mb，一直循环覆盖顺序写入，每当事务提交就刷新内存中的redo脏页到硬盘的redo日志，每当表的脏页刷新到硬盘中时，redo中对应的事务部分就标记为可以覆盖
undo日志
undo日志与redo日志类似，不过是为了实现事务的回滚，每当事务产生一条更新，undo日志就会记录一个对应的撤销sql
锁
锁是实现并发写，和事务隔离的重要机制
锁分类
行锁、表锁
锁按照锁粒度可以分为表级锁和行级锁（还有个页级锁），表锁实现在mysql服务层，与存储引擎无关；行锁实现在存储引擎层，不同存储引擎的支持、实现都有所不同。粒度越小开销越大，并发性越好，出现死锁的概率也越大
横向按照排他性可划分为读锁（共享锁）和写锁（排他锁），写锁的优先级更高，在锁等待队列中可以插队
行锁实现
行锁按照实现算法，又可以分为
- record lock 记录锁
- gap lock 间隙锁
- next-key lock 记录锁+间隙锁
简而言之，记录锁只会锁住记录本身；间隙锁则锁住两个记录的间隙，唯一作用就是防止幻读（mvcc也可解决幻读）；next-key lock则是锁住记录本身以及其前紧挨的间隙。为了覆盖所有间隙，mysql有两个特殊的隐藏记录分别为最大和最小，用于实现表中最大值之后与最小值之前的间隙
意向锁
这个知道就好，一般用不到。意向锁属于表锁，作用是为了兼容行锁与表锁，试想一千万行记录中有一个行写锁，这时候要加表锁难道要先遍历检查每行是否有写锁？不是的，在加行锁的时候就加了对应的表级意向写/读锁，这样就可以快速判断是否可以加表锁
另外间隙锁实现阻塞插入是通过插入意向锁，insert之前要先获取间隙的插入意向锁，而其与间隙锁互斥。插入意向锁之间并不互斥
悲观锁、乐观锁
悲观锁、乐观锁属于加锁策略，悲观锁思想为写冲突会有很多，所以要对操作对象加排他锁；乐观锁则认为写冲突并不多，所以通过更新条件来实现软性的写锁，比如常用update...where version=3，并不加写锁，更新失败则知道出现冲突。需要根据实际业务场景来确定到底使用哪种策略才可以最大化系统并发
加锁
显式加锁
可以显式的加表锁和行锁（读锁与写锁），其中行锁必须在一个事务中，而且行锁的具体算法根据存储引擎、事务隔离级别自动选择
lock table tab_name read|write
flush tables with read|write lock # 全局表加锁
unlock tables
begin
select * from table where ... for update
select * from table where ... lock in share mode
commit / rollback
隐式加锁
DDL语句都会导致表锁；任何的delete、update都会自动添加写锁；可串行隔离级别的事务中会自动加读锁
锁升级
由于InnoDB的行锁是实现在索引上的，假如行锁没有索引，或者索引选择性差导致加锁过多时，有可能会导致行锁升级为表锁。其实是mysql认为使用表锁cost更小，对使用者透明，不过出现这种现象往往意味着sql设计存在缺陷，需要优化
死锁
死锁指两个事务互相等待对方释放所需资源的锁, 导致事务阻塞。解决办法一般有
- 回滚其中一个事务（可能只需回滚到上个savepoint，而非全部回滚）
- 对资源加锁的按照相同顺序
- 悲观锁策略, 事先对可能用到的资源全部加锁
事务隔离级别
由于事务可以回滚，如果不进行适当隔离，回滚的数据就有可能造成错误。由于隔离主要通过锁实现，隔离越严格则并发性就越差，但出问题的概率就越小，我们需要根据实际业务场景设置mysql的隔离级别。标准的隔离级别分为4层，mysql全部支持，默认为可重复读
- 未提交读，read uncommitted。允许事务读到其他事务未提交的修改
- 已提交读，read committed。只允许事务读取其他事务已提交的修改
- 可重复读，repeatable read。保证事务相同的读取得到的结果是一致的
- 可串行化，serializable。事务串行执行，虽然部分读还可以并行，但并发性降至最低
未提交读相当于基本没有隔离，读到其他事务未提交的修改叫做脏读，如果发生回滚则极易出错。通过MVCC和加读锁，已提交读可以避免脏读，但由于该隔离级别允许读到已提交的修改，所以会出现相同的查询，前后读取结果不同的情况，叫做不可重复读（其中由于insert导致读取结果变多的特殊情况叫做幻读）。可重复读级别利用MVCC实现了可重复读，而且在标准允许出现幻读的情况下，超前在该级别解决了幻读。最高级别的可串行化通过默认为所有读加读锁，来解决之前所说的问题，但依然存在事务先后提交而导致的逻辑错误的可能
MVCC
多版本并发控制。mysql为每一条记录维护了一个链表，记录了当前活跃事务对其进行的修改历史链表。嗯，知道这么多就能够理解许多问题了
非锁定一致性读
update、delete会默认加写锁，而select除非明确加锁，或者可串行隔离级别，默认是无锁读的。实现隔离级别并非完全依赖锁，利用MVCC便可以实现非锁定一致性读，如RC级别通过读取MVCC中的最新已提交版本实现已提交读，RR级别通过管理先后关系控制事务读取版本从而实现可重复读。由于不需要加锁，mysql的并发性得到保障
可重复读
该隔离级别在SQL标准中是允许出现幻读的，但mysql通过MVCC实现了可重复读，同时解决了幻读。但又不是彻底的解决了幻读，select式的幻读解决了，但insert导致的幻读却未被解决，如唯一索引插入相同数据，明明select没有该值，但却出现插入阻塞。这时需要明确加读行锁来解决，这主要是因为RR级别下，行锁的默认算法为next-key，可以锁定间隙
有时候RR级别也需要读已提交，此时可以通过加读锁来明确读取记录最新版本
优化
优化的目的是减少查询时间，这个查询时间其实包括了查询请求发送、数据库查询、结果返回，通常情况主要时间消耗在数据库查询环节。所以优化的大原则是
- 最好不需要查询，即通过缓存或产品改进，避免数据库查询
- 查询执行的越快越好，涉及的方面比较多
- 列尽量少、结构尽量简单，内存加载的更多、扫描更快
- 查询尽可能利用索引，减少扫描的行数、避免排序、避免回表
- 结果集尽量精简行数和列
- 避免大事务、大批量、大SQL
优化的主要思路是结合业务实际需要，分析慢查询日志，对慢sql进行分类，再用explain分析，最后通过调整sql、表设计解决慢查询问题
库表优化
表设计原则
- 平衡范式和冗余，适当的冗余和违反范式可以降低sql复杂度
- 单表列数最好小于20，行数控制在千万级别
- 尽量把大列分拆到子表中，需要时连接查询
- 更小的类型通常更好，满足需求的情况下尽量使用最小数据类型，不过也要考虑未来扩展维护
- 尽量使用简单的数据类型，如能用date就别用varchar、能用varchar就别用text
- 尽量使用not null定义列
varchar越小越好
varchar(6) varchar(200)用于存储 hello 时的耗费是一致的, 那么使用短列有优势吗？
事实证明是有很大优势的，因为开辟内存时是以200字节进行的，遇到需要filesort或tmp table作业可能会带来不利影响
InnoDB主键类型选择
优先使用合适大小的自增整数类型
- 合适大小可以减小空间, 加快计算速度
- 自增由于是顺序插入，可以尽可能的减少裂页，插入速度更快。假如使用md5为主键，由于散列均匀，会大量裂页
- 由于二级索引会包含主键，所以主键越小越好
enum
虽然enum更简单更小，但由于维护成本较高，大多数情况都可以使用char来代替
alter table
mysql执行大部分DDL的方法都是创建一个新表，导入数据后，删除旧表，非常耗时、耗内存，要非常慎重
索引设计
索引设计原则
- 尽量使用选择性高的列
- 索引并非越多越好，尽量扩展索引，而非新增，使用联合索引来减少索引数量
查询优化
查询优化原则
- sql尽量精简，拒绝大事务、大批量、大SQL，复杂的sql需求要进行拆分
- 避免mysql承担过多计算任务
查询成本估算
一个查询可能有多种执行方案，mysql会计算每个方案的cost（成本）然后选择成本最低的。因为实时性要求高，这个cost只是估算，比如加载一个页耗时1.0，读取并检测一条记录耗时0.2，再根据mysql的统计数据与执行计划，便能大概估算个cost。所以有时候即便有索引可用，mysql也可能认为全表扫描综合成本更低
当然有时候这个估算也会出错，此时需要明确指定执行计划，如force index
不能使用索引的场景
- 不是按照索引的最左列开始查找
- 不能跳过索引中的列
- 如果查询中的某个列是范围查询, 则其右边的所有列都无法使用索引，比如like between
- 索引列上使用函数, 或者算数运算
- 少用or
- 列类型是字符串的话，查询时一定要给值加引号，否则索引失效
- 表连接时列字符集不同
select
尽量少使用select *，不仅是因为对索引不友好，还因为查出不需要的数据会侵占宝贵的缓存资源，导致缓存频繁变化，命中率下降
where
- 直接在做索引中使用where条件来过滤，存储引擎层便可以完成，最为高效
- 假如无法使用索引筛选数据，如`col like '%a'，但若col列在索引中，则可以通过全量扫描索引过滤数据
- 使用了索引中没有的列为过滤条件，则需要把数据读出到服务层进行扫描过滤
order
避免使用order by rand()
尽量使用升序
limit
limit一个常见问题是大表的分页，由于limit在服务层进行，所以要把结果集全部查出来，再扫描获取所需结果，性能慢在大量的回表，所以解决思路有
- 保证查询为覆盖索引，从而避免回表
- 假如无法满足覆盖索引，则先使用某列如主键通过覆盖索引确定结果集起始点，再通过范围查询获取结果
延伸
join
小表驱动大表
explain
优化之前先要确定sql的执行计划，explain中常用字段的含义为
- id 包括子查询在内每个查询的唯一id
- type 针对单表的
访问方法 - possible_keys 可能用到的索引
- key 实际使用的索引
- key_len
实际使用的索引长度 - ref 表连接方式
- rows
预估的扫描行数 - extra 额外补充信息
type
单表的访问方法，表明mysql如何从单表获取数据
- system 表中只有一行数据或者是空表，只能用于myisam和memory表，Innodb引擎是all或者index
- const 基于主键或者非null唯一索引扫描, 结果最多返回一条数据
- eq_ref 表连接时，基于主键或者唯一索引扫描
- ref 普通索引扫描
- fulltext 全文检索
- index_merge 可以利用index merge特性用到多个索引，提高查询效率
- range 索引范围扫描
-
index 全索引扫描，即可以通过索引扫描完成数据读取和过滤，
避免回表 - All 全表扫描
extra
- Using index 覆盖索引，不需要回表
- Using index condition 使用了索引进行结果过滤
-
Using where 使用了where进行结果过滤，
也即在服务层完成 - Using filesort 在服务层进行了排序操作，尽量避免
- Using temporary 创建了临时表
key_len
这个要特别注意，能帮助我们了解联合索引中有多少个列被实际使用到。列长度大概计算方法见1.4小节
延伸
- 其他调试工具mysqldumpslow、show profile
运维管理
启动关闭
# mysql安装目录bin中有许多实用工具
# mysqld，可启动mysql服务器进程，但并不常用
mysqld
# mysqld_safe，是一个脚本，调用了mysqld，但同时会启动一个监控进程，守护mysql进程
mysqld_safe
# mysql.server，也是一个脚本，调用了mysqld_safe，但可以添加参数进行管理。注意bin中的mysql.server默认为链接文件，源在support-files/mysql.server
mysql.server stop
mysql.server start
# 如何开启/关闭mysql服务
mysqladmin shutdown -uroot -p***
# 如何登陆mysql数据库
mysql -u username -p
配置管理
# 查看配置文件位置
mysql --help | grep cnf
mysqld --verbose --help | grep -A 1 'Default options'
# Default options are read from the following files in the given order:
# /etc/mysql/my.cfn ~/.my.cnf /usr/etc/my.cnf
默认配置
mysql可配置性太强也可以说是一个弱点, 其实大多数配置的默认值已经是最佳配置了, 所以最好不要改动太多。以下创建了一个完整的最小示例配置文件, 可以作为一个良好的起点, 不要以自带配置文件作为起点。此配置文件可能比你见过的其他配置文件太少了, 但实际上已经超过了许多人的需要, 请确保基本了解这些配置的意义
[mysqld]
# General
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_bufffer_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>
thread_cache = <value>
open_files_limit = 65535
[client]
socket = /var/lib/mysql/mysql.sock
port = 3306
用户管理
# 查看系统用户
select * from mysql.user;
# 创建 & 删除用户
create user 'USERNAME'@'HOSTNAME' identified by 'PASSWORD';
drop user 'USERNAME'@'HOSTNAME';
# 修改密码
set password for 'USERNAME'@'HOSTNAME' = password('NEW_PASSWD');
set password = password('NEW_PASSWD'); # 修改当前用户密码
权限管理
# 模式： grant 权限 on 数据库对象 to 用户
# 权限包括：
# select delete update insert 增删改查, select(col1, col2) 限制到列
# create drop alter 表管理权限
# 注意：用户需要重新登录才能权限生效
# FLUSH PRIVILEGES;
# 查看权限
show grants; # 查看当前用户
show grants for 'USERNAME'@'HOSTNAME'; # 查看指定用户
# 授予全部权限
grant all on DB.TABLE to 'USERNAME'@'HOSTNAME';
# 允许用户再授权别人
gran select on DB.TABLE to 'USERNAME'@'HOSTNAME' with grant option;
# 回收权限
# 注意这里有个坑：revoke的权限要和grant的时候一样！ e.g.
# grant all on *.* to user@'%'; 则必须
# revoke all on *.* from user@'%';
# 所以需求如：100个表，其中97个授予delete权限，3个不给，不能简单的 grant delete on db.* .. 然后再revoke
revoke 权限 on 数据库对象 from 用户
查看表信息
# 显示表格创建sql
show create table table_name;
# 简要列出表格的字段信息
describe table_name;
# 查看表大小
# 进入information_schema 数据库（存放了其他的数据库的信息）
use information_schema;
# 查询所有数据的大小
SELECT concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data FROM information_schema.TABLES;
# 查看数据库所有表大小
SELECT
table_name,
round(sum(DATA_LENGTH / 1024 / 1024) , 2) AS 'mb'
FROM
information_schema. TABLES
WHERE
table_schema = 'lbsugc_ifix_before_2017_06_01'
group by table_name
order by mb DESC;
# 查看指定数据库的大小：比如查看数据库home的大小
SELECT concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data FROM information_schema.TABLES WHERE table_schema='home';
# 查看指定数据库的某个表的大小，比如查看数据库home中 members 表的大小
SELECT concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data FROM information_schema.TABLES WHERE table_schema='home' AND table_name='members';
查询管理
# 查看所有连接
# mysql的查询可能处于的状态包括
# Sleep 线程正在等待客户端发送新的请求
# Query 线程正在执行查询或者正在将结果发送给客户端
# Locked mysql服务器层, 该线程正在等待表锁(_存储引擎实现的锁并不会在此反映如行锁_). 对于myisam来说这是一个比较典型的状态, 但在其他没有行锁的引擎中也经常出现
# Analyzing and statistics 线程正在收集存储引擎的统计信息, 并生成查询的执行计划
# Copying to tmp table [on disk] 线程正在执行查询, 并且将结果集都复制到一个临时表中, 这种状态一般要么是在做group by操作, 要么是在file sort, 或者union;on disk标记表示mysql正在将一个内存临时表放到磁盘上
# Sorting result 线程正在对结果集进行排序
# Sending data 多种情况: 可能是线程在多个状态之间传送数据;或者生成结果;或者向客户端返回数据
show full processlist
# 关闭所有连接
for i in $(mysql -uroot -p537ce49da46a -Bse "show processlist" | awk '{print $1}');do mysql -uroot -p537ce49da46a -e "kill $i";done
事务
# 隔离级别设置
# 查看隔离级别
select @@tx_isolation
# 设置读未提交级别
set transaction isolation level read uncommitted
# 设置读提交级别
set transaction isolation level read committed
# 设置可重复读（缺省），保证每次读的结果是一样的
set transaction isolation level repeatable read
# 设置成序列化
set transaction isolation level serializable
# 查看锁竞争统计
show status like 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 150751 |
| Innodb_row_lock_time_avg | 21535 |
| Innodb_row_lock_time_max | 51158 |
| Innodb_row_lock_waits | 7 |
+-------------------------------+--------+
导入、导出
# 导出
mysqldump -h$DB_HOST -Pprot -u$DB_USER -pPwd --skip-lock-tables -q \
DB table1 table2 > $DATA_PATH/itest.sql
# -w 添加搜索条件
mysqldump -h$DB_HOST -Pport -u$DB_USER -ppwd --skip-lock-tables -q \
-w "id < 1000 and createTime>unix_timestamp('20170101')" \
DB table1 table2 > $DATA_PATH/itest.sql
# -d 只导出表结构
# -q 不缓冲查询，直接导出至stdout。（默认打开，用--skip-quick来关闭）该选项用于转储大的表
# -t 只导出数据
# -c 附带列名
# --lock-all-tables 全局加读锁
# --lock-tables 当前表加读锁
# --add-locks 导入, sql文件时锁表
# --skip-lock-tables 导出, 时不加读锁
# --ignore-table=db_name.t2 排除表
mysqldump -h$DB_HOST -Pport -u$DB_USER -pPwd --skip-lock-tables -q -d DB table > $DATA_PATH/itest.sql
导入
# 导入 或者登陆mysql，使用source命令
mysql -h... -P3306 -ulbsugc -p... lbsugc_hard < taskCenter.sql
跨表导入数据
INSERT INTO SELECT
-- 形式为：Insert into Table2(field1,field2,...) select value1,value2,... from Table1
-- 要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，
-- 还可以插入常量
SELECT INTO FROM
-- 语句形式为：SELECT vale1, value2 into Table2 from Table1
-- 要求目标表Table2不存在，因为在插入时会自动创建表Table2，
-- 并将Table1中指定字段数据复制到Table2中
binlog
# 查看binlog状态
show variables like '%log_bin%'; # log_bin ON 开启
# 开启binlog，编辑my.cnf，重启mysql
server-id=1 # 随机设定的节点id
log_bin=/home/lbsugc/.jumbo/var/lib/mysql/mysql-bin # binlog保存路径
expire_logs_days=7 # binlog保留日期 0表示永不过期
# 查看binlog文件
show binary logs; # 查看当前服务器使用的全部binlog，每次重启服务器或者binlog文件过大，则分割新的
show master status; # 当前mysql实例正在使用的binlog文件
show binlog events in “mysql-bin.000005”; # 查看binlog内容
# 使用mysqlbinlog工具
mysqlbinlog mysql-bin.000005; # 查看binlog内容
mysqlbinlog -d lbsugc_rocket mysql-bin.000005 -r out.sql # -d 指定db -r 指定输出文件
mysqlbinlog mysql-bin.000005 --start-position=100 --stop-position=100 # 精确指定输出事件位置
mysqlbinlog mysql-bin.000005 --start-datetime='2018-05-18 12:00'--stop-datetime='2018-05-18 14:00'# 指定输出事件起始时间
# 使用binlog恢复数据
# 原理很简单，mysqlbinlog输出的是sql文件，当做普通的.sql输入数据库即可
# 比如在99号事件误删了数据库，则可这样恢复
mysqlbinlog mysql-bin.000005 --stop-position=98 | mysql -uroot -p*** DB
mysqlbinlog mysql-bin.000005 --start-position=100 --end-position=目标位置 | mysql -uroot -p*** DB
常用命令
# 直接执行sql
mysql -h10.57.27.37 -P5020 -uliu******* -pFb9F39SrpS lbsugc -e ""
# 汉字拼音排序
select poiName
from userProblem
order by convert(left(poiName,1) using gbk) desc;
# 交换一个表中两列值
update table a,table b set a.column1 = b.column2, a.column2=b.column1 where a.id=b.id;
集群
经典问题
mysql server has gone away 错误原因
- mysql宕机
- client连接时间过长
- 查询sql过大被断开
- 多进程场景，错误复用了连接，导致socket混淆
学习资料
- 掘金平台付费《MySQL是怎样运行的：从根儿上理解MySQL》
- 《MySQL技术内幕 InnoDB存储引擎》
- 《高性能MySQL》
- MySQL学习笔记
