MySQL数据库索引及其数据结构

MySQL索引
1.1 什么是索引
在现实生活中，我们经常去图书馆查阅图书。
现在我们将所有图书杂乱无章的摆放在一起，那么找一本书就像大海捞针一样效率非常低。
如果我们按分类整理排序后，根据类别去找对应的图书那么效率就很高了。其实这个整理排序的过程就是索引。

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

1.2 索引的优势与劣势
1.2.1 优势
① 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的 IO 成本。
② 通过索引列对数据进行排序，降低数据排序的成本，降低 CPU 的消耗。
1.2.2 劣势
① 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
② 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
建立科学合理的规范索引
1.3 索引分类和语法
单列索引（给一列字段增加索引） id
组合索引（给多列字段增加索引） id +name
1.3.1 分类
普通索引：仅加速查询
唯一索引：加速查询 + 列值唯一（可以有null）
主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
组合（联合）索引：多列值组成一个索引，注意：最左匹配原则
1.3.2 创建索引
① 直接创建（普通、唯一）
– 创建普通索引
create index 索引名 on 表名(列名);
– 创建唯一索引
create unique index 索引名 on 表名(列名);
– 创建普通组合索引
create index 索引名 on 表名(列名1,列名2…);
– 创建唯一组合索引
create unique index 索引名 on 表名(列名1,列名2…);
② 修改表时指定
– 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table 表名 add primary key(id);
– 添加唯一索引（除了NULL外，NULL可能会出现多次）
alter table 表名 add unique(列名); – 索引名就是列名
– 添加普通索引，索引值可以出现多次。
alter table 表名 add index(列名)；-- 索引名就是列名
③ 创建表时指定【掌握】
create table student(
id int,
username varchar(32),
age int,
primary key(id), – 主键
unique(username), – 唯一
index(age) – 普通
);

1.3.3 删除索引
– 直接删除
drop index 索引名 on 表名;
– 修改表时删除 【掌握】
alter table 表名 drop index 索引名；
CREATE DATABASE web04;
USE web04;
– 创建表指定索引
CREATE TABLE student(
id INT,
username VARCHAR(32),
age INT,
PRIMARY KEY(id),
UNIQUE(username),
INDEX(age)
);
– 删除age索引
ALTER TABLE student DROP INDEX age;
​
1.3.2 索引创建原则

1. 在经常需要 搜索 的列上建索引，这样会大大加快查找速度、
   ​
2. 在经常需要 连接 的列上建索引，可以加快连接的速度。
   ​
3. 在经常需要 排序 的列上建索引，因为索引已经是排过序的，这样一来可以利用索引的排序，加快排序查询速度。
   ​

• 注意:
  那是不是在数据库表字段中尽量多建索引呢?肯定是不是的。因为索引的建立和维护都是需要耗时的

创建表时需要通过数据库去维护索引，添加记录、更新、修改时，也需要更新索引，会间接影响数据库的效率。
1.4 效果演示
1）准备千万记录

– 1. 准备表
CREATE TABLE user(
id INT,
username VARCHAR(32),
password VARCHAR(32),
sex VARCHAR(6),
email VARCHAR(50)
);

– 2. 创建存储过程，实现批量插入记录
DELIMITER $$--声明存储过程的结束符号为$$
CREATE PROCEDURE auto_insert()
BEGIN
DECLARE i INT DEFAULT 1;
START TRANSACTION; – 开启事务
WHILE(i<=10000000)DO
INSERT INTO user VALUES(i,CONCAT(‘jack’,i),MD5(i),‘male’,CONCAT(‘jack’,i,’@itcast.cn’));
SET i=i+1;
END WHILE;
COMMIT; – 提交
END$$ – 声明结束
DELIMITER ; – 重新声明分号为结束符号

– 3. 查看存储过程
SHOW CREATE PROCEDURE auto_insert;

– 4. 调用存储过程
CALL auto_insert();

2）没有索引的查询
– 根据id
select * from user where id = 1234567;
– 根据用户名
select * from user where username = ‘jack1234567’;
– 根据邮箱模糊查询
select * from user where email like ‘jack12345%’;

