【mysql 高级】索引介绍,索引结构 ,哪些情况下需要创建索引 ,哪些情况下不需要创建索引
mysql性能慢的原因
-
1.查询语句写的很烂
-
2.索引失效 (建了索引,但是没有用上)
-
3.关联查询太多join(设计缺陷或不得已的需求)
-
4.服务器各个参数设置不合理,需要调优(缓冲,线程数)、
索引
索引:是帮mysql高效获取数据的数据结构(相当于排序)
在数据之外,数据库还维护着满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构,就是索引。
索引的目的在于提高查询效率,可以类比字典。
索引的简单理解:
如果要查’mysql’这个单词,我们需要先定位到m字母,然后往下找到y字母,再找到下面的sql。
如果没有索引,我们就需要从a开始查,一直找到z。
单值索引:给一个表中的一个字段建立索引
复合索引:给一个表中的多个字段建立索引)
创建索引
基本用法
创建: CREATE 【UNIQUE】 INDEX indexName ON mytable(columnmame(length));
如 id name email
单值索引:create index idx_user_name on user(name) 给name创建索引
多值索引:create index idx_user_nameEmail on user(name,email) 给name和email创建索引
删除: DROP INDEX 【indexName】 ON mytable;
查看: SHOW INDEX FROM table_name;
索引结构
- BTree索引
- Hash索引
- full-text全文索引
- R-Tree索引
那些情况下需要创建索引
1.主键建立唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其它表关联的字段,外键关系建立索引
4.Where条件里用不到的字段不创建索引
5.单键/组合索引的选择(高并发下尽量使用组合索引)
6.查询排序的字段,排序字段若通过索引去访问将大大提高排序速度
7.查询汇总统计或者分组字段
那些情况下不需要创建索引
1.表记录太少
2.经常增删改的表
3.频繁更新的字段不适合创建索引
4.数据重复且分布平均的字段,应该只为经常查询和经常排序的数据建立索引(如果某个数据列中包含许多重复的内容,为它建立索引就没有太大的实际效果,因为索引相当排序)索引的选择性是指索引列中不同值的数目与表中记录的数据比。如果一个表中有2000条记录,表索引列有1980个不同的值,那么这个索引的选择性就是1980/2000=0.99.
一个索引的选择性越接近1,这个索引的效率就越高。
索引优势
- 提高数据检索的效率,降低数据库的IO成本
- 通过索引对数据进行排序,降低数据排序的成本,降低了CPU的消耗。
索引劣势
- 实际上索引也是一张表,该表保存了主键于索引字段,并指向实体表的记录,所以它也是要占用空间的。
- 虽然它大大提高了查询速度,但是同时会降低更新表的速度,因为它每次都会调整因为更新带来的键值变化后的索引信息。
- 如果数据库中有大量数据量的表,就需要花时间研究建立最优秀的索引