面試題之数据库

- 引擎
            - innodb
                - 事务
                - 行锁/表锁
                    - 表锁：
                        select * from tb for update;
                    - 行锁：
                        select id,name from tb where id=2 for update ;
            - myisam
                - 全文索引
                - 快
                - 表锁
                    - select * from tb for update;

索引

- 索引: B+/哈希索引   =》 查找速度快；更新速度慢
            单列：
                - 普通索引：加速查找
                - 唯一索引: 加速查找 + 约束：不能重复
                -    主键: 加速查找 + 约束：不能重复 + 不能为空
            多列：
                - 联合索引
                - 联合唯一索引
                PS：遵循最左前缀的规则
            其他词语：
                - 索引合并，利用多个单例索引查询，根据这两个索引来查询，没有联合索引快；
                - 覆盖索引，在索引表中就能将想要的数据查询到；
　　

 

 

- 创建了索引，应该如何命中索引？  
　　那种情况下，创建了无法命中索引？记住！！！！！
            - 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        -- 这样查询不能使用索引
                
            补充查询优化方案： 
                a.不用select *   
                b.固定长度的字段放在最前边，
                c.char(固定长度)和varchar（不固定长度）
                d.联合索引最左前缀
　　　　　　　　　e.固定数据放内存中（访问频率高的数据，提高访问查询速度）
　　　　　　　　　F.读写分离，创建一个主从表，  读从  从表中，写写到主表，  定期往从表中写入数据
　　　　　　

　　　　　　　　　g.分库，表很多的时候放在不同的库中去，（注意要知道那个库中有哪些表， 连表查询的时候也有代价）
　　　　　　　　　　　　-水平分表，将某些列拆分到一张表中去  例如  博客,博客详情
　　　　　　　　　　　　-垂直分表，将历史信息分到另外一张表中，例如  账单（最近账单和年代久远的账单）
　　　　　　　　h.缓存：将常数据利用redis memcach

数据库分页

        
        - 数据库分页   ：存在问题  越往后查询越慢，因为他是从开头到页码遍历一遍
            select * form tb limit 10 offset 0
            select * form tb limit 10 offset 10
            select * form tb limit 10 offset 20
            select * form tb limit 10 offset 30
            ...
            select * form tb limit 10 offset 3000000
            解决方案：：
            答案一：
                先查主键，在分页。
                select * from tb where id in (
                    select id from tb where limit 10 offset 30
                )
            答案二  常用：
                按照也无需求是否可以设置只让用户看200页
                
            答案三  常用：
                记录当前页  数据ID最大值和最小值
                在翻页时，根据条件先进行筛选；筛选完毕之后，再根据limit offset 查询。
                
                select * from (select * from tb where id > 22222222) as B limit 10 offset 0
                
                如果用户自己修改页码，也可能导致慢；此时对url种的页码进行加密（rest framework ）。
                
            答案四：
                当数据库很大，但是如果只是想获取一条数据
                select * form User where name="alex" limit 1

 

 

 

 

概念

- 概念：
            - 触发器
            - 函数
                - 聚合:max/sum/min/avg
                - 时间格式化 date_format
                - 字符串拼接 concat
            - 存储过程
            以上内容都是保存在数据库中。

 

- 看-看：

            - 如何开启慢日志查询？ 是用于查询那个sql语句慢
　　　　　　　　开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。   >>点我
                slow_query_log = ON                            是否开启慢日志记录
                long_query_time = 2                              时间限制，超过此时间，则记录
                slow_query_log_file = /usr/slow.log        日志文件
                log_queries_not_using_indexes = ON     为使用索引的搜索是否记录
                
            - 执行计划
　　　　　　　　　　
                explain select * from tb;
                
                
            - 导入和导出

- 导入和导出
导出现有数据库数据：

mysqldump -u用户名 -p密码 数据库名称 >导出文件路径           # 结构+数据
mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径       # 结构 
导入现有数据库数据：

mysqldump -uroot -p密码  数据库名称 < 文件路径