2023.6.13 05.数据库索引操作

05.数据库索引操作 1.索引概述 2.索引分类 3.索引环境 4.索引创建 5.索引测试 6.索引管理   1.索引概述 索引就好⽐⼀本书的⽬录，它会让你更快的找到内容，显然⽬录（索引）并不是越多越好，假如这本书1000⻚，有 500⻚也是⽬录，它当然效率低，⽬录是要占纸张的,⽽索引是要占磁盘空间的。   2.索引分类 普通索引 INDEX: 最基本的索引，没有任何限制 唯⼀索引 UNIQUE: 与"普通索引"类似，不同的是索引列的值必须唯⼀，但允许有空值。 全⽂索引 FULLTEXT：仅可⽤于 MyISAM 表，针对较⼤的数据，⽣成全⽂索引很耗时好空间。 主键索引 PRIMARY KEY:它是⼀种特殊的唯⼀索引，不允许有空值。   3.索引环境 1.准备表 2.使⽤存储过程(函数), 批量插⼊数据 mysql> create table t5 (id int, name varchar(30)); Query OK, 0 rows affected (0.02 sec) mysql> desc t5; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(30) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) 2.使⽤存储过程(函数), 批量插⼊数据 1.创建存储过程 mysql> delimiter $$ mysql> create procedure autoinsert() BEGIN declare i int default 1; while (i<200000)do insert into wing.t5 values(i,'wing'); set i = i+1; end while; END $$ mysql> delimiter ; //2.查看存储过程 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 wing.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)   3.调⽤存储过程,执⾏ mysql> call autoinsert();   4.索引创建 1.创建表时创建索引 语法：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)   //3.创建全⽂索引示例myisam drop table tt; CREATE TABLE tt ( id INT, name VARCHAR(30) , comment VARCHAR(50), log text, FULLTEXT INDEX index_tt_log (log) ); //3.创建多列索引示例 drop table tt; CREATE TABLE tt ( id INT, name VARCHAR(30) , comment VARCHAR(50), INDEX index_tt_name_comment (name, comment) ); 2.在已存在的表上创建索引 语法: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);   5.索引测试 1.未建⽴索引 //花费时⻓ mysql> select * from t5 where id=199999; +--------+------+ | id | name | +--------+------+ | 199999 | wing | +--------+------+ 1 row in set (0.08 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: 199949 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 列添加⼀个索引 2.建⽴索引 //对id字段进⾏索引创建 mysql> create index index_t5_id on wing.t5(id); Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 //索引后花费时⻓ mysql> select * from t5 where id=199999; +--------+------+ | id | name | +--------+------+ | 199999 | wing | +--------+------+ 1 row in set (0.00 sec) //建⽴索引后, 再次查看查询优化器如何执⾏查询 mysql> explain select * from t5 where id=200000\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 索引，只扫描了⼀⾏，性能⽐较好。   6.索引管理 1.查看索引 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='19999'; +----+-------------+-------+------------+------+---------------+-------------+--------- +-------+------+----------+-------+ | 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)   2.删除索引 //查看索引名称 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.00 sec) Records: 0 Duplicates: 0 Warnings: 0