mysql

sql的解析顺序

SetJoin的图关系

左连接

右链接

内连接

左连接独占

右链接独占

全连接

全连接独占

索引优势：
提高数据检索的效率、降低数据库的IO成本
降低排序的成本、降低了CPU的消耗
劣势：
索引也是一张表、该表保存了主键与索引字段、并指向实体表的记录、所以索引列也要占用空间
提高了查询速度。但是降低更新表的速度。
如果你的mysql有大数据量的表、就需要花时间研究建立最优秀的索引，或者优化查询逻辑
索引的结构
B-Tree、Hash、fullText、R-Tree
要
1、主键自动建立唯一索引
2、频繁查找的条件字段应该创建索引
3、查询中与其他表关联的字段、外键关系建立索引
4、频繁更新的字段不要索引
5、where条件里用不到的字段不要索引
6、高并发下倾向创建组合索引
7、查询中排序的字段、排序字段若通过索引去访问将大大提高排序速度
8、查询中统计或者分组的字段
不要
1、表记录太少
2、经常增删改的字段
3、数据重复且分布平均，如果有很多重复的内容就不要建立索引

Explain

id：
select查询的序列号、表示查询中执行select的子句或者操作表的顺序
id相同。执行顺序由上至下
id不同、如果是子查询id值越大执行的越靠前
select_Type：
6个值：

• 1、simple：简单的select查询、查询中不包含子查询或者union
• 2、primary：查询中若有任何子查询。最外层的就是primary
• 3、subquery：子查询
• 4、derived：衍生表、mysql会递归这行这些子查询、结果放在临时表
• 5、union：第二个select出现在union之后，被标记为union
• 6、unionresult：两个union结果的合并
  type：
• ALL：全表
• index：只遍历索引树
• range：只检索给定范围的行、使用一个索引来选择行、key列显示使用了那个索引，一般就是你在where语句中出现了between<>\in等查询、这种范围扫描索引比全表扫描要好、因为它只需要开始于索引的某一列、而结束语另一点，不会扫描全部索引
• ref：非唯一索引、返回匹配某个单独值的所有列、会找到多个符合条件的行，所以应该属于查找和扫描的混合体
• eq_ref：唯一性所有扫描
• const|、system：表只有一行记录、const类型的特例。const一般是主键。唯一的，索引一次就找到
• NULL

从最好到最差是：
system-const-eq_ref-ref-range-index-all
达到range和ref级别就ok

possible_keys：可能存在的索引
keys：覆盖到的索引
key_len:表示索引中使用的字节数。在不损失精确性的情况下、长度越短越好
ref：显示索引的那一列被使用了、如果可能话。是一个常数哪些列或者常量被应用到了。
rows：根据表统计的记录数，大致估算出所需记录所需要读取的行数。越小越好
extra：包含不适合在其他列中，但是重要的信息

• Using filesort：文件内排序，使用了一个外部的索引排序，
• Using Temporary：新建了内部临时表保存中间结果，常见于orderby和groupby
• UsingIndex：标明使用了覆盖索引，
• UsingWhere
• usingjoinbuff：使用了 链接缓存
• impossible where：不可能的事
• select tables optimized away
• distinct

优化技巧：双表。左连接加右边表。右链接加左边表索引，用小结果集去驱动大的结果集，确保join的时候字段有索引。适当调整joinbuffer

定值、范围还是排序
一般order要给个范围
groupby 基本都要进行排序。会有临时表产生

1、观察
2、开启慢查询日志、设置阈值，比如超过5秒的就是慢sql，并将其抓取
3、explain+sql分析
4、show profile查询sql在mysql中的执行细节和生命周期情况
5、DBA进行参数调优

SQL优化
1、Explain

exists：将主查询的数据放到子查询中做验证、根据验证结果（true or false）来决定主查询的数据结果是否得以保留

慢查询日志

mysql> show variables like '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | OFF                                  |
| slow_query_log_file | /var/lib/mysql/1b742f19c17b-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)

开启慢查询日志

mysql> set global slow_query_log=1
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+---------------------+--------------------------------------+
| slow_query_log      | ON                                   |
| slow_query_log_file | /var/lib/mysql/1b742f19c17b-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.00 sec)

查看默认的时间

mysql> show variables like 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

set global long_query_time=3

mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)

mysql> show global variables like 'long_query_time%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+

mysqldumpslow

showprofile

mysql中可以分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量
默认关闭、并保存最近15次的运行结果

mysql> show variables like 'profiling'
    -> ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

开启

mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql中的锁

更新丢失

脏读

不可重复度

幻读

三大锁

• 表锁（读） 共享锁：MyISAM，冲突最低、并发最低
• 行锁（写） 排他锁：InnoDB：开销大、加锁慢、会出现死锁、锁定粒度最小、发生锁冲突的概率最、并发度也最高，InnoDB和Myisam的最大不同：1是支持事务、2是采用了行级锁
• 页锁
  简而言之：就是读锁会阻塞写、但是不会阻塞读、而写锁会把读和写全部阻塞
  

主从复制

Mysql主从复制的步骤
1、master将改变记录二进制日志（binarylog）。这些记录过程叫做二进制日志事件，binary log events；
2、salve将master的binary log events拷贝到它的中继日志（relay log）；
3salve重做中继日志中的时间、将改变应用到自己的数据库中、Mysql复制是异步的且串行化的。