mysql优化,建立索引的优缺点,什么场景需要建立索引,什么场景下无需建立索引,mysql索引知识
管理员 发布于 4年前 386
一、什么是索引？
索引（在MySQL中也叫“键key”）是存储引擎快速找到记录的一种数据结构。
我们需要知道索引其实是一种数据结构，其功能是帮助我们快速匹配查找到需要的数据行，是数据库性能优化最常用的工具之一。其作用相当于超市里的导购员、书本里的目录。
二、索引设计的原则
适合索引的列是出现在where子句中的列，或者连接子句中指定的列；基数较小的类，索引效果较差，没有必要在此列建立索引；使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间；
不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
三、优点：
通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性。可以加快数据的检索速度;可以加速表与表之间的连接;在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间
四、缺点
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加;索引需要占用物理空间，数据量越大，占用空间越大;会降低表的增删改的效率，因为每次增删改索引，都需要进行动态维护
五、什么时候需要创建索引
主键自动建立唯一索引;频繁作为查询条件的字段应该创建索引;查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找;查询中统计或者分组的字段；
六、什么时候不需要创建索引
频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件where条件里用不到的字段，不创建索引；表记录太少，不需要创建索引；经常增删改的表；数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包含大量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建立索引。
七、索引的分类：(可以使用SHOW INDEX FROM table_name;查看索引详情)
主键索引 PRIMARY KEY 它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。注意：一个表只能有一个主键。 唯一索引 UNIQUE 唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。 可以通过ALTER TABLE table_name ADD UNIQUE (column);创建唯一索引： 可以通过ALTER TABLE table_name ADD UNIQUE (column1,column2);创建唯一组合索引： 普通索引 INDEX 这是最基本的索引，它没有任何限制。 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引： 组合索引 INDEX 即一个索引包含多个列，多用于避免回表查询。 可以通过ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);创建组合索引： 全文索引 FULLTEXT 也称全文检索，是目前搜索引擎使用的一种关键技术。 可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引： 索引一经创建不能修改，如果要修改索引，只能删除重建。可以使用DROP INDEX index_name ON table_name;删除索引。
八、索引和sql语句的优化
1、前导模糊查询不能使用索引，如name like ‘%静’ 2、Union、in、or可以命中索引，建议使用in 3、负条件查询不能使用索引，可以优化为in查询，其中负条件有！=、<>、not in、not exists、not like等 4、联合索引最左前缀原则，又叫最左侧查询， 如果在（a，b，c）三个字段上建立联合索引，那么它能够加快a|（a，b）|（a，b，c）三组的查询速度。 5、建立联合查询时，区分度最高的字段在最左边 6、如果建立了（a，b）联合索引，就不必再单独建立a索引。同理，如果建立了（a，b，c）索引就不必再建立a， （a，b）索引 7、存在非等号和等号混合判断条件时，在建索引时，要把等号条件的列前置 8、范围列可以用到索引，但是范围列后面的列无法用到索引。 索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。范围条件有：<、 9、把计算放到业务层而不是数据库层。在字段上计算不能命中索引， 10、强制类型转换会全表扫描，如果phone字段是varcher类型，则下面的SQL不能命中索引。Select * fromuser where phone=13800001234 11、更新十分频繁、数据区分度不高的字段上不宜建立索引。更新会变更B+树，更新频繁的字段建立索引会大大降低 数据库性能。“性别”这种区分度不太大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表 扫 描类似。一般区分度在80%以上就可以建立索引。区分度可以使用count（distinct（列名））/count（*）来 计算。 12、利用覆盖索引来进行查询操作，避免回表。被查询的列，数据能从索引中取得，而不是通过定位符row-locator再 到row上获取，即“被查询列要被所建的索引覆盖”，这能够加速度查询。 13、建立索引的列不能为null，使用not null约束及默认值 14、利用延迟关联或者子查询优化超多分页场景，MySQL并不是跳过offset行，而是取offset+N行，然后放弃前 offset行，返回N行， 那当offset特别大的时候，效率非常低下，要么控制返回的总数，要么对超过特定阈值的页 进行SQL改写。 15、业务上唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。 16、超过三个表最好不要用join，需要join的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索 引。 17、如果明确知道查询结果只要一条，limit 1能够提高效率，比如验证登录的时候。 18、Select语句务必指明字段名称 19、如果排序字段没有用到索引，就尽量少排序 20、尽量用union all 代替 union。Union需要将集合合并后在进行唯一性过滤操作，这会涉及到排序，大量的cpu运算，加大资源消耗及延迟， 当然，使用union all的前提条件是两个结果集没有重复数据。
....
有漏的可以在底部留言建议 3Q
小结:
对于自己编写的SQL查询语句，要尽量使用EXPLAIN命令分析一下，做一个对SQL性能有追求的程序员。衡量一个程序员是否靠谱，SQL能力是一个重要的指标。作为后端程序员，深以为然。
请勿发布不友善或者负能量的内容。与人为善，比聪明更重要！
该博客于2020-12-7日，后端基于go语言的beego框架开发
前端页面使用Bootstrap可视化布局系统自动生成
是我仿的原来我的TP5框架写的博客，比较粗糙，底下是入口
侯体宗的博客
文章标签
- test 三种扣减库存方案 Hyperf2.1启动报错端口占用 上外网神器Ghelper 上外网神器集装箱 laravel中HMVC模式 Hyperf框架验证器 Hyperf自定义函数编写 Hyperf对象转成数组 PHP2021面试题集 centos7系统上挂载 Hyperf框架 秒杀系统的设计 Laravel Octane PHP数组底层实现原理 php7垃圾回收机制 Laravel中类型转换 mysql事务特征及隔离级别 Linux-awk命令 mysql编码 JWT源码实现 Laravel内核分析 Laravel中的用法 centos7+go环境+beego框架 go+beego框架开发博客 go+beego开发博客首页 go+beego自定义公共函数 beego注册_登录_查询 beego控制器创建 Beego框架安装 go语言环境安装 laravel+Elasticsearc laravel运行原理 windows7搭建Vuejs环境 Laravel分块 laravel去重(distinct() laravel集合 laravel+mysql读写分离 laravel5+复杂原生sql laravel+MissMyCat/al lnmp sessio laravel自定义数组分页 laravel中.env配置 laravel8队列发邮件 码云仓库常用操作 laravel8配置163SMTP laravel8配置全局函数 laravel/jetstream之认证 laravel/jetstream
友情链接
