Mysql高级

一、Mysql架构介绍

第一种Linux安装MySQL

把安装包放到opt目录下
查看是否安装过MySQL

先安装server

root@hadoop2 opt]# rpm -ivh MySQL-server-5.5.48-1.linux2.6.i386.rpm --force --nodeps

安装客户端

rpm -ivh MySQL-client-5.5.48-1.linux2.6.i386.rpm --force --nodeps

查看MySQL的组，表示安装成功了

[root@hadoop2 opt]# cat /etc/passwd|grep mysql
mysql:x:496:493:MySQL server:/var/lib/mysql:/bin/bash

查看是否安装成功

mysqladmin --version

启动MySQL

service mysql start 

关闭服务

service mysql stop 

密码设置和开机自启动

给root用户设置密码

/usr/bin/mysqladmin -u root password 123456

启动有密码的MySQL

[root@yhn opt] mysql -u root -p
Enter password:123456

设置开机自启动

chkconfig mysql on

ntsysv

看到有*表示开机自启动成功

ps -ef|grep mysql

修改配置文件

修改字符集

连接数据库

创建库

建表

插入数据

插入中文乱码
通过修改字符集解决同 下

第二种安装方式

安装gcc环境

[root@root opt]# yum -y install make gcc-c++ cmake bison-devel ncurses-devel

把数据库文件传到opt目录下解压

[root@hadoop2 mysql-5.6.14]# tar xvf mysql-5.6.14.tar.gz 

进入mysql目录下

编译安装[源码=》编译]

cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DSYSCONFDIR=/etc -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock -DMYSQL_TCP_PORT=3306 -DENABLED_LOCAL_INFILE=1 -DWITH_PARTITION_STORAGE_ENGINE=1 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci

编译并安装

make && make install

配置MySQL

设置权限

使用下面的命令查看是否有mysql用户及用户组

cat /etc/passwd 查看用户列表
cat /etc/group  查看用户组列表

如果没有就创建

groupadd mysql
useradd -g mysql mysql

修改/usr/local/mysql权限

chown -R mysql:mysql /usr/local/mysql

初始化配置，进入安装路径（在执行下面的指令），执行初始化配置脚本

cd /usr/local/mysql
scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql    [这是一条指令]

注：在启动MySQL服务时，会按照一定次序搜索my.cnf，先在/etc目录下找，找不到则会搜索"$basedir/my.cnf"，在本例中就是安装目录 /usr/local/mysql/my.cnf，这是新版MySQL的配置文件的默认位置！
注意：在CentOS 6.8版操作系统的最小安装完成后，在/etc目录下会存在一个my.cnf，需要将此文件更名为其他的名字，如：/etc/my.cnf.bak，否则，该文件会干扰源码安装的MySQL的正确配置，造成无法启动。
修改名称，防止干扰：

mv /etc/my.cnf /etc/my.cnf.bak

启动MySQL
添加服务，拷贝服务脚本到init.d目录，并设置开机启动
[注意在 /usr/local/mysql 下执行]

[root@hadoop2 mysql]# cp support-files/mysql.server /etc/init.d/mysql  拷贝配置文件
cp：是否覆盖"/etc/init.d/mysql"？ y   要输入y然后回车不然启动服务出现下面的错误

[root@hadoop2 mysql]# service mysql start
/etc/init.d/mysql: /usr/bin/my_print_defaults: /lib/ld-linux.so.2: bad ELF interpreter: No such file or directory
Starting MySQL.The server quit without updating PID file (/var/lib/mysql/hadoop2.pid).                     [失败]

 
chkconfig mysql on   设置为自启动，以后开机就自己启动
netstat -anp | more  启动服务后3306端口被监听
service mysql start  --手动启动MySQL服务

执行下面的命令修改root密码

cd /usr/local/mysql/bin

[root@hadoop2 bin]# ./mysql -u root  -p  打开MySQL然后看见输入密码回车，刚开始密码为空

mysql> SET PASSWORD = PASSWORD('123456');

mysql>quit 退出

简单使用：

mysql>create database yhndb; 创建库,注意 ；不要丢
mysql> use yhndb
Database changed
mysql> create table user(id int,name varchar(32));  创建表
Query OK, 0 rows affected (0.11 sec)

