关于SQL优化的笔记

1.MySQL版本

=
以下笔记来自5.5版本

安装：rpm -ivh rpm软件名
卸载：yun -y remove XXX

安装时 有日志提醒我们可以修改密码：
/usr/bin/mysqladmin -u root password

启动：service mysql start
	关闭：service mysql stop
	重启：service mysql restart

在计算机reboot后，登录MySQL： mysql
可能会报错的原因："/var/lib/mysql/mysql.sock不存在"
–原因 ：未启动MySQL
办法： 启动服务：
1).每次使用前 手动开启 /etc/init.d/mysql start
2).开机自启 chkconfig mysql on（chkconfig mysql off）
检查开机是否自动启动：ntsysv（有标记的是开机自启滴）

数据库存放目录：ps -ef|grep mysql（Linux指令）ps：可查数据库目录

与pid文件目录

MySQL核心目录：
/var/lib/mysql  :mysql 安装目录
/usr/share/mysql :  配置文件
/usr/bin : 命令目录（mysqladmin、mysqldump等）
/etc/init.d/mysql  脚本

mysql的清屏操作 Ctrl+L （system clear）
 
 
 

2.MySQL底层原理

=
逻辑分层
1）连接层
2）服务层
提供接口与提供SQL优化器（MySQL QUery Optimizer）
3）引擎层
提供存储数据的方法
InnoDB（mysql默认的）:事务优先（适合高并发操作：行锁）
MyISAM：性能优先（表锁）

4）存储层

查询数据库引擎： 支持哪些引擎？show engines \G；

查看当前使用的引擎 show variables like ‘%storage_engine%’;

 
 
 

3.SQL优化

=
原因：性能低、执行时间太长、等待时间太长、SQL语句欠佳（连

接查询）、索引失效、服务器参数设置不合理（缓冲、线程数）

A SQL：
编写过程：

select …from… join …on…where…group by… having…order

by…limit…
解析过程：
from…on…join…where…group by… having…select…order by…limit…

关于解析分析：https://www.cnblogs.com/annsshadow/p/5037667.html
B SQL优化（main：优化索引）
索引：index是帮助mysql高效获取数据的数据结构（树：b+，Hash树）

索引的优势：
1）提高查询效率（降低IO使用率）
2）降低CPU使用率（比如按年龄查找，在索引中就已经排好了（B+树） ，直接输出就行）
索引的弊端：
1）索引相对很大，可存于固件上 （硬盘）
2）索引的适用范围：a。少量数据 b。修改频繁的字段 c。使用率少的数

据
3）降低增删改的效率（既要改数据表又要改索引表）

PS：关于Btree
1）一般都指B+树，数据全部存放在叶节点上
2)B+树的查询次数：n次（B+树的高度）
3）B+树由于数据存在叶子节点，而且叶子节点组成有序链表，所以范围查

询性能稳定

 
 
 

4.索引 （目录）

=
分类：
一 、单值索引：单列（一个表可以有多个单值索引）
二、唯一索引：unique index（不能重复，唯一）
三、复合索引：（多个列组成的：二级目录）

创建索引：
方式一：
create 索引类型 索引名 on 表（字段）
方式二：
alter table 表名 索引类型 索引名 （字段）

PS：
如果一个字段是主键，则改字段默认就是主键索引

 
 
 

5.SQL性能问题，优化方法

=
A.分析SQL的执行计划：（explain），可以模拟SQL优化器执行SQL语句（

以便知道自己编写SQL状况）

B.查询优化查询优化其会干扰我们的优化

查询执行计划：explain +SQL语句

表的执行顺序 因数量的个数改变而改变的原因：笛卡尔积
数据量小的表 优先查询；

#关于explain表的各个字段：

1）ID值不同：ID值越大优先级越高（方法栈，先进后出。本质就是在嵌套查询时，从内往外）

2）关于explain表中的select_type:

PRIMARY：包含子查询SQL中的 主查询 （最外层）
SUBQUERY：包含子查询SQL中的子查询（非最外层）
simple：简单查询（不包含子查询、union）
derived：衍生查询（使用临时表）
A、在from子查询中只有一个表
B、在from子查询中，如果有表1，表2两张表，则表1就是衍生表

3）关于type：索引类型

system>const>eq_ref>ref>range>index>all

ps:system与const只是理想情况；实际能达到ref与range级别

system：只有一条数据的系统表或衍生表只有一条数据的主查询

const：仅仅能查到一天数据的SQL，用于Primary或unique索引（类型与索引类型有关）

eq_ref:唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有一个）

ref:非唯一性索引，对于每个索引键的查询，返回匹配的所有行（0，∞）

