高性能MySQL我爱编程运维相关

MySQL高级

2018-04-17  本文已影响86人  紫雨杰
MySQL的架构介绍
MySQL的索引优化分析
MySQL的查询截取分析
MySQL的锁机制
MySQL的主从复制

一、MySQL的架构介绍

检查是否安装过MySQL.png 
3、安装MySQL服务端
      安装命令：rpm -ivh MySQL服务端的软件包名   【i表示安装， v表示日志， h表示进度条】
安装MySQL服务端.png 
4、安装MySQL客户端
      安装命令：rpm -ivh MySQL客户端的软件包名   【i表示安装， v表示日志， h表示进度条】
安装MySQL客户端.png 
5、查看MySQL 安装时创建的MySQL用户和MySQL组
      检查MySQL 是否安装成功，有两种方法：
          ①、MySQL 安装成功后，会创建MySQL用户和MySQL组，查看MySQL用户或MySQL组，如果有，则表示安装成功
                 cat /etc/passwd|grep mysql
                 cat /etc/group|grep mysql
          ②、执行 mysqladmin --version命令，如果显示除MySQL的安装版本，则表示安装成功

6、MySQL服务的启 + 停
      service mysql start
      service mysql stop

7、MySQL 服务启动后，开始连接
      ①、首次连接MySQL，只需输入mysql，无需输入密码即可连接成功，因为MySQL默认没有密码。    
      ②、按照安装MySQL 服务端时的提示修改登陆密码：
                  执行命令：/usr/bin/mysqladmin -u root password 123456  即可
      ③、设置登陆密码成功之后，再次连接MySQL 需要输入：mysql -u用户名 -p密码

8、自启动MySQL服务
      ①、设置开机自启动MySQL：chkconfig mysql on
      ②、查看：chkdinfig  --list|grep mysql
      ③、执行：ntsysv  【看到[*] mysql 这一行，表示开机后自启动mysql, 按 F1可获取关于某项服务的详情】
  
9、MySQL的安装位置
      ①、在linux 下查看MySQL 的安装目录：ps -ef|grep mysql
      ②、             路径                                 解释            
                 /var/lib/mysql/                   mysql数据库文件的存放路径
                 /usr/share/mysql                  mysql配置文件目录
                 /usr/bin                          mysql相关命令目录
                 /etc/init.d/mysql                 mysql启停相关脚本
       
10、修改配置文件位置
        5.5 版本：cp /usr/share/mysql/my-huge.cnf  /etc/my.cnf
        5.6 版本：cp /usr/share/mysql/my-default.cnf  /etc/my.cnf
       【注意：版本不一样，配置文件的名称可能不一样】

11、修改字符集和数据存储路径 
        ①、向MySQL中新建数据库，新建表，插入中文数据后，发现乱码，则需要修改复制到etc中的mysql配置文件，向其中添加指定的字符集
        ②、配置文件中有三处需要修改：
                [client] 
                 #password=your_password
                 port=3306
                 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】

                    socket=/var/lib/mysql/mysql.sock
                    skip-external-locking
                    key_buffer_size=384M
                    max_allowed_packet=1M
                    table_open_cache=512
                    sort_buffer_size=2M
                    read_buffer_size=2M
                    read_rnd_buffer_size=8M
                    myisam_sort_buffer_size=64M
                    thread_cache_size=8
                    query_cache_size=32M
                    #Try number of CPU`s*2 for thread_concurrency
                    thread_concurrency=8M

                [mysql]
                    no-auto-rehash
                    【default-character-set=utf8】

         ③、修改配置完成之后，需要重启服务，然后重新创建数据库，重新创建表，因为修改之后的配置文件对修改之前创建的库和表不起作用
MySQL的逻辑架构.png 
  ①、连接层：
         最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。
         在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程，同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

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

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

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

二、MySQL的索引优化分析

SQL手写顺序.png 
        * 机读顺序
SQL机读顺序.png 
         * 总结
SQL执行顺序总结.png 
2、Join图 【七种JOIN的SQL编写】
                       SQL JOINS
  ①、内连接
         SQL：     SELECT  <select_list>  
                   FROM TableA A
                   INNER JOIN TableB B
                   ON A.Key = B.Key;
