mysql索引笔记

mysql索引

众所周知，索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构，SQL的主流索引结构有B+树以及Hash结构，聚集索引以及非聚集索引用的是B+树索引。

聚集索引

 

非聚集索引

　　该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

　　分成普通索引，唯一索引，全文索引。

索引结构

　　BTree索引
　　Hash索引
　　full-text全文索引
　　R-Tree索引

避免全表扫描的方法

1.在 where 及 order by 涉及的列上建立索引。

 

2.避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num is null可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0。

3.应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20

 

5.in 和 not in 也要慎用，否则会导致全表扫描。如：select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3。

6.下面的查询也将导致全表扫描：select id from t where name like '李%'若要提高效率，可以考虑全文检索。

7.避免在索引列上使用计算，也就是说，应尽量避免在 where 子句中对字段进行表达式操作和函数操作，这将导致引擎放弃使用索引而进行全表扫描。

如：select id from t where num/2=100应改为:select id from t where num=100*2

select id from t where substring(name,1,3)='abc' ，name以abc开头的id，应改为:select id from t where name like 'abc%'

 

8.很多时候用 exists 代替 in 是一个好的选择：exists用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值true或false。

select num from a where num in(select num from b)

用下面的语句替换：select num from a where exists (select 1 from b where num=a.num)

9.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

10.用>=替代>

高效: SELECT * FROM  EMP  WHERE  DEPTNO >=4 
低效: SELECT * FROM EMP WHERE DEPTNO >3
两者的区别在于, 前者DBMS将直接跳到第一个DEPT等于4的记录，而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

11.用Where子句替换having子句

数据类型选择　

　1.数字类型
　　　　Float和double选择（尽量选择float）
　　　　区分开TINYINT / INT / BIGINT，能确定不会使用负数的字段，建议添加 unsigned定义
　　　　能够用数字类型的字段尽量选择数字类型而不用字符串类型的
　　2.字符类型
　　　　char,varchar,TEXT的选择：非万不得已不要使用 TEXT 数据类型，定长字段，建议使用 CHAR 类型（填空格），不定长字段尽量使用 VARCHAR（自动适应长度，超过阶段），且仅仅设定适当的最大长度
　　3.时间类型
　　　　按选择优先级排序DATE（精确到天）、TIMESTAMP、DATETIME（精确到时间）
　　4.ENUM
　　　　对于状态字段，可以尝试使用 ENUM 来存放
　　5.避免使用NULL字段
　　　　很难查询优化且占用额外索引空间

引用 https://www.cnblogs.com/linyouyi/p/9903010.html

引用 https://www.cnblogs.com/SZLLQ2000/p/9797199.html

感谢