MySQL进阶：索引与优化

索引的使用

索引提升查询效率

/*创建索引*/
create index index_name on t_user(name);
/*删除索引*/
drop index index_name on t_user;

避免索引失效

1) 全值匹配

全值匹配，对索引中所有列都指定具体值。改情况下，索引生效，执行效率高。

explain select * from t_user where name='Himit_ZH' and status='1' and address='广东'\G;

2) 最左前缀法则

联合索引中，在创建索引的顺序中的索引必须左边索引都必要出现，先后顺序无关，但必须出现，不能跳过，例如 name，status，address只能出现以下情况

create index idx_seller_name_sta_addr on t_user(name,status,addresss);

/* 以下都会走索引 */
select * from t_user where name='Himit_ZH'

select * from t_user where name='Himit_ZH' and status='1' 

select * from t_user where name='Himit_ZH' and status='1' and address='广东'\G;

select * from t_user where  status='1' and address='广东' and name='Himit_ZH'\G;

3) 范围查询右边的列，不能使用索引。

// 后面的 address的索引会失效。
select * from t_user where name='Himit_ZH' and status>'1' and address='广东'\G;

4) 索引列上不能使用运算操作，否则索引失效。

// 如此查询，索引将失效。
select * from t_user where substring(name,6,2)='ZH';

5) 字符串不加单引号，索引失效。

// name走索引，但是status不走索引
select * from t_user where name ='Himit_ZH' and status = 1

// name和status都走索引
select * from t_user where name ='Himit_ZH' and status = '1' 

6) 尽量使用覆盖索引，避免使用select *

select * from t_user where name='Himit_ZH'

用到了索引，但是由于是select * 还会到表结构查询相应的行数据。

select name from t_user where name='Himit_ZH'

此时查询的字段name在B+树中已经存了数据，不需要回表查询了。

select name,sex from t_user where name='Himit_ZH'

此时查询的字段多了一个sex，并没有为这个字创建索引，此时又会发生回表查询。

1. 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。（and 会走索引）

例如：name有索引，sex没有索引。

SQL explain select name,sex from t_user where name='Himit_ZH' or sex='男'

进行了全文扫描，并未走索引查询。

8) 使用%模糊匹配时，%出现在模糊字符串前面不走索引，例如%ZH或者%ZH%,但是ZH%只在后面会走索引。

explain select * from t_user where name like '%Himit_ZH%' 

解决方法：使用覆盖索引，即获取的查询字段都有建立索引。(name,address,status)

explain select name,address,status from t_user where name like '%Himit_ZH%';

9) 有可能SQL判断走索引比全文扫描慢，就不会走索引。

10) is NULL或者is not NULL 有时候不走索引，都是MySQL底层会进行字段判断，看执行效率是否需要走索引还是走全文扫描。

11) in走索引，not in不走索引。

```sql
explain select * from t_user where id in (1,2);
```

