Mysql优化

Mysql 架构

1、连接层

最上层是- -些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2、服务层

第二层架构主要完成大多少的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3、引擎层

存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

4、存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

常用的两种数据库存储引擎对比

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条数据也会锁住整张表，不适合高并发操作	行锁，操作时只锁一行，不对其他行有影响，适合高并发操作
缓存	只缓存索引，不缓存真是数据	不仅缓存索引，还缓存真实数据，对内存要求较高，内存大小对性能有直接影响
表空间	小	大
关注点	性能	事务

7种join查询

mysql种没有full outer join 语法，可以用联合查询union

全外连接相当于左外连接联合右外连接

1
2
3

select <list> from TableA A LEFT JOIN TableB B on A.key=B.key where B.key is NULL
union
select <list> from TableA A RIGHT JOIN TableB B on A.key=B.key where A.key is NULL

全连接相当于左连接联合右连接

1
2
3

select <list> from TableA A LEFT JOIN TableB B on A.key=B.key 
union
select <list> from TableA A RIGHT JOIN TableB B on A.key=B.key

索引

可以简单理解为是一种排好序的方便快速查找的数据结构

优势
- 提高数据检索效率，降低数据库IO成本
- 对数据进行排序，降低数据排序的成本，降低CPU功耗
劣势
- 索引也是一张表，有主键，索引字段，指向实体表的记录，需要占用空间
- 虽然索引可以提高查询效率，但是也会降低更新表的速度，每一次的insert update delete 都要维护相应的索引
- 如果数据量过大，就需要花时间建立更优秀的索引

索引分类

单值索引
- 即一个索引只包含单个列，一个表可以有多个单值索引
唯一索引
- 索引列必须唯一，允许有null
复合索引
- 一个索引包含多个列

基本语法

--创建
create [unique] index indexName on tableName(colname1,colname2,..)
alter tableName add [unique] index [indexName] on tableName(colname1,colname2,..)
--删除
drop index [indexName] on tableName
--查看
show index from tableName\G

索引结构
- BTree
- Hash
- full-text
- R-Tree

性能分析Explain

使用explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql语句的，分析你的查询语句或者表结构的性能瓶颈
用处
- 表的读取顺序
- 数据读取操作的操作类型
- 那些索引可以使用
- 那些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
用法
Explain + sql语句

输出字段意义

id
- id相同，表加载按从上到下顺序执行
- id不同，id大的表优先执行
- id相同不同都存在，id大的先加载，id相同的按从上到下的顺序执行（derived2 = 由id=2的表(t3)衍生而来）

select_type

--种类
simple   		--表示简单的select查询，查询中不包含子查询、联合查询、union
primary  		--嵌套查询的最外层查询
subquery 		--子查询
derived			--表示衍生出来另一张表，例如上图的t3衍生出s1，此时s1为临时表
union			--两个select语句普通联合查询，后面的被标记为union，如果union出现在子查询中，则外层查询被标记derived
union result	--从union表获取结果的select

table 显示此行数据是关于哪张表的
partitions 使用的哪些分区（对于非分区表值为null），在5.5版本中需要加上explain partitions select …..
type 访问类型排列
- 从最好到最差依次是
  
  system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all
  
  常用的：
  
  system>const>eq_ref>ref>range>index>all
  
  system : 表中只有一行记录，一般不会出现
  
  const : 表示通过索引一次就找到了，用来查找primary key 或unique索引（直接指定值）
  
  eq_ref : 唯一性索引扫描，对于每一个索引键，表中只有一条数据与之对应，常见于主键或唯一索引扫描（索引相等匹配）
  
  ref : 不唯一索引扫描，可能返回多行数据
  
  range : 指定索引范围的扫描
  
  index : 遍历索引树
  
  all : 全表扫描
- 开发中尽可能达到ref级别
possible_key 列出查询中可能用到的索引，但不一定真正用到
key : 查询中真正用到的索引，如果为null则没有使用索引，如果用到覆盖索引，则possible_key为null而key不为null

key_len : 表示索引中使用的字节数，可通过该列计算查询中使用到的索引长度，在不损失精度的情况下，长度越短越好

             key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len根据表定义计算得到，不是通过表内检索出的

