MYSQL性能优化--索引

MYSQL性能

随着我们开发的项目使用的用户越来越多，数据库有可能存储着大量的信息，但是，数据量一旦骤增的时
候，我们的效率可能极其低下，那么我们可能要对当前的效率进行一定的提高。提高效率的方式有很多，索引可以帮助我们提高数据库的查询效率

测试数据库效率

1）查看数据库累计操作

--查询累计操作数据影响
show global status like 'Innodb_rows%';

2)分析查询语句的执行效率

插入千万条记录

create database db666;

use db666;

-- 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();

测试查询一条语句的时长：

select * from user where username='jack1000000';  // 耗时：6-8秒

问题：在千万数据量的情况下，一条简单的查询就耗时过长，如果复杂的查询执行时长就更加耗时，想一想用户

3）记录查询耗时语句

数据库自带日志记录：慢查询日志

--查看慢查询日志开启情况
show variables like '%slow_query_log%';
--查看慢查询事件配置
show variables like'%long_query_time%';

开启慢查询日志

set global slow_query_log = on;

设置慢查询sql的时间阈值

-- 全局配置（下次生效...）
set global long_query_time=3;
-- 临时（会话）配置（本次会话窗口生效）
set session long_query_time=3;

测试慢查询日志记录

查询：select * from user where id=22;

慢查询日志文件分析

MYSQL索引

1.索引

• 将数据进行排序整理的过程就称为索引
• 我们根据索引去查，提高查询效率，减少耗时时间

2.MySQL索引分类

• 主键（约束）索引
  主键就是一个索引，在索引的基础上具有约束（非空和唯一）行为
  主键约束+提高查询效率

• 唯一（约束）索引
  唯一约束+提高查询效率

• 普通索引
  仅提高查询效率

• 组合（联合）索引
  多个字段组成索引提高查询效率

3. MySQL索引语法

3.1 创建索引

① 直接创建【了解】

• 主键索引无法使用直接创建

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

-- 创建唯一索引
create unique index 索引名 on 表名(字段);


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

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

代码展示

-- 创建学生表
CREATE TABLE student(
 id INT,
 name VARCHAR(32),
 email VARCHAR(40)
);

-- name字段设置普通索引
CREATE INDEX name_idx ON student(name);

-- email字段设置唯一索引
CREATE UNIQUE INDEX telephone_uni_idx ON student(email);

② 修改表时指定【了解】

-- 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table 表名 add primary key(字段);   ---主键默认的索引名：primary  key

-- 添加唯一索引（除了NULL外，NULL可以出现多次）
alter table 表名 add unique(字段);      --- 默认索引名：字段名

-- 添加普通索引
alter table 表名 add index(字段);      ---默认索引名：字段名

代码展示

-- 指定id为主键索引
ALTER TABLE student ADD PRIMARY KEY(id);
-- 指定name为普通索引
ALTER TABLE student ADD INDEX(name);
-- 指定email为唯一索引
ALTER TABLE student ADD UNIQUE(email);

③ 创建表时指定【掌握】

-- 创建教师表
CREATE TABLE teacher(
 id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引
 `name` VARCHAR(32),
 email VARCHAR(40) UNIQUE, -- 唯一索引
 sex VARCHAR(5),
 birthday DATE,
 INDEX(`name`) -- 普通索引
);

3.2 删除索引

-- 直接删除
drop index 索引名 on 表名;

-- 修改表时删除
alter table 表名 drop index 索引名;

4. 索引的优缺点

* 优点
		提高查询效率
* 缺点
		索引占用磁盘空间
		添加记录、更新、修改时，也会更新索引，会间接影响数据库的效率。

5. 索引创建原则

1. 经常使用where条件搜索的字段

2. 经常使用表连接的字段（内连接、外连接） 外键

3. 经常排序的字段 order by

* 注意：索引本身会占用磁盘空间，不是所有的字段都适合增加索引....

6. 常见索引失效情况

-- 1.使用like模糊匹配，%通配符在最左侧使用时
select * from user where email like '%jack1234567%';

-- 2.尽量避免使用or，如果条件有一个没有索引，那么会进行全表扫描
select * from user where id = 88 or password = xxxx;

-- 3.在索引列上进行计算
select * from user where  id+1 = 88;

-- 4.数据类型不匹配
select * from user where  username = "123"; 正确
select * from user where  username = 123; 索引失效