MySQL索引优化与应用指南

用户11286421

发布于 2025-03-28 10:56:38

12700

代码可运行

文章被收录于专栏：学习学习

运行总次数：0

代码可运行

索引的基本概念

索引类似于书本中的目录，它提供了一个数据项与实际数据存储位置之间的映射关系。通过索引，可以快速找到数据，而不需要遍历整个数据集。

索引的类型：

常见的索引类型有：

B树索引：在数据库中，B树（或B+树）是最常见的索引结构。它是一种平衡树，保证了数据的查找、插入和删除操作都能保持在对数时间复杂度。
B树：每个节点可以有多个子节点，且有序排列。
B+树：是B树的一种变体，所有的数据都存储在叶子节点中，内部节点仅仅起到导航作用。B+树在数据库中应用广泛。
哈希索引：使用哈希函数将数据映射到一个位置。查找时，通过哈希函数快速定位到数据的存储位置。哈希索引的优点是查找速度极快，但它不支持范围查询。
位图索引：通过位图的方式存储数据的存在与否，适合低基数（distinct值较少）列的查询，比如性别、地区等。
全文索引：专门用于处理文本数据，能够快速搜索关键词。

无索引的问题

索引：提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度。常见索引分为：

主键索引(primary key)
唯一索引(unique)
普通索引(index)
全文索引(fulltext)–解决中子文索引问题。

案例：先整一个海量表，在查询的时候，看看没有索引时有什么问题？

--构建一个8000000条记录的数据
--构建的海量表数据需要有差异性，所以使用存储过程来创建， 拷贝下面代码就可以了，暂时不用理解
-- 产生随机字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin 
 declare chars_str varchar(100) default
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare return_str varchar(255) default '';
 declare i int default 0;
 while i < n do 
   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i = i + 1;
   end while;
 return return_str;
 end $$
delimiter ;
--产生随机数字
delimiter $$
create function rand_num()
returns int(5)
begin 
 declare i int default 0;
 set i = floor(10+rand()*500);
return i;
end $$
delimiter ;
--创建存储过程，向雇员表添加海量数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0; 
 set autocommit = 0;  
 repeat
  set i = i + 1;
  insert into EMP values ((start+i) 
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
 until i = max_num
 end repeat;
 commit;
end $$
delimiter ;
-- 执行存储过程，添加8000000条记录
call insert_emp(100001, 8000000);

到此，已经创建出了海量数据的表了。

查询员工编号为998877的员工

select * from EMP where empno=998877;

可以看到耗时4.93秒，这还是在本机一个人来操作，在实际项目中，如果放在公网中，假如同时有1000个人并发查询，那很可能就死机。

解决方法，创建索引

alter table EMP add index(empno);

换一个员工编号，测试看看查询时间

select * from EMP where empno=123456;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 123456 | sJtPVg | SALESMAN | 0001 | 2025-03-24 00:00:00 | 2000.00 | 400.00 |    463 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (0.04 sec)

索引的理解

建立测试表

create table if not exists user ( 
   id int primary key,     --一定要添加主键哦，只有这样才会默认生成主键索引
   age int not null,
   name varchar(16) not null
);
mysql> show create table user \G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
 `id` int(11) NOT NULL,
 `age` int(11) NOT NULL,
 `name` varchar(16) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8  --默认就是InnoDB存储引擎
1 row in set (0.00 sec)

插入多条记录

--插入多条记录，注意，我们并没有按照主键的大小顺序插入哦
mysql> insert into user (id, age, name) values(3, 18, '杨过');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user (id, age, name) values(4, 16, '小龙女');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user (id, age, name) values(2, 26, '黄蓉');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user (id, age, name) values(5, 36, '郭靖');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user (id, age, name) values(1, 56, '欧阳锋');
Query OK, 1 row affected (0.00 sec)

查看插入结果

select * from user; 
+----+-----+-----------+
| id | age | name      |
+----+-----+-----------+
|  1 |  56 | 欧阳锋    |
|  2 |  26 | 黄蓉      |
|  3 |  18 | 杨过      |
|  4 |  16 | 小龙女    |
|  5 |  36 | 郭靖      |
+----+-----+-----------+
5 rows in set (0.00 sec)

索引的工作原理

在数据库中，索引通常是通过创建一个指向数据的指针来加速查找过程。例如，当你查询一个数据库表时，数据库并不会扫描所有记录，而是使用索引跳转到数据的位置。

举例：假设有一个包含百万条记录的表，索引会创建一个映射，指示每个数据的具体位置。比如，在一个用户表中，索引可以指示每个用户的ID以及该用户的详细信息存储在何处。查询时，系统会使用这个映射直接跳转到特定用户，而不需要检查每一条记录。

理解单个Page

MySQL 中要管理很多数据表文件，而要管理好这些文件，就需要先描述，在组织 ,我们目前可以简单理解成一个个独立文件是有一个或者多个Page构成的。

不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 构成双向链表因为有主键的问题， MySQL 会默认按照主键给我们的数据进行排序，从上面的Page内数据记录可以看出，数据是有序且彼此关联的。

为什么数据库在插入数据时要对其进行排序呢？我们按正常顺序插入数据不是也挺好的吗？插入数据时排序的目的，就是优化查询的效率。页内部存放数据的模块，实质上也是一个链表的结构，链表的特点也就是增删快，查询修改慢，所以优化查询的效率是必须的。正式因为有序，在查找的时候，从头到后都是有效查找，没有任何一个查找是浪费的，而且，如果运气好，是可以提前结束查找过程的。

理解多个Page

通过上面的分析，我们知道，上面页模式中，只有一个功能，就是在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能。但是，我们也可以看到，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。
如果有1千万条数据，一定需要多个Page来保存1千万条数据，多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找。这效率也太低了。

索引操作

创建主键索引

第一种方式

-- 在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));

