一、Mysql的索引

（1）什么是索引

一般的应用系统中，读写比例一般在10：1，读是比写要多得多的，而且插入操作和一般的更新操作很少出现性能问题。

在实际环境中，工作人员遇到最多的，也是最容易出现问题的还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重，而索引的出现就是加快查询速度

（2）索引的作用

索引可以加快客户端的查询数据的速度
索引在Mysql中也叫做”键“，是存储引擎用于快速找到记录的一种数据结构

当表中的数据量越来越大时，索引对于性能的影响越来越重要，并且索引优化是对查询性能优化最有效的手段，可以轻而易举的将查询性能提升好几个度

可以把索引看作是字典中的音序表，想要查某个字时，如果不使用音序表，就需要把几百页的字典诶个翻一遍，而有了音序表就可以快速进行定位，从而找到指定的字

（3）索引原理

索引的目的就是提高查询效率，跟使用字典查询某个字一样，都是先看音序表或者偏旁表，然后定位到指定的章节，然后在较少的页数中寻找指定的字即可，不需要从几百页中诶个寻找

索引就是通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说有了这种索引机制，我们可以总是可以使用同一种查找方式来锁定数据

（4）磁盘IO与预读

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，会把当前磁盘地址的数据和相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到

每一次IO读取的数据称之为一页（page），具体一页有多大数据和操作系统有关，一般为4K或8K，也就是我们读取一页内的数据的时候，实际上才发生的一次IO读取，这个理论对于索引的数据结构设计非常有帮助

（5）索引的数据结构—B+树

任何一种数据结构都不是凭空产生的，每一种数据结构都会有他的背景和使用场景，而数据结构的作用就是每次查找数据的时候把磁盘IO读取的次数控制到一个很小的数量，最好是常数数量，那么如果一个高度可控的多路搜索树是否能满足要求呢，B+树就这样诞生了

-B+树的查找过程

可以看到当用户查找数据时，索引会由大到小缩小范围，最后成功找到数据

-B+树的性质

索引字段要尽量的小
索引的最左匹配特性（从左往右匹配）

（6）索引分类

索引总共分为5类：

普通索引 index：加速查找
唯一索引：

主键索引：primary key，加速查找+约束，不为空并且数据有唯一性

唯一索引：unique，加速查找+约束，唯一性

联合索引：

primary key（id，name）：联合主键索引

unique（id，name）：联合唯一索引

index（id，name）：联合普通索引

全文索引fulltext：用于搜索很长的一篇文章时使用，效果最好
空间索引spatial：不常用

（7）索引的两大类型

在创建上面的索引时，为其指定索引类型，主要分为两类：

hash类型的索引： 查询单条快，范围查询慢

b+树类型的索引： 层数越多，数据量指数级增长，innoDB默认支持b+树

不同的存储引擎支持的索引类型是不一样的

存储引擎	支持的索引类型
InnoDB	支持事务，支持行级别锁定，支持B+树、Full-text等索引，不支持hash索引

MyISAM	不支持事务，支持表级别锁定，支持B+树、Full-text等索引，不支持hash索引
Memory	不支持事务，支持表级别锁定，支持B+树、hash等索引，不支持Full-text索引

NDB	支持事务，支持行级别锁定，支持hash索引，不支持B+树、Full-text等索引
Archive	不支持事务，支持标记别锁定，不支持B+树、hash、Full-text等索引

（8）创建、删除索引

-创建索引

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

[root@rzy ~]# mysql -u root -p123123  #进入数据库
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (58.38 sec)
mysql> create database aaa;  #创建aaa库
Query OK, 1 row affected (0.00 sec) 
mysql> use aaa;  #进入（选中）aaa库
Database changed
mysql> create table aaa(id int primary key,name char(10),age int);  #id为主键索引，写到项里
Query OK, 0 rows affected (0.14 sec)
mysql> create table bbb(id int ,name char(10),age int,index(id));  #id为普通索引，写到最后
Query OK, 0 rows affected (0.10 sec)

方法二： 在已经创建的表上创建索引

mysql> show tables;  #查看所有的表
+---------------+
| Tables_in_aaa |
+---------------+
| aaa           |
| bbb           |
+---------------+
2 rows in set (0.00 sec)
mysql> drop table aaa; #删除两个表
Query OK, 0 rows affected (0.00 sec)
mysql> drop table bbb;
Query OK, 0 rows affected (0.00 sec)
mysql> create table aaa(id int,name char(10),age int); #创建一个新的表，这次不指定索引
Query OK, 0 rows affected (0.01 sec)
mysql> create index a1 on aaa(id); #创建普通索引，索引名称为a1，为aaa表的id项创建索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> create index a2 on aaa(id,name);  #创建index普通联合索引，索引的是aaa表的id项和name项，索引名称是a2
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