![在这里插入图片描述](https://img-blog.csdnimg.cn/20200812212809341.png#pic_center)


```sql
explain select * from t_user where id not in (1,2);
```

![在这里插入图片描述](https://img-blog.csdnimg.cn/20200812212823581.png#pic_center)

单列索引与复合索引

尽量使用复合索引，少用单列索引。

相当于创建了name和sex索引，这样无论出现单个name，sex还是同时出现，都走一个索引。
create index idx_name_sex on t_user(name,sex)


这样创建，如果两个索引条件同时出现，MySQL只会走一个最优索引，而不是两个索引。
create index idx_name on t_user(name)
create index idx_sex on t_user(sex)

查看索引的使用情况

show status like 'Hander_read%';

show global status like 'Hander_read%';

SQL优化

explain分析，表中的type是访问类型，效率从高到低如下：

system > const > eq_ref > ref > fulltext > ref_or_null >index_merge > unique_subque> index_subquery>range > index > ALL

优化插入大量数据

1）批量插入数据主键最好有序，这样InnoDB引擎创建B+树索引时效率更快。

load data local infile '文件位置' into table '表名' fields terminated by ',' lines terminated by '\n';

2）关闭唯一性校验

插入数据前关闭唯一性校验

SET UNIQUE CHECKS=0;

插入后重新开启

SET UNIQUE CHECKS=1;

3)手动提交事务

插入数据前关闭自动提交事务

SET AUTOCOMMIT=0;

插入后再开启

SET AUTOCOMMIT=1;

insert语句优化

• 多行数据时使用

insert into t_user values(...),(...),...

• 按主键顺序插入

insert into t_user values(1,..),(2,..),...

order by语句优化

查询的字段有创建索引，也就是覆盖索引时，order by会走索引排序，而非全文排序,例如 age，id有索引。

select age,id from t_user order by age;

但若是select * 会走filesort

select * from t_user order by age;

多字段排序时,必须同升同降，一升一降会导致走filesort，同升同降才会走索引排序。

select age,id from t_user order by age asc,id desc;

group by语句优化

group by默认会使用filesort排序，如果不想排序，再分组后面加上order by null，关掉会提高分组效率。

select age,id from t_user group by age order by null;

嵌套子查询优化

explain select * from t_user where in (select id from user_role);

explain select * from t_user u,user_role ur where u.id=ur.user_id;

可以看到type变成ref，比index效率更高，所以建议使用多表查询。

or条件优化

建议使用union替换or

explain select * from t_user where id =1 or id=10;

explain select * from t_user where id =1 union select * from t_user where id =10;

type的const的效率远高于range。

limit分页优化

1) 先从索引上完成分页操作，再使用关联查询查回所需的其它字段。

explain select * from tb_item limit 200000,10; 

explain select * from tb_item t,(select id from tb_item order by id limit 200000,10) a where t.id=a.id;

2) 仅适用于主键自增的表，不能有断层。(效率超高，总结走索引，但是有前提)

explain select * from tb_item id > 200000 limit 10;

索引提示

USE INDEX 提示建议MySQL使用指定的索引

select * from t_user use index(idx_name) where name = 'Himit_ZH'

IGNORE INDEX 提示忽略使用指定索引

select * from t_user ignore index(idx_id) where name = 'Himit_ZH'

FORCE INDEX 强制使用索引

select * from t_user force index(idx_name) where name = 'Himit_ZH'

查询缓存

1)查看当前MySQL数据库是否支持查询缓存

show variables like 'have_query_cache';

2)查看是否开启了缓存

show variables like 'query_cache_type';

3)查看查询缓存的占用大小(16M)

show variables like 'query_cache_size';

修改查询缓存大小

在MySQL配置文件my.conf 添加一行query_cache_size=×××

4)开启查询缓存

在MySQL配置文件my.conf 添加一行query_cache_type=1

5)查询缓存失效的情况 - 完全相同的语句包括大小写必须一致，若是前后查询语句不同，则缓存失效。 - select语句有不确定的查询参数时，不会走缓存，例如now(),current_date().....，user()..... - 没有进行表查询。例如 select 'A'； - 查询系统数据库时不走查询缓存（mysql，information_schema,performance_schema数据库的表） - 进行对表增删改的操作时，与该表关联的查询语句缓存会失效。

内存优化

• MyISAM内存优化

my.cnf配置key_buffer_size = ...索引块缓冲区大小

• innoDB内存缓存

innodb_buffer_pool_size=... 表数据和索引数据的缓存池大小

innodb_log_buffer_size=... 重做日志缓存大小，避免磁盘读写。

并发参数调整

如果需要数据库在较短的时间内处理大量连接请求，可以考虑适当增大back_log 的值。从实现上来说，MySQL Server是多线程结构，包括后台线程和客户服务线程。多线程可以有效利用服务器资源，提高数据库的并发性能.

1) max_connections

采用max_connections控制允许连接到MySQL数据库的最大数量，默认值是151。如果状态变量connection_errors_max_connections不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections的值。 MySQL最大可支持的连接数，取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度，期望的响应时间等。在Linux平台下，性能好的服务器，支持500-1000个连接不是难事，需要根据服务器性能进行评估设定。

