索引原理

索引原理

1.innodb索引

• 聚焦左营 只有一个逐渐
• 辅助索引 除了主键之外的所有索引都是辅助索引
• 回表: 只查询一个索引并不能解决查询中的问题,还需要到具体的表中去获取正行数据

2.myisam索引

• 辅助索引 除了主键之外的索引都是辅助索引

3.树

• b树
  • blance tree
• b+树
  • 数据只存在叶子节点
  • 在子节点之间加入了双向地址链接,方便在子节点之间进行数据的读写

4.索引的种类

• primary key 的创建自带索引效果 非空 + 唯一 + 聚焦索引
• unique 唯一约束的创建也自带索引效果 唯一 + 辅助索引
• index 普通的索引 辅助索引
• 创建索引
  • create index ind_name on 表 (name);
• 删除索引
  • drop index ind_name on 表;

5.索引的优缺点

• 优点: 查找速度快
• 缺点 : 浪费空间,托慢写的速度
  • 不要再程序中创建无用的索引

6.正确的是使用索引

• 所查询的列不是创建了索引的列
• 在条件中不能带运算或者函数,必须是"字段 = 值"
• 如果创建索引的列的内容重复率高也不能有效利用索引
  • 重复率不超过10%的列适合做索引
• 数据对应的范围如果太大的话也会不命中
  • between and > < >= <= != not in
• like如果把%放在最前面也不能命中索引
• 多条件的情况
  • and 只要有一个条件列是索引列就能命中索引
  • or 只有所有的条件都是索引列才能命中索引
• 联合索引
  • where a = xx and b = xxx;
  • 对a和b都创建索引 _ 联合索引
  • create index ind_minx on s1(id,name);
  • 在多个条件的相连的情况下,使用联合索引的效率要高于使用单字段索引
  • 按照创建索引的顺序条件中从哪一个字段开始出现范围,后面无论如何精确索引都不能命中了
  • 联合索引在使用的时候遵循最左前缀原则:
    • 最左边的字段必须带上才能命中索引
  • 联合索引中只能使用and才能生效,使用or生效

多几嘴

1.mysql 神器 explain

• 执行计划
• explain select* from s1 where id < 100000;
• 查看语句是否命中索引.命中索引的类型

2.覆盖索引,索引合并

• 覆盖索引
  • using index 表示索引覆盖
• 索引合并
  • 创建索引时是分开创建的,用的时候临时合在一起了
  • using union 表示索引合并

3.mysql 慢日志

• 慢日志是通过配置文件开启
• DBA可以管这个

4.7表联查速度慢怎么办

• 表结构的角度
  • 尽量用固定的数据类型代替可变数据类型
  • 把固定长度放在前面
• 数据的角度
  • 表中的数据多,查询效率慢
    • 列多: 垂直分表
    • 行多 : 水平分表
• sql的角度
  • 尽量把条件写的细致点,where条件就多做筛选,
  • 多表的时候连表代替子查询
  • 创建有效的索引,而规避无效的索引
• 配置的角度
  • 开启慢日志查询,来确认具体的有问题的sql
• 数据库
• 读写分离
  • 解决数据库读的瓶颈

5.数据库的导入导出

• mysqldump -uroot -p123 homework > 路径 : 备份数据库中的所有表和数据
• 创建一个新的库,sourse 路径:恢复表
• 备份库
  • mysqldump -uroot -p123 --database homework > 路径

6.开启事务,给数据加锁

• begin;
• select a from b where 条件 for update;
• update b set ... where 条件;
• commit; 未提交前别人用不了这条数据,会夯住