MySQL优化
51.索引的优缺点：可以增加查询效率，但降低update,insert,delete的效率，需要维护索引的结构。
2.5叉B树：key的数量[ceil(m/2)-1]<=n<=m-1，n>4时，中间节点向上分裂，两边节点分裂。
3.m叉BTree: 1.树中每个节点最多有m个孩子
2.除了根节点与叶子节点，每个节点至少有[ceil(m/2)]个节点
3.若根节点不是叶子节点，则至少有两个孩子
4.所有的叶子节点都在同一层
5.每个非叶子节点有n个key与n+1个指针组成，其中[ceil(m/2)-1]<=n<=m-1
4.叉B+Tree：1.最多有n个key，BTree最多有n-1个 key
2.B+Tree的叶子节点保存所有的key,依key的大小顺序排列
3.所有的非叶子节点都可以看做key的索引部分，所有的数据页保存在叶子节点，
4.叶子节点之间有指针，便于范围查询
5.create index idx_city_name on city (city_name); 创建索引
6.show index from city \G查看索引 create view view_city as select语句 创建视图 一张虚拟的表 简单 安全 数据独立
7.drop index idx_city_name on city 删除索引
8.alter table city add unique/primary idx_city_name(city_name); 创建唯一/主键索引
9.查询频度高，在where条件后选取最常用，过滤效果好的列，使用唯一索引，短索引，提升I/O效率，复合索引用最左前缀原则
10.存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合。简化开发人员的工作，减少数据在数据库与服务器之间的传输。函数有返回值，过程没有 delimiter 声明SQL语句分隔符
11.create proceduce proceduce_name(in/out/inout 传入/输出/ num int，@decription/@@decription用户/系统会话变量)参数传递
`begin
//declare num int default 10; 声明变量
set num=num+10;给变量赋值
select语句
end;创建存储过程 call proceduce_name()调用存储过程
12. show/drop procudure status \G查看/删除存储过程 b
13.repeat 语句 util 满足条件 退出循环 end repeat;
c:loop set n=n-1; if n<=0 then loop循环。leave 退出 leave c; end if; end loop c;
14 .游标是用来存储查询结果集的数据类型
create procedure pro_test11()begin declare e_id int(10); declare emp_result cursor for select查询语句 //声明游标 open emp_result; //打开游标 fetch emp_result into e_id;//fetch只能一行一行获取 用循环获取 select concat(‘id=‘,e_id); close emp_result; //关闭end;
15. 触发器是与表有关的数据库对象，在增删改之前或之后，触发并执行触发器中定义的SQL集合
create trigger emp_insert_triggerafter inserton empfor each row begin insert into emp_logs(id,....) values(new.id) 修改前old 修改后 newend;
16.存储引擎是存储数据，建立索引，更新查询数据等技术的实现方式。
Innodb 支持事务，外键，表锁,行锁(默认,适合高并发) MyISAM不支持事务，外键，表锁 访问速度高
开启事务 增加数据 不提交 查询不到 on delete restrict/on update cascade
MyISAM .frm存储表结构 .MYD存储数据 .MYI存储索引
Innodb frm存储表结构 .ibd存储数据索引
MEMORY .frm存储表结构 数据存储在内存 效率高
MERGE 是一组MyISAM表的组合
17 . 查看SQL 执行效率 show （global）status like ‘Com_____‘/InnOdb_rows_%;查看增删改查的次数
18 . 定位低效率执行SQL
1.慢查询日志 2.show processlist time(时间) state(状态) info(语句)
19. explain 分析执行计划
id: 相同是加载顺序从上往下 不同 值越大 优先级越高 越先被执行select_type: simple 简单查询 不包含子查询 union primary 包含子查询 最外层位次标志 subquery 在select where中包含 子查询 derived 在from中包含子查询 递归执行 把结果放在临时表中 union 在第二个select 出现union union result 从union 表获取结果 从上往下 ，效率越低table: 数据来源于哪张表type: null 不查询任何表 select now(); SYSTEM 表中只有一条数据 const 通过一次索引就找到，只返回一条数据 eq_ref 多表关联查询 主键 唯一索引返回一条记录 ref 非唯一索引 返回多条数据 range 范围查询 between > < in index 遍历索引树 ALL 遍历数据文件 从上往下 ，效率越低possible_key: 可能用到的索引key: 实际用到的索引key_len: 索引长度 越短越好ref: rows: 扫描的行Extra: using filesort/temporary/index
20. show profile分析SQL
select @@having_profiling YES 支持select @@prifiling 0未开启 show profiles query_ID Duration(消耗时间) query（语句）SHOW profile for query query_ID 具体
21. trace分析优化器执行计划 MYSQL5.6以后
set optimizer_trace="enable=on" end_markers_in_json=on;set optimizer_trace_max_mem_size=100000select *from information_achema.optimizer_trace\G;
22. (1) 复合索引的最左前缀原则是不跳过索引列，跟先后顺序无关 。
(2)范围查询后面的，索引会失效。
(3)在索引列上计算，索引会失效。
(4)varchar类型的要加单引号，否则索引失效。
(5)尽量用覆盖索引，不要select * using index condition 会回表查询整行数据。
(6)用or分割开的条件，or前用到索引，or后没用到，整体都不会用索引。
(7)like模糊查询 避免‘%xsacs‘ 索引会失效，优化 select 索引列 from emp like ‘%xsacs‘会走索引。
(8)全表扫描比索引快，则全表扫描，不走索引。例如address 是索引列，但99%是北京，1%是西安。
(9)is NULL 和is not NULL都不定会走索引，is NULL大部分是空会走全表扫描，is not NULL也一样。
(10)in 走索引（主键索引），not in 不走索引。
(11)尽量使用复合索引（创建一个复合索引相当于创建多个单列索引），少使用单列索引（会选择最优的一个索引)。
23. 导入大批量数据时：主键有序的快，关闭唯一性校验。set unique_check=0;手动提交事务。
24. insert 优化 insert into tb_test values (1,‘Tom‘),(2,‘Jerry‘),(3,‘Cat‘);手动提交事务.
25. order by 优化 （覆盖索引）using index比filesort效率高。多个字段时，字段顺序要与索引顺序一样；要 么全升序，要么全降序。filesort max_length_for_sort_data>Query语句取出的大小（sort_buffer_size） 使用一次性扫描算法，反之有二次扫描。
26. group by 优化 排序后进行分组 不进行排序 order by null; 创建索引。
27. 子查询优化 用多表联合查询代替子查询。
28. or 优化 or前后都用索引。or 不会使用复合索引， 用union 代替or.
system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
29. 分页查询优化 1.在索引列上完成排序分页 2.主键自增 select * from tb_item where id>20000 limit 10;主键被删除会出现断层。
30. 使用SQL提示
use/ignore index(index_seller_name)
force index(index_seller_name)强制使用该索引 `
31. 应用层面的优化
1.使用数据库连接池
2.减少对MYSQL的访问（避免对数据的重复访问）
３.增加cache层 (使用mybatis/Hibernate的一二级缓存,以及使用redis)
4.MYSQL集群 读（从节点）写（主节点）分离
32. *Mysql 中查询缓存优化*
当执行相同的SQL语句时,服务器会从缓存中读取结果,当数据被修改时,之前的缓存会失效,修改频繁的表不是做查询缓存.
show variables like ‘have_query_cache‘; //是否支持查询缓存 ON /OFFshow variables like ‘query_cache_type‘; //是否开启 0/1 DEMAND(select SQL_(NO)_CACHE title fom emp where id=1;)show variables like ‘query_cache_size‘; //查看缓存大小 size/1024/1024show variables like ‘Qcache%‘; //查看查询缓存的状态信息(命中/增加次数,走与未走缓存数量)
33. *查询缓存失效的情况*
s/Select * from EMP; //查询数据不一致select now(); //查询语句中有一些时不确定的select ‘A‘; //不使用任何表查询select * from mysql/infomation_schame ; //查询系统数据库时 5.在存储函数,触发器的主体内查询 6.当表更改时,使用该表的所有高速查询缓存都将变为无效并被删除 insert/update/delete/drop
34. Mysql内存管理及优化
34.1 MyISAM 存储引擎使用 key_buffer缓存索引块,加速myisam索引的读写速度
key_buffer_size=512M //在/usr/my.cnf配置read_buffer_size //每个session独占 ,不能太大read_rnd_buffer_size //用于做排序的MYISAM表,如 order by 每个session独占 ,不能太大
34.2 InnoDB 用一块内存块做IO缓存池,会缓存数据块 ,索引块
innodb_buffer_pool_size=512M //在操作系统,内存足够可用的时候,设置的值越大,缓存命中越高,访问Innodn表需要的磁盘IO越少,性能越高innodb_log_buffer_size=10M //增加值的大小,避免Innodb在提交事务时不必要扽日志写入磁盘操作
35. Mysql并发参数调整
max_connections //允许连接到mysql的最大连接数 默认值时151 linux平台支持500-1000不是难事back_log //请求数量大于max_connections,其余请求将被存在对栈中 50+(max_connections/5)table_open_cache //用来控制所有sql语句执行线程可打开表缓存的数量thread_cache_size //控制mysql缓存客户服务线程的数量innodb_lock_wait_timeout //用来设置Innodb事务等待行锁的时间,默认值是50ms
36. 表级锁 :偏向ＭyISAM,开销小,加锁快,不会出现死锁,锁粒度大,发生所冲突的概率最高,并发度低;写优先
读锁(共享锁): lock table EMP read; //同一客户端加锁后,只有释放才查询其他表 (读锁只阻塞其他线程的写操作,不会阻塞读操作)写锁(排它锁): lock table EMP write; //(写锁会阻塞其他线程的写操作,读操作)show open tables; in use列 查看被锁定的表show status like ‘Table_lock%‘; table_locks_waited 越高,锁争抢越严重
行级锁 :偏向Innodb,开销大,加锁慢,会出现死锁,锁粒度小,发生所冲突的概率低,并发度高;
索引失效,行锁会升级成表锁
间隙锁 ID自增出现断层 insert/update/delete时会加排他锁,对断层加间隙锁, 断层是添加不进来的
读锁(共享锁): select * from EMP where ...lock in share mode?写锁(排它锁): select * from EMP where ...for update insert/update/delete会自动加写锁
37. 并发事务处理带来的问题
1. 丢失更新: 当多个事务选择同一行,最初的事务修改的值,会被后面的事务修改的值覆盖.
脏读:一个事务对数据访问并修改但未提交,另一个事务访问数据并使用. 3. 不可重复读: 一个事务在读取某些数据的某个时间,再次读取以前的数据,发现和以前读取的不一样(修改)
幻读:一个事务按照相同的查询的条件重新读以前查询过的数据,发现其他事务插入了满足条件的新数据(插入)
38. 事务隔离级别
事务隔离级别越高,性能越低
隔离级别 丢失更新 脏读 不可重复读 幻读 Read uncommited 能解决 不能解决 不能解决 不能解决 Read commited 能解决 能解决 不能解决 不能解决 Repeatable read (默认) 能解决 能解决 能解决 不能解决 Serializable 能解决 能解决 能解决 能解决
39. SQL执行顺序 FROM > ON > JOIN > WHERE > GROUP BY > HAVING > SELECT >ORDER BY > LIMIT
SELELCT * FROM EMP WHERE NAME REGRXP ‘^s‘ / ‘S$‘ / [unc] 正则表达式的使用
40. *Mysql 常用工具*
mysql -u / -p / -h (主机)/ -p(端口) demo_03 -e ‘select * from EMp; -e 执行语句
mysqladmin -uroot -proot create/drop ‘demo01‘ version ;创建/删除数据库 查看版本
mysqlbinlog -vv mysqlbin.000001 查看二进制日志文件
mysqldump -uroot -proot demo_03 tb_book > tb_book.sql 备份数据到tb_book.sql
mysqldump -uroot -proot demo_03 ----add -drop-table > tb_book.sql
mysqldump -uroot -proot -T /tep demo_03 tb_book 在tem目录下生成.sql .txt 文件分别生成表结构和数据
mysqlimport -uroot -proot demo_03 /tem/tb_book.txt 导入txt文本数据
resource /root/t‘b_book.sql 导入sql文件
mysqlshow -uroot -proot demo_03 tb_book --count(表的统计)/-i(表的详细状态信息)
41. Mysql日志
show variables like ‘log_error%‘; 错误日志 默认开启 查看日志地址?log_bin=mysqlbin 在/usr/my.cnf 二进制日志 记录DDL DML(增删改无查询) 用于mysql复制生成mysqlbin.000001以及mysqlbin.index binlog_format=STATEMENT/ROW/MIXED 记录语句/变更信息/?Reset Master 删除并重新记录日志purge master logs to ‘mysqlbin.00006‘ 删除mysqlbin.00006编号之前的日志purge master logs before ‘yyyy-mm-dd hh24:mi:ss‘ 删除在这时间之前的日志--expire_logs_days=# 设置日志过期天气?general_log =1 查询日志 记录所有的 查询 语句general_log_file=query_log.log ?slow_query_log = 1 慢查询日志 记录了所有执行时间超过参数long_query_time并且记录数不小于min_examined_row_limit的SQL语句的日志 效率低的语句slow_query_log_file=slow_query.loglong_query_time=10 默认10秒 mysqldumpslow slow_query.log 查看日志
42. Mysql主从复制原理
Mater主库在事务提交时,会把数据变更作为时间jiluEvent记录在二进制Binlog中
主库推送二进制日志文件Binlog中的日志事件到从库的中继日志Relay Log
slave重做中继日志中的事件,将改变反应它自己的数据