数据库基础 --索引

索引

一. 索引的概念

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

索引就是一个数据结构，b树结构，作用就是提高查询效率

二. 索引的优点以及缺点

1. 优点
   • 1.创建唯一性索引，保证数据库表中每一行数据的唯一性
   • 2.大大加快数据的检索速度，这也是创建索引的最主要的原因
   • 3.减少磁盘IO（向字典一样可以直接定位）
2. 缺点
   • 1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
   • 2.索引需要占用额外的物理空间
   • 3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

三. 索引类型

普通（Normal）：

也叫非唯一索引，是最普通的索引，没有任何的限制。

唯一（Unique）：

唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用 primay key创建。

全文（Fulltext）：

针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数 据的这种情况，如果要解决like 查询效率低的问题，可以创建全文索引。只有文本类型 的字段才可以创建 全文索引，比如 char、varchar、text。

聚合索引：

由多个字段共同组成的索引。

四.索引的优化方式

1. 避免使用左模糊查询

SELECT * FROM user WHERE name LIKE '%用%'

-- 优化方式：尽量在字段后面使用模糊查询
SELECT * FROM user WHERE name LIKE '用%'

2. 避免使用 in 或 not in

SELECT * FROM user WHERE id IN (2,3)

-- 优化方式：如果是连续数值，可以用between代替
SELECT * FROM user WHERE id BETWEEN 2 AND 3

3. 避免使用 or

select * from user WHERE id = 1 or id = 3

-- 优化方式：可以用union（且）代替or
select * from user WHERE id = 1
UNION
select * from user WHERE id = 3

4. 避免判断 null 值

select * from user WHERE age is null 

-- 优化方式：可以给字段添加默认值0，对0值进行判断
select * from user WHERE age = 0

5. 避免在 where 等号左侧使用表达式，函数操作

-- 全表扫描
SELECT * FROM user WHERE age/10 = 9
-- 走索引
SELECT * FROM user WHERE age = 10*9

6. 数据量大时避免使用 where 1=1 条件

SELECT name, age, pwd FROM user where   1=1 and 条件 

-- 优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and
SELECT name, age, pwd FROM user WHERE 条件1 and 条件2

7. 查询条件不能使用 大于，小于，不等于
8. 使用联合索引时，要遵循最左匹配原则

-- 创建联合索引
create index user_index on user (name,age,pwd)
-- 这种方式是不会走索引的
select name,age,pwd from user where age=123 and pwd='312'

9. 避免隐式类型转换造成索引失效

select name from table where name = ‘123; 

事务

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

事务的四大特征：ACID

1. 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败。

2. 持久性：当事务提交或回滚后，数据库会持久化的保存数据。

3. 隔离性：多个事务之间。相互独立。

4. 一致性：事务操作前后，数据总量不变

函数

一. 字符串函数

函数	功能
concat(s1,s2,…sn)	字符串拼接，将 s1，s2 ，sn 拼接成一个字符串
lower(str)	将字符串 全部转为小写
upper(str)	将字符串 全部转为大写
lpad(str,n,pad)	左填充，用字符串pad对 的左边进行填充，达到 个字符串长度
rpad(str,n,pad)	右填充，用字符串pad对 的左边进行填充，达到 个字符串长度
trim(str)	去掉字符串头部和尾部的空格
sbustring(str,start,len)	返回从字符串 str从start位置起的len个长度

代码示例：

二. 数值函数

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回 x/y的模
rand()	返回 0~1内的随机数
round(x,y)	求参数 的四舍五入的值，保留 位小数

代码示例：

三. 日期函数

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定 date的年份
monty(date)	获取指定 date的月份
day(date)	获取指定 date的日期
date_add(date,interval expr type)	返回一个日期 时间值加上一个时间间隔
datediff(date1,date2)	返回时间date1和时间date2之间的天数

代码示例：