mysql数据库优化课程---18、mysql服务器优化（mysql存在索引但是不使用索引的情况有哪些？）...

你的名字 2022-01-06 22:59 271阅读 0赞

# mysql数据库优化课程---18、mysql服务器优化（mysql存在索引但是不使用索引的情况有哪些？） #

## 一、总结 ##

### 一句话总结： ###

##### 都会取到：如果MySQL估计使用索引比全表扫描更慢，则不使用索引。 #####

##### MEMORY/HEAP表 + where条件中不使用“=”进行索引列：如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引列，那么不会用到索引。Heap表只有在“=”的条件下会使用索引。 #####

##### or：用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。 #####

(1)如果MySQL估计使用索引比全表扫描更慢，则不使用索引。例如如果

列key\_part1均匀分布在1到100之间，查询时使用索引就不是很好

也就是比如取1-100条，因为这100条都会被取到，所以用不用索引没有什么意义

(2)如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引

列，那么不会用到索引。Heap表只有在“=”的条件下会使用索引。

(3)用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没

有索引，那么涉及的索引都不会被用到。

mysql>show index from sales\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
… …  
key\_name: ind\_sales\_year  
seq\_in\_index：1  
Column\_name: year  
… …

### 2、数据类型：如果列类型是字符串，但在查询时把一个数值型常量赋值给了一个字符型的列名name，索引会用上么？ ###

##### 不会：那么虽然在name列上有索引，但是也没有用上，所以要注意查询时候的数据类型 #####

那么虽然在name列上有索引，但是也没有用上，所以要注意查询时候的数据类型

sql>。 explain select \* from company2 where name  
name=294\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
id: 1  
select\_type: SIMPLE  
table: company2  
type: ALL  
possible\_keys: ind\_company2\_name  
key: NULL  
key\_len: NULL  
ref: NULL  
rows: 1000  
Extra: Using where  
1 row in set (0.00 sec)

字段和值类型不同:  
\#desc select \* from user where username=200\\G  
当条件中字段和值的类型不同时，该字段的索引可能会失效.

### 3、如何查看索引的使用情况？ ###

##### show status：show status like 'Handler\_read%'; #####

##### Handler\_read\_rnd\_next：Handler\_read\_rnd\_next的值高则意味着查询运行低效，并且应该建立索引补救。 #####

##### Handler\_read\_key：如果索引正在工作，Handler\_read\_key的值将很高，这个值代表了一个行被索引值读的次数。 #####

如果索引正在工作，Handler\_read\_key的值将很高，  
这个值代表了一个行被索引值读的次数。  
• Handler\_read\_rnd\_next的值高则意味着查询运行低效  
，并且应该建立索引补救。  
mysql> show status like 'Handler\_read%';  
\+-----------------------+-------+  
| Variable\_name | Value |  
\+-----------------------+-------+  
| Handler\_read\_first | 0 |  
| Handler\_read\_key | 5 |  
| Handler\_read\_next | 0 |  
| Handler\_read\_prev | 0 |  
| Handler\_read\_rnd | 0 |  
| Handler\_read\_rnd\_next | 2055 |  
\+-----------------------+-------+  
6 rows in set (0.00 sec)

### 4、如何解决嵌套查询索引可能失效的问题？ ###

##### 链接查询：可以使用更有效的链接查询（Join）替代 #####

可以使用更有效的链接查询（Join）替代

普通多表查询索引也用的上

mysql5.1版本嵌套查询索引可能失效，5.7版本优化了，好像不会失效了

### 5、多表查询有哪些？ ###

##### 嵌套查询 #####

##### 普通多表查询 #####

##### 链接查询 #####

### 6、mysql5.7对多表查询的支持情况怎样？ ###

##### 索引优化：目前5.7的版本中常用的多表查询都进行索引优化. #####

\#desc select \* from t1 where class\_id in(select id from class)\\G  
\#desc select t1.\* from t1,class where t1.class\_id=class.id\\G  
\#desc select t1.\* from class left join t1 on t1.class\_id=class.id\\G  
目前5.7的版本中常用的多表查询都进行索引优化.

