逻辑架构
- 连接层
- 业务层
- sql 接口:提供DML,DDL,触发器,视图等等,分类sql类型
- 解析器:解析sql
- 优化器:优化成mysql认为的最好的sql,并选择性使用索引
- 缓存:缓存sql及sql执行后的结果集
- 引擎层
- 数据存储层
优化器
优化sql
优化器会优化sql,能够识别并等价变化,移除一些恒等成立或恒等不成立的判断,比如a>5 and 1=1 会被优化成 a>5 ;部分外连接会被优化成内连接。
选择性使用索引
优化器会通过record_in_range()接口向引擎传入两个边界值,获取大概查询数量,MyISAM是精确值,innodb是估值。通过info()接口返回各种类型的数据,包括索引的基数(每个键值有多少记录)。通过复杂算法估算执行成本,选择性使用索引或则直接抛弃索引的使用。又时sql过于复杂,有可能做出错误的选择。
缓存
缓存执行过的sql及结果集,通过tv键值方式保存在内存中
当sql中含有不确定数据,如 now current_date 不会缓存
优势
后续相应sql不再走解析优化,io。
劣势
为了保证缓存的内容和实际数据绝对一致,当某个数据表发生了更新、删除及插入操作,MySql都会强制使所有引用到该表的查询SQL的缓存失效
参数设置:
SHOW VARIABLES LIKE '%query_cache%';
- query_cache_limit:允许缓存的单条查询结果集的最大容量,默认是1MB,超过此参数设置的查询结果集将不会被缓存
- query_cache_min_res_unit:设置查询缓存Query Cache每次分配内存的最小空间大小,即每个查询的缓存最小占用的内存空间大小
- query_cache_size:设置 Query Cache 所使用的内存大小,默认值为0,大小必须是1024的整数倍,如果不是整数倍,MySQL 会自动调整降低最小量以达到1024的倍数
- query_cache_type:控制 Query Cache 功能的开关,可以设置为0、1、2三种,意义分别如下:
- 0:不会使用 Query Cache
- 1:当SELECT语句中使用SQL_NO_CACHE,将不使用Query Cache;
- 2:当SELECT语句中使用了SQL_CACHE,才使用Query Cache。
- query_cache_wlock_invalidate:
- 1:写锁定的同时将失效该表相关的所有Query Cache
- 0:锁定时刻仍然允许读取该表相关的Query Cache ,默认
缓存的内存管理
-
MySQL缓存机制会在内存中开辟一块内存(query_cache_size)区来维护缓存数据,其中大概有40K的空间是用来维护缓存数据的元数据的,例如空间内存、数据表和查询结果的映射,SQL和查询结果的映射。
-
MySQL缓存机制将大内存块分为小内存块(query_cache_min_res_unit),每个小块中存储自身的类型、大小和查询结果数据,还有前后内存块的指针。
-
MySQL缓存机制会在SQL查询开始(还未得到结果)时就去申请一块内存空间,所以即使缓存数据没有达到这个大小也需要占用申请的内存块空间(like linux filesystem’s block)。如果超出申请内存块的大小,则需要再申请一个内存块。当查询完成发现申请的内存有富余,则会将富余的内存空间释放掉,因而可能会造成内存碎片。
存储引擎
-- 查看当前,默认引擎
show variables like '%storage_engine%';
-- 查看引擎相关信息,之情情况
show engines;
MyISAM | InnoDb | |
---|---|---|
主外键 | n | y |
事务 | n | y |
锁 | 支持表锁 | 支持行锁(行锁跟索引有关) |
缓存 | 只缓存索引,不缓存真实数据 | 索引与真实数据都缓存,对内存要求更高 |
表空间 | 小 | 大 |
关注点 | 性能 | 事务,并发性 |
默认安装 | y | y |
sql
1、sql解析过程
索引
索引是帮助mysql高效获取数据的数据结构。
一张表的索引最好不要超过5个
主键是一定在索引中的
mysql中null列会统一在索引的最左侧,所以使用is null等还是会用索引
常见的索引都是B树结构,其中聚集索引(聚簇),次要索引,覆盖索引,复合索引(组合),前缀索引,唯一索引默认使用B+树。除此之外还有哈希索引
优势
- 提高数据检索效率,降低数据库IO成本
- 通过索引排序,降低排序成本,降低cpu成本
劣势
- 索引也是一张表,保存了主键与索引字段,并指向了实体表的数据,要占用空间
- 提高查询性能,降低更新修改速度
- 大数据量的表中创建较多的组合索引,索引文件增速飞快
索引数据结构
索引按照数据结构层面分为哈希索引与B树索引
hash索引
innodb上创建hash索引会得到B树索引,但是innodb支持自适应哈希索引,用户不可控
创建索引时用 using hash
create index index_test using hash on test1(id);
B树索引
默认或使用 using btree
create index index_test using btree on test1(id);
create index index_test on test1(id);
b树索引 | 哈希索引 | |
---|---|---|
等值 | 需比较 | 直接命中 |
泛查询 | Y | N |
排序 | Y | N |
查询方式 | 最左前缀 | 全键匹配 |
B树索引的结构
-
B+树
节点不存储数据,叶子节点才有数据与数据行的地址指针。
小数据在左,大数据在右,两者之间在中间。
叶子节点的数据为创建该索引的列与到聚簇索引的key
索引类型
大类上分成聚簇跟普通索引,聚簇索引指向真实数据行,直接定位,一次b树遍历获取数据。普通索引的叶子节点通常指向聚簇索引,与索引列的数据。
回表查询:通过索引查询后,还有列不在索引中,需要再次查询聚簇索引树。
-
单值索引:一个索引只有一个列
范围查询,通配符在第一个字符上,将不会使用索引
-
唯一索引:索引列的值必须唯一,允许空值
-
复合索引:一个索引包含多个列。创建时注意顺序
创建一个index(a,b,c),实际上是创建了index(a),index(a,b),index(a,b,c)三个索引,按照创建语句中列的顺序创建。当前面的列没在where中,将不会使用对应索引
-
全文索引: 一般是创建在text上的索引,支持各种字符类型,包括char、varchar。
-
-- 索引的创建 create FULLTEXT index test_index on person(name,info); -- 全文收索,该语句没有索引也能执行,有索引更快罢了 select * from person where match(name,info) against ('zhang asdasd');
-
5.6以前只有MyISAM支持全文索引,5.6 innodb也支持
-
全文索引是通过词频,词性来创建索引的,一个100个单词的字段,可能就有100个索引产生,索引增长迅速
-
词频超过50% 或则ft_stop_word_file指定的文件记录的停用词,长度大于ft_min_word_len、小于ft_max_word_len的词语都不会在索引中
-
在查询中存在match against,并且存在对应列的索引,那么优化器一定会使用全文索引
-
多列参与的全文索引不关注哪个关键字在哪个字段上
-
容易产生碎片
-
布尔全文索引修饰符 abc 包含abc的相关度更高 ~abc 包含abc的相关度更低 -abc 不能有abc +abc 必须要有abc abc* 包含以abc开头的单词相关度更高
-
select * from person where match(name,info) against ('+zhang +asdasd' in boolean mode);
-
-
聚簇索引:聚簇索引是一种数据存储方式,一张表只能有一个,将数据行于相邻的键值紧凑存储在一起。索引项的顺序与数据表的物理顺序是一致的。通常是pk作为索引,没有pk则使用第一个唯一索引,都没有创建一个隐藏的row_id。
- 更改聚簇索引代价很高,数据会移动到新的位置。
- 插入速度与索引列的顺序有很大关系,索引无序,插入严重影响性能
- 插入无续数据,或则更新索引列导致数据变动,可能导致”页分裂“问题
- 页分裂:插入行到某个已满的页中,存储引擎会把该页分裂成两个页来容纳该行,
页分裂会导致表占用更多空间,同时也会导致查询慢(增加寻道时间)
-
覆盖索引:一个索引,运行sql后,所有的列都在索引中,该索引就叫覆盖索引。此时不会回表查询。通常Extra中标记为using index 即是覆盖索引。
-
前缀索引:创建索引时加上长度,让索引变的更小。如 index on table(column(6)) ,表示将该列前6个作为索引
-
前缀索引长度确定:要求创建的索引尽量与原索引定位上接近。可通过 select count(distinct left(column, n))/count(*) from table。所得值越大越好。
-
缺点,排序,分组时该索引将无法使用到
-
-
不可见索引:设置索引为不可见,优化器将不再使用索引,用于测试删除索引对查询的影响。8.0版本才支持
alter table test alter index test_index invisible;
-
降序索引:标识索引排序方式,8.0版本才支持
create INDEX idx1 on test(c1 ASC, c2 ASC),
性能分析
explain sql.....
-
id:select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
-
相同id,从上到下顺序加载
-
不同id,数值越大优先级越高
-
-
select_type:查询类型
- simple:简单的查询,不存在子查询,union等
- primary:查询中包含任何复杂的子部分,最外层标记标记为primary
- subquery:子查询
- derived:衍生,查询中的临时表
- union:union后的查询
- union result:union 表获取的结果
-
table:表
-
type:
-
all:全表扫描
-
index:全索引扫描,如select c1 from t1; c1上有索引
-
range:泛查询
-
ref:非唯一性索引扫描,通过一个索引查询后,返回了很多数据
-
eq_ref:唯一性索引扫描,表中只有一条记录与之匹配。一般在索引用主键或唯一索引时出现
-
const:通过索引一次就获取到一个索引指向的全部数据
-
system:表中只存在一条数据会出现
-
null:没查询到数据
-
优->劣:system>const>eq_ref>ref>range>index>all
-
-
possible_keys::可以用到哪些索引
-
key:真正使用的索引
-
key_len:索引长度
-
ref:显示索引中哪些列被使用了。常见的有常数 where a=const 为const, 外键 a.id = b.id b.id 等等
-
row:读取的数据行数,该值越小,io消耗更少
-
filtered:返回的数据集占rows的百分比,该值越大,说明索引越精准
-
extra:额外信息
- using filesort:排序的字段没有使用到索引,数据量较少时也有可能出现
- using temporary:使用临时表
- using index:使用了覆盖索引,当同时存在using where 标名有用来当查询条件
- using where:查询条件用到索引
- using jion buffer: 使用了连接缓存
- impossible where :where子句中总使false
索引优化
性能下降的原因:
- sql性能差
- 索引失效,索引在某些sql下无法生效
- 关联查询过多
- 服务器调优及各个参数设置(缓冲,线程数等设置不合理)
- mysql 4.1以后使用的是单路排序,一次性读取所需数据行到sort buff中,如果sort buff 满了,将多次读取io,生成临时表后再排序。如果查询列大于 max_length_for_sort_data 或则排序的对象是text或者blob,也会使用多路排序
索引失效的情况有:
-
组合索引前项字段未被使用,索引组中,出现泛查询,则使用该组索引
-
如:index(a,b,c) 在b中用了like 就只能用到index(a,b)
-
通配符开头
-
ont in ,!= ,<>等反向操作
-
前缀索引的排序
-
排序的两列,排序方式不一样,如:orde by a asc ,b desc
-
带运算的不走索引,如 where a= 2+3
-
多条件时,若是单列索引,只会用到其中一个。
-
字符串不加引号
-
带or 的查询,听说后面会支持,目前5.*版本不支持
-
数据量较少时,优化器判断走索引更慢的情况下
如排序时,查询了所有的列 select *
-
查询列较多,范围较大,占比大
如查询user age>20 大部分数据都是大于20的
查询age = 20 大部分是20,且数据量不时,此时如果有组合索引,其他条件加入导致应读取的数据下降时,索引又会生效,组合索引在部分列占比较大情况下,是优于单列索引的,但是要注意列的顺序
-
is not null,is null 索引失效问题应该跟占比有关系
优化策略
-
尽量使用组合索引
-
左连接,右表索引。右连接左表索引
-
条件+排序,泛查询不加入索引中
-
组合索引中,泛查询放后面
-
select count(列) from table 该列必须要有索引,否则将全表扫描。除此之外可以用 count(1),count(*)替代
-
in 小表用子查询,exists 大表用子查询。
大小表指的是结果集,如果通过其他索引筛掉了大部分数据,相差不大
exists 的遍历是经过优化的,不是逐一对比
-
not exists 效率一定高于 not in 。not in 在内外表上都是全表扫描,not exsit 子查询还是会用到索引
-
查询数据能达到覆盖索引最佳(只查询需要的字段,且包含在组合索引中)
-
varchar类型在不用于排序,可使用前缀索引放组合索引最后,减少索引占用空间
-
如果优化器选择了错误的索引,则可以使用索引提示将其禁用,并强制优化器做出其他选择
索引提示
MySql共有三种索引提示,分别是:USE INDEX、IGNORE INDEX和FORCE INDEX
-
use index:use index告诉MySql用列表中的其中一个索引去做本次查询
SELECT * FROM table1 USE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3;
-
ignore index:ignore index告诉mysql不要使用某些索引去做本次查询
SELECT * FROM table1 IGNORE INDEX (col3_index) WHERE col1=1 AND col2=2 AND col3=3;
-
force index:force index和use index功能类似,都是告诉mySQL去使用某些索引。
区别是:
force index:全表扫描被假定为需要很高代价,除非不能使用索引,否则不会考虑全表扫描;
use index:如果MySql觉得全表扫描代价更低的话,仍然会使用全表扫描
SELECT * FROM table1 FORCE INDEX (col3_index) WHERE col1=1 AND col2=2 AND col3=3;
慢sql查询
默认关闭,对mysql性能有一定损耗
-- 查看慢日志查询情况,是否开启,日志位置
show variables like '%slow_query_log%';
-- 开启日志,mysql重启后会关闭
set global slow_query_log=1;
-- 查看慢查询阈值
show variables like '%long_query_time%';
-- 设置慢查询时间阈值
set global long_query_time = 10;
mysqldumpslow:mysql自带的慢查询日志分析工具
-- 得到返回记录集最多的10个sql
mysqldumpslow -s r -t 10 /var/lib/mysql/ubuntu-slow.log
-- 访问记录集最多的10个sql
mysqldumpslow -s c -t 10 /var/lib/mysql/ubuntu-slow.log
-- 按时间排序前十条含有...的sql
mysqldumpslow -s t -t 10 -g 'left join' /var/lib/mysql/ubuntu-slow.log
-- 与more 结合使用,防止爆屏
.......| more
show profile
http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
SET profiling = 1;
-- 查看最近 记录的语句
show profiles;
SHOW PROFILE [type [, type] ... ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]
type: {ALL | BLOCK IO | CONTEXT SWITCHES | CPU| IPC| MEMORY| PAGE FAULTS| SOURCE| SWAPS
}
n show profiles 中的id
碎片化
B树索引可能会有碎片化,索引无续的存储在磁盘上,导致查询效率的降低。表也可能存在碎片化,翻看聚簇索引
可通过optimize table table_name 整理表,或则直接导出导入(代价略大)
该方式只对大部分引擎有用,如MyISAM,innodb,BDB
当optimize命令无效时,可以用 alter table table_name engine=engine_name;将表的引擎改为当前引擎。
对于开启expand_fast_index_create参数的,这种方式可以同时消除表和索引的碎片化
单独索引的整理可以通过删除重建的方式。
锁
-- 表上锁
lock table table_name read/write;
-- 查看装填
show open tables;
-- 释放锁
unlock tables;
共享锁,其他事务可以加共享锁,但不能更改。
排他锁,其他事务不能加任何锁。
update,insert,delete 会自动上排他锁,单独的select是不会加锁的。
锁与索引有关,当不使用索引时(where中没有用到索引),将会锁表
-- 客户端1
-- 关闭自动提交
set autocommit=0;
begin;
-- 查询 并加上排他锁
select * from person where age >5 and age<>1 for update
-- 客户端2
-- 如果没有索引,这个将进行不下去,因为是表锁;如果有索引这里能执行下去,因为是行锁
update person set age=10 where age =15