逻辑架构
- 连接层
- 业务层
- 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
7648
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
