INDEX索引
优点:索引可以降低服务需要扫描的数据量,减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序 I/O
缺点:占用额外空间,影响插入速度
顺序排序,插入新记,会改变顺序,频繁插入数据,会影响性能,不适合大量写建索引
B+Tree索引(按顺序存储,每一个叶子节点到根节点的距离是相同的,左前缀索引,设hi查询范围类的数据)
根节点和分支节点不放完整数据,只放索引字段和指针,完整数据放在叶子节点中,且叶子节点之间有关联
B+Tree索引结构
根节点 分支节点 叶子节点
InnoDB中一颗的B+Tree可以存放多少行数据?
单个叶子记录的行数.这里先计算叶子节点,B+Tree中的单个叶子节点的大小为16K,假设每一条目为1K,那么记录数即为16(16k/1K=16),然后计算非叶子节点能够存放多少个指针,假设主键ID为bigint类型,那么长度为8字节,而指针大小在InnoDB中是设置为6个字节,这样加起来一共是14个字节.那么通过页大小/(主键ID大小+指针大小),
即16*1024/(8+6)
=16384/14
=1170个指针,所以一颗高度为2的B+Tree能存放16*1170=18720条这样的 记录.根据这个原理就可以算出一颗高度为3的B+树可以存放16*1170*1170=21902400条记录.所以在 InnoDB中B+树高度一般为2-3层,它就能满足千万级的数据存储
索引类型
B+Tree、HASH
聚簇索引、非聚簇索引
主键索引、二级索引
稠密索引,稀疏索引:是否索引了每一个数据项
简单索引,组合索引: 是否是多个字段的索引
左前缀索引:取前面的字符做索引
覆盖索引:从索引中即可取出要查询的数据,性能高
聚簇索引:数据和索引放在一起
如InnoDB
frm文件放表的源数据,idb文件里面存放数据和索引
非聚簇索引:数据和索引不放一起。
如MyISAM的三个文件
frm文件放表的源数据,如表结构、源数据等,MYI文件放索引,MYD文件放数据。
主键索引和二级索引:
如学生id设置主键索引,学生姓名按照字母顺序设置索引。以主键索引为首,然后再按照二级索引进行排序,不会影响主键
左前缀索引:取前面的字符做索引。 例如:w%是可以的 %w和%w%是不行的(这是左模糊索引、全模糊索引)
组合索引:firstname,lastname --> index
1、select * from students where firstname='wang' and lastname like 'x%'
可以利用索引,先对firstname进行排序,firstname一样再对lastname进行排序
2、select * from students where lastname like 'x%'
不能利用索引,因为lastname的排序是建立在firstname相同的情况下才可以对lastname进行排序
问这个SQL语句能不能利用索引,左模糊索引、全模糊索引没办法,右模糊索引可以 就是字符加%
管理索引
创建索引:
create [unique] index index_name on tb1_name (index_col_name[(length)],...);
unique可省略,唯一索引。 index_name 索引名。 tb1_name 表名 。
(index_col_name[(length)],...); index_col_name 字段 。 [(length)] 取前多少个字符作为索引
删除索引:drop index index_name on tb1_name;
查看索引:show index from tb1_name;
EXPLAIN工具(可以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息,用来查看查询优化器如何执行查询)
possible_keys; 查询可能会用到的索引
key 显示MySQL决定采用哪个索引来优化查询
如:explain select * from students where stuid=10;
其中possible_keys 对应 PRIMARY #可能会用到的索引为PRIMARY
key 对应 PRIMARY #最终用到的索引为PRIMARY
脚本生成10万条记录:
cat source testlog.sql
create table testlog (id int auto_increment primary key,name char(10),salary int default 20); #创建表,id字段定义自动增长,并且为主键;name字段, char(10); salary默认20
delimiter $$
#定义存储过程
create procedure sp_testlog()
begin
declare i int; #变量为整数
set i = 1; #初始值1
while i
MariaDB在索引上更优化,根据情况使用或者不使用索引,但mysql只要建立好索引,任何情况下都用索引
比如说表很小,搜索的数据占的百分比高, MariaDB不用索引直接全局扫描更快。
type显示的是访问类型,是较为重要的一个指标
掌握以下10种常见的即可 NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>AL
All 最坏的情况,全表扫描
index 和全表扫描一样.只是扫描表的时候按照索引次序进行而不是行.主要优点就是避免了排序,但是开销仍然非常大.如在Extra列看到Using index,说明正在使用覆盖索引,只扫描索引的数据,它比按索引次序全表扫描的开销要小很多
range 范围扫描,一个有限制的索引扫描.key 列显示使用了哪个索引.当使用=, ,>, >=,,BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用range
ref 一种索引访问,它返回所有匹配某个单个值的行.此类索引访问只有当使用非唯一性索 引或唯一性索引非唯一性前缀时才会发生.这个类型跟eq_ref不同的是,它用在关联操 作只使用了索引的最左前缀,或者索引不是UNIQUE和PRIMARY KEY.ref可以用于使 用=或操作符的带索引的列.
eq_ref 最多只返回一条符合条件的记录.使用唯一性索引或主键查找时会发生(高效)
const 当确定最多只会有一行匹配的时候,MySQL优化器会在查询前读取它而且只读取一 次,因此非常快.当主键放入where子句时,mysql把这个查询转为一个常量(高效)
system 这是const连接类型的一种特例,表仅有一行满足条件.
Null 意味着mysql能在优化阶段分解查询语句,在执行阶段甚至用不到访问表或索引(高 效)
使用profile工具
打开后,会显示语句执行详细的过程(如时间),判断效率高低
看是否开启:select @@profiling; 0是未开启
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
开启:set profiling=on; 没有加golob 所以为窗口、当前会话开启,不影响其他会话的状态。
开启之后执行查询命令后使用show profiles;查看执行时间(比如说利用索引查,然后删除索引再查)
用show profiles查询后,回一次给每条命令编号,也可用编号进行详细查询
如:查看4号命令在哪个阶段消费的时间 show profile for query 5;
看cpu在这个查询上化的时间最多:show profile cpu for query 5;
多表字段结合生成新表st
create table st as select stuid,s.name firstname,t.name lastname,s.age from students s cross join teachers t;
查看表结构:desc st;
建立复合索引,有多个字段组成的索引叫做复合索引
create index idx_first_last on st(firstname,lastname);
查看索引:show index from stG
查看表select * from st;
+-------+---------------+---------------+-----+
| stuid | firstname | lastname | age |
+-------+---------------+---------------+-----+
| 1 | Shi Zhongyu | Lin Chaoying | 22 |
| 1 | Shi Zhongyu | Miejue Shitai | 22 |
| 1 | Shi Zhongyu | Zhang Sanfeng | 22 |
| 1 | Shi Zhongyu | Song Jiang | 22 |
| 2 | Shi Potian | Lin Chaoying | 22 |
| 2 | Shi Potian | Miejue Shitai | 22 |
| 2 | Shi Potian | Zhang Sanfeng | 22 |
| 2 | Shi Potian | Song Jiang | 22 |
| 3 | Xie Yanke | Lin Chaoying | 53 |
查看两个索引:explain select * from st where firstname='Shi Zhongyu' and lastname='zhang%';
符合索引使用规则:如果有复合索引,不能跳过第一个索引,后续字段直接当条件是无法利用索引的。
复合索引中第二个索引能不能利用,答不是的,有序的前提下,无序的才可以利用索引。
如:explain select * from st where fitstname='Shi Zhongyu' and lastname='%ang';
这个是可以的,因为前面是有序的,后面虽然是无序的,但是可以的。
前面是无序的,或者没有约定的,直接迷糊查询,这样做是不行的。
如:explain select * from st where lastname like'%ang';前边没有约定有序的,后面模糊查询是不行的
查看所有二进制日志文件列表,及大小
show master logs;
查看使用中的二进制日志文件
show master status;
在线查看二进制文件中的指定内容(不太用,一般用mysqlbinlog)
如:show binlog events in 'mysql-bin.000001' from 6516 limit 2,3
二进制日志文件在重启服务后会重新生成一个新的二进制日志文件
生成新的二进制日志文件:mysqlbinlog flush-logs
服务器租用托管,机房租用托管,主机租用托管,https://www.e1idc.com