数据库性能优化：索引-优快云博客

本文链接：https://blog.youkuaiyun.com/2301_81721754/article/details/146013826

优化分为两种

硬优化，公司掏钱升级硬件
软优化，在操作和设计数据库上个优化（表结构和sql语句）
接下来说的都为软优化

一、软优化

1.执行次数多的语句分类

查询密集型（DQL） -> 查询次数多
修改密集型（DML） -> 修改次数多
根据表的特性，去做专门的优化
2.查询累计插入和查询的数据条数

show global status like 'lnnodb_rows%';

3.千万条数据例子，存储过程，相当于封装的方法，最后call来调用封装的方法

/*  以下代码： 完成1000W条数据的插入  */
-- 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(); -- 插入1000W条数据花费：3m49s 304 ms

二、查询密集型优化

针对查询效率低的问题，进行优化：索引
索引：是帮助mysql高效获取数据的数据结构==（B+树）==
1.索引分类

主键（约束）索引 -> 唯一+非空+提高查询效率
唯一（约束）索引 -> 唯一+提高查询效率
普通索引 -> 仅提高查询效率
组合（联合）索引 -> 多个字段组成索引【联合主键索引、联合唯一索引、联合普通索引】
全文索引TEXT BIGTEXY -> Mysql全文索引引用较少，基本针对文档类数据会选solr、es等文档搜索类数据库
hash索引 -> 根据key - value 等值查询效率非常高，但不适合范围查询

2.创建索引语法，所有的都是以普通为基础扩展的

-- 创建普通索引
create index 索引名 on 表名(字段);

-- 创建唯一索引,创建唯一约束的时候自动添加
create unique index 索引名 on 表名(字段);

-- 创建普通组合索引
create index 索引名 on 表名(字段1,字段2,...);

-- 创建唯一组合索引
create unique index 索引名 on 表名(字段1,字段2,..);

-- 主键索引
-- 在创建pk主键的时候自动添加