阿里一面

表查询 order a升序 b降序会导致什么问题 a,b走索引

Mysql: order by后的各项如果排序不一致会导致联合索引失效，譬如

order by a ASC, b DESC, c DESC

a升序，b降序，排序不一致，索引（a,b,c）失效

但是建索引的时候也可以指定排序

如果建立一个索引(a ASC, b DESC, c DESC)

那 order by a ASC, b DESC, c DESC 语句索引还失效吗？

8.0之后允许索引降序，抛开 sql 优化等细节，只要 order by 顺序和索引顺序一致，那么还是可以用到索引排序的。

(8.0)CREATE INDEX Syntax

(5.7)CREATE INDEX Syntax

设计一个积分榜排名的功能

具体到一个实际例子，比如说直播网站观众向主播送礼物的排行版，如果直接在数据库里面进行排序，弊端有以下几点：

• 排行榜会实时更新，数据每一次变化都要排序，会对数据库的性能造成影响。. 频繁更新数据，导致数据库性能下降。

• 数据量太大时排序时间缓慢

• 对被排序字段添加索引会占用更多空间

Redis 有序集合和集合一样也是string类型元素的集合,且不允许重复的成员。

不同的是每个元素都会关联一个double类型的分数。redis正是通过分数来为集合中的成员进行从小到大的排序。

有序集合的成员是唯一的,但分数(score)却可以重复

这里使用有序集合几个的key是用户的id 分数是 rank的排行榜值最后取前多少条就OK了

redis 127.0.0.1:6379> ZADD w3ckey 1 redis
(integer) 1
redis 127.0.0.1:6379> ZADD w3ckey 2 mongodb
(integer) 1
redis 127.0.0.1:6379> ZADD w3ckey 3 mysql
(integer) 1
redis 127.0.0.1:6379> ZADD w3ckey 3 mysql
(integer) 0
redis 127.0.0.1:6379> ZADD w3ckey 4 mysql
(integer) 0
redis 127.0.0.1:6379> ZRANGE w3ckey 0 10 WITHSCORES
 
1) "redis"
2) "1"
3) "mongodb"
4) "2"
5) "mysql"
6) "4"

总分总模式任务,统计子任务执行是否成功

多线程如果想完成总分总模式，并统计执行结果可以使用线程的Future

首先子线程需要 implements Callable<T>

 static class SunT implements Callable<ResultVO>{

        private String name ;

        public SunT(String name) {
            this.name = name;
        }

        @Override
        public ResultVO call() throws Exception {
            System.out.println(name+"子线程执行开始");
            Thread.sleep(1000);
            System.out.println(name+"子线程执行结束");
            return new ResultVO(name,true);
        }
    }

定义的返回结果

static class ResultVO{
        private String name;

        private boolean resultFlag ;

        public ResultVO(String name, boolean resultFlag) {
            this.name = name;
            this.resultFlag = resultFlag;
        }

        public String getName() {
            return name;
        }

        public boolean isResultFlag() {
            return resultFlag;
        }
    }

主任务代码：

 public static void main(String[] args) {
        System.out.println("主线程开始执行");
        List< Future<ResultVO>> list = new ArrayList<>(10);
        //创建线程池 这里没有手动创建ThreadPoolExecutor了，newCachedThreadPool的最大线程数量会导致OOM
        ExecutorService es = Executors.newCachedThreadPool();
        for(int i=0;i<10;i++){
            String tName = "第"+i+"线程";
            //将子线程加入线程池并将返回结果Future<ResultVO>加入list结果集
            Future<ResultVO> f = es.submit(new SunT(tName));
            list.add(f);
        }
        //从返回list结果集里面获取每一个返回的状态 如果想控制超时时间可以使用
        // future.get(5,TimeUnit.SECONDS);
        for ( Future<ResultVO> future:list ){
            try {
                ResultVO resultVO = future.get();
                System.out.println(resultVO.getName()+"运行结果是"+resultVO.isResultFlag());
            } catch (InterruptedException e) {
                e.printStackTrace();
                System.out.println("运行结果是错误");
            } catch (ExecutionException e) {
                e.printStackTrace();
                System.out.println("运行结果是错误");
            }
        }
        System.out.println("主线程执行结束");
        //结束线程池
        es.shutdown();
    }

