初始Mysql的索引

时间:2022-03-16 04:47:48

mysql的索引优化

近段时间在学习mysql,这篇博客就从以下三个方面谈谈对mysql的索引的理解。如有不足,还请留言交流。

初始Mysql的索引

一 为什么要用索引

一项技术的产生一定是为了解决某个问题,或者是迎合某项业务需求的。所以对于技术,我们就应该本着求真,严谨,热爱的态度来学习使用它。因为每项技术都凝结着无数人的智慧,在推动着社会的发展。

那么mysql的数据库中为什么要使用索引,以下面这个场景为例,假设数据库中有10万条图书数据,要根据书籍的作者查询一本书。以此来比较不使用索引和使用索引的查询速度。

  1. 创建数据库表

        create table tb_book(id int,book_name varchar(64),author varchar(64));
  2. 模拟插入10万条数据:

    /*插入数据的方法*/
    public  void insertBook(Book book){
     connection = DaoUtils.getConnection();
     final String INSERT_BOOK = "insert into tb_book(id,book_name,author) VALUES(?,?,?)";
     try {
         preparedStatement = connection.prepareStatement(INSERT_BOOK);
         preparedStatement.setInt(1,book.getId());
         preparedStatement.setString(2,book.getBookName());
         preparedStatement.setString(3,book.getAuthorName());
         preparedStatement.executeUpdate();
     } catch (SQLException e) {
         e.printStackTrace();
     }finally {
         DaoUtils.close(preparedStatement,connection);
     }
     }
    
    
    /*插入数据的测试*/
     @Test
     public void testInsert(){
     BookDao bookDao = new BookDao();
     long startTime =  System.currentTimeMillis();
     int i = 0;
     for( i = 1;i<100000;i  ){
         Book book = new Book();
         book.setId(i);
         book.setBookName("mysql技术内幕" i);
         book.setAuthorName("Paul" i);
         bookDao.insertBook(book);
     }
     long endTime = System.currentTimeMillis();
     long time = endTime-startTime;
     System.out.println("插入" i "条数据用的时间为:" time);
     }
      注: 以这样的方式插入数据比较耗时,建议使用sql脚本批量查询,也就是将自动提交事物改为手动提交。
  3. 查询指定的书名:

  • 在不使用索引的条件下查询作者名

            /**
       *按照作者名查询
       * @param author
      * @return
        */
     public List<Book> selectByBookAuthor(String author){
      final String  QUERYByBook = "select * from tb_book where author='" author "' " ;
      List<Book> bookList = new ArrayList<>();
      try {
          connection = DaoUtils.getConnection();
          preparedStatement = connection.prepareStatement(QUERYByBook);
          resultSet=preparedStatement.executeQuery();
          Book book = null;
          while(resultSet.next()){
              book = new Book();
              book.setId(resultSet.getInt("id"));
              book.setBookName(resultSet.getString("book_name"));
              book.setAuthorName(resultSet.getString("author"));
              bookList.add(book);
          }
          return  bookList;
      } catch (SQLException e) {
          e.printStackTrace();
      }finally {
          DaoUtils.close(resultSet,preparedStatement,connection);
         }
      return null;
        }
    
       /*查询的测试*/ 
        @Test
        public void testBookByName(){
        BookDao bookDao = new BookDao();
        long startTime = System.currentTimeMillis();
        List<Book> list = bookDao.selectByBookAuthor("Paul69002");
        long endTime = System.currentTimeMillis();
        System.out.println("未使用索引的时间:" (endTime-startTime));
        System.out.println(list.size());
        }

    初始Mysql的索引
    耗时995秒

  • 加上索引,查询书名
    改变表结构,增加索引,并指定索引的长度 。

       alter table tb_book add index index_author(author(16)); # 增加索引字段
    
    
    
       /*使用索引的查询测试*/
       @Test
      public void testBookByName(){
      BookDao bookDao = new BookDao();
      long startTime = System.currentTimeMillis();
      List<Book> list = bookDao.selectByBookAuthor("Paul69002");
      long endTime = System.currentTimeMillis();
      System.out.println("使用索引的时间:" (endTime-startTime));
      }

    初始Mysql的索引
    耗时927秒

  • 可见使用索引可以提升查询速度。

索引的工作原理

对于单表:假设下面的一张表。要想查询某本书,就是要检查表的每一行,看看它是否和期望值匹配。如果表很大,而且仅有几行于搜索条件相匹配,那么整个表的查询效率将会很低。

初始Mysql的索引

如果在上述表的基础上增加索引,查询速度就会快很多,比如我们找出图书编号为13的书籍,开始扫描表,然后我们会找到相对应的行,便会找到1个值,然后我们会到书籍编号为14的值,该值比我们查询的值要大一些,由于索引是有序的,所以当我们读取到那条包含14的索引行时,我们便知道再也没有和13行匹配的数据,这时就会退出查询。

对于单表 索引的优势就是不在执行全表扫描,极大的提升查询的速度。

对于多表查询,索引就更有必要了,假设有三个索引表t1,t2,t3,每个表都有一列,且每一行都有1000行,这些行中包含的数字1~1000的数字,如果想要在这些表的所有行中。将其相等的行找出来 可以使用下列语句进行查询

select t1.i1,t2.i2,t3.i3 from t1 inner join t2 inner join t3 where t1.i1 = t2.i2 and t2.i2= t3.i3

