MYSQL优化及原理1

MYSQL优化及原理

基本的数据结构

• 线性表：存放数据 有数组和链表 和广义表区别：广义表中既可以存放元素和可以存放数据

• 完全二叉树：除叶节点外，所有节点都必须有左节点

• 二叉排序树：左节点一定比根节点小，右节点一定比根节点大

• 平衡二叉树：在二叉排序树的基础上，每棵子树的左子树和右子树的深度不能超过1，并且每颗子树都是平衡二叉树

• 红黑树：在平衡树的基础上，

  1.节点是黑色或者红色
  2.根节点都是黑色
  3.所有叶子都是黑色
  4.每个红色节点的两个子节点都是黑色
  5.从任一节点其叶子的所有路径都包含相同数量的黑色节点

提高了索引速度，减少自旋和查找造成的开销

• B树：屏蔽了红黑树上的每个节点存放数据过少的弊端，树的深度由节点的度决定，如果度比较大，那么节点中存放的数据节点个数比较多，树深度浅，那么整棵树的检索性能更好

• B+树：

  1.只有叶子节点才能存放数据

  2.叶子结点会冗余非叶子结点的键

  3.叶子结点里面放的是数据本身和下一个数据的位置

  4.叶子结点会有指针，提高区间访问效率

  5.叶子结点中元素存放顺序从小到大从左向右排列

• hash表虽然查找大于B+树但是不支持区间访问

索引基础知识

索引：索引可以提高数据检索的速度，提高并发性能
索引存放的位置：innodb中 索引和数据存放在一个文件中（主键自带索引效果），而在MYISAM中索引和数据存放在两个文件（.MYD存放数据.MYI存放索引）
索引的创建
create Index 索引名 ON 表名(具体列名)
唯一索引
create UNIQUE 索引名 ON 表名(具体列名)
联合索引
create index 索引名 ON 表名(列1，列2...)
全文索引
shiro搜索引擎就是全文索引

MYSQL默认采用INNODB引擎，索引底层是B+树

最左匹配原则及注意事项

创建联合索引以后，在使用sql时能否命中索引，就看其是否遵循最左优先匹配原则

create index idx_a_b_c on tb_tmp(a,b,c)
select * from tb_tmp where a=1,b=2,c=3 可以命中索引
select * from tb_tmp where a=1,b=2 可以命中索引
select * from tb_tmp where a=1,c=3 只能命中a索引
select * from tb_tmp where b=2,c=3 不能可以命中索引
select * from tb_tmp where b=2,c=3，a=1 可以命中索引

INNODB和MYISAM的区别

INNODB：支持事务，支持外键，采用行锁，事务完整性高，并发性高
MYISAM：采用表锁，访问速度快，只适合读和插操作

有关索引的面试常见问题

• 为什么索引不用红黑树？

  因为当红黑树中数量较大时，树的深度也会很大，检索性能就会降低

• 为什么非主键索引的叶节点存放的是主键值?

​ 因为其本身还是通过非主键索引找到对应的主键值，然后通过主键值再去查找主键索引树找到具体的记录

• 为什么INNODB要主键？

  因为普通索引要存储主键数据，然后通过主键值去查找主键索引树，以找到记录

sql优化

1. 为什么做sql优化？

   防止在创建表时没有命中索引导致慢sql，导致接口性能差

   数据库的名称不规范，表的名称不规范导致使用时出现了问题

2. 基础规范

   表引擎必须使用innodb，默认使用utf-8必要时使用utf8mb4(汉字四个字节，可以存储表情符号)

   禁止使用存储过程，视图，触发器，Event，其对数据库性能影响过大例如插入一百万条数据不能使用存储过程(表只干插入，其他的操作就不能做了)而是在业务层进行拆分

   禁止在数据库中存储大型数据，照片啥的存在对象存储系统，数据库中存储数据的路径

   禁止在线上环境做数据库压力测试

   测试，开发时数据库环境必须隔离

   库名，表名，列名必须采用小写

   从库必须-s后缀

   备库必须-ss后缀

   禁止使用外键，如需要使用外键则由应用程序实现：外键使得表之间存在耦合，影响update、delete等sql性能有可能造成死锁，高并发下有可能造成数据库瓶颈

   建议将大字段，访问频率低的字段拆分到单独的表中存储，分离冷热数据

   不建议在频繁更新的字段上建立索引，非必要时不要使用join(join的字段必须相同，如果不同可能会导致全表扫描)

   禁止使用select *(本来需要三个字段，现在表结构修改，就会导致查询的结果不一样)

   insert时必须指定字段(同上)

   隐式类型转换会使索引失效，导致全表扫描

   禁止在使用where时使用函数或者表达式(导致不能命中索引全表扫描)