Mysql高级操作
索引概述:
索引是高效获取数据的数据结构
索引结构:
B+Tree()
Hash(不支持范围查询,精准匹配效率极高)
树的区别:
二叉树:可能产生不平衡,顺序数据可能会出现链表结构
平衡二叉树:插入需要自旋,性能根据层级而定,性能不稳定
b+tree:
主键聚簇叶子节点存放数据,非叶子节点存放索引,
二级索引非叶子节点存放索引,叶子节点存放主键
索引优缺点:
优点:
大大加快查询速度
唯一索引可以保证字段唯一
可以加速表与表之间的连接
缺点
创建和维护索引需要消耗时间,随着数据量增加时间也会增加
占用磁盘空间
对表进行urd操作时候也要动态维护,urd性能会下降
创建索引原则(我们对哪种数据创建索引):
更新频繁数据不易创建索引
数据量少的没必要创建,全表和用索引可能差不多
首先考虑在where和orderby字段建立索引
索引分类:
单列索引(只包含单个列):
主键索引:唯一且不为null,一个表只能有一个,(聚集索引:叶子节点下存储数据)
唯一索引:唯一且只能有一个Null值(二级索引,叶子节点存储主键)
普通索引:没有限制(二级索引,叶子节点存储主键)
组合索引/复合索引(包含多列):
为了避免回表,进行更高效的查询
全文索引:
like+%(InnoDB(5.6之后支持)默认3个字符,最大84,MyISam默认4最小1个字符)
空间索引(使用较少)
Sql性能分析
数据库的执行频次
慢查询日志
profile Sql执行查询
explain/desc执行计划查询
索引使用
联合索引:
遵循最左原则,如果最左使用中间跳过了某个字段,会造成后面索引失效,范围查询右侧的列会失效,尽量是<= ,>=
索引失效:
索引列进行了函数运算
没有遵循最有匹配原则
字符串类型索引没有加”,造成隐士转换,导致索引失效
左模糊查询
如果最左使用中间跳过了某个字段,会造成后面索引失效,范围查询右侧的列会失效,尽量是<= ,>=
mysql优化器判定全表比用索引块
or链接一侧有索引,一侧没有索引
sql提示:
多个索引下,可以提醒执行器是由哪个索引,建议使用,忽略使用,强制使用
覆盖索引:
查询返回字段都在联合索引中会直接拿到数据,避免回表即联合索引
前缀索引:
针对字段数据库较大的建立索引,缩小索引长度
单列/联合索引:
避免单列索引在and情况下第二索引不生效,使用联合索引,使用恰当可避免回表
索引设计原则
表层面:数据量大,且查询频繁
字段层面:经常在where groupby orderby后的字段
索引层: 唯一的建立唯一索引,尽量联合索引,大文本尽量前缀索引
附加原则:
区分度较高
索引不易过多
索引不为null加上非空约束
所长度尽量短