3）添加索引
– id设置为主键
alter table user add primary key(id);
– username设置为唯一
alter table user add unique(username);
– 邮箱设置为普通
alter table user add index(email);

4）补充
模糊匹配查询 通配符字符串 %多个任意字符
如果百分号在左侧，索引失效，全表扫描
select * from user where email like ‘%jack12345%’;
尽量避免使用or除非筛选的列也有索引
创建主键索引的时候数据库底层会创建两个临时文件夹,
创建唯一索引和普通的索引时候会创建一个临文件夹.

1.5 索引数据结构【了解】
1.5.1 介绍
我们知道索引是帮助MySQL高效获取排好序的数据结构。
索引= 排序后的数据结构
为什么使用索引后查询效率提高很多呢？接下来我们来了解下。

在没有索引的情况下我们执行一条sql语句，那么是表进行全局遍历，磁盘寻址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。
select * from user where col1=6;

为了加快的查找效率，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。
select * from user where col2=89;

1.5.2 索引数据结构
二叉树 左边子节点比父节点小，右边子节点比父节点大
红黑树（平衡二叉树） 左旋和右旋实现自平衡
Hash 散列
JDK1.7 （数组+链表）
JDK1.8 （数组+红黑树） 如果链表长度《=8
B-Tree （多路搜索平衡树）
B+Tree【MySQL使用】
1.5.3 MySQL中的B+Tree
MySQL数据库默认一次读取一页的大小（4个磁盘块）

– 查看mysql索引节点大小
show global status like ‘innodb_page_size’;

MySQL中的 B+Tree 索引结构示意图：

1.6 数据库存储引擎【了解】
MySQL存储引擎的不同，那么索引文件保存的方式也有所不同，常见的有二种存储引擎MyISAM和InnoDB。
1.6.1 MyISAM
MySQL5.5版本之前默认的存储引擎，不支持事务。
CREATE TABLE myisam_tab(
id INT,
username VARCHAR(32)
)ENGINE = MYISAM;

它的索引文件和数据文件是分离的（非聚集索引）

1.6.2 InnoDB
MySQL5.5版本之后默认的存储引擎，支持事务
CREATE TABLE innodb_tab(
id INT,
username VARCHAR(32)
)ENGINE = INNODB;

它的索引和数据在同一个文件中（聚集索引）

MySQL函数
为了简化操作，mysql提供了大量的函数给程序员使用（比如你想输入当前时间，可以调用now()函数）
函数可以出现的位置：插入语句的values()中，更新语句中，删除语句中，查询语句及其子句中。
2.1 字符串函数

1. 函数：CONCAT(s1,s2…sn)
   描述：字符串 s1,s2 等多个字符串合并为一个字符串
   实例：SELECT CONCAT(“传”, “智”, “播”, “客”);

2. 函数：CHAR_LENGTH(str)
   描述：返回字符串 str 的字符数（长度）
   实例：SELECT CHAR_LENGTH(“传智播客”);

3. 函数：LENGTH(str)
   描述：返回字符串 s 的字节数（长度）
   实例：SELECT LENGTH(“传智播客”) ;

4. 函数：UCASE(s) | UPPER(s)
   描述：将字符串转换为大写
   实例：SELECT UCASE(“itcast”);

5. 函数：LCASE(s) | LOWER(s)
   描述：将字符串转换为小写
   实例：SELECT LCASE(“ITCAST”);

6. 函数：LOCATE(s1,s)
   描述：从字符串 s 中获取 s1 的开始位置
   实例：SELECT LOCATE(‘he’,‘itheima’);

7. 函数：TRIM(str) | LTRIM(str) | RTRIM(str)
   描述：字符串去空格
   实例：SELECT TRIM(" 传智人");

8. 函数：REPLACE(s,s1,s2)
   描述：将字符串 s2 替代字符串 s 中的字符串 s1
   实例：SELECT REPLACE(‘abc’,‘a’,‘x’);

9. 函数：SUBSTR(s, start, length)
   描述：从字符串 s 的 start 位置截取长度为 length 的子字符串
   实例：SELECT SUBSTR(“itcast”, 2, 3);

