MYSQL索引及高性能索引策略

什么是索引

索引是一种特殊的数据库结构,由数据表中的一列或多列组合而成,可以用来快速查询数据表中有某一特定值的记录。

为什么要有索引

索引可以很大程度上提高数据库的查询速度。

在没有索引的时,我们根据id查询一条数据,数据库系统将进行全表扫描,逐行遍历,读取每条记录的所有信息进行匹配,直到找到符合的目标数据。这种情况下数据库的数据量和查询时间无疑是正向增加的,当数据库中数据量十分庞大时时,查询效率会十分低下。

而索引访问呢,则对表的关键数据列建立一个索引,查询数据时不用读完记录的所有信息,直接根据该列上的索引找到对应记录行的位置,就像我们翻一本书的目录一样。

索引的优点和缺点

优点

  • 索引大大加快数据的查询速度

  • 索引大大减小了服务器需要扫描的数据量

  • 索引可以帮助服务器避免排序和临时表

  • 索引可以将随机IO变成顺序IO

  • 索引对于InnoDB(对索引支持行级锁)非常重要,因为它可以让查询锁更少的元组。在MySQL5.1和更新的版本中,InnoDB可以在服务器端过滤掉行后就释放锁,但在早期的MySQL版本中,InnoDB直到事务提交时才会解锁。对不需要的元组的加锁,会增加锁的开销,降低并发性。 InnoDB仅对需要访问的元组加锁,而索引能够减少InnoDB访问的元组数。但是只有在存储引擎层过滤掉那些不需要的数据才能达到这种目的。一旦索引不允许InnoDB那样做(即索引达不到过滤的目的),MySQL服务器只能对InnoDB返回的数据进行WHERE操作,此时,已经无法避免对那些元组加锁了。如果查询不能使用索引,MySQL会进行全表扫描,并锁住每一个元组,不管是否真正需要。

  • 关于InnoDB、索引和锁:InnoDB在二级索引上使用共享锁(读锁),但访问主键索引需要排他锁(写锁)

缺点

  • 创建和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加。
  • 索引需要占磁盘空间,除了数据表占数据空间以外,每一个索引还要占一定的物理空间。

不要盲目的创建索引,只为查询操作频繁的列创建索引,创建索引会使查询操作变得更加快速,但是会降低增加、删除、更新操作的速度,因为执行这些操作的同时会对索引文件进行重新排序或更新。

大量数据导入时,可以先删除索引,再批量插入数据,最后再添加索引。

索引类型

存储方式区分

根据存储方式的不同,MySQL 中常用的索引在物理上分为 B-树索引和 HASH 索引两类,两种不同类型的索引各有其不同的适用范围。

1) B-树索引

B-树索引又称为 BTREE 索引 ,目前大部分的索引都是采用 B-树索引来存储的。

  • 叶子节点:包含的条目直接指向表里的数据行。叶子节点之间彼此相连,一个叶子节点有一个指向下一个叶子节点的指针。
  • 分支节点:包含的条目指向索引里其他的分支节点或者叶子节点。
  • 根节点:一个 B-树索引只有一个根节点,实际上就是位于树的最顶端的分支节点。

基于这种树形数据结构,表中的每一行都会在索引上有一个对应值。因此,在表中进行数据查询时,可以根据索引值一步一步定位到数据所在的行。

  • 查询必须从索引的最左边的列开始。
  • 查询不能跳过某一索引列,必须按照从左到右的顺序进行匹配。
  • 存储引擎不能使用索引中范围条件右边的列。

2) 哈希索引

哈希(Hash)一般翻译为“散列”,也有直接音译成“哈希”的,就是把任意长度的输入(又叫作预映射,pre-image)通过散列算法变换成固定长度的输出,该输出就是散列值。
哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中,MEMORY 存储引擎可以支持 B-树索引和 HASH 索引,且将 HASH 当成默认索引。
HASH 索引不是基于树形的数据结构查找数据,而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快,但也存在下面的一些缺点:

  • MySQL 需要读取表中索引列的值来参与散列计算,散列计算是一个比较耗时的操作。也就是说,相对于 B-树索引来说,建立哈希索引会耗费更多的时间。
  • 不能使用 HASH 索引排序。
  • HASH 索引只支持等值比较,如“=”“IN()”或“<=>”。
  • HASH 索引不支持键的部分匹配,因为在计算 HASH 值的时候是通过整个索引值来计算的。

