索引优化分析
性能下降，SQL慢、执行时间长、等待时间长
- 数据过多——分库分表
- 关联太多的表，太多join——SQL优化
- 没有充分利用到索引 ——索引建立
- 服务器调优及各个参数设置——调整my.cnf
其中优化手段“建立索引”最快捷也最常用。
预热—常见通用的join查询
其中：union关键字在使用时，两个结果的字段相等、字段数相等、字段顺序一致。
union all和union的区别在于去重，union会去重。
select a.*，b.* from t_emp a
left join t_dept b on a.deptid = b.id
where b.id is null
union
select a.*，b.* from t_dept b
left join t_emp a on a.deptid = b.id
where a.id is null;
-- 查询a表独有和b表独有的数据mysql 单表瓶颈500w
索引简介
索引(Index)是帮助MySQL高效获取数据的数据结构。
索引的本质：索引是数据结构
可将索引理解为"排好序的快速查找数据结构"。
数据库系统维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法。这些数据结构就是索引。
索引的存放位置：一般来说，索引本身也很大，不可能全部存储在内存中，因此，索引往往以索引文件的形式存储在磁盘中。
索引的优势：
- 提高数据检索的效率，降低数据库的IO成本；
- 通过索引对数据进行排序，降低数据排序的成本，减少CPU的消耗。
索引的劣势：
- 在提高查询速率的同时，降低了表更新的速度。因在更新表时，MySQL不仅保存表数据，还需保存索引文件每次添加的索引列字段
- 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引也需占用空间。
索引的结构：
B_tree 平衡树
- Btree
- 非叶子节点包含
- 索引指向的数据
- 向下的指针
- 指向数据的指针
- B+tree
- 非叶子节点不包含索引指向的数据
MySQL选择B+tree作为索引，相对有限的内存中，B+tree的占用空间要比Btree小三分之一，相对发生IO的次数更少，时间更短。
时间复杂度
同一问题可用不同的算法解决，而一个算法的质量优劣将影响到算法及程序的执行效率。算法分析的目的在于选择合适的算法和改进算法。
O(n) 时间复杂度 算法随着数量级n的增加在时间维度上的复杂程度。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MciZf6g7-1627788034670)(D:\QianFeng\博客\Mysql_学习记录\image-20210615215759545.png)]
聚簇索引和非聚簇索引
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
“聚簇”表示数据行和相邻的间值聚簇存储在一起。
只有主键索引是聚簇索引，即按照顺序排列好的索引。
除了主键索引，其余建立的索引都是非聚簇索引，特点：非聚簇索引要对所有索引进行查找。
mysql索引分类
单值索引
即一个索引只包含单个列，一个表可以有多个单列索引
create table
customer(
id int(10) unsigned auto_increment,
customer_no varchar(200),
customer_name varchar(200),
primary key(id),
key(customer_name)
);
-- key(customer_name) 随着表的创建，同时创建索引唯一索引
索引列的值必须必须唯一，但允许有空值
主键索引
设定为主键后数据库会自动建立索引，Innodb 为聚簇索引
复合索引
即一个索引包含多个列。针对多个字段创建一个索引
create index idx_age_deptid_name on t_emp(age,deptid,name)
通过符合索引查询的结果为复合索引内的n条数据，即复合索引包含几条字段就返回几条字段的查询结果。
基本语法
- 创建
create [unique] index [indexName] on table_name(column)
- 创建单值索引
给表t_emp表中的name字段创建索引：create index idx_name on t_emp(name) - 唯一索引的创建
在mysql中，随着主键的创立会自动生成索引，所以对于表中其他字段数据集唯一的字段可以设置唯一索引
为员工表中员工编号字段创建唯一索引create unique index idx_empno in temp(emp_no) - 复合索引
- 删除
drop index [indexName] on mytable; - 查看
show index from table_name\G - 使用alter命令（一般情况使用create就足够了）
-- 四种方式来添加数据表的索引
alter table tbl_name add primary key(column_list);
-- 该语句添加一个主键，意味着索引值必须唯一且不为空
alter table tbl_name add unique index_name(column_list);
-- 这条语句创建索引的值必须是唯一的（除了Null外，Null可能会出现多次
alter table tbl_name add index index_name(column_list);
-- 添加普通索引，索引值可出现多次
alter table tbl_name add fulltext index_name(column_list);
-- 该语句指定是索引为fulltext，用于全文索引什么情况下需要创建索引
- 主键自动创建唯一索引；；
- 频繁作为查询条件的字段应该创建索引；
- 查询中与其它表关联的字段，外键关系建立索引；
- 单键/组合索引的选择问题，组合索引的性价比更高；
- 查询中排序的字段，排序字段若通过索引去访问将大大提升排序速度；
- 查询中统计或分组字段 ，
-
group by相较于order by更伤性能，因为group by中包含order by先排序后分组。
那些情况不许创建索引
- 表记录太少
- 经常增删改查的表或字段
- where条件里用不到的字段不需要创建索引
- 过滤性不好的不适合创建索引
衡量是否因该建索引，怎么建立索引的“尺子”
Explain 就是mysql提供给使用者的“尺子”
一、Explain是什么（查看执行计划）
执行计划：在MySQL逻辑架构中optimizer(优化器)在不改变查询结果的情况下调整sql执行顺序，生成执行计划。
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
二、Explain能干什么？
表的读取顺序
哪些索引可以使用
数据读取操作的操作类型
哪些索引被实际使用
表之间的引用
每张表有多少行被物理查询
三、Explain怎么用
EXPLAIN sql查询语句Explain sql语句 返回对sql语句的分析情况。
执行计划包含的信息（关键的）：
- id
- select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
- 三种id情况：
- id号相同：执行顺序由上到下（所显示分析情况表）
- id不相同：如果是子查询，id的序号会递增，id越大优先级越高，越先被执行；
- id列中id既有相同又有不同时：先按id不同，再按id相同进行优先级划分
- 关注点：id号每个号码，表示一趟独立的查询，一个sql的查询趟数越少越好。（相同号码代表一趟查询）
- type
显示查询使用了何种类型。
- 从最好到最差：
system>const>eq_ref>ref>range>index>ALL
- all：全表扫描、效率极低（当type字段中出现此关键词，就可对其进行索引创建）
- index：覆盖索引，出现index 时sql使用了索引但是没有通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组；（需要进行优化）
- range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般就是在你的where语句中出现了between、<、>、in等的查询语句，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一个点，不会涉及到扫描全部索引
思考题：如何向标中填入大量数据
- 一条语句插入多行数据
- 索引的优缺点
- 优点：查询排序快
- 缺点：所有的写操作变慢
所以通过将表中除主键索引外的其他索引全部删除，可加快插入速度
- 关闭事务的自动提交，手动提交事务，一次完成多条数据插入
- 多线程插入数据，实现优化。
查询优化
批量数据脚本
- 向表中插入50w数据
- 建表
CREATE TABEL 'dept'(
'id' INT(11) NOT NULL AUTO_INCREMENT,
'deptName' VARCHAR(30) DEFAULT NULL,
'address' VARCHAR(30) DEFAULT NULL,
'ceo' INT NULL,
PRIMARY KEY('id')
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE 'emp'(
'id' INT(11) NOT NULL AUTO_INCREMENT,
'empno' INT NOT NULL,
'name' VARCHAR(20) DEFAULT NULL,
'age' INT(3) DEFAULT NULL,
'deptid' INT(11) DEFAULT NULL,
PRIMARY KEY('id')
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;- 设置参数
log_bin_trust_function_creators查看参数是否开启：SHOW VARTABLES LIKE 'log_bin_trust_function_creators'log_bin ：是mysql 的二进制日志，实现MySQL主从复制时使用的，MySQL默认为关闭状态。
将参数打开：SET GLOBAL log_bin_trust_function_creators=1其中关于“global”,如果不填加，那么该参数开启只在当前窗口有效；如果添加，整体所有窗口都有效。 - 创建函数，保证每条数据都不同
# 随机产生字符串
DELIMITER $$
# delimiter 修改结束符
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT;
DECLARE i INT DEFAULT 0;
WHILE i<N DO
SET return_str=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i=i+1;
END WHILE;
RETURN return_str;
END$$
# 加入要删除
# drop function rand_string；
# 随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num(from_num INT,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i=FLOOR(from_num+RAND()*(to_num - from_num + 1));
RETURN I;
END$$
# 假如要删除
# drop function rand_num；delimiter修改结束符，需要用到MySQL编程函数，所以修改结束符，以方便MySQL编程的进行。
- 创建存储过程
批量生成数据、导数、数据的操作
- 创建往emp表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE insert_emp(START INT,max_NUM INT)
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit = 0 把autocommit设置为0
SEET autocommit=0
REPEAT
SET i=i+1;
INSERT INTO emp(empno,NAME,age,deptid)
VALUES((START+i),
rand_string(6),
rand_num(30,50),
rand_num(1,10000)
);
UNTIL i = max_num
END REPEAT;
COMMIT;
END$$PROCEDURE存储过程
SEET autocommit=0自动提交=0，将自动提交关闭
- 创建往dept标中插入数据的存储过程
同理编写部门表的存储过程
# 执行存储过程，向dept 表中添加随机数据
DELIMITER $$
CREATE PROCEDURE 'insert_dept'(max_num INT)
BEGIN
DECLARE i INT DEFAUL 0;
SET autocommit=0;
REPEAT
SET i=i+1;
INSERT INTO dept(deptname,address,ceo)
VALUES(
rand_string(8),
rand_string(10),
rand_num(1,500000)
);
UNTIL i=max_num
END REPEAT;
COMMIT;
END$$- 调用存储过程
# 执行存储过程，向dept表中添加一万条数据
DELIMITER ;
CALL inster_dept(10000);
# 向emp表中添加50万条数据
CALL inster_emp(100000,50000);前面一直在提到，通过创建索引能够更快速的从大数据标中完成查询操作，然而在工作中我们会针对一条sql创建多个索引，那么问题就出现了，由于多个索引的存在，我们只有将索引删除掉，才能够保障下一条sql的正常执行。
删除多条索引
删除索引前，我们必须先要知道具体需要删除的索引是哪些，然后进行删除。
- 查询索引名
SHOW INDEX FROM table_name，但show仅是展现，无法从中提取出来。
在MySQL数据库中有一张表存储索引信息，在information_schema元数据（描述数据的数据）库中保存，STATISTICS 统计表中存储。STATISTICS表中存储着索引名称INDEX_NAME和索引对应的表名TABLE_NAME和库名TABLE_SCHEMA、索引的序列SEQ_IN_INDEX等字段。
注：在删除索引时要注意，主键索引不可以删除；对于复合索引删除，索引序列为1的即可
由上所述，我们就可以查询到想要删除t_emp表的索引的语句：SELECT index_name FROM information_schema.STATISTICS WHERE table_name='t_emp' AND table_shema='mydb' AND index_name <> 'PRIMARY' AND seq_in_index = 1; - 取出索引名-字符串
通过游标对象来提取第一步查询出来的索引
CURSOR 游标
FETCH xxx INTO xxx- 拼接索引名的drop语句是字符串类型 如何将字符串转变为sql
预编译prepare 预编译 xxx EXECUTE
# 实现批量删除索引的存储过程
DELIMITER $$
CREATE PROCEDURE 'proc_drop_index'(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ct INT DEFAULT 0;
DECLARE _index VARCHAR(200) DEFAULT *;
DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_name='t_emp' AND table_shema='mydb' AND index_name <> 'PRIMARY' AND seq_in_index = 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2;
OPEN _cur;
FETCH _cre INTO _index;
WHILE _index<>"DO
SET @str=CONCAT("drop index",_index,"on",tablename);
PREPARE sql_str FROM @str；
EXECUTE sql_str;
DEALLOCATE PREPARE sql_str;
SET _index=";
FETCH _cur INTO _index;
END WHILE;
CLOSE _cur;
END$$# 执行存储过程
CALL proc_drop_index("dbname","tablename");
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
