正德厚生,臻于至善

MySQL索引

索引是什么?
MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。
可以简单理解为“排好序的快速查找数据结构”。
数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上我们平常所说的索引,如果没有特别指明,都是指B树(多路搜索树,并不一定是二叉的)结构组织的索引

优势:
类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本。
通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。

劣势:
实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的。
虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

分类:
单值索引:即一个索引只包含单个列,一个表可以有多个单列索引。
唯一索引:索引列的值必须唯一,但允许有空值。
复合索引:即一个索引包含多个列。

基本语法
创建
CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length));
ALTER TABLE 表名 ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
删除
DROP INDEX [indexName] ON mytable;
查看
SHOW INDEX FROM table_name\G
使用ALTER命令
有三种方式来添加数据表的索引:
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。

索引失效:
1.全值匹配我最爱
2.最佳左前缀法则
3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
4.存储引擎不能使用索引中范围条件右边的列
5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
6.MySQL在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
7.注意null/not null对索引的可能影响
8.like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作
问题:解决like ‘%字符串%’时索引不被使用的方法??
9.字符串不加单引号索引失效
10.少用or,用它来连接时会索引失效

【优化口诀】
全职匹配我最爱,最左前缀要遵守;
带头大哥不能死,中间兄弟不能断;
索引列上少计算,范围之后全失效;
LIKE百分写最右,覆盖索引不写*;
不等空值还有OR,索引影响要注意;

赞(0) 打赏
未经允许不得转载:徐万新之路 » MySQL索引
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

联系我们

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