数据库优化之索引技术的使用和理解

最新推荐文章于 2022-01-28 17:26:38 发布

一直在路上java实习生

最新推荐文章于 2022-01-28 17:26:38 发布

阅读量170

点赞数

分类专栏： Java 数据库优化文章标签： Mysql 索引数据库优化

本文链接：https://blog.csdn.net/safadfasd/article/details/102176241

版权

Java 同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

数据库优化

1 篇文章 0 订阅

订阅专栏

本文探讨了数据库优化中的索引优化策略，通过实例展示了如何利用慢日志定位性能瓶颈，并解释了MySQL中InnoDB引擎的BTree索引原理，以及索引对查询速度的影响。同时，对比了BTree与Hash索引的差异，强调了合理使用索引对于提升查询效率的重要性。

摘要由CSDN通过智能技术生成

优化是一个在我们日常开发中经常要做的事情,如果你的项目并发量很大,但是没有更多的服务器可以承担,那么我们首先会想到在业务层面使用多线程的方法将两个调用数据库的操作放在两个线程中,让他们并发执行,这样能缩短一半的时间代码如下

 ExecutorService pools= Executors.newFixedThreadPool(3);
        Callable<UserDao> aa = new Callable<UserDao>() {

            @Override
            public UserDao call() throws Exception {
                UserDao userDao =  userDaoMapper.selectByTelephone(telephone);
                return userDao;
            }
        };
        Future<UserDao> tassk1=pools.submit(aa);

        UserDao userDao = null;
        try {
            userDao = tassk1.get();
        } catch (InterruptedException e) {
            e.printStackTrace();
        } catch (ExecutionException e) {
            e.printStackTrace();
        }

        if(userDao == null){
           throw new BusinessException(EmBusinessError.LOGIN_FAIL);
       }
        int id = userDao.getId();
        Callable<UserPasswordDao> bb = new Callable<UserPasswordDao>() {

            @Override
            public UserPasswordDao call() throws Exception {
                UserPasswordDao userPasswordDao = userPasswordDaoMapper.selectByPrimaryKey(id);
                return userPasswordDao;
            }
        };
        Future<UserPasswordDao> tassk2=pools.submit(bb);
        FutureTask
        UserPasswordDao userPasswordDao = null;
        try {
            userPasswordDao = tassk2.get();
        } catch (InterruptedException e) {
            e.printStackTrace();
        } catch (ExecutionException e) {
            e.printStackTrace();
        }

但是这样做了之后速度还是达不到要求怎么办呢.这是我们就要对我们数据库进行优化,数据库优化大体分为两种,一是索引优化,二是分库分表,这里我们将如何进行索引优化
首先我们打开数据库可视化界面,我们可以通过查询慢日志的方式来查看哪些查询语句需要进行优化

SHOW VARIABLES LIKE "%quer%"

这句话可以查看你是否开启了慢日志功能
在这里插入图片描述
打开慢日志可以在数据库文件里的my.ini文件中添加代码如下

开启慢日志功能后,你就可以在你指定的文件路径下查看超出你规定用时的sql语句.

通过这样的sql语句可以查看该语句是否使用了索引且可以通过type类型知道性能的高低,性能高低的排序一般为:system>const>eq_ref>ref>ref_or_null;
这里可以看到我们性能是比较高的,因为我们是通过主键进行查询的,数据库会默认把主键设置为聚集索引,所以速度快,然后我们再看
在这里插入图片描述
这里通过普通字段title进行查询,但是我们发现他的type是all,这是排在第十位的,所以性能很差,速度慢,现在我们对他插入一个普通索引看看,我们通过以下代码插入索引

ALTER TABLE content_for_all ADD INDEX wt(title)

在这里插入图片描述
发现他的type变成了ref是第4位的,性能有了明显的提高.
那为什么我们的索引会对其sql语句的速度有这么大的影响呢,我们就需要看他的底层实现了
MySql数据库有两种存储引擎分别是MYISAM和InnoDB,一般我们用的比较多的是InnoDB,因为它可以将存储优化为独立表空间结构,好处是可以很方便的完成跨数据库甚至跨硬件的数据读写
那在我们的InnoDB引擎中,我们的索引底层实现一般都是BTree索引他是Balance Tree,具体实现如图
在这里插入图片描述
他是通过中序遍历,将数据有序存放在树中,这样他的时间复杂度为log(n)
如果有2^31条数据,相当于21亿条,正常不加索引需要遍历21亿次,而当我们使用索引是只需要进行31次就可以完成,这不是大大的加快了速度吗
当然除了BTree索引
还有一种叫做hash索引,理论上他的时间复杂度为1,他是将那一列的数据生成对应的hash值,查找时通过对应的hash值就可以直接找到对应的列,但是为什么说是理论上呢,因为他可能会生成相同的hash值,以链表的形式存放,那么就需要在这些相同的值中再进行遍历,他的底层和hashMap十分类似,但是hashMap在jdk1.8以后已经在链表中数大于8时,将其转化为红黑树,这样设计效率可以更高.但是在InnoDB中是没有hsah索引的,我们只能使用一种伪hash索引

正常情况下会有如下查询：
select id,url from table_name where url = "https://www.baidu.com"；
若删除原来 URL 列上的索引，而新增一个被索引的 url_crc 列，使用 CRC32 作为哈希，就可以使用如下的方式进行查询：
select id,url from table_name where url_crc = CRC32("https://www.baidu.com") and url = "https://www.baidu.com"；

这样做的性能会非常高，因为 MySQL 优化器会使用这个选择性很高而体积很小的基于 url_crc 列的索引来完成查找。即使有多个记录有相同的索引值，查找依然很快，只需要根据哈希值做快速的整形比较就能找到索引条目，然后一一比较返回对应的行。另一种方式就是对完整的 URL 字符串做索引，那样会非常慢。