MySQL索引和主键

索引的两种

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

索引的类型

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

普通索引

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

直接创建索引

CREATE INDEX index_name ON table(column(length))

创建主键

alter table tax_source add primary key (id);

删除主键

alter table tax_source drop primary key;

修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

创建表的时候同时创建索引

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 in ， not 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

发表评论

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