数据库相关学习笔记整理

数据库相关的学习笔记,仅作为回顾和复习备用.

数据库调优方法:

1. 如果是应用的问题: 排查应该用索引的地方没有加索引,索引失效了,单表数据量过大,单表行数超500万应该分表,程序异常数据库连接没有释放;
2. 数据库问题 连接资源池不够用,硬件资源不够用,查看是否有共享内存 CPU负载,
3. 请求量太大的问题, 分库分表也不能满足时,将热点数据在Redis做缓存,如果缓存仍然不能满足,将查询字段放在ES大数据组件中,先查ES,拿到主键后再查MySQL, 上层接口循环调用考虑异常情况限流.

索引调优方法:

索引调优,
种类,普通,主键,唯一,联合,覆盖,全文索引合理选择创建索引的列,查询频繁的创建索引,修改过多的不适合,重复值多的不适合,少用in 用union all替代

1. 数据库慢查询 原因.SQL编写问题:没有使用索引或索引没有起作用(索引中有or,like以%开头, 需要类型转换,索引列有运算.使用了函数,复合索引未用左列字段); 查询结果较大,返回不必要的值; 锁使用不合理或出现死锁; 查询语句未优化; i/o读写慢

2. 调优方法: 不要使用select*, OR的查询尽量使用union代替,尽量少用视图,不必要时不用distinct和order by,可以改在客户端执行,尽量选择分区度高的列作为索引,删除表用truncate,不要用delete(truncate后,表和索引所占空间会恢复到初始大小,delete不会减少表和索引的所占空间.

3. 数据库优化: 加索引,限定时间段,exists比in效率高,尽量避免子查询,不可避免时在子查询中过滤掉尽可能多的行;海量查询时少用格式转换,union all比union性能好,不去重不排序

4. 索引的分类. 普通索引:数据可以重复
   ,唯一索引, 要求记录唯一
   全文索引:对大表的文本域char,varchar,text进行索引
   主键索引:唯一索引的基础上列必须是主键

5. MySQL数据类型,数值型5种int型,日期5种,字符串8种;

6. MySQL和SQL区别SQL是一种查询语言,MySQL是数据库软件,MySQL使用SQL来查询数据库

7. sql怎么创建索引create index idx_name from tabel(name)

8. 索引的实现原理使用B树和变种B+树的数据结构,以某种方式引用数据,可以在这种数据结构上实现高级查找算法.

9. 事务的四大特性:原子性,一致性,隔离性,持久性

10. 索引: 索引是一种数据结构,通过缩小一张表需要查询的行数目来加快搜索的速度;
    最常用的索引结构是B-TRee,时间复杂度低,查找删除,插入都可以在对数时间内完成,数据是有序的,包含一个表的列的值.
    索引存储了某一行的指针,某列查询的非常频繁,就在这列上创建索引

11. 创建索引 CREATE INDEX name_index ON employee(Employee_name)

12. 唯一索引和主键的区别
    1)主键为约束,索引为索引,本质不同
    2)主键只能有一个,索引可以有多个;
    3)主键不能为null,唯一索引可以为null
    4)主键创建后一定包含唯一索引,但唯一索引不一定包含主键
    5)主键可以被其他表引用,唯一索引不能
    6)主键是逻辑键,索引是物理键,主键不实际存在

13. 数据库三大范式1NF 原子性,数据库所有字段都是单一不可再分的;
    2NF依赖性,要有主键,其他字段都依赖主键
    3NF所有属性都要和实体相关,不能传递相关性,范式是设计数据库的规范和基本理念.
    常用的一些SQL语句

14. 找出有一个科目成绩大于90分的学生IDselect s.name from student s join score sc on s.id=sc.id group by s.name having sc.score>90
    select s.name from student s join score sc order by sum(sc.score) desc limit 10

15. 建表create database student_coures
    show databases;
    use database student_coures;
    create table Student( id serial,content varchar(800) not null,
    ) engine innodb;

16. 去重select distinct name from table

17. 嵌套查询inin select S.name from student where Sdept in(select Sdept from student Sname=‘d’) (in可以换成=)

18. 查询每个学生超过他平均成绩的课程号
    select Sno,cno from sc x,where grade>(select avg(score) from sc where sc.no=s.no)

19. 查询student中年龄小于最大值的所有人
    select sanme,asge from student where sage<any (select sage from student);

20. 查询所有选修了005课程的学生
    select sname from student where exists(select * from sc where sc.no=stu.no and cno=005’)查询

21. 查询student表中所有人和年龄大于24的人
    select * from student union select * from student where sange>24(union 会去重,不想去重可以用union all)

22. 派生表起别名select A.name from (select * from s) A where A.sage=22