MySql索引管理
# 索引管理
**什么是MySQL索引?**
**相当于一本书中的目录,可以加速查询(select,update,delete),但会影响插入的速度。**
## 一. MySQL读取数据痛点
```bsh
当MySQL中数据越来越多的时候,读取需要的数据的效率是越来越低的,
那么,就需要MySQL的索引功能来提高读取数据效率。
[索引]是帮助MySQL高效获取数据的最重要的[数据结构],
也就是说通过索引功能,可以高效的读取MySQL数据。
```
## 二. MySQL索引种类
```bsh
Btree :平衡多叉树,底层是二分法查找(Shell脚本写过么)
优点:擅长范围查找,快速锁定数据范围,innodb采用btree索引。
HASH:内存散列索引,内存memory引擎用。
优点:比较适合随机的等值查询。
Rtree:空间索引,地理位置,用于索引多维数据的专用数据结构
Full-Text:用于全文检索
```
## 三. Btree种类细分
```bsh
聚簇(区)索引:
主键索引(ID,学号),往往建表的时候就自带,例如关键字primary key.
辅助索引: 工作中最重要,人为对SQL语句优化的关键手段。
单列索引:在一个字段或列上建立的索引。
联合索引:在多列上联合创建索引,用于where后加多个条件时. 例如:where name='oldboy' and age=24;
唯一索引:列的内容唯一。
前缀索引:对列内容的一部分建立索引。
```
## 四.索引如何才能加快查询?
```bsh
1.在where后面的条件以及分组列上建立索引,才能加速查询。
2.生产场景,在[唯一值多]的列上建立索引,才能加速查询。
例子:
select age from oldboy.stu. where name='oldgirl';
这个语句要在name列建立索引,才能加速查询。
```
## 五. 索引的管理
```bah
导入一个百万记录表:
mysql> source ~/t100w_oldboy.sql
mysql> select count(*) from t100w;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
mysql> desc t100w;
+-------+----------
| Field | Type
+-------+----------
| id | int
| num | int
| k1 | char(2)
| k2 | char(4)
| dt | timestamp
+-------+----------
```
### 1 查询索引
##### desc world.city
```bsh
mysql> desc world.city;
PRI : 主键索引(聚簇索引) ,列的值唯一,往往是数字序列。
MUL : 普通索引(辅助索引) ,列值不唯一。
UNI : 唯一索引(辅助索引) ,列值唯一,不一定是数字序列。
```
##### 在什么样的列上建立索引才会加速查询?
```bsh
1.在where后面的条件以及分组列上建立索引,才能加速查询。
2.生产场景,在[唯一值多]的列上建立索引,才能加速查询。
例:性别列,M,F,N,10万行记录,唯一值少就3个,重复值多,这样的列建立索引效率很低。
id列,是主键列,效率最佳。
name,telnmu,qq
```
##### 如何确定一个列唯一值多还是少?
```bsh
show index from world.city;
Table 表名
Key_name 索引名
Column_name 列名
Cardinality 基数(选择度),唯一值的多少就看这列的值。
mysql> show index from world.city;
+-------+------------+-------------+-------------+-------------+------------+---------+------------+
| Table | Non_unique | Key_name | Column_name | Cardinality | Index_type | Visible | Expression |
+-------+------------+-------------+-------------+-------------+------------+---------+------------+
| city | 0 | PRIMARY | ID | 4188 | BTREE | YES | NULL |
| city | 1 | CountryCode | CountryCode | 232 | BTREE | YES | NULL |
+-------+------------+-------------+-------------+-------------+------------+---------+------------+
2 rows in set (0.00 sec)
mysql> select count(*) from world.city;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
1 row in set (0.06 sec)
列上建立索引的参考条件:
唯一值数量/表记录总数>20%是建立索引的底线条件,
如果低于20%,建立索引的效率很低,越大越好,最好超过30%。
```
```bsh
为什么某个列对应的值大于表的总行?
1.因为这个列的值是估算的。
取10%记录,检查唯一值,此时90%
估计100%的唯一值是多少
2.mysql> analyze table world.city;更新预估值。
https://blog.csdn.net/weigeshikebi/article/details/80214965
```
##### 如何查看列的唯一值? 如何找到唯一值多的列?
```bsh
如何查看列的唯一值:distinct
mysql> select count(distinct countrycode) from world.city;
+-----------------------------+
| count(distinct countrycode) |
+-----------------------------+
| 232 |
+-----------------------------+
如何找到唯一值多的列:show index from city;
唯一值占比20%以上,可以建立索引。
实践:
1)查看countrycode列唯一值
mysql> select count(distinct countrycode) from city;
+-----------------------------+
| count(distinct countrycode) |
+-----------------------------+
| 232 |
+-----------------------------+
2)查列总行数
mysql> select count(*) from city;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
1 row in set (0.05 sec)
3)做除法看是否大于20%
mysql> select 232/4079;
+----------+
| 232/4079 |
+----------+
| 0.0569 | 5.69%小于20%,得出结论,不适合在此列建立索引.
+----------+
特殊情况:where a= and b> and c !=
1.a列大多数唯一,后面b,c可以不建索引
2.多条件优先考虑建立联合索引key(a,b,c)
3.千万不要每个列都建立索引 *****
```
```bsh
导致SQL语句慢的尽可能都记录在案。
slow_query_log = ON #<==慢查询开启开关
long_query_time = 1 #<==记录大于1秒的SQL语句。
log_queries_not_using_indexes = ON #<==没有使用到索引的SQL语句。
slow-query-log-file = /data/3306/data/slow.log #<==记录SQL语句的文件。
min_examined_row_limit = 800 #<==记录结果集大于800行的SQL语句。
=================================
慢查询记录语句的条件。
1.记录执行时间大于1秒的SQL语句。
2.没使用索引的。
3.返回结果集大于800的。
=================================
优化器算法会判断:即时建立索引,但是执行该语句不一定使用索引,use index
```
### 2 创建索引
##### 1)单列索引
```bsh
建表的时候使用SQL语句同步建立索引
KEY i_name(name), #未雨绸缪。
key 索引名(列)
命令:
1. alter table 表名 add index 索引名(列);
alter table 表名 drop index 索引名; # 删除索引
2. create index 索引名 on city(name);
```
##### 2)联合(复合)索引
```bsh
建表:key(a,b,c)
条件:where district> and population=
建表:
命令:alter table city add index i_dis_pop(district,population);
重点面试题:联合索引有哪些特性?
key(a,b,c) #给a,b,c建立联合索引。
查询条件包含:
a ab abc #可以用到索引
ac bc b c #用不到索引
```
##### 3)前缀索引
```bsh
条件:
列太长了varchar(255),占用空间,
对列的前n个字符建立索引(前n个字符建立的索引,是整列的90%+)
255字符,取前10个字符,唯一值达到了90%
取前10个字符查唯一值的数量,如果接近整列唯一值的数量(达到整列唯一的90%),此时可以取前10个字符建立索引.
select count(distinct name) from city;
select count(distinct left(name,10)) from city;
命令:
alter table city add index i_x(name(10));
建表的时候建立前缀索引
KEY i_x(name(10)), #<==前10个字符做前缀索引。
KEY `IDX_SUBJECT_TITLE` (`subject_title`(32)) , #<==前32个字符做前缀索引。
```
##### 4)联合索引+前缀索引
```bsh
建表:
KEY `index_nick_type` (`edit_user_nick`(32),`subject_type`)
命令:
alter table city add index i_n_d(name(10),district);
```
##### 5) 唯一索引(要求列值唯一)unique
```bsh
alter table t1 add unique index i_a(a);
```
### 3 删除索引
```bsh
alter table city drop index i_name;
```
### 4 索引建立之前压测
```bsh
模拟数据压测:
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from t100w where k2='CDno'" engine=innodb \
--number-of-queries=200 -uroot -poldboy123 -verbose
没有创建索引执行:
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=100 --iterations=1 --create-schema='oldboy' \
> --query="select * from t100w where k2='CDno'" engine=innodb \
> --number-of-queries=200 -uroot -poldboy123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 180.502 seconds
Minimum number of seconds to run all queries: 180.502 seconds
Maximum number of seconds to run all queries: 180.502 seconds
Number of clients running queries: 100
Average number of queries per client: 2
####
select * from t100w where k2='CDno';
1)在合适的列上建立索引.
select * from t100w where k2='CDno';
要在k2列创建索引.
注意:大表上建立索引会影响业务运行,老男孩案例oracle生产库,很多访问,
500万条记录,alter 90秒. 超过10万记录,尽量业务低谷建立索引,
2)开始创建
创建索引和不建索引的区别:10倍以上甚至百倍的速度区别。
mysql> alter table t100w add index i_k2(k2);
Query OK, 0 rows affected (4.92 sec) # 5秒完成
Records: 0 Duplicates: 0 Warnings: 0
3)重新压测
结果:select * from t100w where k2='CDno' 100个同时执行,执行200次. 255秒.
[root@db01 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='oldboy' --query="select * from t100w where k2='CDno'" engine=innodb --number-of-queries=200 -uroot -poldboy123 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 1.874 seconds
Minimum number of seconds to run all queries: 1.874 seconds
Maximum number of seconds to run all queries: 1.874 seconds
Number of clients running queries: 100
Average number of queries per client: 2
结果是1.8秒,原来188秒接近100倍效率提高.
创建索引,重新压测,对比。10倍以上甚至百倍的速度。
4)结论:创建有效索引和不建索引:
会有10倍以上甚至百倍的速度区别。
```
```bsh
类似工作场景:
mysql> show processlist;
+-----+-----------------+-----------+--------+---------+--------+------------------------+-------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------------+-----------+--------+---------+--------+------------------------+-------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 167611 | Waiting on empty queue | NULL |
| 8 | root | localhost | oldboy | Sleep | 147312 | | NULL |
| 9 | root | localhost | oldboy | Query | 0 | init | show processlist |
| 616 | root | localhost | NULL | Sleep | 41 | | NULL |
| 617 | root | localhost | oldboy | Query | 41 | executing | select * from t100w where k2='CDno' |
| 618 | root | localhost | oldboy | Query | 41 | executing | select * from t100w where k2='CDno' |
| 619 | root | localhost | oldboy | Query | 41 | executing | select * from t100w where k2='CDno' |
| 620 | root | localhost | oldboy | Query | 41 | executing | select * from t100w where k2='CDno' |
| 621 | root | localhost | oldboy | Query | 41 | executing | select * from t100w where k2='CDno' |
| 622 | root | localhost | oldboy | Query | 41 | executing | select * from t100w where k2='CDno' |
| 623 | root | localhost | oldboy | Query | 41 | executing | select * from t100w where k2='CDno' |
| 624 | root | localhost | oldboy | Query | 41 | executing | select * from t100w where k2='CDno' |
| 625 | root | localhost | oldboy | Query | 40 | executing | select * from t100w where k2='CDno' |
```
```bsh
企业故障案例:面试题:mysql Sleep线程过多?怎么解决?
1)什么是sleep线程?
sleep线程长时间保持可客户端与服务端的连接状态
2)导致sleep过多的原因:
1.使用太多持久连接(高并发系统中 不适合使用持久连接),c3p0连接池技术。
2.程序中 没有及时关闭链接。
3.数据库优化不完善 导致执行sql语句过慢
3)解决方法: 这两个参数给它调小点,默认值是28800秒
1、vim /etc/my.cnf 配置文件里进行配置 下次需从启服务器的时候直接生效
想当时就生效利用直接在数据库里设置
interactive_timeout = 120 #交互超时参数 #<==此参数设置后wait_timeout自动生效。
wait_timeout = 120 #设置MySQL的睡眠连接秒数 系统默认是8小时
=================================================
set global wait_timeout = 120 全局生效
set global interactive_timeout = 120
2、和开发沟通,查看代码中没有关闭的连接及时关闭.
3、及时优化慢查询语句
```
### 5 MySQL8.0版新特性(面试题)
```bsh
{VISIBLE | INVISIBLE}
{可见索引 | 不可见索引}
生产索引可能不用,不确定删除后会不会导致业务变慢,这个时候可以暂时隐藏起来,业务不可见。
例子:
alter table world.city add index i_name(name);
alter table world.citycity alter index i_name invisible;
索引的查询方法:
方法1:
show index from world.city;
方法2:(了解)
SELECT INDEX_NAME, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'world' AND table_name='city';
+-------------+------------+
| INDEX_NAME | IS_VISIBLE |
+-------------+------------+
| CountryCode | YES |
| index_name | NO | #<==对于SQL执行不可见.
| PRIMARY | YES |
+-------------+------------+
一段时间后不影响业务,在考虑删除掉.
alter table city drop index i_name;
mysqlslap压测命令选项:
mysqlslap:来自于mariadb包,测试的过程默认生成一个mysqlslap的schema,生成测试表t1,查询和插入测试数据,mysqlslap库自动生成,如果已经存在则先删除。用–only-print来打印实际的测试过程,整个测试完成后不会在数据库中留下痕迹 使用格式:
mysqlslap [options]
常用参数 [options] 说明:
--auto-generate-sql, -a #自动生成测试表和数据,表示用mysqlslap工具自己生成的SQL脚本来测试并发压力
--auto-generate-sql-load-type=type #测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括:read,key,write,update和mixed(默认)
--auto-generate-sql-add-auto-increment #代表对生成的表自动添加auto_increment列,从5.1.18版本开始支持
--number-char-cols=N, -x N #自动生成的测试表中包含多少个字符类型的列,默认1
--number-int-cols=N, -y N #自动生成的测试表中包含多少个数字类型的列,默认1
--number-of-queries=N #总的测试查询次数(并发客户数×每客户查询次数)
--query=name,-q #使用自定义脚本执行测试,例如可以调用自定义的存储过程或者sql语句来执行测试
--create-schema #代表自定义的测试库名称,测试的schema
--commint=N #多少条DML后提交一次
--compress, -C #如服务器和客户端都支持压缩,则压缩信息
--concurrency=N, -c N #表示并发量,即模拟多少个客户端同时执行select。可指定多个值,以逗号或者--delimiter参数指定值做为分隔符,如:--concurrency=100,200,500
--engine=engine_name, -e engine_name #代表要测试的引擎,可以有多个,用分隔符隔开。例如:--engines=myisam,innodb
--iterations=N, -i N #测试执行的迭代次数,代表要在不同并发环境下,各自运行测试多少次
--only-print #只打印测试语句而不实际执行。
--detach=N #执行N条语句后断开重连
--debug-info, -T #打印内存和CPU的相关信息
mysqlslap示例
#单线程测试
mysqlslap -a -uroot -poldboy123
#多线程测试。使用--concurrency来模拟并发连接
mysqlslap -a -c 100 -uroot -poldboy123
#迭代测试。用于需要多次执行测试得到平均值
mysqlslap -a -i 10 -uroot -poldboy123
mysqlslap ---auto-generate-sql-add-autoincrement -a
mysqlslap -a --auto-generate-sql-load-type=read
mysqlslap -a --auto-generate-secondary-indexes=3
mysqlslap -a --auto-generate-sql-write-number=1000
mysqlslap --create-schema world -q "select count(*) from City”
mysqlslap -a -e innodb -uroot -poldboy123
mysqlslap -a --number-of-queries=10 -uroot -poldboy123
#测试同时不同的存储引擎的性能进行对比
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -poldboy123
#执行一次测试,分别50和100个并发,执行1000次总查询
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -poldboy123
#50和100个并发分别得到一次测试结果(Benchmark),并发数越多,执行完所有查询的时间越长。为了准确起见,可以多迭代测试几次
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -poldboy123
使用的命令:
mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=100 --iterations=1 --create-schema='oldboy' \
--query="select * from stu where sname='OLDBOY'" engine=innodb \
--number-of-queries=20 -uroot -poldboy123 -verbose
```