MySQL经常用到show variables like xxx查看MySQL服务配置信息
一.索引是什么？
1.最多一张表不超过五个索引,查找和排序按照where的顺序来
帮助mysql高效获取数据的数据结构（Index）,(”排好序的快速查找数据结构“)
索引不可能全部存储在内存中，一般以文件的形式存储到硬盘上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
2.索引分类：
①单值索引：一个索引只包含单个列，一个表可以有多个单列索引
②唯一索引：索引列的值必须唯一，但允许有空值（银行卡号）
③复合索引：一个索引包含多个列
④基本语法：create index indexname on mytable columnname
ALTER TABLE tablename ADD INDEX indexname (columname)
mysql索引结构：BTree索引，Hash索引，full-text全文索引，R-Tree索引
二叉查找树，每个节点包含索引键值和一个指向对应数据记录物理地址的指针
3.哪些情况要创建索引？
①主键自动建立唯一索引
②频繁作为查询条件的字段应该创建索引
③查询中与其他表关联的字段，外键关系建立索引
④频繁更新的数据不适合创建索引
⑤where语句中用不到的字段就不创建索引，（所建的索引要跟排序一直where后要按索引顺序来）
⑥单值索引和组合索引，（在高并发情况下倾向于组合索引）
⑦查询中排序的字段，排序字段若通过索引去访问提高排序速度
⑧查询中统计或者分组的字段（分组的话必排序所以也和索引息息相关）
4.哪些情况不适合创建索引？
①表记录太少了
②经常增删改的表
③有很多重复的内容，没必要创建索引
二.Mysql常见瓶颈
①CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
②IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
③服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态
1.explain能干吗？
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
2.JOIN语句优化
①尽可能减少JOIN语句中的NestedLoop的循环总次数，永远用小次数结果集驱动大的结果集
（书的种类去驱动书的总数），如果用大表驱动小标，要进行很多次IO。
②优先优化NestedLoop的内层循环；
③保证JOIN语句中的驱动表上的JOIN条件字段已经被索引；
④当无法保证被驱动表的JOIN条件被索引且内存资源充足的条件下，不要太吝啬JoinBuffer的设置
3.案例分析（索引）
①全值匹配我最爱
②最佳左前缀法则，查询从最左列开始并且不跳过索引中的列
③不在索引列上做任何操作（计算，函数，（自动或手动）类型转换），会导致索引失效而转向全表扫描
④存储引擎不能使用索引中范围条件右边的列（between，in，>,范围之后全失效）
⑤尽量使用覆盖索引（只访问索引的查询（索引列和查询列一直）），减少select *
⑥mysql在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
⑦is null,is not null 也无法使用索引
⑧like以通配符开头("%abc…")mysql索引失效会变成全表扫描的操作（百分like加右边）
怎么解决like’%字符串%'索引不被使用的方法?
使用覆盖索引解决两边的内容，对需要查询的字段添加一个覆盖索引。
⑨字符串不加单引号导致索引失效
⑩少用or，用它来连接时会索引失效
口诀：
带头大哥不能死，中间兄弟不能断，最佳做前缀原则，索引列上无计算
like百分加右边（ike要么后面能用要么只有前面能用） ，就看like是不是常量开头
范围之后全失效 字符串单引号不能缺
groupby基本上都需要进行排序，会有临时表产生
4.查询截取分析
①查询优化:
永远小表驱动大表，小的数据集驱动大的数据集。
类似于嵌套循环Nested Loop；(exists,in)
order by 关键字优化:
提高order by的速度
用了order by时不用 select *,
解决方案：提高sort_buffer_size 和 max_length_for_sort_data
GROUP BY 关键字优化:
group by实质是先排序后进行分组，遵照索引建的最佳左前缀
当无法使用索引列，增大max_length_for_sort_data 和sort_buffer_size 参数大小
where 高于having,能写在where 限定的条件就不要去having限定
②慢查询日志
一般不是调优需要的话，一般不建议启动该参数
explain + 慢SQL分析
show profile
运维经理 or DBA 进行SQL数据库服务器的参数调优
--------------------------------------------总结-------------------------------------
慢查询的开启并捕获
explain + 慢SQL分析
show profile 查询SQL在MySQL服务器里面的执行细节和生命周期情况
SQL数据库服务器的参数调优
5.MySQL两种排序方式：
文件排序或扫描有序索引排序
MySQL能为排序和查询使用相同的索引
index idx_abc(a,b,c)，如下案例进行解析
① order by能使用索引最左前缀
order by a
order by a,b
order by a,b,c
order by a DESC,b DESC,c DESC 要么同升要么同降
② 如果where使用索引的最左前缀定义为常量,则order by能使用索引
where a = const order by b,c
where a = const and b = const order by c
where a = const and b > const order by b,c
③ 不能使用索引进行排序
order by a ASC,b DESC,c DESC /排序不一致/
where g = const order b,c /丢失a索引/
where a = const order by c
where a = const order by a,d /d不是索引的一部分/
where a in(…) order by b,c /对于排序来说，多个想等条件也是范围查询/
三.批量数据脚本
①.先建表
②.设置参数log_bin_trust_function_creators
③.创建函数，保证每条数据都不同
随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
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 $$
④ 创建存储过程
创建往emp表中插入数据的存储过程
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 $$
创建往dept表中插入数据的存储过程
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 $$
最后调用存储过程dept,emp。
CALL insert_dept(100,10);//插入10条记录，下标从100开始
CALL insert_emp(100001,500000);//插入50W条数据，下标从100001开始
四.show Profiles是什么？
mysql提供可以分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量，默认是关闭的使用前需要开启
show variables like 'profiling';
set profiling = on;
查看一条sql语句的生命周期;
show profiles
show profile cpu,block io for query query_id;
日常开发用profile去查看一条sql语句的生命周期出现这四个问题sql语句需要修改
converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了
Creating tmp table 创建临时表（拷贝数据到临时表，用完再删除）
Copying to tmp table on disk 把内存中临时表复制到磁盘，危险!!!
locked
五.全局查询日志（不可以在生产环境下开启此功能，要在测试环境下开启）