mysql> insert into user values(100,'tine'); 插入
Query OK, 1 row affected (0.01 sec)

mysql> insert into user values(230,'jecke');
Query OK, 1 row affected (0.09 sec)
mysql> select * from user;
+------+-------+
| id   | name  |
+------+-------+
|  100 | tine  |
|  230 | jecke |
+------+-------+
2 rows in set (0.08 sec)
mysql> Ctrl-C -- exit!

原来只能在/usr/local/mysql/bin里面使用./mysql -u root -p来启动MySQL软件
现在要配置环境变量在opt下也能使用MySQL通过mysql -u root -p启动

[root@hadoop2 bin]# vim /etc/profile  打开文件在最后加入下面的环境变量

PATH=/usr/local/mysql/bin:$PATH

[root@hadoop2 bin]# cd /opt
[root@hadoop2 opt]# mysql  
-bash: /usr/bin/mysql: /lib/ld-linux.so.2: bad ELF interpreter: 没有那个文件或目录
[root@hadoop2 opt]# source /etc/profile 要刷新一下文件
[root@hadoop2 opt]# mysql 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)            这次就有MySQL了
[root@hadoop2 opt]# mysql -u root -p
Enter password:    输入密码

mysql>               进入MySQL了

修改字符集

socket          = /var/lib/mysql/mysql.sock

default-character-set=utf8

[mysqld]
port            = 3306

character_set_server=utf8
character_set_client=utf8
collation-server=utf8_general_ci

[mysql]
no-auto-rehash

default-character-set=utf8  不能加斜杠

之前建的库可能还是乱码，重新建一个库

mysql配置文件

主要配置文件

• 二进制日志log-bin
  • 主从复制
• 错误日志log-error
  • 默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息等。
• 查询日志log
  • 默认关闭，记录查询的sql语句，如果开启会降低mysql的整体性能，因为记录日志也是需要消耗系统资源的。
• 数据文件
• 两系统
  • windows：D:\devSoft\MySQLServer5.5\data目录下可以挑选很多库
  • linux
    • 看看当前系统中的全部库后再进去
    • 默认路径：/var/lib/mysql
• frm文件：存放表结构
• myd文件：存放表数据
• myi文件：存放表索引

如何配置
windows：my.ini文件
Linux：/etc/my.cnf文件

mysql逻辑架构介绍

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，**插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。**这种架构可以根据业务的需求和时机需要选择合适的存储引擎。

sql interface : 关键词
parser：解析转换
optimizer：优化器
caches & buffers: 缓存和缓冲

• 从上到下，连接层，服务层，引擎层，存储层
  

MySQL存储引擎

查看所有引擎

mysql> show engines;

查看当前使用的引擎

mysql> show variables like '%storage_engine%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_storage_engine     | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine             | InnoDB |
+----------------------------+--------+
3 rows in set (0.01 sec)

MyISAM和InnoDB

阿里巴巴、淘宝用哪个

二、 索引优化分析

select * from user where name='yhn' ;
创建单值索引
create index idx_user_name on user(name);

select * from user where name='yhn' and email ='1777788888@qqq.com';
创建复合索引
create index idx_user_nameEmail on user(name,email);

性能下降SQL慢

执行时间长，等待时间长

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

常见通用的Join查询

• SQL执行顺序
  • 手写
    
  • 机读
    
• 总结
  
• Join图-7种JOIN
  




mysql中不支持full outer join 全外连接，可以用下面的方式代替。
union 是合并并去重

各自独有

索引简介

• 1、是什么

  • MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。
    
  • 你可以简单理解为“排好序的快速查找数据结构”。

    • 详解（B树）
      

    • 结论

      • 数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
  • 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
  • **我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。**其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认的都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引（hash
    index）等。

• 2、优势

  • 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。

  • 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

• 3、劣势

  • 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

  • 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或者优化查询。

• 4、mysql索引分类

  • 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

  • 唯一索引：索引列的值必须唯一，但允许有空值

  • 复合索引：即一个索引包含多个列

