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来删)
创建索引的技巧
- 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),
表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、
性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,
这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录 - 对 where,on,group by,order by 中出现的列使用索引。
- 对较小的数据列使用索引,这样会使索引文件更小,同时内存中也可以装载更多的索引键。
- 为较长的字符串使用前缀索引。
- 不要过多创建索引,除了增加额外的磁盘空间外,对于DML操作的速度影响很大,因为其每增删改一次就得从新建立索引。
- 使用组合索引,可以减少文件索引大小,在使用时速度要优于多个单列索引。
- 最左前缀匹配原则。mysql会一直向右匹配直到遇到范围查找就停滞匹配。比如:a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。索引的建立需要考虑常用查询的字段顺序。
- 索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’
就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,
但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。
所以语句应该写成create_time = unix_timestamp(’2014-05-29’); - 尽可能去扩展索引,而不是新建索引。当需要通过增加索引来提高性能时,考虑结合现有索引判断是否可以在已有索引上做扩展。
- =和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)
- 连表时注意条件类型需一致
- 索引散列值(重复少)不适合建索引,例:性别不适合
慢查询优化的基本步骤
- 先运行看看是否真的很慢,注意设置SQL_NO_CACHE
- where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高
- explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
- order by limit 形式的sql语句让排序的表优先查
- 了解业务方使用场景
- 加索引时参照建索引的几大原则
- 观察结果,不符合预期继续从1分析
Reference
本作品采用《CC 协议》,转载必须注明作者和本文链接