BurningBright

  • Home

  • Tags

  • Categories

  • Archives

  • Search

High performance strategy in mysql

Posted on 2017-05-01 | Edited on 2020-09-17 | In db

Clustered Indexes

solidDB and innoDB support clustered index.
It make sure the similar key’s row’s physical position similar too.
The field type ‘char’ or ‘varchar’ is not suitable for building clustered index.
Especially the field storage random char. It will lead to alot of movements.

innoDB will build a clustered index on primary key
If it not exists, system will replace it by a hidden primary key.

Ordinary speak, DBMS will use clustered index to storage data
Second level index is base on it.

Structure

MyISAM


Contain row’s num and row’s value, it’s no difference with orther index.
It’s only a primary key named PRIMARY index(clustered index).

InnoDB

  • TID transaction id (transaction)
  • RP rollback pointer (MVCC)

InnoDB’s secondary index have no need to maintain index
when base data moved or data splited.

Because secondary storage primary key not row num

Difference

Balance

Describe Clustered Not clustered
Offen group and sort yes yes
Return range data yes no
very little differ values no no
little differ values yes no
big differ values no yes
offen update field no yes
foreign key field yes yes
primary key field yes yes
Offen modify index field no yes

when not

  • query quantity is very low.
    If build a index, it will affect performance alot.
  • table data is very low, should not build as will.
  • text image bit blob, should not build.
  • INSERT DELETE UPDATE, much more than SELECT operation.
    They are mutex operation to each other.

Tips

  • build index on field always used to search/ link/ group.
  • don’t calculate on index field behind where.

  • build and maintain will take time
    and it’s proportional with table data quantity.

  • index file will obtain physical space.
  • when INSERT DELETE UPDATE it needs to dynamic maintain index.

If CRUD is balance in field search, the dynamic cost maybe worth while.
Because the other three except Retrieve, need to find operation target as will.

Hash index in mysql

Posted on 2017-05-01 | Edited on 2020-09-17 | In db

Just like the hash function in java
Dispatch field’s row pointer to a slot by field’s hash code.
If some rows have same hash code, then storage pointer to a list.

Prepare

Create a table like this:

1
2
3
4
5
CREATE TABLE animal (
name VARCHAR(50) NOT NULL,
weight DOUBLE(11, 3) NOT NULL,
KEY `name` (`name`) USING HASH
) ENGINE=MEMORY;

mysql> select * from animal;
+————+————-+
| name | weight |
+————+————-+
| python | 20.500 |
| bear | 321.720 |
| lion | 221.980 |
| rabbit | 7.123 |
+————+————-+

Try

f represent the hash function:

1
2
3
4
f('python') = 2123
f('bear') = 6417
f('lion') = 8771
f('rabbit') = 2359

then:

Slot Hash value
2123 point to row 1
2359 point to row 4
6417 point to row 2
8771 point to row 3
  • Mysql calculate query’s hash value to decide
    which pointer or a pointer list, sql want.
  • Because index it’s self only store a short value,
    so hash code’s length is not determined by data type.

Some limit

  1. Index only have hash code, so can’t avoid table space i/o.
    But if table engine is memory, that’s not affected much.
  2. Hash index can’t be used to sort query collection.
  3. Not support part match, it only rely on hash code.
  4. Only support equal operation like:
    = <> in() not in()

raspberry home media

Posted on 2017-04-30 | Edited on 2018-12-16 | In raspberry

Install package(NTFS/exFAT)

1
2
3
4
5
6
7
8
9
10
11
12
13
# if exfat
sudo apt-get install exfat-fuse

# add this at the end(exfat)
/dev/sda1 /mnt/usbdisk vfat rw,defaults 0 0

# if ntfs
sudo apt-get install fuse-utils ntfs-3g
modprobe fuse
sudo nano /etc/fstab

# add this at the end(ntfs)
/dev/sda1 /mnt/myusbdrive ntfs-3g defaults,noexec,umask=0000 0 0
  • umask=0000 mean to all user rwx
  • 0 followed mean no backup
  • 0 at last mean no fsck sequential check.