• 5、基本语法

  • 创建：create [unique] index indexname on mytable(columnname(length));
    alter mytable add [unique] index [indexname] on (columnname(length))

    • 如果是char，varchar类型，length可以小于字段实际长度；如果是blob和text类型，必须指定length。

  • 删除：drop index [indexname] on mytable;

  • 查看：show index from table_name\G

  • 使用alter命令
    

• 6、mysql索引结构

  • B+树索引

    • 索引原理
      
      

  • Hash索引

  • full-text全文索引

  • R-Tree索引

• 7、哪些情况需要创建索引

  • 主键自动建立唯一索引

  • 频繁作为查询条件的字段应该创建索引

  • 查询中与其它表关联的字段，外键关系建立索引

  • 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录，还会更新索引，加重IO负担 where条件里用不到的字段不创建索引

  • 单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引）

  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

  • 查询中统计或者分组字段

• 8、哪些情况不需要创建索引

  • 表记录太少

  • 经常增删改的表

    • Why：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

  • 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
    

性能分析

• MySQL Query Optimizer

• MySQL常见瓶颈

  • CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
  • IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态

• Explain

  • 是什么（查看执行计划）

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

  • 能干嘛

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

  • 怎么玩

    • Explain+SQL语句

    • 执行计划包含的信息
      

  • 各字段解释

    • id

      • select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
      • 三种情况：
        • id相同，执行顺序由上至下
        • id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
        • id相同不同，同时存在
      • 衍生：DERIVED

    • select_type：

      • 有哪些
        

      • 查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询

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

    • table：显示这一行的数据是关于哪些表的。

    • type：
      

      • 访问类型排序

        • type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
          system>const>eq_ref>ref>range>index>ALL

      • system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。

      • const：表示通过索引一次就找到了，const用于比较primary
        key或则unique索引。因为只匹配一行数据，所以很快。如将主键置于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，将遍历全表以找到匹配的行。

      • 一般来说，得保证查询至少达到range级别，最好能达到ref。

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

    • key：实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中，不会出现在possible_keys列表中。（覆盖索引：查询的字段与建立的复合索引的个数一一吻合）

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

    • ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。查询中与其它表关联的字段，外键关系建立索引。
      

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

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

      • Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为“文件排序”。\G表示竖着显示
        

      • Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
        
        注：order by和group by最好按照索引顺序来

      • Using index：表示相应的select操作中使用了覆盖索引（Covering
        Index），避免访问了表的数据行，效率不错！如果同时出现using
        where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

        覆盖索引：建的是col1 col2复合索引，使用的也是这个两个字段为覆盖索引，或者部分满足。
        
        

		 - Using where：表明使用了where过滤。
		
		 - Using join buffer：使用了连接缓存。
		
		 - impossible where：where子句的值总是false，不能用来获取任何元组。（查询语句中where的条件不可能被满足，恒为False）
		
		 - select tables optimized
		   away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
		
		 - distinct：优化distinct操作，在找到第一匹配的元组后即停止找相同值的动作。

	 - 热身Case

索引优化–索引分析

单表

• 索引分析

  • 建表SQL
    
    
    

     - 创建索引
    

		 - 再次查询，只是摆平了全表扫描，还有filesort

	     - 把>改为=，常量比范围更精确

		 - 没有优化前的索引分析

• 开始优化
  • 先删除索引

• 优化

两表优化案例

• 创建表

• 开始explain分析

结论type有all

• 建索引优化
  
  
• 把原来索引删除换索引 优化，把索引加在class表
  
  

不改第一种方式的索引，通过对调查找时候表的位置也能达到第二种的效果

• 使用右外连接

三表优化案例分析

• 创建表

• 开始explain分析

• 建索引

class的索引之前建过

• 结论：Join语句的优化

  • 尽可能减少Join语句 的NestedLoop的循环次数：“永远是小结果驱动大的结果集”；
  • 优先优化NestedLoop的内层循环
  • 保证Join语句中被驱动表上Join条件字段已经被索引；
  • 当无法保证被驱动表的Join条件字段被索引且内存资源充足的条件下，不要太吝啬JoinBuffer的设置。

索引优化–索引失效

• 建表sql

