MySQL索引的一些见解

索引的两种

索引的类型:索引分为聚簇索引和非聚簇索引两种,聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快。

索引的类型

创建索引时字段的length是指索引的长度,可以不指定,length只对字符串类型的字段有效。

普通索引

是最基本的索引,它没有任何限制。它有以下几种创建方式:

  1. 直接创建索引
    CREATE INDEX index_name ON table(column(length))
    
  2. 修改表结构的方式添加索引
    ALTER TABLE table_name ADD INDEX index_name ON (column(length))
    
  3. 创建表的时候同时创建索引
    CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `title` char(255) CHARACTER NOT NULL ,
        `content` text CHARACTER NULL ,
        `time` int(10) NULL DEFAULT NULL ,
        PRIMARY KEY (`id`),
        INDEX index_name (title(length))
    );
    

唯一索引(unique index)

CREATE UNIQUE INDEX indexName ON table(column(length))

主键索引(primary key)

是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引:

CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `title` char(255) NOT NULL ,
        PRIMARY KEY (`id`)
);

组合索引

组合索引遵循最左原则

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

全文索引

主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用,而不是一般的where语句加like。它可以在create table,alter table ,create index使用,不过目前只有char、varchar,text 列上可以创建全文索引。值得一提的是,在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。

  1. 创建表的时候添加全文索引
    CREATE TABLE `table` (
        `id` int(11) NOT NULL AUTO_INCREMENT ,
        `title` char(255) CHARACTER NOT NULL ,
        `content` text CHARACTER NULL ,
        `time` int(10) NULL DEFAULT NULL ,
        PRIMARY KEY (`id`),
        FULLTEXT (content)
    );
    
  2. 修改表结构添加全文索引
    ALTER TABLE article ADD FULLTEXT index_content(content)
    
  3. 直接创建索引
    CREATE FULLTEXT INDEX index_content ON article(content)
    

索引相关sql语句

删除索引

DROP INDEX index_name ON table

什么情况不会用到索引

  • 一条查询语句只会使用一个索引
  • 当列含有null值,则不会使用索引
  • like "xxx%" 会使用索引,但是 “%xxx%” 则不会使用索引
  • 列在被使用计算、函数的时不会使用索引
  • not innot exists , (<> 不等于 !=) 这些不会使用索引
  • < 小于 > 大于 <= >= 这个根据实际查询数据来判断,如果全盘扫描速度比索引速度要快则不走索引
  • 索引的最左前缀原则,如果有(a,b)索引,查询where a会使用索引,但是where b则不会使用索引
  • 索引的最左前缀原则2:如果有(a,b,c,d)索引, a = 1 and b = 2 and c > 3 and d = 4 将在匹配c时不使用索引,但是如果索引是(a, b, d, c)则会全部使用索引,因为:mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配
  • where or 语句将不适用索引,如果or的字段一样,可以改为 where in 语句, 如 SELECT * FROM T1 WHERE a=3 OR a=5 可以改为 SELECT * FROM T1 WHERE a in (3, 5), 也可以使用union代替or: (SELECT * FROM T1 WHERE a=5) UNION (SELECT * FROM T1 WHERE b=5)

索引的优点

  • 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
  • 可以大大加快数据的检索速度,这也是创建索引的最主要的原因
  • 在需要连接的字段上设置索引,可以加快连接查询
  • 可以加快group by 和排序order by的速度

索引的缺点

  • 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加
  • 索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间
  • 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度

什么时候应该创建索引

  • 在经常需要作为查询条件的列上,可以加快搜索的速度
  • 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度
  • 在经常需要根据范围进行搜索、或者进行排序、或者分组的列上建索引,因为索引已经排序,其指定的范围是连续的

什么时候不应该创建索引

  • 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求
  • 对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度
  • 对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少
  • 当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少 索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引

优化索引

  • 使用较短的length
  • 即时删除不再使用的索引

相关文章

https://blog.yeskn.com/archives/1137.html

发表评论

电子邮件地址不会被公开。 必填项已用*标注