Mount disk

1
2
3
sudo mkdir /mnt/seagate_2.5_80
sudo mount -o uid=pi,gid=pi /dev/sda1 /mnt/seagate_2.5_80
sudo umount /mnt/seagate_2.5_80

sda1:

  • sd mean usb equipment
  • a mean first equipment
  • 1 mean the first partition

http://elinux.org/RPi_Adding_USB_Drives
raspberry-pi-to-mount-the-removable-hard-disk

Install MiniDLNA

1
sudo apt-get install minidlna

config file

1
2
3
4
5
6
sudo nano /etc/minidlna.conf
media_dir=/mnt/seagate_2.5_80
db_dir=/var/lib/minidlna
listening_ip=192.168.3.1
port=8200
friendly_name=DLNA

commands

1
2
3
4
5
6
7
8
9
10
11
12
13
# start
sudo service minidlna start
# stop
sudo service minidlna stop
sudo killall minidlna
# uninstall
sudo apt-get remove --purge minidlna
# auto load
sudo update-rc.d minidlna defaults
# cancel auto load
sudo update-rc.d -f minidlna remove
# reflesh resouces
sudo service minidlna force-reload

B-Tree index in mysql

Posted on 2017-04-29 | Edited on 2020-09-17 | In db

Create a table like this:

1
2
3
4
5
6
7
CREATE TABLE People (
last_name varchar(50) not null,
first_name varchar(50) not null,
birthday date not null,
gender enum('m', 'f') not null,
key(last_name, first_name, dob)
);

Create a b-tree index by two name and birthday.

B-Tree structure’s data is Sequential

Use B-Tree index

First of all query’s sequential is required
last_name -> first_name -> birthday
if miss a key then the later condition is invalid.

  1. Match the full value to all fields specific val.
  2. Match a leftmost prefix search the the man who’s last_name is ‘Leon’
  3. Match a column prefix last_name like ‘L%’
    ps. the follow condition will be useless
  4. Match a range of values
    date_format(birthday, ‘%Y-%m-%d’) > ‘2017-04-29’ and
    date_format(birthday, ‘%Y-%m-%d’) < ‘2017-05-01’
  5. Match one part exactly and match a range on another part
    last_name=’Leon’ and first_name like ‘L%’

Rule

  1. Sql condition in B-Tree index must use the first field in index first.
  2. Sequential search condition, jump is useless(follower)
  3. Put range search condtion to the end of where
    the follow index field is invalid

Introduce Shiro

Posted on 2017-04-24 | Edited on 2020-09-17 | In java

d59f6d02-1f45-3285-8983-4ea5f18111d5.png
Shiro can be used in

  • Authentication check current user has identity
  • Authorization check user‘s priority, what he can do
  • Session management when user not logout, it’s info will be storage in session.
  • Cryptography protect data, like password.
  • Web support easy integrate in web environment
  • caching like user‘s role/priority, for improve performence.
  • Concurrency support mulity thread priority share.
  • Testing test support.
  • Run as for user use other user’s mask
  • Remember me next time come, no more login.

Shiro wouldn’t maintain user/priority info

See shiro from outside

5e0e9b41-0cca-367f-8c87-a8398910e7a6.png

  • Subject represent a request entry from net, like user/ robot/ crawler
  • SecurityManager it’s the core of shiro, it’s the security executor.
  • Realm it’s the security data, like a class of user.

1. Use Subject to authentication and authorization, Subject entrust to SecurityManager
2. SecurityManager need Realm to judge whether a user is the use

See shiro from inside

9b959a65-799d-396e-b5f5-b4fcfe88f53c.png

  • Subject framework’s main body
  • SecurityManager like DispatcherServlet in SpringMVC, he controlls global; it’s shiro’s heart.
  • Authenticator this is a extention point
  • Authrizer the request controller, determain whether subject has priority to do so.
  • Realm maybe JDBC/ LDAP/ CAS
  • SessionManager manage session’s life cycle.
  • SessionDAO if we wan’t to put session into redis, we can implements SessionDao in ourselves.
  • CacheManager put user/role/priority into it, for improving performence.
  • Cryptography shiro improve some encryption module.

