命令记不住

登陆

MySQL 索引操作

  |   129 浏览

MySQL索引操作

1.索引概述

提供了类似于书中目录的作用,目的是为了优化查询
索引就好比一本书的目录，它会让你更快的找到内容，显然目录（索引）并不是越多越好，假如这本书1000页，有500也是目录，它当然效率低，目录是要占纸张的,而索引是要占磁盘空间的。

2.索引分类

  • 普通索引INDEX: 最基本的索引，没有任何限制
  • 唯一索引UNIQUE: 与"普通索引"类似，不同的是索引列的值必须唯一，但允许有空值。
  • 全文索引FULLTEXT：仅可用于 MyISAM - 表，针对较大的数据，生成全文索引很耗时好空间。
  • 主键索引PRIMARY KEY:它 是一种特殊的唯一索引，不允许有空值。
1.算法分类
  • B树索引
  • Hash索引
  • R树
  • Full text
  • GIS
2.B树基于不同的查找算法分类
B-tree 
在范围查询方面提供了更好的性能(> < >= <=)
B+Tree   
B*Tree
3.在功能上的分类

辅助索引(S)怎么构建B树结构的?

  • 索引是基于表中,列(索引键)的值生成的B树结构
  • 首先提取此列所有的值,进行自动排序
  • 将排好序的值,均匀的分布到索引树的叶子节点中(16K)
  • 然后生成此索引键值所对应得后端数据页的指针
  • 生成枝节点和根节点,根据数据量级和索引键长度,生成合适的索引树高度
id  name  age  gender
select  *  from  t1 where id=10;
问题: 基于索引键做where查询,对于id列是顺序IO,但是对于其他列的查询,可能是随机IO.

聚集索引(C)

1 前提

  • (1)表中设置了主键,主键列就会自动被作为聚集索引.
  • (2)如果没有主键,会选择唯一键作为聚集索引.
  • (3)聚集索引必须在建表时才有意义,一般是表的无关列(ID)

2 辅助索引(S)怎么构建B树结构的?

  • (1) 在建表时,设置了主键列(ID)
  • (2) 在将来录入数据时,就会按照ID列的顺序存储到磁盘上.(我们又称之为聚集索引组织表)
  • (3) 将排好序的整行数据,生成叶子节点.可以理解为,磁盘的数据页就是叶子节点
4.辅助索引细分
  • 1.普通的单列辅助索引
  • 2.覆盖索引(联合索引)

多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询

  • 3.唯一索引:
    索引列的值都是唯一的.
5.关于索引树的高度受什么影响
    1. 数据量级, 解决方法:分表,分库,分布式
    1. 索引列值过长 , 解决方法:前缀索引
    1. 数据类型:
变长长度字符串,使用了char,解决方案:变长字符串使用varchar
enum类型的使用enum ('山东','河北','黑龙江','吉林','辽宁','陕西'......)

3.索引基本管理

索引建立前

db01 [world]>desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

key   :有没有索引,索引类型
	   PRI: 主键索引
	   UNI: 唯一索引 
	   MUL: 辅助索引(单列,联和,前缀)

单列普通辅助索引
创建索引：

db01 [world]>alter table city add index idx_name(name);
db01 [world]>create index idx_name1 on city(name);

db01 [world]>show index from city;

注意:以上操作不代表生产操作,我们不建议在一个列上建多个索引

删除索引:

db01 [world]>alter table city drop index idx_name1;

覆盖索引(联合索引)

db01 [world]>alter table city add key idx_co_po(countrycode,population);

前缀索引

db01 [world]>alter table city add index idx_di(district(5));

唯一索引

db01 [world]>alter table city add unique index idx_uni1(name);
ERROR 1062 (23000): Duplicate entry 'San Jose' for key 'idx_uni1'

思考

统计city表中，以省的名字为分组，统计组的个数

select district,count(id) from city group by district;

找到world下,city表中 name列有重复值的行,最后删掉重复的行

db01 [world]>select name,count(id) as cid from city group by name  having cid>1 order by cid desc;
db01 [world]>select * from city where name='suzhou';

4.索引深度管理

准备表

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.使用存储过程(函数), 批量插入数据

//创建存储过程
mysql> delimiter $$
mysql> create procedure autoinsert()
BEGIN
declare i int default 1;
while (i<200000)do
insert into bgx.t5 values(i,'bgx');
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 bgx.t5 values(i,'bgx');
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();

5.索引创建

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.创建唯一索引示例
CREATE TABLE tt (
id INT,
name VARCHAR(30) ,
comment VARCHAR(50),
'UNIQUE INDEX' index_tt_name (name) );

//3.创建全文索引示例myisam
CREATE TABLE tt (
id INT,
name VARCHAR(30) ,
comment VARCHAR(50),
log text,
'FULLTEXT INDEX' index_tt_log (log);

//3.创建多列索引示例
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' index_name ON product(name);

//3.创建全文索引示例
CREATE 'FULLTEXT INDEX' index_dept_name ON product (name);

//4.创建多列索引示例
CREATE 'INDEX index_dept_name_comment' ON product (name, id);

6.索引测试

1.未建立索引

//花费时长
mysql> select * from t5 where id=199999;
+--------+------+
| id     | name |
+--------+------+
| 199999 | bgx  |
+--------+------+
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)

2.建立索引

//对id字段进行索引创建
mysql> create index index_t5_id on bgx.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 | bgx  |
+--------+------+
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)

7.删除索引

1.查看索引

SHOW CRETAE TABLE 表名\G
EXPLAIN SELECT * FROM t5 WHERE id='19999';

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
推荐阅读