ref : 显示索引的哪一列被使用了，如果可能的话是一个常数。哪些列和常量被用于查找索引上的值
rows : 大致估算出要找出所需数据所需要读取的行数
extra : 额外的一些重要信息
- Using filesort 表示sql中没用到索引字段的分组或排序，而是用到了非索引字段的分组或排序，不建议出现
- Using temporary : 使用了临时表，常见于order by 与group by , 不建议出现
- Using index : 表示使用了覆盖索引，即，所需要查询的数据都已经出现在索引中，而不用再去访问数据表，效率较高
  
  如果同时出现using where 表示索引用来执行索引键值的查找
  
  如果没有同时出现using where 表示索引用来读取数据而非执行查找动作
- impossible where 表示where后面的条件一直为false

索引优化

索引分析
- 单表：将频繁查询的字段添加索引
- 双表：左连接查询就把索引加在右表，反之
- 三表：同上。对于多级join，也是如此

总结：尽量减少join的次数，尽量使用小的结果集驱动大的结果集

索引失效
- 全值匹配索引不会失效
- 最佳左前缀法则
  - 如果索引了多列，要遵守最左前缀法则，也就是查询从索引的最左前列开始，并且不跳过中间列
- 不在索引上做任何操作
- 存储引擎不能使用索引中范围条件右边的列（name = ‘aaa’ and age > 10 and sex = 1 sex索引失效）
- 尽量使用覆盖索引，减少select *
- mysql在使用不等于（!= 或<>）的时候会导致无法使用索引而全表扫描
- is null is not null 也无法使用索引
- like操作也会索引失效，在字符串右边写% 索引不会失效，建议使用覆盖索引
- 字符串不加单引号索引失效
- 使用or也会索引失效

查询分析

查询优化
- 永远小表驱动大表，即小数据集驱动大数据集
- 使用in时，子查询表数据量小于主查询效率较高，使用exist时，子查询的数据量大于主查询较高
- order by 排序优化，尽量使用index索引排序，即排序字段加索引，避免filesort排序
  - 避免select *，select * 会导致单路排序失效，产生多次磁盘IO影响性能
  - 尝试提高sort_buffer_size的数值
  - 尝试提高max_length_for_sort_data的数值
    - 提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_ buffer_ size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率.
- group by 优化
  - 实质是先排序再分组，依然遵照最左前缀法则
  - 当无法使用索引列时，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
  - where 高于having 能写在where里面的就避免having

慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句,具体指运行时间超过long_ query_ _time值的SQL，则会被记录到慢查询日志中。

具体指运行时间超过long_ query ftime值的SQL,则会被记录到慢查询日志中。long_ query _time的默认值为10，意思是运行10秒以上的语句。

由他来查看哪些SQL超出阈值，比如一条sq|执行超过5秒钟，我们就算慢SQL,希望能收集超过5秒的sql,结合之前explain进行全面分析。

默认mysql没有开启慢查询，需要手动开启，注意：如果不是为了分析sql，不建议开启，会影响性能

show variables like '%slow_query_log%';  --查看是否开启
set global slow_query_log=1; 			 --开启慢查询日志

# 修改之后需要重新连接mysql或者
#  show global variables like '%long_query_time%'; 才能看到修改值
show variables like '%long_query_time%'; --时长阈值
set global long_query_time=3;   		 --设置时长3s就可以记录

# 配置文件中的配置
slow_query_log=1;
slow_query_log_file=/var/lib/mysql/root-slow.log
long_query_time=3;
log_output=FILE

日志分析工具 mysqldumpslow

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/ib/mysql/root-slow.log
#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/ib/mysql/root-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/root-slow.log
#另外建议在使用这些命令时结合|和more使用，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/root-slow.log more

函数和存储过程

#创建函数，假如报错: This function has none of DETERMINISTIC... ..
#由于开启过慢查询日志， 因为我们开启了bin-log, 我们就必须为我们的function指定一个参数。
show variables like "log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;
#这样添加了参数以后，如果mysqld重启，上述参数又会消失,永久方法:
windows下my.ini[mysqld]加上log_bin_trust_function_creators=1
linux下/etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1

show profile 分析sql语句执行消耗的资源情况

#默认关闭状态
show variables like 'profiling';

