| Version 12 (modified by , 2 weeks ago) ( diff ) |
|---|
Други развојни активности
Анализа на перформанси, додавање индекси
За да направиме анализа на перформансите на нашите прашалници ќе разгледаме неколку различни употреби.
Начинот на тестирање е следниот:
- Додаваме голем број на нови записи во табелите (пр. 1М записи) со цел користењето на индекс да биде исплатливо, доколку истиот е искористен соодветно. Јасно е дека за табела со 20 записи нема да има голема предност доколку се користи индекс.
- Пред додавање на индексот ги извршуваме прашалниците 10 пати користејќи
explain analyze. Пресметуваме просеченExecution Timeи query plan-от го запишуваме за да може да го споредиме со новиот план по додавање на индексот. - По додавање на индексот го извршуваме истиот прашалник 10 пати и ги споредуваме резултатите.
Сценарио 1
Ќе ја разгледуваме процедурата yesterdays_most_popular() - прва од Напреден развој на базата.
Бидејќи не можеме директно да извршуваме explain analyze на процедурата, тоа ќе го правиме на прашалниците кои се повикуваат во неа.
Додаваме 1М нови редови во listens табелата со случајна вредност за timestamp во интервал од изминатите 8 месеци.
INSERT INTO listens (listener_id, song_id, timestamp)
SELECT
(SELECT l.user_id FROM listeners l ORDER BY random() LIMIT 1),
(SELECT s.id FROM songs s ORDER BY random() LIMIT 1),
NOW() - (random() * INTERVAL '8 months')
FROM generate_series(1, 1000000)
ON CONFLICT DO NOTHING;
Додаваме индекс на timestamp
CREATE INDEX ON listens(timestamp);
Ќе тестираме 2 прашалници.
Прашалник 1:
EXPLAIN ANALYZE SELECT 1 FROM listens WHERE timestamp BETWEEN CURRENT_DATE - 1 AND CURRENT_DATE;
Без индекс добиваме:
+------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN |
+------------------------------------------------------------------------------------------------------------------------------+
|Gather (cost=1000.00..17155.54 rows=4428 width=4) (actual time=0.342..58.768 rows=4139 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Parallel Seq Scan on listens (cost=0.00..15712.74 rows=1845 width=4) (actual time=0.080..47.575 rows=1380 loops=3) |
| Filter: (("timestamp" <= CURRENT_DATE) AND ("timestamp" >= (CURRENT_DATE - 1))) |
| Rows Removed by Filter: 331269 |
|Planning Time: 0.116 ms |
|Execution Time: 58.965 ms |
+------------------------------------------------------------------------------------------------------------------------------+
Просечното Execution Time од 10 извршувања е 67,14ms.
Со индекс добиваме:
+----------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
|Index Only Scan using listens_timestamp_idx on listens (cost=0.43..140.59 rows=4408 width=4) (actual time=0.034..0.670 rows=4139 loops=1) |
| Index Cond: (("timestamp" >= (CURRENT_DATE - 1)) AND ("timestamp" <= CURRENT_DATE)) |
| Heap Fetches: 0 |
|Planning Time: 0.177ms |
|Execution Time: 0.793ms |
+----------------------------------------------------------------------------------------------------------------------------------------------------+
Можеме да забележиме користење на индекс преку Index Only Scan, сега просечното време за 10 извршувања е 0,83ms.
Прашалник 2:
EXPLAIN ANALYZE SELECT CURRENT_DATE - 1, s.id, COUNT(*)
FROM songs s
JOIN listens l ON l.song_id = s.id
WHERE l.timestamp BETWEEN CURRENT_DATE - 1 AND NOW()
GROUP BY s.id
ORDER BY COUNT(*) DESC
LIMIT 1;
Без индекс добиваме:
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Limit (cost=17116.49..17116.49 rows=1 width=20) (actual time=86.532..92.337 rows=1 loops=1) |
| -> Sort (cost=17116.49..17119.16 rows=1070 width=20) (actual time=86.530..92.334 rows=1 loops=1) |
| Sort Key: (count(*)) DESC |
| Sort Method: quicksort Memory: 25kB |
| -> Finalize GroupAggregate (cost=16834.70..17111.14 rows=1070 width=20) (actual time=86.503..92.307 rows=1 loops=1) |
| Group Key: s.id |
| -> Gather Merge (cost=16834.70..17084.39 rows=2140 width=16) (actual time=86.492..92.297 rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Sort (cost=15834.68..15837.35 rows=1070 width=16) (actual time=81.267..81.270 rows=1 loops=3) |
| Sort Key: s.id |
| Sort Method: quicksort Memory: 25kB |
| Worker 0: Sort Method: quicksort Memory: 25kB |
| Worker 1: Sort Method: quicksort Memory: 25kB |
| -> Partial HashAggregate (cost=15770.14..15780.84 rows=1070 width=16) (actual time=81.226..81.232 rows=1 loops=3) |
| Group Key: s.id |
| Batches: 1 Memory Usage: 73kB |
| Worker 0: Batches: 1 Memory Usage: 73kB |
| Worker 1: Batches: 1 Memory Usage: 73kB |
| -> Hash Join (cost=34.08..15754.87 rows=3054 width=8) (actual time=0.138..80.491 rows=2356 loops=3) |
| Hash Cond: (l.song_id = s.id) |
| -> Parallel Seq Scan on listens l (cost=0.00..15712.74 rows=3054 width=8) (actual time=0.061..79.620 rows=2356 loops=3) |
| Filter: (("timestamp" <= now()) AND ("timestamp" >= (CURRENT_DATE - 1))) |
| Rows Removed by Filter: 330293 |
| -> Hash (cost=20.70..20.70 rows=1070 width=8) (actual time=0.042..0.043 rows=15 loops=3) |
| Buckets: 2048 Batches: 1 Memory Usage: 17kB |
| -> Seq Scan on songs s (cost=0.00..20.70 rows=1070 width=8) (actual time=0.029..0.032 rows=15 loops=3) |
|Planning Time: 0.668 ms |
|Execution Time: 92.596 ms |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Просечното Execution Time од 10 извршувања е 88,65ms.
Со индекс добиваме:
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|QUERY PLAN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Limit (cost=13275.20..13275.21 rows=1 width=20) (actual time=10.802..15.147 rows=1 loops=1) |
| -> Sort (cost=13275.20..13277.88 rows=1070 width=20) (actual time=10.801..15.145 rows=1 loops=1) |
| Sort Key: (count(*)) DESC |
| Sort Method: quicksort Memory: 25kB |
| -> Finalize GroupAggregate (cost=12993.42..13269.85 rows=1070 width=20) (actual time=10.797..15.141 rows=1 loops=1) |
| Group Key: s.id |
| -> Gather Merge (cost=12993.42..13243.10 rows=2140 width=16) (actual time=10.787..15.131 rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Sort (cost=11993.40..11996.07 rows=1070 width=16) (actual time=6.038..6.041 rows=1 loops=3) |
| Sort Key: s.id |
| Sort Method: quicksort Memory: 25kB |
| Worker 0: Sort Method: quicksort Memory: 25kB |
| Worker 1: Sort Method: quicksort Memory: 25kB |
| -> Partial HashAggregate (cost=11928.86..11939.56 rows=1070 width=16) (actual time=6.008..6.012 rows=1 loops=3) |
| Group Key: s.id |
| Batches: 1 Memory Usage: 73kB |
| Worker 0: Batches: 1 Memory Usage: 73kB |
| Worker 1: Batches: 1 Memory Usage: 73kB |
| -> Hash Join (cost=194.78..11913.36 rows=3100 width=8) (actual time=0.937..5.574 rows=2356 loops=3) |
| Hash Cond: (l.song_id = s.id) |
| -> Parallel Bitmap Heap Scan on listens l (cost=160.70..11871.11 rows=3100 width=8) (actual time=0.879..4.934 rows=2356 loops=3) |
| Recheck Cond: (("timestamp" >= (CURRENT_DATE - 1)) AND ("timestamp" <= now())) |
| Heap Blocks: exact=3438 |
| -> Bitmap Index Scan on listens_timestamp_idx (cost=0.00..158.84 rows=7441 width=0) (actual time=1.735..1.735 rows=7067 loops=1) |
| Index Cond: (("timestamp" >= (CURRENT_DATE - 1)) AND ("timestamp" <= now())) |
| -> Hash (cost=20.70..20.70 rows=1070 width=8) (actual time=0.039..0.040 rows=15 loops=3) |
| Buckets: 2048 Batches: 1 Memory Usage: 17kB |
| -> Seq Scan on songs s (cost=0.00..20.70 rows=1070 width=8) (actual time=0.028..0.030 rows=15 loops=3) |
|Planning Time: 0.320 ms |
|Execution Time: 15.232 ms |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Просечно време на извршување е 15,04ms. Во планот забележуваме замена на секвенцијално скенирање со скенирање на индекс.
Заклучуваме дека перформансите на процедурата ќе бидат многу подобри со овој индекс и го додаваме истиот.
Сценарио 2
Ќе го разгледуваме прашалникот кој се извршува при преземање на сите песни/албуми објавени од артистот.
EXPLAIN ANALYZE SELECT
me.id,
me.title,
me.genre,
CASE
WHEN s.id IS NOT NULL AND s.album_id IS NULL THEN 'SONG'
WHEN a.id IS NOT NULL THEN 'ALBUM'
ELSE NULL
END AS type,
me.cover,
me.release_date
FROM musical_entities me
LEFT JOIN songs s ON s.id = me.id
LEFT JOIN albums a ON a.id = me.id
WHERE me.released_by = :artistId
ORDER BY me.release_date DESC
Додаваме 1M нови записи во musical_entities табелата, преку извршување на следниот прашалник повеќепати:
INSERT INTO musical_entities (released_by) SELECT id FROM users ORDER BY random() LIMIT 50000;
Креираме композитен индекс на released_by и release_date
CREATE INDEX me_released_by_release_date_idx
ON musical_entities (released_by, release_date DESC);
Без индекс добиваме:
+--------------------------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +--------------------------------------------------------------------------------------------------------------------------------------------------------+ |Gather Merge (cost=48470.13..48470.59 rows=4 width=82) (actual time=100.621..107.725 rows=3 loops=1) | | Workers Planned: 2 | | Workers Launched: 2 | | -> Sort (cost=47470.10..47470.11 rows=2 width=82) (actual time=76.312..76.315 rows=1 loops=3) | | Sort Key: me.release_date DESC | | Sort Method: quicksort Memory: 25kB | | Worker 0: Sort Method: quicksort Memory: 25kB | | Worker 1: Sort Method: quicksort Memory: 25kB | | -> Nested Loop Left Join (cost=0.84..47470.09 rows=2 width=82) (actual time=55.582..76.259 rows=1 loops=3) | | -> Nested Loop Left Join (cost=0.42..47461.22 rows=2 width=66) (actual time=55.561..76.234 rows=1 loops=3) | | -> Parallel Seq Scan on musical_entities me (cost=0.00..47444.33 rows=2 width=50) (actual time=55.513..76.174 rows=1 loops=3) | | Filter: (released_by = 123456) | | Rows Removed by Filter: 333332 | | -> Index Scan using songs_pkey on songs s (cost=0.42..8.44 rows=1 width=16) (actual time=0.049..0.049 rows=1 loops=3) | | Index Cond: (id = me.id) | | -> Index Only Scan using albums_pkey on albums a (cost=0.42..4.44 rows=1 width=8) (actual time=0.020..0.020 rows=0 loops=3) | | Index Cond: (id = me.id) | | Heap Fetches: 0 | |Planning Time: 0.525 ms | |Execution Time: 107.796 ms | +--------------------------------------------------------------------------------------------------------------------------------------------------------+
Просечното Execution Time од 10 извршувања е 100,5 ms
Со индекс добиваме:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Nested Loop Left Join (cost=1.27..88.91 rows=5 width=82) (actual time=0.061..0.084 rows=3 loops=1) | | -> Nested Loop Left Join (cost=0.85..66.72 rows=5 width=66) (actual time=0.026..0.041 rows=3 loops=1) | | -> Index Scan using me_released_by_release_date_idx on musical_entities me (cost=0.42..24.51 rows=5 width=50) (actual time=0.013..0.018 rows=3 loops=1) | | Index Cond: (released_by = 123456) | | -> Index Scan using songs_pkey on songs s (cost=0.42..8.44 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=3) | | Index Cond: (id = me.id) | | -> Index Only Scan using albums_pkey on albums a (cost=0.42..4.44 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=3) | | Index Cond: (id = me.id) | | Heap Fetches: 0 | |Planning Time: 0.491 ms | |Execution Time: 0.127 ms | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------++
Можеме да забележиме користење на новокреираниот индекс преку Index Scan using me_released_by_index on musical_entities me, како и отсуството на Sort (cost=47470.10..47470.11 rows=2 width=82) (actual time=76.312..76.315 rows=1 loops=3), сега просечното време за 10 извршувања е 0,105ms,
што претставува значително подобрување на перформансите.
Сценарио 3
Ќе разгледуваме прашалник кој ги наоѓа сите песни кои припаѓаат на одреден албум.
SELECT * FROM songs s LEFT JOIN albums a ON a.id = s.album_id WHERE s.album_id = :albumId;
Од претходните примери веќе имаме генерирано доволно записи во потребните табели.
Додаваме индекс на album_id
CREATE INDEX ON songs(album_id);
Без индекс добиваме:
+-------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +-------------------------------------------------------------------------------------------------------------------------------------+ |Nested Loop Left Join (cost=1000.15..11058.40 rows=5 width=36) (actual time=5.216..57.666 rows=3 loops=1) | | -> Gather (cost=1000.00..11050.17 rows=5 width=28) (actual time=5.196..57.634 rows=3 loops=1) | | Workers Planned: 2 | | Workers Launched: 2 | | -> Parallel Seq Scan on songs s (cost=0.00..10049.67 rows=2 width=28) (actual time=11.406..27.456 rows=1 loops=3) | | Filter: (album_id = 5494023) | | Rows Removed by Filter: 266666 | | -> Materialize (cost=0.15..8.18 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=3) | | -> Index Only Scan using albums_pkey on albums a (cost=0.15..8.17 rows=1 width=8) (actual time=0.012..0.013 rows=0 loops=1)| | Index Cond: (id = 5494023) | | Heap Fetches: 0 | |Planning Time: 0.191 ms | |Execution Time: 57.726 ms | +-------------------------------------------------------------------------------------------------------------------------------------+
Просечно Execution time е 56,24ms.
По додавање на индекс:
+-------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +-------------------------------------------------------------------------------------------------------------------------------------+ |Nested Loop Left Join (cost=4.62..32.33 rows=5 width=36) (actual time=0.048..0.054 rows=3 loops=1) | | -> Bitmap Heap Scan on songs s (cost=4.46..24.09 rows=5 width=28) (actual time=0.036..0.041 rows=3 loops=1) | | Recheck Cond: (album_id = 5494023) | | Heap Blocks: exact=3 | | -> Bitmap Index Scan on idx_songs_album (cost=0.00..4.46 rows=5 width=0) (actual time=0.024..0.024 rows=3 loops=1) | | Index Cond: (album_id = 5494023) | | -> Materialize (cost=0.15..8.18 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=3) | | -> Index Only Scan using albums_pkey on albums a (cost=0.15..8.17 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=1)| | Index Cond: (id = 5494023) | | Heap Fetches: 0 | |Planning Time: 0.155 ms | |Execution Time: 0.090 ms | +-------------------------------------------------------------------------------------------------------------------------------------+
Просечно Execution time е сега 0.092ms. Можеме да забележиме користење на индекс преку Bitmap Index Scan on idx_songs_album.
Безбедност и заштита
JWT
На најавени корисници на нашата апликација серверот им издава access token и refresh token.
Access токенот е валиден многу кратко (10 минути) со цел дури и да е превземен од малициозен корисник истиот да стане бескорисен за брзо време. Овој токен е потребен за пристап до содржини и податоци коишто не се достапни за други корисници (пр. преглед/промена на лични податоци)
Refresh токенот е валиден подолго време (14 дена) и се чува во база, за кога на корисникот ќе му истече access токенот тој да може да го докаже својот идентитет на серверот преку refresh токенот, и тогаш серверот да му издаде нов access токен.
public String refreshAccessToken(String refreshTokenString) {
RefreshToken refreshToken = refreshTokenService.validateRefreshToken(refreshTokenString);
User user = refreshToken.getUser();
return jwtService.generateToken(user.getUsername(), user.getRole().name());
}
Проверка за валидност на refresh токен
public RefreshToken validateRefreshToken(String token){
RefreshToken refreshToken = findByToken(token)
.orElseThrow(() -> new InvalidTokenException("Invalid refresh token."));
if (refreshToken.isRevoked()){
throw new InvalidTokenException("Refresh token has been revoked.");
}
if (refreshToken.getExpiresAt().isBefore(Instant.now())){
throw new InvalidTokenException("Refresh token has expired.");
}
return refreshToken;
}
Токенот е потпишан од серверот користејќи таен клуч со цел да не може малициозен корисник сам да си издаде свој токен. Метода за генерирање токен:
public String generateToken(String username, String role){
SecretKey key = Keys.hmacShaKeyFor(authProperties.getSecret().getBytes(StandardCharsets.UTF_8));
return Jwts.builder()
.setSubject(username)
.claim("role", "ROLE_" + role)
.setExpiration(new Date(System.currentTimeMillis() + (long) authProperties.getAccessTokenMaxAge() * 1000))
.signWith(key)
.compact();
}
Дополнително, имплементираме наш филтер кој ќе го проверува постоењето и валидноста на горенаведениот access токен:
@Component
@RequiredArgsConstructor
public class JwtFilter extends OncePerRequestFilter {
private final CustomUserDetailsService userDetailsService;
private final JwtService jwtService;
@Override
protected void doFilterInternal(
@NonNull HttpServletRequest request,
@NonNull HttpServletResponse response,
@NonNull FilterChain filterChain) throws ServletException, IOException {
String token = null;
if (request.getCookies() != null){
for (Cookie cookie: request.getCookies()){
if ("accessToken".equals(cookie.getName())){
token = cookie.getValue();
break;
}
}
}
if (token != null && !token.isEmpty()){
try {
Claims claims = jwtService.extractClaims(token);
String username = claims.getSubject();
if (username != null && SecurityContextHolder.getContext().getAuthentication() == null){
UserDetails userDetails = userDetailsService.loadUserByUsername(username);
UsernamePasswordAuthenticationToken authToken =
new UsernamePasswordAuthenticationToken(username, null, userDetails.getAuthorities());
SecurityContextHolder.getContext().setAuthentication(authToken);
}
} catch (ExpiredJwtException e){
System.out.println("Expired jwt token.");
response.setStatus(HttpStatus.UNAUTHORIZED.value());
return;
} catch (JwtException e){
System.out.println("Invalid jwt token.");
response.setStatus(HttpStatus.UNAUTHORIZED.value());
return;
} catch (Exception e){
System.out.println(e.getMessage());
response.setStatus(HttpStatus.UNAUTHORIZED.value());
return;
}
}
filterChain.doFilter(request, response);
}
}
Потоа, истиот треба да го додадеме во security конфигурацијата
@Bean
public SecurityFilterChain securityFilterChain(HttpSecurity http) throws Exception {
http
.cors(cors -> cors.configurationSource(corsConfigurationSource()))
.csrf(AbstractHttpConfigurer::disable)
.authorizeHttpRequests(auth -> auth
.requestMatchers("/auth/user").authenticated()
.anyRequest().permitAll())
.sessionManagement(session -> session.sessionCreationPolicy(SessionCreationPolicy.STATELESS))
.addFilterBefore(jwtFilter, UsernamePasswordAuthenticationFilter.class) // <---- оваа линија е битна !!!!
.headers((headers) ->
headers.frameOptions(HeadersConfigurer.FrameOptionsConfig::sameOrigin));
return http.build();
}
Чување лозинки
Бидејќи чуваме лозинки во нашата база не смееме истите да ги чуваме како plaintext.
Корстиме BCryptPasswordEncoder за хеширање на истите:
@Bean
public PasswordEncoder passwordEncoder(){
return new BCryptPasswordEncoder(10);
}
// понатаму кога создаваме нов корисник
User.UserBuilder userBuilder = User.builder()
.username(authRequestDto.username())
.password(passwordEncoder.encode(authRequestDto.password()))
// ... останата логика
SQL Injection
За да намалиме можност за SQL injection користиме параметаризирани прашалници, наместо да ги додаваме параметрите со конкатенација.
Всушност, бидејќи користиме Spring Data JPA / JPQL овие работи не треба експлицитно да ги пишуваме, туку се веќе имплементирани:
пример:
@Query("""
SELECT CASE WHEN COUNT (l)>0 THEN true ELSE false END
FROM MusicalEntity me
JOIN Like l on l.musicalEntity.id=me.id
WHERE l.listener.id=:userId
""")
boolean isLikedByUser(@Param("userId") Long userId);
CORS
Конфигурација за CORS
@Bean
public CorsConfigurationSource corsConfigurationSource() {
CorsConfiguration configuration = new CorsConfiguration();
configuration.setAllowedOriginPatterns(List.of("http://localhost:*"));
configuration.setAllowedMethods(List.of("GET", "POST", "PUT", "DELETE", "OPTIONS"));
configuration.setAllowedHeaders(List.of("*"));
configuration.setAllowCredentials(true);
UrlBasedCorsConfigurationSource source = new UrlBasedCorsConfigurationSource();
source.registerCorsConfiguration("/**", configuration);
return source;
}
Во продукциска околина, секако дека ќе треба localhost да се смени со соодветниот домен/и, а дополнително може и да се ограничат и дозволените методи и заглавја, но за рамките на овој проект сметаме дека ова е доволно.
Note:
See TracWiki
for help on using the wiki.