10. 函数：STRCMP(str1,str2)
    描述：比较字符串大小,左大于右时返回1，左等于右时返回0，，左小于于右时返回-1，
    实例：SELECT STRCMP(“a”,“b”);

2.2 日期函数

1. 函数：NOW() | CURDATE() | CURTIME()
   描述：获取系统当前日期时间、日期、时间
   实例：SELECT NOW();

2. 函数：YEAR(DATE) | MONTH(DATE) | DAY(DATE)
   描述：从日期中选择出年、月、日
   实例：SELECT YEAR(NOW());

3. 函数：LAST_DAY(DATE)
   描述：返回月份的最后一天
   实例：SELECT LAST_DAY(NOW());

4. 函数：ADDDATE(DATE,n) | SUBDATE(DATE,n)
   描述：计算起始日期 DATE 加（减） n 天的日期
   实例：SELECT ADDDATE(NOW(),10);

5. 函数：QUARTER(DATE)
   描述：返回日期 DATE 是第几季节，返回 1 到 4
   实例：SELECT QUARTER(NOW());

6. 函数：DATEDIFF(d1,d2)
   描述：计算日期 d1->d2 之间相隔的天数
   实例：SELECT DATEDIFF(‘2019-08-01’,‘2019-07-01’);

7. 函数：DATE_FORMAT(d,f)
   描述：按表达式 f的要求显示日期 d
   实例：SELECT DATE_FORMAT(NOW(),’%Y-%m-%d’);

2.3 数字函数

1. 函数：ABS(x)
   描述：返回 x 的绝对值　　
   实例：SELECT ABS(-1);

2. 函数：CEIL(x) | FLOOR(x)
   描述：向上（下）取整
   实例：SELECT CEIL(1.5);

3. 函数：MOD(x,y)
   描述：返回x mod y的结果，取余
   实例：SELECT MOD(5,4);

4. 函数：RAND()
   描述：返回 0 到 1 的随机数
   实例：SELECT RAND();

5. 函数：ROUND(x)
   描述：四舍五入
   实例：SELECT ROUND(1.23456);

6. 函数：TRUNCATE(x,y)
   描述：返回数值 x 保留到小数点后 y 位的值
   实例：SELECT TRUNCATE(1.23456,3);

2.4 高级函数
2.4.1 case表达式
在查询代码的过程中，可能我们需要对查询的结果进行判断。
– 语法
SELECT
CASE [字段,值]
WHEN 判断条件1
THEN 希望的到的值1
WHEN 判断条件2
THEN 希望的到的值2
ELSE 前面条件都没有满足情况下得到的值
END
FROM
table_name;

– 1.1 确定查询表和连接条件 降序
SELECT * FROM emp e INNER JOIN salarygrade sg ON e.salary BETWEEN sg.losalary AND sg.hisalary;
– 1.2 确定显示字段和排序
SELECT e.ename,sg.grade,
CASE sg.grade
WHEN 1 THEN ‘努力赚钱’
WHEN 2 THEN ‘小康生活’
WHEN 3 THEN ‘可以娶媳妇’
WHEN 4 THEN ‘可以买车’
WHEN 5 THEN ‘可以买房’
ELSE ‘土豪’
END 生活状态
FROM emp e
INNER JOIN salarygrade sg ON e.salary BETWEEN sg.losalary AND sg.hisalary
ORDER BY sg.grade DESC;

2.4.2 if表达式
– 语法
SELECT IF(1 > 0,‘true’,‘false’) from table_name;

练习
– 工资+奖金大于20000的员工 显示家有娇妻，否则显示单身狗
SELECT ename,salary+IFNULL(bonus,0) 工资 ,IF(salary+IFNULL(bonus,0)>20000,‘家有娇妻’,‘单身狗’) FROM emp;

2.4.3 cast类型转换
– 语法
SELECT CAST(参数 AS 类型);

– 类型
字符型：CHAR
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED

练习
– 字符串转为 日期
select cast(‘1999-1-1’ as date);
– 字符串转为 整型
select cast(‘19’ as SIGNED);