Traversal in freemarker

Posted on 2017-04-19 | Edited on 2020-09-17 | In java

List<Question> question = new ArrayList<Question>();
Map<Question, List<Option>> mapping = new LinkedHashMap<Question, List<Option>>();

1
2
3
4
5
6
7
<#if question??>
<#list question as ask>
<#list options[ask.id?c] as option>
<#if option.status = 0>right<#else>false</#if>
</#list>
</#list>
</#if>
  • Is freemarker’s key only be String -_- shit.
  • Why can’t use object as a key
  • Use ?c transform int varable to string

Mapper anaylsis

Posted on 2017-04-10 | Edited on 2018-12-16 | In java

Package

org.apache.ibatis.binding

  • BindingException.java
    When MapperRegistry Or MapperMethod bind interface with
    target class object failed throw the Exception with exception message.
  • MapperMethod.java
    Execute with Command pattern, behind proxy, bind the input interface with it.
  • MapperProxy.java
    The agent, for all interface define in workspace.
  • MapperProxyFactory.java
    To create generic class MapperProxy for all interface,
    load interface’s method cache
  • MapperRegistry.java
    Store all MapperProxyFactory in resource, mapping annotation?
  • package-info.java

Code

MapperMethod

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
public class MapperMethod {

private final SqlCommand command;
private final MethodSignature method;

public MapperMethod(Class<?> mapperInterface, Method method, Configuration config) {
this.command = new SqlCommand(config, mapperInterface, method);
this.method = new MethodSignature(config, mapperInterface, method);
}

public Object execute(SqlSession sqlSession, Object[] args) {
Object result;
switch (command.getType()) {
case INSERT: {/***/}
case UPDATE: {/***/}
case DELETE: {/***/}
case SELECT:
if (method.returnsVoid() && method.hasResultHandler()) {
executeWithResultHandler(sqlSession, args);
result = null;
} else if (method.returnsMany()) {
// example -------------------------------
result = executeForMany(sqlSession, args);
// ---------------------------------------
} else if (method.returnsMap()) {
result = executeForMap(sqlSession, args);
} else if (method.returnsCursor()) {
result = executeForCursor(sqlSession, args);
} else {
Object param = method.convertArgsToSqlCommandParam(args);
result = sqlSession.selectOne(command.getName(), param);
}
break;
case FLUSH:
result = sqlSession.flushStatements();
break;
default:
throw new BindingException("Unknown execution method for: " + command.getName());
}
if (result == null && method.getReturnType().isPrimitive() && !method.returnsVoid()) {
throw new BindingException("Mapper method '" + command.getName()
+ " attempted to return null from a method with a primitive return type (" + method.getReturnType() + ").");
}
return result;
}

......

private <E> Object executeForMany(SqlSession sqlSession, Object[] args) {
List<E> result;
Object param = method.convertArgsToSqlCommandParam(args);
if (method.hasRowBounds()) {
RowBounds rowBounds = method.extractRowBounds(args);
result = sqlSession.<E>selectList(command.getName(), param, rowBounds);
} else {
result = sqlSession.<E>selectList(command.getName(), param);
}
// issue #510 Collections & arrays support
if (!method.getReturnType().isAssignableFrom(result.getClass())) {
if (method.getReturnType().isArray()) {
return convertToArray(result);
} else {
return convertToDeclaredCollection(sqlSession.getConfiguration(), result);
}
}
return result;
}

......
  • SqlCommand an inner static class represent the sql command
  • MethodSignature interface’s method parameters
  • executeForMany method used to execute commander’s command.

MapperProxy<T>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
public class MapperProxy<T> implements InvocationHandler, Serializable {

private static final long serialVersionUID = -6424540398559729838L;
private final SqlSession sqlSession;
private final Class<T> mapperInterface;
private final Map<Method, MapperMethod> methodCache;

public MapperProxy(SqlSession sqlSession, Class<T> mapperInterface, Map<Method, MapperMethod> methodCache) {
this.sqlSession = sqlSession;
this.mapperInterface = mapperInterface;
this.methodCache = methodCache;
}

@Override
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
try {
if (Object.class.equals(method.getDeclaringClass())) {
return method.invoke(this, args);
} else if (isDefaultMethod(method)) {
return invokeDefaultMethod(proxy, method, args);
}
} catch (Throwable t) {
throw ExceptionUtil.unwrapThrowable(t);
}
// transform to the real mapper method
final MapperMethod mapperMethod = cachedMapperMethod(method);
return mapperMethod.execute(sqlSession, args);
// -------------------------------------
}
......
}
  • the front agent
  • used to handle the mapper stuff
  • record all mapper method in cache
  • SqlSession from constructor, used in MapperMethod

