【MySQL的索引和函数】

MySQL的索引和函数

索引

一、什么是索引

索引，使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。 在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

二、索引优点和缺点

优点：

• 1.创建唯一性索引，保证数据库表中每一行数据的唯一性
• 2.大大加快数据的检索速度，这也是创建索引的最主要的原因
• 3.减少磁盘IO（向字典一样可以直接定位）

缺点：

• 1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
• 2.索引需要占用额外的物理空间
• 3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

三、索引的类型

• 普通（Normal）：
  也叫非唯一索引，是最普通的索引，没有任何的限制。
• 唯一（Unique）：
  唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用 primay key创建。
• 全文（Fulltext）：
  针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数 据的这种情况，如果要解决like 查询效率低的问题，可以创建全文索引。只有文本类型 的字段才可以创建 全文索引，比如 char、varchar、text。
• 聚合索引：
  由多个字段共同组成的索引。

四、索引优化

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描
- 如果需求是要在前面使用模糊查询，使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()
2. 尽量避免使用in 和not in，会导致引擎走全表扫描
3. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描
4. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描
5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描
- 可以将表达式、函数操作移动到等号右侧 ( 不建议这样写会造成运行效率降低 ** )
6. 当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描
7. 查询条件不能用 < > 或者 !=
- 使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。要去java进行判断，不能对数据库进行操作
8. where条件仅包含复合索引非前置列
- 如下：复合（联合）索引包含a1，b2，c3三列，但SQL语句没有包含索引前置列"a1"，按照MySQL联合索引的最左匹配原则**，不会走联合索引
9. 隐式类型转换造成不使用索引
- 如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

事物

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败
事务的四大特征：ACID

• 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。
• 持久性：当事务提交或回滚后，数据库会持久化的保存数据。
• 隔离性：多个事务之间。相互独立。
• 一致性：事务操作前后，数据总量不变

函数

一、字符串函数

• 说明
  
• 案例
  select CONCAT(‘a’,‘b’,‘c’)
  
  select LOWER(‘ABC’)
  
  select UPPER(‘abc’)
  
  select LPAD(‘123’,5,‘000’)
  
  select RPAD(‘123’,5,‘000’)
  
  select TRIM(’ aa ')
  select SUBSTRING(‘18035185213’,3,4)
  

2、数值函数

案例：

# 2、数值函数
# 向上取整
select CEIL(11.1)
# 向下取整
SELECT FLOOR(12.8)
# 返回x/y的模（余数）
SELECT MOD(6,3)
# 返回0~1内的随机数
SELECT RAND()
# 求参数的四舍五入的值，保留 n位小数
SELECT ROUND(3.141592618,4)

3、日期和时间函数

# 日期函数
# 返回当前日期
SELECT CURDATE()
# 返回当前时间
SELECT CURTIME()
# 返回当前日期和时间
SELECT NOW()
# 获取指定的的年份
select YEAR(NOW())
# 获取指定的月份
select MONTH(NOW())
# 获取指定的日期
SELECT DAY(NOW())
# 返回一个日期时间值加上一个时间间隔
select date_add(NOW(),INTERVAL 1 YEAR)
select DATE_ADD(now(),INTERVAL 2 DAY)
SELECT DATE_ADD(NOW(),interval 1 MONTH)
# 返回时间1和时间2之间的天数
select DATEDIFF("2023-06-12 23:59:59","2022-06-12 00:31:22")