[Mysql-函数、索引]

目录

函数：

日期函数

 字符串函数

数学函数

聚合函数 

索引： 

索引分类 

慢查询

创建索引

---

函数：

MySQL函数，是一种控制流程函数，属于数据库用语言。

MySQL常见的函数有：

数学函数 用作常规的数学运算

字符串函数 对于字符串类型的字段处理

聚合函数 常用于GROUP BY从句的 SELECT查询中

日期时间函数 对于日期和时间类型的字段进行 处理

自定义函数 根据实际需求自定义函数

这里主要写一些比较常用的函数的应用：

日期函数

curdate() --返回当前日期
curtimu() --返回当前时间
now() -- 返回当前日期和时间

date_format(date,fmt) --依照指定的fmt格式格式化日期date值

month(date) --返回date的月份值
day(date) --返回date的日
year(date) --返回日期date的年份

select datediff(now(),'2024-6-24') -- 只能算天数差
select timestampdiff(second,'2002-9-25',now()); -- 秒差

-- 时间间隔算时间
select now() + interval 5 year;
select now() - interval 5 year;
select date_add(now(),interval 10 year);                   

 字符串函数

-- 字符串拼接
select concat('hello','world')
select concat_ws('#','hello','world')

数学函数

ceiling(3.5) --向上取整
floor(3.5) --向下取整
round(3.556,2) --返回参数x的四舍五入的有y位小数的值
select truncate(3.555,1) -- 截断 

聚合函数 

-- sum()

-- count()

-- avg()

-- max()

-- min()

以上五个在之前的文章中经常使用，这里就不重复

-- gruop_concat

 gruop_concat
select ssex,group_concat(sname) from student group by ssex;

索引： 

索引是一个比较重要的知识点！

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

优点：高效性：利用索引可以提高数据库的查询效率

           完整性：用户可以加速表和表之间的连接，实现表与表之间的参照完整性

           唯一性：索引可以确保所查的数据的唯一性

           特殊能力：通过使用索引，可以在查询过程中，用优化隐藏器，提高系统性能。

缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、 UPDATE和DELETE。

因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘 空间的索引文件。

如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立 最优秀的索引，或优化查询语句。

索引分类 

 主键索引 ：在数据库关系图中为表定义一个主键将自动 创建主键索引。 
唯一索引 ：不允许具有索引值相同的行，从而禁止重复 的索引或键值。 
常规索引 ：最基本的索引类型，没有 唯一性之类的限制。 
全文索引 ：搜索引擎的关键技术，用于检索文本信息，可以是词语或者段落。

为什么要使用索引呢？

这里先说一个知识点，慢查询。

慢查询

什么是慢查询 ？

MySQL默认10秒内没有响应SQL结果,则为慢查询

 Mysql对慢查询的操作：
extra列返回的描述的意义

-- 显示到mysql数据库的连接数 
show status like 'connections'; 

-- 查看慢查询的状态 
Show variables like '%slow_query%'; 

-- 设置慢查询的到表 
mysql.slow_log set global log_output='TABLE'; 

-- 设置慢查询的时间 
set global long_query_time=3; 

-- 开启慢查询 
set global slow_query_log='ON'; 

-- 慢查询的次数 
show status like 'slow_queries'; 

-- 慢查询记录 
select * From mysql.slow_log ; 

-- 慢查询sql语句 
select convert(sql_text using utf8) sql_text from mysql.slow_log 

-- 关闭慢查询 
set global slow_query_log='OFF'; 

那么当使用索引时就可以提高查询效率。 

创建索引

-- 基本格式

-- 主键索引
CREATE TABLE `表名` (
`字段1` INT(11) AUTO_INCREMENT PRIMARY KEY,
#或 PRIMARY KEY(`字段1`)
)

-- 唯一索引
CREATE TABLE `表名` (
`字段1` INT(11) NOT NULL UNIQUE,
#或 UNIQUE KEY(`字段1`)
)

-- 常规索引
CREATE TABLE `表名` (
`字段1` INT(11) NOT NULL,
INDEX/KEY(`字段1`)
)

-- 全文索引
CREATE TABLE `表名` (
`字段1` VARCHAR(32) NOT NULL,,
fulltext key (字段名，字段名，字段名) with parser ngram
)ENGINE=innodb
-- 用法
SELECT <字段表> FROM <表名> 
WHERE MATCH(字段) 
AGAINST (‘要搜索的关键词’);

