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 ```