[[230870]]

MySQL 索引使用的注意事项

索引的目的在于提高查询效率。可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql

INSERT 与 UPDATE 语句在拥有索引的表中执行会花费更多的时间，而SELECT 语句却会执行得更快。这是因为，在进行插入或更新时，数据库也需要插入或更新索引值。

索引的类型：

• UNIQUE(唯一索引)：不可以出现相同的值，可以有NULL值

• INDEX(普通索引)：允许出现相同的索引内容

• PROMARY KEY(主键索引)：不允许出现相同的值

• fulltext index(全文索引)：可以针对值中的某个单词，但效率确实不敢恭维

• 组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一

SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不会使用索引,因为所有索引列参与了计算

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; -- 不会使用索引,因为使用了函数运算,原理与上面相同

SELECT * FROM `houdunwang` WHERE `uname` LIKE'后盾%' -- 走索引

SELECT * FROM `houdunwang` WHERE `uname` LIKE "%后盾%" -- 不走索引

-- 正则表达式不使用索引,这应该很好理解,所以为什么在SQL中很难看到regexp关键字的原因

-- 字符串与数字比较不使用索引;

CREATE TABLE `a` (`a` char(10));

EXPLAIN SELECT * FROM `a` WHERE `a`="1" -- 走索引

EXPLAIN SELECT * FROM `a` WHERE `a`=1 -- 不走索引

select * from dept where dname='xxx' or loc='xx' or deptno=45 --如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引, 我们建议大家尽量避免使用or 关键字

-- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

这里看去看我另外整理的一篇关于mysql优化的 索引不生效替代办法

说说反模式设计

这里的反模式针对的是数据库

什么是“反模式”

反模式是一种试图解决问题的方法，但通常会同时引发别的问题。

反模式分类

　　（1）逻辑数据库设计反模式

在开始编码之前，需要决定数据库中存储什么信息以及最佳的数据组织方式和内在关联方式。

这包含了如何设计数据库的表、字段和关系。

　　（2）物理数据库设计反模式

在确定了需要存储哪些数据之后，使用你所知的RDBMS关系型数据库技术特性尽可能高效地实现数据库管理。

这包含了定义表和索引，以及选择数据类型。也需要是要SQL的“数据定义语言”，比如Create Table语句。

　　（3）查询反模式

SQL的查询是使用“数据操作语言”来完成，比如：Insert、Select、Update和Delete语句。

　　（4）应用程序开发反模式

SQL应该会用在Java、.Net、C++、Php等语言构建的应用程序中，在应用程序中使用SQL的方式有好有坏。

反模式分解

　　（1）目的

这是你可能要去尝试解决的任务。意图使用反模式提供解决方案，但通常会以引起更多问题而告终。

　　（2）反模式

这一部分表述了通常使用的解决方案的本质，并且展示了那些没有预知到的后果，正是这些使得这些方案成为反模式。

　　（3）如何识别反模式

一些固定的方式会有助于你辨识在项目中使用的反模式。你遇到的特殊障碍，或是你自己和别人说的一些话，

都能使你提前识别出反模式。

　　（4）合理使用反模式

规则总有例外。在某些情况下，本来认为是反模式的设计却可能是合理的，或者说至少是所有的方案中最合理的。

　　（5）解决方案

描述了首选的最佳解决方案，他们不仅能够解决原有的问题，同时也不至于引起由反模式导致的新问题。

只能介绍些基础的了

说说分库与分表设计

分表的目的就在于此，减小数据库的负担，缩短查询时间。

mysql中有一种机制是表锁定和行锁定，为什么要出现这种机制，是为了保证数据的完整性;我举个例子来说吧，如果有二个sql都要修改同一张表的同一条数据，这个时候怎么办呢，是不是二个sql都可以同时修改这条数据呢？很显然mysql对这种情况的处理是，一种是表锁定（myisam存储引擎），一个是行锁定（innodb存储引擎）。表锁定表示你们都不能对这张表进行操作，必须等我对表操作完才行。行锁定也一样，别的sql必须等我对这条数据操作完了，才能对这条数据进行操作.如果数据太多，一次执行的时间太长，等待的时间就越长，这也是我们为什么要分表的原因。

怎么单库分表呢？

这边只讲两种

1. 预先估计会出现大数据量并且访问频繁的表，将其分为若干个表(需要代码层控制)

事先建100个这样的表，message_00,message_01,message_02..........message_98,message_99.然后根据用户的ID来判断这个用户的聊天信息放到哪张表里面，你可以用hash的方式来获得，可以用求余的方式来获得

2. 利用merge存储引擎来实现分表

表一

表二

插入数据

建立表-注意看建表语句

查询

