Linux之Mysql索引和优化

一、MySQL 索引

索引作为一种数据结构，其用途是用于提升数据的检索效率。

1、索引分类

- 普通索引（INDEX）：索引列值可重复
- 唯一索引（UNIQUE）：索引列值必须唯一，可以为NULL
- 主键索引（PRIMARY KEY）：索引列值必须唯一，不能为NULL，一个表只能有一个主键索引
- 全文索引（FULL TEXT）：给每个字段创建索引

2、创建索引

1、普通索引（INDEX）
（1）在创建表时指定
mysql> create table student1(
    id int not null, 
    name varchar(100) not null,
    birthdy date, sex char(1) not null, 
    index nameindex (name(50)));​
（2）基于表结构创建
mysql> CREATE INDEX nameindex on student1(name(50));
（3）修改表结构创建​
mysql> ALTER TABLE student1 ADD INDEX nameIndex(name(50));
2、唯一索引（UNIQUE INDEX）（语法同普通索引）
（1）在创建表时指定 
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    UNIQUE INDEX index_name (column1, column2, ...));
（2）基于表结构创建（基于已存在的表） 
CREATE UNIQUE INDEX index_name ON table_name(column1, column2, ...);
（3）修改表结构创建​ 
ALTER TABLE table_name ADD UNIQUE INDEX index_name(column1, column2, ...);
3、全文索引（FULLTEXT INDEX）（（语法同普通索引））
（1）在创建表时指定
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    FULLTEXT INDEX index_name (column1, column2, ...));
（2）基于表结构创建
CREATE FULLTEXT INDEX index_name ON table_name(column1, column2, ...);
（3）修改表结构创建​
ALTER TABLE table_name ADD FULLTEXT INDEX index_name(column1, column2, ...);

3、删除索引

删除索引需要相应的权限，通常是ALTER权限。

mysql> DROP INDEX index_name ON table_name; ​         ​​​​​​​# 直接删除 
mysql> ALTER TABLE table_name DROP INDEX index_name; # 修改表结构删除 

4、查看索引

（1）查看表的索引，返回一个结果集，包含表的所有索引信息，包括索引名称、索引类型、索引的列名等
mysql> SHOW INDEX FROM table_name;
（2）查看表的创建语句，返回表的创建语句，其中包含索引的定义
mysql> SHOW CREATE TABLE table_name;
（3）使用INFORMATION_SCHEMA.STATISTICS表查看表的索引信息，返回包含表的索引信息的结果集，包括索引名称、索引类型、索引的列名等。
mysql> SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';

面试题

index(key)每张表可以有很多列做index，必须对索引起名。

导致SQL执行慢的原因（出现慢查询） 

1.硬件问题。如网络速度慢，内存不足，I/O吞吐量小（I/O负载大），磁盘空间满了等。
2.没有索引或者索引失效.
3.数据过多
​
#索引：当查询出现速度过慢可以通过建立优化查询速度，可以当作调优

索引失效的原因

1.数据类型不匹配：如果查询中使用了索引列但数据类型不匹配，MySQL将无法有效使用索引。例如，将字符串类型的列与数值类型进行比较，或将日期类型的列与文本进行比较
2.数据更新频繁：索引是为了提高查询性能而创建的，但在数据更新频繁的情况下，索引会
导致插入、更新和删除操作的性能下降
3.索引中断、模糊查询​​​​​​

binlog日志的格式

查看binlog日志的格式

mysql> show variables like "%binlog_format%"; 

三种格式 

默认格式：在mysql5.7.7之前，默认格式是statement，5.7.7之后是row格式。
binlog日志有三种模式：
1.Row Level（行模式）
日志中会记录每一行被修改的数据，然后在slave端再对相同的数据进行修改
优点：在row