数据库

数据库的引擎和区别

Mysql的数据库引擎常用的有两种:

InnoDB

• 支持事务
• 支持行锁和表锁

MyISAM

• 不支持事务
• 支持全文索引
• 查询速度比较快
• 只支持表锁

什么是表锁?什么是行锁?

当用户对表进行操作的时候,对表(或者行)进行上锁,不让其他用户进行操作,(除了查询之外)知道该用户释放锁,

下一个用户才能够进行操作

 

为什么要有锁?

为了数据的安全

想象一个场景,如果某东发放100张满1000减900的优惠券,出现了大量的并发操作,

如果没有锁的话那么可能在同一时刻两个用户每人抢到一张,但是数据库没有及时更新

只减了一次.所以加锁是很有必要的.

 

什么是触发器?

在数据库进行增删改前后自定义操作-----(触发器存储在数据库中)

比如:我给A表插入一条数据之前先给B表插入一条数据,

给Abiao插入一条数据之后给C表删除一条数据

触发器用法

 

什么是视图?

对某些表进行sql查询将结果实时的显示出来,本质上是sql语句只能够进行查询.

视图是虚拟表,存放在数据库中

视图的用法

 

什么是存储过程?

存储过程是一组予编译的sql语句
它的优点：

• 存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
• 当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
• 更好的安全机制，对于没有权限执行存储过程的用户，也可授权他们执行存储过程。
• 存储过程可以重复使用,可减少数据库开发人员的工作量

存储过程用法

 

数据库常用的函数

聚合函数(max,avg,min,sum,count)

时间格式化

字符串的拼接

 

什么是索引?

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

最主要的就是加速查找

Mysql常用的索引

• 普通索引：仅加速查询(index)
• 唯一索引：加速查询 + 列值唯一（可以有一个null值 unique）
• 主键索引：加速查询 + 列值唯一 +　表中只有一个（不可以有null pramary key）
• 联合索引：多列值组成一个索引，
  • 专门用于联合搜索，其效率大于索引合并
  • 遵循最左前缀的规则
• 全文索引：对文本的内容进行分词，进行搜索 (全文索引需要指定长度)
• 联合唯一索引:多列值组成一个唯一的索引

索引合并，使用多个单列索引组合搜索
覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

 

如何命中索引

- like '%xx'
    select * from tb1 where name like '%cn';
- 使用函数
    select * from tb1 where reverse(name) = 'wupeiqi';
- or
    select * from tb1 where nid = 1 or email = 'seven@live.com';
    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
- 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where name = 999;
- !=
    select * from tb1 where name != 'alex'
    特别的：如果是主键，则还是会走索引
        select * from tb1 where nid != 123
- >
    select * from tb1 where name > 'alex'
    特别的：如果是主键或索引是整数类型，则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123
- order by
    select email from tb1 order by name desc;
    当根据索引排序时候，选择的映射如果不是索引，则不走索引
    特别的：如果对主键排序，则还是走索引：
        select * from tb1 order by nid desc;
 
- 联合索引最左前缀
    如果联合索引为：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

 

优化数据库的方案

-- 不用select *查询

-- 固定字段列放在前面

-- char和vachar 固定用char
 
-- 尽量使用短索引
 
-- 取一条数据的时候需要用limit 1

-- 将固定的数据放入内存 :choice

-- 访问量大的可以不做连表操作,允许有一定的数据冗余,或者把访问量大的数据放入内存

-- 读写分离 (访问量大)利用数据库的主从进行分离,主用于增删改,从用于查(数据需要异步更新)

-- 分库 当数据库表太多,将表分到不同的数据库中

-- 分表  
    --水平分表 (将某些列拆分到另外一张表中)如:文章/文章详细
    --垂直分表(将历史信息分到另外一张表中) 比如 历史的新闻,账单

-- 缓存 将数据放入缓存中(redis)

-- text列如果想创建索引,必须指定长度

 

 

 

数据库分页页码越大速度越慢

为什么?

因为用户在翻页的时候,数据库会对表进行扫描,

如果用户翻到了100页,要翻101页,那么数据库需要进行扫描到第101页的limit值

越来越慢是他需要扫描的数据越来越多.

如何解决?

根据业务需求,如果对历史信息的利用率不高的话,那么可以限制页码最多查200页,

比如百度就是最多查询76页

 

还有一种方案就是只允许上一页和下一页进行翻页,那么记录当前ID的最大值和最小值

在翻译的时候根据条件进行赛选, 赛选完毕和用limit offset 进行查询,但是这个需要对页码进行

加密防止用户篡改页码