面试之——数据库相关
1 数据库引擎有哪些?
innodb
事务
行锁/表锁:
表锁 select * from tb for update; 行锁: select id,name from tb where id=2 for update; session1 set autocommit = 0; select * from users for update; commit; session2 set autocommit = 0; select * from users for update; # 如果session1不提交(commit), session2对表操作会夯住,时间长了会超时
myisam
全文索引(查询快)
表锁:
select * from tb for update;
表锁: lock table film_text read local; select * from tb for update; unlock tables;
更多锁 https://blog.csdn.net/mysteryhaohao/article/details/51669741
2、设计表 ?
考的就是 fk m2m
待续:
数据库部分：课程， 老师， 学生， 分数
博客题
权限表
搞明白这些表为什么这样设计
3、表设计及使用优化:
查询不要用 select * .... 创建数据库表的时候,固定长度的往前方放（char固定长度 varchar不固定长度） 对FK来说，如果关联选项就固定的几个，选择choices字段，不要再特意建个表放到数据库，而是应该放到内存 读写分离: 利用数据库的主从库: 主库(增删改), 从库(查) 主从库通信同步数据 原生SQL: select * from db.tb ORM: model.User.objects.all().using("default") settings.py: DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'USER': 'root', ...... } } Flask: 路由 db router 分库: 数据库中的表太多(所有的请求都来,压力大),将表分到不同的数据库 缺点: 连表性能降低 分表: 水平分表: 拆字段 (User, UserDetail) 垂直分表: 拆数据记录 (支付宝 账单/消息 历史记录) 挡缓存: 把经常用到的数据放到缓存,请求直接取缓存,减轻数据库的访问压力 redis, memcache(缓存里面也不要放太多的数据) 缺点: 缓存万一挂掉,大量访问扎堆去数据库,数据库永远也起不来,即使起来,很快也挂掉
4、常用的查询语句？
待续。。。
5、触发器
触发器(trigger)：监视某种情况，并触发某种操作。（插入前插入后，更新前更新后。。。）
触发器创建语法四要素：
1.监视地点(table) 2.监视事件(insert/update/delete) 3.触发时间(after/before) 4.触发事件(insert/update/delete)
1.创建触发器语法：
create trigger triggerName after/before insert/update/delete on # 表名 for each row #这句话是固定的 begin ... #需要执行的sql语句 end
注意1:after/before: 只能选一个 ,after 表示 后置触发, before 表示前置触发
注意2:insert/update/delete:只能选一个
6、内建函数
mysql提供的内建函数
max（）， min（）， sum（）， avg（），concat， date_format
1. 字符串长度： select char_length(str); select char_length('中国'); # 长度 2 （字符） select length(str); select length('中国'); #长度 6 （字节） 2.字符串拼接concat: select concat(str1,str2,..); # (主要用在数据库迁移上) select concat('a','中国',1); # a中国1 select concat('a','中国',1,null); # null PS:如果有任何一个参数是Null, 则返回null select concat_ws(separator,str1,str2,...) # separator 代表分隔符 (自定义连接符) select concat_ws('*','a','中国',1); # a*中国*1 select concat_ws('*','a', ' ' ,1); # a**1 PS:如果有 ' ' 空字符串, 不会忽略 select concat_ws('*','a',null,1); # a*1 PS:如果有 null ,会忽略所有的 null 3. 格式化输出 select at(X,D) # X:数字 D:保留位数 4. 指定位置替换字符串 select insert(str,pos,len,newstr) 5. 打印当前日期时间: select curdate() # 2016-12-13 select curtime() # 19:08:36 select now() # 2016-12-13 19:08:43 6. date_format select date_format(ctime,'%Y年%m月%d日%k时%I分%s秒') from users; # 支持汉字 select now(); # 获取系统当前时间 select date_format(now(),'%y-%m-%d');
日期格式化： https://blog.csdn.net/kangbrother/article/details/7030304
7、存储过程（知道概念）
将SQL语句保存到数据库中，并命名；以后在代码中调用时，直接发送名称。
参数：
in ；out；inout
#!/usr/bin/env python # -*- coding:utf-8 -*- import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 执行存储过程 cursor.callproc('p1', args=(1, 22, 3, 4)) # 获取执行完存储的参数 cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") result = cursor.fetchall() conn.commit() cursor.close() conn.close() print(result) mysql: delimiter // # mysql遇到";"就立即执行,为了输入多行代码,临时把";"改成其他符号 create procedure p1(in n1 int, inout n3 int, out n2 int) begin declare temp1 int; # 声明变量temp1 declare temp2 int default 0; select * from v1; set n2 = n1 + 100; # 给n2赋值 set n3 = n3 + n1 + 100; # 给n3赋值 end // delimiter ;
函数和存储过程有什么区别?
相同点:
都需要传参数
都有返回值
不同点:
函数返回值: return,
存储过程返回值: out/inout
存储过程还能返回结果集: select * from tb
MySQL数据库在5.0版本后开始支持存储过程 1.什么是存储过程： 类似于函数(方法),简单的说存储过程是为了完成某个数据库中的特定功能而编写的语句集合，该语句集包括SQL语句（对数据的增删改查）、条件语句和循环语句等。 2. 存储过程优点： 1、存储过程增强了SQL语言灵活性。存储过程可以使用控制语句编写，可以完成复杂的判断和较复杂的运算，有很强的灵活性； 2、减少网络流量，降低了网络负载。存储过程在数据库服务器端创建成功后，只需要调用该存储过程即可，而传统的做法是每次都将大量的SQL语句通过网络发送至数据库服务器端然后再执行； 3、存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 4、系统管理员通过设定某一存储过程的权限实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。 3. 什么时候用存储过程: 涉及到 钱 等很严谨的场景,用存储过程. 4. 写在哪儿？ 写在数据库
8、索引
单列：
普通索引：index # 加速查找
唯一索引：unique # 加速查找 + 约束（不能重复）
主键：primary key # 加速查找 + 约束（不能为空）
多列：
联合索引 # 联合索引遵循： 最左前缀原则
联合唯一索引
其他专业名词：
索引合并： 利用多个单列索引查询
覆盖索引： 在索引表中就能得到想要查询的数据
1. 索引采用的数据结构？
B+树 数据结构
哈希索引结构（都不重复的可以做成哈希索引）
2. 创建了索引，应该如何命中索引？ （哪种情况下无法命中索引？）
首先应该知道：
如果创建索引， 表之外还会创建一个额外的文件
增、删、改 都会变慢 （因为还要更新那个额外的索引文件）
所以要正确的使用索引：
1》聚合函数 索引无效 2》联合索引不遵循最左规则，一定无法命中索引 3》like “%xx” 4》or 条件有不是索引的列 5》列类型如果是字符串类型，需要加引号 select * from tb1 where name = 999 # 这是不行的 主键=999除外 6》order by 后面的条件也必须是
更多索引：http://www.cnblogs.com/wupeiqi/articles/5716963.html
9、如何开启慢日志查询？
配置MySQL自动记录慢日志（了解）
slow_query_log = OFF # 是否开启慢日志记录 long_query_time = 2 # 时间限制，超过此时间，则记录 slow_query_log_file = /usr/slow.log # 日志文件 log_queries_not_using_indexes = OFF # 为使用索引的搜索是否记录（记录哪些没有使用索引查询） 直接放到配置文件就行了
10、执行计划
explain + 查询SQL # 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化
mysql> explain select * from tb2; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | <span style="color: #ff0000">type</span> | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | tb2 | <span style="color: #ff0000">ALL</span> | NULL | NULL | NULL | NULL | 2 | NULL | +----+-------------+-------+------+---------------+------+---------+------+------+-------+
type字段比较重要
type
查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
ALL 全表扫描，对于数据表从头到尾找一遍 select * from tb1; 特别的：如果有limit限制，则找到之后就不在继续向下扫描 select * from tb1 where email = 'seven@live.com' select * from tb1 where email = 'seven@live.com' limit 1; 虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。 INDEX 全索引扫描，对索引从头到尾找一遍 select nid from tb1; RANGE 对索引列进行范围查找 select * from tb1 where name < 'alex'; PS: between and in > >= < <= 操作 注意：!= 和 > 符号 INDEX_MERGE 合并索引，使用多个单列索引搜索 select * from tb1 where name = 'alex' or nid in (11,22,33); REF 根据索引查找一个或多个值 select * from tb1 where name = 'seven'; EQ_REF 连接时使用primary key 或 unique类型 select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid; CONST 常量 表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。 select nid from tb1 where nid = 2 ; SYSTEM 系统 表仅有一行(=系统表)。这是const联接类型的一个特例。 select * from (select nid from tb1 where nid = 1) as A;
11、数据库分页
limit offset
select * from users limit 3 offset 5 ORM: models.Users.objects.all()[0:10] # ORM的本质就是limit offset
使用 limit offset 有个缺陷:
第一页很快，第二页快，，，越往后越慢
数字越大(数据越靠后)，查询速度越慢，因为不管从哪儿开始分页，都得老老实实从头开始一行一行找（不是直接跳到目标行），每次都从头找一遍，所以说反而更慢
select * from users limit 10 offset 0 select * from users limit 10 offset 10 ... select * from users limit 10 offset 10000 # 查询10000~100010 仍然要从第一条开始一行一行的查找 select * from users limit 10 offset 10010 # 查询10010~100020 仍然要从第一条开始一行一行的查找
解决办法：
1. 把缺陷规避掉：按照业务需求，看是否可以设置：只让用户看N页（cnblogs只能看200页）
2. 只显示上一页和下一页按钮
记录当前页的 max_id，min_id，根据条件先去数据库筛选，再limit offset
select * from (select * from users where id > 88888) as U limit 10 offset 0
对url里面的页码进行加密（防止用户自己再url里面修改页码，导致查询速度变慢）
（应用： rest_framework的加密分页器CursorPagination）
注意：先查主键，在分页。（这个方法几乎没作用）
select * from tb where id in (
select id from tb where limit 10 offset 30
)
12、数据库导入导出?
数据库能导入到文件里面excel?
导出现有数据库数据：
◇ mysqldump -u用户名 -p密码 数据库名称 >导出文件路径 # 结构+数据 ◇ mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径 # 结构
导入现有数据库数据：
◇ mysqldump -uroot -p密码 数据库名称 < 文件路径
更多：
http://www.cnblogs.com/wupeiqi/articles/5748496.html
浙公网安备 33010602011771号