逻辑区分

根据索引的具体用途,MySQL 中的索引在逻辑上分为以下 5 类:

1) 普通索引

普通索引是 MySQL 中最基本的索引类型,它没有任何限制,唯一任务就是加快系统对数据的访问速度。

普通索引允许在定义索引的列中插入重复值和空值。

创建普通索引时,通常使用的关键字是 INDEX 或 KEY。

例 1

下面在 tb_student 表中的 id 字段上建立名为 index_id 的索引。

CREATE INDEX index_id ON tb_student(id);

2) 唯一索引

唯一索引与普通索引类似,不同的是创建唯一性索引的目的不是为了提高访问速度,而是为了避免数据出现重复。

创建唯一索引通常使用 UNIQUE 关键字。
例 2

tb_student 表中的 id 字段上建立名为 index_id 的索引,SQL 语句如下:

CREATE UNIQUE INDEX index_id ON tb_student(id);

其中,id 字段可以有唯一性约束,也可以没有。

3) 主键索引

顾名思义,主键索引就是专门为主键字段创建的索引,也属于索引的一种。

主键索引是一种特殊的唯一索引,不允许值重复或者值为空。

创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

4) 空间索引

空间索引是对空间数据类型的字段建立的索引,使用 SPATIAL 关键字进行扩展。

创建空间索引的列必须将其声明为 NOT NULL,空间索引只能在存储引擎为 MyISAM 的表中创建。

空间索引主要用于地理空间数据类型GEOMETRY。对于初学者来说,这类索引很少会用到。
例 3
下面在 tb_student 表中的 line 字段上建立名为 index_line 的索引,SQL 语句如下:

CREATE SPATIAL INDEX index_line ON tb_student(line);

其中,tb_student 表的存储引擎必须是 MyISAM,line 字段必须为空间数据类型,而且是非空的。

5) 全文索引

全文索引主要用来查找文本中的关键字,只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。

全文索引允许在索引列中插入重复值和空值。

不过对于大容量的数据表,生成全文索引非常消耗时间和硬盘空间。

创建全文索引使用 FULLTEXT 关键字。
例 4
在 tb_student 表中的 info 字段上建立名为 index_info 的全文索引,SQL 语句如下:

CREATE FULLTEXT INDEX index_info ON tb_student(info);

其中,index_info 的存储引擎必须是 MyISAM,info 字段必须是 CHAR、VARCHAR 和 TEXT 等类型。

实际使用区分

索引在逻辑上分为以上 5 类,但在实际使用中,索引通常被创建成单列索引和组合索引。

1)单列索引

单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引,单列索引只根据该字段进行索引。

单列索引可以是普通索引,也可以是唯一性索引,还可以是全文索引。只要保证该索引只对应一个字段即可。
例 5
下面在 tb_student 表中的 address 字段上建立名为 index_addr 的单列索引,address 字段的数据类型为 VARCHAR(20),索引的数据类型为 CHAR(4)。SQL 语句如下:

CREATE INDEX index_addr ON tb_student(address(4));

这样,查询时可以只查询 address 字段的前 4 个字符,而不需要全部查询。

2)多列索引

组合索引也称为复合索引或多列索引。相对于单列索引来说,组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段,可以通过这几个字段进行查询。但是,只有查询条件中使用了这些字段中第一个字段时,索引才会被使用。

例如,在表中的 id、name 和 sex 字段上建立一个多列索引,那么,只有查询条件使用了 id 字段时,该索引才会被使用。
例 6
下面在 tb_student 表中的 name 和 address 字段上建立名为 index_na 的索引,SQL 语句如下:

CREATE INDEX index_na ON tb_student(name,address);

该索引创建好了以后,查询条件中必须有 name 字段才能使用索引。

提示:一个表可以有多个单列索引,但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引,以此来加快查询速度。比如,在一个表中创建了一个组合索引(c1,c2,c3),在实际查询中,系统用来实际加速的索引有三个:单个索引(c1)、双列索引(c1,c2)和多列索引(c1,c2,c3)。

