【MySQL 保姆级教学】深层明白索引及特性（重点）--下（12） ...

1. 索引的作用

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

• 主键索引（Primary Key）
  主键索引是一种特别的唯一索引，不允许有重复值，而且每个表只能有一个主键。主键通常用于唯一标识表中的每一行数据。在创建表的时候，如果指定了某列为主键，那么该列会主动创建一个主键索引。
  
• 唯一索引（Unique）
  唯一索引确保了索引列中的全部值都是唯一的，但与主键差别的是，一个表可以有多个唯一索引。唯一索引允许有一个或多个NULL值存在，这取决于数据库体系的设计。
  
• 平常索引（Index）
  平常索引是最基本的索引范例，它没有任何限定，可以包含重复的值。通过在查询条件中频繁使用的列上创建平常索引，可以显著提高查询效率。
  
• 全文索引（Fulltext）
  全文索引主要用于全文本搜刮，它可以对文本内容进行复杂的搜刮操作，比如查找包含特定单词或短语的记录。全文索引特别适合于处理大量的文本数据，如消息文章、博客帖子等。值得注意的是，差别的数据库管理体系支持的全文索引功能大概有所差别，比方MySQL中的InnoDB和`MyISAM存储引擎都支持全文索引，但实现方式和性能特点大概有所区别。
  

示例：
当我们在数据量少的表中查询数据不会发现主键查询和平常查询之间的差别。
创建一个海量表：

1. --构建一个8000000条记录的数据--构建的海量表数据需要有差异性，所以使用存储过程来创建， 拷贝下面代码就可以了，暂时不用理解-- 产生随机字符串
2. delimiter $$
3. create function rand_string(n INT)
4. returns varchar(255)
5. begin
6. declare chars_str varchar(100) default
7. 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
8. declare return_str varchar(255) default '';
9. declare i int default 0;
10. while i < n do
11. set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
12. set i = i + 1;
13. end while;
14. return return_str;
15. end $$
16. delimiter ;--产生随机数字
17. delimiter $$
18. create function rand_num()
19. returns int(5)
20. begin
21. declare i int default 0;
22. set i = floor(10+rand()*500);
23. return i;
24. end $$
25. delimiter ;--创建存储过程，向雇员表添加海量数据
26. delimiter $$
27. create procedure insert_emp(in start int(10),in max_num int(10))
29. begin
30. declare i int default 0;
31. set autocommit = 0;  
32. repeat
33. set i = i + 1;
34. insert into EMP values ((start+i)
35. ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
36. until i = max_num
37. end repeat;
38. commit;
39. end $$
40. delimiter ;-- 执行存储过程，添加8000000条记录
41. call insert_emp(100001, 8000000);

复制代码

注：已经绑定资源，可以自行下载。
进入到一个数据库，用soure命令导入数据：
我的数据包index_data.sql在 /root/MySQL_data/some 目录下
命令：

1. //进入到test数据库
2. use test
4. // 导入数据
5. source /root/MySQL_data/some/index-data.sql

复制代码

这是一个有8000000条记录的数据，需要等一会儿，我花费了7分钟。

此时的表还没有创建索引，进行查询：
命令：select * from EMP where empno = 188888

查询花费了4.69秒，这是在本机一个人来操作，在实际项目中，如果放在公网，如果同时有
1000个人并发查询，那很大概就死机。
怎么让查询变得快呢？
答：创建索引。
创建索引：
命令：alter table EMP add index(empno);

创建索引花费了26.7秒。
再次查询：
命令：select * from EMP where empno = 188888;

当实行查询的时候会明显的变快。
2. 索引操作

2.1 主键索引

2.1.1 主键索引的特点

• 唯一性：主键索引确保了索引列中的每一个值都是唯一的，不允许出现重复值。这是主键索引的焦点特性，确保了每一行数据在表中都有一个唯一的标识符。
  
• 非空性：主键索引的列不允许有NULL值。这意味着在插入或更新数据时，必须为该列提供一个有用的、非空的值。
  
• 主动创建：当您在创建表时指定某个列为PRIMARY KEY时，数据库会主动为该列创建一个主键索引。如果表中没有显式界说主键，某些数据库体系大概会主动创建一个隐式的主键（比方，SQL Server中的IDENTITY列）。
  
• 快速查找：主键索引通常是一个B+树索引，这种布局允许数据库高效地进行查找、插入和删除操作。因此，使用主键进行查询通常比使用其他索引或无索引的查询要快得多。
  
• 聚簇索引：在某些数据库体系中，主键索引默认是聚簇索引（Clustered Index）。聚簇索引决定了数据在物理存储上的顺序，这意味着按主键顺序访问数据时性能最佳。一个表只能有一个聚簇索引，因为数据只能有一种物理排序方式。
  
• 约束作用：主键不仅是一个索引，照旧一种约束。它确保了表中数据的完整性和同等性，防止了重复记录的插入。
  
• 外键引用：主键通常被用作其他表的外键（Foreign Key），以创建表之间的关系。外键必须引用一个唯一的列，通常是另一个表的主键。
  

2.1.2 创建主键索引

• 创建表时在字段后面指定
  命令：
  
  1. create table t1(
  2. id int primary key,
  3. name varchar(10),
  4. age tinyint
  5. );

  复制代码
• 创建表时，在最后面指定某列为索引
  命令：
  
  1. create table t2(
  2. id int,
  3. name varchar(10),
  4. age tinyint,
  5. primary key(id)
  6. );

  复制代码
• 创建表结束后，用 alter命令
  命令：
  
  1. create table t3(
  2. id int,
  3. name varchar(10),
  4. age tinyint
  5. );
  7. alter table t3 add primary key(id);

  复制代码

2.2 唯一键索引

2.2.1 唯一键索引的特点

• 唯一性：
  唯一键索引确保索引列中的每一个值都是唯一的，不允许出现重复值。这是唯一键索引的焦点特性，确保了数据的唯一性和完整性。
  
• 允许多个NULL值：
  与主键索引差别，唯一键索引允许列中有多个NULL值。这是因为NULL在数据库中被视为未知值，而不是详细的重复值。
  
• 提高查询性能：
  唯一键索引可以显著提高查询性能，特别是在需要确保某一列或组合列的值唯一的情况下。通过创建唯一键索引，数据库可以在查询时更快地找到特定的记录。
  
• 约束作用：
  唯一键索引不仅是一个索引，照旧一种约束。它确保了表中数据的完整性和同等性，防止了重复记录的插入。
  
• 可以应用于多个列：
  唯一键索引可以应用于单个列，也可以应用于多个列的组合。当应用于多个列的组合时，确保整个组合的值是唯一的，而不是单个列的值。
  

2.2.2 唯一索引的创建

• 创建表时在字段后面指定
  命令：
  
  1. create table t1(
  2. id int unique,
  3. name varchar(10),
  4. age tinyint
  5. );

  复制代码
• 创建表时，在最后面设置某列为索引
  命令：
  
  1. create table t2(
  2. id int,
  3. name varchar(10),
  4. age tinyint,
  5. unique(id)
  6. );

  复制代码
• 创建表结束后，用 alter命令
  命令：
  
  1. create table t3(
  2. id int,
  3. name varchar(10),
  4. age tinyint
  5. );
  7. alter table t3 add unique(id);

  复制代码

2.3 平常索引

2.3.1 平常索引的特点

• 允许重复值：
  平常索引允许索引列中的值重复。这意味着同一个值可以在索引列中出现多次。
  
• 提高查询性能：
  平常索引可以显著提高查询性能，特别是对于经常用于查询条件的列。通过创建索引，数据库可以更快地定位到所需的记录。
  
• 不强制非空：
  平常索引不要求列中的值必须是非空的。列中的值可以是NULL，而且可以有多个NULL值。
  
• 可以应用于多个列：
  平常索引可以应用于单个列，也可以应用于多个列的组合。当应用于多个列的组合时，索引会根据组合列的值进行排序和查找。
  
• 不影响数据插入和更新：
  创建平常索引不会像唯一索引那样对数据插入和更新施加额外的约束。这意味着在插入或更新数据时，纵然索引列中有重复值，也不会引发错误。
  

总的来说：平常索引的主要目的是提高查询性能，特别是在频繁用于查询条件的列上。
2.3.2 平常索引的创建

• 创建表时，在最后面设置某列为索引
  命令：
  
  1. create table t1(
  2. id int,
  3. name varchar(10),
  4. age tinyint,
  5. index(id)
  6. );

  复制代码
• 创建表后，用alter命令
  命令：
  
  1. create table t2(
  2. id int,
  3. name varchar(10),
  4. age tinyint
  5. );
  7. alter table t2 add index(id);

  复制代码
• 创建表后，创建一个索引名为 idx_name 的索引，create index
  命令：
  
  1. create table t3(
  2. id int,
  3. name varchar(10),
  4. age tinyint
  5. );
  7. create index ind_t3_id on t3(id);

  复制代码
  
  
  

2.4 全文索引

2.4.1 全文索引的作用

当对文章字段或有大量笔墨的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)。
早期版本的MySQL（5.6及之前）中，全文索引仅支持MyISAM存储引擎。
从MySQL 5.7开始，InnoDB存储引擎也支持全文索引。
全文索引的主要作用：

• 提高搜刮性能
  

• 快速查找：全文索引使用专门的算法（如倒排索引）来加速文本搜刮。通过索引，数据库可以快速定位包含特定单词或短语的记录，而不需要扫描整个表。
  
• 减少I/O操作：全文索引减少了磁盘I/O操作，提高了查询效率，特别是在处理大量文本数据时。
  

• 支持复杂的搜刮操作
  

• 模糊匹配：全文索引可以支持模糊匹配，如部门单词匹配、前缀匹配等。
  
• 短语搜刮：可以搜刮包含特定短语的记录。
  
• 近义词搜刮：通过配置，可以支持近义词搜刮，提高搜刮的准确性和相干性。
  
• 布尔搜刮：支持使用布尔运算符（如AND、OR、NOT）进行复杂查询。
  

• 自然语言搜刮
  

• 自然语言处理：全文索引可以支持自然语言搜刮，即用户可以用自然语言形式的查询语句进行搜刮，体系会返回最相干的记录。
  
• 权重计算：全文索引可以根据关键词在文档中的出现频率和位置等因素计算权重，返回最相干的记录。
  

2.4.2 全文索引的创建

创建全文索引：

1. CREATE TABLE articles (
2. id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
3. title VARCHAR(200),
4. body TEXT,
5. FULLTEXT (title,body)
6. )engine=MyISAM;

复制代码

插入数据：

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

复制代码

查询有没有database数据
命令：select * from articles where body like '%database%';

显然，可以查询出效果。
但是，有没有使用全文索引呢？
可以explain 工具查看一下。
命令：
explain select * from articles where body like '%database%';

下面是各项的表明：

• id: 查询块的标识号。在这个例子中只有一个查询块，以是id为1。
  
• select_type: 表明查询的范例。这里"SIMPLE"表示这个查询只涉及一张表。
  
• table: 被访问的表名。这里是"articles"表。
  
• partitions: 如果表被分区，则此列为分区名称。在这里，由于表未分区，因此为NULL。
  
• type: 访问范例，表明MySQL如何读取数据。这里的"ALL"意味着全表扫描，即MySQL将遍历整张表的全部行。
  
• possible_keys: 大概使用的索引列表。在这个例子中，没有列出任何大概的索引，说明查询没有使用索引。
  
• key: 实际使用的索引。同样，这里也是NULL，确认了没有使用索引的事实。
  
• key_len: 索引中使用的字节数。既然没有使用索引，这一项也为NULL。
  
• ref: 显示了哪个列或常量被用来查找行。这里为NULL，再次确认没有使用索引来优化查询。
  
• rows: MySQL估计的要查抄的行数。这里估计为6行。
  
• filtered: 过滤掉不符合WHERE子句的行后剩余的百分比。这里为16.67%，意味着大约只有16.67%的数据会被实际返回给查询。
  
• Extra: 包含其他额外的信息。这里的"Using where"表示MySQL正在使用WHERE子句过滤行。
  

总的来说，这个EXPLAIN输出表明了一个全表扫描的操作，没有使用任何索引，大概会导致较慢的查询速度，尤其是在大表上的情况。如果大概的话，考虑添加适当的索引以改善查询性能。
已经创建全文索引了，什么没有使用呢？怎么才气使用呢？
命令：

1. select * from articles
2. where match(body) against ('database');

复制代码

为什么会报错呢？
答：当你在一个全文索引中指定了多个列时，查询时必须使用相同的列集。
命令：

1. select * from articles
2. where match(title,body) against('database');

复制代码

用explain工具查询：

key对应的是title，可以看出，使用的是title索引，以是，改查询使用索引了。
为什么key只是对应title而不是对应title 和body 呢？
答：创建全文索引的时候使用的是 fulltext(title,body),以是该全文索引是多列，而且该全文索引的名字是第一个列名–title 。这里的key对应的是索引的名字
3. 查询索引

• 方法一：
  语法：show keys from table_name;
  上述命令显示的内容看着不方便可以使用：
  show keys from table_name\G
  示例：
  命令：show keys from articles \G
  
  
  
  
• 方法2：
  语法：show index from table_name;
  或：show index from table_name\G
  示例：
  命令：show index from t1\G
  
  
  
  
• 方法3(查询到的信息表大略)：
  语法：desc table_name;
  

4. 删除索引

• 删除主键索引
  语法：alter table table_name drop primary key;
  一个表中只用一个主键索引，像这种范式的删除方法实在照旧针对它一个索引。
  示例：
  命令：alter table t1 drop primary;
  
  
  
  
• 其他索引的删除
  语法：alter table table_name drop index column_name;
  
  
  
  
• 使用 drop index
  语法：drop index index_name on table_name;
  该语法不能删除主键，主键是表中重要的构成部门，只能用 alter table t1 drop primary进行删除。
  示例:
  删除表t1的唯一索引unique
  命令：drop index unique on t1
  
  
  
  为什么会报错呢？
  答：unique 是索引的范例，不是索引的名字。删除索引的时候要先查询索引的范例。
  查询一下索引的名字：show index from t1\G
  
  
  
  删除number列的索引：drop index number on t1;
  
  
  
  

5. 索引创建原则

索引创建原则 ：

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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。