此查询结果应该有1000行,其中每一行有三个相等的值,如果不使用索引进新查询,根本不知道那些行包含那些值,于是我们必须尝试所有的组合,以找到与where相匹配的行。可能的组合有1000* 1000 *1000(10亿) 这比匹配数目多了100万倍。如果表的数据继续增大,查询的次数更多。这个时候就需要建立索引了,具体建立的索引的原理如下:

  1. 从表t1中选择一行,看该行包含什么样的值
  2. 利用表t2的索引,直接与表t1的值进行匹配,类似的利用表3的行直接与t1表的行相匹配
  3. 继续选择t1的下一行,重复前面的过程,直到持续到表t1所有的行被检查完为止。

这样查询的结果就是只对t1进行全表扫描,对表t2和t3进行索引查找.

通过以上的示例,就可以看到使用索引的好处:

  1. 加快查询速度
  2. 改进查询性能,也就是提升查询速度。

    二 mysql中怎么添加索引

增加索引:

  1. 使用alter关键字添加索引

     alter table tb_name add index index_name(index_columns)  
     alter table tb_name add unique index_name(index_columns)
     alter table tb_name add primary key (index_columns)   #主键索引,不允许为null,
     alter table tb_name add fulltext index_name(index_columns)
     alter table tb_name add spatial index_name(index_columns)

    注: 其中tb_name是添加表的名称,index_name是索引的名称,可以自定义,index_columns 是添加索引的列。

限制某个索引: 就使用上述的primary key和unique ,两者的不同在于有以下两点:

  • unique允许有多个空值,而primary key 不允许有空值
  • 一个表中只能包含一个primary key ,而可以有多个unique索引。

2.除primary key外 ,使用create index 添加索引

      crate index index_name on tb_name (index_columns);
      create unique index_name on tb_name(index_columns);
      create fulltext index_name on tb_name(index_columns);
      create spatial index_name on tb_name(index_columns);

3.创建表时添加索引

    create table tb_name(
    ..列定义..
    index index_name(index_columns)
    unique index_name(index_columns)
    primary key (index_columns)
    fulltext index_name(index_columns)
    spatial index_name(index_columns)
    );

删除索引:

删除索引的方法是 drop index 和alter table语句 ,具体语法如下:

  1. 使用drop语句

    drop index index_name on tb_name;
    drop  index 'primary' on tb_name;
  2. 使用alter来删除索引

    alter table tb_name drop index_name(index_columns);
     alter table tb_name drop primary key;

三 怎样用好索引

索引的代价:

  1. 索引提高了查询速度的同时,降低了插入,删除更新值的速度。也就是说索引降低了大部分与写入相关的操作。原因就在于写入一个行不仅是写入一个数据行,还要更改索引,表的索引越多,就更改的越多,平均性能就下降的越快。
  2. 索引也会占用磁盘空间。增加io读写的负担。

怎么选择索引:

  1. 为用于搜索,排序或者分组的列创建索引,而对于用作输出显示的列则不用创建索引,也就是说,最佳的候选列是那些出现在where字句中的列,连接子句中的列,或者出现在order by 或者 gruop by子句中的列,那些只出现在select关键字后面输出列表里的列,则不是很好的索引候选列。

  2. 认真考虑列的基数,也就是指他所容纳所有非重复值得个数,重复值越少,使用索引优化的性能越高。例如对于记录性别的列,就每必要列出他们的索引,列的重复个数太多,基数太小。

  3. 索引短小值,尽量选择较小的数据类型。短小值可以让比的操作更快,对于更短小的值,键缓存的索引块可以容纳更多的键值。
    特别是InnoDB他使用的是聚簇索引,所以主键尽量短小会带来更多的好处。

  4. 索引字符串值的前缀。想要对字符串进行索引,应尽可能的指定字符串的前缀长度。具体的语法为:

      create table tb_book(
       id  int not null,
       book_name varchar(64),
      author varchar(64),
       index (author(10))  # 指定具有特定前缀索引的长度。
       )
  5. 利用最左前缀 当创建包含n个列的复和索引时,实际上会创建n个专供Mysql使用的索引。复和索引相当于多个索引,因为索引中最左边的任意数据列集合都可用于匹配各个行,这样的集合称为“最左前缀”
    .下面用一个例子进行说明:

假设有一个表,他拥有一个或者多个列的复和索引,这些列的名字分别为: country,state和city。在索引里,行的排列顺序为country/state/city,因此,行首先会自动按照country/state顺序排序,然后再按country进行排序。这意味着,即使再查询时只指定了country的值,或者只指定了country值和state的值,MYSQL也可以充分的利用索引。因此,索引可用于搜索下列几种组合

   country,state , city
   country ,state
    country

注意:对于没有包含最左边前缀的那些搜索,如按照state或city来搜索,mysql无法使用索引。

6.不要建立过多索引,正所谓物极必反,索引亦是如此,前面提到过建立过多的索引会影响数据的写操作,而且还会占用额外的数据空间。

7.让参与的索引比较类型保持匹配。也就是再选择索引类型时,请考虑在索引列上执行的是什么类型的比较操作。不同的存储引擎使用的数据结构不同。

  • 对于散列索引适用于精确查找,所以你要想精确查找某个数据的话就将表的存储引擎改为MyISAM,因为MyIsAM的默认索引类型为散列索引。
  • 对于范围查找使用B树索引效果会更好,将表的结构改为InnoDB的存储引擎会更好,这是因为InnoDB的默认存储引擎为B树索引。

8.利用慢查询日志找出那些性能低劣的查询。也就是用处不大的索引。这里不做过多说明。详见《Mysql技术内幕》

参考书籍: 《MYSQL技术内幕》