-- 应用举例，全文索引
create table wenzhang(
    wid int PRIMARY KEY auto_increment,
    title varchar(20),  
    content text, 
    zuozhe varchar(20), 
    FULLTEXT(title,content,zuozhe) with parser ngram -- 中日韩
);

insert into wenzhang(title,content,zuozhe) 
values
('西安往事','这是一个古老的城市，在这个城市中有很多的人，工厂，建筑物','小杨'),
('山西往事','这是一个古老的城市，这里有很多的人，工厂，建筑','老陶'),
('地球往事','这是一个古老的星球，这里有很多的人','老刘在西安'),
('银河往事','这是一个系，打算在这个系之外造一个西安','小彭');

select * from wenzhang where match(title,content,zuozhe) AGAINST('西安');

select * from wenzhang where match(title,zuozhe) AGAINST('西安');

alter table wenzhang add fulltext(title,zuozhe)with parser ngram;

附一些索引常见面试题：

索引按照物理实现⽅式，索引可以分为 2 种：聚簇（聚集）和⾮聚簇（⾮聚集）索引。我们也把⾮聚集 索引称为⼆级索引或者辅助索引。

        聚簇索引：

                特点： 1. 使⽤记录主键值的⼤⼩进⾏记录和⻚的排序，这包括三个⽅⾯的含义： ⻚内 的记录是按照主键的⼤⼩顺序排成⼀个 单向链表 。 各个存放⽤户记录的⻚也是根据⻚中⽤户记录的主键⼤⼩顺序排成⼀个双向链表 。 存放⽬录项记录的⻚分为不同的层次，在同⼀层次中的⻚也是根据⻚中⽬录项记录的主键⼤⼩顺序排成⼀个双向链表 。

                             2. B+树的 叶⼦节点 存储的是完整的⽤户记录。 所谓完整的⽤户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

                优点： 数据访问更快 ，因为聚簇索引将索引和数据保存在同⼀个B+树中，因此从聚簇索引中获取数据⽐ ⾮ 聚簇索引更快 聚簇索引对于主键的 排序查找 和 范围查找 速度⾮常快 按照聚簇索引排列顺序，查询显示⼀定范围数据的时候，由于数据都是紧密相连，数据库不⽤从多 个数据块中提取数据，所以 节省了⼤量的io操作 。

                缺点： 插⼊速度严重依赖于插⼊顺序 ，按照主键的顺序插⼊是最快的⽅式，否则将会出现⻚分裂，严重 影 响性能。因此，对于InnoDB表，我们⼀般都会定义⼀个⾃增的ID列为主键 更新主键的代价很⾼ ，因为将会导致被更新的⾏移动。因此，对于InnoDB表，我们⼀般定义主键 为 不可更新。

        ⼆级索引（辅助索引、⾮聚簇索引） ： 概念：回表 以某列⼤⼩排序的B+树只能确定我们要查找记录的主键值，所以如果我们想根 某列的值查 找到完整的⽤户记录的话，仍然需要到 聚簇索引 中再查⼀遍，这个过程称为 回表 。也就是根据某列的值查询⼀条完整的⽤户记录需要使⽤到 2 棵B+树！  ⼆级索引访问需要两次索引查找 ，第⼀次找到主键值，第⼆次根据主键值找到⾏数据

B-Tree和B+Tree:

B-Tree结构的数据可以让系统⾼效的找到数据所在的磁盘块。

        为了描述B-Tree，⾸先定义⼀条记录为⼀ 个⼆元组(key,data]，key为记录的键值，对应表中的主键值，data为⼀⾏记录中除主键外的教据。对于 不同的记录，key值互不相同。 

B+Tree 所有的叶⼦结点中包含了全部关键字的信息，⾮叶⼦节点只存储键值信息，及指向含有这些关键字 记录的指针，且叶⼦结点本身依关键字的⼤⼩⾃⼩⽽⼤的顺序链接，所有的⾮终端结点可以看成是 索引部分，结点中仅含有其⼦树根结点中最⼤(或最⼩)关键字。(⽽B树的⾮终节点也包含需要查找的 有效信息)

        所有叶⼦节点之间都有⼀个链指针。

        数据记录都存放在叶⼦节点中。