在开发中，数据库的种类千奇百怪，有各种，比如早期的SQLServer，，Oracle，如今还有许多国产的数据库，然而有不少开发还是经常使用的Mysql，然而关于Mysql中的各种各样的函数，用的却是没有那么多的，当天了不起就来带着大家一同看看这个Mysql的各种罕用的函数。

DATE_FORMAT(date,format)

SELECTDATE_FORMAT(NOW(),'%Y-%m-%d%H:%i:%s');

普通咱们经常使用的时刻，都是经常在条件中经常使用的这个比如：

whereDATE_FORMAT(createTime,'%Y-%m-%d')='2020-11-11'

而且咱们也可以对他启动分组比如：

GROUPBYDATE_FORMAT(createTime,'%H');

这样的分组条件就是小时，每小时有多少数据，假设是每天，则换成就可以了

STR_TO_DATE(str,format)

比如上方这样的：

SELECTSTR_TO_DATE('2023-11-21','%Y-%m-%d');

SELECTYEAR(NOW());SELECTMONTH(NOW());SELECTDAY(NOW())

DAYOFYEAR（date）

SELECTDAYOFYEAR(NOW());333

字符串函数

CHAR_LENGTH(str)

CHARACTER_LENGTH（str）

这两个函数都是用来前往字符串的字符数的：

selectCHAR_LENGTH("hello");selectCHARACTER_LENGTH('hello');结果：5

CONCAT(str1,str2,...)

CONCAT_WS(separator,str1,str2,...)

第一个就是多个字符串兼并成一个字符串，第二个的意思则是，再两边可以拼接一段内容，或许了解为用两边的内容隔开。

这个简直是咱们常罕用的函数，用来做两个字段的兼并等等操作

SELECTCONCAT('hello','world');helloworldSELECTCONCAT_WS(',','hello','world');hello,world

FIELD(str,str1,str2,str3,...)

前往第一个字符串再字符串列表的位置：

SELECTFIELD('x','d','c','x');3

TRIM('xxxx')

这个也是咱们在开发的时刻经常会用到，然而在数据库层面其实很少用到，最多的还是在代码外面去除空格经常使用，再数据库中，含意也是一样的，都是用来去除字符串开局和开头的空格。

这个时刻就有人想问了，假设我只想去掉扫尾的，或许只想去掉开头的空格怎样办？

LTRIM(str)

RTRIM(str)

第一个是去掉开局的空格，第二个是去掉开头的空格，这个了不起都不用给大家展示，一个是去掉左边的，一个是去掉左边的，没有R没有L的则是两边都去掉。

UCASE(str)

UPPER(str)

这两个函数成果是一样的，转换字符串字符为大写，既然有大写，那么就势必会有小写

LCASE(str)

LOWER(str)

上述两个方法是吧字符串转为小写

**REPLACE()**

将字符串中产生的一切子字符串交流为新的子字符串

这个函数就比拟无心思了，把字符串给翻转上来，然而这个函数不是属于罕用的函数，了不起真实是想不出什么需求，须要吧这个字符串给你倒上来，难道还想滚瓜烂熟？

聚合函数