### 7、mysql root密码如何破解？ ###

##### 密码没忘记情况：set password：set password=password('123'); #####

##### 密码忘记了的情况：1)关闭mysql服务；2)越过user表启动mysql；3)关闭win下任务管理器中mysqld进程 #####

1.密码没有忘，但我要改密码  
set password=password('123');  
  
2.密码忘记了，我要破解密码  
1)关闭mysql服务  
net stop mysql57  
  
2)越过user表启动mysql  
a.越过授权表启动mysql母进程  
cd AppServ\\MySQL\\bin  
mysqld.exe --skip-grant-tables &  
  
b.无密码登录  
mysql -uroot  
  
c.刷新管理员权限  
flush privileges;  
  
d.有权限后改密码  
alter user 'root'@'localhost' identified by '789';  
  
3)关闭win下任务管理器中mysqld进程  
  
4)重新启动mysql服务  
net start mysql57

## 二、内容在总结中 ##

check表检查:  
mysql> check table v\_user;  
\---------------------------------------------------------------------------+  
| yzmedu.v\_user | check | Error    | Table 'yzmedu.class' doesn't exist  
                                                                           |  
| yzmedu.v\_user | check | Error    | View 'yzmedu.v\_user' references invalid table(s) or c  
olumn(s) or function(s) or definer/invoker of view lack rights to use them |  
| yzmedu.v\_user | check | error    | Corrupt  
                                                                           |  
\---------------------------------------------------------------------------+  
  
索引的存储分类:  
1.myisam  
1)frm 表结构  
2)myd 表数据  
3)myi 表索引  
  
2.innodb  
1)frm 表结构  
2)ibd 索引+部分表数据  
3)ibdata1 所有表共享空间  
  
复合索引:  
对于创建的多列索引，只要查询的条件中用到最左边的列，索引一般就会被使用.  
  
like关键字:  
\#desc select \* from user where username like 'linux%'\\G  
当使用like进行搜索时，%在前索引可能会失效.  
  
null判断:  
\#desc select \* from user where username is null;  
当判断null值时会使用username这一列的索引.  
  
or关键字:  
\#desc select \* from user where username='user7' or age=15\\G  
在使用or的情况下两边的索引都有可能失效.  
  
字段和值类型不同:  
\#desc select \* from user where username=200\\G  
当条件中字段和值的类型不同时，该字段的索引可能会失效.  
  
查看索引的使用频次:  
mysql> show status like "%Handler\_read%";  
\+-----------------------+-------+  
| Variable\_name         | Value |  
\+-----------------------+-------+  
| Handler\_read\_key      | 42    |    \#越大越好  
| Handler\_read\_rnd\_next | 306   |    \#越小越好  
\+-----------------------+-------+  
  
多表查询索引使用:  
\#desc select \* from t1 where class\_id in(select id from class)\\G  
\#desc select t1.\* from t1,class where t1.class\_id=class.id\\G  
\#desc select t1.\* from class left join t1 on t1.class\_id=class.id\\G  
目前5.7的版本中常用的多表查询都进行索引优化.  
  
Mysql服务器优化:  
1.四种字符集问题  
2.slow log慢查询日志问题  
3.root密码丢失  
  
mysql root密码破解:  
1.密码没有忘，但我要改密码  
set password=password('123');  
  
2.密码忘记了，我要破解密码  
1)关闭mysql服务  
net stop mysql57  
  
2)越过user表启动mysql  
a.越过授权表启动mysql母进程  
cd AppServ\\MySQL\\bin  
mysqld.exe --skip-grant-tables &  
  
b.无密码登录  
mysql -uroot  
  
c.刷新管理员权限  
flush privileges;  
  
d.有权限后改密码  
alter user 'root'@'localhost' identified by '789';  
  
3)关闭win下任务管理器中mysqld进程  
  
4)重新启动mysql服务  
net start mysql57

转载于:https://www.cnblogs.com/Renyi-Fan/p/9843821.html