• 案例（索引失效）

  1、全值索引我最爱

  
  索引内的三个值都使用到了
  

  2、最佳左前缀法则：

  • 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。（带头大哥不能死，中间兄弟不能断哈哈哈）
    没有用到索引断层了，要是没有开头的name,索引就失效了

之间兄弟不能断

3、不在索引列上作任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

select * from staffs where name=2000;
name是字符串类型，不加引号MySQL在底层会进行类型转换，导致索引失效
4、存储引擎不能使用索引中范围条件右边的列

使用>之后age用于排序，而不是像name用于查找。二三条key_len为78表示pos没有用到
中间兄弟别搞范围，要搞等值
5、尽量使用索引覆盖（只访问索引的查询（索引列和查询列一致）。减少select * **
![
按需取数据，用多少取多少，尽量与索引一致
Extra中出现了using index很好！
6、MySQL在使用不等于的时候无法使用索引会导致全表扫描

7、is null，is not null 也无法使用索引

8、like以通配符开头（’%abc…’)MySQL索引失效会变成全表扫描

• like%加右边
• 问题：解决like ‘%字符串%’时索引不被使用的方法？

创建表

没有建索引都是全表扫描

创建索引

下面这些要么全部吻合或者沾边的都会用到索引


使用*或者字段名超过索引里的字段名，也会让索引失效。
id是主键索引不用管，Email是索引里没有的会造成索引失效

 - 利用覆盖索引解决两边%的优化问题。建的索引和查的字段在顺序个数上最好完全一致

9、字符串（varchar类型)不加单引号索引失效

10、少用or，用它来连接时索引会失效
11、小总结

优化总结口诀：

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用；

VAR引号不可丢，SQL高级也不难！

面试题讲解

• 建表
  
• 创建索引
  
• 问题

• 分析
  情况1：1，1 2，1 2 3 都会用索引
  情况2：1234,1243都会用到索引，4321会进行性能调优自动转换也会用索引
  

情况3：c1 c2可以用到 c3只能勉强用到一部分只能用到一部分只能用于排序不能用于查找，c4用不到

情况4:虽然这样写但是底层执行还是会改到 1234这样，4后面没有了失效就失效了

情况5：用到了c1 c2
c3的作用在排序而不是查找，用到了但是没有统计在结果中
索引的作用是查找和排序

情况6：和上面一样

情况7：中间少了三，没有三楼。根据c4排序，MySQL内部产生内排序Usering filesort

情况8：只用c1一个字段索引，但是c2 c3用于排序无filesort

情况9：只用c1一个字段索引，通过c3 c2 排序会产生filesort

情况10：会正常用到索引，用c1,c2两个字段索引，但是c2,c3用于排序无filesort

情况11：和c5没有关系会正常用到索引,用c1,c2两个字段索引，但是c2,c3用于排序无filesort内排序

情况12：order by只要你不和索引的顺序一样基本都会产生filesort,但是当c2为一个常量的时候，by c3 c2就相当于只有一个by c3不会产生filesort

情况12：用的是group by：c2,c3分组，只用上了c1

情况13：按c3 c2分组顺序和索引不一致出现filesort,temporary

• 定值、范围还是排序，一般order by是给个范围

• group by基本上都需要进行排序，会有临时表产生。分组前要先排序，只不过分组多个having

• 一般性建议

  • 对于单键索引，尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
  • 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

三. 查询截取分析

• 分析

  1、 观察，至少跑1天，看看生产的慢SQL情况。

  2、开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
  3、explain+慢SQL分析
  4、show profile
  5、运维经理 or DBA，进行SQL数据库服务器参数调优。

• 总结

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

查询优化

永远小表驱动大表，类似嵌套循环Nested Loop

一般都使用第一种方式

- 优化原则：小表驱动大表，即小的数据集驱动大的数据集。

- EXISTS
- SELECT … FROM table WHERE EXISTS(subquery)

		 - 该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。
	- 提示
		 - EXISTS（subquery）只返回TRUE或FALSE，因此子查询中的SELECT *也可以是SELECT 1或SELECT
		   ‘X’，官方说法是实际执行时会忽略SELECT清单，因此没有区别。
		 - EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题。
		 - EXISTS子查询往往也可以用条件表达式/其他子查询或者JOIN来替代，何种最优需要具体问题具体分析。
	 - 总结

