mysql索引的那些事儿

mysql索引

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

什么字段适合加索引 : 条件查询次数比较多,不要频繁更新的字段,值有非常多的字段。

显示索引信息

SHOW INDEX FROM table_name

普通索引

##创建普通索引，如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
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; 

唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

##创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length)) 
## 修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))
## 创建表的时候直接指定
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
);  

常见的添加索引的四种方式：

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): 添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

如何定位并优化慢查询Sql

show variables like '%quer%'

long_query_time：慢日志阈值，超过才会被记录
slow_query_log：打开/关闭慢日志
slow_query_log_file：慢日志记录的位置

set global slow_query_log = on ; 打开慢查询
set long_query_time  = 5    设置慢查询的时间阈值为：5s

show status like '%slow_queries%'   慢查询的数量

使用explain分析sql性能

EXPLAIN 
  select * from  account where name  = ''
	

id标明sql的执行顺序（越大越先执行）
type表示mysql找到数据行的方式:

index/all表示全表扫描，是最慢的，每次看到这两个要特别注意
extra:可以获取到更为详细的信息

规范使用索引：

• 如果条件中加入like的话前面,前面加入%会导致全表扫描.尽量不要使用前导模糊查询。
  如果使用or的话,那么or中所有的条件必须加索引,否则也会全表扫描.
  判断是否null时,使用is null可以使用索引,不要使用=null会导致全表扫描
  使用group by时,不会使用索引,会进行全表扫描,如果要求效率比较高的话,可以进行禁止排序,order by null

• 尽量避免非操作符的使用，在索引使用NOT ,<>,会导致索引失效,比如a不等于0 a<>0可以修改为 a>0 or a<0 ,NOT修改为a>0或者a>"",避免全表扫描.

• 避免不必要的类型转换，这里的类型转换是潜在的类型转换,比如将字符串与数字类型比较,这样会将字符串进行转换,导致全表扫描.

• 少使用*

• 合理使用in与exists

  exists是循环的方式,外表记录数代表循环的次数,外表的记录少,适合用它.

  in先执行子查询,子查询去重之后,然后在执行主查询,子查询返回结果越少,越适合这种方式.

  如果两张表数据一样大,那么用in和exists差别不大,但是如果不一样大,子查询小的用in,主查询小的用exists.

• 规范所有关键字的书写,select ,update,delete,要么全大写要不全小写.

• 联合索引的使用，最好和索引的从左到右的顺序一致，使用等值查询；多列同时查询，索引会一直传递并生效。索引查找遵循最左侧原则。但是遇到范围查询列之后的列索引失效。排序也能使用索引，合理使用索引排序，避免出现file sort。