MySQL的索引原理与查询优化

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL的索引原理与查询优化

slashboywang 2018-08-25 00:12:00 浏览695
展开阅读全文

一、MySQL 索引简介

1、 MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。

打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。

2、索引分单列索引和组合索引。
  • 单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。
  • 组合索引,即一个索引包含多个列。
3、索引的详细分类:
  1. 普通索引index :加速查找
  2. 唯一索引
    • 主键索引:primary key :加速查找+约束(不为空且唯一)
    • 唯一索引:unique:加速查找+约束 (唯一)
  3. 联合索引
    • primary key(id,name):联合主键索引
    • unique(id,name):联合唯一索引
    • index(id,name):联合普通索引
  4. 全文索引fulltext :用于搜索很长一篇文章的时候,效果最好。
  5. 空间索引spatial :了解就好,几乎不用
4、创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。

5、索引的两大类型hash与btree
  1. 我们可以在创建上述索引的时候,为其指定索引类型,分两类
  • hash类型的索引:查询单条快,范围查询慢
  • btree类型的索引:b+树,层数越多,数据量指数级增长(我们就用它,因为innodb默认支持它)

2、不同的存储引擎支持的索引类型也不一样

  • InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
  • MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
  • Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;
  • NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引;
  • Archive 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引;
6、滥用索引的缺点:
  • 第一,创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。

  • 第二,索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。

  • 第三,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

创建UNIQUE | FULLTEXT | SPATIAL 一样的方法

方法一:创建表时创建索引

CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
   [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY  [indexName] (username(length))   
);  

方法二:CREATE在已存在的表上创建索引

CREATE [UNIQUE | FULLTEXT | SPATIAL | INDEX ]  indexName ON mytable(username(length)); 

方式三:修改表结构(添加索引)

ALTER table tableName ADD  [UNIQUE | FULLTEXT | SPATIAL | INDEX ] indexName(columnName)

删除索引的语法

DROP INDEX [indexName] ON mytable; 

三、实测索引的功效

1. 前期准备工作

  • 创建一个名为text的数据库:
create database text charset utf8;
  • 创建一张名为text的数据表
create table text(id int,name varchar(20))
  • 通过创建存储过程,实现批量插入记录(大约需要半小时时间)
delimiter $$ #声明存储过程的结束符号为$$
create procedure insertinfo()
BEGIN
    declare i int default 1;
    while(i<1000000)do
        insert into text values(i,concat('wangjifei',i));
        set i=i+1;
    end while;
END $$ 
delimiter ; #重新声明分号为结束符号为;
  • 查看存储过程
 show create procedure insertinfo\G 
  • 调用存储过程
 call insertinfo();

2、在没有索引的前提下测试查询速度

mysql> select * from text where id = 1234;
+------+---------------+
| id   | name          |
+------+---------------+
| 1234 | wangjifei1234 |
+------+---------------+
1 row in set (0.39 sec)
mysql> select * from text where name = 'wangjifei12345';
+-------+----------------+
| id    | name           |
+-------+----------------+
| 12345 | wangjifei12345 |
+-------+----------------+
1 row in set (0.53 sec)

3、加上索引

//1. 一定是为搜索条件的字段创建索引,比如select * from t1 where age > 5;就需要为age加上索引
//2. 在表中已经有大量数据的情况下,建索引会很慢,且占用硬盘空间,插入删除更新都很慢,只有查询快
//比如create index myname on text(name);会扫描表中所有的数据,然后以name为数据项,
//创建索引结构,存放于硬盘的表中。建完以后,再查询就会很快了

//给name加上普通索引
mysql> create index myname on text(name);
Query OK, 0 rows affected (18.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

//给id加上唯一索引
mysql> create unique index myid on text(id);
Query OK, 0 rows affected (10.83 sec)
Records: 0  Duplicates: 0  Warnings: 0
效果立竿见影,查询速度翻了几十倍
mysql> select * from text where id = 1234;
+------+---------------+
| id   | name          |
+------+---------------+
| 1234 | wangjifei1234 |
+------+---------------+
1 row in set (0.00 sec)
mysql> select * from text where name = 'wangjifei12345';
+-------+----------------+
| id    | name           |
+-------+----------------+
| 12345 | wangjifei12345 |
+-------+----------------+
1 row in set (0.01 sec)
mysql> select * from text where name = 'wangjifei823458';
+--------+-----------------+
| id     | name            |
+--------+-----------------+
| 823458 | wangjifei823458 |
+--------+-----------------+
1 row in set (0.00 sec)

四、批量添加测试数据的方法:

  • 通过存储过程批量创建数据
1. 创建存储过程
delimiter $$ #声明存储过程的结束符号为$$
create procedure insertinfo()
BEGIN
   declare i int default 1;
   while(i<1000000)do
       insert into text values(i,concat('wangjifei',i));
       set i=i+1;
   end while;
END $$ 
delimiter ; #重新声明分号为结束符号为;

2. 查看存储过程
show create procedure insertinfo\G;

3. 调用存储过程
call insertinfo();

五、正确使用索引

  • 覆盖索引
    select * from text where name = 'wangjifei882345';
    该sql命中了索引,但未覆盖索引。利用name = 'wangjifei882345'到索引的数据结构中定位到该name在硬盘中的位置,或者说再数据表中的位置。

    但是我们select的字段为*,除了name以外还需要其他字段,这就意味着,我们通过索引结构取到name还不够,还需要利用该name再去找到该name所在行的其他字段值,这是需要时间的,

    很明显,如果我们只select name,就减去了这份苦恼,如下select name from text where name = 'wangjifei882345';这条就是覆盖索引了,命中索引,且从索引的数据结构直接就取到了name在硬盘的地址,速度很快

mysql> select name from text where name = 'wangjifei882345';
+-----------------+
| name            |
+-----------------+
| wangjifei882345 |
+-----------------+
1 row in set (0.00 sec)
  • 联合索引
    为了增加效果对比,在创建联合索引前将之前创建的普通索引删除掉了
mysql> select * from text where id = 2435353252 and name = 'wangjifei123333';
Empty set (0.58 sec)  //普通查询

mysql> create index idname on text(id,name); //创建联合索引
Query OK, 0 rows affected (26.86 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from text where id = 2435353252 and name = 'wangjifei123333'; 
Empty set (0.00 sec)  // 联合索引查询

网友评论

登录后评论
0/500
评论