#开启
set profiling=on;

mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration   | Query                                         |
+----------+------------+-----------------------------------------------+
|        1 | 0.50159825 | select * from emp group by id%10 limit 150000 |
|        2 | 0.52081325 | select * from emp group by id%29 order by 5   |
+----------+------------+-----------------------------------------------+
mysql> show profile cpu,block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000119 | 0.000114 |   0.000000 |            0 |             0 |
| checking permissions | 0.000010 | 0.000008 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000036 | 0.000038 |   0.000000 |            0 |             0 |
| init                 | 0.000037 | 0.000035 |   0.000000 |            0 |             0 |
| System lock          | 0.000009 | 0.000009 |   0.000000 |            0 |             0 |
| optimizing           | 0.000005 | 0.000005 |   0.000000 |            0 |             0 |
| statistics           | 0.000019 | 0.000019 |   0.000000 |            0 |             0 |
| preparing            | 0.000010 | 0.000010 |   0.000000 |            0 |             0 |
| Creating tmp table   | 0.000040 | 0.000040 |   0.000000 |            0 |             0 |
| Sorting result       | 0.000005 | 0.000004 |   0.000000 |            0 |             0 |
| executing            | 0.000003 | 0.000003 |   0.000000 |            0 |             0 |
| Sending data         | 0.520359 | 0.520519 |   0.000000 |            0 |             0 |
| Creating sort index  | 0.000085 | 0.000076 |   0.000000 |            0 |             0 |
| end                  | 0.000007 | 0.000006 |   0.000000 |            0 |             0 |
| query end            | 0.000010 | 0.000010 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000006 | 0.000006 |   0.000000 |            0 |             0 |
| query end            | 0.000004 | 0.000005 |   0.000000 |            0 |             0 |
| closing tables       | 0.000010 | 0.000009 |   0.000000 |            0 |             0 |
| freeing items        | 0.000027 | 0.000028 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000014 | 0.000013 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set, 1 warning (0.00 sec)
# 需要注意的项
converting HEAP to MyISAM #查询结果太大，内存都不够用了往磁盘上搬了。
#拷贝数据到临时表
Creating tmp table  #创建临时表
#用完再删除
Copying to tmp table on disk #把内存中临时表复制到磁盘，危险! ! !
locked

全局查询日志！！生产环境禁止开启！！

# 在my.cnf中配置
general_log=1
#记录日志文件的位置
general_log_file=/path/log
#输出格式
log_output=FILE

#编码开启
set global general_log=1;
set global log_output='TABLE';
select * from mysql.general_log ; --查看日志

锁

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

表锁(偏读)

#手动加表锁
lock table tableName1 read/write,tableName2 read/write,...
#解锁
unlock tables;

MyISAM在执行查询语句(SELECT) 前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
MySQL的表级锁有两种模式:
- 表共享读锁(Table Read Lock)
- 表独占写锁(Table Write Lock)

锁类型	可否兼容	读锁	写锁
读锁	是	是	否
写锁	是	否	否

结合上表：

1、对MyISAM表加读锁，不会阻塞其他会话对同一表的读请求，但会阻塞写请求，只有释放锁之后才会执行其他会话的写操作

2、对MySAM表加写锁，会阻塞其他会话对同一张表的读和写请求，只有释放锁之后才会执行

表锁定分析

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 312   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 4     |
| Table_open_cache_misses    | 2     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.01 sec)
# Table_locks_immediate: 产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1;
#Table_locks_waited: 出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁 值加1)，此值高则说明存在着较严重的表级锁争用情况;

此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞

行锁（偏写）

事务—–一组sql组成的逻辑单元，具备以下四个属性
- 原子性(Atomicity) :事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
- 一致性(Consistent) :在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改以保持数据的完整性;事务结束时，所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
- 隔离性(Isolation) :各个事务之间的操作互不影响。
- 持久性(Durable) :事务完成之后，它对于数据的修改是永久性的。
并发事务带来的问题
- 更新丢失
  - 两个事务同时操作一条数据，最后保存更改的时候必定会有一条线程的更改被覆盖，不符合事务的隔离性
- 脏读
  - 事务A读到了事务B更改但未提交的数据，不符合事务的隔离性
