mysql 索引

基本介绍

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度
索引的价值，在于提高一个海量表的检索速度，索引的算法有btree 二叉树的算法，还有一种就是hash算法

索引的分类

主键索引 (primary key)，唯一索引(unique)，普通索引(index)，全文索引(fulltext)

创建一个海量表

//部门表
mysql> CREATE TABLE dept( /*部门表*/
    -> deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
    -> dname VARCHAR(20)  NOT NULL  DEFAULT "",
    -> loc VARCHAR(13) NOT NULL DEFAULT ""
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected (0.06 sec)

//员工表
mysql> CREATE TABLE emp
    -> (empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
    -> ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
    -> job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
    -> mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
    -> hiredate DATE NOT NULL,/*入职时间*/
    -> sal DECIMAL(7,2)  NOT NULL,/*薪水*/
    -> comm DECIMAL(7,2) NOT NULL,/*红利*/
    -> deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
    -> )ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected (0.06 sec)

//工资级别表
mysql> CREATE TABLE salgrade(
    -> grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    -> losal DECIMAL(17,2)  NOT NULL,
    -> hisal DECIMAL(17,2)  NOT NULL
    -> )ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

//插入数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

//定义一个新的命令结束符 防止存储过程冲突
delimiter $$

//创建一个函数，名字 rand_string，可以随机返回我指定的个数字符串
mysql> create function rand_string(n INT)
    -> returns varchar(255) #该函数会返回一个字符串
    -> begin
    -> #定义了一个变量 chars_str， 类型  varchar(100)
    -> #默认给 chars_str 初始值   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
    -> declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    -> declare return_str varchar(255) default '';
    -> declare i int default 0;
    -> while i < n do
    -> # concat 函数 : 连接函数mysql函数
    -> set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
    -> set i = i + 1;
    -> end while;
    -> return return_str;
    -> end $$
Query OK, 0 rows affected (0.05 sec)

//这里我们又自定了一个函数,返回一个随机的部门号
mysql> create function rand_num( )
    -> returns int(5)
    -> begin
    -> declare i int default 0;
    -> set i = floor(10+rand()*500);
    -> return i;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

//创建一个存储过程， 可以添加雇员
mysql> create procedure insert_emp(in start int(10),in max_num int(10))
    -> begin
    -> declare i int default 0;
    -> #set autocommit =0 把autocommit设置成0
    -> #autocommit = 0 含义: 不要自动提交
    -> set autocommit = 0; #默认不提交sql语句
    -> repeat
    -> set i = i + 1;
    -> #通过前面写的函数随机产生字符串和部门编号，然后加入到emp表
    -> insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
    -> until i = max_num
    -> end repeat;
    -> #commit整体提交所有sql语句，提高效率
    -> commit;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

//添加8000000数据
mysql> call insert_emp(100001,8000000)$$
Query OK, 0 rows affected (5 min 19.60 sec)

mysql> delimiter ;

//从800w的数据中查询
//本地操作且一个人查询需要的1.15sec已经非常慢了，如果一个网站1w人同时操作 会挂的
mysql> select * from emp where empno=3189732;
+---------+--------+----------+-----+------------+---------+--------+--------+
| empno   | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+---------+--------+----------+-----+------------+---------+--------+--------+
| 3189732 | OEFnxA | SALESMAN |   1 | 2020-11-21 | 2000.00 | 400.00 |    124 |
+---------+--------+----------+-----+------------+---------+--------+--------+
1 row in set (1.15 sec)

问题和解决方案

上表查询速度太慢，需要创建索引解决，但是应该创建主键索引，唯一索引 还是 普通索引呢，这个需要看情况，上表empno是随机创建的，可能重复，所以只能创建普通索引。

添加普通索引

//给emp表的empno添加索引
mysql> alter table emp add index(empno);
Query OK, 8000000 rows affected (8.45 sec)
Records: 8000000  Duplicates: 0  Warnings: 0

引起的文件变化

emp.MYI是索引文件，从1k变成了71m

测试效果

//效果显著
mysql> select * from emp where empno=7237892;
+---------+--------+----------+-----+------------+---------+--------+--------+
| empno   | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+---------+--------+----------+-----+------------+---------+--------+--------+
| 7237892 | UDgWoJ | SALESMAN |   1 | 2020-11-21 | 2000.00 | 400.00 |    150 |
+---------+--------+----------+-----+------------+---------+--------+--------+
1 row in set (0.00 sec)

索引的原理

没有索引

添加索引 二叉树

索引的优势和代价

• 二叉树9条数据 只要比对4次 2^4，数据量小看不出其强悍之处， 1073741824条数据只需要比对30次 2^30 = 1073741824
• 执行dml操作时，同时也要自动维护二叉树，将记录放进对应的节点，速度会有一定影响，但是不大
• 添加索引会占用磁盘空间，是用空间换取时间的经典案例
• 以上操作仅给 empno字段创建了索引，但并没有给其他字段创建索引，所以查询其他字段依旧很慢

mysql> select * from emp where empno=7237892;
+---------+--------+----------+-----+------------+---------+--------+--------+
| empno   | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+---------+--------+----------+-----+------------+---------+--------+--------+
| 7237892 | UDgWoJ | SALESMAN |   1 | 2020-11-21 | 2000.00 | 400.00 |    150 |
+---------+--------+----------+-----+------------+---------+--------+--------+
1 row in set (0.00 sec)

mysql> select * from emp where ename='eeeeee';
Empty set (1.22 sec)

创建主键索引

主键本身就自带索引，所以创建主键索引就是创建主键，主键索引是一种特殊的唯一索引，一个表只能有一个主键，所以也只能有一个主键索引，不允许有空值。

//方式一 在创建表时，直接在字段名后面指定 primary key
mysql> create table test1(id int primary key,name varchar(30));
Query OK, 0 rows affected (0.68 sec)

//方式二 在创建表最后，指定某列，某几列为主键索引, 这里也可能是复合主键索引primary key(id,name);
mysql> create table test2(id int,name varchar(30),primary key(id));
Query OK, 0 rows affected (0.08 sec)

//方式三 在创建表后，再指定主键索引
mysql> create table test3(id int,name varchar(30));
Query OK, 0 rows affected (0.07 sec)

mysql> alter table test3 add primary key(id);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

主键索引的特点

• 一个表中，最多有一个主键索引，当然可以是复合主键
• 主键索引和唯一索引的效率高，因为没有重复，二叉树纵向深
• 创建主键索引的列，它的值不能为null，而且不能重复
• 主键索引的列，基本上是int

创建唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

//方式一 在表定义时，某列后直接指定 unique 即可.
mysql> create table test4(id int primary key,name varchar(30) unique);
Query OK, 0 rows affected (0.06 sec)

//方式二 我们可以在表的定义后面指定某列，或者某几列为uniqu
mysql> create table test5(id int primary key,name varchar(30),unique(name));
Query OK, 0 rows affected (0.06 sec)

//方式三 在创建表后，再指定唯一索引
mysql> create table test6(id int primary key,name varchar(30));
Query OK, 0 rows affected (0.06 sec)

mysql> alter table test6 add unique(name);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

//方式四 uni_name是唯一索引的名字，命名规范是uni_后面带上该字段的名称
mysql> create unique index uni_name on test7(name);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test7;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                   |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test7 | CREATE TABLE `test7` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

唯一索引的特点

• 一个表中，可以有多个唯一索引
• 查询的效率很高
• 如果你在某个字段上要创建唯一索引，必须保证这列的值不重复.
• 如果我们在唯一索引上在指定了 not null 约束，等价于一个主键索引

创建普通索引

//方式一 在表的定义的最后指定某列为索引
mysql> create table test8(id int primary key,name varchar(30),index(name));
Query OK, 0 rows affected (0.06 sec)

mysql> create table test9(id int primary key,name varchar(30));
Query OK, 0 rows affected (0.05 sec)

//方式二 在创建表后，再指定普通索引
mysql> alter table test9 index(name);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(name)' at line 1
mysql> alter table test9 add index(name);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

//方式三 inx_name是普通索引的名字，命名规范是inx_后面带上该字段的名称
mysql> create table test10(id int primary key,name varchar(30));
Query OK, 0 rows affected (0.05 sec)
mysql> create index inx_name on test10(name);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test10;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                             |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test10 | CREATE TABLE `test10` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `inx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

普通索引的特点

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

创建全文索引（了解）

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

创建全文索引

mysql> create table articles(
    -> id int unsigned primary key auto_increment,
    -> title varchar(200),
    -> body text,
    -> fulltext(title,body)
    -> )engine=myisam;
Query OK, 0 rows affected (0.04 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.01 sec)
Records: 6  Duplicates: 0  Warnings: 0


mysql> select * from articles;
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | MySQL Tutorial        | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
+----+-----------------------+------------------------------------------+
6 rows in set (0.00 sec)

//这种方式根本没有用到索引
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 ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

//explain + 语句，可以检索该语句是否用到了索引  key=null 没有到
mysql> explain select * from articles where body like '%database%';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | articles | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

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_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 ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

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_len: 0
          ref:
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

全文索引的几个概念

• 停止词: 全文索引不会对一般意义的单词建索引
• 匹配度: mysql提供的全文索引机制，是以匹配度来返回结果

//全文索引不会对一般意义的单词建索引
mysql> select * from articles where match(title,body) against('a');
Empty set (0.00 sec)

//mysql提供的全文索引机制，是以匹配度来返回结果
mysql> select match(title,body) against('database') from articles;
+---------------------------------------+
| match(title,body) against('database') |
+---------------------------------------+
|                    0.6554583311080933 |
|                                     0 |
|                                     0 |
|                                     0 |
|                    0.6626645922660828 |
|                                     0 |
+---------------------------------------+
6 rows in set (0.00 sec)

显示索引信息

你可以使用 show index/keys 命令来列出表中的相关的索引信息，可以通过添加 \G 来格式化输出信息

创建测试表

//test11表包含 主键索引 唯一索引 普通索引
mysql> create table test11(id int primary key,name varchar(30),email varchar(30) unique,index(name));
Query OK, 0 rows affected (0.67 sec)

//这种方式其实也可以查看索引
mysql> show create table test11;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                             |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test11 | CREATE TABLE `test11` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `email` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

显示索引信息

• show keys from test11
• show index from test11
• show indexes from test11
• 三种方式完全一样，一般用第二种

//show keys from test11 和 show index from test11 完全一样
mysql> show keys from test11\G
*************************** 1. row ***************************
        Table: test11		# 表名：test11
   Non_unique: 0			# 不是唯一索引: 否
     Key_name: PRIMARY		# 索引名称：主键
 Seq_in_index: 1			# 
  Column_name: id			# 索引位置：id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE		# 索引算法：二叉树
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: test11
   Non_unique: 0
     Key_name: email
 Seq_in_index: 1
  Column_name: email
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: test11
   Non_unique: 1
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
3 rows in set (0.00 sec)

删除索引

在删除索引前，先使用查看索引的指令，看key_name 是什么，然后再删除

删除主键索引语法

alter table 表名 drop primary key

删除其他索引语法

alter table 表名 drop index 索引名

删除索引的第三种语法

drop index 索引名 on 表名

//查看索引
mysql> show index from test11\G
*************************** 1. row ***************************
        Table: test11
   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:
*************************** 2. row ***************************
        Table: test11
   Non_unique: 0
     Key_name: email
 Seq_in_index: 1
  Column_name: email
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: test11
   Non_unique: 1
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
3 rows in set (0.00 sec)

//删除主键索引
mysql> alter table test11 drop primary key;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

//删除其他索引
mysql> alter table test11 drop index email;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test11 drop index name;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

//查看索引
mysql> show index from test11\G
Empty set (0.00 sec)

修改索引

先删除，再创建吧，其实删除索引也可以用修改字段实现

创建索引的原则

较频繁的作为查询条件的字段应该创建索引

select * from emp where empno=1

唯一性太差（重复太多）的字段不适合单独创建索引，即使频繁作为查询条件

select * from emp where sec=‘男’

更新非常频繁的字段不适合创建索引

select * from emp where logincount=1

不会出现在where子句中的字段不该创建索引

第一句的反写