数据库索引的原理?创建索引的缺点是什么，什么情况索引失效?优化数据库的方法有哪些?

数据库的索引原理需要以具体的数据库为准，但都是大差不差的，基本都是B树。如一些系统的文件使用的索引也是B树。

以Mysql的索引为列

• MySql数据库使用的索引结构为B+数，但Mysql使用的B+树不同于通常的B+树，它会在B+树的页子节点曾加一条向左的指针，使其成为的双向链表。

创建索引的缺点

什么是索引

• 索引是一种帮助快速查找数据的数据结构，既然是数据，那么需要占用一定的资源。

索引只适用与读多写少的表，为了保证索引的时效性。在数据被更改之后，所以需要重新建立部分索引，主要占用了CPU资源。

索引还需要占用一定的磁盘空间，所以索引一般适用于较短的字段，如有特殊必要可以使用前缀索引。

索引失效场景

1. OR

2. Like

3. 数据分布

4. 类型不一致

5. 函数计算

6. 不满足最左前缀法则

7. 使用大于>或小于<

• 索引失效场景大部分都和B+数的特性：有序性 有关

数据库如何优化

• 硬件

  • 更换为高性能的硬件

• 软件

  • 架构

    • 分库分表

    • 引入缓存

    • 主从集群

    • 连接池优化

    • 操作系统调优

    • 网络

    • ES

  • 数据库配置

    • 最大链接数量

    • 超时时间

    • 设置内存缓冲区大小

    • 合适的表类型字段

  • 索引

    • 在读多查少的表建立索引

    • 在区分度高的字段建立索引

    • 创建时满足最左前缀法则，将常用字段放置最左侧

    • 使用时尽量符合最左法则，避免第一个字段失效

    • 针对有需求的大字段建立前缀索引

    • 使用覆盖索引查询

    • 使用聚集索引查询

    • 可以的前提建立联合索引

    • 避免回表

    • 对常用where字段建立索引

    • . . . . . .

  • SQL

    • 插入数据时，使用单条语句批量插入，十万级+数据可以使用Mysql提供的Load指令插入

    • orderBY排序时候，建立索引，避免FileSort ，根据情况建立降/升序索引（Mysql 8.0 引入）

    • 尝试手动控制事务，避免重复提交开启

    • 根据索引删除，避免行锁升级表锁

    • 大数据分页可以嵌套一个Id子查询，或对Id添加>=条件

    • 使用有序行/简短的主键数据插入，避免一些页分裂/合并

    • 避免索引失效场景

    • 使用explain/optimizer_trace分析慢SQL

    • . . . . . .

• 总结到SQL优化中，就三点:

  • 最大化利用索引；

  • 尽可能避免全表扫描；

  • 减少无效数据的查询；