第二种方式：

-- 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));

第三种方式：

create table user3(id int, name varchar(30));
-- 创建表以后再添加主键
alter table user3 add primary key(id);

主键索引的特点：

一个表中，最多有一个主键索引，当然可以使符合主键
主键索引的效率高（主键不可重复）
创建主键索引的列，它的值不能为null，且不能重复
主键索引的列基本上是int

唯一索引的创建

第一种方式

-- 在表定义时，在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);

第二种方式

-- 创建表时，在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));

第三种方式

create table user6(id int primary key, name varchar(30)）；
alter table user6 add unique(name);

唯一索引的特点：

一个表中，可以有多个唯一索引
查询效率高
如果在某一列建立唯一索引，必须保证这列不能有重复数据
如果一个唯一索引上指定not null，等价于主键索引

普通索引的创建

第一种方式

create table user8(id int primary key,
 name varchar(20),
 email varchar(30),
 index(name) --在表的定义最后，指定某列为索引
);

第二种方式

create table user9(id int primary key, name varchar(20), email 
varchar(30));
alter table user9 add index(name); --创建完表以后指定某列为普通索引

第三种方式

create table user10(id int primary key, name varchar(20), email 
varchar(30));
-- 创建一个索引名为 idx_name 的索引 
create index idx_name on user10(name);

普通索引的特点：

一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

全文索引的创建当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)。

CREATE TABLE articles (
 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
 title VARCHAR(200),
 body TEXT,
 FULLTEXT (title,body)
)engine=MyISAM;

INSERT INTO articles (title,body) VALUES
 ('MySQL Tutorial','DBMS stands for DataBase ...'),
 ('How To Use MySQL Well','After you went through a ...'),
 ('Optimizing MySQL','In this tutorial we will show ...'),
 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
 ('MySQL vs. YourSQL','In the following database comparison ...'),
 ('MySQL Security','When configured properly, MySQL ...');

查询有没有database数据如果使用如下查询方式，虽然查询出数据，但是没有使用到全文索引

mysql> select * from articles where body like '%database%';
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  1 | MySQL Tutorial   | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+

可以用explain工具看一下，是否使用到索引

mysql> explain select * from articles where body like '%database%'\G
*************************** 1. row ***************************
           id: 1
 select_type: SIMPLE
       table: articles
         type: ALL
possible_keys: NULL
         key: NULL <== key为null表示没有用到索引
     key_len: NULL
         ref: NULL
         rows: 6
       Extra: Using where
1 row in set (0.00 sec)

如何使用全文索引呢？

mysql> SELECT * FROM articles
   -> WHERE MATCH (title,body) AGAINST ('database');
 +----+-------------------+------------------------------------------+
 | id | title             | body                                     |
 +----+-------------------+------------------------------------------+
 |  5 | MySQL vs. YourSQL | In the following database comparison ... |
 |  1 | MySQL Tutorial   | DBMS stands for DataBase ...             |
 +----+-------------------+------------------------------------------+

通过explain来分析这个sql语句

mysql> explain SELECT * FROM articles WHERE MATCH (title,body) AGAINST 
('database')\G
*************************** 1. row ***************************
           id: 1
 select_type: SIMPLE
       table: articles
         type: fulltext
possible_keys: title
         key: title <= key用到了title
     key_len: 0
         ref: 
         rows: 1
       Extra: Using where

查询索引

第一种方法： show keys from 表名

mysql> show keys from goods\G
 *********** 1. row ***********
       Table: goods   <= 表名
 Non_unique: 0       <= 0表示唯一索引
   Key_name: PRIMARY <= 主键索引
 Seq_in_index: 1
 Column_name: goods_id <= 索引在哪列
   Collation: A
 Cardinality: 0
   Sub_part: NULL
     Packed: NULL
       Null: 
 Index_type: BTREE   <= 以二叉树形式的索引
     Comment: 
1 row in set (0.00 sec)

第二种方法: show index from 表名;
第三种方法（信息比较简略）： desc 表名；

删除索引

第一种方法-删除主键索引： alter table 表名 drop primary key;
第二种方法-其他索引的删除： alter table 表名 drop index 索引名；索引名就是show keys from 表名中的 Key_name 字段

mysql> alter table user10 drop index idx_name;

第三种方法方法： drop index 索引名 on 表名

mysql> drop index name on user8;

索引创建原则

比较频繁作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合作创建索引
不会出现在where子句中的字段不该创建索引

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2025-03-27，如有侵权请联系 cloudcommunity@tencent.com 删除

数据

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度