ORDER BY关键字优化

• 1、ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序
  • 建表SQL

• case1
  
  分析order by会不会产生filesort
  前两个案例order by里面都有age带头大哥不会产生filesort 第三个案例order by里没有age会产生filesort.第四个里面顺序不对也会产生filesort

• case2
  第一个第二个里面 没有带头大哥，第四个order by默认是升序
  

  • MySQL支持两种方式的排序

    • FileSort和Index，Index效率高。FileSort方式效率较低。
    • Using Index，它指MySQL扫描索引本身完成排序。

  • ORDER BY满足两种情况，会使用Index方式排序：
    - ORDER BY语句使用索引最左前列
    - 使用Where子句与ORDER BY子句条件列组合满足索引最左前列

• 2、尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

• 3、如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

  • 双路排序
    • MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据。读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。
    • 从磁盘取排序字段，在buffer进行排序，再从磁盘读取其他字段。
  • 取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序
  • 单路排序
    • 从磁盘读取查询需要的所有列，按照order
      by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间。
  • 结论及引申出的问题
    • 由于单路是后出的，总体而言好过双路
    • 但是用单路有问题
      

• 优化策略

  • 增大sort_buffer_size参数的设置
  • 增大max_length_for_sort_data参数的设置
  • Why
    

• 小总结

group by关键字优化

• group by实质是先排序后进行分组，遵照索引建的最佳左前缀。
• 当无法使用索引列，增大
• max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置。
• where高于having，能写在where限定的条件就不要去having限定了。

慢查询

• 是什么

  • MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
  • long_query_time的默认值是10，意思是运行10秒以上的语句。
  • 由它来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前的explain进行全面分析。

• 怎么玩

• 说明

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

• 查看是否开启及如何开启

  • 默认：SHOW VARIABLES LIKE ‘%slow_query_log%’;
    
  • 开启：set global slow_query_log=1;
    
    

• 那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢
  

• Case

  • 查看当前多少秒算慢：SHOW VARIABLES LIKE ‘long_query_time%’;
  • 设置慢的阈值时间：set global long_query_time=3;
  • 为什么设置后看不出变化（设置3之后，查询依然显示10）：
    • 需要重新连接或新开一个会话才能看到修改值。
    • SHOW VARIABLES LIEK ‘long_query_time%’;
    • show global variables like ‘long_query_time’;
  • 记录慢SQL并后续分析
    

• 查询当前系统中有多少条慢查询记录：
  show global status like ‘%Slow_queries%’;

• 配置版
  

• 日志分析工具mysqldumpslow

  • 在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
  • 查看mysqldumpslow的帮助信息
    • mysqldumpslow --help
    • s：是表示按照何种方式排序
    • c：访问次数
    • I：锁定时间
    • r：返回记录
    • t：查询时间
    • al：平均锁定时间
    • ar：平均返回记录数
    • at：平均查询时间
    • t：即为返回前面多少条的数据
    • g：后边搭配一个正则匹配模式，大小写不敏感
  • 工作常用参考
    

批量数据脚本

• 往表里插入1000w数据
• 建表
  
  
• 设置参数log_bin_trust_function_creators

• 创建函数，保证每条数据都不同

  • 随机产生字符串，让$$代替;结束
    
  • 随机产生部门编号
    
    

• 创建存储过程

  • 创建往emp表中插入数据的存储过程
    

  • 往dept表中添加随机数据
    
    delimiter; 恢复;结束语句。
    call insert_dept(100,10);调用存储过程插入数据，里面调用了之前定义的随机字符串函数
    
    

• 调用存储过程插入500000数据

Show Profile 进行sql分析

• 是什么：是mysql提供的可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量

• 默认情况下，参数处于关闭状态，并保存最近15次的运行结果

• 分析步骤
  1、是否支持，看看当前的mysql版本是否支持

  • show variables like ‘profiling’;

    

2、开启功能，默认是关闭，使用前需要开启
- set profiling = on;

3、运行SQL

• select * from emp group by id%10 limit 150000;
• select * from emp group by id%20 order by 5;

