MySQL高级

sql_mode配置

ALTER TABLE student ADD COLUMN age INT
SHOW VARIABLES LIKE 'sql_mode'
USE study
SELECT * FROM student
SELECT name,age,max(age) FROM student GROUP BY age

报错（GROUP BY分组查询只能查询分组的字段）sql5.5以后

MySQL存储引擎

主要用的多的（MyISAM/InnoDB）(SHOW ENGINES   查看存储引擎)

MySQL插入n条数据

MySQL主从复制

主表先把数据写进二进制文件，从表在从二进制文件读取，所以不允许有函数（now（）就会数据不一致）

SHOW VARIABLES LIKE 'log_bin_trust_function_creators'
SET GLOBAL log_bin_trust_function_creators=1;（设置为1时允许有函数，GLOBAL表示当前窗口有效，不止当前会话）

#随机生成n位的字符串
CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) 
BEGIN 
DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'; 
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()*62 ),1)); 
SET i = i +1; 
END WHILE; 
RETURN return_str; 
END
SELECT rand_string(10)
#随机生成n~m的编号
CREATE  FUNCTION `rand_num`(n INT,m INT) RETURNS INT(11) 
BEGIN 
DECLARE i INT  DEFAULT 0 ;
SET i = FLOOR(n+RAND()*(m-n+1)); 
RETURN i; 
END
SELECT rand_num(0,100)

#插入n条数据
CREATE PROCEDURE `add_student` ( IN n INT ) BEGIN
DECLARE i INT DEFAULT 1;
SET AUTOCOMMIT=0;
WHILE( i <= n ) DO
    INSERT INTO student VALUES(null,rand_string (4),rand_num( 0,100 ),NULL);
SET i = i + 1;
END WHILE;
COMMIT;
END
CALL add_student(100000)

成功截图

MySQL性能优化

索引：排好序快速查找的数据结构（B+树）

创建索引：CREATE (unique) INDEX idx_name ON 表名(字段1，字段...)

删除索引：DROP INDEX 索引名 ON 表名//alter table 表名 drop index 索引名;

修改索引：先删除再创建

查询索引：SHOW INDEX FROM 表名

索引的分类

1. 单值索引：只包含一列
2. 唯一索引：字段值唯一（unique）
3. 主键索引
4. 复合索引：包含多列

explain性能分析（连表查询）

course表

id越大越先执行，id一样按顺序从上往下执行（id值 越少越好）

type查询使用的类型，最好到最差依次为：

key_len越大越好

rows：查询的条数，越小越好

extra：group by包含order by  出现最好建索引（必须要有过滤条件）；多表关联，关联字段建索引

查看当前表的所有索引

SELECT index_name FROM information_schema.STATISTICS
WHERE table_name='表名' AND table_schema='库名'
AND index_name<>'PRIMARY' AND seq_in_index=1;

单表优化

1. where条件后面最好全部建成一个复合索引(全部建成一个复合索引和顺序无关)
2. 复合索引是一个层级关系，不按顺序的字段会索引失效，按顺序则索引有效，不跳过索引中的列
3. 不在索引列做任何操作（计算、函数...）
4. 使用范围查询（<或者>），范围右边的列会失效，因此范围查询放在最右边（例：金额、时间判断）
5. 使用不等于（<>或者！=）时使索引失效
6. is null可以使用索引，is not null 不行
7. 模糊查询通配符在前面会使索引失效
8. 字符串不加单引号会使索引失效
9. 不要用select *
10. EXPLAIN SELECT SQL_NO_CACHE * FROM student    表示不通过缓冲查询

连表优化

例：

组合优化：解决出现

必须过滤：where过滤用了索引或者limit,必须按照建索引顺序来，全升序后者全降序