MySQL索引及其优化查询

MySQL-索引

索引优势：提高检索效率、降低数据库的IO成本、通过索引对数据进行排序、降低数据排序成本、降低CPU性能消耗

劣势：索引也是一张表、表中保存了主键与索引字段、并指向实体表的记录、索引也占内存空间

• 索引分类

• 单值索引
  • 一个索引只包含单个列、一个表有多个单列索引
• 唯一索引
  • 索引列的值必须唯一、但可以为null
• 复合索引
  • 一个索引包含多个列

• MyISAM和In noDB索引区别

• MyISAM
  • 不支持事务、每次查询都是原子性的
  • 支持表级锁、每次操作都是针对整张表
  • 存储表的总行数、一个MyISAM有3个文件(类结构文件、索引文件、数据文件)
• InnoDB
  • 支持ACID事务
  • 支持四种隔离级别（读未提交、读已提交、可重复读、序列化）
  • 支持行级锁、外键约束、写并发
  • 不仅缓存索引、还缓存数据

• 索引底层采用B+Tree：在所有叶子节点存储下一个节点的指针（真实数据只保存于叶子节点中）、非叶子节点不保存数据、只存储指引搜索方向的数据项。

• 哪些情况不需要建立索引

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该简历索引
• 查询中与其他表关联的字段（通过外键建立索引）

• 事务的特性

• 一致性
• 原子性
• 隔离性
• 持久性

• SQL优化

• SQL语句及其索引优化
• 数据库表结构优化
• 系统配置优化、硬件优化

• 分库分表策略

• 按照权重进行分配
• 按照业务进行分配
• 按照一致性哈希算法进行分配

SQL优化

查看执行计划：explan+sql语句

1. 开启慢查询日志、设置阈值、将超过阈值的sql抓取出来
2. 使用explan+sql语句进行分析
3. 查看id、type、key、rows、Extra
4. Show profile（查询sql在mysql服务器里面执行的细节和声明周期情况）
5. 然后进行参数调优

• id：select 查询序号包含一组数字、表示查询中执行select子句操作表的顺序

• id相同执行顺讯由上到下
• Id不同、如果是子查询，id序号会自增，id值越大，优先级越高，越先被执行

• type:查询显示的类型：system>const>eq_ref>ref>range>index>all

• system:表示只有一行记录
• Const：通过索引一次就可以查询到
• eq_ref：唯一索引扫描、对于每一个索引键、都只有一条记录与其匹配、常见于主键或唯一键索引
• Ref：非唯一索引扫描、返回匹配某个值的所有行
• Range：只检查给定范围的行、使用一个索引来进行选择
• Index：遍历索引树
• All：遍历全表查找匹配的行

• Key：实际使用的索引、如果为null则没有使用索引

• Rows：每张表有多少行被优化查询

• 如何避免索引失效？

• 最佳左前缀原则
  • 如果索引了多列，要遵循最佳左前缀原则，并且中间的索引列不能断
• 不在索引列做任何操作（计算、函数会导致索引失效）
• 使用覆盖引擎（按需获取数据、避免使用Select *）
• 范围之后全失效
• 不使用!= 、is null、is not null
• ☆☆☆ Like ☆☆☆ 通配符%在前面会进行全表查询-会导致索引失效
• 字符串不加单引号
• 少用or