mysql学习笔记-索引1

最新推荐文章于 2024-04-15 18:40:28 发布
最新推荐文章于 2024-04-15 18:40:28 发布
阅读量79 收藏
点赞数
分类专栏： mysql 文章标签： mysql 索引 mysql索引 优化 sql优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/programmer_trip/article/details/108018357
版权

索引失效(应尽量避免)

1 全值匹配我最爱
2 最佳左前缀法则(如果索引了多列，要遵守最左前缀法则，查询从索引的最左侧列开始并且不跳过索引中的列，,比如建的索引为ABC，使用的时候AC,那么C用不到索引查询)
3 不再索引列上做任务操作(计算，函数，(自动 or 手动)类型转换)，会导致索引失效而转向全表扫描
4 存储引擎不能使用索引中范围条件右边的列
5 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select*
6 mysql在使用不等于(!= 或 <>)时无法使用索引导致全表扫描
7 is null,is not null 也无法使用索引
8 like 以通配符开头(’%abc…’)mysql索引失效会变成全表扫描操作，解决like '%字符串%'时索引不被使用的方法
9 字符串不加单引号索引失效
10 少用or，用它来连接时索引失效

最佳左前缀案例

#建表
create table staffs(
	id int primary key auto_increment,
	name varchar(25) not null default '',
	age int not null default 0,
	pos varchar(20) not null comment '职位',
	add_time timestamp not null comment '入职时间'
);

#插入数据
insert into staffs(name,age,pos,add_time) values('z3',22,'manager',now());
insert into staffs(name,age,pos,add_time) values('lili',23,'dev',now());
insert into staffs(name,age,pos,add_time) values('xiaowagn',23,'dev',now());
insert into staffs(name,age,pos,add_time) values('1500',23,'dev',now());

#查询 
mysql> select * from staffs;
+----+----------+-----+---------+---------------------+
| id | name     | age | pos     | add_time            |
+----+----------+-----+---------+---------------------+
|  1 | z3       |  22 | manager | 2020-08-15 10:01:21 |
|  2 | lili     |  23 | dev     | 2020-08-15 10:01:21 |
|  3 | xiaowagn |  23 | dev     | 2020-08-15 10:01:21 |
|  4 | 1500     |  23 | dev     | 2020-08-15 13:25:02 |
+----+----------+-----+---------+---------------------+

#建立联合索引
alter table staffs add index idx_staffs_nameAgePos(name,age,pos);

#查看sql执行计划  查询name使用到了索引，并且type为ref
mysql> explain select * from staffs where name = 'z3';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 77      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+

#查询 name age 同样用到了索引 type为ref，ref列看到两个const,说明两个索引都用到了
mysql> explain select * from staffs where name = 'z3' and age = 22;
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 81      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+

#查询name age pos 用到了索引 type为ref，ref列看到3个const,说明3个索引都用到了
mysql> explain select * from staffs where name = 'z3' and age = 22 and pos = 'manager';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 143     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+

#仅查询age,会发现type为ALL,为全表扫描
mysql> explain select * from staffs where  age = 22 ;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

# 查询 pos ,会发现type为ALL,为全表扫描
mysql> explain select * from staffs where pos = 'manager';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

# 查询 age pos ，可以看到type为ALL,没有用到索引
mysql> explain select * from staffs where age = 22 and pos = 'manager';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

explain select * from staffs where name = 'z3' and pos = 'manager';

#查询name,pos，可以看到type为ref,ref列看到一个const,说明只用到了一个索引，由于跳过一个索引列，因此pos索引无法使用

mysql> explain select * from staffs where name = 'z3' and pos = 'manager';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 77      | const |    1 |    33.33 | Using index condition |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+

对索引列进行操作会导致索引失效

#对索引字段name不做修改，可以正常使用到索引
mysql> explain select * from staffs where name = 'z3';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 77      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+

#对索引列使用函数导致索引失效，变成了全表扫描
mysql> explain select * from staffs where trim(name) = 'z3';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+

存储引擎不能使用索引中范围条件右边的列

#
mysql> explain select * from staffs where name = 'z3' and age = 21 and pos = 'manager';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 143     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+

