MySQL 索引

Mysql官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构，提取句子主干，就可以得到索引的本质；

索引是什么：‘你可以简单理解为‘排好序的快速查找数据结构’

如果要查询23，在左边数据表，要一个一个对比，查询很多次才能查到；

右边（二叉树）可以看做是索引的一种方式：查询第一个比大小，小了往左找，再比大小，大了往右边找，找三次就能找到。

0、mysql 索引结构

1、Btree索引

原理图
1. 有数据
2. 有向下的指针
3. 有指向数据的指针（红色小方块）

关于时间复杂度

2、B+Tree（MySQL选择这个）

原理图
1. 有数据
2. 向下的指针

关于时间复杂度

1、索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引（PRIMARY KEY）
- 唯一的标识，主键不可重复，只能有一列作为主键
唯一索引（UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
```
--创建唯一索引
CREATE UNIQUE INDEX idx_empnum ON t_emp(empnums)
```
常规索引（KEY/INDEX）
- 默认的，index / key关键字来设置
全文索引（FullText）
- 在特定的数据库引擎下才有，MylSAM
- 快速定位数据

复合索引,即一个索引包含多列（）

-- 新建复合索引，根据多个字段建立索引
CREATE INDEX idx_age_deptid_name ON t_emp(age,deptid,NAME);

1.1、索引的优缺点

优点

类似大学图书馆键书目索引，提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

缺点

虽然索引大大提高了查询速度，同时却会 降低更新表的速度，如对表进行INSERT、UPDATE 和 DELETE。

因为更新表时，MYSQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要 占用空间 的

1.2、索引的使用

1. 在创建表的时候给字段增加索引
2. 创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student
    
-- 增加一个全文索引
--                                          索引名↓     +    列名↓
ALTER TABLE `student` ADD FULLTEXT INDEX `studentName`（`studentName`）;


-- EXPLAIN 关键字； 分析SQL执行的状况
EXPLAIN SELECT * FROM student ；

2、测试索引

2.1用sql语句插入100万条数据

DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION mock_data()
RETURNS INT
BREGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	
	WHERE i < num DO
		-- 插入语句
		INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
		VALUES (
        CONCAT ('用户'1),
		'123456789@qq.com',
		CONCAT(`18`,FLOOR(RAND()*((999999999 - 100000000) + 100000000))),
		FLOOR(RAND() * 2),
		UUID(),
		FLOOR(RAND() * 100)
        );
		
		SET i = i + 1;
	
	END WHILE;
	return i;

END;

2.2、查询用户名为‘用户9999’

SELECT * FROM app_user WHERE name = '用户9999'  -- 1.098 sec

2.3、分析sql的执行情况

EXPLAIN SELECT * FROM app_user WHERE name = '用户9999' ;
-- 下图执行结果，rows = 991749，执行99万行才找到它

2.4、创建索引

-- id_表名_字段名
-- CREATE INDEX 索引名 on 表(字段)
CREATE INDEX id_app_user_name ON app_user('name');
CREATE INDEX idx_phone ON t_emp(PHONE);

2.5、再次执行查询并分析

查询

SELECT * FROM app_user WHERE name = '用户9999'  -- 0.001 sec

分析

EXPLAIN SELECT * FROM app_user WHERE name = '用户9999' ;
-- 下图执行结果，rows = 1，执行1行即找到它

设计表--> 查看索引

索引在小数据量的时候，用处不大；但是在大数据的时候，区分十分明显!

3、索引原则

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表不需要加索引
索引一般加在常用来查询的字段上！

索引的数据结构

3.1 、哪些情况需要创建索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引，即WHERE后的查询条件
查询中与其它表关联的字段，外键关系建立索引
单键 / 组合索引的选择问题，组合索引性价比更高
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度，即Order By 后面的字段
查询中统计或者分组字段即 group by 后面的字段

3.2、哪些情况不要创建索引

表记录太少
经常增删改的表或者字段（比如电商平台的用户余额字段，一直在变，不要加索引）
Where条件里用不到的字段不创建索引
过滤性不好的不适合建索引（比如性别，只有男女不知道，三个选项。没必要）

4、SQL性能分析（如何建索引，那把尺子，在这个章节！！！）

posted on 2021-03-14 15:19 KAI_D 阅读(66) 评论(0) 收藏举报