MySQL高级day02

索引

1.索引的概念

索引是帮助MySQL高效获取数据的数据结构。可以的到索引的本质就是一种数据结构，可简单理解为：排好序的快速查找数据结构

索引的目的: 提高查询效率，可类比字典

2.索引优势劣势

优势

1. 类似于大学图书馆数目索引，提高数据检索的效率，降低数据库的IO成本
2. 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的小号

劣势

1. 虽然索引大大提高了查询速度，同时却会降低更新表的速度
2. 索引列要额外占用空间的

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. 表的读取顺序
2. 那些索引可以使用
3. 数据读取操作的操作类型
4. 那些索引被实际使用
5. 表之间的引用
6. 每张表有多少被物理查询

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;

REPEAT

SET i = i + 1;

INSERT INTO dept(deptname,address,ceo) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));

UNTIL i = max_num

END REPEAT;

COMMIT;

END$$

#删除

# DELIMITER ;

# drop PROCEDURE insert_dept;

步骤五: 调用存储过程

#执行存储过程，往dept表添加1万条数据

DELIMITER ;

CALL insert_dept(10000);

#执行存储过程，往emp表添加50万条数据

DELIMITER ;

CALL insert_emp(100000,500000);

 

验证

select count(id) from emp

select count(id) from dept

删除索引

创建批量删除索引的存储过程

DELIMITER $$

CREATE PROCEDURE proc_drop_index (dbname VARCHAR(200),tablename VARCHAR(200))

BEGIN

  DECLARE done INT DEFAULT 0;

  DECLARE ct INT DEFAULT 0;

  DECLARE _index VARCHAR(200) DEFAULT '';

  DECLARE _cur CURSOR FOR SELECT index_name FROM information_schema.STATISTICS WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND index_name <>'PRIMARY';

  DECLARE CONTINUE HANDLER FOR NOT FOUND set done=2;

   OPEN _cur;

   FETCH _cur INTO _index;

   WHILE _index<>'' DO

SET @str = CONCAT("drop index ",_index," on ",tablename);

PREPARE sql_str FROM @str;

EXECUTE sql_str;

DEALLOCATE PREPARE sql_str;

SET _index='';

FETCH _cur INTO _index;

END WHILE;

  CLOSE _cur;

END$$

 

CALL proc_drop_index("dbname","tablename");

 

删除案例

CALL proc_drop_index("mydb","t_emp");

 

SELECT index_name FROM information_schema.STATISTICS WHERE table_schema='mydb' AND table_name='t_emp' AND seq_in_index=1 AND index_name <>'PRIMARY'

单表索引优化

1. 全值匹配我最爱←

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'acew'

有多少个条件就创建多少个条件的索引

2. 最佳左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and emp.name='abcd';

虽然可以正常使用，但是只有部分被使用到了。

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptid=4 AND emp.name = 'acew'

完全没有使用上索引。

结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

3. 范围查询
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = ‘abc’ ;

创建索引create index idx_age_name_deptid on emp(age,deptId,name)

存储引擎不能使用索引中范围条件右边的列

create index idx_age_deptid_name on emp(age,name,deptId)

4. 索引失效的情况
4.1. 索引列不要做任何操作←
不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

这两条sql哪种写法更好

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name LIKE 'abc%'

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(emp.name,3) = 'abc'

4.2. 使用了不等于←
mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> 'abc'

CREATE INDEX idx_name ON emp(NAME)

4.3. 使用了IS NOT NULL
下列哪个sql语句可以用到索引

EXPLAIN SELECT * FROM emp WHERE age IS NULL

EXPLAIN SELECT * FROM emp WHERE age IS NOT NULL

CREATE INDEX index_age ON emp(age)

4.4. like通配符以%开头
like以通配符开头(’%abc…’) mysql索引失效会变成全表扫描的操作

EXPLAIN SELECT * FROM emp WHERE name like 'abc%'

CREATE INDEX index_name ON emp(name)

EXPLAIN SELECT * FROM emp WHERE name like '%abc%'

首字母都不能确定这个索引肯定用不上

字符串不加单引号索引失效

4.5. 类型转换←

CALL proc_drop_index("mydb","emp");

