索引的使用

索引提升查询效率

/*创建索引*/
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,并没有为这个字创建索引,此时又会发生回表查询。

7) 用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不走索引。

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

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

单列索引与复合索引

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

相当于创建了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 ):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境下运行。

事务并发的问题:

问题 含义
丢失更新(Lost Update ) 当两个或多个事务选择同一行,最初的事务修改的值,会被后面的事务修改的值覆盖。
脏读(Dirty Reads ) 当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个数据,然后使用了这个数据。
不可重复读〔Non-Repeatable Reads ) 一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现和以前读出的数据不一致。
幻读(Phantom Reads ) 一个事务按照相同的查询条件重新读取以前查询过的数据,却发现其他事务插入了满足其查询条件的新教据。

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;

评论