MySQL原理及优化实战-笔记
⼀、索引的概述
1.为什么要使⽤索引
在海量数据中进⾏查询某条记录的场景是经常发⽣的，那么如何提升查询性能，就跟要查询的数据字段是否有索引有关系。如果字段加了索引，那么查询的性能就⾮常快！——就是为了快！
索引为什么快？
索引到底是什么？
在使⽤索引的是要注意什么样的事项？
2.索引是什么
查字典的⽅式？“数”shu--通过⽬录来查，能够快速的定位到⽬标数据所在的页码。
没有使⽤索引的时候，数据的查询需要进⾏多次IO读写，这样的性能较差——全表扫描的过程。
为数据库的某个字段创建索引，相当是为这个字段的内容创建了⼀个⽬录。通过这个⽬录可以快速的实现数据的定位，也就是通过索引能够快速的到某条数据所在磁盘的位置。
现在的疑问？
索引存放位置
索引的分类及如何创建
索引使⽤了哪种数据结构：各种数据结构的查询性能进⾏分析
3.索引存放的位置
对于mac系统在/usr/local/mysql⽂件夹中，对于win系统c:/programdata/mysql(隐藏⽂件夹)
InnoDB存储引擎的表：将索引和数据存放在同⼀个⽂件⾥。（为什么？有什么优势？）*.ibd
MyISAM存储引擎的表：索引和数据分开两个⽂件来存储。索引：*.MYI ; 数据：MYD
4.索引的分类
主键索引：主键⾃带索引效果，也就意味着通过主键来查询表中的记录，性能是⾮常好的。
普通索引：为普通列创建的索引。
创建索引的命令：
# 格式
create index 索引名称 on 表名(列名)
# 例⼦
create index idx_name on employees(name)
唯⼀索引：就像是唯⼀列，列中的数据是唯⼀的。⽐普通索引的性能要好。
# 格式
create unique index 索引名称 on 表名(列名)
# 例⼦
create unique index idx_unique_name on employees(name)
联合索引（组合索引）：⼀次性为表中的多个字段⼀起创建索引，最左前缀法则（如何命中联合索引中的索引列）。注意：⼀个联合索引建议不要超过5个列
# 格式
create index 索引名称 on 表(列1，列2，列3)
# 例⼦
create index idx_name_age_position on employees(name,age,position)
全⽂索引
进⾏查询的时候，数据源可能来⾃于不同的字段或者不同的表。⽐如去百度中查询数据，千锋教育，来⾃于⽹页的标题或者⽹页的内容。MyISAM存储引擎⽀持全⽂索引。在实际⽣产环境中，并不会使⽤MySQL提供的MyISAM存储引擎的全⽂索引功能来是实现全⽂查。⽽是会使⽤第三⽅的搜索引擎中间件⽐如ElasticSearch（多）、Solr。
⼆、索引使⽤的数据结构
使⽤索引查数据性能很快，避免了全表扫描的多次磁盘IO读写。但是我们发现，使⽤索引实际上也需要在索引中查数据，⽽且数据量是⼀样的，那么凭什么索引就能快呢？这就跟索引使⽤了哪种数据结构⽀持快速查。
什么叫数据结构：存放数据的结构。⽐如：数组、链表、栈、堆、队列等等这些概念。
1.线性表：
线性的维护数据的顺序。
对于线性表来说，有两种数据结构来⽀撑：
查看mysql索引线性顺序表：相邻两个数据的逻辑关系和物理位置是相同的。
线性链式表：相邻两个数据的逻辑关系和物理存放位置没有关系。数据是有先后的逻辑关系，但是数据的物理存储位置并不连续。
单向链表：能够通过当前结点到下⼀个节点的位置，以此来维护链表的逻辑关系
结点结构：数据内容+下⼀个数据的指针
双向链表：能够通过当前结点到上⼀个或下⼀个节点的位置，双向都可。
结点结构：上⼀个数据的指针+数据内容+下⼀个数据的指针
顺序表和链式表的区别：
- 数组：进⾏数据的查询性能（可以通过数组的索引/下标）：时间复杂度（⽐较次数）/空间复杂度（算法需要使⽤多少个变量空间）
数组的查询性能⾮常好：时间复杂度是O(1)。
数组的增删性能是⾮常差的：
- 链表：查询的性能是⾮常差的：时间复杂度是O(n)。
增删性能是⾮常好的：
2.栈、队列、串、⼴义表
栈：先进后出，有顺序栈、链式栈
队列：先进先出，有顺序队列、链式队列
串：String 定长串、StringBuffer/Stringbuilder动态串
⼴义表：更加灵活的多维数组，可以在不同的元素中创建不同的维度的数组。
3.树
查树的查性能是明显⽐线性表的性能要好，那么接下来我们就要学习这么⼏种树：
1）多叉树
⾮⼆叉树
2）⼆叉树
⼀个结点最多只能有2个⼦结点，可以是0、1、2⼦结点。
3）⼆叉查树
⼆叉查树的查性能是ok的，查询性能跟树的⾼度有关，树的⾼度⼜根你插⼊数据的顺序有关系。特点：⼆叉树的根结点的数值是⽐所有左⼦树的结点的数值⼤，⽐右⼦树的⼏点的数值⼩。这样的规律同样满⾜于他的所有⼦树。
4）平衡⼆叉树（理想概念的树）
我们知道⼆叉查树不能⾮常智能的维护树的⾼度，因此⼆叉查树在某些情况下查询性能是不ok的，此时平衡⼆叉树就出现了。
特点：平衡⼆叉树中的树及其所有⼦树都应满⾜：左⼦树和右⼦树的深度差不能超过1
如果平衡⼆叉树不满⾜这个特点，那么平衡⼆叉树要进⾏⾃⼰旋转，如何⾃⼰旋转：
左旋、右旋、双向（先左后右、先右后左）
5）红⿊树（平衡⼆叉树的⼀种体现）
平衡⼆叉树为了维护树的平衡，在⼀旦不满⾜平衡的情况就要进⾏⾃旋，但是⾃旋会造成⼀定的系统开销。因此红⿊树在⾃旋造成的系统开销和减少查询次数之间做了权衡。因此红⿊树有时候并不是⼀颗平衡⼆叉树。
红⿊树已经是在查询性能上得到了优化，但索引依然没有使⽤红⿊树作为数据结构来存储数据，因为红⿊树在每⼀层上存放的数据内容是有限的，导致数据量⼀⼤，树的深度就变得⾮常⼤，于是查询性能⾮常差。因此索引没有使⽤红⿊树。
6）B树
B树允许⼀个结点存放多个数据。这样可以使更⼩的树的深度来存放更多的数据。但是，B树的⼀个结点中到底能存放多少个数据，决定了树的深度。
通过数值计算，B树的⼀个结点最多只能存放15个数据，因此B树依然不能满⾜海量数据的查询性能优化。
7）B+树
B+树的特点：
⾮叶⼦结点冗余了叶⼦结点中的键。
叶⼦结点是从⼩到⼤、从左到右排列的
叶⼦结点之间提供了指针，提⾼了区间访问的性能
只有叶⼦结点存放数据，⾮叶⼦结点是不存放数据的，只存放键
8）哈希表
使⽤哈希表来存取数据的性能是最快的，O(1)，但是不⽀持范围查（区间访问）
三、InnoDB和MyISAM的区别
InnoDB和MyISAM都是数据库表的存储引擎。那么在互联⽹公司，或者追求查询性能的场景下，都会使⽤InnoDB作为表的存储引擎。
为什么？
1.InnoDB引擎——聚集索引
把索引和数据存放在⼀个⽂件中，通过到索引后就能直接在索引树上的叶⼦结点中获得完整的数据。
可以实现⾏锁/表锁
2.MyISAM存储引擎——⾮聚集索引
把索引和数据存放在两个⽂件中，查到索引后还要去另⼀个⽂件中数据，性能会慢⼀些。
除此之外，MyISAM天然⽀持表锁，⽽且⽀持全⽂索引。
四、索引常见的⾯试题
1.问题⼀：为什么⾮主键索引的叶⼦节点存放的数据是主键值
如果普通索引中不存放主键，⽽存放完整数据，那么就会造成：
数据冗余，虽然提升了查询性能，但是需要更多的空间来存放冗余的数据
维护⿇烦：⼀个地⽅修改数据，需要在多棵索引树上修改。
2.问题⼆：为什么InnoDB表必须创建主键
创建InnoDB表不使⽤主键能创建成功吗？如果能创建功能，能不能为这张表的普通列创建索引？
如果没有主键，MySQL优化器会给⼀个虚拟的主键，于是普通索引会使⽤这个虚拟主键——也会造成性能开销。为了性能考虑，和设计初衷，那么创建表的时候就应该创建主
键。
3.问题三：为什么使⽤主键时推荐使⽤整型的⾃增主键
1）为什么要使⽤整型：
主键-主键索引树-树⾥的叶⼦结点和⾮叶⼦结点的键存放的是主键的值，⽽且这颗树是⼀个⼆叉查树。数据的存放是有⼤⼩顺序的。
整型：⼤⼩顺序是很好⽐较的
字符串：字符串的⾃然顺序的⽐较是要进⾏⼀次编码成为数值后再进⾏⽐较的。（字符串的⾃然顺序，A Z）
uuid随机字符串
2）为什么要⾃增：
如果不⽤⾃增：（10 1 6。 200。 18。29）使⽤不规律的整数来作为主键，那么主键索引树会使⽤更多的⾃旋次数来保证树索引树的叶⼦节点中的数据是从⼩到⼤-从左到右排列，因此性能必然⽐使⽤了⾃增主键的性能要差！
五、联合索引和最左前缀法则
1.联合索引的特点
在使⽤⼀个索引来实现多个表中字段的索引效果。
2.联合索引是如何存储的
3.最左前缀法则
最左前缀法则是表⽰⼀条sql语句在联合索引中有没有⾛索引（命中索引/不会全表扫描）
# 创建联合索引
create index idx_a_b_c on table1(a,b,c);
# sql语句有没有命中索引
select * from table1 where a = 10; √
select * from table1 where a = 10 and b=20; √
select * from table1 where a = 10 and b=20 and c=30; √
select * from table1 where b = 10; ×
select * from table1 where b = 10 and c=30; ×
select * from table1 where a = 10 and c=30; √（但是只⾛了a的索引，因为c不满⾜最左前缀法则）
select * from table1 where c = 30; ×
select * from table1 where a = 10 and c = 30 and b = 20;  √ (abc全⾛)=》mysql有⼀个内部优化器会做⼀次内部优化。
六、SQL优化
SQL优化的⽬的是为了SQL语句能够具备优秀的查询性能，实现这样的⽬的有很多的途径：
⼯程优化如何实现：数据库标准、表的结构标准、字段的标准、创建索引
SQL语句的优化：当前SQL语句有没有命中索引。
1.⼯程优化如何实现
参考《58到家-MySQL军规升级版》
2.Explain执⾏计划——SQL优化神器
得知道当前系统⾥有哪些SQL是慢SQL，查询性能超过1s的sql，然后再通过Explain⼯具可以对当前SQL语句的性能进⾏判断——为什么慢，怎么解决。
要想知道哪些SQL是慢SQL，有两种⽅式，⼀种是开启本地MySQL的慢查询⽇志；另⼀种是阿⾥云提供的RDS（第三⽅部署的MySQL服务器），提供了查询慢SQL的功能。explain SELECT * from employees where name like "customer100%"
通过在SQL语句前⾯加上explain关键字，执⾏后并不会真正的执⾏sql语句本⾝，⽽是通过explain⼯具来分析当前这条SQL语句的性能细节：⽐如是什么样的查询类型、可能⽤到的索引及实际⽤到的索引，和⼀些额外的信息。
3.MySQL的内部优化器
在SQL查询开始之前，MySQL内部优化器会进⾏⼀次⾃我优化，让这⼀次的查询性能尽可能的好。
当前执⾏的SQL
explain select * from tb_book where id=1;
show warnings;
内部优化器优化后的效果：
/* select#1 */ select '1' AS `id`,'千锋Java厉害' AS `name` from `db_mysql_pro`.`tb_book` where true
4.select_type列
关闭 MySQL 对衍⽣表的合并优化：
set session optimizer_switch='derived_merge=off';
执⾏了这样的计划：
EXPLAIN select (select 1 from tb_author where id=1) from (select * from tb_book where id=1) der;
derived：
第⼀条执⾏的sql是from后⾯的⼦查询，该⼦查询只要在from后⾯，就会⽣成⼀张衍⽣表，因此他的查询类型：derived
subquery：
在select之后 from之前的⼦查询
primary：
最外部的select
simple：
不包含⼦查询的简单的查询
union：
使⽤union进⾏的联合查询的类型
5.table列
当前查询正在查哪张表
type列可以直观的判断出当前的sql语句的性能。type⾥的取值和性能的优劣顺序如下：
null > system > const > eq_ref > ref > range > index > all
对于SQL优化来说，要尽量保证type列的值是属于range及以上级别。
null
性能最好的，⼀般在使⽤了聚合函数操作索引列，结果直接从索引树获取即可，因此是性能最好。
system
很少见。直接和⼀条记录进⾏匹配。
const
使⽤主键索引或唯⼀索引和常量进⾏⽐较，这种性能⾮常好
eq_ref
在进⾏多表连接查询时。如果查询条件是使⽤了主键进⾏⽐较，那么当前查询类型是eq_ref
EXPLAIN select * from tb_book_author left JOIN tb_book on tb_book_author.book_id = tb_book.id
ref
简单查询：EXPLAIN select * from tb_book where name='book1'
如果查询条件是普通列索引，那么类型ref
复杂查询：EXPLAIN select book_id from tb_book left join tb_book_author on tb_book.id = tb_book_author.book_id
如果查询条件是普通列索引，那么类型ref
range:
使⽤索引进⾏范围查
explain select * from tb_book where id>1
index
查询没有进⾏条件判断。但是所有的数据都可以直接从索引树上获取(book表中的所有列都有索引)
explain select * from tb_book
all
没有⾛索引，进⾏了全表扫描
explain select * from tb_author
7.id列
在多个select中，id越⼤越先执⾏，如果id相同。上⾯的先执⾏。
8.possible keys列
这⼀次的查询可能会⽤到的索引。也就是说mysql内部优化器会进⾏判断，如果这⼀次查询⾛索引的性能⽐全表扫描的性能要查，那么内部优化器就让此次查询进⾏全表扫描——这样的判断依据我们可以通过trace⼯具来查看
EXPLAIN select * from employees where name like 'custome%'
这条sql⾛索引查询的⾏数是500多万，那么总的数据⾏数也就500多万，因此直接进⾏全表扫描性能更快
9.key列
实际该sql语句使⽤的索引
该sql语句可能要查询的数据条数
11.key_len列
键的长度，通过这⼀列可以让我们知道当前命中了联合索引中的哪⼏列。
EXPLAIN select * from employees where name = 'customer10011' # 74
EXPLAIN select * from employees where name = 'customer10011' and age=30 # 74 4 = 78
EXPLAIN select * from employees where name = 'customer10011' and age=30 and position='dev' # 74 4 62 = 140
EXPLAIN select * from employees where name = 'customer10011' and position='dev' # 74
name长度是74，也就是当看到key-len是74，表⽰使⽤了联合索引中的name列
计算规则：
- 字符串
1. char(n): n字节长度
2. varchar(n): 2字节存储字符串长度，如果是utf-8，则长度3n + 2
- 数值类型
1. tinyint: 1字节
2. smallint: 2字节
3. int: 4字节
4. bigint: 8字节
- 时间类型
1. date: 3字节
2. timestamp: 4字节
3. datetime: 8字节
如果字段允许为NULL，需要1字节记录是否为NULL
索引最⼤长度是768字节，当字符串过长时, mysql会做⼀个类似左前缀索引的处理，将前半部分的字符提取出来做索引
extra列提供了额外的信息，是能够帮助我们判断当前sql的是否使⽤了覆盖索引、⽂件排序、使⽤了索引进⾏查询条件等等的信息。
Using index：使⽤了覆盖索引
所谓的覆盖索引，指的是当前查询的所有数据字段都是索引列，这就意味着可以直接从索引列中获取数据，⽽不需要进⾏查表。
使⽤覆盖索引进⾏性能优化这种⼿段是之后sql优化经常要⽤到的。
EXPLAIN select book_id,author_id from tb_book_author where book_id = 1 -- 覆盖索引
EXPLAIN select * from tb_book_author where book_id = 1 -- 没有使⽤覆盖索引
using where
使⽤了普通索引列做查询条件
EXPLAIN select * from tb_author where name > 'a'
using index condition
查询结果没有使⽤覆盖索引，建议可以使⽤覆盖索引来优化
EXPLAIN select * from tb_book_author where book_id > 1
Using temporary
在⾮索引列上进⾏去重操作就需要使⽤⼀张临时表来实现，性能是⾮常差的。当前name列没有索引
EXPLAIN select DISTINCT name from tb_author
Using filesort
使⽤⽂件排序：会使⽤磁盘+内存的⽅式进⾏⽂件排序，会涉及到两个概念：单路排序、双路排序
EXPLAIN select * from tb_author order by name
Select tables optimized away
直接在索引列上进⾏聚合函数的操作，没有进⾏任何的表的操作
EXPLAIN select min(id) from tb_book
七、Trace⼯具
在执⾏计划中我们发现有的sql会⾛索引，有的sql即使明确使⽤了索引也不会⾛索引。这是因为mysql的内部优化器任务⾛索引的性能⽐不⾛索引全表扫描的性能要差，因此
mysql内部优化器选择了使⽤全表扫描。依据来⾃于trace⼯具的结论。
set session optimizer_trace="enabled=on", end_markers_in_json=on; -- 开启trace
select * from employees where name > 'a' order by position; -- 执⾏查询
SELECT * FROM information_schema.OPTIMIZER_TRACE; -- 获得trace的分析结果
{
"steps": [
{
"join_preparation": { -- 阶段1：进⼊到准备阶段
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employee          }
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { -- 阶段2: 进⼊到优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { -- 条件处理
"condition": "WHERE",
"original_condition": "(`employees`.`name` > 'a')",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`employees`.`name` > 'a')"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`employees`.`name` > 'a')"
}
]
/* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [ -- 表依赖详情
{
"table": "`employees`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。