Articles of tag under mysql

2018-06-16 16:03:37

icp(index condition pushdown)

icp(acronym of index condition pushdown), which used when index(part of a multicolumn index) can be use to filter out some(enormous maybe) on storage level

  • the output of EXPLAIN cound display some information to indicate user, suck as key_len and key, especially in Extra column which will dislay using index condition
  • I think that only multicolumn index could make use of this method
  • even there exist only one where condition, the icp method would happened, becuase it did
  • need satisfy leftmost prefix
  • For InnoDB tables, ICP is used only for secondary indexes, The goal of ICP is to reduce the number of full-row reads and thereby reduce I/O operations. For InnoDB clustered indexes, the complete record is already read into the InnoDB buffer. Using ICP in this case does not reduce I/O.
  • Conditions that refer to subqueries cannot be pushed down.
  • it depends on optimizer and analyse according to statistic to decide where use icp on specific query
Read More »
2018-06-16 16:02:17

index merge

theory

index merge is that mysql sometimes(depends on some) could use seperate index on where condition at storage level, then can scan them simultaneously and them merge the result

  • where clause could affect optimizer whether to use index merge method, especially when intersect on index merge method
  • whether to use the method, I think the cardinatity of a index is very important, the higher the cardinatity the lower the posibility to use the method, because the purpose index merge is to reduce reduncdant rows retrieve, if optimizer think it can not satify this requirement, why use it?
  • the access method merges index scans from a single table; it does not merge scans across multiple tables.
  • type, key, key_len, rows will dispaly different in EXPLAIN when optimizer decide to use this method
  • it's more easier to judge a or opration whether the optimizer decide to use this method than a and opration
Read More »
2018-06-16 16:00:05

a little about query of mysql

There some theory about query on mysql we should know so that we can use it more efficiently.

some important theories

  • avoid reduncdant data access such as appliction retrieving more data than nedd and server level get reduncdant data
  • sometimes a fewer queries would be better than a single query
  • a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. This indicate whether use the index to get data you want is detemined by optimizer and the data on the table
Read More »
2018-06-16 15:52:19

index in mysql

There are some types of index which depends on storage engine. Most used index would be B-Tree indexes, which complement by InnoDB.

B-Tree indexes

  1. InnoDB refers to them by their primary key values
  2. store in order
  3. multicolumn index should satisfy leftmost prefix
  4. the index can be used by ORDER BY,GROUP BY and DISTINCT
Read More »
2017-10-17 09:54:41

linux安装MySQLDb报错mysql_config not found

linux下(我这里是linux mint 18.2)用以下命令安装MySQLDb

pip install MySQL-python

报了个错:

sh: mysql_config: not found
Traceback (most recent call last):
  File "setup.py", line 15, in <module>
    metadata, options = get_config()
  File "/home/zhxia/apps/source/MySQL-python-1.2.3/setup_posix.py", line 43, in get_config
    libs = mysql_config("libs_r")
  File "/home/zhxia/apps/source/MySQL-python-1.2.3/setup_posix.py", line 24, in mysql_config
    raise EnvironmentError("%s not found" % (mysql_config.path,))
EnvironmentError: mysql_config not found
Read More »
2017-10-03 10:39:46

如何处理mysql的history文件

今天使用mysql,想起linux下的命令行下面有一个history命令,它能将我们最近敲过的命令记录显示出来,然后我就想mysql命令有没有类似的呢?然后发现还真的有,在unix类操作系统下它会在用户的主目录下面生成一个.mysql_history,里面存放了我们敲过的mysql命令。不过这似乎并不是一件好事儿,因为它甚至能将明文密码显示出来,那么,应该怎么处理这个文件呢?

Read More »