本节转自MySQL索引类型详解

创建/删除索引

1)创建表时创建索引

CREATE TABLE 表名 (
字段名1  数据类型 [完整性约束条件…],
字段名2  数据类型 [完整性约束条件…],
[UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY    索引名  (字段名[(长度)]  [ASC |DESC]));

2)在已存在的表上创建索引 CREATE

CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名    ON 表名 (字段名[(长度)]  [ASC |DESC]) ;

3)在已存在的表上创建索引 ALTER

 ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX    索引名 (字段名[(长度)]  [ASC |DESC]) ;

4)删除索引

DROP INDEX 索引名 ON 表名字;

示例

1.创建索引
    -在创建表时就创建(需要注意的几点)
    create table s1(
    id int ,#可以在这加primary key
    #id int index #不可以这样加索引,因为index只是索引,没有约束一说,
    #不能像主键,还有唯一约束一样,在定义字段的时候加索引
    name char(20),
    age int,
    email varchar(30)
    #primary key(id) #也可以在这加
    index(id) #可以这样加
    );
    -在创建表后在创建
    create index name on s1(name); #添加普通索引
    create unique age on s1(age);添加唯一索引
    alter table s1 add primary key(id); #添加住建索引,也就是给id字段增加一个主键约束
    create index name on s1(id,name); #添加普通联合索引
2.删除索引
    drop index id on s1;
    drop index name on s1; #删除普通索引
    drop index age on s1; #删除唯一索引,就和普通索引一样,不用在index前加unique来删,直接就可以删了
    alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的,那么我们也用alter来删)

创建索引的技巧

  1. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),
    表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、
    性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,
    这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录
  2. 对 where,on,group by,order by 中出现的列使用索引。
  3. 对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键。
  4. 为较长的字符串使用前缀索引。
  5. 不要过多创建索引,除了增加额外的磁盘空间外,对于DML操作的速度影响很大,因为其每增删改一次就得从新建立索引。
  6. 使用组合索引,可以减少文件索引大小,在使用时速度要优于多个单列索引。
  7. 最左前缀匹配原则。mysql会一直向右匹配直到遇到范围查找就停滞匹配。比如:a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。索引的建立需要考虑常用查询的字段顺序。
  8. 索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’
    就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,
    但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。
    所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
  9. 尽可能去扩展索引,而不是新建索引。当需要通过增加索引来提高性能时,考虑结合现有索引判断是否可以在已有索引上做扩展。
  10. =和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器
    会帮你优化成索引可以识别的形式

索引无法命中的情况

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不会使用索引,因为所有索引列参与了计算 

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不会使用索引,因为使用了函数运算,原理与上面相同 

SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引 

SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引 

-- 正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因 

-- 字符串与数字比较不使用索引; 
CREATE TABLE `a` (`a` char(10)); 
EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引 
EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引 

select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引,我们建议大家尽量避免使用or 关键字 

-- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

-- 组合索引最左前缀
    如果组合索引为:(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

-- 排序条件为索引,则select字段必须也是索引字段,否则无法命中
- order by
    select name from s1 order by email desc;
    当根据索引排序时候,select查询的字段如果不是索引,则不走索引
    select email from s1 order by email desc;
    特别的:如果对主键排序,则还是走索引:
        select * from tb1 order by nid desc;

注意事项

  • 避免使用select *
  • count(1)或count(列) 代替 count(*)
  • 创建表时尽量时 char 代替 varchar
  • 表的字段顺序固定长度的字段优先
  • 组合索引代替多个单列索引(经常使用多个条件查询时)
  • 尽量使用短索引
  • 使用连接(JOIN)来代替子查询(Sub-Queries)
  • 连表时注意条件类型需一致
  • 索引散列值(重复少)不适合建索引,例:性别不适合

慢查询优化的基本步骤

  1. 先运行看看是否真的很慢,注意设置SQL_NO_CACHE
  2. where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高
  3. explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
  4. order by limit 形式的sql语句让排序的表优先查
  5. 了解业务方使用场景
  6. 加索引时参照建索引的几大原则
  7. 观察结果,不符合预期继续从1分析

Reference

MySQL索引原理以及查询优化

MySQL索引类型详解

MySQL索引原理与高性能索引策略

本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!