内连接.png 
  ②、左外连接
         SQL：     SELECT  <select_list>  
                   FROM TableA A
                   LEFT JOIN TableB B
                   ON A.Key = B.Key;
左外连接.png 
  ③、右外连接
         SQL：     SELECT  <select_list>  
                   FROM TableA A
                   RIGHT JOIN TableB B
                   ON A.Key = B.Key;
右外连接.png 
  ④、左外连接去交集
         SQL：     SELECT  <select_list>  
                   FROM TableA A
                   LEFT JOIN TableB B
                   ON A.Key = B.Key
                   WHERE B.Key IS  NULL;
左外连接去交集.png 
  ⑤、右外连接去交集
         SQL：     SELECT  <select_list>  
                   FROM TableA A
                   RIGHT JOIN TableB B
                   ON A.Key = B.Key
                   WHERE A.Key IS  NULL;
右外连接去交集.png 
  ⑥、全外连接 【注意：MySQL不支持全外连接】
         SQL：     SELECT  <select_list>  
                   FROM TableA A
                   FULL OUTER JOIN TableB B
                   ON A.Key = B.Key;
       
         因为MySQL不支持，所以需要使用 UNION 联合查询，【UNION 可以去重】：
                   SELECT  <select_list>  
                   FROM TableA A
                   LEFT JOIN TableB B
                   ON A.Key = B.Key              
             UNION
                   SELECT  <select_list>  
                   FROM TableA A
                   ROGHT JOIN TableB B
                   ON A.Key = B.Key；
全外连接.png 
    ⑦、全外连接去交集【注意：MySQL不支持全外连接】 
         SQL：     SELECT  <select_list>  
                   FROM TableA A
                   FULL OUTER JOIN TableB B
                   ON A.Key = B.Key
                   WHERE A.Key IS NULL OR B.Key IS NULL;

         因为MySQL不支持，所以需要使用 UNION 联合查询，【UNION 可以去重】：
                   SELECT  <select_list>  
                   FROM TableA A
                   LEFT JOIN TableB B
                   ON A.Key = B.Key   
                   WHERE B.Key IS NULL           
             UNION
                   SELECT  <select_list>  
                   FROM TableA A
                   ROGHT JOIN TableB B
                   ON A.Key = B.Key
                   WHERE A.Key IS NULL；
全外连接去交集.png 索引图.png 
                    左表是数据表，一共有两列七条记录，最左边的是数据记录的物理地址
               
                 为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，
        这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

             结论：数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

      ③、一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上

      ④、平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉树)结构组织的索引。
         其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引(hash index)等。

  2、索引的优势
        ①、类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本；
        ②、通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

  3、索引的劣势
        ①、实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的；
        ②、虽然索引大大提高了查询速度，同时却会降低更新表的速度
           如：对表进行 INSERT、UPDATE、DELETE，因为更新表时，MySQL不仅要保存数据，
           还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
        ③、索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

  4、MySQL索引分类 【复合索引优于单值索引】
        ①、单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
        ②、唯一索引：索引列的值必须唯一，但允许有空值
        ③、复合索引：即一个索引包含多个列

        ④、基本语法：
                创建：
                   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命令
                  有四种方式来添加数据表的索引：
                       ALTER TABLE tbl_name ADD PRIMARY KEY(column_list)：该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
                       ALTER TABLE tbl_name ADD UNIQUE index_name(column_list)：该语句创建索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)
                       ALTER TABLE tbl_name ADD INDEX index_name(column_list)：添加普通索引，索引值可出现多次
                       ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list)：该语句指定了索引为 FULLTEXT，用于全文索引

  5、MySQL索引结构
        ①、BTree索引
             检索原理
