索引
1.索引的概念
索引是帮助MySQL高效获取数据的数据结构。可以的到索引的本质就是一种数据结构,可简单理解为:排好序的快速查找数据结构
索引的目的: 提高查询效率,可类比字典
2.索引优势劣势
优势
- 类似于大学图书馆数目索引,提高数据检索的效率,降低数据库的IO成本
- 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的小号
劣势
- 虽然索引大大提高了查询速度,同时却会降低更新表的速度
- 索引列要额外占用空间的
3.时间复杂度
时间复杂度:随着N数据量级的增加,在时间维度的复杂程度
空间复杂度:随着N数据量级的增加,在空间维度的复杂程度
4.聚簇索引与非聚簇索引
1. 聚簇索引
聚拢到一块儿,主键用的是聚簇索引,按照一定的顺序排好
如果要查1-6 只需要把1-6索引取出来不用扫整个索引
2. 非聚簇索引
分散到四周,不是按照顺序拍的,得扫描整个索引
在字典查 猫或狗 我们先查反犬旁 再分别找
非主键用的是非聚簇索引
5.索引分类
基本语法
查看:
SHOW INDEX FROM t_emp;
创建:
CREATE [UNION] INDEX [indexName] ON table_name(cloumn)
删除:
DROP INDEX [indexName] ON mytable;
修改:
ALTER TABLE tbl_name ADD PRIMARY KEY (cloumn_list)
一般是不修改索引的。
1. 唯一索引
索引列的值必须唯一,但允许有空值
create unique index index_empno on t_emp(empno)
2. 主键索引←
设定为主键后数据库会自动建立索引,innodb为聚簇索引
3. 复合索引
即一个索引包含多个列
create index index_age_deptid_name on t_emp(age,deptId,name
哪些情况需要创建索引
主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
两个表之间存在外键关联,那这个外键也应该建立索引
单键索引与组合索引相对比,组合索引性价比更高
查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段
哪些情况不要创建索引
1.表记录太少
2.经常增删改的表或者字段
3.Where条件里用不到的字段不创建索引
4.过滤性不好的不适合建索引
性别 就男 女 未知
6.执行计划
概念
利用explanin看我们的SQL语句性能如何
作用
- 表的读取顺序
- 那些索引可以使用
- 数据读取操作的操作类型
- 那些索引被实际使用
- 表之间的引用
- 每张表有多少被物理查询
1.id字段
SQL语句在执行过程中执行的顺序
a.id相同:执行顺序由上至下
b.id不同:id值越大优先级越高,越先被执行
一个id代表一趟查询 查询趟数越少越好
2.Type
当我们在执行SQL语句的时候如果出现**range,index,all**建议对其进行优化(如果能优化)
3.key_len
当索引使用长度越长越好 使用的字段越多
4.rows
扫描行数越少越好
5.extra
a.Using filesort
意味着order by后面的字段没有创建索引 使用了**手工排序** 要你命三千
b.Using temporary
意味着group by后面的字段没有创建索引 就会出现Using temporary 要你命三万
c.using join buffer
批量添加数据
建表
CREATE TABLE `dept` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deptName` varchar(30) DEFAULT NULL,
`address` varchar(40) DEFAULT NULL,
ceo INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
`empno` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
设置参数
设置参数log_bin_trust_function_creators
show variables like 'log_bin_trust_function_creators';
set global 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 $$
#假如要删除
#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;
步骤三: 创建存储过程,往emp表中插入数据
DELIMITER $$
CREATE PROCEDURE insert_emp(START INT,max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET 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$$
#删除
#DELIMITER ;
#drop PROCEDURE insert_emp;
步骤四: 创建存储过程,往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE `insert_dept`(max_num INT)
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0