MYSQL优化原理学习

MYSQL优化：

命令;
关闭;
service mysql stop
启动：
service mysql restart
登录：
mysql -u root -p
数据库存放目录：
ps -ef | grep mysql
一、Mysql逻辑分层：
自上而下分别是：连接层、服务层、引擎层、存储层


InnoDB(默认):事务优先 （适合高并发操作；行锁）
MyISAM:性能优先（表锁）
查询数据库引擎：show engines
支持哪些引擎：show variables like ‘%storage_engine%’
二：sql优化
优化原因：性能低、执行时间长、等待时间长、sql语句欠佳、索引失效、服务器参数设置不合理；
1.SQL：sql语句欠佳
编写过程是：
selelct dinstinct * from … join …on…where …group by …having…order by …limt
解析过程：
from … on…join…where…group by …having…select dinstinct…order by
2.SQL优化：
主要就是正坐在优化索引；
索引：相当于目录；
索引：index是帮助MYSQL高效获取的数结构。索引是数据结构（B树）
B树：二叉树
两层B树

小的放在左边，大的放右边；
索引的弊端：
1.本身很大，可以存放在内存/硬盘
2.索引会降低增删改的效率，提高查询效率；
优势：
1.提高查询效率，降低IO使用率；
降低CPU使用率；
三层B树：
三层Btree可以存放上百万条数据；
Btree：一般是指B+，数据全部存放在叶节点中；
3.索引：
索引分类：
主键索引：不能重复；不能为null
单值索引：单列，一个表可以有多个单值索引；
唯一索引：不能重复；ID
复合索引：多个列构成的索引（相当于二级目录 ：）
例如 ： 张三 ，30； 张三， 25； 李四，30；先找张三 找到以后再找年龄，找两次；二李四只找一次，区分来找；并不是所有的都找两次；
创建索引：
方式一：
create 索引类型 索引名 on 表（字段）
单值：
create index dept_index on tb(dept);
索引类型 索引名 表（字段）
唯一索引：
create uniqe index name_index on tb(name);
复合索引：
create index dept_name_index on tb(dept,name);
方式二：
alter table 表名 索引类型 索引名（字段）
单值：
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 表名；
查询索引;
show index from 表名 ；
show index from 表名 \G
SQL性能问题：
1.分析SQL的执行计划；explain:可以模拟sql优化器执行SQL语句,知道开发人员编写的sql状况；
2.MYSQL查询优化其会干扰我们的优化；
3.explain 使用：
explain +sql

（1）id:值相同：
从上往下依次执行，table顺序，t-tc-c，数据个数越多，语句不变，数据大的后面执行，数据小的优先查询；笛卡尔积；
id:值不同：
ID值越大越优先查询；

（2）select_type:查询类型；
PRIMARY:包含子查询sql中的子查询（最外层）；
SUBQUERY：包含子查询sql中的子查询（非最外层）；
simple:简单查询（不包含子查询、不包含union）
derived:衍生查询（使用到临时表）
1.在from子查询中只有一张表
2.在from子查询中，如果有table union table2,则table1就是derived（衍生表）
（3）type: 索引类型；类型

range:检索指定范围的行，where 后面是一个范围查询（between, > < >=,特殊：in有时会失效，从而 转为无索引all）
index:查询全部索引中的数据；
all:查询全部表中的数据；





6.优化案例：
单表优化；
两表优化；
多表优化：

总结：
1.索引不能跨列使用，保持索引的数据和使用顺序一致性；
2.索引需要逐步优化；
3.将含有In范围查询放到where条件的最后，防止失效。
左连接：

where 小表.x = 打标.y；

–以上两个for循环，最终都会循环3000次，但是对于双层循环来说，一般建议将数据小的循环放外侧，数据大的循环放内侧；

索引建立在经常使用的字段上，在这个例子中，t.cid = c.cid ， t.cid使用的比较频繁，因此给这个表加索引；
一般情况对于左外连接，给左表加索引，右外连接，给右表加索引；

Using join buffer:extre中的一个选项，作用：Mysql引擎使用了连接缓存。
3.多表优化：
1.小表驱动大表；
2.索引建立在常用的字段上；

7.避免索引失效的一些原则；
1.复合索引，不要垮列或者无序使用（最佳左前缀）
2.复合索引；尽量使用全索引匹配；
3.不要在索引上进行操作，例如：索引上+、-、*、/等
4.



使用索引覆盖挽救一部分；
（6）尽量不要是有类型转换；

（7）尽量不要使用or，否则索引失效；

它可以将or左侧的tname索引干掉；不要使用；
其他优化方法：
exist和in
select … from table where exixt(子查询)
select … from table where 字段 in(子查询)
如果主查询的数据大，则使用IN，效率高;
如果子查询的数据大，则使用exist，效率高；
exist语法：将主查询的结果，放到子查询结果中进行条件校验（看子查询是否有数据，如果有数据，则查询成功；如果符合校验，则保留数据；）
select tname from teacher where exists (select * from teacher);
– 等价于select tname from teacher;
（2）
order by 优化；
底层是：using filesort 有两种算法：双路排序、单路排序；（根据IO的次数，即访问磁盘次数）
MySql4.1之前使用双路排序：扫描两次磁盘（1：从磁盘读取排序字段，对排序字段进行排序；（在buffer中进行的排序）；2：从磁盘扫描其他字段）
MySql4.1之前使后单路排序：只读取一次（全部字段），在buffer中进行排序，会有一定的隐患，（有可能不是一次IO，有可能是多次IO）原因：如果数据量大，则无法将所有字段的数据一次性读取完，因此会进行分片读取；
注意：单路排序比双路排序会占用更多的buffer；
单路排序在使用时，如果数据量大，可以考虑调大buffer的容量大小；
buffer:容量大小调节方法：
set max_length_for_sort_data =1024;
如果set max_length_for_sort_data值太低，则mysql会自动从单路—>到双路；（太低：需要排序的类的总大小）
提高order by 查询的策略；
1.选择使用单路、双路；调整bufferd的容量大小；
2.避免select …
3.复合索引 不需要跨列使用，避免using filesort
4.保证全部的排序字段，排序的一致性（都是升序或降序）
排序：慢查询日志：MySql提供的一种日志记录，用于记录MySql中相应时间超过阈值的SQL语句，（long_query）
检查是否开启了慢查询日志：
show variables like ‘%slow_query_log%’;
开启：
临时开启：
set global slow_quert_log = 1;-----在内存中开启；
exit
service mysql restart

永久开启：
/etc.my.cnf文件中追加配置：
vi/etc/my.cnf
在mysqld中写：
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-show.log
**(8)锁机制：**解决因资源共享而造成的并发问题；
分类：
操作类型：
1.读锁（共享锁）：对同一个数据，多个读操作可以同时进行，互不干扰；
2.写锁（互斥锁）：如果当前写锁操作没有完毕，则无法进行其他的读草操作、写操作；
操作范围：
1.表锁：一次性对一张表整体加锁。如果MyISAM存储引擎使用表锁，开发小加锁快；无死锁；但锁的范围大，
2.行锁：一次性对一条数据加锁；如InnoDB存储引擎使用行锁：锁的范围较小，并发度高；开销大，加锁慢容易出现死锁；
3.



2.表锁是通过unlock tables ;行锁是通过事务解锁；