Mysql优化实战总结与面试

mysql优化：

• 硬件优化
• Mysql服务器的提升
• sql本身优化
• 反范式优化
• 索引优化

优化简介：

1.硬件优化：

使用机械硬盘或者加装固态硬盘

2.Mysql服务器优化：

可以将Mysql放在window环境中也可以放到linux环境下后者更快

3.sql本身优化：

1.尽量减少查询所有以及子查询等一些操作，关联查询要比只查询要快

2.避免进行全表扫描，比如使用模糊查询第一个字用通配符，或者or，以及is null andis not null

3.不要对索引字段进行操作，或者会导致索引失效

4.使用联合索引的时候不要跨列使用否则也会导致索引失效

5.对联合索引最好使用最左原则（从联合索引的最左侧那个列开始在where后面使用）

6.联合索引的全值匹配就是在条件语句之后对联合索引中的字段顺序与字段名完全一致全部都有用到

4.反范式优化：反范式基于三范式

三范式：一个列之存储一个数据和其他列没有任何关系

name	age-sex
张三	21-男

第一范式：各列与各列没有任何关系

name	age	sex
张三	21	男

第二范式：建立一个中间表让表与表的关系连接起来

订单表id	商品名称
1	东鹏特饮

用户表id	用户姓名
1	李四

中间表id	用户表id	订单表id
1	1	1

第三范式：建立表与表之间的关系让他们的数据呈现在一张表上

反范式：就是在一张表中插如另一张表的少数几个字段作为一张表信息显示（低冗合）

5.索引优化：

索引（数据结构）：是帮助Mysql高数获取信息的数据结构；

索引分类：

1. 普通索引：
2. 唯一索引：可以为空，一个表中只能有一个主键，在建立索引的时候使用unique而不是index
3. 主键索引：在建立表的时候建立一个主键、然后这个主键不能为空、每一个表中也只有一个主键
4. 联合索引：在一个表中建立了多个索引字段，这些字段在建立的时候就排好了顺序在使用的时候不可以打乱这些顺序使用

联合索引需注意点：

• 当一个表中的字段全部都为索引时，此时在用联合索引时可以不遵循最左原则，也称全字段；
• 当一个表中有一个或者多个字段并且存在一个联合索引，那么这个时候要用联合索引时必须遵循最左原则；
• 当一个表中有一个联合索引但是在使用过程中打乱了他的顺序此时改索引不会失效，因为Mysql有个优化器可以自动优化、但前提是联合索引中的索引字段必须要全部用上；
• 当表中存在主键时那么该主键也是一个索引；

1. 非聚集索引

建立索引： 1.CREATE INDEX 索引名称 ON 表名（索引字段名（可以有多个））

                   2.ALTER TABLE 表名 add 索引类型(index/unique)  索引名称(索引字段)

执行计划:（EXPLAIN）:通过执行计划的key字段判断sql语句是否用到索引

查看执行计划是否充分用到索引：通过keylength字段查看到他的数值再利用算法

key Length: 等于索引的长度、索引为int类型且不为空则他的索引长度为4，索引长度为int类型且可以为空则他的索引为5，4+1如果值可以为空；

算法：字符串类型（2或0）+ 字符集（3）* 本身长度 + 是否为空 = keylength

       算法需要的参数：

             1：字符串类型 （varchar+2,char+0）

             2：字符集（UTF8 长度为3）

             3：本身长度 （数据库设置的长度）

             4：是否为空；（null +1, notnull +0）（int类型只需要关注这一个参数）

范围条件放索引字段的最后：

             范围条件：WHERE中的条件字段 

             放最后：放在索引字段的最后

 

进阶面试题：

Mysql建立索引需要注意那些：

三星索引：

        一星索引：在改动较小稳定性高的字段上建立索引；

       二星索引：排序（order by）顺序和B+Tree的顺序相同；（较难达到）

       三星索引：建立的索引字段名刚好与要查询的字段名相同；（很难达到）

高阶面试题：

1.Mysql内存结构：SGA(系统全局)，PGA（程序缓存区）

       SGA(系统全局)：全局共享内存（innodb_buffer_pool）

       PGA（程序缓存区）：线程独享内存(sort_buff_size、join_buffer-size)

2.为什么要使用B+树不用红黑树和其他树？

      考虑深度问题所以使用B+树

      B树：每一个节点都带key但是不带具体信息，检索速度繁琐

      二叉树：每一层只带几个数据所以会导致深度很深，查询速度会很慢

      红黑树：和二叉树差不多深度太深影响速度

      B+树：之分为三层只有叶子节点带有具体信息，他会按照你要查询的条件去进行判断，

      深度浅效率快