阅读 54

MYSQL:索引

1/为什么要建立索引

索引是查询优化最主要的方式;
索引优化是数据库优化的最重要手段。

查询方式:
   一种是:全表扫描;# 但是如果表很大,则这种方式的速度就很慢了
   一种是:利用数据表上建立的索引index进行扫描查询。
  
复制代码

2/为什么建立索引会加快查询的速度

数据库的索引可以加快查询速度,
原因是索引使用特定的数据结构(B-Tree)对特定的列(这里特定的列就是设置了索引的列)额外组织存放,
加快存储引擎(索引是存储引擎实现)查找记录的速度。
复制代码

3/什么是聚簇索引??

就是索引和数据放在一起。
当你查询语句使用索引(通常是where条件匹配索引),就会利用树(B-TREE)的结构加快查找,
索引会按值查找到要查找的行在表中位置,不需要再回到数据库表查询数据的就是聚簇索引。

通常来说,我们还是需要回到数据库表再查数据,这样需要消耗额外的磁盘IO。
所以有些时候(如按顺序读取数据)全表扫描会比使用索引快的原因就在于此。

查询条件只有一个字段时,在该字段建立索引即可,可优化的地方是对于text blob字段使用前缀索引。

当查询条件有多个字段时,单列索引和多列索引有很大的区别。
如果使用多列索引,where条件中字段的顺序非常重要,需要满足最左前缀列。

什么是最左前缀?
    查询条件中的所有字段需要从左边起按顺序出现在多列索引中,
    查询条件的字段数要小于等于多列索引的字段数,
    中间字段不能存在范围查询的字段(<,like等),
    这样的sql可以使用该多列索引。
复制代码

4/Mysql索引根据用途分为:

1.普通索引:列值可以取空值或重复值。创建使用关键字INDEX或KEY;
2.唯一值索引:列值不能重复;即索引列值必须是唯一的,但可以是空值;创建使用关键字UNIQUE;
3.主键索引:主键索引是系统自动创建的主键索引,并且是唯一的。与唯一索引区别是;列值不能为空;
4.聚簇索引:就是数据存储的物理存储顺序,非聚簇索引就是索引顺序与数据的物理顺序无关。一个表只能有一个聚簇索引。目前只有InoDB和solidDB支持。
5.全文索引:只能创建在varchar或text的列上;建立全文索引能够在全文索引的列上进行查找。
复制代码

5/创建索引的3种方式

<1>方式1:
create index index_fieldName on tableName(fieldName);

<2>方式2:建表时指定索引
create table basic_df(
             tid int,
             tname varchar(20),
             gender varchar(1),
             index [indexName] (tid)
         );

<3>方式3:
alter table tableName add unique index indexName (fieldName);
复制代码

4/什么是单列索引和多列索引

1)单列索引:
  就是一个索引只包含表中的一个列;
  比创建一个学号ID的索引;
  以name再创建一个姓名的单列索引。
  即每个索引包含一个列。
  
2)组合索引(复合索引或多列索引):
  就是表中的两个列或多个列来创建成一个索引;
  比如;以用户ID、用户名Name、用户年龄Age来创建的索引就是联合索引。
  
  
复制代码

5/关于索引的常用命令

   drop table if exists my_test;  --如果my_test表存在则删除
   create table my_test(
        id int(11) not null unique auto_increment,  
        name varchar(10) not null default 0,  
        age int(11) not null default 0 ) engine=innodb charset=utf8;
  
   -- 查看表my_test的全部索引
   show index from my_test;
 
   -- 创建索引
   -- 为表my_text中的列name创建普通索引名称index_n
   create index index_name on my_test(name); 
   -- 为age做降序索引
   create index index_age on my_test(age DESC); 
   -- 建立列age和name的联合索引,按照age升序后再按照name降序排序
   create index index_age_name on my_test(age,name DESC);
   
   --删除索引
   --删除表my_test中的index_name索引
   drop index index_name on my_test;
  
   --修改索引,包括删除索引和添加新的索引
   alter table my_test drop index index_age;
   alter table my_test add index index_age(age);
复制代码

6/通过实例理解单例索引、多列索引以及最左前缀原则

实例:现在我们想查出满足以下条件的用户uid:
mysql> select uid from basic_df where lname='Liu and fname = 'Zhiqun' and age=26 ;
因为我们不想扫描整表,故考虑用索引。

1、单列索引:
   ALTER TABLE basic_df add index index_lname (lname);
   index_lname是单列索引的名字
   将lname列建索引,这样就把范围限制在lname='Liu'的结果集1上,
   之后扫描结果集1,产生满足fname='Zhiqun'的结果集2,再扫描结果集2,找到 age=26的结果集3,即最终结果。

  由于建立了lname列的单列索引,与执行数据库表的完全扫描相比,效率提高了很多,
  但我们扫描的记录数量仍旧远远超过了实际所需要的。
  虽然我们可以删除lname列上的索引,再创建fname或者age列的索引,但是,不论在哪个列上创建索引搜索效率仍旧相似。

2、多列索引:
 ALTER TABLE basic_df add index index_lname_fname_age (lame,fname,age);
 index_lname_fname_age 是组合索引的名字。
 为了提高搜索效率,我们需要考虑运用多列索引,
 由于索引文件以B-Tree格式保存,所以我们不用扫描任何记录,即可得到最终结果。

 注:在mysql中执行查询时,只能使用一个索引,如果我们在lname,fname,age上分别创建索引,
 执行查询时,只能使用一个索引,mysql会选择一个最严格(获得结果集记录数最少)的索引。

 3.最左前缀:
   顾名思义,就是最左优先,上例中我们创建了index_lname_fname_age多列索引,
   相当于创建了(lname)单列索引,(lname,fname)组合索引以及(lname,fname,age)组合索引。

   注:在创建多列索引时,要根据业务需求,在where子句中进行筛选的时候使用最频繁的一列放在最左边。
复制代码
文章分类
代码人生
文章标签