mysql的优化

1.mysql的目录结构

1.1linux下mysql的安装路径

路径	解释
/var/lib/mysql	mysql数据库文件的存储路径
/usr/share/mysql	配置文件目录
/usr/bin	相关命令目录
/etc/init.d/mysql	启动暂停相关脚本
/var/log/mysql	日志文件目录

这个主要是5.5的，mysql每个版本目录位置，文件名都会有小变化

1.2设置mysql的字符集编码

查看当前默认的字符集编码格式

show variables like '%char%';

如果不对的话,就设置MySQL的编码

vim /etc/my.cnf

重新启动

service mysqld restart

service 

再次查看编码格式

show variables like'%char%';

这个修改的是之后的数据库和数据表的编码格式，主要作用是之后创建的默认是这种编码格式，但是自己已经创建过的是不会进行修改的

2 mysql的存储引擎

查看mysql支持和默认的数据库引擎

show engines;

2.1 myIsAM和InnoDB的对比

对比项	MyIsAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其他行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	安装	安装

2.2 sql性能下降原因

• 查询语句写的烂
• 索引失效
• 关联索引太多join（设计缺陷或不得已的需求）
• 服务器调优及各个参数设置（缓冲，线程数等）

2.3 slq执行顺序

1. form
2. join on
3. where
4. group by
5. having
6. select
7. distinct
8. order by
9. limit

2.4 七种join的使用

select *
from TableA A
inner join TableB B
on A.key=B.key

select *
from Table A
left join TableB
on A.key=B.key

select * 
from TableA A
right join TableB B
on A.key=B.key

select *
from TableA A
left join TableB B
on A.key=B.key
where B.key is NULL

select  * 
from TableA A
right join TableB B
on A.key = B.key
where A.key is null

select *
from TableA A
full outer join TableB B
on A.key=B.key

selct *
from TableA A
full outer join TableB B
on A.key=B.key
where A.key is NULL
or B.key is null

3. 索引

3.1 索引的介绍

定义：索引是帮助mysql高效获取数据的数据结构。
索引概述：数据本身之外，数据库还维持着一个满足特定算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
特点：

• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
• 平时所说的索引，没有特别指明的话，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。
• 聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。

3.2索引的优缺点：

索引的优势：
- 提高数据索引的效率，降低数据库的io成本。
- 通过索引列对数据进行排序，降低数据排序的成本，降低了cpu的消耗。
索引的劣势：
- 实际上索引也是一张表，该表保存了主键和索引字段，指向实体表的记录，所以索引也是要占用空间的。
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。

3.3 索引的分类

• 单值索引 ：一个索引只包含单个列，一个表可以有多个单列索引。
• 唯一索引：索引列的值必须唯一，但允许有空值。
• 复合索引：一个索引包含多个列

基本语法：

# 创建索引
create [ unique ] index indexname on table_name(columnname(length));
alter table_name add [ unique ]  index [ indexname ] on (columnname(length))
# 删除索引
drop index [ indexname ] on tablename
# 查看索引
show index from table_name 

3.4 索引结构

• Btrr索引
• Hash索引
• full-text全文索引
• R-Tree索引

3.5什么时候创建索引

适合创建索引：
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段，外键关系创建索引
- 查询中经常统计或者分组的字段
不适合创建索引：
- 表太少（2000条一下一般不需要）
- 经常增删改的表
- 数据重复并且分布平均的表字段（比如姓名字段就不要创建索引了）

4. 性能分析

4.1 explain的使用

4.1.1 概述：

使用explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句的。分析你的查询语句或是表结构的性能瓶颈。
使用： explain +sql语句

4.1.2 explain 能做什么

1. 表的读取顺序
2. 数据读取操作的操作类型
3. 哪些索引可以使用
4. 哪些索引被实际使用
5. 表之间的索引
6. 每张表有多少行被优化器查询

4.1.3explain的使用：

• id：select查询的序列号，包含一组数字，表示查询中select子句或操作表的顺序

1. Id相同，执行顺序从上到下
2. id不同，如果是子查询，id的序号会递增，id值越大优先级越高，优先被执行
3. id相同不同，同时存在

• select_type:查询的类型：

1. simple： 检点的select查询，查询中不包含子查询或者union
2. primary： 查询中若包含任何复杂的子部分，最外层查询则被标记为primary
3. subquery： 在select或where列表中包含了子查询
4. derived： 在from列表中包含的子查询被标记为derived，mysql会递归执行这些子查询，把结果放在临时表中
5. union： 若第二个select出现在unio之后，则被标记为union；若union包含在form子句的子查询中，外层select将被标记为：derived
6. union result：从union表获取结果的select

• table： 显示这一行的数据是关于那张表的
• type：显示查询使用了那种类型
  从最好到最差依次是：
  system>const>eq_ref>ref>range>index>all

