索引使用数据结构为数据建立了目录，可以根据索引快速的定位到数据在硬盘上存放的位置
索引存放的位置：c:/programdata/mysql
InnoDB存储的表，将索引和数据存放在同一个文件内 。.idb
MyISAM存储的表，将索引和数据存分开两个文件存储。索引：.MYI *.MYD
索引的分类：
主键索引：主键自带索引效果，通过主键来查询表内的数据是非常好的
普通索引：为普通列创建索引
格式：create index 索引名称 on 表名（列名）
例：create index idx_name on employees(name)
唯一索引：列中的数据是唯一，性能比普通索引好
格式：create unique index 索引名称 on 表名（列名）
例：create unique index idx_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。
索引使用的数据结构
树的性能比线性表的好
平衡二叉树为了维护树的平衡，在一旦不满足平衡的情况就要进行自旋，但是自旋会造成一定的系统开销。
红黑树已经是在查询性能上得到了优化，但索引依然没有使用红黑树作为数据结构来存储数据，因为红黑树在每一层上存放的数据内容是有限的，导致数据量一大，树的深度就变得非常大，于是查询性能非常差。
使用B+树的原因
- 非叶子结点冗余了叶子结点中的键。
- 叶子结点是从小到大、从左到右排列的
- 叶子结点之间提供了指针，提高了区间访问的性能
- 只有叶子结点存放数据，非叶子结点是不存放数据的，只存放键
哈希表，性能是最快的，但是不支持范围查找
三、InnoDB和MyISAM的区别
InnoDB为聚集存储
索引和文件存放在一个文件夹内，通过找到索引后就能直接在叶子结点上获取完整的数据，可实现行锁和表锁
MyISAM为非聚集存储
索引和数据分开存储，查找到索引后还要去另一个文件中找数据，性能较慢
MyISAM天然支持表锁，全文索引
1.事务安全（MyISAM不支持事务，INNODB支持事务）
2.外键 MyISAM 不支持外键， INNODB支持外键.
3.锁机制（MyISAM时表锁，innodb是行锁）
4.查询和添加速度（MyISAM批量插入速度快）
5.支持全文索引（MyISAM支持全文索引，INNODB不支持全文索引）
6.MyISAM内存空间使用率比InnoDB低
四、索引常见的面试题
为什么非主键索引的叶子节点存放的是数据是主键的值
如果不同是索引不存放主键，而存放完整数据，那么就会造成：
数据冗余，数据修改麻烦
为什么InnoDB一定要创建主键
innodb存储引擎他是如果你没有创建索引,他会自动帮你找一列数据,这列数据没有重复的,把这个当成索引,然后在这个里面进行存储.
但是万一这个表找不到这个字段,他会自动帮你在底层创建一个隐藏的主键,这个就是int类型,通过这个隐藏键将这个数据按照B+树方式进行存储
所以在建表时候自己能把主键建了就建了,别老是让MySQL底层帮你创建,他这个innodb就是这么设计的
所以innodb存储引擎的表自己创建一个主键,这样MySQL也能够节省损耗
为什么使用主键时推荐使用整型的自增主键
主键-主键索引树-树里的叶子结点和非叶子节点都会存放主键的值，而且这是一个B+树，数据存放是有大小顺序的。
整型：大小顺序好比较
字符串：字符串的自然顺序的比较是要进行一次转码才能进行比较的。
为什么要自增：
如果使用不规律的数作为主键，那么主键索引树会使用更多的自旋次数来保证叶子结点中的数据是从小到大，从左到右的排列，性能较差。
五、联合索引和最左前缀法则
1.联合索引的特点
在使用一个索引来实现多个表中字段的索引效果。
1)简单概括
聚集索引：就是以主键创建的索引。
非聚集索引：就是以非主键创建的索引(也叫做二级索引)。
2)详细概括
聚集索引
聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，因为只要找到第一个索引值记录，其余的连续性的记录在物理表中也会连续存放，一起就可以查询到。
缺点：新增比较慢，因为为了保证表中记录的物理顺序和索引顺序一致，在记录插入的时候，会对数据页重新排序。
非聚集索引
索引的逻辑顺序与磁盘上行的物理存储顺序不同，非聚集索引在叶子节点存储的是主键和索引列，当我们使用非聚集索引查询数据时，需要拿到叶子上的主键再去表中查到想要查找的数据。这个过程就是我们所说的回表。
3)聚集索引和非聚集索引的区别
聚集索引在叶子节点存储的是表中的数据。
非聚集索引在叶子节点存储的是主键和索引列。
3.联合索引是如何存储的
https://zhuanlan.zhihu.com/p/109623980
现根据第一列排序，再根据第二列排序，依次向后，最后加上主键。
4.最左前缀法则
最左前缀法则是表示一条sql语句在联合索引中有没有走索引（命中索引/不会全表扫描）
六、SQL优化
1、Explain执行计划——SQL优化神器
通过在SQL语句前面加上explain关键字，执行后并不会真正的执行sql语句本身，而是通过explain工具来分析当前这条SQL语句的性能细节：比如是什么样的查询类型、可能用到的索引及实际用到的索引，和一些额外的信息。
2、MySQL的内部优化器
在SQL查询开始之前，MySQL内部优化器会进行一次自我优化，让这一次的查询性能尽可能的好。
explain select * from tb_book where id=1;
show warnings;
3、select_type列
关闭 MySQL 对衍生表的合并优化：
set session optimizer_switch=‘derived_merge=off’;
- derived：
第一条执行的sql是from后面的子查询，该子查询只要在from后面，就会生成一张衍生表，因此他的查询类型：derived - subquery：
在select之后 from之前的子查询 - primary：
最外部的select - simple：
不包含子查询的简单的查询 - union：
使用union进行的联合查询的类型
4、table列
正在查询哪张表
5、type列
ype列可以直观的判断出当前的sql语句的性能。type里的取值和性能的优劣顺序如下：
null > system > const > eq_ref > ref > range > index > all
对于SQL优化来说，要尽量保证type列的值是属于range及以上级别。
- null
性能最好的，一般在使用了聚合函数操作索引列，结果直接从索引树获取即可，因此是性能最好。 - system
很少见。直接和一条记录进行匹配。 - const
使用主键索引或唯一索引和常量进行比较，这种性能非常好 - eq_ref
在进行多表连接查询时。驱动表（外表）使用被驱动（内表）的主键（唯一）进行关联，每次加载外表的一行，使用主键找到内表的值,外表为全表扫描，内表为eq_ref。如果查询条件是使用了主键或唯一索引进行比较，那么当前查询类型是eq_ref。
无谓语时
1.当使用left join时，左表是驱动表，右表是被驱动表
2.当使用right join时，右表时驱动表，左表是驱动表
3.当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表
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
- 简单查询：EXPLAIN select * from tb_book where name=‘book1’
- range:
使用索引进行范围查找
explain select * from tb_book where id>1
- index
查询没有进行条件判断。但是所有的数据都可以直接从索引树上获取(book表中的所有列都有索引)
explain select * from tb_book
- all
没有走索引，进行了全表扫描
explain select * from tb_author
id列
在多个select中，id越大越先执行，如果id相同。上面的先执行。
possible keys列
这一次的查询可能会用到的索引。也就是说mysql内部优化器会进行判断，如果这一次查询走索引的性能比全表扫描的性能要查，那么内部优化器就让此次查询进行全表扫描——这样的判断依据我们可以通过trace工具来查看
EXPLAIN select * from employees where name like 'custome%'
9.key列
实际该sql语句使用的索引
10.rows列
该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列
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zA2n7iNv-1638508142548)(img/image-20210518104705482.png)]
计算规则：
- 字符串
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列
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
表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。
EXPLAIN select * from tb_author where name > 'a'
- using index condition
代表查询条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。
使用了使用索引作为查询条件，存储引擎使用限制条件搜索记录，然后返回记录给server，server在再根据检查条件，判断此条记录是否符合。Using index condition的提示，这表明会将有关二级索引的查询条件放在存储引擎层判断一下，这个特性就是所谓的索引条件下推（Index Condition Pushdown，简称ICP）
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 (`employees`.`name` > 'a') order by `employees`.`position`"
}
] /* 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 */
},
{
"rows_estimation": [
{
"table": "`employees`",
"range_analysis": {
"table_scan": {
"rows": 5598397,
"cost": 576657
} /* table_scan */,
"potential_range_indexes": [ -- 可能使用到的索引
{
"index": "PRIMARY", -- 主键索引
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_name_age_position", -- 联合索引
"usable": true,
"key_parts": [
"name",
"age",
"position",
"id"
] /* key_parts */
},
{
"index": "idx_hire_time",
"usable": false,
"cause": "not_applicable"
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "idx_name_age_position",
"usable": false,
"cause": "query_references_nonkey_column"
}
] /* potential_skip_scan_indexes */
} /* skip_scan_range */,
"analyzing_range_alternatives": { -- 分析各个索引使用的成本
"range_scan_alternatives": [
{
"index": "idx_name_age_position",
"ranges": [
"a < name"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": true,
"index_only": false, -- 是否使用了覆盖索引
"rows": 2799198, -- 要扫描的行数
"cost": 2.08e6, -- 要花费的时间
"chosen": false, -- 是否选择使用这个索引
"cause": "cost" -- 不选择的原因：开销比较大
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`employees`",
"best_access_path": { -- 最优访问路径
"considered_access_paths": [ -- 最后选择的访问路径
{
"rows_to_scan": 5598397, -- 全表扫描的行数
"access_type": "scan", -- 全表扫描
"resulting_rows": 5.6e6, -- 结果的行数
"cost": 576655, -- 花费的时间
"chosen": true, -- 选择这种方式
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 5.6e6,
"cost_for_plan": 576655,
"sort_cost": 5.6e6,
"new_cost_for_plan": 6.18e6,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`employees`.`name` > 'a')",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`employees`",
"attached": "(`employees`.`name` > 'a')"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"optimizing_distinct_group_by_order_by": {
"simplifying_order_by": {
"original_clause": "`employees`.`position`",
"items": [
{
"item": "`employees`.`position`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`employees`.`position`"
} /* simplifying_order_by */
} /* optimizing_distinct_group_by_order_by */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"steps": [
] /* steps */,
"index_order_summary": {
"table": "`employees`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "unknown",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"finalizing_table_conditions": [
{
"table": "`employees`",
"original_table_condition": "(`employees`.`name` > 'a')",
"final_table_condition ": "(`employees`.`name` > 'a')"
}
] /* finalizing_table_conditions */
},
{
"refine_plan": [
{
"table": "`employees`"
}
] /* refine_plan */
},
{
"considering_tmp_tables": [
{
"adding_sort_to_table": "employees"
} /* filesort */
] /* considering_tmp_tables */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"sorting_table": "employees",
"filesort_information": [
{
"direction": "asc",
"expression": "`employees`.`position`"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false,
"cause": "not applicable (no LIMIT)"
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"memory_available": 262144,
"key_size": 40,
"row_size": 190,
"max_rows_per_buffer": 1379,
"num_rows_estimate": 5598397,
"num_rows_found": 5913852,
"num_initial_chunks_spilled_to_disk": 1954,
"peak_memory_used": 262144,
"sort_algorithm": "std::stable_sort",
"sort_mode": "<fixed_sort_key, packed_additional_fields>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
八、SQL优化实战
sql优化详解：https://blog.csdn.net/zzhongcy/article/details/106783473
1.order by优化
在排序应用场景中，很容易出现文件排序的问题，文件排序会对性能造成影响，因此需要优化
优化手段：左前缀顺序排序，覆盖索引
- 如果排序的字段创建了联合索引，那么尽量在业务不冲突的情况下，遵循最左前缀法则来写排序语句。
- 如果文件排序没办法避免，那么尽量想办法使用覆盖索引。all->index
2.group by优化
同orderby
3.文件排序的原理
在执行文件排序的时候，会把查询的数据的大小与系统变量：max_length_for_sort_data的大小进行比较（默认是1024字节）,如果比系统变量小，那么执行单路排序，反之则执行双路排序
单路排序
把查询到的所有的数据扔到sort_buffer内存缓冲区中，进行排序，然后结束
双路排序
把查询到的数据，取数据的排序字段和主键字段，在内存缓冲区中排序完成后，将主键字段做一次回表查询，获取完整数据。
举个例子，下面有一段sql：
select * from user where name = "自由的辣条" order by age
双路排序过程:
MySQL 4.1 之前使用的双路排序，通过两次扫描磁盘得到数据。读取主键id 和 order by 列并对其进行排序，扫描排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
从索引 name 找到第一个满足 name = ‘自由的辣条’ 的主键id
根据主键 id 取出整行，把排序字段 age 和主键 id 这两个字段放到 sort buffer(排序缓存) 中
从索引 name 取下一个满足 name = ‘自由的辣条’ 记录的主键 id
重复 3、4 直到不满足 name = ‘自由的辣条’
对 sort_buffer 中的字段 age 和主键 id 按照字段 age进行排序
遍历排序好的 id 和字段 age ，按照 id 的值回到原表中取出 所有字段的值返回给客户端
单路排序过程：
从索引name找到第一个满足 name = ‘自由的辣条’ 条件的主键 id
根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer(排序缓存)中
从索引name找到下一个满足 name = ‘自由的辣条’ 条件的主键 id
重复步骤 2、3 直到不满足 name = ‘自由的辣条’
对 sort_buffer 中的数据按照字段 age 进行排序
返回结果给客户端
4.分页优化
对于这样的优化查询，mysql会把全部的10010数据拿到，并舍弃掉前面的10000条
-- 一次行获取10010，再舍弃掉前10000条
Explain select * from employees limit 1000000,10
如果在主键连续的情况下，可以使用主键来做条件，但是这种情况是很少见的
Explain select * from employees where id>100000 limit 10
对于主键不连续情况下的例子：
Explain select * from employees order by name limit 1000000,10
-- 通过先进行覆盖索引的查找，然后在使用join做连接查询获取所有数据。这样比全表扫描要快
explain select * from employees a inner join (select id from employees order by name limit 1000000,10) b on a.id = b.id;
join优化
在join中会涉及到大表（数据量大）和小表（数据量小）的概念。MySQL内部优化器会根据关联字段是否创建了索引来使用不同的算法：
mysql会自动识别大表小表，与写的顺序无关
-
Nlj(嵌套循环算法)：如果关联字段使用了索引，mysql会对小表做全表扫描，每次提取一条记录，用小表的数据去和大表的数据去做索引字段的关联查询（type：ref）
-
bnlj（块嵌套循环算法）：如果关联字段没有使用索引，mysql会提供一个join buffer缓冲区，先把小表放到缓冲区中，然后全表扫描大表，把大表的数据和缓冲区中的小表数据逐行在内存中进行匹配。
结论：使用join查询时，一定要建立关联字段的索引，且两张表的关联字段在设计之初就要做到字段类型、长度是一致的，否则索引失效。
6.in和exists优化
在sql中如果A表是大表，B表是小表，那么使用in会更加合适。反之应该使用exists。
in后接小表，exists后接大表
- in: B的数据量<A的数据量
select * from A where id in (select id from B)
# 相当于：
for(select id from B){ //B的数据量少，所以循环次数少。
select * from A where A.id = B.id
}
- exists: B的数据量>A的数据量 (10: id 1. 2. 3. 4)
select * from A where exists (select 1 from B where B.id = A.id) true / false
等价于
for(select * from A){
select * from B where B.id = A.id
}
7.count优化
count直接使用的主键，对于count的优化应该是架构层面的优化，因为count的统计是在一个产品会经常出现，而且每个用户访问，所以对于访问频率过高的数据建议维护在缓存中。
九、锁的定义和分类
1.锁的定义
锁是用来解决多个任务（线程、进程）在并发访问同一共享资源时带来的数据安全问题。虽然使用锁解决了数据安全问题，但是会带来性能的影响，频繁使用锁的程序的性能是必然很差的。
对于数据管理软件MySQL来说，必然会到任务的并发访问。那么MySQL是怎么样在数据安全和性能上做权衡的呢？——MVCC设计思想。
2.锁的分类
1）从性能上划分：乐观锁和悲观锁
- 悲观锁：悲观的认为当前的并发是非常严重的，所以在任何时候操作都是互斥。保证了线程的安全，但牺牲了并发性。——总有刁民要害朕。
- 乐观锁：乐观的认为当前的并发并不严重，因此对于读的情况，大家都可以进行，但是对于写的情况，再进行上锁。以CAS自旋锁，在某种情况下性能是ok的，但是频繁自旋会消耗很大的资源。——天网恢恢疏而不漏
2）从数据的操作细粒度上划分：表锁和行锁
- 表锁：对整张表上锁
- 行锁：对表中的某一行上锁。
3）从数据库的操作类型上划分：读锁和写锁
这两种锁都是属于悲观锁
- 读锁（共享锁）：对于同一行数据进行”读“来说，是可以同时进行但是写不行。
- 写锁（拍他锁）：在上了写锁之后，及释放写锁之前，在整个过程中是不能进行任何的其他并发操作（其他任务的读和写是都不能进行的）。
对整张表进行上锁。MyISAM存储引擎是天然支持表锁的，也就是说在MyISAM的存储引擎的表中如果出现并发的情况，将会出现表锁的效果。MyISAM不支持事务。InnoDB支持事务
在InnoDB中上一下表锁:
# 对一张表上读锁/写锁格式：
lock table 表名 read/write;
# 例子
lock table tb_book read;
# 查看当前会话对所有表的上锁情况
show open tables;
# 释放当前会话的所有锁
unlock tables;
读锁： 其他任务可以进行读，但是不能进行写
写锁：其他任务不能进行读和写。
4.行锁
MyISAM只支持表锁，但不支持行锁，InnoDB可以支持行锁。
在并发事务里，每个事务的增删改的操作相当于是上了行锁。
上行锁的方式：
- update tb_book set name=‘qfjava2101’ where id=8; 对id是8的这行数据上了行锁。
- select * from tb_book where id=5 for update; 对id是5的这行数据上了行锁。
十、MVCC设计思想
MySQL为了权衡数据安全和性能，使用了MVCC多版本并发控制的设计。
1.事务的特性
- 原子性：一个事务是一个最小的操作单位（原子），多条sql语句在一个事务中要么同时成功，要么同时失败。
- 一致性：事务提交之前和回滚之后的数据是一致的。
- 持久性：事务一旦提交，对数据的影响是持久的。
- 隔离性：多个事务在并发访问下，提供了一套隔离机制，不同的隔离级别会有不同的并发效果。
2.事务的隔离级别
read uncommit:脏读：一个事务读取到另一个事务还未提交的数据 不可重复读：一个事务两个读取同一张表，读取到的数据不一致 幻读：事务可重复读取表，但是修改表的时候却发现数据已存在
read commit:不可重复度，幻读
repeatable read ：幻读
解决方案：
通过上行锁来解决虚读问题：
Serializable:串行化的隔离界别直接不允许事务的并发发生，不存在任何的并发性。相当于锁表，性能非常差，一般都不考虑
3.MVCC思想解读
MySQL在读和写的操作中，对读的性能做了并发性的保障，让所有的读都是快照读，对于写的时候，进行版本控制，如果真实数据的版本比快照版本要新，那么写之前就要进行版本（快照）更新，这样就可以既能够提高读的并发性，又能够保证写的数据安全。
十一、死锁和间隙锁
1.死锁
所谓的死锁，就是开启的锁没有办法关闭，导致资源的访问因为无法获得锁而处于阻塞状态。
演示：事务A和事物B相互持有对方需要的锁而不释放，造成死锁的情况。
2.间隙锁
行锁只能对某一行上锁，如果相对某一个范围上锁，就可以使用间隙锁。间隙锁给的条件where id>13 and id<19，会对13 和19 所处的间隙进行上锁。
索引失效
1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
3、组合索引，不是使用第一列索引，索引失效。
4、数据类型出现隐式转化或。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
5、在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
6、对索引字段进行计算操作、字段上使用函数。（索引为 emp(ename,empno,sal)）
7、当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效
5410
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
