mysql优化练习
二叉树做为索引
特点 ： root节点 ，左节点小于root，右节点大于root
平衡二叉树，左右子树的高度不能大于1. o(logn)
根据二叉树的特点，如果删除一边节点，并在另外一边添加节点，有可能变成链表结构，复杂度变成o(n)
btree 结构
btree 根节点至少有2个子节点
m阶树的含义，每个子节点最多含有m个孩子 (m>=2)
除了根节点和叶节点外，其它节点至少有ceil（m/2）个孩子，其中ceil 取上限值 （例如1.5 取2）
所有叶子结点高度都一样。
b+ tree
++
hash索引 用于等值的查询比b+tree 效率高，但是不能范围查询。
bitmap
聚簇索引和非聚簇索引
如果表有主键，那么主键作为聚簇索引，一个表只能有一个聚簇索引，聚簇索引存储行信息，非聚簇索引存储非聚簇索引和聚簇索引，需要拿到聚簇索引之后在进行查询才能获取数据。
如果没有主键那么第一个非空的字段作为一个聚簇索引
以上都不满足，隐式的创建一个主键（聚簇索引）。
开启慢查询日志
Show variables like ‘%queries%’
查看是否启用慢查询
show variables like ‘slow_query_log’;
set global slow_query_log = on;
Set global slow_query_log_file=’’;
Set global log_queries_not_using_indexs=on;
Set global long_query_time= 1 ; 1s
可以强制走什么索引
select count(id) from tab force index(primary) ;
这句未必走主键索引，查询优化器可能会选择非聚簇的索引
use information_schema;
select table_schema, sum((data_length+index_length)/1024/1024) M from information_schema.tables
where table_schema is not null
group by table_schema ;
select table_schema, concat(truncate(sum(data_length)/1024/1024,2),’ MB’) as data_size,
concat(truncate(sum(index_length)/1024/1024,2),’MB’) as index_size
from information_schema.tables
group by TABLE_SCHEMA;
SELECT
a.,CONCAT(‘kill ‘ ,a.id,’;’)
FROM
information_schema.PROCESSLIST a
WHERE
a.STATE = ‘Waiting for table level lock’;
select a. from information_schema.PROCESSLIST a ;
select a.* from information_schema.innodb_trx a ;
慢查询分析工具
Mysqlddumpslow -h 看看有哪些参数
mysqlddumpslow -t 10 /xxx/xx.log |more
Pt-query-digest slow-log > slow_log.report
Pt-query-digest slow-log -review \
h=127.0.0.1,D=test,p=root,P=3306 ,u=root ,t=query-view \
—create-reviewable \
—review-history t = hostname_slow
查询次数多且每次查询占用时间长的sql
io大的sql pt-query-digest 中rows examine项
未命中索引的sql rows examine 和rows send 对比大的索引用的少
使用执行计划explain 语句
type ：const常数，eq_reg，ref，range，index，all 表
possible-keys
key
Key_len
Rows 表扫描的数量
using filesort 用文件排序优化
using temporary mysql需要用临时表来存结果
max 和count 函数优化，增加索引，覆盖索引
count(*) 是包含null的，count（列）不包含
索引优化
注意索引的最左匹配原则，复合索引时候，如果遇到范围查询即停止（> ,< ,between,like）
= ,in 可以乱序的，mysql会进行优化。
如何选择合适的列进行建立索引？
where从句，group by 从句，order by 从句，on从句
索引字段越小越好
离散大的列放到联合索引前边，怎么判断离散程度，用select count（distinct 列）进行判断。
分析重复冗余的索引
pt-duplicate-key-checker -uroot -p ‘’ -h ip即可。
Pt-index-usage -uroot -p mysql-slow.log
mysql事务
ACID
原子性，一致性，隔离性，持久性
mysql默认级别rr ，repeate ，能避免幻读的， 幻读sessionA，查询数据 share in model 读锁，
SessionB 插入或者删除数据，之后SessionA 更新数据，它会把新插入的数据也同时更新，产生幻读。
表象 是快照读 伪mvcc ，mysql的多版本机制。
当前读就是加锁的增删改查语句，无论是共享锁，排它锁。
快照读 是 不加锁的非阻塞读，提高并发。
RR 以及更高级别下，默认gap间隙锁，这也是避免幻读。
对主键或者唯一索引会用到gap锁吗？
where 条件语句如果全部命中，则不会用gap锁，只会加记录锁。
gap锁会出现在非唯一索引或者不走索引的当前读中！
表结构优化
尽可能使用not null 定义字段并设置默认值，因为null值可能增加复杂度
可以使用unix_timestamp(‘2014-09-21’) 存储时间为int
使用from_unixtime(timestr)
Biting 8个字节存储ip地址 inet_aton(‘192.168.1.1’);
Net_ntoa(ipaddress)
系统配置优化
/etc/sysctl.conf
增加tcp支持的队列数
net.ipv4.tcp_max_syn_backlog=65535
Net.ipv4.tcp_max_tw_buckets=8000
Tw_reuse=1
Tw_recycle =1
Fin_timeout =10
mysql配置
innodb_buffer_pool_size
用于配置innodb的缓冲池大小，如果只有innodb表推荐占总内存的75%
innodb_buffer_pool_instances
控制缓存池的个数，默认情况下只有一个缓冲池
innodb_log_buffer_size
innodb log 缓冲的大小，由于日志最长每秒钟就会刷新所以一般不用太大
innodb_flush_log_at_trx_commit
影响innodb的io效率，一般建议为2，数据安全性高的话使用默认1. 2是把数据提交到缓冲区，也就是最多失去1s的数据。
innodb_read_io_threads
Innodb_write_io_threads
决定innodb读写的io进程数，默认为4
innodb_file_per_table
控制innodb每一个表使用独立的表空间，默认为off，设置为on
innodb_stats_on_metadata
决定mysql在什么情况下会刷新innodb表的统计信息
mycat
Pxc加上replication 二种集群方案
mysql锁
myisam 和innodb 锁类型区别？
前者仅仅支持表锁，后者表锁和行锁，默认是行级锁。
读锁是共享锁，写锁是排它锁。
读读 不冲突，读写冲突，写读冲突，写写冲突
lock in share model 共享锁 读锁
innodb情况下，走索引的时候使用行级锁，没使用索引时候是表级锁。
mysql 5.7复制基础
mysql复制是基于binlog日志，日志有三种格式statement，row，mixed
statement 存储sql语句，存储日志量最小
row 存储event数据，存储日志量大，但是不能很直接的读取
mixed 混合。
mysql部分复制
master端
binlog-do-db
binlog-ignore-db
slave端
replicate-do-db
replicate-ignore-db
replicate-do-table
replicate-ignore-table
replicate-wild-do-table
replicate-ignore-do-table
mysql复制类型
- 二进制日志的复制
- 使用gtid完成基于事务的复制
- 基于mysql5.7的 gtid 进行复制
- GTID (Global Transaction ID)