这里有一点需要注意的

future.get(); 是一个阻塞方法

for(int i=0;i<10;i++){
           String tName = "第"+i+"线程";
           //将子线程加入线程池并将返回结果Future<ResultVO>加入list结果集
           Future<ResultVO> f = es.submit(new SunT(tName));
           ResultVO resultVO = future.get();
}

如上的代码块会导致第一个SunT提交任务之后会阻塞一致等待任务执行结束之后才能创建第二个任务线程池也就变成异步执行了,和单线程效果一样。

Spring事务如何实现全成功和全失败

首先我们看一下想要实现事务操作代码是什么

 Connection conn = null;
        try {
                //获取连接
            conn=(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=FALSE&serverTimezone=UTC","root","xb199795");
            //设置事务自动提交为false
            conn.setAutoCommit(false);
            //业务操作 xxx
            //业务操作 bbb
            //业务操作成功后提交事务
            conn.commit();
        } catch (SQLException e) {
            System.out.println("************事务处理出现异常***********");
            e.printStackTrace();
            try {
                    //业务操作出现sql异常 回滚事务
                conn.rollback();
                System.out.println("*********事务回滚成功***********");
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }finally {
                try {
                    conn.close();
                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
            }
        }

接下来我们看spring是如何操作代码的

ConnectionHandler

public class ConnectionHandler {
        //将数据库连接保存在map中， 以DataSource为键
    private Map<DataSource, Connection> map = new HashMap<>();
    
    public Connection getConnectionByDatabase(DataSource dataSource) throws SQLException{
        Connection conn = map.get(dataSource);
        if(conn == null) {
            conn = dataSource.getConnection();
            map.put(dataSource, conn);
        }
        return conn;
    }
    
    public void openConnection(DataSource dataSource) throws SQLException {
        Connection conn = map.get(dataSource);
        if(conn.isClosed()) {
            conn = dataSource.getConnection();
            map.put(dataSource, conn);
        }
    }
}

我们都知道，springMVC是可以配置多个数据源的，所以这里我们直接以数据源作为map的键。连接作为值保存起来，每次获取连接时，如果map中不存在连接，则重新获取一个连接。这样就保证了在程序运行周期内，同一个数据源获取到的连接都为同一个。也就完成了我们最重要的第一步，每次进行数据库操作时，使用的都是同一个连接。

如果是在单线程模式下，其实这个类已经足够了的。spring可没那么简单，它是一个多线程的。也就是说在并发的情况下，map是线程不安全的。所以这里我们还得再封装一下。

SingleConnectHandler

public class SingleConnectHandler {
    private static ThreadLocal<ConnectionHandler> localThread = new ThreadLocal<>();

    private static ConnectionHandler getConnectionHahdler() {
        ConnectionHandler ch = localThread.get();
        if(ch == null) {
            ch = new ConnectionHandler();
            localThread.set(ch);
        }
        return ch;
    }

    public static Connection getConnection(DataSource dataSource) throws SQLException {
        return getConnectionHahdler().getConnectionByDatabase(dataSource);
    }

    public static void openConnection(DataSource dataSource) throws SQLException {
        getConnectionHahdler().openConnection(dataSource);
    }
}

这个类我们需要一个ThreadLocal类来帮我们保证在多线程下。每个线程能拿到自己变量副本ConnectionHandler。也就是说并发下其实每个线程其实获取到的连接都是不一样的。

ConnectionHandler + SingleConnectHandler保证了在多线程环境下可以从dataSource中获取一条连接且不受其他线程干扰。

这样我们的项目就可以支持多线程下操作了，完了之后是TransactionManager.java这个类。spring中这个类的功能可强大了。不过我们这个项目毕竟是阉割版的，我就只写了几个常规的方法。如开启事务，关闭事务，提交事务与回滚事务。

public class TransactionManager {
    private static DataSource dataSource;

    public TransactionManager(DataSource dataSource) {
        TransactionManager.dataSource = dataSource;
    }

    private Connection getConnection() throws SQLException {
        return SingleConnectHandler.getConnection(dataSource);
    }

    public void openConnection() throws SQLException {
        SingleConnectHandler.openConnection(dataSource);
    }
    //从SingleConnectHandler获取一个线程安全的连接 开启事务的时候设置自动提交为false
    public void start() throws SQLException {
        Connection conn = getConnection();
        conn.setAutoCommit(false);
    }
    public void close() throws SQLException {
        Connection conn = getConnection();
        conn.close();
    }
    public void commit() throws SQLException {
        Connection conn = getConnection();
        conn.commit();
    }
    public void rollBack() throws SQLException {
        Connection conn = getConnection();
        conn.rollback();
    }
    public boolean isAutoCommit() throws SQLException {
        return getConnection().isClosed();
    }
}

注意start方法被修改了设置连接的自动提交为false 这样就就只能手动提交事务。

接下来业务类就很简单正常的使用PreparedStatement提供的接口即可

UserService

public interface UserService {
    public void buy() throws SQLException;
    public void addShops() throws SQLException;

}

UserServiceImpl

public class UserServiceImpl implements UserService {

    private String sql = "insert into user(account,password) values(?,?)";
    private DataSource dataSource;

    public UserServiceImpl(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public void buy() throws SQLException {
        Connection conn = SingleConnectHandler.getConnection(dataSource);
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, Thread.currentThread().getName()+"buy");
        ps.setString(2, new Date().toString());
        ps.execute();
        System.out.println("方法buy，---当前线程："+Thread.currentThread()+"-------使用的Connection:"+conn.hashCode());
    }

    @Override
    public void addShops() throws SQLException {
        Connection conn = SingleConnectHandler.getConnection(dataSource);
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, Thread.currentThread().getName()+"addShops");
        ps.setString(2, new Date().toString());
        ps.execute();
        System.out.println("方法addShops，当前线程："+Thread.currentThread()+"----使用的Connection:"+conn.hashCode());
    }
}

MyDatasource

public class MyDatasource implements DataSource {

    public static final String driverClassName = "com.mysql.jdbc.Driver";
    public static final String password = "root";
    public static final String username = "root";
    public static final String url = "jdbc:mysql://localhost:3306/qq";

    @Override
    public Connection getConnection() throws SQLException {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }
}

重写了getConnection方法返回DriverManager获取的连接

接下来执行的示例代码

public static void main(String[] args) throws SQLException, InterruptedException {
        DataSource b = new MyDatasource();
        UserService u = new UserServiceImpl(b);
        TransactionManager t = new TransactionManager(b);
        try {
            /**
             * 这里开启事务的时候其实相当于执行了  Connection conn = getConnection();
             * conn.setAutoCommit(false); 直接设置自动提交为false
             */
            t.start();
            //业务执行buy
            u.buy();
            //业务执行addShops
            u.addShops();
            /**
             * 从ConnectionHandler 获取当前线程的事务之后提交
             * Connection conn = getConnection();  conn.commit();
             */
            t.commit();
            //同上 关闭当前线程的事务
            t.close();
        } catch (Exception e) {
            try {
                //出现任何异常的时候获取当前线程的连接并回滚
                t.rollBack();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }
    }

总结：

spring 整体上事务保证使用aop切入示例代码部分将业务执行部分剥离开使用TransactionManager去开启事务提交事务关闭事务回滚事务。ConnectionHandler和ConnectionHandler可以保证在线程安全的情况下从dataSource中获取到一个数据库连接。

mysql数据库达到瓶颈的情况下,如何扩展

要正确的优化SQL，我们需要快速定位能性的瓶颈点，也就是说快速找到我们SQL主要的开销在哪里？而大多数情况性能最慢的设备会是瓶颈点，如下载时网络速度可能会是瓶颈点，本地复制文件时硬盘可能会是瓶颈点，为什么这些一般的工作我们能快速确认瓶颈点呢，因为我们对这些慢速设备的性能数据有一些基本的认识，如网络带宽是2Mbps，硬盘是每分钟7200转等等。因此，为了快速找到SQL的性能瓶颈点，我们也需要了解我们计算机系统的硬件基本性能指标，下图展示的当前主流计算机性能指标数据。

从图上可以看到基本上每种设备都有两个指标：

延时（响应时间）：表示硬件的突发处理能力；
带宽（吞吐量）：代表硬件持续处理能力。

从上图可以看出，计算机系统硬件性能从高到代依次为：

CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘

根据数据库知识，我们可以列出每种硬件主要的工作内容：

CPU及内存：缓存数据访问、比较、排序、事务检测、SQL解析、函数或逻辑运算；

网络：结果数据传输、SQL请求、远程数据库访问（dblink）；

硬盘：数据访问、数据写入、日志记录、大数据量排序、大表连接。

根据当前计算机硬件的基本性能指标及其在数据库中主要操作内容，可以整理出如下图所示的性能基本优化法则：

这个优化法则归纳为5个层次：

减少数据访问（减少磁盘访问）
返回更少数据（减少网络传输或磁盘访问）
减少交互次数（减少网络传输）
减少服务器CPU开销（减少CPU及内存开销）
利用更多资源（增加资源）

由于每一层优化法则都是解决其对应硬件的性能问题，所以带来的性能提升比例也不一样。传统数据库系统设计是也是尽可能对低速设备提供优化方法，因此针对低速设备问题的可优化手段也更多，优化成本也更低。我们任何一个SQL的性能优化都应该按这个规则由上到下来诊断问题并提出解决方案，而不应该首先想到的是增加资源解决问题。

以下是每个优化法则层级对应优化效果及成本经验参考：

优化法则	性能提升效果	优化成本
减少数据访问	1~1000	低
返回更少数据	1~100	低
减少交互次数	1~20	低
减少服务器CPU开销	1~5	低
利用更多资源	@~10	高

接下来，我们针对5种优化法则列举常用的优化手段并结合实例分析。

减少数据访问

创建并使用正确的索引
只通过索引访问数据

有些时候，我们只是访问表中的几个字段，并且字段内容较少，我们可以为这几个字段单独建立一个组合索引，这样就可以直接只通过访问索引就能得到数据，一般索引占用的磁盘空间比表小很多，所以这种方式可以大大减少磁盘IO开销。

优化SQL执行计划

返回更少的数据

数据分页处理
只返回需要的字段

通过去除不必要的返回字段可以提高性能，例：
调整前：select * from product where company_id=?;
调整后：select id,name from product where company_id=?;

减少交互次数

batch DML

数据库访问框架一般都提供了批量提交的接口，jdbc支持batch的提交处理方法，当你一次性要往一个表中插入1000万条数据时，如果采用普通的executeUpdate处理，那么和服务器交互次数为1000万次，按每秒钟可以向数据库服务器提交10000次估算，要完成所有工作需要1000秒。如果采用批量提交模式，1000条提交一次，那么和服务器交互次数为1万次，交互次数大大减少。采用batch操作一般不会减少很多数据库服务器的物理IO，但是会大大减少客户端与服务端的交互次数，从而减少了多次发起的网络延时开销，同时也会降低数据库的CPU开销。

In List

很多时候我们需要按一些ID查询数据库记录，我们可以采用一个ID一个请求发给数据库，如下所示：
for :var in ids[] do begin
  select * from mytable where id=:var;
end;
我们也可以做一个小的优化， 如下所示，用ID INLIST的这种方式写SQL：
select * from mytable where id in(:id1,id2,...,idn);

使用存储过程

普通业务逻辑尽量不要使用存储过程，定时性的ETL任务或报表统计函数可以根据团队资源情况采用存储过程处理。

优化业务逻辑

减少数据库服务器CPU运算

使用绑定变量

绑定变量是指SQL中对变化的值采用变量参数的形式提交，而不是在SQL中直接拼写对应的值。
非绑定变量写法：Select * from employee where id=1234567
绑定变量写法：
Select * from employee where id=?
Preparestatement.setInt(1,1234567)
Java中Preparestatement就是为处理绑定变量提供的对像，绑定变量有以下优点：
1、防止SQL注入
2、提高SQL可读性
3、提高SQL解析性能，不使用绑定变更我们一般称为硬解析，使用绑定变量我们称为软解析。

合理使用排序

Oracle的排序算法一直在优化，但是总体时间复杂度约等于nLog(n)。普通OLTP系统排序操作一般都是在内存里进行的，对于数据库来说是一种CPU的消耗，曾在PC机做过测试，单核普通CPU在1秒钟可以完成100万条记录的全内存排序操作，所以说由于现在CPU的性能增强，对于普通的几十条或上百条记录排序对系统的影响也不会很大。但是当你的记录集增加到上万条以上时，你需要注意是否一定要这么做了，大记录集排序不仅增加了CPU开销，而且可能会由于内存不足发生硬盘排序的现象，当发生硬盘排序时性能会急剧下降，这种需求需要与DBA沟通再决定，取决于你的需求和数据，所以只有你自己最清楚，而不要被别人说排序很慢就吓倒。

减少比较操作
大量复杂运算在客户端处理

什么是复杂运算，一般我认为是一秒钟CPU只能做10万次以内的运算。如含小数的对数及指数运算、三角函数、3DES及BASE64数据加密算法等等。

如果有大量这类函数运算，尽量放在客户端处理，一般CPU每秒中也只能处理1万-10万次这样的函数运算，放在数据库内不利于高并发处理。

利用更多的资源

客户端多进程并行访问

多进程并行访问是指在客户端创建多个进程(线程)，每个进程建立一个与数据库的连接，然后同时向数据库提交访问请求。当数据库主机资源有空闲时，我们可以采用客户端多进程并行访问的方法来提高性能。如果数据库主机已经很忙时，采用多进程并行访问性能不会提高，反而可能会更慢。所以使用这种方式最好与DBA或系统管理员进行沟通后再决定是否采用。

数据库并行处理

数据库并行处理是指客户端一条SQL的请求，数据库内部自动分解成多个进程并行处理，如下图所示：

海量URL筛选,不使用中间件

分治法

题目描述：

给定a、b两个文件，各存放50亿个url，每个url各占64B，内存限制是4GB，请找出a、b两个文件共同的url

分析：

由于每个url需要占64B，所以50亿个url占用空间大小为50亿×64=5GB×64=320GB.由于内存大小只有4GB，因此不可能一次性把所有的url加载到内存中处理。对于这种题目，一般采用分治法，即把一个文件中的url按照某一特征分成多个文件，使得每个文件的内容都小于4GB，这样就可以把这个文件一次性读入到内存中进行处理。

解答：

1、遍历文件a，对遍历带的url求hash(url)%500，根据计算结果把遍历到的url分别存放到a0,a1,a2,a3...,a499（计算结果为i的url存储到文件ai中），这样每个文件的大小大约为600MB。当某一个文件中的url的大小超过2GB时，可以按照类似的方法把这个文件继续分为更小的子文件（例如a1文件的大小超过2GB，则把文件继续分为a11，a12...)

2、使用同样的方法遍历文件b，把文件b的url分别存储到文件b0,b1,b2...b499中去。

3、通过之前的划分，与ai中的url相同的url一定在bi中。由于ai与bi中所有的url的大小不会超过4GB，因此可以把它们同时读入内存中进行处理。具体为：遍历文件ai，把遍历到的url存入hash_set中，接着遍历文件bi中的url，如果这个url在hash_set中存在，那么说明这个url是这两个文件共同的url，可以把这个url保存到另一个单独的文件中。当把文件a0~a499都遍历完成后，就找到了两个文件共同的url。

布隆过滤器

有关布隆过滤器的知识可以参考如下文章：

https://www.cnblogs.com/cpselvis/p/6265825.html

解答为：

创建一个布隆过滤器，开辟一个足够的位空间（二进制向量）；
设计一些种子数，用来产生一系列不同的映射函数（哈希函数）；
使用一系列的哈希函数对此URL中的每一个元素（字符）进行计算，产生一系列的随机数，也就是一系列的信息指纹；
将一系列的信息指纹在布隆过滤器中的相应位，置为1。

服务端限流的实现

常见的限流算法有：计数器、令牌桶、漏桶。

1、计数器算法

采用计数器实现限流有点简单粗暴，一般我们会限制一秒钟的能够通过的请求数，比如限流qps为100，算法的实现思路就是从第一个请求进来开始计时，在接下去的1s内，每来一个请求，就把计数加1，如果累加的数字达到了100，那么后续的请求就会被全部拒绝。等到1s结束后，把计数恢复成0，重新开始计数。

具体的实现可以是这样的：对于每次服务调用，可以通过 AtomicLong#incrementAndGet()方法来给计数器加1并返回最新值，通过这个最新值和阈值进行比较。

这种实现方式，相信大家都知道有一个弊端：如果我在单位时间1s内的前10ms，已经通过了100个请求，那后面的990ms，只能眼巴巴的把请求拒绝，我们把这种现象称为“突刺现象”

2、漏桶算法

为了消除"突刺现象"，可以采用漏桶算法实现限流，漏桶算法这个名字就很形象，算法内部有一个容器，类似生活用到的漏斗，当请求进来时，相当于水倒入漏斗，然后从下端小口慢慢匀速的流出。不管上面流量多大，下面流出的速度始终保持不变。

不管服务调用方多么不稳定，通过漏桶算法进行限流，每10毫秒处理一次请求。因为处理的速度是固定的，请求进来的速度是未知的，可能突然进来很多请求，没来得及处理的请求就先放在桶里，既然是个桶，肯定是有容量上限，如果桶满了，那么新进来的请求就丢弃。

在算法实现方面，可以准备一个队列，用来保存请求，另外通过一个线程池定期从队列中获取请求并执行，可以一次性获取多个并发执行。

这种算法，在使用过后也存在弊端：无法应对短时间的突发流量。

3、令牌桶算法

从某种意义上讲，令牌桶算法是对漏桶算法的一种改进，桶算法能够限制请求调用的速率，而令牌桶算法能够在限制调用的平均速率的同时还允许一定程度的突发调用。

在令牌桶算法中，存在一个桶，用来存放固定数量的令牌。算法中存在一种机制，以一定的速率往桶中放令牌。每次请求调用需要先获取令牌，只有拿到令牌，才有机会继续执行，否则选择选择等待可用的令牌、或者直接拒绝。

放令牌这个动作是持续不断的进行，如果桶中令牌数达到上限，就丢弃令牌，所以就存在这种情况，桶中一直有大量的可用令牌，这时进来的请求就可以直接拿到令牌执行，比如设置qps为100，那么限流器初始化完成一秒后，桶中就已经有100个令牌了，这时服务还没完全启动好，等启动完成对外提供服务时，该限流器可以抵挡瞬时的100个请求。所以，只有桶中没有令牌时，请求才会进行等待，最后相当于以一定的速率执行。

实现思路：可以准备一个队列，用来保存令牌，另外通过一个线程池定期生成令牌放到队列中，每来一个请求，就从队列中获取一个令牌，并继续执行。

posted @ 2020-01-20 10:03 reload 阅读(287) 评论(0) 收藏举报

刷新页面返回顶部