第五章索引

布满荆棘的人生 2022-01-26 05:55 372阅读 0赞

## 1 定义 ##

索引是一个单独的，存储上的数据结构，他包含着对数据表里所有记录在磁盘的引用指针

## 2 作用 ##

快速找出在某个或者多个列中有一特定值的行，提高查询操作的速度。

## 3 索引的优缺点 ##

优点：
    　　1、所有的MySql列类型(字段类型)都可以被索引，也就是可以给任意字段设置索引
        2、大大加快数据的查询速度
    缺点：
         1、创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加
    　　　2、索引也需要占空间，我们知道数据表中的数据也会有最大上线设置的，如果我们有大量的索引，索引文件可能会比数据文件更快达到上线值
    　　　3、当对表中的数据进行增加、删除、修改时，索引也需要动态的维护，降低了数据的维护速度。

## 4 索引的实际使用 ##

什么时候要使用索引？
 主键自动建立唯一索引；
 经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
 作为排序的列要建立索引；
 查询中与其他表关联的字段，外键关系建立索引
 高并发条件下倾向组合索引；
 
 什么时候不要使用索引？
 经常增删改的列不要建立索引；
 有大量重复的列不建立索引；
 表记录太少不要建立索引；
 *在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的；
 *在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了；
 *LIKE操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引；
 *在索引的列上使用表达式或者函数会使索引失效，例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。
 *在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
 *在查询条件中使用<>会导致索引失效。
 *在查询条件中使用IS NULL会导致索引失效。
 *在查询条件中使用OR连接多个条件会导致索引失效，这时应该改为两次查询，然后用UNION ALL连接起来。
 *尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引；
 *只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快--不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

## 5 索引的操作 ##

show tables;
    
    drop table test;
    
    
    /*建表的时候创建索引  
      普通索引 适合任意数据类型 允许插入空值和重复值
    
    */  
    create table test(
      t_id int not null auto_increment,
      t_name varchar(20) not null,
      index (t_name),
      primary key(t_id) 
    );
    
    show create table test;
    
    -- 创建唯一索引  
    create table test2(
      t_id int not null,
      unique index(t_id)
    );
    show create table test2;
    
    
    -- 创建单列索引
    
    create table test3(
      t_id int not null,
      t_name varchar(20) not null,
      index SingleIdx(t_name)
    );
    show create table test3;
    
    -- 创建多列索引
    create table test4(
      t_id int not null,
      t_name varchar(20) not null,
      t_address varchar(20) not null,
      index (t_name,t_address)
    );
    
    show create table test4;
    
    --  创建全文索引  
    /*
      1 全文索引必须在数据引擎为myisam 在才可以
      2 这里牵涉到存储引擎 
      3 了解存储引擎的特点
    */
    create table test5(
      t_id int not null,
      t_name varchar(22),
      fulltext index (t_name)
    )engine=myisam;
    
    -- 空间索引  字段必须为not null 存储引擎必须是myisam
    create table test6(
       t_name geometry not null,
       spatial  index (t_name)
    )engine=myisam;
    show create table test7;
    
    
    create table test7 (
      t_id int not null,
      t_name varchar(22) not null
    );
    
    
    /*####在已有表中创建索引####*/
    -- 1 alter table
    alter table test7 add index (t_name);
    
    --  查看表中的索引
    show index from test7;
    
    -- 2 用create index 
    create unique index t_id_index on test7(t_id);
    
    show create table test7;
    
    
    /*####删除索引####*/
    -- 1 alter table 来删除
    alter table test7 drop index t_name;
    
    --  2 用drop index 来删除
    drop index t_name on test7;

## 6 存储引擎介绍 ##

简介

MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能

常见 MyISAM、InnoDB 、Memory、CSV等9种**重点掌握****InnoDB和MyISAM****的区别**

<table style="margin-left:4.9pt;width:377pt;"> 
 <tbody> 
 <tr> 
 <td style="width:129pt;"> 名称 </td> 
 <td style="width:107pt;"> InnoDB </td> 
 <td style="width:141pt;"> MyISAM </td> 
 </tr> 
 <tr> 
 <td style="width:129pt;"> 事务处理 </td> 
 <td style="width:107pt;"> 支持 </td> 
 <td style="width:141pt;"> 不支持 </td> 
 </tr> 
 <tr> 
 <td style="width:129pt;"> 数据行锁定 </td> 
 <td style="width:107pt;"> 支持 </td> 
 <td style="width:141pt;"> 不支持 </td> 
 </tr> 
 <tr> 
 <td style="width:129pt;"> 外键约束 </td> 
 <td style="width:107pt;"> 支持 </td> 
 <td style="width:141pt;"> 不支持 </td> 
 </tr> 
 <tr> 
 <td style="width:129pt;"> 全文索引 </td> 
 <td style="width:107pt;"> 不支持 </td> 
 <td style="width:141pt;"> 支持 </td> 
 </tr> 
 <tr> 
 <td style="width:129pt;"> 表空间大小 </td> 
 <td style="width:107pt;"> 较大,约2倍 </td> 
 <td style="width:141pt;"> 较小 </td> 
 </tr> 
 </tbody> 
</table>

适用场合

使用MyISAM:   不需事务，空间小，以查询访问为主

使用InnoDB:     多删除、更新操作，安全性高，事务处理及并发控制

操作

-- 查看存储引擎
    show engines;
    -- 查看当前表的存储引擎
    show create table test5;
    -- 修改存储引擎
    alter table test4 engine = myisam;