一、Mysql分层:
1.结构:客户端层和服务层，服务层包括连接层、服务层(提供各种用户使用的接口、提供优化器)、引擎层、存储层
2.流程:例如客户端发起一个查询请求，先和数据库连接；查询请求给服务层，服务层对查询请求进行优化；将优化的结果给引擎层，选择当前数据库的引擎，引擎将最终的数据交给了存储层
3.引擎：InnoDB(默认.)，事务优先(适合高并发操作:行锁)，MyISAM，性能优先(表锁)
4.一些命令和细节:
1.show engines; //显示引擎 2.mysql结束符除了 ; 还有 \G
2. show variables like '%storage_engine%' //查看当前使用的引擎
3. //指定引擎,默认增长2，中文编码，等都可以在后面增加
create table id(
id int (4) auto_increment,
name varchar(5),
primary key(id)) ENGINE MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf-8
4.show create table teacher; //查看创建表的字段结构
5. show index from teacher; //查看表的索引
二、SQL优化
1.原因:性能低、执行时间太长、等待时间太长、SQL语句欠佳(连接查询、索引失效、服务器参数设置不佳)
2.a.SQL:
编写过程: select dinstinct.. from .. join ..on ..where .. group by .. having .. ordey by .. limit..
解析过程: from .. on .. join .. where .. group by .. having .. select dinstinct ..ordey by limit
b.SQl优化:主要是在优化索引:
索引:相当于书的目录，不然整本书查询;
索引:帮助MYSQL高效获取数据的数据结构。索引是数据结构(树：B树(默认)，像二叉树、Hash树)
索引弊端:
1.索引本身很大，是如下的事例图，可以存放在内存/硬盘(通常为硬盘)
2.索引不是所有情况适用:a.少量数据 b.频繁更新字段 c.很少的使用的字段
3.降低增删改的效率；因为除了改如下数据库的数据，还有改索引的数据结构
索引的优势:
1.提高查询效率(降低IO使用率，因为sql解析过程是io的过程)
2.降低CPU使用率（因为B树索引本身就是个排序的结构，直接使用）
事例图:
三、索引
1.分类:
主键索引:不能重复，id,也不能为null，和唯一索引的区别
单值索引:单列,age;一个表可以多个单值索引,name;
唯一索引:不能重复，id(和单值索引的区别)
复合索引:多个列构成(相当于2级目录:找赵 z:zhao),
1.(name,age)--先根据name找人，有重复再根据age找具体的
2.复合索引不一定同时用2个字段，如果name就一个李四，就不用age再找了
2.创建索引:
方式一: create 索引类型 索引名 on 表(字段1)
单值: create index dept_index on tb(dept);
唯一:create unique index name_index on tb(name);
复合:create index dept_name_index on tb(dept,name);
方式二: alter table 表名 add 索引类型 索引名(字段)
单值:alter table tb add index dept_index(dept);
唯一:alter table tb add unique index name_index(name);
复合:alter table tb add index dept_name_index(dept,name);
注意:如果一个字段是primary key,则字段默认是主键索引
删除索引:drop index 索引名 on 表名;
drop index name_index on tb;
查询索引:show index from 表名;
四.SQl性能问题
a.分析SQl执行计划：explain ,可以模拟SQl优化器执行SQl语句,从而让开发人员知道自己编写sql的状态
b.MYSQL查询优化会干扰我们的优化
查询执行计划:explain select * from tb;
使用expalin，结果参数: id:编号
select_type:查询类型
table:表
type:类型
possible_key:预测用到的索引
key:实际使用的索引
key_len:实际使用索引的长度，例如长度8，可以猜到是varchar
ref:表之间的引用
row:通过索引查询的数据量
extra:额外的信息
五、
rows：被索引优化查询的实际数据个数
extra ：
1.出现 using filesort:性能消耗大;需要"额外"的一次排序 (常见于order by)
例子:select * from test02 where name='' odery by age //排序，先查询，先通过名字查询，再通过年龄排序，没有年龄，又查询了一次，再排序
总结:对于单索引，如果排序和查找是同一字段，则不会出现using filesort；否则就会
复合索引:不能跨列(最佳左前缀) (a1,a2,a3)
例子:select * from test02 where a1="" order by a3; --using filesort
select * from test02 where a2="" order by a3; --using filesort
select * from test02 where a1="" order by a2; --不会
避免: where 和 order by 按照复合索引的顺序使用，不要跨列或者无序使用
2.出现 using temporary:性能损耗大，用到临时表，一般出现在group by
例子:select a1 from test02 where a1 in (1,2,3) group by a2; --using temporary(按a1查询的数据，会放到临时表，再查找，排序，再返回回去)
避免:查询哪些列，就按照哪些列分组
再示例: 解析过程:from .. on .. join where .. group by .. having .. select dinstinct ..order by limit ..(where表里面有a2,a4，但是没有a3，所以再用临时表，再分组)
select * from test03 where a2=2 and a4=4 group by a2,a4; --没有using tem
select * from test03 where a2=2 and a4=4 group by a3; --有using tem
3.using index:性能提升，索引覆盖。原因：不读取原文件，直接在索引表获取数据(不回表查询)
例子:select age from table where age=.. (只要使用的列，全部都在索引中，就是索引覆盖) 单索引
select a1,a2 fro mtable a1='' or a2 ='' (复合索引(a1,a2,a3),也是using index)
再示例:
select a1,a2 from test02 where a1='' or a2 ='';(索引 a1,a2)
select a1,a2 from test02;
结论:如果用到了索引覆盖(using index，会对possible_key和key造成影响)
a.如果没有where，索引只会出现在key
b.如果有where，则索引出现在key和possible_key
4.using where:需要原表查询
假设age是索引列,select age,name from where age =.. 此语句必须回原表查Name，因为索引表无法满足了
5.impossible where:where子句永远为false；
例子:select * from test01 where a1='x' and a1='y'；
六、优化案例
单表优化:
例子: select bid from book where typeid in (2,3) and authorid=1 order by typeid desc;
优化:加索引(bid,typeid,authorid)
根据SQL实际的执行顺序,修改索引顺序(authorid ，typeid，bid)
再修改sql:select bid from book where authorid=1 and typeid in (2,3) order by typeid desc;
小结:a.索引不能跨列使用(保持索引的定义和使用的顺序一致性) b.索引需要慢慢优化 c.将含in的范围查询，放到where的最后面
两表优化:
例子:select * from teacher2 t left outer join course2 c on t.cid = c.cid where c.cname='java'；
索引往哪张表加?
1.小表驱动大表(小表10,大表300,左表的数据要求最小的)--2个for循环,10*300=300*10，对于java程序,for循环，外层越小越好
2.索引建立经常使用的字段下（本题t.cid=t.c.cid可知，t.cid字段使用频繁，因此给该字段加索引,一般情况对于左外连接，给左表加，右连接，给右表加索引）
三表优化:
a.小表驱动大表 b.索引建立在经常使用的字段
避免索引失效的一些原则:
1.复合索引
a.复合索引，不要跨列或无序使用 --正确:(a,b,c) where a.. b.. order by c
b.复合索引，尽量使用全索引匹配 --正确(a,b,c) where a=.. b=.. c=.. 尽量3个索引全部用上，不然相当于把一级目录删掉
2.不要再索引上进行任何操作(计算、函数、类型转换)，否则索引失效
错误例子:select .. where A.x*3 =.. ; A.x是索引
对于复合索引(一个索引)，如果索引(a,b,c)，a失效，b,c同时失效
3.复合索引不能使用不等于、大于、小于(!= <> ) 或者is null(is not null)，否则自身及右侧索引全部失效
4.SQl优化只是概率层次的，因为服务层中有SQL优化器，会影响到自己的优化，所以只能通过explain来分析
5.补救。尽量使用索引覆盖(using index)
6.like尽量以"常量"开头，不要以%开头，否则索引失效
错误例子:select * from xx where name like '%name'；
如果非要%覆盖，可以select name from xx where name like ''%name"； --索引覆盖，一定程度解决这问题
7.尽量不要使用类型转化(显示，隐式)，否则所以失效
错误例子 ：select * from teacher where tname= 123 //程序底层就爱那个123 ->'123'，进行了类型转换
8.尽量不要使用or，否则索引失效
错误例子:select * from teacher where name="1" or tcid >1； //将or左侧的tname失效，很强大
9.exist语法：将主查询的结果，放到子查询中进行条件校验,select tname from table where exists (select * from table)
10.exist和in：如果主查询的数量集大，则使用in，如果子查询的数量集大，则使用exist
11.using filesort的注意事项：
注意：using filesort：二种算法，单路排序和双路排序，根据io的次数
1.mysql 4.1之前，默认使用双路排序；扫描二次磁盘(1.从磁盘读取排序字段，对排序字段进行排序，再读取其他字段)
2.mysql 4.1之后，默认使用单路排序，只读取一次(全部字段) 例子：select name from table where id=''，也有其他情况，数据放在buffer里面，如果无法将所有字段的数据一次性读取完毕，会多次读取。
12.针对11，所以提高order by查询的策略：
a：选择使用单路、双路；调整buffer的容量大小，set max_length_for_sort_data = 1024
b: 避免使用select * ...
c: 复合索引 不要跨列使用，避免using filesort
d: 保证全部的排序字段 排序的一致性(都是升序或降序)
13. 慢查询日志：mysql提供的一种日志记录，用于记录mysql响应时间查过阈值的sql
13.1 查看是否开启慢查询日志记录，默认是关闭的，show variables like '%slow_query_log%'；
13.2 临时开启：set global slow_query_log = 1；退出服务就没有了
13.3 永久开启：/etc/my.cnf 中追加配置
[mysqld]:
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log //路径
14.慢查询阈值：show variables like '%long_query_time%'；
临时设置阈值：set global long_query_time=5； --重新登录生效
永久设置阈值：[mysqld]:
set global long_query_time=5 //service mysql restart 重启生效
15.通过mysqldumpslow工具查看慢sql,mysql自带的，可以通过过滤条件，快速查找需要定位的慢sql
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
