MySQL高级day02_方向反必排序-CSDN博客

本文链接：https://blog.csdn.net/Wynb1020/article/details/123671460

索引1.索引的概念索引是帮助MySQL高效获取数据的数据结构。可以的到索引的本质就是一种数据结构，可简单理解为：排好序的快速查找数据结构索引的目的: 提高查询效率，可类比字典2.索引优势劣势优势类似于大学图书馆数目索引，提高数据检索的效率，降低数据库的IO成本通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的小号劣势虽然索引大大提高了查询速度，同时却会降低更新表的速度索引列要额外占用空间的3.时间复杂度时间复杂度:随着N数据量级的增加，在时间维度的复杂程度空间复杂

摘要由CSDN通过智能技术生成

索引

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