一、MySQL

检查当前Linux系统是否安装过mysql： rpm -qa|grep -i mysql

1
2
3
4
5
6
7
8

service mysql start
ps -ef | grep mysql
service mysql stop

/usr/bin/mysqladmin -u root password root
mysql -uroot -p  密码：root

chkconfig --list mysqld

MySQL存储引擎对比
对比项|MyISAM|InnoDB
-|:-:|:-:
主外键|不支持 | 支持
事务 |不支持 | 支持
行表锁|表锁，不适合高并发|行锁，适合高并发
缓存|只缓存索引，不缓存真实数据|不仅缓存索引，还要缓存真实数据
表空间|小|大
关注点|性能|事务
默认安装|Y|Y

二、Select Joins

Inner Join 内连接

• 产生A和B的交集。

SELECT
from TableA A
inner join TableB B
on A.Key = B.Key

Left Join 左连接

• 产生表A的完全集，而B表中匹配有值列，没匹配的以null值取代。

SELECT
from TableA A
left join TableB B
on A.Key = B.Key

• 产生在A表中有，而在B表中没有的集合。

SELECT
from TableA A
left join TableB B
on A.Key = B.Key
where B.Key is NULL

Right Join 右连接

• 产生表B的完全集，而A表中匹配的则有值，没匹配的以null值取代。

SELECT
from TableA A
right join TableB B
on A.Key = B.Key

• 产生在B表中有而在A表中没有的集合。

SELECT
from TableA A
right join TableB B
on A.Key = B.Key
where A.Key is NULL

Full Outer Join 全连接(MySQL不支持)

• 产生A和B的并集。对于没有匹配的记录，则以null做为值。

SELECT
from TableA A
full outer join TableB B
on A.Key = B.Key

• 产生（A表中有但B表没有）和（B表中有但A表中没有）的数据集。

SELECT
from TableA A
full outer join TableB B
on A.Key = B.Key
where A.Key is null or B.Key is null

Union (MySQL 合并去重)

• 等价于 Full Outer Join

SELECT
from TableA A
left join TableB B
on A.Key = B.Key
union
SELECT
from TableA A
right join TableB B
on A.Key = B.Key

• 等价于 Full Outer Join Where

SELECT
from TableA A
left join TableB B
on A.Key = B.Key
where B.Key is null
union
SELECT
from TableA A
right join TableB B
on A.Key = B.Key
where A.Key is null

cross join 笛卡尔积(交差集)

把表A和表B的数据进行一个 N * M 的组合，即笛卡尔积。
产生 N * M 条记录，在开发过程中我们肯定是要过滤数据，所以这种很少用。

三、Index 索引

索引：是帮助MySQL高效获取数据的数据结构。（排好序的快速查找数据结构）B树—多路搜索树。

优化分析：性能下降SQL慢，执行时间长，等待时间长。

创建索引

1
2
3
4
5
6

Create user
id name email wxNum
select * from user where name = '';

给name列创建索引
Create index idx_user_name on user(name);

优势：
1、索引提高数据检索的效率，降低数据库的IO成本。
2、通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势：
1、实际索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，索引列也要占空间。
2、提高查询速度，降低更新表的速度。保存表数据，还要保存索引文件每次更新添加索引列字段。

索引分类

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

explain + SQL

show index from Table;

Create index idx_nameAge on Table(name,age);

explain select * from Table;

id select_type table type possible_keys key key_len ref rows Extra

id (表的读取顺序)

select_type (数据读取操作的操作类型)

• 1、SIMPLE： 简单的 select 查询，查询中不包含 子查询 或者 UNION。
• 2、PRIMARY： 查询中若包含任何复杂的子部分，最外层查询则被标记为 PRIMARY。
• 3、SUBQUERY： 在 SELECT 或 WHERE 列表中包含了子查询。
• 4、DERIVED： 在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询,把结果放在临时表里。
• 5、UNION：
  • 若第二个SELECT出现在UNION之后，则被标记为UNION;
  • 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED。
• 6、UNION RESULT： 从 UNION 表获取结果的 SELECT。

type (查询使用类型)

从最好到最差依次是：system > const > eq_ref > ref > range > index > ALL
一般来说，保证查询至少达到 range 级别，最好能达到 ref。

• system： 表只有一行记录(等于系统表)，这是const类型的特列，平时不会出现，也可以忽略不计。
• const： 表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快Dr如将主键置于where列表中，MySQL 就能将该查询转换为一个常量。
• eq_ref： 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一 索引扫描。
• ref： 非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
• range： 只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点， 而结束语另- -点，不用扫描全部索引。
• index： Full Index Scan，index 与 ALL 区别是 index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表，但index是 从索引中读取的，而all是 从硬盘中读的)
• ALL： Full Table Scan，将遍历全表以找到匹配的行。

