MySQL
SQL:
- 可以单行或多行书写，以分号结尾
- 在MySQl中SQl语句不区分大小写，关键字建议使用大写
- 单行注释： --或#
- 多行注释： /* */
SQl的分类:
-
DDL:定义
-
数据库操作：
-
show databases;
-
查看当前使用的数据库 select database();
-
create database 数据库名;
-
drop database 数据库名;
-
use 数据库名；
-
-
表操作：
-
create table 表名( 字段1 字段1类型[comment 字段1注释], 字段2 字段2类型[comment 字段1注释], 字段3 字段3类型[comment 字段1注释], …… 字段n 字段n类型[comment 字段1注释], )[comment 表注释]; -
show tables;
-
查询表结构 desc 表名;
-
查询表的建表语句 show create table 表名;
-
修改表：
- alter table 表名 add 字段名 类型（长度）;
- 修改数据类型 alter table 表名 modify 字段名 新数据类型（长度）;
- alter table 表名 change 旧字段名 新字段名 类型 （长度）;
- alter table 表名 drop 字段名;
- alter table 表名 rename to 新表名;
-
删除表：
- 删的不留痕迹 drop table 表名;
- 删了数据，但留下框框 truncate table 表名;
-
-
-
DML：操作
- 添加数据：
- 部分字段添加数据 insert into 表名(字段名1， 字段名2，…) values (值1， 值2，…);
- 全部字段添加数据 insert into 表名 values (值1， 值2， …);
- 批量添加数据： insert into 表名 values (值1， 值2， …)， (值1， 值2， …);
- 修改数据：
- update 表名 set 字段名1 = 值1, 字段名2 = 值2,…[where 条件];
- 删除数据：
- delete from 表名[where 条件];
- 添加数据：
-
DQl：查询
-
基本查询：
- 查询多个字段：select 字段1， 字段2，…from 表名;
- 查询所有字段：select * from 表名;
- 设置别名：select 字段1[as 别名] from 表名;
- 去除重复记录：select distinct 字段列表 from 表名;
-
条件查询：
- 语法： select 字段列表 from 表名 where 条件列表;
- 不等于： <> , ! =
- 范围之间（包括边界）： between xxx and xxx
- 多选一： in(xxx, xxx)
- 模糊匹配（_匹配单个字符， %匹配任意个字符）：like
- 为空：is null
- 并且： and, &&
- 或者：or, ||
- 非，不是： not, !
-
聚合函数：
-
将一列数据作为一个整体，进行纵向计算
-
语法：select 聚合函数 (字段列表) from 表名;
-
cout() :统计数量
-
max() ：最大值 ； min() ：最小值
-
avg() :平均值
-
sum() :求和
-
select count(*) from emp;#null不参与聚合函数计算 select avg(age) from emp; select max(age) from emp; select sum(age) from emp where worksddress='北京';
-
-
分组查询：
-
语法：select 字段列表 from 表名[where 条件] group by 分组字段[having 分组后过滤条件]
-
分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无意义
-
select gender,count(*) from emp group by gender; select gender,avg(age) from emp group by gender; select worksddress,count(*) from emp where (age<45) group by worksddress having count(*)>=4;
-
-
排序查询：
-
语法：select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;
-
asc:升序（默认）desc:降序
-
select * from emp order by age asc; select * from emp order by entrydate desc; select * from emp order by age asc,entrydate desc;
-
-
分页查询：
- 语法：select 字段列表 from 表名 limit 起始索引，查询记录数;
- 起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数
- 分页查询是数据库的方言，不同数据库有不同的实现，MySQL中是limit
- 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 查询记录数
-
执行顺序：
-
-
DCL：控制
-
管理用户：
- 查询用户：use mysql;
- 创建用户： creat user ‘用户名’@‘主机名’ identified by ‘密码’;
- 修改用户密码：alter user ‘用户名’@‘主机名’ identified with mysql_native_password by ‘密码’;
- 删除用户： drop user ‘用户名’@‘主机名’;
-
权限控制：
-
查询权限：show grants for ‘用户名’@‘主机名’;
-
授予权限：grant 权限列表 on 数据库名.表名 ‘用户名’@‘主机名’;
-
撤销权限：revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’
-
权限（多个权限间用逗号分隔） 说明 all，all privileges 所有权限 select 查询数据 insert 插入数据 update 修改数据 delete 删除数据 alter 修改表 drop 删除数据库/表/视图 create 创建数据库/表
-
-
SQL数据类型：
函数
-
字符串函数：
-
-
数值函数：
-
日期函数;
-
流程函数：
-
-
约束：作用于表中字段上的规则，限制存储案在表中的数据
-
非空：not null;
-
唯一： unique;
-
主键（非空且唯一） : primary key;
-
默认(若未指定该字段的值，则采用默认值)：default;
-
检查（保证字段满足一个条件）：check;
-
外键（两张表建立联系的字段）：foreign key;
-
外键约束：
-
语法：
-
create table 表名（ 字段名 数据类型,...[constraint][外键名称] foreign key(外键字段名) references 主表（主表列名） ); -
alter table 表名 add constrint 外键名称 foreign key (外键字段名）references 主表（主表列名）; -
no action(restrict):当父表中删除/更新对应记录时，首先检查该记录是否有对应外键若有则不允许删除/更新
-
cascade：当父表中删除/更新对应记录时，首先检查该记录是否有对应外键若有也删除/更新外键在子表中的记录
-
set null：当父表中删除/更新对应记录时，首先检查该记录是否有对应外键若有设置子表中外键值为null（这就要要求外键允许取null）
-
-
-
多表查询：
-
多表关系：
- 一对多：在多的一方建立外键，指向一的一方的主键
- 一对一：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的
- 多对多：建立第三张中间表，中间表至少包含两个外键，分别关联两主键
-
多表查询：select * from emp, dept where dept_id = emp.id
-
多表查询分类：
-
（1）连接查询
内连接：相当于查询A、B交集部分数据
select 字段列表 from 表1、表2 where 条件…;
select 字段列表 from 表1 [inner] join 表2 on 连接条件…;
外连接：左外连接（查询左表所有数据，以及两张表交集部分数据） （会包含左表所有数据)
select 字段列表 from 表1 left [outer] join 表2 on 条件
右外连接（查询右表所有数据，以及两张表交集部分数据）(会包含右表所有数据）自连接：当前表与自身的连接查询，自连接必须使用表别名
select 字段列表 from 表A 别名A join 表A 别名B on 条件…;
（2）子查询
标量子查询：子查询返回的结果是单个值（数字、字符串、日期）最简单的形式 ，这种子查询成为标量子查询。（<,>…)
列子查询：返回的结果是一列（可以是多行），这种子查询称为列子查询(in,not in, any, some,all)
(3)联合查询union（去重后接在一起） union all(直接接在一起）【多张表的列数必须保持一致，字段类型也需要保持一致】
select 字段列表 from 表A…
union [all]
select 字段列表 from 表B…
多表查询 select * from emp,dept;#笛卡尔积
select * from emp,dept where dept_id=dept.id;
（1）连接查询
内连接：相当于查询A、B交集部分数据select 字段列表 from 表1、表2 where 条件…;
select 字段列表 from 表1 [inner] join 表2 on 连接条件…;
外连接：左外连接（查询左表所有数据，以及两张表交集部分数据） （会包含左表所有数据)
select 字段列表 from 表1 left [outer] join 表2 on 条件
右外连接（查询右表所有数据，以及两张表交集部分数据）(会包含右表所有数据）自连接：当前表与自身的连接查询，自连接必须使用表别名
select 字段列表 from 表A 别名A join 表A 别名B on 条件…;
(3)联合查询
联合查询union（去重后接在一起） union all(直接接在一起）【多张表的列数必须保持一致，字段类型也需要保持一致】select 字段列表 from 表A…
union [all]
select 字段列表 from 表B.
（2）子查询
标量子查询：子查询返回的结果是单个值（数字、字符串、日期）最简单的形式 ，这种子查询成为标量子查询。（<,>…)列子查询：返回的结果是一列（可以是多行），这种子查询称为列子查询
(in:在指定的集合范围之内
not in：不在指定的集合范围之内
any：子查询返回列表中，有任意一个满足即可
some：与any等同，使用some的地方都可以使用any
all：子查询返回列表的所有值都必须满足
行子查询返回的结果是一行（可以是多列）
=，<>,in ,not in
表子查询：返回的结果的多行多列
-
-
-
-
事务：是一组操作的集合，它是一个不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功，要么同时失败。
-
事务操作：
-
方式一
1.查看/设置事务提交方式 select @@autocommit;#查看事务的提交方式 1自动提交0手动提交set @@autocommit=0;#改为手动
2.事务
3.提交事务：commit;
4.回滚事务；rollback;
方式二
1.开启事务：start transaction 或begin2.提交事务：commit;
3.回滚事务；rollback;
-
-
事务的四大特性（acid）：
- 原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
- 一致性：事务完成时，必须使得所有数据都保持一致状态
- 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
- 持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
-
并发事务问题：
- 脏读：一个事务读到另一个事务还没有提交的数据
- 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
- 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影
-
事务隔离级别：
- 隔离级别(由低到高） 脏读 不可重复读 幻读
read uncommitted √ √ √
read committed × √ √
repeatable read × × √
serializable × × × - 查看事务隔离级别：select @@transaction_isolation;
- 设置事务隔离级别：set [session/global] transaction isolation level [read uncommitted/read committed/repeatable read/serializable]
- 注意：事务隔离级别越高，数据越安全，性能越低
- 隔离级别(由低到高） 脏读 不可重复读 幻读
-
-
MySQL体系结构：
-
-
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表而不是基于库的，所以存储引擎也可以被称为表引擎。（不同的引擎都有自己的应用场景）
默认存储引擎是InnoDB。
-
-
InnoDB
- InnoDB 是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB 是默认的 MySQL 引擎。
- 特点：
- DML 操作遵循 ACID 模型，支持事务
- 行级锁，提高并发访问性能
- 支持外键约束，保证数据的完整性和正确性
- 文件：
- xxx.ibd: xxx代表表名，InnoDB 引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。
- InnoDB逻辑存储结构：
-
MyISAM:
- 特点：
- 不支持事务，不支持外键
- 支持表锁，不支持行锁
- 访问速度快
- 文件：
- xxx.sdi: 存储表结构信息
- xxx.MYD: 存储数据
- xxx.MYI: 存储索引
- 特点：
-
Memory:
- Memory 引擎的表数据是存储在内存中的，受硬件问题、断电问题的影响，只能将这些表作为临时表或缓存使用。
- 特点：
- 存放在内存中，速度快
- hash索引（默认）
- 文件：
- xxx.sdi: 存储表结构信息
-
存储引擎特点：
-
特点 InnoDB MyISAM Memory 存储限制 64TB 有 有 事务安全 支持 - - 锁机制 行锁 表锁 表锁 B+tree索引 支持 支持 支持 Hash索引 - - 支持 全文索引 支持（5.6版本之后） 支持 - 空间使用 高 低 N/A 内存使用 高 低 中等 批量插入速度 低 高 高 支持外键 支持 - -
-
-
存储引擎的选择：
-
在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。
-
InnoDB: 如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，则 InnoDB 是比较合适的选择
-
MyISAM: 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那这个存储引擎是非常合适的。（被MongoDB取代）
-
Memory: 将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。Memory 的缺陷是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性（被redis取代）
-
电商中的足迹和评论适合使用 MyISAM 引擎，缓存适合使用 Memory 引擎。
-
-
-
索引：索引是帮助 MySQL 高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查询算法，这种数据结构就是索引。
-
优点：
- 提高数据检索效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
-
缺点：
- 索引列也是要占用空间的
- 索引大大提高了查询效率，但降低了更新的速度，比如 INSERT、UPDATE、DELETE
-
索引结构：
-
索引结构 描述
B+Tree 最常见的索引类型，大部分引擎都支持B+树索引
Hash 底层数据结构是用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询
R-Tree(空间索引) 空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-Text(全文索引) 是一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucene, Solr, ES -
索引 InnoDB MyISAM Memory
B+Tree索引 支持 支持 支持
Hash索引 不支持 不支持 支持
R-Tree索引 不支持 支持 不支持
Full-text 5.6版本后支持 支持 不支持
-
-
B+tree：
-
-
与 B-Tree 的区别：
- 所有的数据都会出现在叶子节点
- 叶子节点形成一个单向链表
MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能。
-
Hash：
-
-
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。 -
特点：
-
Hash索引只能用于对等比较（=、in），不支持范围查询（betwwn、>、<、…）
-
无法利用索引完成排序操作
-
查询效率高，通常只需要一次检索就可以了，效率通常要高于 B+Tree 索引
-
存储引擎支持：
-
Memory
-
InnoDB: 具有自适应hash功能，hash索引是存储引擎根据 B+Tree 索引在指定条件下自动构建的
-
-
-
-
索引分类：
-
分类 含义 特点 关键字 主键索引 针对于表中主键创建的索引 默认自动创建，只能有一个 PRIMARY 唯一索引 避免同一个表中某数据列中的值重复 可以有多个 UNIQUE 常规索引 快速定位特定数据 可以有多个 全文索引 全文索引查找的是文本中的关键词，而不是比较索引中的值 可以有多个 FULLTEXT -
在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：
-
分类 含义 特点 聚集索引(Clustered Index) 将数据存储与索引放一块，索引结构的叶子节点保存了行数据 必须有，而且只有一个 二级索引(Secondary Index) 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 可以存在多个 -
-
聚集索引选取规则：
- 如果存在主键，主键索引就是聚集索引
- 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
- 如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引
-
语法
-
创建索引：
- CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, …);
- 如果不加 CREATE 后面不加索引类型参数，则创建的是常规索引
-
查看索引：
- SHOW INDEX FROM table_name;
-
删除索引：
- DROP INDEX index_name ON table_name;
-
-
使用规则：
-
最左前缀法则：
- 如果索引关联了多列（联合索引），要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。
- 如果跳跃某一列，索引将部分失效（后面的字段索引失效）
- ps：最左前缀和查询位置的防止无关，最左指的是最左边的字段是否存在
- 联合索引中，出现范围查询（<,>）,范围查询右侧的列索引将会失效，可以用(<=, >=)来规避这种情况
-
索引失效情况
- 在索引列上进行运算操作，索引将失效。如：explain select * from tb_user where substring(phone, 10, 2) = ‘15’;
- 字符串类型字段使用时，不加引号，索引将失效。如：explain select * from tb_user where phone = 17799990015;，此处phone的值没有加引号
- 模糊查询中，如果仅仅是尾部模糊匹配，索引不会是失效；如果是头部模糊匹配，索引失效。如：explain select * from tb_user where profession like ‘%工程’;，前后都有 % 也会失效。
- 用 or 分割开的条件，如果 or 其中一个条件的列没有索引，那么涉及的索引都不会被用到，要解决这个问题就需要把查询的字段都建立索引。
- 如果 MySQL 评估使用索引比全表更慢，则不使用索引，例如is not null 如果null值比较多，就会走索引，否则就不会走索引。
-
SQL提示：
- 是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
- 例如，使用索引：
explain select * from tb_user use index(idx_user_pro) where profession=“软件工程”; - 不使用哪个索引：
explain select * from tb_user ignore index(idx_user_pro) where profession=“软件工程”; - 必须使用哪个索引：
explain select * from tb_user force index(idx_user_pro) where profession=“软件工程”; - use 是建议，实际使用哪个索引 MySQL 还会自己权衡运行速度去更改，force就是无论如何都强制使用该索引。
-
覆盖查询&回表查询
-
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能找到），减少 select *。
-
explain 中 extra 字段含义：
using index condition：查找使用了索引，但是需要回表查询数据
using where; using index;：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询 -
如果在聚集索引中直接能找到对应的行，则直接返回行数据，只需要一次查询，哪怕是select *；如果在辅助索引中找聚集索引，如select id, name from xxx where name=‘xxx’;，也只需要通过辅助索引(name)查找到对应的id，返回name和name索引对应的id即可，只需要一次查询；如果是通过辅助索引查找其他字段，则需要回表查询，如select id, name, gender from xxx where name=‘xxx’;（因为这里出现了索引表之外的gender字段，所以要避免回表查询，只需在联合索引中将gender字段也包含进去即可）
-
ps: 覆盖索引不需要再进行回表查询，一次索引扫描就可以了。
所以尽量不要用select *，容易出现回表查询，降低效率，除非有联合索引包含了所有字段
-
-
前缀索引：
-
当字段类型为字符串（varchar, text等）时，有时候需要索引很长（大文本）的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率，此时可以只降字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
-
语法：create index idx_xxxx on table_name(columnn(n));
前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。 -
求选择性公式：
select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email, 1, 5)) / count(*) from tb_user;
show index 里面的sub_part可以看到接取的长度
ps:前缀索引大概率要回表查询！
-
-
单列索引&联合索引：
- 单列索引：即一个索引只包含单个列
- 联合索引：即一个索引包含了多个列
- 在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。
- 单列索引情况：
explain select id, phone, name from tb_user where phone = ‘17799990010’ and name = ‘韩信’;
这句只会用到phone索引字段 - 注意事项
多条件联合查询时，MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询
-
设计原则：
- 针对于数据量较大，且查询比较频繁的表建立索引
- 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
- 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
- 如果是字符串类型的字段，字段长度较长，可以针对于字段的特点，建立前缀索引
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
- 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率
- 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询
-
-
-
SQL优化：
-
普通插入：
- 采用批量插入（一次插入的数据不建议超过1000条）
- 手动提交事务
- 主键顺序插入
-
大批量插入：
-
如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令插入。
-
# 客户端连接服务端时，加上参数 --local-infile（这一行在bash/cmd界面输入） mysql --local-infile -u root -p # 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关 set global local_infile = 1; select @@local_infile; # 执行load指令将准备好的数据，加载到表结构中 load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';
-
-
主键优化:
- 数据组织方式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（Index organized table, IOT）
-
页分裂：页可以为空，也可以填充一般，也可以填充100%，每个页包含了2-N行数据（如果一行数据过大，会行溢出），根据主键排列。
-
页合并：当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录到达 MERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前后）看看是否可以将这两个页合并以优化空间使用。
-
主键设计原则：
- 满足业务需求的情况下，尽量降低主键的长度
- 插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键
- 尽量不要使用 UUID 做主键或者是其他的自然主键，如身份证号
- 业务操作时，避免对主键的修改
-
order by 优化：
- Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
- Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高
- 如果order by字段全部使用升序排序或者降序排序，则都会走索引，但是如果一个字段升序排序，另一个字段降序排序，则不会走索引，explain的extra信息显示的是Using index, Using filesort，如果要优化掉Using filesort，则需要另外再创建一个索引，如：create index idx_user_age_phone_ad on tb_user(age asc, phone desc);，此时使用select id, age, phone from tb_user order by age asc, phone desc;会全部走索引
- 总结：
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
尽量使用覆盖索引
多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k）
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
-
group by优化：
- 在分组操作时，可以通过索引来提高效率
- 分组操作时，索引的使用也是满足最左前缀法则的
-
limit 优化：
- 常见的问题如limit 2000000, 10，此时需要 MySQL 排序前2000000条记录，但仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。
优化方案：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化
- 常见的问题如limit 2000000, 10，此时需要 MySQL 排序前2000000条记录，但仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。
-
count优化：
- MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高（前提是不适用where）；
- InnoDB 在执行 count() 时，需要把数据一行一行地从引擎里面读出来，然后累计计数。
优化方案：自己计数，如创建key-value表存储在内存或硬盘，或者是用redis - count的几种用法：
- 如果count函数的参数（count里面写的那个字段）不是NULL（字段值不为NULL），累计值就加一，最后返回累计值
- 用法：count(*)、count(主键)、count(字段)、count(1)
- count(主键)跟count()一样，因为主键不能为空；count(字段)只计算字段值不为NULL的行；count(1)引擎会为每行添加一个1，然后就count这个1，返回结果也跟count()一样；count(null)返回0
- 各种用法的性能：
- count(主键)：InnoDB引擎会遍历整张表，把每行的主键id值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加（主键不可能为空）
- count(字段)：没有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加；有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加
- count(1)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一层，放一个数字 1 进去，直接按行进行累加
- count(*)：InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加
- 按效率排序：count(字段) < count(主键) < count(1) < count()，所以尽量使用 count()
-
update 优化：
- InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。
-
-
-
锁：
-
概述：
-
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/0)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
-
全局锁：锁定数据库中的所有表
-
表级锁：锁住当前数据的某张表
-
行级锁：锁住表中某一行中的数据
-
-
全局锁：全局锁就是对整个数据库上锁，只读不改
-
表级锁：
-
分类：
- 表共享读锁（read lock）
- 表独占写锁（write lock）
-
语法：
- 加锁：lock tables 表名 … read/write
- 释放锁：unlock tables/客户端断开连接
-
元数据锁（MDL）：
- MDL上锁过程是系统自动控制，无需显示使用，在访问一张表的时候会自动加上
- MDL主要作用是维护元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作
-
意向锁：
-
为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
1.意向共享锁（lS) : 由语句select … lock in share mode添加，与表锁read兼容，与表锁排它锁write互斥。
2.意向排他锁（IX)∶由insert、update、delete、select … for update添加，与表锁共享锁read及排它锁互斥，意向锁之间不会互斥。
-
-
-
行级锁：
-
行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高应用在lnnoDB存储引擎中。
-
InnoDB的数据是基于索引组织的，行锁是通过对索引项加锁来实现的，而不是对记录加的锁
-
分类：
-
行锁：锁定单个行的锁，防止其他事务对此进行update和delete。
-
两种类型：
-
共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁
-
排它锁：允许获取排它锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排它锁
-
默认情况下，InnoDB在REPEATABLE READ 事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读
- 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁
- InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中所有记录加锁，此时会升级为表锁
-
-
-
间隙锁：锁定索引记录间隙，防止其他事务进行insert。
-
临键锁：前两者的组合，同时锁住数据和间隙
-
间隙锁/临键锁
- 默认情况下，InnoDB在REPEATABLE READ 事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读
- 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁
- 索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁
- 索引上的范围查询（唯一索引）–会访问到不满足条件的第一个值为止
- 默认情况下，InnoDB在REPEATABLE READ 事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读
-
总结：
- 表锁和行锁都有两种形式：共享锁和排它锁；
意向锁是为了优化判断表级锁是否兼容行级锁这一过程，即：会自动产生这么一个代表行级锁类型的锁，这样，表级锁直接和意向判断，就可以知道是否兼容了。
-
-
-
-
-
InnoDB引擎：
- 逻辑存储结构：
- 逻辑存储结构：
nnoDB使用next-key锁进行搜索和索引扫描，以防止幻读
- 针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁
- InnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中所有记录加锁，此时会升级为表锁
- 间隙锁：锁定索引记录间隙，防止其他事务进行insert。
- 临键锁：前两者的组合，同时锁住数据和间隙
- 间隙锁/临键锁
- 默认情况下，InnoDB在REPEATABLE READ 事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读
- 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁
- 索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁
- 索引上的范围查询（唯一索引）--会访问到不满足条件的第一个值为止
- 总结：
- 表锁和行锁都有两种形式：共享锁和排它锁；
- `意向锁`是为了优化**判断表级锁是否兼容行级锁**这一过程，即：会自动产生这么一个代表行级锁类型的锁，这样，表级锁直接和意向判断，就可以知道是否兼容了。
42万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