EXPLAIN SELECT * FROM emp WHERE name =123

CREATE INDEX index_name ON emp(name)

5. 一般性建议

1.对于单键索引，尽量选择针对当前query过滤性更好的索引(身份证号， 手机号)

2.在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序 中，位置越靠前越好。这样过滤后面数据就会更少

3.在选择组合索引的时候，尽量选择可以能够包含当前query中的where字 句中更多字段的索引----全值匹配我最爱

4.在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个 字段放在索 引次序的最后面

5.书写sql语句时，尽量避免造成索引失效的情况

关联查询优化

建表

CREATE TABLE IF NOT EXISTS `class` (

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

`card` INT(10) UNSIGNED NOT NULL,

PRIMARY KEY (`id`)

);

CREATE TABLE IF NOT EXISTS `book` (

`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

`card` INT(10) UNSIGNED NOT NULL,

PRIMARY KEY (`bookid`)

);

 

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));

 

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));

commit

四个SQL分析语句

CALL proc_drop_index("mydb","emp");

CALL proc_drop_index("mydb","dept");

#第1个SQL

EXPLAIN SELECT SQL_NO_CACHE c.`name`,ab.name ceoname FROM emp c LEFT JOIN

(SELECT a.`name`,b.`id` FROM emp a

INNER JOIN dept b ON b.ceo= a.id)ab

ON c.`deptId`= ab.id;

#第2个SQL

EXPLAIN SELECT SQL_NO_CACHE ab.name,c.`name` ceoname FROM

(SELECT a.`name`,b.`CEO` FROM emp a

LEFT JOIN dept b ON a.`deptId`= b.`id`)ab

LEFT JOIN emp c ON ab.ceo=c.`id`;

#第3个SQL 

EXPLAIN SELECT SQL_NO_CACHE a.`name`,c.`name` ceoname FROM emp a

LEFT JOIN dept b ON a.`deptId`= b.`id`

LEFT JOIN emp c ON b.`CEO`= c.`id`;

#第4个SQL

EXPLAIN SELECT SQL_NO_CACHE a.`name`,(SELECT c.name FROM emp c WHERE c.id=b.`CEO`)ceoname

FROM emp a

LEFT JOIN dept b ON a.`deptId`= b.`id`;

执行效果

总结

1、保证被驱动表的join字段已经被索引

2、left join 时，选择小表作为驱动表，大表作为被驱动表(可以创建索引)。

3、inner join 时，mysql会自己帮你把小结果集的表选为驱动表。

4、子查询(临时表)尽量不要放在被驱动表，有可能使用不到索引。

5、能够直接多表关联的尽量直接关联，不用子查询,没有办法使用索引。

子查询优化

尽量不要使用not in 或者 not exists

选取一个查询：查询所有的非掌门人

SELECT * FROM t_emp a WHERE a.id NOT IN(

SELECT b.ceo FROM t_dept b WHERE b.ceoIS NOT NULL)

排序分组优化

1. 排序优化

尽量使用Index方式排序,避免使用FileSort方式排序

1.1. 无过滤不索引

explain select SQL_NO_CACHE * from emp order by age,deptid;

explain select SQL_NO_CACHE * from emp order by age,deptid limit 10;

create index idx_age_deptid on emp (age,deptid)

1.2. 顺序错，必排序

create index idx_age_deptid_name on emp (age,deptid,name)

explain select * from emp where age=45 order by deptid,name;

explain select * from emp where age=45 order by name,deptid;

1.3. 方向反必排序

explain select * from emp where age=45 order by deptid desc,name desc;

explain select * from emp where age=45 order by deptid asc,name desc;

索引的选择

CALL proc_drop_index("mydb","emp");

explain SELECT SQL_NO_CACHE * FROM emp WHERE age=30 AND empno<101000 ORDER BY NAME; 

双路排序和单路排序

如果不在索引列上，filesort有两种算法，mysql就要启动双路排序和单路排序

怎么提升内存

1. 尝试提高 sort_buffer_size

不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的1M-8M之间调整

2. 尝试提高 max_length_for_sort_data

提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出 sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用 率.1024-8192之间调整

GROUP BY关键字优化

group by 使用索引的原则几乎跟order by一致 ，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。

覆盖索引

什么是覆盖索引？

简单说就是select 到 from 之间查询的列 <=使用的索引列+主键
也就是尽量少写select *

8个SQL

1、列出自己的掌门比自己年龄小的人员

2、列出所有年龄低于自己门派平均年龄的人员

3、列出至少有2个年龄大于40岁的成员的门派

4、至少有2位非掌门人成员的门派

5、列出全部人员，并增加一列备注“是否为掌门” 如果是掌门人显示是 不 是掌门人显示否

6、列出全部门派，并增加一列备注“老鸟or菜鸟”，若门派的平均值年龄>50 显示“老鸟”，否则显示“菜鸟”

7、显示每个门派年龄最大的人

8、显示每个门派年龄第二大的人

慢查询日志

1. 概念
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

2. 使用
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件

SHOW VARIABLES LIKE '%slow_query_log%';

使用set global slow_query_log=1;开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。全局变量设置，对当前连接不影响对当前连接立刻生效如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

修改my.cnf文件，[mysqld]下增加或修改参数 然后重启MySQL服务器

slow_query_log =1

slow_query_log_file=/var/lib/mysql/atguigu-slow.log

3. 慢查询时间
查看当前多少秒算慢

SHOW VARIABLES LIKE 'long_query_time%';

设置慢的阙值时间set long_query_time=1(当前有效)

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。

4. 慢查询实战
set global slow_query_log=1;

set long_query_time=0.5

CALL proc_drop_index("mydb","emp");

CALL proc_drop_index("mydb","dept");

#快

SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4
	
	SELECT SQL_NO_CACHE * FROM emp WHERE emp.name LIKE 'abc%'

#慢

SELECT SQL_NO_CACHE c.`name`,c.`age`,aa.age FROM emp c INNER JOIN(

    SELECT a.`deptId`,AVG(a.`age`)age FROM emp a

    WHERE a.`deptId` IS NOT NULL

    GROUP BY a.`deptId`)aa ON c.`deptId`=aa.deptid

	WHERE c.`age`< aa.age
	
	 
	
	SELECT c.`name`,c.`age`,aa.age FROM emp c INNER JOIN(
	
	SELECT a.`deptId`,AVG(a.`age`)age FROM emp a
	
	WHERE a.`deptId` IS NOT NULL
	
	GROUP BY a.`deptId`)aa ON c.`deptId`=aa.deptid
	
	WHERE c.`age`< aa.age

 

	SELECT SQL_NO_CACHE c.`name`,ab.name ceoname FROM emp c LEFT JOIN
	
	(SELECT a.`name`,b.`id` FROM emp a
	
	INNER JOIN dept b ON b.ceo= a.id)ab
	
	ON c.`deptId`= ab.id;

慢查询日志分析工具

mysqldumpslow --help

工作常用参考

得到返回记录集最多的10个SQL

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join"
 /var/lib/mysql/atguigu-slow.log

另外建议在使用这些命令时结合|和more使用，否则有可能出现爆屏情况

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

SHOW PROCESSLIST
可以看到系统的一些连接情况

mysql -uroot -p123 -P3306 -h127.0.0.1

视图
将一段查询sql封装为一个虚拟的表, 这个虚拟表只保存了sql逻辑，不会保存任何查询结果。

语法
创建

CREATE VIEW view_deptName as SELECT b.`deptName`,

IF (AVG(a.age)>50,'老鸟','菜鸟')'老鸟or菜鸟'

FROM t_emp a

INNER JOIN t_dept b ON a.`deptId`= b.`id`

GROUP BY b.`id` ,b.`deptName`

查询

select * from view_deptName; 

更新

CREATE OR REPLACE VIEW view_deptName as SELECT b.`deptName`,

IF (AVG(a.age)>50,'老鸟','菜鸟')'老鸟or菜鸟'

FROM t_emp a

INNER JOIN t_dept b ON a.`deptId`= b.`id`

GROUP BY b.`id` ,b.`deptName`

注意事项(适用5.5)
mysql的视图中不允许有from后面的子查询，但oracle可以