【MySQL】-【索引的创建与设计原则】

索引的声明与使用

索引的分类

MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。没有空间索引是因为有的存储引擎不支持空间索引

1. 从功能逻辑上说索引主要有 4 种分别是普通索引、唯一索引、主键索引、全文索引。
2. 按照物理实现方式索引可以分为 2 种聚簇索引和非聚簇索引。
3. 按照作用字段个数进行划分分成单列索引和联合索引。

唯一性索引与唯一性约束是一一对应关系你给某个字段添加了唯一性约束它会自动生成唯一性索引你给某个字段添加了唯一性索引他会自动具备唯一性约束
主键约束上自动就会有主键索引主键索引就是聚簇索引一张表里最多只能有一个主键索引因为一张表中最多只能有一个主键

创建索引

创建表的时候创建索引

一、隐式的方式创建索引。在声明有主键约束、唯一性约束、外键约束的字段上会自动的添加相关的索引

CREATE DATABASE dbtest2;

USE dbtest2;

CREATE TABLE dept(
dept_id INT PRIMARY KEY AUTO_INCREMENT,
dept_name VARCHAR(20)
);

CREATE TABLE emp(
emp_id INT PRIMARY KEY AUTO_INCREMENT,
emp_name VARCHAR(20) UNIQUE,
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id)
);

二、显式的方式创建
语法

CREATE TABLE table_name [col_name data_type] [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC |
DESC]
# 1. UNIQUE、FULLTEXT、SPATIAL为可选参数分别表示唯一索引、全文索引和空间索引
#2. INDEX与KEY的作用相同用来指定创建索引INDEX较常用
#3. index_name指定索引的名称给索引起别名为可选参数如果不指定那么MySQL默认col_name为索引名
#4. col_name为需要创建索引的字段列该列必须从数据表中定义的多个列中选择
#5. length为可选参数表示索引的长度只有字符串类型的字段才能指定索引长度
#6. ASC或DESC指定升序或者降序的索引值存储。

1. 创建普通的索引

CREATE TABLE book(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
INDEX idx_bname(book_name)
);

通过命令查看索引

#方式1在命令行中
SHOW CREATE TABLE book;

#方式2在软件中
SHOW INDEX FROM book;

性能分析工具EXPLAIN

EXPLAIN SELECT * FROM book WHERE book_name = 'mysql高级';

2. 创建唯一索引声明有唯一索引的字段在添加数据时要保证唯一性但是可以添加nullnull可以多次添加

CREATE TABLE book1(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
UNIQUE INDEX uk_idx_cmt(COMMENT)
);

SHOW INDEX FROM book1;

INSERT INTO book1(book_id,book_name,COMMENT)
VALUES(1,'Mysql高级','适合有数据库开发经验的人员学习');

INSERT INTO book1(book_id,book_name,COMMENT)
VALUES(2,'Mysql高级',NULL);

SELECT * FROM book1;

3. 主键索引
   1通过定义主键约束的方式定义主键索引

CREATE TABLE book2(
book_id INT PRIMARY KEY ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);
SHOW INDEX FROM book2;

2通过删除主键约束的方式删除主键索引ALTER TABLE book2 DROP PRIMARY KEY;

4. 创建单列索引
   我们前面定义的都是单列索引单列索引的Seq_in_index都为1
   
5. 创建联合索引

CREATE TABLE book4(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR,
#声明索引
INDEX mul_bid_bname_info(book_id,book_name,info)
);

SHOW INDEX FROM book4;

分析我们按照上面建了索引显然二叉树是根据book_id排序的因为book_id是第一个字段那么在查询中只要使用了book_id作为限定条件就会走索引

EXPLAIN SELECT * FROM book4 WHERE book_id = 1001 AND book_name = 'mysql';

没有使用book_id查询不会走索引

EXPLAIN SELECT * FROM book4 WHERE book_name = 'mysql';

6. 创建全文索引

CREATE TABLE test4(
id INT NOT NULL,
NAME CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX futxt_idx_info(info(50))# 建立索引时字段可能比较长我们可以取前50个字符
)

CREATE TABLE `papers` (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
title varchar(200) DEFAULT NULL,
content text,
PRIMARY KEY (id),
FULLTEXT KEY title (title,content)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
# 在MySQL5.7及之后版本中可以不指定最后的ENGINE了因为在此版本中InnoDB支持全文索引

以这种方式创建的索引在查询时建议使用match+against方式查询SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);取代SELECT * FROM papers WHERE content LIKE ‘%查询字符串%’;前者比后者快N倍但是可能存在精度问题

如果需要全文索引的是大量数据建议先添加数据再创建索引。因为不断加数据的时候索引也会跟着变添加数据比较慢

7. 空间索引创建中要求空间类型的字段必须为非空 。
   举例创建表test5在空间类型为GEOMETRY的字段上创建空间索引SQL语句如下

CREATE TABLE test5(
geo GEOMETRY NOT NULL,
SPATIAL INDEX spa_idx_geo(geo)
) ENGINE=MyISAM;

在已经存在的表上创建索引

可以使用ALTER TABLE语句或者CREATE INDEX语句。
一、ALTER TABLE
语法ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length],...) [ASC | DESC]

CREATE TABLE book5(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

SHOW INDEX FROM book5;

ALTER TABLE book5 ADD INDEX idx_cmt(COMMENT);
ALTER TABLE book5 ADD UNIQUE uk_idx_bname(book_name);
ALTER TABLE book5 ADD INDEX mul_bid_bname_info(book_id,book_name,info);

二、CREATE INDEX
语法CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC | DESC]

CREATE TABLE book6(
book_id INT ,
book_name VARCHAR(100),
AUTHORS VARCHAR(100),
info VARCHAR(100) ,
COMMENT VARCHAR(100),
year_publication YEAR
);

SHOW INDEX FROM book6;

CREATE INDEX idx_cmt ON book6(COMMENT);
CREATE UNIQUE INDEX  uk_idx_bname ON book6(book_name);
CREATE INDEX mul_bid_bname_info ON book6(book_id,book_name,info);