SQL优化（一）

一、MySql启动问题、配置文件
1.Mysql主流版本 5.x
验证 mysql --version
启动：service mysql start
关闭： service mysql stop
重启： service mysql restart
设置密码 /user/bin/mysqladmmin -u root password xxx
登录： mysql -u - root -p
数据库存放目录：ps -ef|grep mysql
mysql字符编码 show variables like ‘char’
设置编码：vi /etc/my.cnf

二、mysql分层、存储引擎
select
1-----连接层----提供与客户端连接的服务
2-----服务层—提供各种用户使用的接口：增伤改查
—提供SQL优化器
3-----引擎层—提供了各种存储数据的方式 INNODB MYSIAM
----InnoDB:事务优先 适合高并发操作 使用的是行锁
----MyISAM:性能优先 使用的是表锁
4-----存储层—存储数据
查询数据库引擎：支持哪些引擎？ show engines

查询当前引擎：show variables like ‘%sotage_engine%’
指定数据库对象的引擎： create table tb(…)ENGINE= MYISAM AUTO_INCREMENT = 1
CHARSET = UTF8;

三、SQL解析过程、索引、B树
产生原因：性能低、执行时间长、等待时间太长、SQL语句欠佳（连接查询）、索引失效
服务器参数设置
SQL
--------编写过程：select distinct …from …join…on…where…group by …having…order by
--------解析过程：from …on …join…where …group by…having…select…order by limit
sql优化，主要就是在优化索引
索引：相当于书的目录 index是帮助mysql高效获取数据的数据结构
索引是数据结构（树：B树、hash树…）
mysql使用的是B树
索引的弊端：索引本身很大，一般存放在硬盘中，需要占据一定的空间
索引不适用情况：少量数据、频繁更新的数据 很少使用的列
索引会降低增删改的效率
优势 提高查询效率 降低IO使用率 降低CPU使用率 如Order by age 因为B树索引本身就是一个排好序的，所以不需要再排序

mysql的查询缓存 在mysql8.0之后取消 因为缓存的失效很容易 只要对表有任何的更新 这个表的所有
查询缓存都会被全部清空

四、B树和索引

三层B树可以存放上百万条数据
Btree：一般都是指的B+ 数据全部存放在叶节点中
B+树查询任意的数据次数 都是n次（B+树的高度）

索引的分类：
---------单值索引：单列 age name 一个表可以有多个单值索引、
---------唯一索引： 不能重复 Id
---------符合索引：多个列构成的索引 （name,age） 先根据name找再根据age找

创建索引
------ create index idx_xxx on t_student(name)
------- create unique index on idx_name on …
-------- 复合索引 create index idx_id_name on t_student(name,age)

查看索引 show index from 表名

SQL性能问题
1）分析SQL的执行计划 explain 可以模拟SQL优化器执行SQL语句
2）Mysql 查询优化器会干扰优化

五、Mysql优化
1)
id:id值相同 从上到下依次执行
表的执行顺序 因数量的个数改变而改变的原因：笛卡尔积
最终结果一致 但是中间过程不同
数据量小的表 优先执行

id值 不同，id值越大越优先查询 本质 再嵌套子查询时，先查内存 再查外层

2）select_type
PRIMARY 包含子查询SQL中的主查询
SUBQUERY 包含子查询SQL的子查询
simple 简单查询
derived 衍生查询

3） type 索引类型
system> const>eq_ref>ref>range>index>all 要对type进行优化的前提 有索引
其中：system const 只是理想情况 实际能达到 ref range
system ：只有一条数据的系统表 或衍生表只有一条数据的主查询
const: 仅仅能查到一条数据的SQL，用于primary Key 或unique索引
eq_ref:唯一性索引 对于每个索引键的查询 返回匹配唯一行数据 有且只有一个 常见于唯一索引和主键索引
一般如果两表联查，需要两个表匹配的数据一致
ref: 非唯一性索引 对于每个索引的查询，返回匹配的所有行
range 检查指定范围的行 where 后面是一个范围查询
index：查询全部索引中数据
如：explain select tid from teacher 只需要扫描索引表 不需要扫描全部
all:查询所有表中的数据

4） possible_keys 可能用到的索引 是一种预测 不准
5） key 实际用到的索引

6） key_len 索引的长度 用于判断复合索引是否被完全使用
null mysql 用一个字节标记null
用两个字节标识可变长度

7） ref 指明当前表所参照的字段

8）rows ：被索引优化查询的数据个数 实际通过索引查到的数据个数
9） extra

1. using filesort 性能消耗大 需要额外的一次排序 一般出现在order by
   eg:
   explain select * from test where a1= xx order by a1
   explain select * from test where a1 = xx order by a2

复合索引 不能跨列
小结：
对于单索引：如果排序和查找是同一个字段 则不会出现using filesort
对于复合索引：where 和order by按照符合索引的顺序使用 不要跨列或无序使用

2.using temporary 性能损耗大 用到了临时表
在 使用的时候 用到了临时表 一般出现在group by语句中
explain select a1 from test where a1 in(1,2,3) group by a1
explain select a1 from test where a1 in(1,2,3) group by a2----------using temporary using filesort
如何避免
查询哪些列 就根据哪些列group by
解析过程
from where group by having select distinct order by limit

3.using index 性能提升 索引覆盖
性能提升的原因 在于此次查询不读取原文件 只从索引文件获取数据 不需要回表查询
只要使用到的列 全部都在索引中 就是索引覆盖

如果用到了索引覆盖 会对possible_keys造成影响
如果没有where 则索引只出现在key中
如果有where 则索引出现在key和possible_keys中

4.using where 需要回表查询
如果既需要在索引表中查 又需要从原表中查
如 select age,name from …where age = 假设age为索引列，此时需要查name 所以需要回表查询