一道MySQL查询题目

题目:有三张表:学生表 student,科目表:course,成绩表:grade。要求展示如下结果:

image-20211008175900619

阅读更多

MySQL事务基础

前言

在关系型数据库中,事务的重要性不言而喻,它是区别于文件系统的重要特征之一,在事务中涉及到的事务特性,隔离级别以及实现等,都是需要我们去理解以及深入探索的,只有理解了其运行和实现方式,才能在生产过程中运用灵活。在这篇文章中,主要介绍下事务的整体的知识框架和基本内容的介绍,可能涉及不深或不全面,但没关系,尝试着去理解,你也能收获不少。

阅读更多

Laravel中使用Redis

索引技术是数据库优化查询的一个重要手段,所以掌握好索引的相关知识是至关重要的。本文以 MySQL 数据库为研究研究对象,讨论关于索引的相关话题。

首先要明白的是索引是在存储引擎层上实现的,而MySQL支持多种存储引擎,各种引擎对索引的支持也不相同,所以MySQL中支持多种索引类型。

查看表中的索引:

1
SHOW INDEX FROM tablename

从存储结构上划分

分为:BTree 索引(B-Tree索引、B+Tree索引),Hash索引,full-index全文索引,R-Tree索引。

B-Tree:能加快数据的访问速度,因为存储引擎不再需要进行全表扫描来获取数据,数据分布在各个节点之中。

B+Tree: 是B-Tree的改进版本,同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上,并且增加了顺序访问指针,每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说,进行范围查找时只需要查找两个节点,进行遍历即可。而B-Tree需要获取所有节点,相比之下B+Tree效率更高。

B+Tree相对于B-Tree有几点不同:

  • 非叶子节点只存储键值信息。
  • 所有叶子节点之间都有一个链指针。
  • 数据记录都存放在叶子节点中。

001

002

id name age
1 Bob 34
2 Ann 77
3 Alice 5
4 Ken 91
5 Mai 22
6 Tom 89
7 Jann 38

Hash索引:

基于hash表。所以这种索引只支持精确查找,不支持范围查找,不支持排序。这意味着范围查找或ORDER BY都要依赖server层的额外工作。目前只有Memory引擎支持显式的hash索引(但是它的hash是nonunique的,冲突太多时也会影响查找性能)。Memory引擎默认的索引类型即是Hash索引,虽然它也支持B-Tree索引。

根据数据与索引的存储关联性划分

分为:聚集索引、非聚集索引

聚集索引:是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。
一般情况下主键会默认创建聚簇索引,且一张表只允许存在一个聚簇索引。

聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。

辅助索引(非聚集索引):与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据,而是存储相应行数据的聚集索引键,即主键。
当通过辅助索引来查询数据时,InnoDB存储引擎会遍历辅助索引找到主键,然后再通过主键在聚集索引中找到完整的行记录数据。

InnoDB和MyISAM都支持B+Tree索引,InnoDB主键采用聚集索引,其他字段是辅助索引(非聚集索引),MyISAM采用非聚集索引。

根据应用层次划分

分为:主键索引、普通索引、唯一索引、组合索引、全文索引

主键索引:根据主键建立的索引,不允许重复,不允许空值。

1
ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('column');

普通索引:最基本的索引,一个索引中只包含单个列,一个表中可以有多个单列索引。

1
ALTER TABLE 'table_name' ADD INDEX index_name('column');

唯一索引:用来建立索引的列必须是唯一的,允许为空值。

1
ALTER TABLE 'table_name' ADD UNIQUE index_name('column');

组合索引:将多个列作为一条索引进行检索,使用最左匹配原则。

1
ALTER TABLE 'table_name' ADD INDEX index_name('column1','column2','column3');

全文索引:对于文本的大对象,或者较大的CHAR类型的数据的列构建的对象

1
ALTER TABLE 'table_name' ADD FULLTEXT INDEX index_name('column');

创建全文索引

1
2
3
4
5
6
7
8
9
//创建表的时候添加FULLTEXT索引
CTREATE TABLE my_table(
id INT(10) PRIMARY KEY,
info TEXT,
FULLTEXT(info)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

// 创建表以后,在需要的时候添加FULLTEXT索引
ALTER TABLE my_table ADD FULLTEXT INDEX index_name(column_name);

使用全文索引,不能使用 LIKE的查询语法查询

1
SELECT * FROM table_name MATCH(index_name) AGAINST('查询字符串');

MySQL开启 关闭 重启命令

启动

1、使用 service 启动

1
service mysql start

2、使用 mysqld 脚本启动

1
/etc/inint.d/mysql start

3、使用 safe_mysqld 启动

1
safe_mysql&
阅读更多

MySQL对一张百万级别的表做查询优化

一般对于大数据量的表,都使用分页查询

1
SELECT COUNT(*) FROM add_test_str_memory; -- 1000004
1
2
3
4
5
SELECT * FROM add_test_str_memory ORDER BY id LIMIT 0,10;
SELECT * FROM add_test_str_memory ORDER BY id LIMIT 1000,10;
SELECT * FROM add_test_str_memory ORDER BY id LIMIT 10000,10;
SELECT * FROM add_test_str_memory ORDER BY id LIMIT 100000,10;
SELECT * FROM add_test_str_memory ORDER BY id LIMIT 1000000,10;

随着页数增多,查询速度越慢。

阅读更多

MySQL生成百万条测试数据

使用 MySQL 时经常会碰到大数据量的一些操作,但有些时候数据库中并没有足够的数据进行测试,所以需要在测试库中自行生成数据。

整体思路:

1、使用 MySQL 自定义函数rand_string,生成长度为n的随机字符串。

2、为了使数据插入快些,创建2张表,一张临时内存表,一张普通表。

3、创建存储过程,让操作简单些。

4、调用存储过程。

5、查询内存表中生成的记录

6、将数据从临时内存表插入普通表中

7、查询普通表生成的记录

阅读更多