9.索引
本章目标
- 索引简介
- 创建索引
本章内容
索引（Index）是帮助MySQL高效获取数据的数据结构（排好序）
就好比字典的目录
一、 索引简介
1、什么是索引
索引是将关键字数据以某种数据结构的方式存储到外存，用于提升数据的检索性能；索引既有逻辑上的概念，更是一种物理存储方式，且事实存在、需要耗费一定的储存空间。 经常使用表中的某一列或某几列为条件进行查询，数据量比较大时，可以创建索引，以提高查询的速度。
索引的原理大致概括为以空间换时间
2、索引优缺点
2.1、优点:
- 大大加快数据的检索速度（大大减少检索的数据量）——-创建索引的最主要因素
- 通过建立唯一索引，保证数据的唯一性
- 帮助服务器避免排序和临时表
- 随机I/O变成顺序I/O
- 可以加速表和表质检的连接，特别是实现数据完整性方面特别有意义
2.2、缺点：
- 创建索引和维护索引需要很多时间，这种时间随着数据量的增加而增加。
- 如果一个数据建立了索引，那么增删改这个数据，相应的索引也要进行动态修改，这将大大降低sql的执行效率。
- 需要占用物理存储空间：索引需要使用物理文件存储，会耗费一定空间。
3、 索引关键字的选取原则
- 表的某个字段值离散度越高，该字段越适合选作索引的关键字。
- 占用储存空间少的字段更适合选作索引的关键字。
- 较频繁地作为where查询条件的字段应该创建索引，分组字段或者排序字段应该创建索引，两个表的连接字段应该创建索引。
- 更新频繁的字段不适合创建索引，不会出现在where子句中的字段不应该创建索引。
- 最左前缀原则
- 尽量使用前缀索引
4、索引分类
| 类别 | 详情 |
|---|---|
| 按数据结构分类 | B+tree索引、Hash索引、Full-text索引 |
| 按物理存储分类 | 聚集索引、非聚集索引（也叫二级索引、辅助索引） |
| 按字段特性分类 | 主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT) |
| 按字段个数分类 | 单列索引、联合索引（也叫复合索引、组合索引） |
5、 查看索引
Show index|keys from 表名；
SHOW KEYS FROM table_name;
二、 创建索引
基础数据：
--首先创建表
drop table if EXISTS student;
CREATE TABLE `student` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '用户名称',
`email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '邮箱',
`phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '电话',
`gender` tinyint unsigned NOT NULL COMMENT '1:男; 0:女',
`password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '密码',
`age` tinyint DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='学生表';
-- 1. 先执行 删除函数语句
DROP FUNCTION IF EXISTS mock_data;
-- 2.解决异常
-- This function has none of DETERMINISTIC, NO SQL
SET GLOBAL log_bin_trust_function_creators = 1;
-- 3. 然后执行 创建函数
-- 插入100万条数据
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num int default 10;
DECLARE i int default 0;
WHILE i<num DO
INSERT INTO `student`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'151303131@qq.com',concat('18',floor(RAND()* 999999999)),floor(RAND()* 2),uuid(),floor(RAND()* 100));
SET i = i+1;
END WHILE;
RETURN i;
end;
-- 4.最后调用创建好的函数
select mock_data();
数据不能太少，起步50万
1、 普通索引（单列索引）：
单列索引是最基本的索引，它没有任何限制。 直接创建索引
CREATE INDEX index_name ON table_name(col_name);
修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name(col_name);
创建表的时候同时创建索引
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`name` varchar(20) NOT NULL ,
PRIMARY KEY (`id`),
INDEX index_name (name)
)
删除索引
DROP INDEX index_name ON table_name;
或者
alter table `表名` drop index 索引名;
2、 复合索引：
复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。
创建一个复合索引
create index index_name on table_name(col_name1,col_name2,...);
修改表结构的方式添加索引
alter table table_name add index index_name(col_name,col_name2,..);
基于(列Ａ，列Ｂ)两列创建索引：
- 可应用索引的情况：A、AB、BA两列结合;
- 不应用索引的情况：Ｂ
-
- 创建索引
create index in_student on student(name,phone);
-- 索引有效
select * from student where name=‘用户499999’ and phone=‘18280215566’
-- 索引有效
select * from student where name=‘用户499999’
-- 索引无效
select * from student where phone=‘18280215566’
- 创建索引
-
3、 唯一索引：
唯一索引和普通索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许存在空值。
创建单个索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
创建多个索引
CREATE UNIQUE INDEX index_name on table_name(col_name,...);
修改表结构
单个
ALTER TABLE table_name ADD UNIQUE index index_name(col_name);
多个
ALTER TABLE table_name ADD UNIQUE index index_name(col_name,...);
如果在已经有数据的表上添加唯一性索引的话： 如果添加索引的列的值存在两个或者两个以上的空值，则不能创建唯一性索引会失败。（一般在创建表的时候，要对自动设置唯一性索引，需要在字段上加上 not null） 如果添加索引的列的值存在两个或者两个以上的null值，还是可以创建唯一性索引，只是后面创建的数据不能再插入null值 ，并且严格意义上此列并不是唯一的，因为存在多个null值。
4、 主键索引
主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：
创建表时添加
CREATE TABLE `news` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` varchar(255) NOT NULL ,
`content` varchar(255) NULL ,
`time` varchar(20) NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
创建表后添加
alter table tbl_name add primary key(col_name);
5、 删除索引
DROP INDEX 索引名 ON 表名；
通过主键约束自动添加的主键索引不能用DROP INDEX语句删除，但通过唯一约束自动创建的唯一索引可以这么删除，但是删除后唯一约束也被删除了。
6、 索引失效
- 在where后使用or，导致索引失效（尽量少用or）
- 使用like ，like查询是以%开头
- 复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用
- 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
- 使用in导致索引失效
- DATE_FORMAT()格式化时间，格式化后的时间再去比较，可能会导致索引失效。
- 对于order by、group by 、 union、 distinc 中的字段出现在where条件中时，才会利用索引！
有效：
SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';
无效：
SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;
7、 全文检索(了解)
在一般情况下，模糊查询都是通过 like 的方式进行查询。但是，对于海量数据，这并不是一个好办法，在 like “value%” 可以使用索引，但是对于 like “%value%” 这样的方式，执行全表查询，这在数据量小的表，不存在性能问题，但是对于海量数据，全表扫描是非常可怕的事情,所以 like 进行模糊匹配性能很差。
全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较
fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。
CREATE FULLTEXT INDEX index_fulltext ON table_name(col_name)
目前只有char、varchar，text 列上可以创建全文索引
fulltext索引配合match against操作使用，而不是一般的where语句加like。
SELECT * FROM tablename WHERE MATCH(column1) AGAINST('中国')
这条命令将把column1字段里有中国的数据记录全部查询出来，
注：被匹配的必须是一个完整的单词，比如“中国 人民”，可以被匹配，如果是“中国人”则匹配不到
完整例子：
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
) ENGINE=InnoDB;
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
SELECT * FROM articles WHERE MATCH(title,body) AGAINST('MySQL');
MySQL是一个独立的单词才可以
mysql 默认全文索引的最小单词是四个字符
可以通过ft_min_word_len或者innodb_ft_min_token_size设置，配置在文件/etc/my.cnf中 SHOW GLOBAL VARIABLES LIKE ‘%ft_m%’”查询设置的结果是否生效
本文来自博客园，作者：icui4cu，转载请注明原文链接：https://www.cnblogs.com/icui4cu/p/18818225
浙公网安备 33010602011771号