innodb 三大特性。 事物。 外键 行级锁
1.开启慢日志记录命令
2.开启sql执行时间统计。profile功能 可以查询cpu使用情况，还有每个sql的时间使用情况。
3.explan出来的列的解释
id： ID越大最先执行，id相同从上到下
select_type：simple单表没有连接和子查询； PRIMARY主查询外层查询；UNION UNION中第二个或者后面的查询语句；
SUBQUERY（select/where之后包含的子查询）
type：重要指标 从好到差null（没走表）、system、const（唯一索引）、eq_ref、ref（普通索引）、range、index（索引走了一
堆）、all
possible_key：可能用到的索引
key：实际用到的索引；null就是没用到索引。
key_len：长度 联合索引用到几个索引
row：执行行数 预估值 不是实际值
filtered：查询的数据使用率 越大越好 最好百分之百说明没有浪费的数据被查询
Extra：额外的使用的资源
using index condition 目前说不清需要考证
using where 和 using index反正是用到索引了具体需要在研究
backward index scan是反向索引
using temporary 使用临时表 效率低
4索引失效规则如下
了解B+ tree怎么存储的数据肯定就知道为什么失效了
函数失效
类型转换失效
or失效
最左原则
mysql评估sql走全表快还是走索引快一般就是查询出来的数据大于数据的一大半。 is null 和 is not null 是这个规则的特殊情况 符
合规则就走 索引 不符合就不走。看 null多还是。 非null多。
5SQL提示
规则都是 select * from 表 use index（索引名）
use index 提示表用这个索引 建议 mysql最终可能不走这个索引
ignore index 忽略表用这个索引
force index 强制表使用这个索引
6.索引的一些其他的知识
覆盖索引： 意思就是 查询的数据都在索引上 索引的数据已经涵盖了 所以就是 索引覆盖。
前缀索引：为了解决一个页里面多存一些数据 所以针对 varchar text这种类型的 指定字符串前缀当索引 ，指定多少个字符当前缀索引 很重要因为 不重复比重越大索引效率越高。可以用distinct这个字段 比如select count（distinct 字段）/count（*）from 表越接近1肯定越好啊。
===============================================
优化优化优化
8.sql语句优化
insert： 批量插入 insert into tb_表 values（1，“11”），（2，“22”）；
多个insert在一个事务中做
保持主键顺序插入这样笔记块 和底层的 B+tree有关
9.load指令大批量插入
文件模版
1，张三，2020-01-01，18
mysql --local-infile -u root -p
select @@local_infile;
set global local_infile =1
load data local infile '/root/sql.log' into table '表名' fields terminated by ',' lines terminated by '\n';
10.主键优化 .页分裂和页合并。
分裂是因为插入的数据 不是按照顺序的所以页满了肯定就分裂了 分裂会找到合适的地方插入进入 一般是中间
页合并是因为数据被删除了。找个合适的机会 进行页合并一般达到页百分之50 可以设置。在索引的时候
11.order by 优化. 会用到索引。遵循最左原则
using filesort 根据索引查询或者全表扫描出来对应字段。在sort buffer中完成排序操作。而不是通过索引直接返回的排序结果叫using filesort
最重要的一点 是必须使用到覆盖索引 不然就会using filesort
using index 是通过索引扫描直接返回有序数据 复用了索引的排序 不需要额外的排序叫 using index
创建特殊的索引 create index 索引起名 on 表名（字段 asc,字段 desc）。然后联合索引就可以根据这个做Btree构建数据结构了
总结order by优化
根据索引排序多字段排序遵循最左原则
尽量使用覆盖索引
多字段排序可以设置索引的时候根据字段排序设置索引
如果一定出现using filesort 可以设置缓冲区大小 默认256k 满了进入磁盘排序
show variables like ‘sort_buffer_size’ 查看缓冲区大小
12.group by优化
联合索引的时候。 分组也有最左原则。 可以where = 在 group 只要先用到最左就会用到索引
比如 name 和 age是联合索引。下面就会用到索引可以提高效率
select name，age，count（*） from biao where name=小 group by age；
- limit 优化
因为大数据情况 limit 越往后 性能越差
覆盖索引加 字查询来解决 就是返回的时候 先查询出来id。然后在查询 可以in查询也可以连接查询
比如 select * from 表 ，（select id from 表 limit 100000，10）临时名字 where 表.id = 临时名字.id
因为查询出id一次性的去回表所以比较快
14.count优化
myisam 引擎快因为他有计数器
innodb 慢因为真的需要一行行的算
count（）不取值；；；；；
count（主键） 查询的数据返回服务层直接进行累加 因为主键肯定不是null
count（1）。不取值；；；；；查询返回的每一行都放一个1。然后计算1
count（列）。不包含null。 把每行字段都取出来。然后累加。 需要看有没有 not null约束
最终排序 最优选择如下。 最好用count（）
count（*）约等于 count(1) > count (主键) >count(列)
15.update优化
行级锁 是主要是根据索引 的 没有索引 就没有行级锁
where的字段一定要 根据索引字段更新， 不然行锁会升级到表锁
16.视图
cascaded 插入检查不满足视图不能插入 会传递
local 插入检查不满足视图不能插入。 不会传递
17.锁
全局锁。 锁所有表
表级锁。
行级锁
全局锁
数据库备份的时候加. 为了保证数据一致性 保存的时候 数据被修改 所以加锁
flush tables with read lock;
mysqldump -uroot -p12345 数据库 > 名字.sql。 mysqldump不是mysql里面的命令是外面的命令
unlocks tables;
主从模式 从数据库添加全局锁 从库不能执行从主数据库同步过来的binlog
不加锁的备份 mysqldump --single-transaction -uroot -p12345 数据库 > 名字.sql 低层通过快照读备份的。应该是rr哪种的readview
表级锁
表共享读锁（read lock）
表独占写锁（write lock）
语法：
加锁lock tables 表名... read/write
表名一下可以写多个 read和write选一个
解锁：unlock tables; 或者断开数据库连接
读锁加上之后。所有操作只能读 不能写
网友评论