检索原理图.png 
       【初始化介绍】
        一颗B+树，浅蓝色的块我们称为一个磁盘块，可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示)，
    如磁盘块1包含数据项17和35，包含指针P1,P2,P3,P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
    真实的数据存在于叶子节点：即3、5、9、10、13、15、28、29、36、60、75、79、90、99.
    非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17,35并不真实存在于数据表中。
    
       【查找过程】
       如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，
   锁定磁盘块1的P2指针，内存时间因为非常短(相比磁盘的IO)可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，
   发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO.
    
       真实的情况是：3层的B+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高是巨大的，如果没有索引，
   每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

        ②、Hash索引
        ③、full-text 全文索引
        ④、R-Tree 索引
        
  6、哪些情况下需要创建索引
        ①、主键自动建立唯一索引
        ②、频繁作为查询条件的字段应该创建索引
        ③、查询中与其它表关联的字段，外键关系建立索引
        ④、频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录，还会更新索引，加重了IO负担
        ⑤、where条件里用不到的字段不创建索引
        ⑥、单键/组合索引的选择问题，who? (在高并发下倾向创建组合索引)
        ⑦、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度           
        ⑧、查询中统计或者分组字段

  7、哪些情况下不需要创建索引
        ①、表记录太少
        ②、经常增删改的表
           原因：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
        ③、数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。
           注意：如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

 【假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种话表A字段建索引一般不会提高数据库的查询速度。
   索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99.
    一个索引的选择性越接近于1，这个索引的效率就越高。】
Explain.png 
             select_type
               (1)、有六个：
                                    id        select_type
                                     1         SIMPLE
                                     2         PRIMARY
                                     3         SUBQUERY
                                     4         DERIVED
                                     5         UNION 
                                     6         UNION RESULT
               (2)、查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
                     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

             table
                  显示这一行的数据是关于哪张表的

             type【显示查询使用了何种类型】
               (1)、type有8种值：
                     ALL、index、range、ref、eq_ref、const,system、NULL

               (2)、访问类型排列：
                     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
                    一般来说，得保证查询至少达到range级别，最好能达到ref

               (3)、各种类型的解释：
                    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,将遍历全表以找到匹配的行
                   
             possible_keys
                (1)、显示可能应用在这张表中的索引，一个或多个
                     查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

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

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

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

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

             Extra
                (1)、包含不适合在其它列中显示但十分重要的额外信息
                    1、* Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
                           MySQL中无法利用索引完成的排序操作称为"文件排序"
Using filesort.png 
                    2、* Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表，
                          常见于排序order by 和分组查询group by
using temporary.png 
                    3、USING index：表示相应的SELECT操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
                       -  如果同时出现using where，表明索引被用来执行索引键值的查找；
                       -  如果没有同时出现using where，表明索引用来读取数据而非执行查找操作
                       - 覆盖索引(索引覆盖)：
                             就是select的数据列只用从索引中就能取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，
                          而不必根据索引再次读取数据文件，即建的索引name,age，select 查找的字段刚到也是name，age。
                    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操作，在找到第一匹配的元素后即停止找同样值的动作

三、MySQL的查询截取分析

四、MySQL的锁机制

如何锁定一行.png 
      ⑤、案例总结：
              InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，
          但是在整体并发处理能力方面要远远优于MyISAM的表级锁定。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。
          但是，InnoDB的行级锁定同样也有弱点，当使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

      ⑥、如何分析行锁定？
            通过 show status like 'innodb_row_lock%'；检查状态变量来分析系统上的行锁的争夺情况
            各个状态变量的说明如下：
                innodb_row_lock_current_waits：当前正在等待锁定的数量；
                innodb_row_lock_current_waits：当前正在等待锁定的数量；
                *  innodb_row_lock_time：从系统启动到现在锁定总时间长度；
                *  innodb_row_lock_time_avg：每次等待所花平均时间；
                innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；
                *  innodb_row_lock_waits：系统启动后到现在总共等待的次数；

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

      ⑦、优化建议：
            尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
            合理设计索引，尽量缩小锁的范围
            尽可能较少检索条件，避免间隙锁
            尽量控制事务大小，减少锁定资源量和时间长度
            尽可能低级别事务隔离
   
 3、页锁
      ①、特点：
          - 开销和加锁时间介于表锁和行锁之间；
          - 会出现死锁；
          - 锁定粒度介于表锁和行锁之间，并发度一般

五、MySQL的主从复制

主从复制.png 
 9、主机新建库，新建表，INSERT记录，从机复制
 10、如何停止从服务复制功能： stop slave;
上一篇 下一篇

猜你喜欢

热点阅读