4、查看结果，show profiles; 查看执行过的sql

5、诊断SQL，show profile cpu, block io for query [上一步前面的问题SQL数字号码];

• 参数备注
  
  6、日常开发出现下面status时需要注意的结论
• converting HEAP to MyISAM：查询结果太大，内存都不够用了往磁盘上搬了
• Creating tmp table：创建临时表
  • 拷贝数据到临时表
  • 用完再删除
• Copying to tmp table on disk：把内存中临时表复制到磁盘，危险！
• locked

全局查询日志

• 配置启用
  

• 编码启用
  

• 永远不要在生产环境开启这个功能！推荐使用show profile查功能更强大

四、MySQL锁机制

概述

• 定义
  

• 生活购物
  

• 锁的分类

  • 从对数据操作的类型（读/写）分

    • 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

    • 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

  • 从对数据操作的粒度分

    • 表锁
    • 行锁

三锁

• 开销、加锁速度、死锁、粒度、并发性能
• 只能就具体应用的特点来说那种锁更合适

表锁（偏读）

• 特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

• 案例分析

  • 建表SQL
    

  • 手动增加表锁

    • lock table 表名字 read(write), 表名字2 read(write), 其他;
    • lock table mylock read ,book writer;

  • 查看表上加过的锁

    • show open tables;

  • 释放表

    • unlock tables;

  • 加读锁（我们为mylock表加read锁（读阻塞写例子））

    • 在session1 中给mylock表加读锁,在session1中读mylock可以，但是不能改mylock，读别的表也不行
    • session2读查mylock可以，读锁是共享的，读写其他表也可以。写mylock会阻塞，当在session1中给mylock解锁，session2
      
      
      

  • 加写锁（我们为mylock表加write锁（MyISAM存储引擎的写阻塞读例子））

    • 在session1中给mylock加写锁，session1中可以读写mylock,不能读其他锁
    • 在session2中可以查其他没有加锁的表，查mylock会阻塞，在session中解锁之后才能查出来
      
      

• 案例结论

  • 简而言之，就是读锁会阻塞写，但是不会堵塞读，而写锁则会把读写都堵塞。
    

• 表锁分析

  • 看看哪些表被加锁了：show open tables;有1的是锁了

  • 如何分析表锁定：可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定。

    • show status like ‘table%’;
      

    • 这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量的说明如下：

      • Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1；

      • Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况。

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

行锁（偏写）

• 特点

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

  • Innodb与MyISAM的最大不同有两点：

    • 一是支持事务（TRANSACTION）
    • 二是采用了行级锁

• 由于行锁支持事务，复习老知识

  • 事务（Transaction）及其ACID属性
    

  • 并发事务处理带来的问题

    • 更新丢失（Lost Update）
      

    • 脏读（Dirty Reads）

    • 不可重复读（Non-Repeatable Reads）
      

    • 幻读（Phantom Reads
      

  • 事务隔离级别
    

• 案例分析

  • 建表sql
    
    为a b两个字段创建了两个单值索引

  • 行锁定 基本演示

    • 两个session中都设置为手动提交事务，MySQL中事务隔离级别是可重复读，避免了脏读，读不到没有提交的数据。两边都提交之后session2查找到a=4时b=4001。
    • 图2在session1中修改a=4时b的数据，之后没有提交，再在session2中修改a=4时b的数据会堵塞 ，在session1 commit提交之后session2再修改数据。session2中也提交之后数据都变为了4003.
    • 图3 session1中修改a-=4,session2中修改a=9这样互相不影响。
      
      

  • 无索引 行锁升级为表锁

    • 如果在更新数据的时候出现了强制类型转换导致索引失效，使得行锁变表锁，即在操作不同行的时候，会出现阻塞的现象。
    • b=4000没有加’ ',所以在session2 查操作不同行时也会出现阻塞

  

  • 间隙锁危害

    • 原来表中没有a =2这个数据，当使用范围条件1<a<6的时候，a=2使用间隙，innodb会给这个间隙也加锁，所以session2中插入a=2数据的时候会阻塞。session1提交之后才执行。
      
      
      

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

    • 危害：

      • 因为Query执行过程中通过范围查找的话，会锁定整个范围内所有的索引键值，即使这个键值并不存在。

      • 间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

  • 面试题：常考如何锁定一行

    • select * from 表 where 某一行的条件 for update;
      

  • 行锁案例结论

    • InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。

    • 但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

  • 行锁分析

    • 如何分析行锁定

      • 通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
      • 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：系统启动后到现在总共等待的次数。

    • 对于这5个变量，比较重要的是

      • innodb_row_lock_time_avg（等待平均时长）

      • innodb_row_lock_waits（等待总次数）

      • innodb_row_lock_time（等待总时长）

      • 这三项，尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

    • 优化建议

      • 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。

      • 合理设计索引，尽量缩小锁的范围。

      • 尽可能减少索引条件，避免间隙锁。

      • 尽量控制事务大小，减少锁定资源量和时间长度。

      • 尽可能低级别事务隔离。