# 将age改为 >21,会发现type变成range，并且使用到的索引长度由之前的额143变成81，说明没有使用到pos索引
mysql> explain select * from staffs where name = 'z3' and age > 21 and pos = 'manager';
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys         | key                   | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 81      | NULL |    1 |    33.33 | Using index condition |
+----+-------------+--------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+

尽量使用覆盖索引

#使用了select *
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 143     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+

#查询的所有字段都在建立的索引中，可以看到在extra中使用了using index，相对select *性能更好
mysql> explain select name,age,pos from staffs where name = 'z3' and age = 22 and pos = 'manager';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 143     | const,const,const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+

mysql在使用不等于(!= 或 <>)时无法使用索引导致全表扫描

#使用 = 时可以用到索引
mysql> explain select * from staffs where name = 'z3';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 77      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+

#使用 != 时，索引失效，变成全表扫描
mysql> explain select * from staffs where name != 'z3';
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+

使用 <>  时，索引失效，变成全表扫描
mysql> explain select * from staffs where name <> 'z3';
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+

is null,is not null 也无法使用索引

#查询字段使用 is null，由于name字段设置了not null ,因此查找is null是不可能有结果的，就出现了Impossible WHERE，如果该字段允许为null,就会发现该字段索引用不上，变成了全表扫描
mysql> explain select * from staffs where name is null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+

#使用 is not null 同样会使得索引失效，变成了全表扫描 
mysql> explain select * from staffs where name is not null;
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | idx_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+--------+------------+------+-----------------------+------+---------+------+------+----------+-------------+

like 以通配符开头(’%abc…’)mysql索引失效会变成全表扫描操作，

解决like '%字符串%'时索引不被使用的方法

#建表
create table tbl_user(
	id int primary key auto_increment,
	name varchar(20) not null,
	age int,
	email varchar(20)
);

#插入数据
insert into tbl_user(name,age,email) values('1aa1',21,'b@163.com');
insert into tbl_user(name,age,email) values('2aa2',222,'a@163.com');
insert into tbl_user(name,age,email) values('3aa3',265,'c@163.com');
insert into tbl_user(name,age,email) values('4aa4',21,'d@163.com');
insert into tbl_user(name,age,email) values('aa',121,'e@163.com');

#查询
mysql> select * from tbl_user;
+----+------+------+-----------+
| id | name | age  | email     |
+----+------+------+-----------+
|  1 | 1aa1 |   21 | b@163.com |
|  2 | 2aa2 |  222 | a@163.com |
|  3 | 3aa3 |  265 | c@163.com |
|  4 | 4aa4 |   21 | d@163.com |
|  5 | aa   |  121 | e@163.com |
+----+------+------+-----------+

#没有建索引时，不管查一个字段还是多个字段基本都是全表扫描
#现在建一个联合索引
create index idx_tbl_nameAge on tbl_user(name,age);

#like中 % 加在两边  索引失效  全表扫描
mysql> explain select * from tbl_user where name like '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

#like中 % 加在左边  索引失效  全表扫描
mysql> explain select * from tbl_user where name like '%aa';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

#like中 % 加在右边  索引没有失效  type为range,使用到了索引
mysql> explain select * from tbl_user where name like 'aa%';
+----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tbl_user | NULL       | range | idx_tbl_nameAge | idx_tbl_nameAge | 62      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+-----------------+-----------------+---------+------+------+----------+-----------------------+

但是如果%一定要加在两边，否则查询出的数据有误，又要使用索引，解决办法，尽量使用覆盖索引

#这里不使用select * 而是select 建立索引的字段，在使用覆盖索引的情况下，like '%字符串%' 索引就不会失效
mysql> explain select id,name,age from tbl_user where name like '%aa%';
+----+-------------+----------+------------+-------+---------------+-----------------+---------+------+------+----------+--------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key             | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+----------+------------+-------+---------------+-----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tbl_user | NULL       | index | NULL          | idx_tbl_nameAge | 67      | NULL |    5 |    20.00 | Using where; Using index |
+----+-------------+----------+------------+-------+---------------+-----------------+---------+------+------+----------+--------------------------+

#select 索引 + 未建索引字段时，like '%aa%'会导致索引失效，转为全表扫描
mysql> explain select id,name,age,email from tbl_user where name like '%aa%';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