range：检索制定范围的行，where后面是一个范围查询（between ，in，>,<,>=）(特殊：in 有时候会失效，就变成all啦)

index与all：前者是查询全部索引中的数据，后者是查询全部表中的数据。

4）possible_keys:可能用到的索引，是一种预测的，不准

5）key： 实际用到的索引

6）key_len： 索引的长度

用途：用于判断复合索引是否被完全使用

6.优化案例（暂略）

=

7.避免索引失效（优化失效）的一些原则（暂略）

=

8.一些其他的优化方法

=
（1） exist和in
例：select … from table where exists/in(子查询)；
如果主查询的数据集大，则使用in
如果子查询的数据集大，则使用exist

exist的语法：将主查询的结果，放到子查询结果中进行条件校验（看子查询是否有数据，若有，则校验成功）
如果 复合校验，则保留数据；

（2）order by 优化
using filesort 有两种算法：双路排序、单路排序（根据io的次数）
##########还待研究##########
MySQL4.1之前 默认使用 双路排序：（双路：扫描两次磁盘（1：从磁盘

读取排序字段，在buffer进行排序2：读取其他字段））

MySQL4.1之后 默认使用 单路排序（只读一次（全部字段），但这种单

路排序会有一定的隐患（不一定真的是“单路IO”，有可能是多次IO）【

如果数据量特别大，则无法将所有字段的数据一次性读完，则多次读取】

）

===================================
注意：

1）单路排序比双路排序会占用更多的buffer；
2）单路排序在使用时，如果数据大，可以调大buffer的容量（set

max_length_for_sort_data = 1024 单位byte）；
3）若max_length_for_sort_data值太低（需排序的列总大小超过所定阈值

），则mysql会自动从 单路切到双路

===================================

提高order by查询的策略:
a.选择使用单路、双路；调整buffer的容量大小；
b.避免select * …（因为*代表全部，系统需去计算）
c.复合索引 不要跨列使用，避免using filesort
d.保证全部的排列字段 排序的一致性（desc啥的）

9.SQL 排查 -慢查询日志

=
慢查询日志：MySQL的一种日志记录，用于mysql中响应超过阈值的SQL语句

（long_query_time，默认10s）
###慢查询日志（默认关闭），开发调优时建议打开，在部署时关闭

检查慢查询日志的状态：show variables like ‘%slow_query_log%’;

临时开启：set global slow_query_log = 1；–在内存中开启

永久开启：
改/etc/my.ini文件,在【mysqld】里面追加slow_query_log=1和

slow_query_log_file=/自己喜欢的地方/localhost-slow.log

慢查询阈值：show variables like ‘%long_query_time%’;

临时设置阈值：set global long_query_time=5；–要重新登录才生效

永久设置阈值：
改/etc/my.ini文件,在【mysqld】里面追加long_query_time=5

=============================================
selectsleep（6）；//模拟超过阈值的SQL
–查询超过阈值的SQL： show global status like ‘%slow_queries%’
慢查询的SQL被记录在日志中，因此可以通过日志看具体的慢SQL
cat

10.分析海量数据

=
暂略，以后有需求再补

11.锁

=
锁机制：解决因资源共享而引发的并发问题（只是降低冲突的概率）

分类：
操作类型：
a.读锁（共享锁）：多个读操作可同时进行
b.写锁（互斥锁）：只进行当前写操作，只有释放才能进行别的操作

操作范围：
a.表锁（如MyISAM存储引擎使用表锁，开销小、加锁快；无死锁（百度上说可能有死锁）；但锁的范围（粒子）大，容易产生锁冲突、并发度低）
b.行锁（如InnoDB存储引擎使用行锁，开销大、加锁慢；容易出现死锁；但锁的范围（粒子）较小，不易产生锁冲突、并发度高（小概率发生高并发问题：脏读、幻读、不可重复读、丢失更新等问题））
c.页锁
##悲观锁：需要使用数据库的锁机制，如数据库有表级排它锁，有行级排它锁。假定一切操作都可能发现并发冲突，所以采取悲观态度。通过加锁，屏蔽一切可能违反数据完整性的操作
##乐观锁：其实不是真实的去锁住记录不让访问，或者不让更新。 假定操作很少发生冲突，一般对于读多写少的情况。只在提交操作时检查是否违反数据完整性。
PS： 乐观锁不能解决脏读的问题。 可以通过版本号是否比上个版本号或者时间戳来实现。 对于冲突检测后的处理，需要业务逻辑去处理。

增加锁：
local table 表A read/write …

查看加锁的表：
show open tables; --0代表没加锁

释放锁：
unlock tables；

未完待续
av29072634以后补充