Frontend responsibilities
Backend responsibilities
.click(handler) - event handling for specified elements$("your_selector").click(function(event) { |
.on( events [, selector ] [, data ], handler ) - event handling for dynamic elements$(document).on("click","your_selector", function (event) { |
if you know the particular node you’re adding dynamic elements to, you could specify it instead of the document.
$("parent_selector").on("click","your_selector", function (event) { |
$(document).on("click", "#your_div", {name: "Jack"}, handler); |
Deprecated API
As of jQuery 3.0, .bind() and .delegate() have been deprecated. It was superseded by the .on() method for attaching event handlers to a document since jQuery 1.7, so its use was already discouraged.
$("your_selector").click(function(event) { |
Get the element that triggered the event
event.targetthisNote: event.target equals this, and equals document.getElementById("your_selector")
Get the element id
this.idevent.target.id$(this).attr('id')Get jQuery object by element
$(this)$(event.target)Form Events
.blur(handler).change(handler).focus(handler).focusin(handler).focusout(handler).select(handler).submit(handler)Keyboard Events
.keydown(handler).keypress(handler).keyup(handler)Mouse Events
.click(handler).contextmenu(handler). The contextmenu event is sent to an element when the right button of the mouse is clicked on it, but before the context menu is displayed..dblclick(handler). The dblclick event is sent to an element when the element is double-clicked..hover(handlerIn, handlerOut).mousedown(handler).mouseenter(handler).mouseleave(handler).mousemove(handler).mouseout(handler).mouseover(handler).mouseup(handler).toggle(handler, handler). Bind two or more handlers to the matched elements, to be executed on alternate clicks.SELECT trx_mysql_thread_id, trx_state, trx_started, trx_query |
After killed the query thread. The client receive a error message 2013 - Lost connection to server during query.
SELECT * |
Create a table for test
CREATE TABLE `t_user` ( |
Executing the SQL statement 1 to lock the table
SET autocommit = 0; |
Executing the SQL statement 2, which will wait for the lock.
-- Temporarily set the lock wait timeout to 10 minutes. By default, it is 50 seconds. We need a longer timeout to find out the locked SQL statements. |
If waiting for lock is timeout (by default, it is 50 seconds), SQL statement 2 will receive a error message
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
After finished the lock test, you can COMMIT or ROLLBACK the transaction of SQL statement 1 and set autocommit to 1.
COMMIT; |
Query whether some SQL statement threads are waiting for lock
SELECT * |
Query whether some SQL statement treads are running with lock
SELECT * |
show open tables where in_use > 0; |
Get transaction IDs and real Thread IDs of lock-holding and lock-waiting executing SQL.
SHOW ENGINE INNODB STATUS; |
To find “TRANSACTION xxx, ACTIVE xxx sec” in the result text
lock-holding transaction Information
---TRANSACTION 580438, ACTIVE 1862 sec |
lock-waiting transaction Information
---TRANSACTION 580444, ACTIVE 36 sec starting index read |
There are only one lock-holding transaction and one lock-waiting transaction. So we can guess that thread 16 block thread 17, or that transaction 580438 block transaction 580444.
MySQL 5.x
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; |
MySQL 8
Query lock dependency
SELECT * FROM performance_schema.data_lock_waits; |
BLOCKING_ENGINE_TRANSACTION_ID: lock-holding transaction IDREQUESTING_ENGINE_TRANSACTION_ID: lock-waiting transaction IDThe result is BLOCKING_ENGINE_TRANSACTION_ID blocked REQUESTING_ENGINE_TRANSACTION_ID. We can confirm that transaction 580438 blocked transaction 580444. You can get the real thread IDs from the result of SHOW ENGINE INNODB STATUS;. Therefore, we can confirm that thread 16 blocked thread 17.
Query lock-holding and lock-waiting transaction information
SELECT * FROM performance_schema.data_locks; |
SHOW ENGINE INNODB STATUS;kill {thread_ID}; |
sudo lshw |
CPU Information
lscpu |
CPU Architecture
arch |
vmstat
echo "CPU Usage: "$[100-$(vmstat 1 2|tail -1|awk '{print $15}')]"%" |
/proc/stat
grep 'cpu ' /proc/stat | awk '{usage=($2+$4)*100/($2+$4+$5)} END {print "CPU Usage: " usage "%"}' |
cat /proc/stat |grep cpu |tail -1|awk '{print ($5*100)/($2+$3+$4+$5+$6+$7+$8+$9+$10)}'|awk '{print "CPU Usage: " 100-$1 "%"}' |
top
top -bn2 | grep '%Cpu' | tail -1 | grep -P '(....|...) id,'|awk '{print "CPU Usage: " 100-$8 "%"}' |
Block Devices Information
lsblk |
df -h |
Folder Disk Space Usage
# all subdirectories size and total size |
File Disk Space Usage
ls -lh . |
sudo dmidecode -t memory |
free -h |
Total network traffic
nload |
traffic by socket
iftop |
traffic by process ID (PID)
nethogs |
speedtest-cli
# install speedtest-cli |
LAN/private IP address
ifconfig |
Public IP address
curl ifconfig.me |
Public IP Information
curl ipinfo.io |
nmap
Nmap adapts its techniques to use the best available methods using the current privilege level, unless you explicitly request something different. The things that Nmap needs root (or sudo) privilege for on Linux are: Sniffing network traffic with libpcap. Sending raw network traffic.
# fast scan top 100 open parts (-F) |
-sS: (TCP SYN scan) - SYN scan is the default and most popular scan option for good reasons. It can be performed quickly, scanning thousands of ports per second on a fast network not hampered by restrictive firewalls. It is also relatively unobtrusive and stealthy since it never completes TCP connections.-sT: (TCP connect scan)-sU: (UDP scans)-n: (No DNS resolution) - Tells Nmap to never do reverse DNS resolution on the active IP addresses it finds. Since DNS can be slow even with Nmap’s built-in parallel stub resolver, this option can slash scanning times.-T: Set a timing template-T4: prohibits the dynamic scan delay from exceeding 10 ms for TCP ports. Note that a faster speed can be less accurate if either the connection or the computer at the other end can’t handle it, and is more likely to trigger firewalls or IDSs.-T5: prohibits the dynamic scan delay from exceeding 5 ms for TCP ports.--min-hostgroup numhosts: (Adjust parallel scan group sizes) Nmap has the ability to port scan or version scan multiple hosts in parallel.-F: (Fast (limited port) scan) Scan fewer ports than the default scan. Normally Nmap scans the most common 1,000 ports for each scanned protocol. With -F, this is reduced to 100.–top-ports [number]: to scan the top [number] most common ports.-p-: to scan 65535 TCP ports. Scanning all ports is too slow.-p<from>-<to>: to scan a range of ports.-p<port1>,<port2>: to scan specific ports.-p<from>-<to>,<port1>,<port2>: to scan multiple ports.-Pn: (No ping) This option skips the host discovery stage altogether. When ping is disabled on target server, we need add -Pn to skip ping.States of nmap
Linux Distro name and version
cat /etc/os-release |
Linux kernel version
uname -a |
System hostname and related settings
hostnamectl |
Start date and time of operating system
uptime -s |
Environment variables
env |
PATH
echo -e ${PATH//:/\\n} |
View Processes
top |
View listening ports
lsof
# lsof |
-i: select IPv4 or IPv6 files-P: no port names-n: no host namesCOMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME |
NAME = *:[port]: expose port on all network.NAME = [127.0.0.1 or localhost]:[port]: expose port on local network.# netstat |
Kill a Process
kill <PID> |
Kills a process by searching for its name
pkill -9 -f YOUR_PROCESS_NAME |
Kill a process by port
lsof -t -i:port | xargs kill |
Process start time
ps -p <pid> -o lstart,etime |
Process basic information
ps -p <pid> -o pid,cmd,lstart,etime,pcpu,pmem,rss,thcount |
Architecture of software
cat `which {your_software}` | file - |
[1] 10 Commands to Collect System and Hardware Info in Linux
MySQL 5.7.6 supports full-text index for Chinese, Japanese, and Korean (CJK).
The built-in MySQL full-text parser uses the white space between words as a delimiter to determine where words begin and end, which is a limitation when working with ideographic languages that do not use word delimiters. To address this limitation, MySQL provides an ngram full-text parser that supports Chinese, Japanese, and Korean (CJK). The ngram full-text parser is supported for use with InnoDB and MyISAM.
Creating a FULLTEXT Index that Uses the ngram Parser
CREATE FULLTEXT INDEX content_fulltext ON table_name(column1, column2,...) with parser ngram; |
SELECT COUNT(*) FROM articles |
the columns named in the MATCH() function (title and body) are the same as those named in the definition of the article table’s FULLTEXT index. To search the title or body separately, you would create separate FULLTEXT indexes for each column.
-- retrieves all the rows that contain the word “MySQL” but that do not contain the word “YourSQL” |
+ stands for AND- stands for NOTOR. The word is optional, but the rows that contain it are rated higher.") characters matches only rows that contain the phrase literally, as it was typed.[1] ngram Full-Text Parser - MySQL 5.7 Documentation
[2] MySQL 全文索引
A column should represent only one dimension/concept/meaning
Why:
For example, status column should only represent enable or disable. If you want to represent if it’s formal, you can add a column formal.
Multiple groups of the same structure columns should be put into another separate table
Why:
How:
report (id, name, s1_content, s1_status, s1_submit_time, s2_content, s2_status, s2_submit_time,...) |
=>
report (id, name) |
Moving TEXT/BLOB column to a separate table
Why
If a table contains string columns such as name and address, but many queries do not retrieve those columns, consider splitting the string columns into a separate table and using join queries with a foreign key when necessary. When MySQL retrieves any value from a row, it reads a data block containing all the columns of that row (and possibly other adjacent rows). Keeping each row small, with only the most frequently used columns, allows more rows to fit in each data block. Such compact tables reduce disk I/O and memory usage for common queries.
MySQL you are discouraged from keeping TEXT data (and BLOB, as written elsewhere) in tables frequently searched.
Select All
$("*") |
Select by element id
$("#id") |
Select by element class name
$(".className") |
Select by element tag name
$("div") |
Select by multiple condition union
$("selector1,selector2,...,selectorN ") |
Select by tag name and class name
$('div.my-class') |
$("form > input") // all direct children |
$("label + input") // select all input after label |
$("#prev ~ div") // select all siblings |
$("tr:first") |
// :contains |
// [attr] |
Filter by attribute and class name
$('.myclass[reference="12345"]') |
// :hidden |
// :nth-child |
Filter by element name
:button //Selects all button elements and elements of type button. |
Filter by input element type
:checkbox |
Filter by status of form element
:checked //Matches all elements that are checked or selected. |
// :not() |
:header // select all header elements, such as <H1>,<H2> |
filter("p") // get all <p> elements in jquery dom set |
first() |
Select parent or ancestors
parent() // one parent |
Select children or descendants
children("") |
Select siblings
siblings() |
Select only need columns
Convert a complex SQL statement to multiple simple SQL statements
Convert queries to use indexes. Using indexes operators: =, >, <, >=, <=, BETWEEN, IN. Not using indexes operators: LIKE, <>, NOT IN. Using MySQL built-in functions on column make query cannot use index.
Using LEFT JOIN or NOT EXISTS to replace NOT IN
A NOT IN query example
select * |
Use NOT EXISTS
select * |
Use LEFT JOIN
select * |
Using Full-text search MATCH ... AGAINST... or search engine replace LIKE
Using IN, or UNION, or AND NOT to replace OR
ORs are notoriously bad performers, because it splinters the execution path. The UNION alleviates that splintering, and the combines the two results sets. That said, IN is preferable to ORs because though being logically the same, the execution of IN is generally more optimized.
The OR query example 1:
select * |
Using IN
select * |
The OR query example 2:
select * |
Using UNION
select * |
The OR query example 3:
select * |
Using AND NOT
select * |
Use BETWEEN to replace DATE(), DATE_FORMAT()
A DATE_FORMAT example:
DATE_FORMAT(`date`, "%Y-%m") = "2022-07" |
YEAR(`date`) = "2022" and MONTH(`date`) = "7" |
Use BETWEEN
`date` BETWEEN '2022-07-01' and '2022-07-31' |
Convert function to use on right side
WHERE f(g(h(column))) > value |
WHERE column > H(G(F(value))) |
select *SUBSTR(column, start, length) if it could be.Late row lookups: Inner join ids instead of select *
Inner join is first to find offset + size rows id, then find all rows data by id setselect * is just to find offset + size rows data.select *
select * |
Inner join ids
select * |
In most cases, the inner join has a 5x performance improvement, but in some cases there is no difference in performance.
The performance gap is more evident for large row-width tables and queries with large offset.
Specify start row id for next page query
first page query
select * |
next page query
select * |
General
Optimizing SELECT Statements - MySQL 8.0 Reference Manual
High Offset
MySQL ORDER BY / LIMIT performance: late row lookups
OR
The jdbcType is a property for mapping Java object field values to SQL column values.
You need to specify the jdbcType when passing null values for parameters. Some databases need to know the value’s type even if the value itself is NULL. The JDBC specification itself that requires the type to be specified.
preparedStatement.setNull(int parameterIndex, int sqlType)Most of the times you don’t need to specify the jdbcType as MyBatis is smart enough to figure out the type from the objects you are working with. But if you send your parameters to the MyBatis statement inside a HashMap, for example, and one of the parameters is null, MyBatis won’t be able to determine the type of the parameter by looking at the HashMap because the HashMap is just a generic container and null itself carries no type information. At that point it would be o good idea to provide the jdbcType so that switching the database implementation later on does not cause any issues with null values.
JDBC Types
MySQL and Java DB currently do not support the ARRAY SQL data type.
Convert java.util.Date objects to date strings
The java.util.Date objects will default convert to datetime strings (TIMESTAMP JDBC type) like 2022-08-18 11:12:05.545. If you want to convert a Date object value to a date string (DATE JDBC type) like 2022-08-18, you can specify the jdbcType to DATE.
The javaType is a property for mapping result set column values to Java field values.
MyBatis can usually figure out the type if you’re mapping to a JavaBean. However, if you are mapping to a HashMap, then you should specify the javaType explicitly to ensure the desired behavior.
typeHandler can convert column values to Java values and verse vice.
Enum type value and the column value<resultMap id="BaseResultMap" type="com.demo.Task"> |
#{category,typeHandler=com.demo.typehandler.CategoryEnumTypeHandler} |
public class TaskCategoryEnumTypeHandler extends BaseTypeHandler<TaskTypeCategory> { |
public enum TaskTypeCategory { |
public class Task { |
The POJO (a plain Java object)
@Data |
Frontend
id=xxx&name=xxx&address.province=xxx&address.city=xxx&address.area=xxx |
Code example
let json = { |
function serializeTwoLevelsNestedJson(json) { |
Backend
@RequestMapping("/test") |
Frontend
Backend
@RequestMapping("/test") |
Frontend
{ |
Backend
@RequestMapping("/test") |
The POJO (a plain Java object)
@Data |
Frontend
This type of query strings looks like id=xxx&name=xxx&addressList[0].province&addressList[0].city&addressList[0].area, but it actually is
id=1&name=ABC&addressList%5B0%5D.province=AA&addressList%5B0%5D.city=BB&addressList%5B0%5D.area=CC |
You need to Encode square brackets [ and ] by the encodeURIComponent(str) method.
Code example
let json = {id: 1, name: "Tom", addressList: [ |
function serializeTwoLevelsNestedJson(json) { |
Backend
@RequestMapping("/test") |
Frontend
Backend
@RequestMapping("/test") |
Frontend
{ |
Backend
@RequestMapping("/test") |