本文详细介绍了Mysql的执行顺序、函数使用、索引原理及优化，包括各种函数类型、索引结构（如B树、B+树）、引擎对比、锁机制、事务实现和性能调优。同时，探讨了主从复制、读写分离的实现，以及ShardingSphere在读写分离中的应用。
本文主要是关于mysql的一些个人理解，如果文中用词或者理解方面出现问题，欢迎指出。此文旨在分享我对于mysql的整一些个人理解，对于细节方面不会去深究，若有名词或者理解方面的问题，欢迎指出
Mysql
须知：在此是默认读者熟悉了sql的一些概念，在此只是记录平时经常用到的一些sql以及背后的原理
mysql的执行顺序
select[distinct]
from
join（如left join）
on
where
group by
having
union
order by
limit
Mysql的执行顺序是
- from
- on
- join
- where
- group by
- having
- select
- distinct
- union
- order by
- limit
让我们一步一步去认识执行顺序
-
首先from,拿到某个表，如果from这里有多个表，它会做一个表之间的笛卡尔积。
-
通过on的条件去join一个表，这里假设我的on条件是两个表的某个字段相等，这时相当于将字段相等的两条记录拼接在一起
-
where在上述操作得到的临时表后筛选符合条件的记录，得到一个临时表
-
group by是将字段进行分组，就是将重复的字段合并成一个字段，分开来去展示，可以将已经分好的字段执行聚合函数。
MySql从5.7版本开始默认开启only_full_group_by规则，规则核心原则如下，没有遵循原则的sql会被认为是不合法的sql
- group by后面的字段必须是select后面所有的字段,having和order by的字段根据执行顺序，它们后面的字段也必须是select里面的字段，但不像group by要求全部，它们可以选择单个字段。
- 如果分组列中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组
-
having：过滤分组，having在group by的基础上继续筛选，所以也叫组级筛选，而与之相对的where是对from的表进行行级过滤
HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是，WHERE过滤行，而HAVING过滤分组。不过通常having是和group by成对出现的
-
select,将所要展示的字段从临时结果集中展示出来
-
distinct :去除重复的字段
-
union：将union前后得到的表组合起来，这里要求两张表的字段是一样的，否则mysql中会将第二个表的记录组合进第一个表，而字段显示第一张表的字段。
-
order by：对上述操作产生的临时表针对某字段进行排序操作。
-
limit: 取出临时表中的前几条记录
补充：
- join是内连接还是外连接，无非就是记录连接的方式不同。内连接inner join相当于where，相当于将条件满足的记录拼接到一起。而外连接又分为left outer join(left join)和right outer join(right join)，左外连接:A LEFT JOIN B，保持左表不变，而右表根据on的条件将满足符合的记录拼接到左表中去。右外连接反之亦然。
- where ：由于where的执行顺序在from on join之后，条件里是不允许用临时表不存在的字段，像聚合函数、其他表的字段是不允许用的。
- group by：group by后面的字段要用select中的所有字段
mysql函数使用
数字型函数
| ABS | 求绝对值 |
|---|---|
| SQRT | 求二次方根 |
| MOD | 求余数 |
| CEIL 和 CEILING | 两个函数功能相同，都是返回不小于参数的最小整数，即向上取整 |
| FLOOR | 向下取整，返回值转化为一个BIGINT |
| RAND | 生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列 |
| ROUND | 对所传参数进行四舍五入 |
| SIGN | 返回参数的符号 |
| POW 和 POWER | 两个函数的功能相同，都是所传参数的次方的结果值 |
字符型函数
| LENGTH | 计算字符串长度函数，返回字符串的字节长度 |
|---|---|
| CONCAT | 合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个 |
| INSERT | 替换字符串函数 |
| LOWER | 将字符串中的字母转换为小写 |
| UPPER | 将字符串中的字母转换为大写 |
| LEFT | 从左侧字截取符串，返回字符串左边的若干个字符 |
| RIGHT | 从右侧字截取符串，返回字符串右边的若干个字符 |
| TRIM | 删除字符串左右两侧的空格 |
| REPLACE | 字符串替换函数，返回替换后的新字符串 |
| SUBSTRING | 截取字符串，返回从指定位置开始的指定长度的字符换 |
| REVERSE | 字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串 |
日期/时间函数
| 函数名称 | 作 用 |
|---|---|
| CURDATE 和 CURRENT_DATE | 两个函数作用相同，返回当前系统的日期值 |
| CURTIME 和 CURRENT_TIME | 两个函数作用相同，返回当前系统的时间值 |
| NOW 和 SYSDATE | 两个函数作用相同，返回当前系统的日期和时间值 |
| UNIX_TIMESTAMP | 获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数 |
| FROM_UNIXTIME | 将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数 |
| MONTH | 获取指定日期中的月份 |
| MONTHNAME | 获取指定日期中的月份英文名称 |
| DAYNAME | 获取指定曰期对应的星期几的英文名称 |
| DAYOFWEEK | 获取指定日期对应的一周的索引位置值 |
| WEEK | 获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53 |
| DAYOFYEAR | 获取指定曰期是一年中的第几天，返回值范围是1~366 |
| DAYOFMONTH | 获取指定日期是一个月中是第几天，返回值范围是1~31 |
| YEAR | 获取年份，返回值范围是 1970〜2069 |
| TIME_TO_SEC | 将时间参数转换为秒数 |
| SEC_TO_TIME | 将秒数转换为时间，与TIME_TO_SEC 互为反函数 |
| DATE_ADD 和 ADDDATE | 两个函数功能相同，都是向日期添加指定的时间间隔 |
| DATE_SUB 和 SUBDATE | 两个函数功能相同，都是向日期减去指定的时间间隔 |
| ADDTIME | 时间加法运算，在原始时间上添加指定的时间 |
| SUBTIME | 时间减法运算，在原始时间上减去指定的时间 |
| DATEDIFF | 获取两个日期之间间隔，返回参数 1 减去参数 2 的值 |
| DATE_FORMAT | 格式化指定的日期，根据参数返回指定格式的值 |
| WEEKDAY | 获取指定日期在一周内的对应的工作日索引 |
| TIMESTAMPDIFF | 计算两个日期的时间差函数 |
聚合函数
| 函数名称 | 作用 |
|---|---|
| MAX | 查询指定列的最大值 |
| MIN | 查询指定列的最小值 |
| COUNT | 统计查询结果的行数 |
| SUM | 求和，返回指定列的总和 |
| AVG | 求平均值，返回指定列数据的平均值 |
流程控制函数
| 函数名称 | 作用 |
|---|---|
| IF | 判断，流程控制 |
| IFNULL | 判断是否为空 |
| CASE | 搜索语句 |
TRUNCATE()函数介绍
TRUNCATE(X,D) 是MySQL自带的一个系统函数。
其中，X是数值，D是保留小数的位数。
其作用就是按照小数位数，进行数值截取（此处的截取是按保留位数直接进行截取，没有四舍五入）。
2、数值保留规则
规则如下：
1）当 D 大于0，是对数值 X 的小数位数进行操作；
2）当 D 等于0，是将数值 X 的小数部分去除，只保留整数部分；
3）当 D 小于0，是将数值 X 的小数部分去除，并将整数部分按照 D 指定位数，用 0 替换。
mysql索引
索引是什么：
索引的作用相当于目录
可用的索引结构：B树 B+树 Hash
索引的优缺点：
优点：可以提高查询的速度
缺点：需要创建索引和维护索引，特别是有索引的数据更改时，需要更新索引，如果索引结构这时很庞大，更新一次会耗费不少时间
并不是使用了索引就保证能提高查询效率，大多数情况下，使用索引还是比全表排查快的，但是在数据量不大的情况下，假如在InnoDB搜索非主键的索引，由于要回查主键，它所花费的时间反而会比全表查询慢，索引不可以滥用。
用什么实现索引
hash索引
hash表查询提取数据是很快的，只要通过hash函数得到hash值就可以得到value,时间复杂度O(1)，即使hash表有hash冲突的情况,也可以通过链地址法去解决，在JDK1.8之前HashMap 就是通过链地址法来解决哈希冲突的。不过，JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。最大的缺点就是使用hash表作为索引，它是不支持范围和顺序查询的，如果hash表要查询一个小于500的数，hash如何能够找到呢？难道要将1-499的数都查询一遍吗。所以并没有使用hash表去作为索引
二叉搜索树
如果使用二叉搜实现，在特殊情况下，二叉搜索树会退化成链表结构，导致时间复杂度变成线性结构。
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
红黑树
红黑树是自平衡的二叉搜索树，但是由于二叉树的性质，会导致在数据量很大的时候，树的高度会很大，导致磁盘IO读取次数过多，例如，读取一个在底层的叶子节点，会从根节点开始找，先从硬盘读取根节点，再根据大小继续去找，这时，如果树的高度很高，就意味着要找到这个底层的叶子节点要经过O（树的高度）的磁盘IO，这个开销是很大的。那么有什么办法可以降低这个高度呢？既然限制了高度，也就是垂直方向，那么可以往水平方向发展，也就是可以从二叉树变为3、4等多叉树。从树的直观感受上来说，就是从一个“瘦高”的树变为“矮胖”的树，这就是B树
怎么手撕红黑树：
研究红黑树的底层原理
B-树/B树
前面说到了B树是从红黑树演变出来的自平衡多叉树，节点保存的数据量是有限的，了解过计算机底层原理这方面的话，会知到硬盘同内存打交道的单位的数据最大是4K，可能有时候有一些局部读取的原理可能会取几十K（4的整数倍），取个16K，24K也是可以的。知道了这个我们来演示一下B-树的查询过程
由于B树节点存储的是索引和数据，这会导致单个节点能够保存的索引并不高，继而导致某些情况下硬盘IO还是很高，查找效率不高，所以mysql使用了B树的变种树B+树
B+树(B-tree变种)
B+树和B树的最大区别就是B+树只有叶子节点保存索引和数据，非叶子节点保存冗余的索引。并且叶子节点之间还有指针，非叶子节点和B树一样没有指针，这也是B+树支持范围和顺序查找的原因。
为什么要将非叶子节点的数据移除
因为一个节点它所能承载的数据量是有限的，这是因为计算机底层原理限制了磁盘和内存的交互大小，（从磁盘读取一个节点就是一次磁盘IO）所以把数据去除后，非叶子节点能够存储更多的索引，从磁盘读到内存的索引会变多，也就更容易的找到所要查询的数据，减少磁盘的IO
mysql引擎
5.7以前MySQL用的是MYISAM，5.7之后改用InnoDB
InnoDB和MYISAM最大的不同就是叶子节点的数据存放的不同，InnoDB主键索引的叶子节点存放的是主键所在的记录，副键索引存放的是主键。MYISAM无论主键索引还是副键索引存放的是指向磁盘的索引
InnoDB和MYISAM的优缺点
由于存储data的不同，也就造成了各有有缺点
InnoDB的主键存放的是记录，所以可以直接取出来，对于副键索引，还需要根据找到的主键，再去查一遍（回表），并且数据改变的时候，需要维护主键的索引，如果索引结构复杂，会导致效率低
MYISAM由于存放的是指针，所以无论是主键还是副键，都直接找到记录所在的指针，去数据表文件查找记录，但是对比InnoDB来说，多了个根据指针查找的操作，会对效率有所降低，在数据改变时，不需要去维护指针，只需要改变数据文件中的记录
1.是否支持行级锁
MyISAM 只有表级锁(table-level locking)，而 InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
也就说，MyISAM 一锁就是锁住了整张表，这在并发写的情况下是多么滴憨憨啊！这也是为什么 InnoDB 在并发写的时候，性能更牛皮了！
2.是否支持事务
MyISAM 不提供事务支持。
InnoDB 提供事务支持，具有提交(commit)和回滚(rollback)事务的能力。
3.是否支持外键
MyISAM 不支持，而 InnoDB 支持。
🌈 拓展一下：
一般我们也是不建议在数据库层面使用外键的，应用层面可以解决。不过，这样会对数据的一致性造成威胁。具体要不要使用外键还是要根据你的项目来决定。
4.是否支持数据库异常崩溃后的安全恢复
MyISAM 不支持，而 InnoDB 支持。
使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于 redo log 。
🌈 拓展一下：
- MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。//具体细节实现
- MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性（ 默认支持的隔离级别是
REPEATABLE-READ）。//具体细节 - 保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。
5.是否支持 MVCC
MyISAM 不支持，而 InnoDB 支持。
讲真，这个对比有点废话，毕竟 MyISAM 连行级锁都不支持。
MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提供性能。//MVCC粒度比行级锁还小
mysql锁机制
mysql事务以及实现机制
事务有ACID特性
mysql使用re_do log实现事务的持久性、原子性
具体实现:
为什么要使用re_do log？
mysql查询或更改一条数据的时候，会从硬盘读取一条数据放到内存中，再在内存中做相应操作，再保存回硬盘中。
由于从硬盘读取一次数据就是一次磁盘IO，为了降低磁盘IO的次数，mysql会将
un_do log实现事务的原子性
un do log实现事务的原子性以及MVCC
mysql性能优化
mysql性能优化可以从以下方面入手：
-
设计合理的表结构
- 不要使用无法加索引的类型作为关键字段，比如 text类型
- 为了避免联表查询，有时候可以适当的数据冗余
- 根据业务，选择合理的引擎
-
建立合适的索引结构
- 如果在SQL里使用了MySQL部分自带函数，索引将失效，同时将无法
使用 MySQL 的 Query Cache**，比如** LEFT(), SUBSTR(), TO_DAYS()
DATE_FORMAT(), 等，如果使用了 OR 或 IN**，索引也将失效**，就是改变了索引字段的方法
-
编写简洁高效的sql语句
-
不要在where 子句中的“=”左边进行算术或表达式运算，否则系统将可能无法正确使用索引
-
尽量不要在where条件中使用函数，否则将不能使用索引
-
mysql分库分表
分布式mysql-cluster
MySQL的主从复制以及读写分离
mysql复制流程
-
主从复制：在每个事务更新数据完成之前，master在二进制日志记录这些改变。写入二进制日志完成后，master通知存储引擎提交事务。
-
Slave将master的binary log复制到其中继日志。首先slave开始一个工作线程（I/O），I/O线程在master上打开一个普通的连接，然后开始binlog dump process。binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件，I/O线程将这些事件写入中继日志。
-
Sql slave thread（sql从线程）处理该过程的最后一步，sql线程从中继日志读取事件，并重放其中的事件而更新slave数据，使其与master中的数据一致，只要该线程与I/O线程保持一致，中继日志通常会位于os缓存中，所以中继日志的开销很小。
-
查看主从是否延迟
查看从库的参数:show slave status\G;
Read_Master_Log_Pos: 21416041 传输过来的主库日志的位置
Exec_Master_Log_Pos: 21416041 执行到主库日志的位置
Seconds_Behind_Master: 0 从库延迟秒5.主从延迟的因素
网络延迟
主库高并发
从库高并发
一般解决方式是多添加几台slave，这样从库的压力低了。或者再添加一台只用于
备份的slave，可以最大限度的防止主从延迟6.主从同步延迟原理
MySQL的主从复制都是单线程的操作，主库对所有DDL和DML产生的binlog是顺序写
所以效率很高，从库的i/o线程获取binlog也是顺序读，而sql线程执行relaylog时
是随即写，因此成本会比较高，而且主库是可以并发的，而从库的sql线程只能等
上一个DDL操作结束之后才能执行新的DDL操作，而且当从库的查询并发高时，从库
的sql线程操作还可能会与查询语句产生lock争用。MySQL主从复制–主库已有数据的解决方案
- 锁定主数据库，只允许读取不允许写入，防止备份过程中，主库和从库数据不一致；
- flush tables with read lock；
- 使用mysqldump来导出数据，导出的数据放到从库里面
- 启动主从复制
- 解锁主数据库：unlock tables；
mysql读写分离原理
- 读写分离就是在主服务器上修改，数据会同步到从服务器，从服务器只能提供读取数据，不能写入，实现备份的同时也实现了数据库性能的优化，以及提升了服务器安全。
常见的读写分离实现：
- 基于程序代码内部实现
在代码中根据select 、insert进行路由分类，这类方法也是目前生产环境下应用最广泛的。优点是性能较好，因为程序在代码中实现，不需要增加额外的硬件开支，缺点是需要开发人员来实现，运维人员无从下手。
- 基于中间代理层实现
代理一般介于应用服务器和数据库服务器之间，代理数据库服务器接收到应用服务器的请求后根据判断后转发到，后端数据库，有以下代表性的程序。
（1）mysql_proxy。mysql_proxy是Mysql的一个开源项目，通过其自带的lua脚本进行sql判断。
（2）Atlas。是由 Qihoo 360, Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。
（3）Amoeba。由阿里巴巴集团在职员工陈思儒使用序java语言进行开发，阿里巴巴集团将其用户生产环境下，但是他并不支持事物以及存储过程。
扫一扫
976
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
