数据库索引
索引概述
索引就好⽐⼀本书的⽬录，它会让你更快的找到内容，显然⽬录（索引）并不是越多越好，假如这本书1000⻚，有
500⻚也是⽬录，它当然效率低，⽬录是要占纸张的,⽽索引是要占磁盘空间的。
索引分类
普通索引 INDEX: 最基本的索引，没有任何限制
唯⼀索引 UNIQUE: 与"普通索引"类似，不同的是索引列的值必须唯⼀，但允许有空值。
全⽂索引 FULLTEXT：仅可⽤于 MyISAM 表，针对较⼤的数据，⽣成全⽂索引很耗时好空间。
主键索引 PRIMARY KEY:它是⼀种特殊的唯⼀索引，不允许有空值。
# 索引环境
1、创建一个表
mysql> create table t5 (id int, name varchar(30));
Query OK, 0 rows affected (0.02 sec)
2、使用存储过程（函数），批量插入数据
# 创建存储过程
mysql> delimiter $$
mysql> create procedure autoinsert()
-> BEGIN
-> declare i int default 1;
-> while (i<200000)do
-> insert into test.t5 values(i,'wing');
-> set i = i+1;
-> end while;
-> END $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
# 查看存储过程
mysql> show procedure status\G
mysql> show create procedure autoinsert\G
*************************** 1. row ***************************
Procedure: autoinsert
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `autoinsert`()
BEGIN
declare i int default 1;
while (i<200000)do
insert into test.t5 values(i,'wing');
set i = i+1;
end while;
END
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
# 调用存储过程
mysql> call autoinsert();
# 索引创建
语法：CREATE TABLE 表名 (字段名 数据类型 [完整性约束条件…],[UNIQUE | FULLTEXT | SPATIAL ] INDEX
| KEY [索引名] (字段名[(⻓度)] [ASC |DESC]));
//1.创建普通索引示例
CREATE TABLE tt (
id INT,
name VARCHAR(30),
comment VARCHAR(50),
INDEX index_tt_name (name)
);
//2.创建唯⼀索引示例
drop table tt;
CREATE TABLE tt (
id INT,
name VARCHAR(30) ,
comment VARCHAR(50),
UNIQUE INDEX index_tt_name (name)
);
mysql> show create table tt;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tt | CREATE TABLE `tt` (
`id` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
`comment` varchar(50) DEFAULT NULL,
UNIQUE KEY `index_tt_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
//创建全⽂索引示例myisam
drop table tt;
CREATE TABLE tt (
id INT,
name VARCHAR(30) ,
comment VARCHAR(50),
log text,
FULLTEXT INDEX index_tt_log (log)
);
//创建多列索引示例
drop table tt;
CREATE TABLE tt (
id INT,
name VARCHAR(30) ,
comment VARCHAR(50),
INDEX index_tt_name_comment (name, comment)
);
# 在已存在的表上创建索引
语法:CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(⻓度)] [ASC |DESC]);
//1.创建普通索引示例
CREATE INDEX index_name ON product(name);
//2.创建唯⼀索引示例
CREATE UNIQUE INDEX unique_index_name ON product(name);
//3.创建全⽂索引示例
CREATE FULLTEXT INDEX full_index_dept_name ON product (name);
//4.创建多列索引示例
CREATE INDEX index_dept_name_id ON product (name, id);
# 索引测试
//花费时常
mysql> select * from t5 where id='199999';
+--------+------+
| id | name |
+--------+------+
| 199999 | wing |
+--------+------+
1 row in set (0.09 sec)
//explain查看查询优化器如何决定执⾏查询
mysql> explain select * from t5 where id=199999\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 199819
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
SELECT 查询的执⾏计划。它告诉我们查询的执⾏⽅式和相关的统计信息。以下是每个列的含义：
id : 查询中每个 SELECT ⼦句的唯⼀标识符。
select_type : 查询的类型。在这个例⼦中，查询类型为 SIMPLE ，表示这是⼀个简单的 SELECT 查询，不
包含⼦查询或联接。
table : 正在访问的表。
partitions : 匹配的分区。在这个例⼦中，分区为 NULL ，表示没有使⽤分区。
type : 访问表的⽅式。在这个例⼦中， type 为 ALL ，这意味着 MySQL 将扫描整个表来查找匹配的⾏。
possible_keys : 可能使⽤的索引列表。在这个例⼦中，没有使⽤索引，因此 possible_keys 为 NULL 。
key : 实际使⽤的索引。在这个例⼦中，没有使⽤索引，因此 key 为 NULL 。
key_len : 使⽤的索引的⻓度。在这个例⼦中，没有使⽤索引，因此 key_len 为 NULL 。
ref : 列与索引之间的匹配条件。在这个例⼦中，没有使⽤索引，因此 ref 为 NULL 。
rows : MySQL 估计必须扫描的⾏数。在这个例⼦中，MySQL 估计必须扫描 199949 ⾏才能找到匹配的⾏。
filtered : MySQL 扫描表后过滤⾏的百分⽐。在这个例⼦中，MySQL 扫描了整个表，但只返回了 10% 的
⾏，因此 filtered 为 10.00 。
Extra : 其他信息。在这个例⼦中，使⽤了 WHERE ⼦句来过滤⾏，因此 Extra 为 Using where 。
执⾏计划告诉我们这个查询没有使⽤索引，因此可能会⽐较慢。如果表中有许多⾏，这个查询可能会变得⾮
常缓慢。如果你想让这个查询更快，可以考虑向 id 列添加⼀个索引。
//对id字段进⾏索引创建
mysql> create index index_t5_id on test.t5(id);
Query OK, 0 rows affected (0.68 sec)
Records: 0 Duplicates: 0 Warnings: 0
//索引后花费时⻓
mysql> explain select * from t5 where id=199999\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t5
partitions: NULL
type: ref
possible_keys: index_t5_id
key: index_t5_id
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
table : 查询的表名，这⾥是 t5 。
partitions : 如果查询使⽤了分区表，这⾥会列出使⽤的分区。
type : 访问表的⽅式，这⾥是 ref ，表示使⽤了索引查找。
possible_keys : 可能使⽤的索引，这⾥是 index_t5_id 。
key : 实际使⽤的索引，这⾥也是 index_t5_id 。
key_len : 使⽤的索引的⻓度，这⾥是 5 ，表示使⽤了⼀个 5 字节的索引。
rows : 表示查询扫描的⾏数，这⾥是 1 ，表示只扫描了⼀⾏。
这个查询使⽤了 index_t5_id 索引，只扫描了⼀⾏，性能⽐较好。
# 索引管理
//查看索引
mysql> show create table t5;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| t5 | CREATE TABLE `t5` (
`id` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
KEY `index_t5_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> explain select * from t5 where id='199999';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t5 | NULL | ref | index_t5_id | index_t5_id | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
# 删除索引
//查看索引名称
mysql> show create table t5\G
*************************** 1. row ***************************
Table: t5
Create Table: CREATE TABLE `t5` (
`id` int(11) DEFAULT NULL,
`name` varchar(30) DEFAULT NULL,
KEY `index_t5_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
//删除索引语法: DROP INDEX 索引名 ON 表名
mysql> drop index index_t5_id on t5;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
浙公网安备 33010602011771号