mysql 调优相关

我做了一个导航站（域名是挂路灯的全拼gualudeng.com)，里面精选了各种影视，动漫，黑科技，实用工具，搞笑有趣的站点，动动大家可爱的小手，点进来看看吧,良心站点。

1.红黑树

红黑树是一个自平衡的二叉树，通过5条规则对数的及结构进行约束，可以实现一种比平衡二叉树更加容易维护的弱平衡二叉树，5条约束分别为
1.每一个节点用黑白进行标记，非黑即白
2.根节点是黑节点
3.叶子节点是空的黑节点
4.一个红节点的子节点必须是黑节点
5.任一路径包含的黑色节点数要相同

2.b+tree，b-tree 

1.b+树是一种多路搜索数，还是基于二叉数的。只不过他的一个数据节点（链表）保存了多个索引。一个节点就
是磁盘的一个磁盘块。一次读取一个磁盘块到内存中，减少磁盘io。在内存中遍历一个链表就可以快速定位要
查找索引的子节点位置
（比如我们要查找9，那么就需要遍历当前节点的链表，发现9 在链表节点的7-10之间，那么，就可以获取到当前区间的子节点指针。以此往复最多4层就可以定位到相应的数据位置）。
一般一个b+树的层数为3-4层，就可以存储海量的数据。查询效率非常高。
2.b+数与b-树的区别。b+数节点不存储真实数据的指针或则真实数据（为了扩大单一节点存储索引的量），叶子节点才存储数据。
b+数的叶子节点索引是一个双向链表，且有序的，非常适合用来排序，范围查找。mysql中的hash表数据结构就是因为不能实现范围查找而导致使用率低。
b+树必须要搜索到叶子节点才能结束，b-tree可能再中间阶段就能结束。
3.mysql的innodb引擎会维护一个主键索引。这个主键索引（id）是一个聚集索引，他的叶子节点完全保存了数据库的所有字段数据，不是数据的指针。（这是聚集索引）。
然后其他的索引比如日期索引，他的叶子节点保存的数据是表的主键。所以在查到数据之后还要去主键索引中再查一次才能获取到最终数据。（非聚集索引）
4.innodb 推荐使用id自增组件

3.sql优化

1.在需要经常检索的字段上添加索引，一个表上的索引也不要建得太多了
2.避免在一些索引列上进行运算，函数运算等操作。
3.一些查询尽量使用预编译查询
4.减少数据库连接，将多个sql语句合并一次执行。
5.表关联是尽量设置别名
6.写查询条件时注意列的数据类型，整形不要加引号（变成字符型，不会走索引）,字符型加引号。
7.表设计时索引字段不要为空，为字段选择合适的数据类型，越小的数据类型通常会更快
8.联合索引的最左匹配原则
9.避免多个范围查询，mysql不会同时使用两个索引查询一个结果 select * from users where id>5 and age <20
10.覆盖索引是指select的值就存在与索引数据中，这样直接扫描索引就可以获取值，很快。
11.删除表的一些冗余索引
12.在innodb引擎中执行count(*)将会是索引全表扫描
13.关联时小表驱动大表，使用straight_join来强制选用驱动表。因为需要全表扫描小表，所以在小表上排序影响不大。
14.对于某些分页数据，如果不要求100%准确可以使用id的范围查询
15.联合索引（a,b） WHERE a>10 ORDER BY b 这种情况不走索引
16.行锁是3基于索引的，如何查询过程中索引失效，那么将会表锁
17.间隙锁是在范围查询的时候会把整个范围的数据锁住

4.mysql explain 执行计划分析

1.id列。数值越大，优先执行。相同的化从上而下执行
2.type 列依次有
    const 常量级查询，当使用唯一索引或者主键索引进行等值查询的时候如 select * from user where id=1
    eq_ref 当使用唯一索引或则主键索引进行多行等值查询的时侯（上面是单行，这里是多行）比如查询用户的订单关联查询. select * from orders,users where orders_user_id=users.id
(等价于 select * from orders join users on orders_user_id=users.id)
这个查询对于orders 表是全表扫描，users 是 eq_ref
    ref 普通索引的等值查询（不是唯一索引）
    range 索引的范围查询
    index 索引文件的全表扫描
    all 数据库文件的全表扫描
