数据库相关问题

最新推荐文章于 2024-07-15 20:55:00 发布

jcsyl_mshot

最新推荐文章于 2024-07-15 20:55:00 发布

阅读量416

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/jcsyl_mshot/article/details/80221855

版权

数据库专栏收录该内容

22 篇文章 0 订阅

订阅专栏

1. 如果在数据库上进行了误操作该怎么处理

1.利用数据备份。
2.利用 binlog 文件。

2．数据库在进行水平分表之后， sql 分页查询该怎么进行？分表之后想让一个 id 多个表是自增的，效率实现。数据库中的分页查询语句怎么写？

1.用 union all 合并几个分表的结果集，之后进行分页查询。

union all 在合并多个结果集时候，将所有的列都列印出来
UNION 命令只会选取不同的值。

SELECT * FROM((SELECT id,money,createtime,status,error AS
     form_account,payment_no AS type_name FROM pre_pay_log
     WHERE 1 AND memb_id =51225) 
     UNION ALL (SELECT id,money,createtime,status,form_account,type_name 
     FROM pre_cash_log WHERE 1 AND memb_id =51225)) as a 
     ORDER BY a.id DESC LIMIT 0,20

2.如假定共 3 个分表，记录数分别为 90， 120， 80 ，总记录数为 290
设分页是每页显示 40 条，则
第 1 页表一的 1 到 40
第 2 页表一的 41 到 80
第 3 页表一的 81 到 90 + 表二的 1 到 30
第 4 页表二的 31 到 70
第 5 页表二的 71 到 110
第 6 页表二的 111 到 120 + 表三的 1 到 30
…..
3.用 sphinx（斯芬克司）先建索引，然后分页查询。

当我们对 MySQL 进行分表操作后，将不能依赖 MySQL 的自动增量来产生唯一 ID 了，因为数据已经分散到多个表中。

3．关系型数据库和非关系型数据库的区别

非关系型数据库的优势：
1. 性能
NOSQL 是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过 SQL 层的解析，所以性能非常高。

2.可扩展性
同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

关系型数据库的优势：
1.复杂查询
可以用 SQL 语句方便的在一个表以及多个表之间做非常复杂的数据查询。

2.事务支持
使得对于安全性能很高的数据访问要求得以实现

4.数据库索引失效的几种情况

1.对于组合索引，不是使用的第一部分，则不会使用索引。

2.or 语句前后没有同时使用索引。要想使用 or，又想让索引生效，只能将 or 条件中的每个列都加上索引。

3.如果列类型是字符串，那一定要在条件中使用引号引起来，否则不会使用索引。

4.如果 mysql 估计使用全表描述比使用索引快，则不使用索引。

5.在索引列上做运算或者使用函数。

6.以“%”开头的 LIKE 查询， 模糊匹配。

5.一个表只有一列 name，有重复的 name, 求出前十个 name数最大的 name

select distinct name,count(name) a from user
group by name
order by desc
limit 10
**LIMIT 5,10; //检索记录行 6-15**

6.数据库表里有 100 万条数据，想要删除 80 万条数据，但是因为锁的原因，删除很慢，现在想要快速删除怎么办？

如果需要保留的数据不多，需要删除的数据很多，那么可以考虑把需要保留的数据复制到临时表，然后删除所有数据，最后复制回去。

具体做法是：先把要保留的数据用 insert into … select * from … where …移到另外的表中， truncate table 旧表，然后再 insert into … select * from … ，这个不存在锁，比 delete效率高。

七.创建数据库连接池
① 编写 class 实现 DataSource 接口
② 在 class 的构造器一次性创建 10 个连接，将连接保存 LinkedList 中
③ 实现 getConnection，从 LinkedList 中返回一个连接
④ 提供将连接放回连接池中的方法
1、连接池代码

public class myDataSource implements DataSourse{
    LinkedList<Connection> datasources = new LinkedList();
    public myDataSource(){
        for(int i=0;i<10;i++){
            try{
                //1、 装载 sqlserver 驱动对象
                DriverManager.registerDriver(new SQLServerDriver)
                //2、 通过 JDBC 建立数据库连接
                Connection con  = DriverManager.getConnection(
                "jdbc.sqlserver://192.168.2.6:1433;DatabaseName=customer","sa","124");
                dataSources.add(con);
                }catch (Eeception e){ e.prinStackTrace();}
            }
    }
    public Connection getConnection() throws SQLException{
        final Connection conn = dataSources.removeFirst();
        return conn;
    }
    public void releaseConnection(Connection conn){
        dataSouces.add(conn);
    }
}