在MySQL中，聚合函数重要由：count,sum,min,max,avg,这些聚合函数咱们之前都学过，不再重复。这里咱们学习另外一个函数:group_concat(），该函数用户成功行的兼并。

group_concat()函数首先依据groupby指定的列启动分组，并且用分隔符分隔，将同一个分组中的值衔接起来，前往一个字符串结果。

group_concat([distinct]字段名[orderby排序字段asc/desc][separator'分隔符'])

留意：

数学函数

round(x,保管位数)函数

四舍五入；当对正数启动四舍五入：依照反常的计算模式，四舍五入即可。当对正数启动四舍五入：先把符号丢到一边，对去掉负号后的正数启动四舍五入，成功以后，再把这个负号，补上即可。

selectround(1.595658,3)asout_put;

ceil(x)函数

向上取整，前往>=该参数的最小整数。求的是大于等于这个数字的最小整数

selectceil(1.9)asout_put;selectceil(1.1)asout_put;

至于数学函数中的avg和sum了不起都不用说了，一个平均数，一个求和，

---

MySQL数据库优化（七）

为了能最小化磁盘I/O MyISAM 存储引擎采用了很多数据库系统使用的一种策略 它采用一种机制将最经常访问的表保存在内存区块中

对索引区块来说 它维护着一个叫索引缓存(索引缓冲)的结构体 这个结构体中放著许多那些最常使用的索引区块的缓冲区块　对数据区块来说 MySQL没有使用特定的缓存 它依靠操作系统的本地文件系统缓存

本章首先描述了 MyISAM 索引缓存的基本操作 然后讨论在MySQL 中所做的改进 它提高了索引缓存性能 同时能更好地控制缓存操作

线程之间不再是串行地访问索引缓存 多个线程可以并行地访问索引缓存　可以设置多个索引缓存 同时也能指定数据表索引到特定的缓存中

索引缓存机制对 ISAM 表同样适用 不过 这种有效性正在减弱 自从MySQL 开始 MyISAM 表类型引进之后 ISAM 就不再建议使用了 MySQL 更是延续了这个趋势 ISAM 类型默认被禁用了

可以通过系统变量 key_buffer_size 来控制索引缓存区块的大小 如果这个值大小为 那么就不使用缓存 当这个值小得于不足以分配区块缓冲的最小数量( )时 也不会使用缓存

当索引缓存无法操作时 索引文件就只通过操作系统提供的本地文件系统缓冲来访问(换言之 表索引区块采用的访问策略和数据区块的一致)

一个索引区块在 MyISAM 索引文件中是一个连续访问的单元 通常这个索引区块的大小和B树索引节点大小一样(索引在磁盘中是以B树结构来表示的 这个树的底部时叶子节点 叶子节点之上则是非叶子节点)

在索引缓存结构中所有的区块大小都是一样的 这个值可能等于 大于 或小于表的索引区块大小 通常这两个值是不一样的

当必须访问来自任何表的索引区块时 服务器首先检查在索引缓存中是否有可用的缓冲区块 如果有 服务器就访问缓存中的数据 而非磁盘 就是说 它直接存取缓存 而不是存取磁盘 否则 服务器选择一个(多个)包含其它不同表索引区块的缓存缓冲区块 将它的内容替换成请求表的索引区块的拷贝 一旦新的索引区块在缓存中了 索引数据就可以存取了

当发生被选中要替换的区块内容修改了的情况时 这个区块就被认为 脏 了 那么 在替换之前 它的内容就必须先刷新到它指向的标索引

通常服务器遵循LRU(最近最少使用)策略 当要选择替换的区块时 它选择最近最少使用的索引区块 为了想要让选择变得更容易 索引缓存模块会维护一个包含所有使用区块特别的队列(LRU链) 当一个区块被访问了 就把它放到队列的最后位置 当区块要被替换时 在队列开始位置的区块就是最近最少使用的 它就是第一候选删除对象

共享访问索引缓存

在MySQL 以前 访问索引缓存是串行的 两个线程不能并行地访问索引缓存缓冲 服务器处理一个访问索引区块的请求只能等它之前的请求处理完 结果 新的请求所需的索引区块就不在任何索引缓存环冲区块中 因为其他线程把包含这个索引区块的缓冲给更新了

从MySQL 开始 服务器支持共享方式访问索引缓存

没有正在被更新的缓冲可以被多个线程访问

缓冲正被更新时 需要使用这个缓冲的线程只能等到更新完成之后

多个线程可以初始化需要替换缓存区块的请求 只要它们不干扰别的线程(也就是 它们请求不同的索引区块 因此不同的缓存区块被替换)

共享方式访问索引缓存令服务器明显改善了吞吐量

多重索引缓存

共享访问索引缓存改善了性能 却不能完全消除线程间的冲突 它们仍然争抢控制管理存取索引缓存缓冲的结构 为了更进一步减少索引缓存存取冲突 MySQL 提供了多重索引缓存特性 这能将不同的表索引指定到不同的索引缓存

当有多个索引缓存 服务器在处理指定的 MyISAM 表查询时必须知道该使用哪个 默认地 所有的 MyISAM 表索引都缓存在默认的索引缓存中 想要指定到特定的缓存中 可以使用 CACHE INDEX 语句

如下语句所示 指定表的索 t t 和 t 引缓存到名为 hot_cache 的缓存中

mysql>CACHEINDEXt t t INhot_cache;　+ + + + +　|Table|Op|Msg_type|Msg_text|　+ + + + +　|test t |assign_to_keycache|status|OK|　|test t |assign_to_keycache|status|OK|　|test t |assign_to_keycache|status|OK|　+ + + + +

注意 如果服务器编译支持存 ISAM 储引擎了 那么 ISAM 表也使用索引缓存机制 不过 ISAM 表索引只能使用默认的索引缓存而不能自定义

CACHE INDEX 语句中用到的索引缓存是根据用 SET GLOBAL 语句的参数设定的值或者服务器启动参数指定的值创建的 如下　mysql> SET GLOBAL keycache key_buffer_size= * ;想要删除索引缓存 只需设置它的大小为　mysql> SET GLOBAL keycache key_buffer_size= ;索引缓存变量是一个结构体变量 由名字和组件构成 例如 keycache key_buffer_size keycache 就是缓存名 key_buffer_size 是缓存组件　默认地 表索引在服务器启动时指定到主(默认的)索引缓存中 当一个索引缓存被删掉后 指定到这个缓存的所有索引都被重新指向到了默认索引缓存中去　对一个繁忙的系统来说 我们建议以下三条策略来使用索引缓存　热缓存占用 %的总缓存空间 用于繁重搜索但很少更新的表　冷缓存占用 %的总缓存空间 用于中等强度更新的表 如临时表　冷缓存占用 %的总缓存空间 作为默认的缓存 用于所有其他表　使用三个缓存的一个原因是好处在于 存取一个缓存结构时不会阻止对其他缓存的访问 访问一个表索引的查询不会跟指定到其他缓存的查询竞争 性能提高还表现在以下几点原因　热缓存只用于检索记录 因此它的内容总是不需要变化 所以 无论什么时候一个索引区块需要从磁盘中引入 被选中要替换的缓存区块的内容总是要先被刷新　索引被指向热缓存中后 如果没有需要扫描全部索引的查询 那么对应到B树中非叶子节点的索引区块极可能还保留在缓存中　在临时表里必须频繁执行一个更新操作是相当快的 如果要被更新的节点已经在缓存中了 它无需先从磁盘中读取出来 当临时表的索引大小和冷缓存大小一样时 那么在需要更新一个节点时它已经在缓存中存在的几率是相当高的

中点插入策略

默认地 MySQL 的索引缓存管理系统采用LRU策略来选择要被清除的缓存区块 不过它也支持更完善的方法 叫做 中点插入策略

使用中点插入策略时 LRU链就被分割成两半 一个热子链 一个温子链 两半分割的点不是固定的 不过缓存管理系统会注意不让温子链部分 太短 总是至少包括全部缓存区块的 key_cache_division_limit 比率 key_cache_division_limit 是缓存结构体变量的组件部分 因此它是每个缓存都可以设置这个参数值

当一个索引区块从表中读入缓存时 它首先放在温子链的末尾 当达到一定的点击率(访问这个区块)后 它就提升到热子链中去 目前 要提升一个区块的点击率( )对每个区块来说都是一样的 将来 我们会让点击率依靠B树中对应的索引区块节点的级别 包含非叶子节点的索引区块所要求的提升点击率就低一点 包含叶子节点的B索引树的区块的值就高点

提升起来的区块首先放在热子链的末尾 这个区块在热子链内一直循环 如果这个区块在该子链开头位置停留时间足够长了 它就会被降级回温子链 这个时间是由索引缓存结构体变量的组件 key_cache_age_threshold 值来决定的

这个阀值是这么描述的 一个索引缓存包含了 N 个区块 热子链开头的区块在低于 N*key_cache_age_threshold/ 次访问后就被移动到温子链的开头位置 它又首先成为被删除的候选对象 因为要被替换的区块还是从温子链的开头位置开始的

中点插入策略就能在缓存中总能保持更有价值的区块 如果更喜欢采用LRU策略 只需让 key_cache_division_limit 的值低于默认值

中点插入策略能帮助改善在执行需要有效扫描索引 它会将所有对应到B树中高级别的有价值的节点推出的查询时的性能 为了避免这样 就必须设定 key_cache_division_limit 远远低于 以采用中点插入策略 则在扫描索引操作时那些有价值的频繁点击的节点就会保留在热子链中了

索引预载入

如果索引缓存中有足够的区块用来保存全部索引 或者至少足够保存全部非叶子节点 那么在使用前就载入索引缓存就很有意义了 将索引区块以十分有效的方法预载入索引缓存缓冲 从磁盘中顺序地读取索引区块

没有预载入 查询所需的索引区块仍然需要被放到缓存中去 虽然索引区块要保留在缓存中 因为有足够的缓冲 它们可以从磁盘中随机读取到 而非顺序地

想要预载入缓存 可以使用 LOAD INDEX INTO CACHE 语句 如下语句预载入了表 t 和 t 的索引节点(区块)

mysql>LOADINDEXINTOCACHEt t IGNORELEAVES;　+ + + + +　|Table|Op|Msg_type|Msg_text|　+ + + + +　|test t |preload_keys|status|OK|　|test t |preload_keys|status|OK|　+ + + + +

增加修饰语 IGNORE LEAVES 就只预载入非叶子节点的索引区块 因此 上述语句加载了 t 的全部索引区块 但是只加载 t 的非叶子节点区块

如果使用 CACHE INDEX 语句将索引指向一个索引缓存 将索引区块预先放到那个缓存中去 否则 索引区块只会加载到默认的缓存中去

索引缓存大小

MySQL 引进了对每个索引缓存的新变量 key_cache_block_size 这个变量可以指定每个索引缓存的区块大小 用它就可以来调整索引文件I/O操作的性能

当读缓冲的大小和本地操作系统的I/O缓冲大小一样时 就达到了I/O操作的最高性能了 但是设置索引节点的大小和I/O缓冲大小一样未必能达到最好的总体性能 读比较大的叶子节点时 服务器会读进来很多不必要的数据 这大大阻碍了读其他叶子节点

目前 还不能控制数据表的索引区块大小 这个大小在服务器创建索引文件 ` MYI 时已经设定好了 它根据数据表的索引大小的定义而定 在很多时候 它设置成和I/O缓冲大小一样 在将来 可以改变它的值 并且会全面采用变量 key_cache_block_size

重建索引缓存

索引缓存可以通过修改其参数值在任何时候重建它 例如

mysql>SETGLOBALcold_cache key_buffer_size= * * ;

如果设定索引缓存的结构体变量组件变量 key_buffer_size 或 key_cache_block_size 任何一个的值和它当前的值不一样 服务器就会清空原来的缓存 在新的变量值基础上重建缓存 如果缓存中有任何的 脏 索引块 服务器会先把它们保存起来然后才重建缓存 重新设定其他的索引缓存变量并不会重建缓存

lishixinzhi/Article/program/Oracle//

mysql数据库的优化方法？

我们都知道，服务器数据库的开发一般都是通过java或者是PHP语言来编程实现的，而为了提高我们数据库的运行速度和效率，数据库优化也成为了我们每日的工作重点，今天，昌平IT培训就一起来了解一下mysql服务器数据库的优化方法。

为什么要了解索引

真实案例

案例一：大学有段时间学习爬虫，爬取了知乎300w用户答题数据，存储到mysql数据中。那时不了解索引，一条简单的“根据用户名搜索全部回答的sql“需要执行半分钟左右，完全满足不了正常的使用。

案例二：近线上应用的数据库频频出现多条慢sql风险提示，而工作以来，对数据库优化方面所知甚少。例如一个用户数据页面需要执行很多次数据库查询，性能很慢，通过增加超时时间勉强可以访问，但是性能上需要优化。

索引的优点

合适的索引，可以大大减小mysql服务器扫描的数据量，避免内存排序和临时表，提高应用程序的查询性能。

索引的类型

mysql数据中有多种索引类型，primarykey，unique，normal，但底层存储的数据结构都是BTREE;有些存储引擎还提供hash索引，全文索引。

BTREE是常见的优化要面对的索引结构，都是基于BTREE的讨论。

查询数据简单暴力的方式是遍历所有记录;如果数据不重复，就可以通过组织成一颗排序二叉树，通过二分查找算法来查询，大大提高查询性能。而BTREE是一种更强大的排序树，支持多个分支，高度更低，数据的插入、删除、更新更快。

现代数据库的索引文件和文件系统的文件块都被组织成BTREE。

btree的每个节点都包含有key，data和只想子节点指针。

btree有度的概念d>=1。假设btree的度为d，则每个内部节点可以有n=[d+1，2d+1)个key，n+1个子节点指针。树的大高度为h=Logb[(N+1)/2]。

索引和文件系统中，B-TREE的节点常设计成接近一个内存页大小(也是磁盘扇区大小)，且树的度非常大。这样磁盘I/O的次数，就等于树的高度h。假设b=100，一百万个节点的树，h将只有3层。即，只有3次磁盘I/O就可以查找完毕，性能非常高。

索引查询

建立索引后，合适的查询语句才能大发挥索引的优势。

另外，由于查询优化器可以解析客户端的sql语句，会调整sql的查询语句的条件顺序去匹配合适的索引。