黑马程序员技术交流社区

标题: 【广州校区】+【原创】+数据库索引及优化 [打印本页]

作者: wujianhui 时间: 2019-4-4 14:37
标题: 【广州校区】+【原创】+数据库索引及优化
1.数据库索引引入
在项目开发中，我们经常使用到mysql数据库，而且往往数据库存储的数据非常多，少则几万，多则上亿那么为我们在使用查询语句查询其中一条数据的时候，一般使用的是下面的语句：

[SQL] 纯文本查看 复制代码

SELECT * FROM 表名 WHERE 字段名=字段值

   使用这个语句时会去扫描全表，也就是这张表有多少数据就会扫描多少次，直到找到这条数据。这个过程在表存储的数据量不是很多的时候是没有问题的，但是在数据量一大的情况下，就会很浪费性能。而在一般的应用中，读写的比例在10:1左右，也就是说在生产环境中，我们遇到最多的，最容易出现问题的是一些复杂的查询操作，因此对查询语句的优化就比较重要了，而索引就是其中一种优化手段。
2.索引介绍
      在mysql数据库中，索引是一种特殊的文件，它们包含着对数据表里面所有记录的引用指针。其实数据库索引有点类似于我们在上学时使用到的新华字典，以前当我们遇到一个不认识的字，我们会去查字典。如果不用偏旁部首去查，一个个找，那么花费的时间是很多的。而使用偏旁部首去找就能很快找出来。索引就是这样的工具，能加快查询的效率，可以极大的提升查询的性能。所以，索引的本质就是通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
3.索引的数据结构
      磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。
      根据索引的目的，我们可以知道，索引的数据结构的目的应该是每次查找数据时就把磁盘io次数控制在一个很小的数量级，最好就是常数数量级，而b+树就这样产生了。

如上图，是一颗b+树，每一个青色方块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项5、28和65，包含指针P1、P2、P3，P1表示小于28大于5的磁盘块，P2表示在28到65之间的磁盘块，P3表示大于等于65的磁盘块。b+树通常有两个指针，一个指向根结点，另一个指向关键字最小的叶子结点。因些，对于b+树进行查找两种运算：一种是从最小关键字起顺序查找，另一种是从根结点开始，进行随机查找。
如图所示，如果要查找数据项38，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定38在28和65之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，38在35和56之间，锁定磁盘块3的P2指针，通过指针加载磁盘块9到内存，发生第三次IO，同时内存中做二分查找找到38，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
4.创建案例使用的表及添加数据
在mysql中创建表news

[SQL] 纯文本查看 复制代码