页锁

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

五、主从复制

复制的基本原理

• slave会从master读取binlog来进行数据同步

• 三步骤+原理图
  

复制的基本原则

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

复制的最大问题

延时

一主一从常见配置

• MySQL版本一致后台以服务运行

• 主从配置在[mysqlId]节点下，都是小写

• 主机修改my.ini配置文件

  • ipconfig查看Windows的IP地址。ifconfig查看Linux的ip地址。在Linux ping windows的ip地址192.168.226.1。在windows ping Linux 的ip地址 192.168.59.130 实现双向ping通
  • 【必须】主服务器唯一ID
    • server-id=1
  • 【必须】启用二进制日志

    • log-bin=自己本地的路径/mysqlbin
      

    • 【可选】启用错误日志

      • log-err=自己本地的路径/mysqlerr
        

    • 【可选】根目录

      • basedir=自己本地路径
        

    • 可选】临时目录

      • temdir=自己本地路径
        

    • 【可选】数据目录

      • datadir=自己本地路径/Data/
        

    • read-only=0

      • 主机，读写都可以

    • 【可选】设置不要复制的数据库

      • binlog-ignore-db=mysql

    • 【可选】设置需要复制的数据库

      • binlog-do-db=需要复制的主数据库名字

• 从机修改my.cnf配置文件[linux]

  • 【必须】从服务器唯一ID

    • server-id=2

  • 【可选】启用二进制日志
    

• 因修改过配置文件，请主机+从机都重启后台mysql服务

• 主机从机都关闭防火墙

  • windows手动关闭

  • 关闭虚拟机linux防火墙：service iptables stop

• 在Windows主机上建立账户并授权slave\

  • C:\Users\17788>mysql -u root -p 进入MySQL

  • 授权-------------复制 ---------给从机 ----------用户名---------【从机数据库IP】----------------密码

  • GRANT REPLICATION SLAVE ON . TO ‘yhn’@‘192.168.59.130’ IDENTIFIED BY ‘123456’;(在编写模式复制)

  • flush privileges;

  • 查询master的状态

    • show master status
      
    • 记录下File和Position的值

  • 执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

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

  • CHANGE MASTER TO MASTER_HOST=‘主机IP’, MASTER_USER=‘zhangsan’,
    MASTER_PASSWORD=‘123456’, MASTER_LOG_FILE=‘file名字’,
    MASTER_LOG_POS=position数字;
    
    eg.
    

• 启动从服务器复制功能

  • start slave;

  • show slave status\G【\G是为了以键值的形式显示，好看一些】

• 下面两个参数都是Yes，则说明主从配置成功！

  • Slave_IO_Running：Yes

  • Slave_SQL_Running：Yes
    

• 主机新建库、新建表、insert记录，从机复制

  • 如何停止从服务复制功能

  • stop slave;

问题原因：mysql服务没有安装。

解决办法： 在 mysql bin目录下 以管理员的权限 执行 mysqld -install命令

然后仍然以管理员的权限 net start mysql 开启Mysql服务了。

附卸载mysql服务的方法。

1、以管理员的权限 net stop mysql ，关闭mysql服务

2、以管理员的权限 mysqld -remove ，卸载mysql服务

3、重新安装 执行 mysqld -install命令
show variables like ‘log%’;查看是否使用了日志