mysql

为什么使用数据索引能提高效率

1. 数据索引的存储是有序的
2. 在有序的情况下，通过索引查询一个数据是无需遍历索引记录的
3. 极端情况下，数据索引的查询效率为二分法查询效率，趋近于 log2(N)

mysql联合索引

1. 联合索引是两个或更多个列上的索引。对于联合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a 、 a,b 、 a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。
2. 利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知 道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。

什么情况下应不建或少建索引

1. 表记录太少
2. 经常插入、删除、修改的表
3. 数据重复且分布平均的表字段，假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
4. 经常和主字段一块查询但主字段索引值比较多的表字段

为什么要分区？

1. 我们都知道mysql数据库的数据以文件的形式存储在磁盘，默认是放在/mysql/data下面，一张表对应着三个文件，一个是frm存放表结构，一个是myd存放表数据的，一个是myi存表索引的。
2. 假设有一张表的数据量非常大，那么myd，myi就会变得很大，查找数据就会变得很慢，那么我们的解决方法是什么？就是分区，在物理上将这一张表对应的三个文件，分割成许多个小块，然后我们查找一条数据时，就不用全部查找了，只需知道这条数据在哪一块，然后在那一块找就行，如果表的数据太大，一个磁盘也放不下，我们可以把数据分配到不同的磁盘里面

四种隔离级别

1. Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。
2. Repeatable read (可重复读)：可避免脏读、不可重复读的发生。
3. Read committed (读已提交)：可避免脏读的发生。
4. Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

行级锁定的优点：

1. 当在许多线程中访问不同的行时只存在少量锁定冲突。
2. 回滚时只有少量的更改
3. 可以长时间锁定单一的行。

行级锁定的缺点：

1. 比页级或表级锁定占用更多的内存。
2. 当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。
3. 如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。
4. 用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。

MySQL 触发器简单实例

1. CREATE TRIGGER --触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.
2. { BEFORE | AFTER } --触发器有执行的时间设置：可以设置为事件发生前或后。
3. { INSERT | UPDATE | DELETE } --同样也能设定触发的事件：它们可以在执行insert、update或delete的过程中触发。
4. ON --触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。
5. FOR EACH ROW --触发器的执行间隔：FOR EACH ROW子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次。
6. --触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句， 包括复合语句，但是这里的语句受的限制和函数的一样。

B+树索引和哈希索引的区别

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接，是有序的

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可,是无序的

哈希索引的优势：

1. 等值查询。哈希索引具有绝对优势（前提是：没有大量重复键值，如果大量重复键值时，哈希索引的效率很低，因为存在所谓的哈希碰撞问题。）

哈希索引不适用的场景：

1. 不支持范围查询
2. 不支持索引完成排序
3. 不支持联合索引的最左前缀匹配规则

通常，B+树索引结构适用于绝大多数场景，像下面这种场景用哈希索引才更有优势：

在HEAP表中，如果存储的数据重复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引，例如这种SQL：

select id,name from table where name='李明'; — 仅等值查询

而常用的InnoDB引擎中默认使用的是B+树索引，它会实时监控表上索引的使用情况，如果认为建立哈希索引可以提高查询效率，则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引（在InnoDB中默认开启自适应哈希索引），通过观察搜索模式，MySQL会利用index key的前缀建立哈希索引，如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询。

注意：在某些工作负载下，通过哈希索引查找带来的性能提升远大于额外的监控索引搜索情况和保持这个哈希表结构所带来的开销。但某些时候，在负载高的情况下，自适应哈希索引中添加的read/write锁也会带来竞争，比如高并发的join操作。like操作和%的通配符操作也不适用于自适应哈希索引，可能要关闭自适应哈希索引。

数据库表创建注意事项

一、字段名及字段配制合理性

1. 剔除关系不密切的字段
2. 字段命名要有规则及相对应的含义（不要一部分英文，一部分拼音，还有类似a.b.c这样不明含义的字段）
3. 字段命名尽量不要使用缩写（大多数缩写都不能明确字段含义）
4. 字段不要大小写混用（想要具有可读性，多个英文单词可使用下划线形式连接）
5. 字段名不要使用保留字或者关键字
6. 保持字段名和类型的一致性
7. 慎重选择数字类型
8. 给文本字段留足余量

二、系统特殊字段处理及建成后建议

1. 添加删除标记（例如操作人、删除时间）
2. 建立版本机制

三、表结构合理性配置

1. 多型字段的处理，就是表中是否存在字段能够分解成更小独立的几部分（例如：人可以分为男人和女人）
2. 多值字段的处理，可以将表分为三张表，这样使得检索和排序更加有调理，且保证数据的完整性！

四、其它建议

1. 对于大数据字段，独立表进行存储，以便影响性能（例如：简介字段）
2. 使用varchar类型代替char，因为varchar会动态分配长度，char指定长度是固定的。
3. 给表创建主键，对于没有主键的表，在查询和索引定义上有一定的影响。
4. 避免表字段运行为null，建议设置默认值（例如：int类型设置默认值为0）在索引查询上，效率立显！
5. 建立索引，最好建立在唯一和非空的字段上，建立太多的索引对后期插入、更新都存在一定的影响（考虑实际情况来创建）。

另外：本月的第一波福利已经在微头条公布了，还没有领取的可以去领取一下。

閱讀更多 Java高級架構師 的文章

關鍵字: 查询 技术 索引