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，并没有为这个字创建索引，此时又会发生回表查询。
- 用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);
```
```sql
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=db012) 重启MySQL
service mysql restart3) 进入数据库，创建同步数据的账户，并且进行授权操作
*.*所有的数据库所有的表，账号：`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/mysqlbin2) 重启MySQL
service mysql restart3) 执行以下指令
指定当前从库对应的主库的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;腾讯云开发者
