MySQL3--数据库优化：索引、主从复制、集群、锁

一、数据库优化思想

 1、SQL性能下降原因

•  查询语句不好
• 索引失效：单值，复合
• 关联查询太多（jion）
• 服务器各个参数设置待调优（缓冲、线程数等）

   执行时间长，等待时间长

2、数据库调优

（1）数据类型

• 尽量适用能正确存储数据的最小数据类型（更快）
• 尽可能只用简单数据类型（更少的cpu周期）

        如整数比字符操作代价低

           日期和时间用date、time和datetime存储

            ip地址用整形存储        

           ，而不是字符串

• 尽量避免null，列在设计时设置为not null

（2）调优低效SQL语句 

•   优化关联查询，尽量少使用jion

          使用的时候确保on或using条件中的列上有索引

•  优化group by

       确保group by和order by 只涉及到一个表的列

       当group by不需要排序分组结果时，使用order by null禁止排序，否则会按分组字段自动排序

•  优化 limit

           当偏移量比较大时，尽可能采用覆盖索引扫描，而不是查询所有列

•  优化count查询

        count（某列）---统计该列的有值的结果数，不统计null值【较慢】

        count（*）----忽略所有列，直接统计所有行数，可为近似值使用【更快】

•  优化插入语句 insert

        尽量避免使用一次性插入多个值的insert语句

（3）索引

• 索引设置问题
• 索引是否失效

（4）锁优化

       行锁  表锁

（5）服务器参数调优：缓冲、线程数

（6）大数据量并发

        使用Redis针对查询，提高查询效率

        分库分表、主从复制、集群

3、性能检测

    （1）定位低效索引

•     slow-query-log  慢查询日志---查询结束后记录
•     show processList  实时查看SQL执行情况--查看当前正在进行的线程：线程状态、是否锁表

    （2）进一步丰富

•     show profile---分析SQL语句性能消耗：执行时间，cpu和内存使用情况show profile cpu
•     trace---获取优化器如何选择执行计划
•     explain--查看SQL执行计划

                id--执行顺序

                select-type--每个select子句类型

                type---访问类型：all 全表扫描  index 索引全扫描 range 索引范围扫描

                                            ref 单独值的所有行：eq-ref  唯一索引，const 常量

                possible-keys---可能用到的索引

                key---实际使用的索引

                key-len 使用到索引字段的长度

                ref---表的连接匹配条件， 用于查找索引列上的值

                rows---需要读取的行数

                Extra--其他信息

•  InnoDB-row-lock   ---查看锁的信息                     

二、MySQL底层知识

1、逻辑架构

• 第一层：服务器层---提供连接处理、授权认证、安全等功能
• 第二层：核心服务功能---查询解析、分析、优化、缓存等
• 第三层：存储引擎层---数据的存储和提取，服务器通过API与存储引擎通信，存储引擎不解析                 SQL，不同存储引擎间也不相互通信。

2、查询执行流程

•   客户端发送查询请求----服务器----先检查缓存-----有返回结果
•    缓存没有---SQL解析--预处理--优化器生成执行计划--存储引擎API执行查询--返回结果                                             

三、索引

（一）索引概述

   1、索引相当于目录  

      索引是一种排好序的快速查找 数据结构

     索引大大减少了服务器扫描的数据量，

     帮助服务器避免排序和临时表，可以将随机IO变成顺序IO

  2、索引两大功能：

      查找---where 后面语句

      排序---order by 后面语句

 3、底层数据结构--B树 多路搜索树

                              InnoDB使用B+Tree（叶子结点存数据）

 4、数据库两种检索方式

    （1）全表扫描

    （2）根据索引检索（效率很高，主要原因是缩小了扫描范围）

（二）索引的分类

1、分类

   （1）单值索引---一个索引只包含单个列

                   普通索引：值可重，可null

                   唯一索引：值唯一，可null

                   主键索引：值唯一且不可null

   （2）复合索引---一个索引包含多个列

                             【最佳左前缀法】带头索引不能无，中间字段不能缺

    （3）全文索引---char varchar text类型是字段上（InnoDB中没有，MyISAM中才有）

2、聚簇索引和非聚簇索引

（1）聚簇索引---一级索引（一般为主键索引）

（2）非聚簇索引---二级索引

  【查找过程】

    select 是有索引的字段

 （1） 当字段为主键索引---直接到该索引字段的B+树上找到对应的叶子节点拿数据（ 聚簇索引）

 （2）当字段为非主键索引（非聚簇索引）---在该索引字段的B+树上找到对应的叶子节点得主键

                                                                        然后进行（1）

3、功能分类

 （1）B-tree索引---所有值都是按顺序存储的，每个叶子结点到根得距离相同【快速访问】

          限制：最左前缀法、若查询条件为某列的范围，则右边的所有列无法使用索引

  （2）hash索引---基于哈希表，将所有索引列的哈希码存在索引中（Memory）

            限制：非顺序存储，不支持部分索引列查找，只支持等值比较查询

  （3）全文索引---关键字匹配查询，基于相似度的查询

  （4）覆盖索引---要查的列都是索引列

  （5） 空间索引  --从所有维度来索引数据（MyISAM）

           特点：无需前缀查询              