MapperProxyFactory<T>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
public class MapperProxyFactory<T> {

private final Class<T> mapperInterface;
private final Map<Method, MapperMethod> methodCache = new ConcurrentHashMap<Method, MapperMethod>();

public MapperProxyFactory(Class<T> mapperInterface) {
this.mapperInterface = mapperInterface;
}

public Class<T> getMapperInterface() {
return mapperInterface;
}

public Map<Method, MapperMethod> getMethodCache() {
return methodCache;
}

@SuppressWarnings("unchecked")
protected T newInstance(MapperProxy<T> mapperProxy) {
return (T) Proxy.newProxyInstance(mapperInterface.getClassLoader(), new Class[] { mapperInterface }, mapperProxy);
}

public T newInstance(SqlSession sqlSession) {
final MapperProxy<T> mapperProxy = new MapperProxy<T>(sqlSession, mapperInterface, methodCache);
return newInstance(mapperProxy);
}

}
  • used to create generic mapper proxy object
  • a reference to method cache parse to MapperProxy
  • new instance the agent object

MapperRegistry

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
public class MapperRegistry {

private final Configuration config;
private final Map<Class<?>, MapperProxyFactory<?>> knownMappers = new HashMap<Class<?>, MapperProxyFactory<?>>();

public MapperRegistry(Configuration config) {
this.config = config;
}

@SuppressWarnings("unchecked")
public <T> T getMapper(Class<T> type, SqlSession sqlSession) {
final MapperProxyFactory<T> mapperProxyFactory = (MapperProxyFactory<T>) knownMappers.get(type);
if (mapperProxyFactory == null) {
throw new BindingException("Type " + type + " is not known to the MapperRegistry.");
}
try {
return mapperProxyFactory.newInstance(sqlSession);
} catch (Exception e) {
throw new BindingException("Error getting mapper instance. Cause: " + e, e);
}
}

public <T> boolean hasMapper(Class<T> type) {
return knownMappers.containsKey(type);
}

public <T> void addMapper(Class<T> type) {
if (type.isInterface()) {
if (hasMapper(type)) {
throw new BindingException("Type " + type + " is already known to the MapperRegistry.");
}
boolean loadCompleted = false;
try {
knownMappers.put(type, new MapperProxyFactory<T>(type));
// It's important that the type is added before the parser is run
// otherwise the binding may automatically be attempted by the
// mapper parser. If the type is already known, it won't try.
MapperAnnotationBuilder parser = new MapperAnnotationBuilder(config, type);
parser.parse();
loadCompleted = true;
} finally {
if (!loadCompleted) {
knownMappers.remove(type);
}
}
}
}
......
}
  • record every mapper’s proxy factory
  • parse mapper’s annotation?

Discuss

  • Mybatis could use interface to execute sql is base on java’s
    dynamic invoke.
  • SqlSession is come from MapperRegistry getMapper method’s
    input parameter.
  • At last the SqlSession execute Object’s sql

Java dynamic proxy

Posted on 2017-03-26 | Edited on 2018-12-16 | In java

Use scope

  • Java AOP is base on dynamic proxy
  • Plug in, for increasing function of package already exists.
  • Framework, for some abstract programming level.

Demo

1. an Interface