2) back_log

back_log参数控制MSQL监听TCP端口时设置的积压请求栈大小。如果MySq]的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_Jog，如果等待连接的数量超过back_Jog，将不被授予连接资源，将会报错。5.6.6版本之前默认值为50，之后的版本默认为50+ ( max_connections/5），但最大不超过900。 如果需要数据库在较短的时间内处理大量连接请求，可以考虑适当增大back_log的值。|

3) table_open_cache

该参数用来控制所有SQL语句执行线程可打开表缓存的数量，而在执行SQL语句时，每一个5QL执行线程至少要打开1个表缓存。该参数的值应该根据设置的最大连接数max_connections以及每个连接执行关联查询中涉及的表的最大数量来设定︰ max_connections x N ;

4) thread_cache_size

为了加快连接数据库的速度，MySQL会缓存一定数量的客户服务线程以备重用，通过参数thread_cache_size可控制MySQL缓存客户服务线程的数量。

5) innodb_lock_wait_timeout

该参数是用来设置InnoDB事务等待行锁的时间，默认值是50ms，可以根据需要进行动态设置。对于需要快速反馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起;对于后台运行的批量处理程序来说，可以将行锁的等待时间调大，以避免发生大的回滚操作。

innoDB 行锁

ACID属性：

• 原子性(Atomicity )：事务是一个原子操作单元，其对数据的修改，要么全部成功，要么全部失败。
• 一致性(Consistent )：在事务开始和完成时，数据都必须保持一致状态。
• 隔离性(lsolation )：事务完成之后，对于数据的修改是永久的。
• 持久性(Durable )：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境下运行。

事务并发的问题：

InnoDB实现了以下两种类型的行锁。

• 共享锁(S）∶又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
• 排他锁(X）∶又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获 取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁(X); 对于普通SELECT语句，innoDB不会加任何锁;

手动加锁

共享锁(S）: SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁(x): SELECT * FROM table_name WHERE ... FOR UPDATE

• 无索引（索引失效）的操作会升级行锁成表锁，此次操作事务不提交，他人无法进行更新操作。
• 间隙锁：当我们用范围条件，而不是使用相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据进行加锁;对于键值在条件范围内但并不存在的记录，叫做"间隙(GAP )"，InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的间隙锁(NextKey锁)。

主从复制

master节点

1) 在my.cnf配置以下内容：

#mysql服务ID，保证整个集群环境中唯一
server-id=1

#mysql binlog日志的存储路径和文件名
log-bin=/var/lib/mysq1/mysqlbin

#错误日志，默认已经开启
#log-err

#mysql的安装目录
#basedir

#mysq1的临时目录
#tmpdir

#mysq1的数据存放目录
#datadir

#是否只读，1代表只读，0代表读写
read-only=0

#忽略的数据指不需要同步的数据库
binlog-ignore-db=mysq1

#指定同步的数据库
#binlog-do-db=db01

2) 重启MySQL

service mysql restart

3) 进入数据库，创建同步数据的账户，并且进行授权操作

*.*所有的数据库所有的表，账号：`itcast'@"192.168.192.1' 从节点的地址 identified by 为创建密码

grant replication slave on *.* to 'itcast'@"192.168.192.2' identified by '123456';

flush privileges;

4) 查看master的状态

show master status;

• File：应该读取哪个日志文件开始推送日志文件
• Postition：文件哪个位置开始推送日志
• Binlog_Ignore_DB：不需要同步的数据库

slave节点

1) my.conf中配置以下内容：

#mysql服务端ID，唯一
server-id=2

#指定binlog日志
log-bin=/var/1ib/mysq1/mysqlbin

2) 重启MySQL

service mysql restart

3) 执行以下指令

指定当前从库对应的主库的IP地址，用户名，密码，从哪个日志文件开始的那个位置开始同步推送日志。

change master to master_host='192.168.192.1',master_user='itcast',master_password='123456',master_log_file="mysqlbin.000001",master_log_pos=413;

4) 开启同步

start slave;

show slave status;

5) 停止同步

stop slave;