2、使用连接池重构我们的用户查询函数

public void FindAllUser(){
    MyDataSource dataSource = new MyDataSource();
    Connection conn = dataSource.getConnection();
    //创建状态
    Statement state = con.createStatement();
    //查询返回结果
    ResultSet result = state.executeQuery("select * from users");
    //输出查询结果
    while（rest.next()){
        System.out.println(result.getString());
    }
    result.close();
    state.close();
}

连接池的工作原理：
连接池的核心思想是连接的复用，通过建立一个数据库连接池以及一套连接使用、分配和管理策略，使得该连接池中的连接可以得到高效，安全的复用，避免了数据库连接频繁建立和关闭的开销.

连接池的工作原理主要由三部分组成，分别为连接池的建立，连接池中连接的使用管理，连接池的关闭。

第一、连接池的建立。一般在系统初始化时，连接池会根据系统配置建立，并在池中建立几个连接对象，以便使用时能从连接池中获取。 java 中提供了很多容器类，可以方便的构建连接池，例如 Vector（线程安全类）， linkedlist 等

第二、连接池的管理。连接池管理策略是连接池机制的核心，连接池内连接的分配和释放对系统的性能有很大的影响。其策略是：
当客户请求数据库连接时，首先查看连接池中是否有空闲连接，如果存在空闲连接，则将连接分配给客户使用并作相应处理（即标记该连接为正在使用，引用计数加 1）；如果没有空闲连接，则查看当前所开的连接数是否已经达到最大连接数，如果没有达到最大连接数，如果没达到就重新创建一个连接给请求的客户；如果达到，就按设定的最大等待时间进行等待，如果超出最大等待时间，则抛出异常给客户。

当客户释放数据库连接时，先判断该连接的引用次数是否超过了规定值，如果超过了就从连接池中删除该连接，并判断当前连接池内总的连接数是否小于最小连接数，若小于就将连接池充满；如果没超过就将该连接标记为开放状态，可供再次复用。

第三、连接池的关闭。当应用程序退出时，关闭连接池中所有的链接，释放连接池相关资源，该过程正好与创建相反.

7.优化查询的方法

1.使用索引
应尽量避免全表扫描，首先应考虑在 where 及 orderby,group by 涉及的列上建立索引。

2.优化SQL语句
2.1任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
2.2不在索引列做运算或者使用函数。
2.3 查询尽可能使用 limit 减少返回的行数，减少数据传输时间和带宽浪费

3.优化数据库对象
3.1 优化表的数据类型使用 procedure analyse()函数对表进行分析，该函数可以对表中列的数据类型提出优化建议。能小就用小。表数据类型第一个原则是：使用能正确的表示和存储数据的最短类型。这样可以减少对磁盘空间、内存、cpu 缓存的使用。使用方法：select * from 表procedureanalyse();

3.2 对表进行拆分通过拆分表可以提高表的访问效率。有 2 种拆分方法：
1.垂直拆分
把主键和一些列放在一个表中，然后把主键和另外的列放在另一个表中。如果一个表中某些列常用，而另外一些不常用，则可以采用垂直拆分。
2.水平拆分
根据一列或者多列数据的值把数据行放到二个独立的表中。

3.3 使用中间表来提高查询速度
创建中间表，表结构和源表结构完全相同，转移要统计的数据到中间表，然后在中间表上进行统计，得出想要的结果。

4.应用优化
4.1 使用数据库连接池
4.2 使用查询缓存它的作用是存储 select 查询的文本及其相应结果。如果随后收到一个相同的查询，服务器会从查询缓存中直接得到查询结果。查询缓存适用的对象是更新不频繁的表，当表中数据更改后，查询缓存中的相关条目就会被清空.

8.如果有一个特别大的访问量到数据库上，怎么做优化？

1.使用优化查询的方法（见上面）

2.主从复制，读写分离，负载均衡
目前，大部分的主流关系型数据库都提供了主从复制的功能，通过配置两台（或多台）数据库的主从关系，可以将一台数据库服务器的数据更新同步到另一台服务器上。网站可以利用数据库的这一功能，实现数据库的读写分离，从而改善数据库的负载压力。一个系统的读操作远远多于写操作，因此写操作发向 master，读操作发向 slaves 进行操作（简单的轮循算法来决定使用哪个slave）。
利用数据库的读写分离，Web 服务器在写数据的时候，访问主数据库（Master），主数据库通过主从复制机制将数据更新同步到从数据库（Slave），这样当 Web服务器读数据的时候，就可以通过从数据库获得数据。这一方案使得在大量读操作的Web 应用可以轻松地读取数据，而主数据库也只会承受少量的写入操作，还可以实现数据热备份，可谓是一举两得的方案。

