一、索引的数据结构和磁盘的存取原理
1》不使用顺序查找,因为顺序查找比较慢,通过特定数据结构的特点来提升查询速度,这种数据结构就是可以理解成索引。
2》索引一般以文件形式存储在磁盘上,索引检索需要磁盘I/O操作,为了尽量减少磁盘I/O。磁盘往往不是严格按需读取,而是每次都会预读,而且内存和磁盘以页为单位交换数据,所以在读取的数据不存在内存中时,会从磁盘中读取一批数据(页)到内存中。
3》不管在哪种程序优化上,要想快速提高性能,直接将常用的、少变更的数据直接读取到内存中,使用的时候直接在内存上读取,而不去磁盘上读取,减少I/O操作,这样就能使程序快上10倍以上,但由于内存容量的限制,也不可能将所有的数据都放内存中。
二、建立索引目的:
1》加快查询速度,使用索引后查询有迹可循。
2》减少I/O操作,通过索引的路径来检索数据,不是在磁盘中随机检索。
3》消除磁盘排序,索引是排序的,走完索引就排序完成。
三、MySQL索引分类
1》普通索引:最基本的索引,没有任何限制
2》唯一索引:与“普通索引”类似,不同的就是:索引列的值必须唯一,但允许有空值
3》主键索引:它是一种特殊的唯一索引,不允许有空值。
4》全文索引:仅可用于MyISAM表,针对较大的数据,生成全文索引很耗时耗空间
5》组合索引:为了更多的提高mysql效率可建立组合索引,遵循‘最左前缀’原则
6》覆盖索引:就是直接走的索引,直接在内存中就拿到值,不需要查询数据库。如分页就要走覆盖索引,因为性能比较高
7》聚簇索引:主键就是聚集索引,聚簇索引保证关键字的值相近的元组存储的物理位置也相同(所以字符串型不宜建立聚簇索引,特别是随机字符串,会使得系统进行大量的移动操作),且一个表只能有一个聚簇索引。因为有存储引擎实现索引,所以并不是所有的引擎都支持聚簇索引。目前,只有solidDB和InnoDB支持。
8》非聚簇索引:二级索引叶子节点保存的不是指行的物理位置的指针,而是行的主键值。这意味着通过二级索引查找行。InnoDB对主键建立聚簇索引。如果不指定主键,InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引,InnoDB会定义一个隐藏的主键,然后对其建立聚簇索引。一般来说,DBMS都会以聚簇索引的形式来存储实际的数据,它是其它二级索引的基础。
优化注意点:
1》索引其实就是一种归类方式,当某一个字段属性都不能归类,建立索引后是没什么效果的,或归类就二种(0和1),且各自都数据对半分,建立索引后的效果也不怎么强。
2主键的索引是不一样的,要区别理解。
3当时间存储为时间戳保存的可以建立前缀索引。
4.在什么是字段上建立索引,需要根据查询条件而定,不要一上来就建立索引,浪费内存还有可能用不到。
5.大字段(blob)不要建立索引,查询也不会索引。
6.唯一索引一定要小心使用,它带有唯一约束,由于前期需求不明等情况,可能造成我们对于唯一的误判。
7.写入比较频繁的时候,不能开启MySQL的查询缓存,因为在每一次写入的时候不光要写入磁盘还得更新缓存中的数据。
8.其实建索引的原理就是将磁盘I/O操作的最小化,不在磁盘中排序,而是在内存中排好序,通过排序的规则去指定磁盘读取就行,也不需要在磁盘上随机读取。
9.由于磁盘整理磁盘碎片,所有有的时候我们也可以通过建立聚集索引来减少这一类的问题。
10.当一个表中有100万数据,而经常用到的数据只有40万或40万以下,是不用考虑建立索引的,没什么性能提升。
11.使用count统计数据量的时候建议使用count(*)而不是count(列),因为count(*)MySQL是做了优化的。
12.二次SQL查询区别不大的时候,不能按照二次执行的时间来判断优化结果,没准第一次查询后又保存缓存数据,导致第二次查询速度比第二次快,很多时候我们看到的都是假象。
13.什么时候开MySQL的查询缓存,系统(写多,读少)、SQL优化测试,建议关闭查询缓存,系统(写少,读多),建议开启查询缓存。
14.Explain执行计划只能解释SELECT操作。
15.查询优化可以考虑让查询走索引,走索引能提升查询速度,索引覆盖是最快的
16.子查询比join快,虽然规律不绝对,但对大表多数有效
17.where条件多条件一定要按照小结果集排大结果集前面
18.尽量避免大事务操作,提升系统并发能力,有时无法避免,改用定时器延迟处理
19.使用UNION ALL替换OR多条件查询并集。
20.在大数据表删除也是一个问题,避免删除过程数据库奔溃,可以考虑分配删除,一次删除1000条,删完后等一会继续删除
21
四、常用索引的地方
1》主键的聚集索引
2》外键索引
3》类别只有0和1就不要建索引,没有意义,对性能没有提升,还影响写入性能
4》用模糊其实是可以走前缀索引
五、不适合建立索引:
1》频繁更新的字段不适合建立索引
2》where条件中用不到的字段不适合建立索引,都用不到建立索引没有意义还浪费空间
3》表数据可以确定比较少的不需要建立索引
4》数据重复且发布比较均匀的字段不适合建索引(唯一性太差的字段不适合建立索引),例如性别
5》参与列计算的列不适合建立索引
使用Explain
https://blog.csdn.net/why15732625998/article/details/80388236