数据库杂记篇(MySql,个人观点，不一定正确，先写下自己研究的,接下来再不断补充)

仅代表个人观点，并不一定正确。

支持技术分享，转载或复制，请指出文章来源 此博客作者为Jack__0023

1、背景

最近看的书老被打断，趁有点想法，怕遗忘，先记录下来(看看过阵子尝试用记忆宫殿的方式来记东西，这脑子呀)

在数据库查询操作中，影响的重要因素可以认为是 查询的时候的计算时间 和 查询开始之前的等待时间 ，索引影响的是前者，锁机制影响的是后者。

2、MySql的 查询 优化方式----索引和锁机制（先定个小目标，我目前知道的就两种，这里不说SQL，索引缓存先不说，也暂时不说事务,这些坑后面研究透后再补上去）

2-1 索引(定义、使用的原因和注意的事情、种类、中间会包含各种参考举例)

在影响数据库查询的诸多因素之中，索引应该是一个无法忽视的存在，索引的使用不当，那可就真帮倒忙了，那么什么是索引？

2-1-1 索引定义

这句是废话可忽略：(如果想要快速记下这段话，可以在脑子里面一边看一边联想书的实体影像，翻书声音等)

简单可理解为一本书的 目录，其实就是 索引，不过这里 数据库的索引，可以准确定位到 某一行 数据。如下图片(以下均简称 目录图 )，就可以理解为索引，你可以抽象为，它每一节只对应一行内容。

对于索引应该大概有个印象了，那么我们为什么要使用索引？

2-1-2 使用索引 的原因和注意的事情

使用索引的原因(好想打成元婴，修仙法力无边)

一般 如果不使用索引扫描 的话，那么我们使用 SELECT 应该都是全表扫描，简单来说，就是 整本书从头看到尾，一个标点符号都不能跳过，对于只想看感兴趣的内容的你，可能是很闹心的
所以 一般来说(?)，使用索引查询，会有更好的查询效果（相当于一对一查询，直接定位到你要看的内容章节，例如直接跳转到上图的第五章那一行，节在 组合索引 会提到）。

要注意的事情，查询的内容的多少，同时回答上面的 一般来说

为什么说一般来说？来举个哈密瓜
例如你要查询的内容，占据整个数据表的 很大比例 (例如80-90%的书籍都是你想读的，我有没有get到你的吐槽点)，
那么可能，索引扫描 效果 可能没有 全表扫描 效果好，
原因是：如果这种情况，你还索引扫描，就相当于，看完一行又返回去再看目录，接着再去看对应的内容，你看目录也要时间对不对？具体计算你可以看网上那些大牛的计算方式

2-1-3 索引的种类（这里没有很多研究，不敢多逼逼，可以网上搜索，有很多，后面我实践之后再补上来）

2-1-3-1 普通索引

2-1-3-2 唯一索引

2-1-3-3 主键索引

2-1-3-4 组合索引

2-1-3-4-1 什么是组合索引？

例如上面的 目录图 中，第五章第二节的纪传体，这是组合起来的两个查询条件(第五章是一个，第二节是一个，才能定位到纪传体)
可以理解SQL为： … where a = “第五章” and b = “第二节”

2-1-3-4-2 怎么定义组合索引？

例如： 只是给你们看建表时候形式是怎么样的
CREATE TABLE ‘test’ {
‘id’ int(11) NOT NULL auto_increment,
‘a’ int(11) NOT NULL default ‘0’,
‘b’ int(11) NOT NULL default ‘0’,
PRIMARY KEY (‘id’),
KEY ‘normal_key’ (‘a’,‘b’) //这里就是组合索引的形式
} ENGINE=InnoDB

2-1-3-4-3 注意事项

2-1-3-4-3-1 如果你这里没有把 a和b 标注为组合索引 ，那么sql查询的时候，例如a，b字段都有索引，那么会使用a的索引，然后再在查询出来的临时表中，全表扫描符合内容为 “第二节” 的行，原因是 数据库一个数据表，只能使用一个索引**。

2-1-3-4-3-2 注意 最左前缀 这个东西，如果顺序不对，那么组合索引是不会生效的

2-1-3-4-3-3 注意 组合索引的副作用

在某些情况下，组合索引会对一些查询产生误导，这时候你应该考虑是否阻止组合索引或者设计更好的索引，例如你有abc三个字段组成组合索引
如果你只通过b去查询数据库前十条数据， SELECT * FROM test WHERE b = ‘777’ limit 10;
根据最左前缀，应该不会使用组合索引，
但是这时候，如果，数据库是按照a去排序的，那么优化器，认为使用组合索引会更好，这就很尴尬了，本来全表扫描，十条就够了，这可能导致，要查询的数据，比全表扫描还多(因为全表扫描只要前十条)

这时候你应该尝试优化sql，例如改成 SELECT * FROM test WHERE b = ‘777’ order by id limit 10;(用主键id去排序，不以a排序)，或者直接看是否应该修改索引

2-1-3-5 全文索引

小结

具体选择用不用索引，用哪种，可能要你根据业务场景来分析了，这种事情，没有什么很好的避开方式，你只能通过业务中要使用的sql，然后监控sql的执行效果来分析了，
分析时候请记住
a、最左前缀
b、模糊查询请自重，索引完全匹配不了，所以应该会出现全文扫描
(因为我们这里说的是查询，所以可以使用 explain 来进行分析,expalin的仔细介绍可以看下网上资料，很多)。如果要日志的话，也可以设置慢查询日志，然后去查看日志，在my.cnf文件中可以设置。

2-2、锁机制(这里是指MyISAM 表锁 和 InnoDb 行锁，单纯说锁)

锁这块是影响查询的另一个重要因素，当有多用户并发访问操作数据库中某一资源，为了保证原子性，那么应该通过锁来协调这些操作。

那么很明显，如何减少锁定的等待时间，就是这个节要说的了（这里需要使用 show processlist 命令来进行验证）

2-2-1 MyISAM 是一个表级别的锁，它会锁住整张表，然后执行完一个操作之后，再释放锁给别人去操作，一个个排队轮流来执行（你可以执行5条update，然后 show processlist去查看

例如可以使用 ab 在后端中执行5条 UPDATE count_t set count = count + 1; 然后去数据库命令行执行 SELECT * FROM count_t WHERE id = 1;）
，你会发现，应该只有一个在Updating，其它都是LOCKED

2-2-2 InnoDb 是一个行级别的锁，顾名思义，它会锁住的单位是行，而不是表，所以它同时执行多条操作（同样例子

例如可以使用 ab 在后端中执行5条 UPDATE count_t set count = count + 1; 然后去数据库命令行执行 SELECT * FROM count_t WHERE id = 1;）
如果我没记错，六个应该都在Updating。

小结

在这里的时候，行InnoDb 的效果应该比 MyISAM 要好一些，但是真的 InnoDb 就一定比 MyISAM 好吗？不一定，要联系上下文。
例如，你可以尝试一下，一个后端页面中写这样的功能
先执行一次查询，然后循环一千次，伪代码是这样

//执行sql查询
String sql;
for (int i = 1; 1 <= 1000; i++) {
 	sql = "UPDATE count_t set count = count + 1 wyhere id = "+i;
 	//执行sql修改
 }

接着用 ab 模拟 50 个用户，每个用户请求2次，一共请求100次，可能效果会和你想想的不一样

那么什么时候用 MyISAM? 什么时候用InnoDb ?

个人觉得，多插入修改的时候使用InnoDb，多查询的时候使用 MyISAM吧

其他的后面实践完毕，再一一填坑