效果

从上面的操作中，我不知道你有没有发现点什么？假如我有一张用户表user，有50W条数据，现在要拆成二张表user1和user2，每张表25W条数据，
INSERT INTO user1(user1.id,user1.name,user1.sex)
SELECT (user.id,user.name,user.sex)FROM user where user.id <= 250000
INSERT INTO user2(user2.id,user2.name,user2.sex)
SELECT (user.id,user.name,user.sex)FROM user where user.id > 250000
这样我就成功的将一张user表，分成了二个表，这个时候有一个问题，代码中的sql语句怎么办，以前是一张表，现在变成二张表了，代码改动很大，这样给程序员带来了很大的工作量，有没有好的办法解决这一点呢？办法是把以前的user表备份一下，然后删除掉，上面的操作中我建立了一个alluser表，只把这个alluser表的表名改成user就行了。但是，不是所有的mysql操作都能用的

分库

推荐中间件 mycat

分库与分表带来的分布式困境与应对之策

数据迁移与扩容问题（通过程序先读出数据，然后按照指定的分表策略再将数据写入到各个分表中。）

表关联问题（设计之初就应该尽量避免联合查询，可以通过程序中进行拼装）

分页与排序问题（需要在不同的分表中将数据进行排序并返回，并将不同分表返回的结果集进行汇总和再次排序，最后再返回给用户）

分布式事务问题（目前，分布式事务并没有很好的解决方案）

分布式全局唯一ID（ UUID）

说说 SQL 优化之道

常见的简化规则如下：

1）不要有超过5个以上的表连接（JOIN）

2）考虑使用临时表或表变量存放中间结果。

3）少用子查询

4）视图嵌套不要过深,一般视图嵌套不要超过2个为宜。

这里只能挑简单的说了

• 限制结果集（要尽量减少返回的结果行，包括行数和字段列数。）

• 合理的表设计

• 索引优化等等

MySQL 遇到的死锁问题

死锁一般是事务相互等待对方资源，最后形成环路造成的。

1.不同表相同记录行锁冲突

这种情况很好理解，事务A和事务B操作两张表，但出现循环等待锁情况。

2.相同表记录行锁冲突

这种情况比较常见，之前遇到两个job在执行数据批量更新时，jobA处理的的id列表为[1,2,3,4]，而job处理的id列表为[8,9,10,4,2]，这样就造成了死锁。

3.不同索引锁冲突

这种情况比较隐晦，事务A在执行时，除了在二级索引加锁外，还会在聚簇索引上加锁，在聚簇索引上加锁的顺序是[1,4,2,3,5]，而事务B执行时，只在聚簇索引上加锁，加锁顺序是[1,2,3,4,5]，这样就造成了死锁的可能性。

4.gap锁冲突

innodb在RR级别下，如下的情况也会产生死锁，比较隐晦。不清楚的同学可以自行根据上节的gap锁原理分析下。

如何避免死锁

1）以固定的顺序访问表和行。比如对第2节两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；又比如对于3.1节的情形，将两个事务的sql顺序调整为一致，也能避免死锁。

2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。

3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。

4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。

5）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。

存储引擎的 InnoDB 与 MyISAM

1、事务处理

innodb 支持事务功能，myisam 不支持。

Myisam 的执行速度更快，性能更好。

2、select ,update ,insert ,delete 操作

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择

InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表

3、锁机制不同

InnoDB 为行级锁，myisam 为表级锁。

注意：当数据库无法确定，所找的行时，也会变为锁定整个表。

如： update table set num = 10 where username like "%test%";

4、查询表的行数不同

MyISAM：select count(*) from table,MyISAM只要简单的读出保存好的行数，注意的是，当count(*)语句包含where条件时，两种表的操作是一样的

InnoDB ： InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行

5、物理结构不同

MyISAM ：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。

.frm文件存储表定义。

数据文件的扩展名为.MYD (MYData)。

索引文件的扩展名是.MYI (MYIndex)

InnoDB：基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB

6、anto_increment 机制不同

更好和更快的auto_increment处理

其他：为什么MyISAM会比Innodb 的查询速度快

INNODB在做SELECT的时候，要维护的东西比MYISAM引擎多很多；

1）数据块，INNODB要缓存，MYISAM只缓存索引块， 这中间还有换进换出的减少；

2）innodb寻址要映射到块，再到行，MYISAM 记录的直接是文件的OFFSET，定位比INNODB要快

3）INNODB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护

MVCC ( Multi-Version Concurrency Control )多版本并发控制

