Sql
优化技巧
小技巧
-
避免使用select *
-
可以确保不会出现重复数据的情况下，选择union all便是最方便快捷的选择用union all代替union
-
小表驱动大表
- in 适用于左边大表，右边小表。
- exists 适用于左边小表，右边大表
-
主键优化
-
数据逻辑存储结构
- TableSpece表空间
- Segment段
- Extent区1M
- Page页16kb
- Row行
- 插入和删除会导致页分裂与页合并
-
主键设计原则
- 降低主键长度
- 尽量选择顺序插入，自增主键
- 尽量不要使用UUID做主键
-
-
insert优化
-
批量操作
-
手动提交事务
-
主键顺序插入
- 性能高于乱序插入，乱序插入容易页分裂
-
大批量插入数据可以用加载文件的形式，然后用load指令
-
-
limit优化
- where id > 1000000 limit 20要求id是连续的，并且有序的
- 通过覆盖索引加子查询的方式来优化大数据的查询
-
count优化
- 按照效率排序的话，count(字段）<count(主键id)<count(1)≈count(),所以尽量使用count()。
- 通过主键和字段都需要先把值取出来拿到服务层后再累加
- count(1),不取值，返回的每一行放一个数字1，直接进行累加
- count(*)，不取值，直接累加
- count(字段)，如果字段是null是不计数的
-
update优化
- 要根据索引字段查询，否则可能升级为表锁
-
order by 优化
-
根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
-
尽量使用覆盖索引。
-
多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。
-
如果不可避免的出现filesort,大数据量排序时，可以适当增大排序缓冲区大小sort buffer size(默认256k)。
-
类型
-
using filesort
- 需要将返回的结果在排序缓冲区排序
-
using index
- 直接通过索引返回数据，性能高
-
-
-
group by优化
- 使用where条件在分组前，就把多余的数据过滤掉了，这样分组时效率就会更高一些
- 分组操作时，可以通过索引来提高效率
- 分组操作时，索引的使用也是满足最左前缀原则
-
用连接查询代替子查询
- mysql执行子查询时，需要创建临时表
-
控制join表的数量
-
join
-
left join
-
求两个表的交集外加左表剩下的数据
- 使用left join关联，mysql会默认用left join关键字左边的表，去驱动它右边的表
-
-
inner join：求两个表交集的数据
- 使用inner join关联，mysql会自动选择两张表中的小表，去驱动大表
-
-
控制索引的数量
- 在insert、update和delete操作时，需要更新B+树索引。如果索引过多，会消耗很多额外的性能
-
选择合理的字段类型
- 能用数字类型，就不用字符串
- 尽可能使用小的类型
- 长度固定的字符串字段，用char类型,长度可变的字符串字段，用varchar类型。金额字段用decimal，避免精度丢失问题。
- char表示固定字符串类型，该类型的字段存储空间的固定的，会浪费存储空间
- varchar表示变长字符串类型，该类型的字段存储空间会根据实际数据的长度调整，不会浪费存储空间
-
索引优化
-
慢查询日志，默认超过10秒算慢
-
查看执行频次
-
SHOW GLOBAL STATUS LIKE ‘Com________’
- 可以查看具体的select、update、delete等操作次数
-
-
SQL性能分析
-
profile
- #查看每一条SQL的耗时基本情况
show profiles; - #查看指定query id的SQL语句各个阶段的耗时情况
show profile for query query_id; - #查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
- #查看每一条SQL的耗时基本情况
-
-
使用explain命令，查看mysql的执行计划
-
重点关注
-
key（查看有没有使用索引）
-
key_len（查看索引使用是否充分）
-
type（查看索引类型）
- system(表只有一条记录)
- const(通过索引一次就能找到)
- eq_ref(用于主键或者唯一索引扫描)
- ref(用于非主键和唯一索引扫描)
- fulltext(全文扫描)
- ref_or_null(类似于ref,还会扫描null的数据)
- index_merge(多种索引合并的方式扫描)
- unique_subquery(类似于eq_ref,条件用于in子查询)
- index_subquery(类似于unique_subquery,条件用于in子查询，但条件非主键或唯一索引)
- range(范围扫描)
- index(全索引扫描)
- All(全表扫描)
- system > const > eq_ref > ref > range > index > ALL
-
Extra（查看附加信息：排序、临时表、where条件为false等）
-
-
-
-
索引失效原因
-
不满足最左前缀原则
-
使用了select *
-
尽量用覆盖索引
- using index condition:查找使用了索引，但是需要回表查询数据
- using where;using index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
-
-
范围索引列没有加=号等
-
索引列上有计算
-
字符类型没加引号
-
like查询左边有%
-
用is null和is not null有没有走索引，取决于数据分布情况
- 字段全空，is not null 更稀有走索引，字段全有值，is null更稀有走索引
-
使用or关键字时没有注意
- or分割条件，要所有条件都走索引才能走索引
-
数据分布影响
- 如果MySQL评估使用索引比全表更慢，则不使用索引。
-
-
force index来强制查询sql走某个索引
-
user index是建议用 force index是强制
- ignore忽略索引
-
-
前缀索引
-
截取指定长度当索引
-
当字段类型为字符串(varchar,text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘O,影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。
-
根据索引的选择性来决定,选择性越高，查询效率越高，选择性是指不重复的索引值（基数）和数据表的记录总数的比值
-
语法
- create index idx_xxoxx on table_name(column(n))
-
-
单列索引&联合索引
- 推荐使用联合索引
-
索引设计原则
- 1.针对于数据量较大，且查询比较频繁的表建立索引。
- 2.针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
- 3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
- 4.如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
- 5,尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
- 6.要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
- 7.如果索引列不能存储null值，请在创建表时使用not nullL约束它。当优化器知道每列是否包含null值时，它可以更好地确定哪个索引最有效地用于查询。
索引
底层结构
-
Hash表
-
Hash 索引不支持顺序和范围查询
- Memory引擎支持hash索引
-
-
B树&B+树
-
异同
- B 树的所有节点既存放键(key) 也存放 数据(data)，而 B+树只有叶子节点存放 key 和 data，其他内节点只存放 key。
- B 树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点，形成单线链表，mysql优化成双向循环链表
- B 树的检索的过程相当于对范围内的每个节点的关键字做二分查找
可能还没有到达叶子节点，检索就结束了。而 B+树的检索效率就很稳定了，任何查找都是从根节点到叶子节点的过程，叶子节点的顺序检索很明显。 - 相对于二叉树来说，层级更少，搜索效率更高
- 正常的树的高度不会超过3，因为超过数据量太大就需要考虑分库分表了
-
-
R-tree(空间索引)
- MyISAM引擎的一个特殊索引类型
-
Full-text(全文索引)
- 通过建立倒排索引，快速匹配文档的方式
索引类型
-
主键索引
- 关键字PRIMARY，只能有一个
-
唯一索引
- 关键字UNIQUE,可以有多个，避免列值重复
-
普通索引
- 可以有多个
-
全文索引
- 关键字FULLTEXT，可以有多个
inodb分为聚集索引与非聚集索引
-
聚集索引（必须有，且只有一个）
- 查询速度非常的快，因为整个 B+树本身就是一颗多叉平衡树，叶子节点也都是有序的，定位到索引的节点，就相当于定位到了数据
- 依赖于有序的数据、更新代价大
- 如果存在主键，主键索引就是聚集索引。
- 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引。
- 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
- 叶子节点挂的是整条记录的数据
-
非聚集索引（索引结构和数据分开存放的索引）（二级索引）
- 更新代价比聚集索引要小
- 可能会二次查询(回表)
- 非聚集索引不一定回表查询。
- 概念：二级索引的叶子节点存储的数据是主键。也就是说，通过二级索引，可以定位主键的位置。
覆盖索引
- 需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询
联合索引
- 使用表中的多个字段创建索引，就是 联合索引
最左前缀匹配原则
- 在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询，如 >、<、between 和 以%开头的like查询 等条件，才会停止匹配。
- 如果全部存在，顺序可以颠倒，内部会自己优化
参考链接：
https://blog.csdn.net/Dream_xun/article/details/123492022
https://mp.weixin.qq.com/s?__biz=MzkwNjMwMTgzMQ==&mid=2247490262&idx=1&sn=a67f610afa984ecca130a54a3be453ab&source=41#wechat_redirect
Mysql
悲观锁
-
对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处于锁定状态
-
包括共享锁和排它锁
-
共享锁
- 不堵塞，多个用户可以同时读一个资源，互不干扰。
-
排他锁
- 一个写锁会阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用户读取正在写入的资源。
-
悲观锁一般就是利用类似 SELECT … FOR UPDATE 这样的语句，对数据加锁，避免其他事务意外修改数据
乐观锁
- 乐观锁，实际就是通过版本号，从而实现 CAS 原子性更新
- MVCC，其本质就可以看作是种乐观锁机制
死锁
-
互斥条件
- 一段时间内某资源只由一个进程占用
-
请求和保持条件
- 请求新的被占用，已持有不释放
-
不剥夺条件
- 已获得的资源未使用玩，不能被剥夺
-
环路等待条件
- 发生死锁时，必然存在一个进程——资源的环形链p0,等p1,p1等p2,p2在等p0
-
最大限度地降低死锁
-
设置获得锁的超时时间
-
按同一顺序访问对象
- 这个是最重要的方式
-
避免事务中的用户交互
-
保持事务简短并在一个批处理中
-
使用低隔离级别
-
使用绑定连接
-
锁机制
-
Innodb
-
锁粒度
-
全局锁
- 在InnoDB引擎中，我们可以在备份时加上参数-single–transaction参数来完成不加锁的一致性数据备份。
- 一般是备份的时候使用
-
表锁
-
表锁
- 表共享读锁(read lock)
- 表独占写锁(write lock)
- 1.加锁：lock tables表名.，read/write。
2.释放锁：unlock tables/客户端断妍连接 - 读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。
-
元数据锁（meta data lock,MDL），可以理解为表结构锁
- MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。
在MySQL5.5中引入了MDL,当对一张表进行增删改查的时候，加MDL读锁（共享）；当对表结构进行变更操作的时候，加MDL写锁（排他）。 - select object_type,object_schema,object_name,lock_type,lock_ duration from performance_schema.metadata_locks
查看元数据锁
- MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。
-
意向锁
- 为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
- 1.意向共享锁(IS):与表锁共享锁(read)兼容，与表锁排它锁(write)互斥。
2.意向排他锁(IX):与表锁共享锁(read)及排它锁(write)都互斥。意向锁之间不会互斥。 - select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks
查看意向锁
-
-
行锁(3种)
-
行锁(Record Lock)
- RC、RR隔离级别下都支持
-
间隙锁（GAP Lock）
- RR级别下支持
- 间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁
- 锁住的是两条记录的间隙
- 索引上的等值查询(唯一索引)，给不存在的记录加锁时，优化为间隙锁。简单理解通过不存在的主键更新记录就是间隙锁，而且范围不包含当前主键
-
临键锁(next-key )
- RR隔离级别下支持
- 即 record lock + gap lock ，是通过在 index 上加 lock 实现的
- 索引上的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，加next-key lock。比如给年龄加索引，就会给年龄位某个具值比如18的前后加索引，因为以后还有可能插入年龄为18的值。
- 索引上的范围查询（唯一索引）-会访问到不满足条件的第一个值为止，会加上对应的临键锁
- 锁住的是记录和间隙
-
行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁
-
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks
查看行锁 -
1.如果检索数据为 unique index ，则降级为 record lock 行锁
2.如果检索数据是普通 index ，则为 next-key lock
3.如果检索数据没有 index ，则直接锁住全表，即表锁
-
-
互斥与兼容一般是指不同的mysql客户端访问数据
-
-
-
MyISAM 直接使用表锁
MVCC
-
多版本并发控制（MVCC），是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。 这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读
-
实现方法
-
隐藏字段
-
DB_TRX_ID事务id
-
DB_ROLL_PTR回滚指针
-
DB_ROW_ID
- 如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引
-
-
readview做可见性判断
-
max_trx_id
- 目前出现过的最大的事务 ID+1，即下一个将被分配的事务 ID。大于等于这个 ID 的数据版本均不可见
-
min_trx_id
- 活跃事务列表 m_ids 中最小的事务 ID，小于这个 ID 的数据版本均可见
-
m_ids
- Read View 创建时其他未提交的活跃事务 ID 列表
-
m_creator_trx_id
- 创建该 Read View 的事务 ID
-
trx_id:代表是当前事务ID.
①.trx_id=creator_.trx_id?可以访问该版本
②.trx_id<min_trx_id?可以访问该版本
③.tr%id>max_trx_id?不可以访问该版本
④.min_trx_id<=trx_id<=max_trx_id?如果trx_id不在mids中是可以访问该版本的
-
-
undo-log
-
数据可见性算法
-
-
MVCC解决不可重复读问题
-
MVCC+ Next-key Lock 来解决幻读问题
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