（三）添加索引的条件  

1、数据量庞大

2、该字段很少进行DML操作（对字段增删改，索引也需要重新排序，进行维护）

3、该字段经常出现在where子句中（即经常根据哪个字段查询）

（四）索引的创建和删除

1、添加索引是给某一字段或某些字段添加索引

2、创建索引对象

     create index 索引名称 on 表名（字段名）

3、删除索引

    drop index 索引名称 on 表名

（五）索引实现原理

   1、原数据根据创建的索引对象进行自动排序

   2、select语句根据索引分的区，排的序进行扫描（在索引生成的B-Tree上查找）

   3、将select条件中的值转变成物理地址，

          然后直接通过物理地址在硬盘或内存中定位，而不是通过表

[注]

 具体在硬盘还是内存主要由存储引擎决定，其中memory是放在内存中

（六）索引失效的情况

  1、like以通配符开头---模糊查询like第一个字符是通配符%而不是具体字符

  2、字符串不加单引号

  3、where中or连接语句没有同时使用索引列

  4、索引列出现了隐式类型转换

（七）索引优化

1、一表加索引---直接加

2、两表加索引  

      左连接---加右表（左表一定都有）

      右连接---加左表（右表一定都有）

3、三表加索引

    加两个索引，两两选一个加，两表加同2

  【总结】jion语句的优化

        1、加索引加载表中经常查询的字段中

        2、尽量减少jion中的循环总次数----用小表驱动大表，

                                                                即左连接时，左表小表，右连接时，右表小表

4、其他优化建议

   （1）最佳左前缀法则---带头字段不能无，中间字段不能缺

   （2）不在索引上做任何操作：计算，函数等

   （3）尽量使用覆盖索引，减少select *

   （4）is null ，is not null，不等于情况不可使用索引

四、主从复制和集群

（一）主从复制

  1、作用

    不同服务器之间同步数据

    即让一台服务器的数据的数据与其他服务器保持同步

   解决问题：数据分布、负载均衡、备份、高可用性和故障切换、MySQL升级测试

  2、复制规则

    （1）每个从机只有一个主机

              每个主机可以有多个从机  

   （2）两种复制方式

             基于行的复制

             基于语句的复制

  3、复制步骤

• 二进制日志事件：将主库更新的事件记录到二进制日志
• 从库将二进制日志拷到本地的中继日志中
• 从库的SQL线程从中继日志中读事件并在备库执行，从而更新备库数据库

        主库----二进制日志---中继日志（从库）---  从库更新

（二）集群

  1、集群---通过主从复制实现（redis）---分布式思想（spring Cloud ，Dubbo）

  2、 只要有从的角色，都不能进行写操作

  3、一个主机可配置多台从机，从机又可以配置多台从机

            减轻了一台主机的压力，但增加了服务之间的延迟时间

            故，集群提高效率在于服务间延迟时间

   4、提高服务间延迟时间不同层面：

      （1） 代码层面----sql语句写的效率高一点

                                ---mysql的优化---索引，提高查询效率  快照（缓存） 常访问的复制到redis中

     （2）缓存机制----线程池，常量池，字符池

     （3） 消息队列----处理不依赖于下层系统处理结果的，MQ先反馈

                             ----依赖则提升下层系统响应效率（1，2，4，5）

     （4）  垃圾回收机制---减少STW的时间---少用全局变量，优化等

     （5）   redis本身的性能----不用关系型的表，查询速度快

                                           ---简化各种安全机制（少账户密码的安全认证）

                                               非必要不持久化，关系型数据持久化备份

五、锁

1、读写锁 --表锁   

   （1） 对数据的操作上：读共享，写独占

   （2）手动加解表锁（读写锁）

          加锁： lock table 表名1 read/write ，表名2  read/write ，其他；

          解锁：unlock tables

 （3）读写锁特点   

   <1>线程1给表加读锁之后，自己可以读，但不能改，也不能读其他的表----自己可读不可写

          线程2可以读加锁的表，但一旦对此表进行修改，则会一直等待（阻塞），知道线程1unlock解锁----其他可读不可写

  <2>线程1给表加写锁，自己可读可改

        线程2不可读不可改，否则阻塞，直到线程1解锁--其他不可读不可改

2、表锁和行锁

（1）给表加锁----读写锁   MyISAM

（2）行锁----Innodb---支持事务，innodb默认加行锁

           有了索引自动加行锁

            给某一行加行锁----for update

3、锁优化

（1）查看锁信息

        show sratus like ‘innodb_row_lock%’

参数含义：

 （2）优化建议

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

事务隔离级别详见：MySQL1---理论概述：DB,DBMS，SQL、数据库设计三范式、常见数据类型、事务、存储引擎、锁等_@snow＇的博客-CSDN博客