InnoDB：通过为每一行记录添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。但是InnoDB并不存储这些事件发生时的实际时间，相反它只存储这些事件发生时的系统版本号。这是一个随着事务的创建而不断增长的数字。每个事务在事务开始时会记录它自己的系统版本号。每个查询必须去检查每行数据的版本号与事务的版本号是否相同。让我们来看看当隔离级别是REPEATABLE READ时这种策略是如何应用到特定的操作的：

SELECT InnoDB必须每行数据来保证它符合两个条件：

1、InnoDB必须找到一个行的版本，它至少要和事务的版本一样老(也即它的版本号不大于事务的版本号)。这保证了不管是事务开始之前，或者事务创建时，或者修改了这行数据的时候，这行数据是存在的。

2、这行数据的删除版本必须是未定义的或者比事务版本要大。这可以保证在事务开始之前这行数据没有被删除。

为什要 用 B-tree

　B-Tree就是我们常说的B树，一定不要读成B减树，否则就很丢人了。B树这种数据结构常常用于实现数据库索引，因为它的查找效率比较高 。理论太多了，全靠百度

聚集索引与 非聚集索引的区别

SQL SERVER提供了两种索引：聚集索引和非聚集索引。其中聚集索引表示表中存储的数据按照索引的顺序存储，检索效率比非聚集索引高，但对数据更新影响较大。非聚集索引表示数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置，非聚集索引检索效率比聚集索引低，但对数据更新影响较小。

limit 20000 加载很慢怎么解决

举个例子

日常分页SQL语句

select id,name,content from users order by id asc limit 100000,20

扫描100020行

如果记录了上次的最大ID

select id,name,content from users where id>100073 order by id asc limit 20

扫描20行。

1.子查询优化法

先找出第一条数据，然后大于等于这条数据的id就是要获取的数据

缺点：数据必须是连续的，可以说不能有where条件，where条件会筛选数据，导致数据失去连续性

select * from Member where MemberID >= (select MemberID from Member limit 100000,1) limit 100

从结果中可以得知，当偏移1000以上使用子查询法可以有效的提高性能。

选择合适的分布式主键 方案

1 不能有单点故障。

2 以时间为序，或者ID里包含时间。这样一是可以少一个索引，二是冷热数据容易分离。

3 可以控制ShardingId。比如某一个用户的文章要放在同一个分片内，这样查询效率高，修改也容易。

4 不要太长，最好64bit。使用long比较好操作，如果是96bit，那就要各种移位相当的不方便，还有可能有些组件不能支持这么大的ID。

twitter

1 41位的时间序列（精确到毫秒，41位的长度可以使用69年）

2 10位的机器标识（10位的长度最多支持部署1024个节点）

3 12位的计数顺序号（12位的计数顺序号支持每个节点每毫秒产生4096个ID序号） 最高位是符号位，始终为0。

Flicker在解决全局ID生成方案里就采用了MySQL自增长ID的机制（auto_increment + replace into + MyISAM）。一个生成64位ID

UUID算法的核心思想是结合机器的网卡、当地时间、一个随即数来生成UUID

基于redis的分布式ID生成器

MongoDB文档（Document）全局唯一ID

聊聊 MongoDB 使 用场景

mongodb的主要目标是在键/值存储方式（提供了高性能和高度伸缩性）以及传统的RDBMS系统（丰富的功能）架起一座桥梁，集两者的优势于一身。mongo适用于以下场景：

a.网站数据：mongo非常适合实时的插入，更新与查询，并具备网站实时数据存储所需的复制及高度伸缩性。

b.缓存：由于性能很高，mongo也适合作为信息基础设施的缓存层。在系统重启之后，由mongo搭建的持久化缓存可以避免下层的数据源过载。

c.大尺寸、低价值的数据：使用传统的关系数据库存储一些数据时可能会比较贵，在此之前，很多程序员往往会选择传统的文件进行存储。

d.高伸缩性的场景：mongo非常适合由数十或者数百台服务器组成的数据库。

e.用于对象及JSON数据的存储：mongo的BSON数据格式非常适合文档格式化的存储及查询。

不适合的场景：

a.高度事物性的系统：例如银行或会计系统。传统的关系型数据库目前还是更适用于需要大量原子性复杂事务的应用程序。

b.传统的商业智能应用：针对特定问题的BI数据库会对产生高度优化的查询方式。对于此类应用，数据仓库可能是更合适的选择。

c.需要SQL的问题

倒排索引

常规的索引是文档到关键词的映射：

文档——>关键词

但是这样检索关键词的时候很费力，要一个文档一个文档的遍历一遍。（这事不能忍~）

于是人们发明了倒排索引~

倒排索引是关键词到文档的映射

关键词——>文档

这样，只要有关键词，立马就能找到她在那个文档里出现过，剩下的事就是把她揪出来了~~~

---