possible_keys (可能使用的索引)

• 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

key (实际使用索引)

实际使用的索引。如果为NULL，则没有使用索引。
查询中若使用了覆盖索引，则该索引仅出现在key列表中。

覆盖索引(Covering Index)

• 就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

注意：

• 如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可用 select *。
• 因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

key_len (索引长度)

• 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_ len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_ Jen是根据表定义计算而得，不是通过表内检索出的。

ref (表之间引用)

• 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

rows (被优化器查询的行数)

• 根据表统计信息，及索引选用情况，大致估算出找到所需的记录所需要读取的行数。（用的越少越好）

Extra (扩展)

包含不适合在其他列中显示，但十分重要的额外信息。

• 1、Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序成为“文件排序”。
• 2、Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询 group by。
• 3、Using index：表示相应的select操作中使用了覆盖索引(Covering Index)， 避免访问了表的数据行，效率不错!如果同时出现 usingwhere,表明索引被用来执行索引键值的查找;如果没有同时出现 usingwhere,表明索引用来读取数据而非执行查找动作。
• 4、Using where：表明使用了 where 过滤。
• 5、Using join buffer：使用了连接缓存。
• 6、Impossible where：where 字句的值总是false，不能用来获取任何元素。
• 7、select tables optimized away：在没有 group by 字句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即完成优化。
• 8、distinct：优化 distinct 操作，在找到第一匹配的元素后即停止找同样值的动作。

四、索引优化

explain + SQL;

• type 是 ALL 需要优化。
• Extra 含有 Using filesort 需要优化。

单表优化：对查询常量值的字段建立相应的索引，对于字段要求 between、<、>、in 会出现range，则不建立索引。
两表优化：左连接，右连接 有一表为驱动表 必为ALL。

• 左连接，索引放右表。
• 右连接，索引放左表。
  三表优化：同两表优化。

1、索引最好设置在需要经常查询的字段中。
2、尽可能减少Join语句中的NestedLoop的循环总次数；
3、永远用小结果集驱动大的结果集。
4、优先优化NestedLoop的内层循环。
5、保证Join语句中被驱动表上Join条件字段已经被索引。
6、当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要吝啬JoinBuffer设置

索引优化规则

• 1、全值匹配。
• 2、最佳左前缀法则：查询从索引的最左前列开始并且不跳过索引中的列。
  • 索引带头大哥不能死，中间兄弟不能断。
• 3、不在索引列上做任何操作，否则会导致索引失效而转向全表扫描。
  • 计算，函数，类型转换(手动or自动) 都会导致索引失效。
• 4、存储引擎不能使用索引中范围条件右边的列。
• 5、尽量使用覆盖索引(只访问索引的查询，索引列和查询列一致)，减少 select * 使用。
• 6、MySQL在使用不等于(!=或者<>的时候无法使用索引会导致全表扫描)。
• 7、is null，is not null 也无法使用索引。
• 8、like ‘%A%’ 通配符MySQL语句(%写两边或左面)，索引失效会变成全表扫描操作。
  • %最好写右面 ‘A%’，type=range
  • 解决 %写两边或左面 索引失效——> 覆盖索引(查询列和索引一致或使用部分索引)
• 9、字符串不加单引号索引失效，因为MySQL底层会对索引列进行类型转换。
• 10、少用 or，用 or 来连接时会索引失效，导致全表扫描。
  • or 连接的是 两个最左索引列 的查询。(name=’a’ or name=’b’)
• 11、order by 后 要和索引的顺序一致。否则会出现 filesort。
  • order by 时，不要用 select *。字段多的话，影响性能。

分析

1、观察，至少跑1天，生产的慢SQL情况。
2、开启慢查询日志，设置阙值，比如超过5秒钟的就是慢SQL,并将它抓取出来。
3、explain+慢SQL分析。
4、show profile
5、进行SQL数据库服务器的参数调优。

总结

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

MySQL支持两种方式的排序：FileSort 和 Index ，Index效率高。
尽可能在索引列完成排序操作 Order by 满足两种情况会使用Index方式排序：

• Order by 语句使用索引最左前列。
• 使用 Where 字句与Order by 子句条件列组合满足索引最左前列。

如果不再索引列上，FileSort 有两种算法：

• 双路排序：两次扫描。
• 单路排序：比双路更优。
  • 单路优化：增大sort_buffer_size、max_length_for_data 参数的设置。

