百万数据索引优化-笔记

阅读次数: 1,712

  • A+
所属分类:MYSQL
批量插入
建库
create database bigData;
use bigData;
建表
create table dept(
id intprimary key auto_increment,
deptno mediumint not null,
dname varchar(20) not null,
loc varchar(13) not null
)engine=innodb default charset=gbk;
create table emp(
id int primary key auto_increment,
empno mediumint not null,
ename varchar(20) not null,
job varchar(9) not null,
mgr mediumint not null,
hiredate DATE not null,
sal decimal(7,2) not null,
comm decimal(7,2) not null,
deptno mediumint not null
)engine=innodb default charset=gbk;
创建函数,假如报错:this function has none of DETERMINISTIC...
查看参数
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;
重启后,参数就会消失
windows 配置文件
myslq.ini log_bin_trust_function_creators=1
随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
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 $$
随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100+RAND()*10);
RETURN i;
END $$
假如要删除
drop function rand_num;
创建存储过程
插入数据
DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ((START+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$
插入数据
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept(deptno,dname,loc) VALUES ((START + i),rand_string(10),rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$
修改结束语句
delimiter ;
call insert_dept(100,10);
delimiter ;
call insert_emp(100001,500000);
1.索引是什么?
mysql索引:索引是帮助mysql高效获取数据的数据结构
可以得到索引的本质:数据结构
简单的理解为 排好序的快速查找数据结构
2.为什么要建索引?
优势:
1.提高数据检索的效率
2.降低数据排序的成本
劣势:
1.索引也要占空间的
2.提高查询效率,降低更新表的速度(增,删,改)
3.索引的基本语法
创建 create index indexName on table(colum)
删除 drop index indexName on table
查看 show index from tables; show index from tables\G
4.explain
使用explain关键字可以模拟优化查询语句,从而知道mysql是如何处理你的SQL语句,分析你的查询语句或者表结构的性能瓶颈
优化口诀
1.全值匹配我最爱
2.最左前缀要遵守
3.带头大哥不能死
4.中间兄弟不能断
5.不在索引列上进行计算
6.范围之后全失效
7.不等空值还有or
8.like百分写最右
假设
index(a,b,c)
where语句 索引是否被用到
where a = 3 Y,用到a
where a = 3 and b = 5
慢查询日志
mysql的慢查询日志是mysql提供的一种日志记录,它用来记录mysql在响应时间超过阙值,具体运行时间超过long_query_time值得sql,则会被记录到慢查询日志中。
在mysql源码中判断是大于,而不是大于等于
show profile

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: