mysql索引
索引目的
一般应用系统中数据库读写比例在10:1左右,随着数据量的增多,一些复杂的数据库查询会出现性能问题.索引的目就是在于提高查询效率.
索引原理
索引本质是一种数据结构,可以快速查找具有特定列值的行.避免全表扫描,减少磁盘IO次数,从而提高查询效率.
索引存储类型
- B+Tree: InnoDB使用.每一个叶子节点都包含指向下一个叶子节点的指针,从而方便叶子节点的范围遍历
- B-Tree: 所有的值都是按顺序存储的,并且每一个叶子页到根的距离相同,很适合查找范围数据;可以对<,<=,=,>,>=,BETWEEN,IN,以及不以通配符开始的LIKE使用索引
索引设计
- 索引字段尽量使用数字型（简单的数据类型）
- 尽量不要让字段的默认值为NULL
- 前缀索引和索引选择性
- 使用唯一索引
- 使用组合索引代替多个列索引
- 注意重复/冗余的索引、不使用的索引
索引查询
可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询，但必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。
创建表结构如下
CREATE TABLE People (
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum('m', 'f') not null,
key(last_name, first_name, dob)
);
其组合索引包含表中每一行的last_name、first_name和dob列。其结构大致如下：
最左边前缀原则
- 查询必须从索引的最左边的列开始，否则无法使用索引.例如，你不能直接利用索引查找在某一天出生的人.
- 不能跳过某一索引列.例如，你不能利用索引查找last name为Smith且出生于某一天的人.
- 存储引擎不能使用索引中范围条件右边的列.例如，如果你的查询语句为WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23'，则该查询只会使用索引中的前两列，因为LIKE是范围查询.
支持的查询类型
- 全值匹配: 对索引中的所有列都指定具体的值.例如，上图中索引可以帮助你查找出生于1960-01-01的Cuba Allen.
- 匹配最左前缀: 你可以利用索引查找last name为Allen的人，仅仅使用索引中的第1列.
- 匹配列前缀: 例如，你可以利用索引查找last name以J开始的人，这仅仅使用索引中的第1列.
- 匹配范围值: 可以利用索引查找last name在Allen和Barrymore之间的人，仅仅使用索引中第1列.
- 精准匹配某一列并范围匹配另一列: 可以利用索引查找last name为Allen，而first name以字母K开始的人.
- 只访问索引的查询: 如果查询的列都位于索引中，则不需要再多一次I/O回读元组.
索引使用
-
如果对大的文本进行搜索，使用全文索引而不要用使用 like ‘%…%’
-
like语句不要以通配符开头
对于LIKE：在以通配符%和_开头作查询时，MySQL不会使用索引。like操作一般在全文索引中会用到（InnoDB数据表不支持全文索引）
例如下句会使用索引：
SELECT * FROM mytable WHERE username like'admin%'而下句就不会使用：
SELECT * FROM mytable WHEREt Name like'%admin' -
不要在列上进行运算: 索引列不能是表达式的一部分，也不是是函数的参数.
例如以下两个查询无法使用索引：
select actor_id from sakila.actor where actor_id+1=5; #表达式select ... where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col)<=10; #函数 -
尽量不要使用NOT IN、<>、!= 操作,否则将引擎放弃使用索引而进行全表扫描
-
用 or 分割开的条件， 如果 or 前的条件中的列有索引， 而后面的列中没有索引， 那么涉及到的索引都不会被用到
-
组合索引的使用要遵守“最左前缀”原则'
-
使用索引排序时，ORDER BY也要遵守“最左前缀”原则
-
如果列类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引起来，否则的话即便这个列上有索引，MySQL 也不会用到的，因为MySQL 默认把输入的常量值进行转换以后才进行检索
-
任何地方都不要使用
select * from t，用具体的字段列表代替*，不要返回用不到的任何字段 -
如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引.即索引列有大量数据重复或数据总量很少时.
回表
回表: B+树索引结构为: 主键索引存储整行数据,非主键索引存储的值为主键id.在使用非主键索引进行查询时要先在非主键索引树中找到主键id,再去主键索引查找所在行的具体数据.此过程即为回表
覆盖索引
概念
覆盖索引: 即从非主键索引中就能查到的记录,而不需要查询主键索引中的记录,避免了回表的产生,减少了树的搜索次数,显著提升性能.
使用
将原普通索引删除,然后与需要检索的字段一起创建新的组合索引
如,原表user有主键索引id和非主键索引name.在进行一下查询时就会发生回表
select age from user where name='张三'
修改: 删除name索引,新建name,age联合索引.再次执行上面sql就不会发生回表.
