[大数据]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? ?---查看锁的信息? ? ?? ? ? ? ? ? ? ??

? ??

二、索引

（一）索引概述

? ?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、作用

? ??

? 2、复制规则

? ??

? 3、复制步骤

（二）集群

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

四、锁

待更...