主从复制的原理
1. 影响 MySQL-A 数据库的操作，在数据库执行后，都会写入本地的日志系统 A中.
2. 实时的将变化了的日志系统中的数据库事件操作，通过网络发给MYSQL-B。MYSQL-B收到后，写入本地日志系统B，然后一条条的将数据库事件在数据库中完成
3. 那么，MYSQL-A 的变化，MYSQL-B也会变化，这样就是所谓的 MYSQL的复制。在上面的模型中，MYSQL-A就是主服务器，即master，MYSQL-B就是从服务器，即slave。

3.数据库分表，分区，分库
分表见上面描述。
分区就是把一张表的数据分成多个区块，这些区块可以在一个磁盘上，也可以在不同的磁盘上，分区后，表面上还是一张表，但数据散列在多个位置，这样一来，多块硬盘同时处理不同的请求，从而提高磁盘I/O读写性能，实现比较简单。包括水平分区和垂直分区。
分库是根据业务不同把相关的表切分到不同的数据库中，比如web、bbs、blog等库。

9.sql 注入的问题

一．sql 语句应该考虑哪些安全性？
1.防止 sql 注入，对特殊字符进行过滤、转义或者使用预编译的 sql 语句绑定变量。
2.当 sql 语句运行出错时，不要把数据库返回的错误信息全部显示给用户，以防止泄漏服务器和数据库相关信息。

二．什么叫做 SQL 注入，如何防止？请举例说明。
①举个例子：你后台写的 java 代码拼的 sql 如下：

// 该 ename 为前台传过来的一个查询条件 
public List getInfo(String ename){ 
    StringBuffer buf = new StringBuffer();
    buf.append("select empno,ename,deptno from emp where ename = '").append(ename).append("'");
    ...
    }

而前台页面有个输入框如下：职员姓名：____
该文本域对应上面方法的 ename 参数。如果用户在查询时向职员姓名文本域中输入的是如下信息： ’ or ‘1’=’1 那么这时就会涉及到 sql 注入这个概念了。上面的字符串传到后台后，与其它 select 等字符串拼成了如下的语句： select empno,ename,deptno from emp where ename = ” or ‘1’=’1’ 上面的 where 条件是永远成立的，如果你的表中有权限限制，比如只能查询本地市的信息，过滤条件中有地市过滤，不过因为输入 ‘or’1’=’1 字符串后，条件永远成立，导致你能看到所有城市的职员信息，那就会产生权限问题了，用户能看到不该看到的信息。同理，如果是 insert 或者 update 等语句的话，通过 sql 注入会产生不可估量的问题.这种不安全的情况是在 SQL 语句在拼接的情况下发生。

② 解决方法：
1.参数绑定。为了防范这样”SQL 注入安全“可以用预编译解决（不要用拼接 SQL 字符串,可以用 prepareStatement,参数用 set 方法进行填装）。【PreparedStatement是java.sql包下面的一个接口，用来执行SQL语句查询，通过调用connection.preparedStatement(sql)方法可以获得PreparedStatment对象。数据库系统会对sql语句进行预编译处理（如果JDBC驱动支持的话），预处理语句将被预先编译好，这条预编译的sql查询语句能在将来的查询中重用，这样一来，它比Statement对象生成的查询速度更快。】

2、检查变量的数据类型和格式
如果你的 SQL 语句是类似 whereid={$id}这种形式，数据库里所有的 id 都是数字，那么就应该在 SQL 被执行前，检查确保变量 id 是 int 类型；如果是接受邮箱，那就应该检查并严格确保变量一定是邮箱的格式，其他的类型比如日期、时间等也是一个道理。总结起来：只要是有固定格式的变量，在 SQL 语句执行前，应该严格按照固定格式去检查，确保变量是我们预想的格式，这样很大程度上可以避免 SQL 注入攻击。

3.所有的 SQL 语句都封装在存储过程中。所有的 SQL 语句都封装在存储过程中，这样不但可以避免 SQL 注入，还能提高一些性能。