1. system 表只有一行数据，这是const类型的特例，平时不会出现，这个也可以忽略不计
2. const 表示通过索引依次就找到了，const用于比较primary 可以或者unique索引，因为只匹配一行数据，所以很快。
3. eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
4. ref 非唯一索引扫描，返回匹配某个独立值的所有行。
5. range 只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。一般是在where语句中出现了between。这种范围扫描索引比全表扫描要好，只需从索引的某一点到结束语另一点
6. index 全表扫描，只遍历索引树，通常比all快
7. all 将遍历全表以找到匹配的行
   一般来说，得保证查询至少达到range级别，最好能达到ref

• possible_keys:显示可能应用字啊这张表中的索引，一个或多个。查询设计到的字段上若存在索引，则该索引将被列出，不一定被实际查询使用
• key：实际用到的索引
• key_len: 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
• ref： 显示索引的那一列被使用了，如果可以的话，是一个常数。哪些列或常量被用于查找索引列上的值。
• rows： 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
• extra： 表示不适合在其他列中显示但十分重要的额外信息

1. using filesort 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行。mysql中无法利用索引完成的排序操作称为“文件排序”
2. using temporary 使用了临时表保存中间结果，mysql在堆查询结果排序时使用临时表。常见于排序order by和分组查询group by。
3. using index 表示相应党的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错。
   3.1 如果同时出现using where 表示索引被用来执行索引键值的查找。
   3.2 如果没有同时出现using where，表示索引用来读取数据而非执行查找动作。

5.索引优化

5.1索引分析

1.  慢查询的开启并捕获
2. explain+慢sql分析
3. show profile 查询sql在mysql服务器里面的执行细节和生命周期情况
4. slq数据库服务器的参数调优

5.2索引优化

1. 全值匹配我最爱
2. 最佳左前缀法则： 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
3. 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫
4. 存储引擎不能使用索引中范围条件右边的列
5. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *R
6. mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
7. is null ,is not null 也无法使用索引
8. like以通配符开头(%abc…’) mysql索引失效会变成全表扫描的操作
9. 字符串不加单引号索引失效
10. 少用or,用它来连接时会索引失效

5.3小表驱动大表

• EXISTS

SELECT .. FROM table WHERE EXISTS (subquery)

该语法可以理解为:将主查询的数据，放到子查询中做条件验证，根据验证结果(TRUE或FALSE) 来诀定主查询的数据结果是否得以保留

• 提示:

1. EXISTS (subquery)只返回TRUE或FALSE，因此子查询中的SELECT*也可以是SELECT 1或select ‘X’，官方说法是实际执行时会忽略SELECT清单，因此没有区别
2. EXISTS子查询的实际执行过程可能经过了优化而不是我们理解.上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
3. EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析

5.4提高order by的速度：

1. Order by时select“是一个大忌只Query需要的字段，这点非常重要。在这里的影响是:
   1.1 当Query的字段天小总和小于max_length_for_sort_data而且排序字段不是TEXTIBLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。
   1.2 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
2. 尝试提高sort_buffer_size
   不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高因为这个参数是针对每个进程的
3. 尝试提高max_length_for_sort_data
   提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率.

5.5提高group by的速度

几乎和order by一致
group by实质是先排序后进行分组，按照索引建的最佳左前缀
where高于having，能写在whrer限定的条件就不要去having限定了。

5.6慢查询日志

默认情况下，mysql数据库没有开启慢查询日志，需要手动来设置参数。
如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志后或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

#  查看慢查询是否开启，并且慢查询的存储位置
SHOW VARIABLES LIKE'%slow_query_log%';
# 开启慢查询
SET GLOBAL slow_query_log=1;
# 查看默认情况下，多长时间才会进行记录
SHOW VARIABLES LIKE 'long_query_time%';

mysql默认带有的分析sql工具
mysqldumpslow <- 这个自己查训吧，就不写了

5.7show profile

是什么：是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于sql的调优的测量
默认情况下，参数处于关闭状态，并保持最近15次的运行结果

# 查看是否打开show profile
show variable like 'profiling'
# 开启show profile
set profiling=on

5.8全局查询日志

不要在生产环境开启这个功能

# 开启全局查询功能
set global general_log=1;
set global log_output='TABLE';
# 查看所编辑过的sql语句
select * from mysql.general_log;

6. mysql锁机制

6.1 基础锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
• 写锁（排他锁）：当前写操作没有完成前，他会阻塞其他写锁和读锁。
• 表锁（偏读）：偏向myisam存储引擎，开销小，加锁快，无死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

# 查看表上加过的锁
show open tables;
# 手动添加表锁
lock table 表名称 read( write ),表名称2 read(write), 其他;
# 解锁(解除所有的锁)
unlock tables

show status like 'table%'

Table_ locks_ jimmediate: 产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1 ;
Table_ locks_ _waited: 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在着较严重的表级锁争用情况;

• 行锁：偏向InnoDB存储引擎，开销大，加锁慢;会出现死锁;锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

    当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题一一最后的更新覆盖了由其他事务所做的更新。
  

#查看当前的事务隔离级别
show variables like'tx_isolation';

• 无索引行锁升级为表锁

6.2间歇锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)”，
InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁) 。

• 危害
  因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在
  间隙锁有-一个比较致命的弱点，就是当锁定- -一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

人为的锁定一行