3.possible_keys 查询过程中可能使用到的索引
4.key 查询过程中使用到的索引
5.EXPLAIN SELECT * FROM `reports` where id>5 and id<10 or view_num=10  type 将会是index_merge
6.在extra中出现了 using filesort 表示将会运用快速排序算法对数据进行临时排序（没有通过索引排序，速度慢）。
    出现use temparore 表示使用了临时表对数据进行了处理性能低。使用order by 的时候经常出现

5.开启慢查询日志

临时开启
set global slow_query_log=1
set global long_query_time=3 #一般设置时间阈值为3秒，默认为10秒。
show global status like "$Slow_queries" #查看有多少条慢查询记录
mysqldumpslow 是一个慢查询管理工具

6.利用 show profiles 查看sql 在各个运行阶段的时间耗用情况

数据库常用命令

set global slow_query_log=1 设置全局变量，会话关闭后也有效
show variables like '%slow_query_log%';  #查看数据库变量
set global slow_query_log=1   #设置全局变量
show global status like '%Slow_queries%'; #status 时服务器运行状态，不可修改
timestamp 时间戳数据库存储的时候会根据客户端失去把他转换为utc标准时区，查询的时候又会转换回来。
datetime 是原样输出。

7.myisam 与 innodb 

myisam 不支持事务，外键。会维护表的总记录数。innodb 的每一条sql语句都会封装成事务自动提交。
myisam 是非聚集索引，myisam 是表锁，innodb 是行锁。

8.mysql  事务

1.acid 特性
原子性最小单位不可分割，一致性同时成功，同时失败，隔离性事务之间相互隔离，持久性
2.事务隔离级别
    1.如果我们在事务的处理中不加任何的锁，将会产生脏读的情形。事务A读取到了事务B没有提交的数据。
我们可以通过加写锁来解决脏读问题
    2.如果之加写锁将会产生不可重复读的现象，即事务A中两次读取的数据不一样，因为事务B修改了数据并提交了。所以我们可以在读的时候加读锁。事务A没有结束，B就不能修改数据。
    3.同时加读锁和写锁任然会有幻读的现象产生。即事务A在第二次读取到了新的数据（事务B产生的，行锁无法对新的数据产生约束）--一般幻读的影响性不大。可以通过事务的串行运行解决。
3.事务的隔离级别
    1.读未提交 （不加锁，脏读，不可重复读，幻读）
    2.读已提交  (加写锁，不可重复读，幻读)
    3.可重复读 （加写锁，加读锁，幻读）
    4.串行化  
4.mysql事务的默认隔离级别为可重复读
5.死锁
    事务A查询字段1，事务B查询字段2，然后事务A想要修改字段2,就等待事务B执行完释放锁。但是在事务B中，想要修改字段1。也要等待事务A释放锁。两者相互等待就形成了死锁。
6.mvcc机制
    每一条数据其实有一个隐藏的版本号，读数据的时候，同时读取版本号。然后修改数据。在提交数据的时候会比对读取数据时的版本号，一致则提交成功。不一致则回滚（其他事务修改了数据）。更新数据的同时更新版本号。
    mvcc多版本并发控制。读数据不加锁，写数据加锁。最后提交的时候比对数据版本号，一致则提交成功。不一致则回滚（其他事务修改了数据）。一定程度上提高并发能力
7.其实mysql的可重复读就是通过mvcc机制实现的

9.分库分表

1.垂直分库
    类似与微服务的业务分离，不同的表，放到不同的数据库。
2.垂直分表
    大表按照字段拆成小表。比如用户表拆成，用户基本信息表和用户详情表。（不常用的列与常用的列分离）
3.水平分库
    数据分片。通过一些算法把数据分布式存储到不同的数据库
    1.按照范围分片id 1-10000 ，10000-20000
    2.hash取余分片（redis分布式槽位分片）
    3.地理位置划分
    4.时间分片（订单流水）
4.水平分表
    数据分片，通过算法把表拆分成结构相同的多个表。
5.分库分表带来的问题
对于分布式数据库架构，排序，分页，分组一直时令人头痛的问题。（可以考虑ES搜索引擎）
    1.查询问题
        1.1单表多分片。订单表可以按照，用户id分片。然后维护一个商品id与用户id的映射表（可分片，空间换时间，先利用商品id分片查找到用户id，然后用用户id分片查找到订单信息）
    2.join 关联问题
        可以根据具体的业务需求在一定程度上增加数据的冗余，减少数据关联。

    

10.