字符串不加单引号索引失效

# name字段为varchar类型，但是这里查询时，2000为int类型，mysql查询时会自动将int的2000隐式转换为varchar类型的'2000',就像上面说的，对索引列进行的任何操作都会导致索引失效，因此这里的name索引失效，导致全表扫描
mysql> explain select * from tbl_user where name = 2000;
+----+-------------+----------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys   | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+-----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tbl_user | NULL       | ALL  | idx_tbl_nameAge | NULL | NULL    | NULL |    5 |    20.00 | Using where |
+----+-------------+----------+------------+------+-----------------+------+---------+------+------+----------+-------------+

#这里name的值3
mysql> explain select * from staffs where name = '2000';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 77      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
programmer_trip
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql索引学习笔记
m0_73257876的博客
09-12 305
索引是存储引擎快速找到记录的一种数据结构。数据库中的数据可以理解成字典中的单词，而索引就是目录，显而易见这是一种空间换时间的做法，目录占用了空间，但是加快了我们找到单词的速度，正如索引需要空间存储，但是利用索引我们可以快速的找到想要的数据。本文主要讨论B+树索引。
mysql学习笔记1
py_xiaobai的博客
04-29 131
create table book( id INT NOT NULL AUTO_INCREMENT, book_name VARCHAR(100) NOT NULL, author VARCHAR(40) NOT NULL, publish_date DATE, price float, PRIMARY KEY (id ) )ENGINE=InnoDB DEFA...
MySQL学习笔记-索引
金鳞踏雨
04-03 1745
什么是索引？ 索引的分类 功能分类 结构分类 索引的操作 创建索引 创建索引的语法 （1）为student表中姓名列创建一个普通索引 （2）为student表中年龄列创建一个唯一索引 查看索引 查看索引的语法 （1）查看student表中的索引 alter语句添加索引 添加索引的语法 （1）为student表中name列添加全文索引 删除索引 删除索引的语法 （1）删除student表中的idx_score索引 索引的实现原则 磁盘存储 BTree B+Tr.
Mysql学习笔记3---索引
最新发布
weixin_57501048的博客
04-15 526
索引是高效获取数据的数据结构。
MySQL学习笔记----索引的创建和删除
GOV_D的博客
08-11 1299
文章目录引入索引的目的索引的分类及原则添加索引(1)使用ALTER TABLE语句添加索引(2)使用CREATE INDEX语句对表增加索引删除索引组合索引与前缀索引索引使用分析及注意事项 引入索引的目的 在数据量和访问量不大的情况下，mysql访问是非常快的，是否加索引对访问影响不大。但是当数据量和访问量剧增的时候，就会发现mysql变慢，甚至down掉，这就必须要考虑优化sql了，给数据库建立正确合理的索引，是mysql优化的一个重要手段。 索引的目的在于提高查询效率，可以类比字典，如果要查“mysq
MYSQL学习笔记-索引参照.pdf
12-08
1. **普通索引**（Non-Unique Index）：是最基础的索引类型，对索引列没有唯一性要求。可以通过`CREATE INDEX`、`ALTER TABLE`或在创建表时指定来创建。 2. **唯一索引**（Unique Index）：索引列的值必须是唯一的...
MYSQL学习笔记-索引[文].pdf
10-11
MYSQL学习笔记-索引 MYSQL学习笔记-索引是一篇关于MYSQL数据库索引的详细笔记，涵盖了索引的基本概念、类型、创建方法以及使用场景。下面是笔记中的主要知识点： 一、索引的基本概念 * 在数据库表中，使用索引...
MYSQL学习笔记-索引
08-28
MYSQL学习笔记-索引MYSQL学习笔记-索引MYSQL学习笔记-索引
MySQL学习笔记-初级操作
12-14
1. **安装MySQL** 在开始学习之前，你需要在你的计算机上安装MySQL。这可以通过访问MySQL官方网站下载适合你操作系统的安装包来完成。安装过程中，你需要设置root用户的密码，这个密码将用于所有数据库管理操作。 ...
MySQL学习笔记-基础到进阶
05-28
内容概要：MySQL学习笔记，内容包括SQL基本语法、MySQL基础知识，包括存储引擎、索引、视图、锁、优化、存储结构等MySQL主要的内容。 适用人群：适合自学MySQL的同学使用。 能学到什么：MySQL从基础到进阶的全部内容...
MySql查询本周或下周过生日的人
programmer_trip的博客
05-25 6253
这几题我想了很久,因此记录一下. 做题思路,先将学生的生日年份统一成当前时间的年份,这样就可以直接用相差的天数判断是否本周或者下周过生日了 首先还是来建表加数据吧 -- 创建表格 create table student( s_id varchar(20) not null, s_name varchar(255) not null default '', s_birth varchar(20) not null default '', s_sex varchar(10) not null defa
HTTP协议以及登录案例(学习笔记)
programmer_trip的博客
03-29 1700
HTTP: 概念：Hyper Text Transfer protocol 超文本传输协议 传输协议：定义了客户端和服务器端通信时，发送数据的格式 特点： ①基于TCP/IP的高级协议：是安全的，TCP三次握手和四次挥手 ②默认端口号：80 ③基于请求/相应模型的:一次请求对应一次相应 ④无状态的：每次请求之间相互独立，不能交互数据 历史版本： ①1.0：每发送一次请求都会建立新的连接 ② 1.1：复用连接，每次发送请求后会等一段时间，在这段时间内再次发送请求，就会复用刚才建立的连接，否则就会关闭连接
clickhouse直连mysql
programmer_trip的博客
07-01 1216
遇到了一个这样的需求，报表上展示超时的订单信息 部门，统计日期，超时订单编号，订单计划开始/结束时间 订单实际开始/结束时间 这里需要让业务方当其认为这条记录不是真的超时时，为这条记录打上一个不纳入统计的标签，如果业务方选择了隐藏，那么这部分打上标签的数据就不会纳入统计，如果没有点隐藏按钮就不会受影响，打标签前怎么统计就怎么统计。 目前的解决方案是：前端在页面交互时通过业务方输入的内容将带标签信息写入mysql表，使用clickhouse直连mysql提供接口查询服务。 如果有更好的设计方案也欢迎交流???
linux虚拟机中安装mysql
programmer_trip的博客
05-29 1214
安装流程 一,先将mysql安装包上传到虚拟机中,会以进度条的形式显示上传文件的进度.(博主把mysql安装包上传在/root/software目录下) 二.对已经打包的mysql进行拆包 ①首先我们来看一下上传的mysql的文件详情 [root@hdp01 software]# ll /root/software 总用量 785796 -rw-r--r--. 1 root root 195094741 5月 29 02:37 jdk-8u221-linux-x64.tar.gz -rw-r--r--.
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:异常处理以及java程序与数据库建立连接的流程
programmer_trip的博客
05-03 892
使用的JDBC是: mysql-connector-java-5.1.36.jar 使用的mysql版本是：8.0.12 一、创建注册驱动以及建立java程序与mysql数据库的连接 import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class Wrong...
使用poi工具将sql查询结果写入excel文件(报表的导出数据功能)
programmer_trip的博客
02-09 517
这里以查询mysql数据并导出为excel为例子 关于创建数据库及表数据导入这里没讲，可以自行创建和插入测试数据 这里用到的数据库是poi mysql表为test 1 首先创建一个maven项目 demo_poi 2 pom.xml中引入mysql连接的驱动包，以及poi需要使用的jar包 <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-jav
navicate远程连接宝塔面板mysql数据库
programmer_trip的博客
06-01 318
宝塔面板装在阿里云服务器上， 需要远程连接宝塔面板的mysql，必须开启阿里云安全组的配置规则中开放3306端口，同时注意再宝塔面板的安全-防火墙中也要开放306端口， 另外需要设置所有主机或者指定主机能够访问该数据库，最后刷新下权限，应该就可以连上了 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'xxxxx' WITH GRANT OPTION; flush privileges; ====>血的教训啊，弄了好久才发现原来宝塔
mysql学习笔记-索引2
programmer_trip的博客
04-16 107
----------------------------------索引----------------------------------- mysql官方对索引的定义： 索引(Index)是帮助mysql高效获取数据的数据结构===>索引的本质：索引是数据结构 索引是排好序的快速查找数据结构 一般索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上 平时所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引，其中聚集索引，次要索引，覆盖

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值