- 不可重复度
  - 一个事务两次读到的数据不一致，不符合隔离性
- 幻读
  - 事务A读到了事务B提交的数据，不符合隔离性

脏读是读到了修改数据，幻读是读到了新增或删除数据

事务的隔离级别，用来解决由于隔离性，并发时出现的问题
- 读未提交 Read uncommitted
  - 会出现脏读问题（事务A读到了事务B更改但未提交的数据）
- 读已提交 Read committed
  - 可解决脏读，但是会出现不可重复读（一个事务两次读到的数据不一致）
- 可重复读 Repeatable read
  - 可解决不可重复读，但是会出现幻读（事务A读到了事务B新增或删除的数据）
- 可序列化 Serializable
  - 事务串行化顺序执行，解决幻读。
SQL语句中的varchar类型不加单引号会导致索引失效，从而导致行锁升级为表锁
间隙锁
- 当我们用范围条件而不是相等条件检索数据，并请求读锁或者写锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于条件中键值不存在的记录叫做间隙（GAP）,InnoDB也会对这个间隙加锁，就叫间隙锁
- 比如当where a > 0 and a < 6 ;并且a =2 项不存在，此事务提交前，任何a=2的插入操作都会阻塞，某些情况下影响性能，也可以说是防止幻读

如何锁定一行

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dept where id = 5 for update;  --锁定一行后，其他事务对此条记录的update都会阻塞
+----+--------+------------+----------+
| id | deptno | dname      | loc      |
+----+--------+------------+----------+
|  5 |    105 | PKhDxCvuLw | YJhSRHLJ |
+----+--------+------------+----------+
1 row in set (0.00 sec)

mysql> commit;  --事务提交，解除锁定
Query OK, 0 rows affected (0.00 sec)

行锁定分析

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |  --当前正在等待锁的数量
| Innodb_row_lock_time          | 0     |  --从系统启动到现在的总锁定时间
| Innodb_row_lock_time_avg      | 0     |  --每次等待锁花的平均时间
| Innodb_row_lock_time_max      | 0     |  --从系统启动到现在等待时间最长的一次是多长时间
| Innodb_row_lock_waits         | 0     |  --系统启动到现在一共等待的次数
+-------------------------------+-------+
5 rows in set (0.00 sec)

优化建议

尽可能让所有数据检索都通过索引完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间
尽可能低级别事务隔离

页锁（了解）

开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间，并发度一般。

主从复制

mysql的主从复制分为三步
- 1、master将改变记录到二进制日志binary log文件，这些事件叫做二进制日志事件，binary log events
- 2、slaver 将master的binary log events拷贝到他的中继日志 relay log
- 3、slaver 重做中继日志中的事件，将改变应用到自己的数据库。mySQL的复制是异步且串行化的
复制的基本原则
- 每个slaver只有一个master
- 每个slaver都有唯一的服务器ID
- 每个master可以有多个slaver

一主一从配置

mysql版本一致且后台正常运行
主机修改my.cnf配置文件
- sever-id=1(必须)
- bin-log=/path/mysqlbin(必须)
- log-err=/path/mysqlerr(可选)
- basedir=path(可选)
- tmpdir=path(可选)
- datadir=path/data(可选)
- read-only=0(必须)
- binlog-ignore-db=databeseName(可选)//设置忽略数据库
- binlog-do-db=databaseName(可选)//设置需要复制的数据库
从机修改my.cnf
- server-id=2(必须)
- log-bin=mysql-bin(必须)启用二进制日志
重启服务器
防火墙关闭
主机建立从机账户
- GRANT REPLICATION SLAVE ON *. * T0 ‘zhangsan‘@’从机地址’ IDENTIFIED BY ‘密码’ ; 从机可以以用户名zhangsan以及密码登录主机服务器
- flush privileges//刷新权限
- show master status //查看主机状态

从机mysql执行命令

mysql>CHANGE MASTER TO MASTER_ HOST=’主机IP’, MASTER_USER='zhangsan', MASTER_PASSWORD='密码',MASTER_LOG_FILE='mysqlbin.数字’, MASTER_LOG_PoS=Position数字;
mysql>start slaver ;# 开启从机复制
mysql>show slaver status; #查看从机状态
mysql>stop slaver ; #停止从机复制