CREATE TABLE `news` (

  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '新闻id',

  `title` char(255) NOT NULL COMMENT '新闻标题',

  `content` text COMMENT '新闻内容',

  `createtime` date DEFAULT NULL COMMENT '新闻创建时间',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1000004 DEFAULT CHARSET=utf8 COMMENT='新闻表';



SET FOREIGN_KEY_CHECKS = 1;

然后使用Java代码插入100万条数据

[Java] 纯文本查看 复制代码

@Test

public void test21(){

        String sql = "insert into news(title,content,createtime) values(?,?,?)";

        for (int i = 1; i < 1000000; i++) {

            int count = template.update(sql, "news time"+i,"According to Xinhua News Agency"+i,new Date());



        }

        System.out.println("执行成功");

}

5.mysql索引分类
5.1单列索引
5.1.1普通索引
这个索引是最基本的索引，没有任何限制，其sql格式有两种：

[SQL] 纯文本查看 复制代码

CREATE INDEX 索引名 ON `表名`(`字段名`);

ALTER TABLE 表名 ADD INDEX 索引名(`字段名`)

那么我们先在未创建索引查询一条数据，看下所耗的时间

[SQL] 纯文本查看 复制代码

SELECT * FROM news WHERE title='news time555455';

现在我们来创建索引

[SQL] 纯文本查看 复制代码

ALTER TABLE news ADD INDEX title_index(title);

之后我们再来使用同样一条sql语句来进行查询，我们发现查询速度提升的不是一点点

5.1.2唯一索引
与上面的普通索引相比，它是有限制的，那就是索引列的值必须唯一，但允许有空值（与主键不同）。如果是组合索引，则列值的组合必须是唯一，创建唯一索引的方式与普通索引是类似的：

[SQL] 纯文本查看 复制代码

CREATE UNIQUE INDEX 索引名 ON `表名`(`字段名`);

ALTER TABLE 表名 ADD UNIQUE INDEX 索引名(`字段名`)

我们可以先删除之前的索引，再添加新的索引

[SQL] 纯文本查看 复制代码

DROP INDEX 索引名 ON 表名;

-- 删除索引

DROP INDEX title_index ON news;

-- 添加唯一索引

CREATE UNIQUE INDEX title_index ON `news`(`title`);

同样使用之前的sql语句查询一条新闻，我们可以看到时间也是比不用查询快很多。

5.1.3主键索引
主键索引建立的规则是 int优于varchar,一般在建表的时候创建,最好是与表的其他字段不相关的列或者是业务不相关的列.一般会设为 int 而且是 AUTO_INCREMENT自增类型的，主键索引不允许有空值。
5.2组合索引
5.2.1组合索引实例
一个表中会含有多个列，所以可以创建多个单列索引，但是一个表中含有多个单列索引不代表是组合索引，组合索引包含多个列，但是只有一个索引名称，格式是：

[SQL] 纯文本查看 复制代码

 CREATE INDEX 组合索引名 ON `表名`(`字段1`, `字段2`, `字段3`···);

先删除之前的索引，然后使用一个多条件查询语句来记录没有组合索引时的查询时间

[SQL] 纯文本查看 复制代码

DROP INDEX title_index ON news;

CREATE INDEX title_createtime_index ON `news`(`title`, `createtime`);

使用组合索引以后，进行查询，发现极大的增加了查询的速度。

5.2.2组合索引查询的“最左前缀”
如果建立了组合索引title_createtime_index，那么实际上是包含了2个索引(title)(title,createtime)，在使用查询的时候会遵循mysql组合索引的“最左前缀”原则，最左前缀，简单的理解就是只从最左面的开始组合，并不是只要包含这两列的查询都会用到该组合索引，例如下面的例子：

[SQL] 纯文本查看 复制代码

-- 会使用到上面的组合索引

SELECT * FROM news WHERE title='news time555455';

SELECT * FROM news WHERE title='news time555455' and createtime ='2019-4-4';

-- 不会使用到索引

SELECT * FROM news WHERE createtime ='2019-4-4';

也就是说当where后面跟的条件查询不符合最左前缀组合，那么就不会使用到组合索引。
5.3全文索引
文本字段上(text)如果建立的是普通索引,那么只有对文本的字段内容前面的字符进行索引,其字符大小根据索引建立索引时申明的大小来规定.如果文本中出现多个一样的字符,而且需要查找的话,那么其条件只能是 where column like '%xxx%' 这样做会让索引失效，这个时候全文索引就可以起作用了。
建立全文索引的两种格式和创建全文索引：

[SQL] 纯文本查看 复制代码

-- 修改表结构添加全文索引

        ALTER TABLE 表名 ADD FULLTEXT 索引名(字段名);

-- 直接创建索引

        CREATE FULLTEXT INDEX 索引名 ON 表名(字段名);

-- 创建新闻表的全文索引    

    ALTER TABLE news  ADD FULLTEXT index_content(content);

不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。
6.应用索引的优缺点
6.1优点
      (1)可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.
      (2)建立索引可以大大提高检索的数据,以及减少表的检索行数
      (3)在表连接的连接条件可以加速表与表直接的相连
      {4}在分组和排序字句进行数据检索,可以减少查询时间中分组和排序时所消耗的时间(数据库的记录会重新排序)
      (5)建立索引,在查询中使用索引可以提高性能
6.2缺点
      (1)在创建索引和维护索引会耗费时间,随着数据量的增加而增加
      (2)索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
      (3)当对表的数据进行 INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。

7.索引的优化
   因为本身使用索引是为了增加查询的效率，提高性能，但是不当的使用索引反而会增加数据库的负担，所以我们在使用索引时，可以对索引进行优化来尽可能的避免出现索引失效的情况。
      （1）尽量使用全值匹配；
      （2）遵循最左前缀法则；
      （3）不要在索引列上做任何操作，如计算、函数、类型转换等，这些会导致索引失效，进而导致全表扫描；
      （4）尽量使用覆盖索引，即只访问索引的查询（索引列与查询列一致），减少select *；
      （5）mysql在使用!=，<和>等符号时是无法使用索引的，这样也会造成全表扫描；
      （6）is null ，is not null 也无法使用索引，会造成索引失效；
      （7）like以通配符开头（'%xxx···'）同样会造成索引失效；
      （8）少用or，使用or连接时索引也会失效；
      （9）order by 子句尽量使用index方式进行排序，避免使用filesort方式排序。

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2