MySQL基础之索引

目录

索引是什么?

索引的优势与劣势?

Mysql索引结构：

索引的分类

什么情况下需要建立了索引

那些情况下不适合建立索引

Mysql的常见的瓶颈:

索引创建的语法以及其他深入知识

索引失效问题：

---

索引是什么?

索引是排好序的快速查找的数据结构

索引的优势与劣势?

优势：提高数据检索的效率，降低数据库的IO成本，通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势：实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也是要占空间的，虽然索引大大提高了查询速度，同事会降低更新速度，如对表的insert　update　delete等操作，因为更新表时，Mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新带来的键值对、变化后的索引信息。

Mysql索引结构：

• BTree索引

• B＋Tree索引

索引的分类

主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引

单值索引：　即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有空值

复合索引：即一个索引包含多个列

什么情况下需要建立了索引

主键自动建立唯一索引

频繁作为查询条件的字段应该创建索引

查询中与其他表关联的字段,外键关系建立索引

频繁更新的字段不适合创建索引,因为每次更新不单单更新了记录还会更新索引

where 条件里用不到的字段不会创建索引

单键/组合索引的选择问题,在高并发下选择组合索引

查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度

查询中统计或者分组字段

那些情况下不适合建立索引

表记录太少

经常增删改的字段

数据重复且分布平均的字段

Mysql的常见的瓶颈:

CPU : CPU在饱和的时候一般发生在数据装入内存或重磁盘上读取数据的时候

IO：磁盘IO瓶颈发生在装入数据远大于内存容量的时候

服务器硬件的瓶颈

索引创建的语法以及其他深入知识

创建索引：

CREATE INDEX indexName ON table_name (column_name)

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

 创建表的时候直接指定：

CREATE TABLE mytable(  
 
ID INT NOT NULL,   
 
username VARCHAR(16) NOT NULL,  
 
INDEX [indexName] (username(length))  
 
);  

删除索引的方法： 

DROP INDEX [indexName] ON mytable; 

索引失效问题：

• 全值匹配我最爱

索引  idx_staffs_nameAgePos 建立索引时 以 name ， age ，pos 的顺序建立的。全值匹配表示 按顺序匹配的

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

 

 

• 最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

 and 忽略左右关系。既即使没有没有按顺序 由于优化器的存在，会自动优化。

经过试验结论  建立了 idx_nameAge 索引  id 为主键

    1.当使用覆盖索引的方式时，(select name/age/id from staffs where age=10 (后面没有其他没有索引的字段条件))，即使不是以 name 开头，也会使用 idx_nameAge 索引。

    既 select 后的字段 有索引，where 后的字段也有索引，则无关执行顺序。

    2.除开上述条件 才满足最左前缀法则。

EXPLAIN SELECT * FROM staffs WHERE age = 25 AND pos = 'dev';

EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';

• 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

• 存储引擎不能使用索引中范围条件右边的列

• 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

• mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

• is not null 也无法使用索引,但是is null是可以使用索引的

• like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

• 字符串不加单引号索引失效

• 少用or,用它来连接时会索引失效