1.什么是索引?
- 索引是一种能够帮助mysql高效获取数据的数据结构,即在存储数据之外,msyql还需要开辟空间来维护这些满足特定查找算法的数据结构。这种索引可以指向我们的数据,通过特定的数据结构实现高效的查找算法。
- 高效获取数据的一种数据结构
2.当我们查询数据时候,没有索引,就会整个表遍历,性能极低
3.索引有什么用?
- mysql的数据是存储在磁盘中的,查询数据就会进行io操作,索引可以减少io操作
- .MYI存放索引文件
4.索引在存储引擎中实现
- 不同的存储引擎 有不同的索引结构
二叉树 的弊端:
顺序插入时,会形成一个单项的链表结构,降低查询性能
红黑树:是一个自动平衡的二叉树,但是当数据量较大时,形成的树状结构较深,检索速度降低
n阶的B树 有n-1个key 和n个指针
B+树 所有的数据都会出现在叶子节点,叶子节点上的节点起到索引的作用,而且叶子节点形成单项链表
mysql索引对b+树进行优化,在原有B+树的基础上,增加一个指向相邻链表的指针
根据索引的不同形式可以分为以下两种:
默认主键索引是聚集索引,聚集索引的选择规章:主键 ----- 第一个唯一索引----自动生成一个rowid作为聚集索引 (innodb)存储引擎中
根据聚集索引查询的性能会比根据二级索引的查询效率高
关于InnoDB索引的数据结构
采用b+树作为存储数据的数据结构,B+树的两大好处:1 查询速度更快,2 查询比较稳定
因为innodb 默认存在聚集索引,一般都是主键值 主键的信息比较明了,叶子节点上面存放所有的数据,而非叶子节点上存放的是索引值。就造成每页的数据相对来说更多一些,内存跟磁盘进行交互是以页作为单位,这样可以减少io操作,就能够提升查询的效率
对于sql来说,存放在叶子节点上的数据,执行的路径是一样长的,保证可以查询稳定性
为什么不用二叉树或者红黑树?
因为随着数据量过大,二叉树会发生倾斜,红黑树也会层级更深,而且红黑树本身不平衡,二叉树有可能会形成一个链表,而层级更深的红黑树也会降低查询的效率
mysql性能分析的工具
- sql的执行频率 show [session|global] status 可以查询当前数据库的crud的执行频率
- show global status likes “Com_______”
慢查询日志 用于定位sql优化
show variables like ‘slow_query_log’;
修改为on
设置慢查询日志的时间,超过两秒视为慢查询
索引的使用
- 最左前缀法则:如果索引了多列(联合索引),要遵循最左前缀法则,最左前缀法则指的是查询从索引的最左列开始,并不跳过索引中的列。如果跳过某一列,索引将部分失效
可以看到如果不包含最左边的列,索引失效
和索引字段放置的位置无关 2.范围查询
联合索引中出现(><),范围查询的右侧索引会失效。
status 索引失效 ,避免失效可以在业务允许的情况使用 >= , <=
3.索引列运算
在索引列上运算,则会使索引失效
字符串不加单引号
模糊查询 如果是尾部进行模糊匹配则索引不会失效,如果是头部进行模糊匹配则索引失效。
or连接条件 如果用or隔开的条件,如果or前面的条件中的索引,而后面的列中没有索引,那么涉及的索引都不会被用到
对于or连接条件,只需要将没有索引的字段建立索引即可。
数据分布影响 如果mysql评估,走索引的速度比全表扫描还慢,则不会走索引。如果要查询的数据表中数据分布较少,则会走索引,如果表中的数据分布较多,则会走全表扫描
SQL 提示
– 有单列索引,和联合索引,但是最后查询走了联合索引
– EXPLAIN select * from tb_user where profession =‘软件工程’;
- use index
- ignore index
- force index
use index 可以建议mysql我们所需要用的索引 (并不是必须使用)
EXPLAIN SELECT * FROM tb_user use INDEX(idx_user_pro) where profession =‘软件工程’;
ignore index
EXPLAIN SELECT * FROM tb_user IGNORE INDEX(idx_user_pro) where profession =‘软件工程’;
force index
EXPLAIN SELECT * FROM tb_user FORCE INDEX(idx_user_pro) where profession =‘软件工程’;
覆盖索引
尽量使用覆盖索引(查询使用了索引,并且需要返回的列,该索引中已经全部能够找到)减少select *前缀索引
当字段类型为 字符串 (varchar,text),有时需要索引很长的字符串,这会让索引变得更大,查询时,浪费大量的磁盘ID,影响查询效率,此时可以将字符串的一部分前缀,建立索引,这样可以大大节约索引的空间,从而提高索引效率