1
2
3
4
public interface Interface {
void hi();
String sayHello(String arg);
}
  • first interface used to call a method
    that has no arguments and return
  • second interface used to call a method
    that has a argument and return string

2. an implements class

1
2
3
4
5
6
7
8
9
10
11
public class HelloProxy implements Interface {
@Override
public void hi() {
System.out.println("Hello world");
}
@Override
public String sayHello(String arg) {
System.out.println("Hello " + arg);
return "Hello " + arg;
}
}
  • Geet to world
  • Geet to the input argument and return

3. an InvocationHandler

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;

public class DynamicProxyHandler implements InvocationHandler {

private Object proxied;

public DynamicProxyHandler(Object proxied) {
this.proxied = proxied;
}

@Override
public Object invoke(Object proxy, Method method, Object[] args)
throws Throwable {

if (args != null && args.length > 0 && args[0] != null)
return "Invoke " + method.invoke(proxied, args);

return null;
}

}
  • The handler implements InvocationHandler is the key
    to dynamic call the target methods or fields.

  • All the call in handler would be redirect to a call processor.

  • The first argument in method invoke is used to deal the
    manual call object.

  • The second argument is the method you call in a
    uppper reference or a maunal call method.

  • The third argument is the arguments you want to input,
    the processor would distinguish the method sign may be -_-.
    uppper input or maunal input arguments.

4. have a try

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
import java.lang.reflect.Proxy;

public class SimpleDemo {

public static void main(String[] args) {
Interface hello = (Interface) Proxy.newProxyInstance(
HelloProxy.class.getClassLoader(),
new Class[] { Interface.class },
new DynamicProxyHandler(new HelloProxy()));

hello.hi();
System.out.println(hello.sayHello("proxy"));

}

}
  • The newProxyInstance will create a subClass of the second argument
    so the $proxy0 can be transform to the Interface.

  • The first argument classLoader is to point which class is agent.

  • The third argument is the handler we created in step 3
    take a proxied hello interface implement into it’s constructor.

  • The result in sayHello has been deal by handler.

Result:

Hello world
Hello proxy
Invoke Hello proxy

Latex source file

Posted on 2017-03-25 | Edited on 2020-09-17 | In tool

Latex’s reserved characters

# $ % ^ & _ { } \

They have specal meaning in latex.
Use backslash to output them

\# \$ \% \^{} \& \_ \{ \} \ {}

The backslash it’s self can’t be output by add another backslash.
Have a try .

The \\ is used to take a newline.

Commands

Command start with \ backslash

You can \textsl{lean} on me!
->
You can lean on me!


Please, start a new line right here!\newline Thank you!
Please, start a new line right here!\ Thank you!
->
Please, start a new line right here!
Thank you!

Note

If Latex meet a ‘%’ percent sign, it will ignore words behind it.

1
2
3
4
5
This is an % stupid
% Better: instructive <----
example: Supercal%
ifragilist%
icexpialidocious

This is an example: Supercalifragilisticexpialidocious


If comment content is very long, try verbatim macro package’s comment environment.
In document’s guid space declare \usepackage{verbatim}

1
2
3
4
5
6
7
This is another
\begin{comment}
rather stupid,
but helpful
\end{comment}
example for embedding
comments in your document.

This is another example for embedding comments in your document.

Latex source file

Posted on 2017-03-22 | Edited on 2020-06-17 | In tool

blank space distance

Blank space and table sign are same in Latex as a space.
The multi blank characters and a new line will be seem as one space.
The head blank space will be ignore normally.

The new line mean an old paragraph ended and a new paragraph begin.

It does not matter whether you
enter one or several spaces
after a word.
An empty line starts a new
paragraph.

1
2
3
It does not matter whether you enter one or several spaces
after a word.
An empty line starts a new paragraph.
1…212223…29

Leon

282 posts
20 categories
58 tags
GitHub
Links
  • clock
  • typing-cn
  • mathjax
  • katex
  • cron
  • dos
  • keyboard
  • regex
  • sql
  • toy
© 2017 – 2024 Leon
Powered by Hexo v3.9.0
|
Theme – NexT.Muse v7.1.2