方法三： 使用alter在已经存在的表上创建索引

mysql> alter table aaa add unique index(age);  #创建unique唯一索引，索引的是aaa表的age项
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table aaa add primary key(name); #创建primary主键索引，索引的是aaa表的name项
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

-查看所有索引

mysql> show index from aaa\G;  #查看aaa表的所有索引
*************************** 1. row ***************************
        Table: aaa  #索引的表
   Non_unique: 0
     Key_name: PRIMARY  #索引名称
 Seq_in_index: 1
  Column_name: name  #索引的项
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: aaa
   Non_unique: 0
     Key_name: age
 Seq_in_index: 1
  Column_name: age
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: aaa
   Non_unique: 1
     Key_name: a1
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 4. row ***************************
        Table: aaa
   Non_unique: 1
     Key_name: a2
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 5. row ***************************
        Table: aaa
   Non_unique: 1
     Key_name: a2
 Seq_in_index: 2
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
5 rows in set (0.00 sec)
ERROR: 
No query specified

-删除索引

mysql> drop index a1 on aaa;  #删除aaa表的索引a1，a1是索引名称
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> drop index a2 on aaa;  #删除aaa表的索引a2
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> drop index age on aaa; #删除aaa表的索引age
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from aaa\G;
*************************** 1. row ***************************
        Table: aaa
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)
ERROR: 
No query specified
mysql> alter table aaa drop primary key; #删除主键索引
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from aaa\G; #再次查看发现没有索引了
Empty set (0.00 sec)
ERROR: 
No query specified

（9）测试索引

******（1）准备表
mysql> drop table aaa; #删除刚才的表
Query OK, 0 rows affected (0.00 sec)
mysql> create table aaa(id int,name char(10),age int); #创建一个新表
Query OK, 0 rows affected (0.00 sec)
mysql> show tables ;  #查看所有表
+---------------+
| Tables_in_aaa |
+---------------+
| aaa           |
+---------------+
1 row in set (0.00 sec)
******（2）插入数据
mysql> delimiter $$ #定义结束符号为$$，不定义的话，下面定义存储过程是无法写；的
mysql> create procedure a1()  #定义存储过程，有点像python的函数
    -> BEGIN
    -> declare i int default 1;  #相当于i=1
    -> while(i<3000000)do #使用while循环 
    -> insert into aaa values(i,concat('egon',i),i); #利用循环插入数据
    -> set i=i+1; 
    -> END while;
    -> END$$
Query OK, 0 rows affected (0.19 sec)
mysql> delimiter ; #把结束符变回;
mysql> show create procedure a1\G; #查看存储过程
*************************** 1. row ***************************
           Procedure: a1
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `a1`()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into aaa values(i,concat('egon',i),i);
set i=i+1;
END while;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)
ERROR: 
No query specified
mysql> call a1(); #调用存储过程
mysql> select * from aaa;
。。。。。。
mysql> select * from aaa where id = 123456; #查询指定的id为123456的。可以看到花费了0.27秒
+--------+------------+--------+
| id     | name       | age    |
+--------+------------+--------+
| 123456 | egon123456 | 123456 |
+--------+------------+--------+
2 rows in set (0.27 sec)
******（3）加上索引进行查询，查询id，就给id加上索引
mysql> create index aaa on aaa(id);  #给aaa表的id项加上普通索引，名称为aaa
Query OK, 0 rows affected (1.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> select * from aaa where id = 123456; #再次查看，可以看到花费0.00秒，加快了查询速度
+--------+------------+--------+
| id     | name       | age    |
+--------+------------+--------+
| 123456 | egon123456 | 123456 |
| 123456 | i          | 123456 |
+--------+------------+--------+
2 rows in set (0.00 sec)

Mysql的索引、视图、触发器、存储过程（上）

一、Mysql的索引

（1）什么是索引

（2）索引的作用

（3）索引原理

（4）磁盘IO与预读

（5）索引的数据结构—B+树

（6）索引分类

（7）索引的两大类型

（8）创建、删除索引

-创建索引

-查看所有索引

-删除索引

（9）测试索引

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像