group by 实质是先排序后进行分组，遵照索引 最佳左前缀原则。

慢查询日志

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

运行时间超过 long_query_time 值的SQL，会被记录到慢查询日志中默认关闭，调优需要手动开启。
long_ query_ time的默认值为10，意思是运行10秒以上的语句。由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL,希望能收集超过5秒的sql,结合之前explain进行全面分析。

slow_query_log

1
2
3
4
5
6
7
8
9

<!--如何查看-->
SHOW VARIABLES LIKE '%slow_query_log%';

<!--如何开启 下面命令只对当前数据库有效-->
set global slow_query_log=1;

<!--永久生效 修改my.cnf[mysqld]下增加或修改参数-->
slow_query_log=1
slow_query_log_file=host_name-slow.log

long_query_time

1
2
3
4
5
6
7
8

<!--是大于 > 10秒 而非 大于等于 >= 10-->
SHOW VARIABLES LIKE '%long_query_time%';

<!--设置时间-->
set global long_query_time=3;

<!--设置后可能时间看不到变化 需要用 global 或者重新连接或新开一个会话才能看到修改值-->
show global VARIABLES like 'long_query_time';

Slow_queries

1
2

<!--SQL语句健康查询-->
show global status like 'Slow_queries';

mysqldumpslow –help 慢查询工具

• s：是表示按照何种方式排序。
• c：访问次数。
• l：锁定时间。
• r：返回记录。
• t：查询时间。
• al：平均锁定时间。
• ar：平均返回记录数。
• at：平均查询时间。
• t：即为返回前面多少条的数据。
• g：后边搭配一个正则匹配模式，大小写不敏感的。

Show Profile

1
2
3
4
5
6
7
8
9
10
11

<!--当前MySQL是否支持-->
Show VARIABLES like 'profiling';

<!--开启功能，默认关闭-->
set profiling=on;

<!--查看SQL语句运行时间-->
show profiles

<!--诊断SQL 数字代表 profile 记录SQL的号码-->
show profile cpu,block io for query 1;

status 如果出现以下结论

• converting HEAP to MyISAM：查询结果太大，内存不够用往磁盘上添加了。
• Creating tmp table：创建临时表，拷贝数据到临时表 用完再删除
• Copying to tmp table on disk：把内存中临时表复制到磁盘，危险。
• locked

全局查询日志

永远不要再生产环境开启这个功能

1
2
3
4
5
6
7
8

<!--开启功能，默认关闭-->
set global general_log=1;

<!--输出到表-->
set global log_output='TABLE';

<!--查看-->
select * from mysql.general_log;

索引失效

• 1、如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)，要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引；

• 2、对于多列索引，不是使用的第一部分，则不会使用索引；

• 3、like查询以%开头；

• 4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引；

• 5、如果mysql估计使用全表扫描要比使用索引快,则不使用索引。

五、MySQL锁机制

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

表锁 MyISAM引擎

偏向MyISAM引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
Myisam的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎。

• 因为写锁后其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

加锁：lock table 表名 (read/write);
查看表锁：show open tables;
解锁：unlock tables;
表锁分析：show status like ‘table%’;

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

READ LOCK （读锁 共享锁）

WRITE LOCK （写锁 排他锁）

行锁 InnoDB引擎

偏向InnoDB引擎，开销大加锁慢；会出现死锁；锁定粒度小，发生锁冲突概率最低，并发度最高。
InnoDB：支持事务(Transaction)、采用行锁。

事务的 ACID 属性

原子性（Atomicity）： 是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）： 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。
隔离性（Isolation）： 是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（Durability）： 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响。

数据库的隔离级别

对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题：

• 脏读： 对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还没有被提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的。
• 不可重复读： 对于两个事务 T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段. 之后, T1再次读取同一个字段, 值就不同了。
• 幻读： 对于两个事务 T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行. 之后, 如果 T1 再次读取同一个表, 就会多出几行。

数据库事务的隔离性

• 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。
• 一个事务与其他事务隔离的程度称为隔离级别。不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

Mysql 数据库提供的 4 种事务隔离级别 isolation level

Mysql 默认的事务隔离级别为：REPEATABLE READ (可重复读)

MySql 设置隔离级别

• SELECT @@tx_isolation;
• set [glogal | session] transaction isolation level 隔离级别名称;
• set tx_isolation=’隔离级别名称’;

行锁演示

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况。

1
2
3
4
5
6
7

show status like 'innodb_row_lock%';

