Mysql:索引、锁模块、sql优化、语法

一、索引模块

索引的数据结构

Hash
二叉查找树
B树
B+树
为什么要使用索引？
索引让我们避免全表扫描，提升检索效率
什么样的信息能成为索引？
主键唯一键等能让数据具备唯一区分性的字段都能成为索引
索引的数据结构
主流是B+树，还有hash结构BitMap，mysql数据库不支持BitMap

Hash

优点：通过字段的值计算hash值，定位数据非常快
缺点：不支持范围查询
为什么不支持范围查询？
因为底层数据结构是散列的，无法进行比较大小

二叉查找树

平衡二叉树查询效率还可以
缺点：虽然支持范围查询，但是查询效率低。如果树的高度越高，那么查询IO次数会越多

B树

如何去减少查询IO次数
B树在平衡二叉树中，减少树的高度（因为B树的节点中可以有多个元素）
B树查询比平衡二叉树效率要高，因为B树的节点中可以有多个元素，从而减少树的高度，减少IO操作，从而提高查询效率
缺点：范围查询效率还是比较低

B+树

B+树解决范围查询问题、减少IO查询的操作
B+树算法： 通过继承了B树的特征，B+树相比B树，新增叶子节点与非叶子节点关系，叶子节点中包含了key和value，非叶子节点中只是包含了key，不包含value。通过非叶子节点查询叶子节点获取对应的value，所有相邻的叶子节点包含非叶子节点，使用链表进行结合，有一定顺序排序，从而范围查询效率非常高。

缺点：因为有冗余节点数据，会比较占内存。

MyISAM和InnoDB对B-Tree索引不同的实现方式

MyISAM底层使用B+树，叶子节点的value对应存放行树的地址，再通过行数定位到数据
InnoDB底层使用B+树，叶子节点的value对应存放的是行的data数据，相比MyISAM效率要高些，但是比较占硬盘内存大小

索引优化

索引无法存储null值

如果条件中有or，要想索引生效，要将or条件中的每个列都加上索引

like查询以%开头的不走索引

如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引

如果mysql估计使用全表扫描要比使用索引快，则不使用索引

联合索引最左匹配原则

如果建立（a,b）顺序的索引：a = 3 and b = 4 ,会走联合索引。单独 a = 3 也会走联合索引。单独b=4，不会走联合索引

mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。比如a = 3 and b = 4 and c > 5 and d = 6 如果建立（a、b、c、d）顺序的索引，d是用不到索引的，如果建立（a、b、d、c）的索引则都可以用到。a、b、d的顺序可以任意调整。

=和in可以乱序 ，比如 a = 1 and b = 2 and c = 3 建立（a、b、c）索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

索引建立的越多越好吗

数据量小的表不需要建立索引，建立会增加额外的索引开销
数据变更需要维护索引，因此更多的索引意味着更多的维护成本
更多的索引意味着也需要更多的空间

二、调优sql

执行 show variables like '%quer%' 查询慢日志相关的配置

show status like '%slow_queries%'  查询属于慢查询的数量

show variables like '%quer%'  -- 查看慢查询相关配置

show status like '%slow_queries%' -- 查看本次回话有多少条慢查询

-- 设置后需要重新连接数据库(重启数据库服务会还原配置)
set global slow_query_log = on  -- 开启慢查询

set global long_query_time = 1  -- 超过一秒为慢查询

-- 修改 my.ini（windown） my.cnf（liunx） 配置文件是永久生效的

explain 查看执行计划

如果 type项 为 all 或者index表示需要优化

extra 项 执行状态说明，该列包含MySQL解决查询的详细信息，为Using filesort 或者 Using temporary 表示需要优化

alter table person_info_large add index idx_name(name)  给表person_info_large的name字段加索引

select count(id) from person_info_large fore index(primary) 强制走主键索引

三、锁模块

MyISAM与InnoDB关于锁方面的区别是什么

MyISAM默认用的是表级锁，不支持行级锁
InnoDB默认用的是行级锁，也支持表级锁

表级锁会锁住整张表

表级别的读锁：

同一张表，表查询语句没有结束 ，如果此时有增删改查语句执行，增删改查语句会一直等待查询结束再执行

在读表时加读锁，增删改查操作需要等待

读锁也叫共享锁：即一条查询语句没有结束，另一条查询语句不会等待它结束再执行，可以同时执行

表级别的写锁：

同一张表，增删改查语句没有结束 ，如果此时有查询语句执行，查询语句会一直等待增删改查语句结束再执行

在写表时加写锁，查询语句需要等待

写锁也叫排他锁：即在一条增删改查语句没有结束，查询语句和其他增删改查语句需要等待

排他锁：一条语句没有执行完，其他语句需要等待其执行在执行

数据库事务的四大特性

原子性：一个事物内所有操作，要么全部成功，要么全部失败。

一致性：就是A和B的钱是1000元，A给你100元，无论最后双方转了多少次，总的钱一定是1000元。

隔离性：多个用户并发访问数据库时，一个用户的事务不能被其他用户的事务所干扰，多个并发事务之间数据要相互隔离，不能相互影响

持久性：一个事务一旦被提交，他对数据库中数据的该变是永久的

事务隔离机制

-- 查看事务隔离级别
select @@tx_isolation

-- 设置事务隔离级别为读未提交
set session transaction isolation level read uncommitted;

四、语法部分

group by

满足select子句中的列名必须为分组列或列函数
列函数对于group by 子句定义的每个组各返回一个结果

-- 查询所有同学的学号、选课数、总成绩
select student_id,count(course_id),sum(score),course_id 
from score
group by student_id;

一张表 ：
如果用 group by,那么你的select语句中选出的列要么是你group by里用到的列，要么就是带有之前我们说的如sum min max avg count 等聚合函数的列
多表 ：
select 里面的列要么是该group by里出现的列，要么是别的表的列或者带有函数的列

having

通常和group by子句一起使用
where过滤行，having过滤组
出现在同一sql的顺序：where > group by > having

-- 查询平均成绩大于60分的同学的学号和平均成绩
select student_id,avg(acore) from score group by student_id having avg(score)>60; 

五、三大范式

第一范式：每一列不能再拆分

即数据库表的每一列都是不可分割的原子数据项，而不能是集合、数组、记录等非原子数据项。即实体中的某个属 性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中每个列的值只能是表的一个属性或一个属性 的一部分。简而言之，第一范式每一列不可再拆分，称为原子性。 第一范式：每一列不能再拆分

总结：如果不遵守第一范式，查询出数据还需要进一步处理（查询不方便）。遵守第一范式，需要什么字段 的数据就查询什么数据（方便查询）。

第二范式：一张表只描述一件事、表中的每一个字段都依赖于主键

总结：如果不准守第二范式，数据冗余，相同数据无法区分。遵守第二范式减少数据冗余，通过主键区分相 同数据。

第三范式：从表的外键必须使用主表的主键

总结：如果不准守第三范式，可能会有相同数据无法区分，修改数据的时候多张表都需要修改（不方便修 改）。遵守第三范式通过id可以区分相同数据，修改数据的时候只需要修改一张表（方便修改）。