MySql基础：表的索引_表索引-优快云博客

本文链接：https://blog.youkuaiyun.com/sjm18796129182/article/details/143178118

前言：为什么需要索引

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

测试：先整一个海量表，在查询的时候，看看没有索引时有什么问题？
--构建一个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的员工
mysql> select*from EMP where empno=998877;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 998877 | HdtKtP | SALESMAN | 0001 | 2024-10-23 00:00:00 | 2000.00 | 400.00 |    433 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (7.55 sec)

查询时间用了7s左右，试着船舰索引后再去查询！

测试：
mysql> alter table EMP add index(empno);
Query OK, 0 rows affected (37.48 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select*from EMP where empno=998877;
+--------+--------+----------+------+---------------------+---------+--------+--------+
| empno  | ename  | job      | mgr  | hiredate            | sal     | comm   | deptno |
+--------+--------+----------+------+---------------------+---------+--------+--------+
| 998877 | HdtKtP | SALESMAN | 0001 | 2024-10-23 00:00:00 | 2000.00 | 400.00 |    433 |
+--------+--------+----------+------+---------------------+---------+--------+--------+
1 row in set (0.01 sec)

说明：可以看到在没有增加索引前，查询时间用到了大概7秒，而增加索引后，查询时间只需要0.01秒，由此可见索引的重要性。

2. 建立共识

说明：MySQL 中的数据文件，是以page为单位保存在磁盘当中的。 MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。而此时IO的基本单位就是Page。为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。为何更高的效率，一定要尽可能的减少系统和磁盘IO的次数。

3. 索引的理解

建立测试表

测试：mysql> create table if not exists user(
    -> id int primary key,
    -> age int not null,
    -> name varchar(16)not null);
Query OK, 0 rows affected (0.03 sec)

说明：插入多条数据且没有按照主键的大小顺序进行插入。

测试：
mysql> insert into user (id, age, name) values(3, 18, '杨过');
Query OK, 1 row affected (0.02 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.00 sec)

ERROR 1062 (23000): Duplicate entry '2' for key 'user.PRIMARY'
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)

说明：查询插入结果。

测试：
mysql> select *from user;
+----+-----+-----------+
| id | age | name      |
+----+-----+-----------+
|  1 |  56 | 欧阳锋    |
|  2 |  26 | 黄蓉      |
|  3 |  18 | 杨过      |
|  4 |  16 | 小龙女    |
|  5 |  36 | 郭靖      |
+----+-----+-----------+

说明：为何MySQL和磁盘进行IO交互的时候，要采用Page的方案进行交互呢?用多少，加载多少不香吗? 如上面的5条记录，如果MySQL要查找id=2的记录，第一次加载id=1，第二次加载id=2，一次一条记录，那么就需要2次IO。如果要找id=5，那么就需要5次IO。但，如果这5条(或者更多)都被保存在一个Page中(16KB，能保存很多记录),那么第一次IO查找id=2的时候，整个Page会被加载到MySQL的Buffer Pool中，这里完成了一次IO。但是往后如果在查找id=1,3,4,5 等，完全不需要进行IO了，而是直接在内存中进行了。所以，就在单Page里面，大大减少了IO的次数。你怎么保证，用户一定下次找的数据，就在这个Page里面？我们不能严格保证，但是有很大概率，因为有局部性原理。往往IO效率低下的最主要矛盾不是IO单次数据量的大小，而是IO的次数。

4. 索引操作

4. 1 创建主键索引

第一种方式：在建表的时候，直接在字段名后指定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。

4.2 唯一索引

第一种：在表定义的时候，在某列的后面直接指定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，等价于主键索引。

4.3 普通索引

第一种：在表的定义最后面，指定某列为索引

语法：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);

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

4.4 全文索引

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

语法：SELECT * FROM tablename WHERE MATCH (全文索引的列) AGAINST ('查询对象');

测试：

Database changed
mysql> CREATE TABLE articles (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> title VARCHAR(200),
    -> body TEXT,
    -> FULLTEXT (title,body)
    -> )engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| articles      |
| exam_result   |
| student       |
| test1         |
| user          |
+---------------+
5 rows in set (0.00 sec)

mysql> 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 ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
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 ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

4.5 索引操作

查询索引

第一种：show keys from tablename

测试：
mysql> show keys from test1\G;
*************************** 1. row ***************************
        Table: test1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

第二种：show index from table name

测试：
mysql> show index from test2\G;
*************************** 1. row ***************************
        Table: test2
   Non_unique: 1
     Key_name: idx_name
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row in set (0.00 sec)

第三种：desc tablename

删除索引

第一种-删除主键索引：alter table tablename drop primary key

测试：
mysql> alter table test1 drop primary key;
Query OK, 0 rows affected (0.06 sec)

第二种-其他索引的删除：alter table tablename drop index 索引名

测试：

mysql> alter table test2 drop index idx_name;
Query OK, 0 rows affected (0.02 sec)

第三种：drop index 索引名 on tablename

测试：
mysql> drop index idx_name on test2;
Query OK, 0 rows affected (0.01 sec)

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