Innodb_row_lock_current_waits：当前正在等待锁定的数量。
Innodb_row_lock_time：从系统启动到现在锁定总时间的长度。(重要)
Innodb_row_lock_time_avg：每次等待所花平均时间。(重要)
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间。
Innodb_row_lock_waits：系统启动后到现在总共等待的次数。(重要)

更新同一行数据，不同行无冲突。
session_1|session_2
-|-
关闭自动提交 set autocommit=0;|关闭自动提交 set autocommit=0;
更新但是不提交，没有手动提交 commit;|session_2 被阻塞，只能等待
提交更新 commit;|解除阻塞，更新正常进行
.| commit;

索引失效行锁变表锁

更新不同行数据
session_1|session_2
-|-
关闭自动提交 set autocommit=0;|关闭自动提交 set autocommit=0;
session_1 更新第 1 行，varchar类型不写 '' 单引号|session_2 更新第 2 行，阻塞
commit; 提交|session_2 执行操作
.|commit; 提交

因为varchar类型不写 '' 单引号，导致索引列类型转换，索引失效，导致行锁变表锁。

间隙锁危害

更新不同行数据,但是a没有id为2的数据。
session_1|session_2
-|-
关闭自动提交 set autocommit=0;|关闭自动提交 set autocommit=0;
update Table set b=’001’ where a>1 and a<6;|insert into Table values(2,’002’);
commit; 提交| session_2 执行操作
.|commit; 提交

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

指定锁某一行

页锁 (忽略)

开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

六、主从复制

MySQL复制过程分成三步：

• 1、master 将改变记录到 binary log 。记录过程叫做二进制日志事件，binary log events;
• 2、slave 将 master 的 binary log events 拷贝到它的中继日志(relay log)。
• 3、slave 重做中继日志中的事件，将改变应用到自己的数据库中。异步的且串行化的。

复制基本原则：

• 每个 slave 只有一个 master。
• 每个 slave 只能有一个唯一的服务器ID。
• 每个 master 可以有多个 slave。

一主一从 配置

• 1、处在同一网段，MySQL版本一致且后台以服务运行。
• 2、主从都配置在 [mysqld]节点下，都是小写。
• 3、主机修改（比如在Windows下） my.ini 配置文件
  • 必须 主服务器唯一ID：server-id=1
  • 必须 启动二进制日志：log-bin=自己数据库本地的路径(E:/MySQL/data/mysqlbin)
  • 可选 启动错误日志：log-err=自己数据库本地的路径(E:/MySQL/data/mysqlerr)
  • 可选 根目录：basedir=自己数据库本地的路径(E:/mysql-5.7.24/)
  • 可选 临时目录：tmpdir=自己数据库本地的路径(E:/mysql-5.7.24/)
  • 可选 数据目录：datadir=自己数据库本地的路径(E:/MySQL/data/)
  • 主机，读写都可以：read-only=0
  • 可选 设置不要复制的数据库：binlog-ignore-db=mysql
  • 可选 设置需要复制的数据库：binlog-do-db=mysql
• 4、从机修改 （比如在Linux下）my.cnf 配置文件
  • 必须 主服务器唯一ID：注释 server-id=1，打开 server-id=2
  • 修改完配置文件，需要重启 MySQL。
• 5、主机从机都关闭防火墙。
• 6、在Windows主机上建立账户并授权slave。
  • cd:E:/mysql-5.7.24/bin 进入MySQL。

1
2
3
4
5
6
7
8
9

mysql>GRANT REPLICATION SLAVE ON \*.\* TO 'zhangsan'@'从机数据库IP' IDENTIFIED BY '123456';

//刷新一波
flush privileges;

//查询 master 的状态
show master status;

status 记录下 File 和 Position

• 7、在Linux从机上配置需要复制的主机。

1
2
3
4
5
6
7
8
9
10
11
12

mysql>CHANGE MASTER TO MASTER_HOST='主机IP',
MASTER_USER='zhangsan',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysqlbin.具体数字',MASTER_LOG_POS=具体值;

--> 这里如果出现错误 1198 (run STOP SLAVE first) 说明之前做过同步，先停止 stop slave;

mysql>start slave;
mysql>show slave status\G

Slave_IO_Running:Yes
Slave_SQL_Running:Yes

• 8、主机新建库，新建表，CRUD记录，从机复制。
• 9、停止从机服务复制功能：stop slave;

视图

MySQL 5.1 新特性。通过表数据动态生成的虚拟表，只保存了sql逻辑，不保存查询结果。

应用：

• 1、多个地方用到同样的查询结果。
• 2、该查询结果使用的sql语句较复杂。

1
2
3

create view 视图名
as
查询语句;

感谢阅读

• ← Previous Post
• Next Post →