`
hideto
  • 浏览: 2674971 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

HPM Note4, Schema Optimization and Indexing

阅读更多
Choosing Optimal Data Types
Smaller is usually better
Simple is good
Avoid NULL if possible

Indexing Basics
Indexes are implemented in the storage engine layer, not the server layer

B-Tree indexes is the default index type of MySQL
B-Trees store the indexed columns in order, they're usefull for searching for ranges of data
B-Tree indexes work well for lookups by the full key value, a key range, or a key prefix
B-Tree limitations:
1. They are not usefull if the lookup does not start from the leftmost side of the indexed columns
2. You can't skip columns in the index
3. The storage engine can't optimize accesses with any columns to the right of the first range condition

Hash indexes
Hash index is built on a hash table and is useful only for exact lookups that use every column in the index
For each row, the storage engine computes a hash code of the indexed columns, which is a small value that will probably differ from the hash codes computed for other rows with different key values
It stores the hash codes in the index and stores a pointer to each row in a hash table
Hash indexes are the default index type for Memory tables in MySQL
Hash index limitations:
1. Index contains only hash code and row pointers, MySQL can't use the values in the index to avoid reading the rows
2. MySQL can't use has indexes for sorting bc they don't store rows in sorted order
3. Hash indexes don't support partial key matching bc they compute the hash from the entire indexed value
4. Hash indexes support only equality comparisons that use the =, IN(), and <=> operators, they don't support range queries
5. Accessing data in a hash index is very quick, unless there are many collisions(When there are multiple values with the same hash, the storage engine must follow each row pointer in the linked list and compare their values to the lookup value to find the right rows)
6. Some index maintenance operations can be slow if there are many hash collisions
Hash index are useful for lookup tables in a classic "star" schema data-warehousing
The InnoDB storage engine has a special feature called adaptive hash indexes
When InndoDB notices that some index values are being accessed very frequently, it builds a hash index for them in memory on top of B-Tree index
This process is completely automatic, and you can't control or configure it
You can build your own hash index if storage engine does not support it

Spatial(R-Tree) indexes
MyISAM supports spatial indexes, which you can use with geospatial types such as GEOMETRY

Full-text indexes
FULLTEXT is a special type of index for MyISAM tables
Full-text indexes are for MATCH AGAINST operations, not ordinary WHERE clause operations

Indexing Strategies for High performance
Isolate the Column
If you don't isolate the indexed columns in a query, MySQL generally can't use indexes on columns unless the columns are isolated in the query
// these queries can't use index
SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;
SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;


Prefix Inndexes and Index Selectivity
For long character columns such as BLOB or TEXT, we can index the prefix and make index selectivity close to full column's selectivity

Clustered Indexes
From wikipedia:
Index architectures can be classified as clustered or non-clustered.
Unclustered
An Unclustered index is structured in a way that doesn't correspond to the order of the actual data records. It resembles the words index at the back of a book. For this reason, it will perform worse than clustered indexes on ranged searches where the result set is large, since each result could cost an additional I/O-operation to get the actual data record. One can have any number of these kinds of indexes, since all that is required is the space to store the index itself -- one does not copy the actual data to create a new index.
Clustered
Clustering alters the data block into a certain distinct order to match the index, hence it is also an operation on the data storage blocks as well as on the index. An address book ordered by first name resembles a clustered index in its structure and purpose. The exact operation of database systems vary, but because storing data is very redundant the row data can only be stored in one order. Therefore, only one clustered index can be created on a given database table. Clustered indexes can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items are selected.
Since the physical records are in this sort order on disk, the next row item in the sequence is immediately before or after the last one, and so fewer data block reads are required. The primary feature of a clustered index is therefore the ordering of the physical data rows in accordance with the index blocks that point to them. Some databases separate the data and index blocks into separate files, others put two completely different data blocks within the same physical file(s).Clusturing is a great example of Indexing and is far more sophisticated than an unclustered index.

Now only solidDB and InnoDB support clustered indexes
InnoDB clusters the data by the primary key
If you don't define a primary key, InnoDB will try to use a unique nonnullable index instead
If there's no such index, InnoDB will define a hidden primary key for you and the cluster on that

Clustered indexes advantages:
1. You can keep related data close together
2. Data access is fast
3. Queries that use covering indexes can use the primary key values contained at the leaf node

Clustered indexes disadvantages:
1. Clustering gives the largest improvement for I/O-bound workfloads. If the data fits in memory the roder in which it's accessed doesn't really matter, so clustering doesn't give much benefit
2. Insert speeds depend heavily on insertion order
3. Updating the clustered index columns is expensive, because it forces InnoDB to move each updated row to a new location
4. Tables built upon clustered indexes are subject to page splits when new rows are inserted, or when a row's primary key is updated such that the row must be moved
5. Clustered tables can be slower for full table scans
6. Secondary(nonclustered) indexes can be larger than you might expect, because their leaf nodes contain the primary key columns of the referenced rows
7. Secondary index accesses require two index lookups instead of one. To find a row from a secondary index, the storage engine first finds the leaf node in the secondary index and then uses the primary key values stores there to navigate the primary key and find the row

An index that contains or covers all the data needed to satisfy a query is called a covering index

An Indexing Case Study
You should put range column to the end of index, because MySQL uses only the leftmost prefix, up to and including the first condition that specifies a range of values
Avoid multiple range conditions
The query can be slow if the user interface is paginated and someone requests a page that's not near the beginning
Denormalizing, precomputing, and caching are likely to be the only strategies that work for queries like this
An even better strategy is to limit the number of pages you let the user view
This is unlikely to impact the user's experience, because no one really cares about the 10,000th page of search results

Index and Table Maintenance
Finding and Repairing Table Corruption
CHECK TABLE
REPAIR TABLE
ALTER TABLE innodb_tbl ENGINE=INNODB;
myisamchk
backup and recovery

Updating Index Statistics
ANALYZE TABLE
SHOW INDEX FROM
INFORMATION_SCHEMA.STATICTICS

Reducing Index and Data Fragmentation
OPTIMIZE TABLE
dump and reload the data


Normalization and Denormalization
Database Normalization

Advantages of Normalized Schema:
1, Normalized upodates are usually faster than denormalized updates
2, When the data is well normalized, there's little or no duplicated data, so there's less data to change
3, Normalized tables are usually smaller, so they fit better in memory and perform better
4, The lack of redundant data means there's less need for DISTINCT or GROUP BY queries when retrieving lists of values

Drawbacks of Normalized Schema:
Any non-trivial query on a well-normalized schema will probably require at least one join, and perhaps several
This is not only expensive, but it can make some indexing strategies impossible

Advantages of Normalized Schema:
1, Everyhing is in the same table, which avoids joins. This can be much faster than a join, because it avoids random I/O
2, A single table can allow more efficient indexing strategies

In the real world, you often need to mix the approaches, possibly using a partially normalized schema, cache tables, and other techniques
The most common way to denormalized data is to duplicate or cache selected columns from one table in anotehr table, you can use triggers to udpate the cached values which makes the implementation easier
You'll often need extra idnexes, redundant fields, or even cache and summary tables to speed up read queries

Speeding Up ALTER TABLE
MySQL's ALTER TABLE performance can become a problem with very large tables, MySQL performs most alterations by making an empty table with the desired new structure, inserting all the data from the old table into the new one, and deleting the old table
ALTER TABLE sakila.film MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5; // slow
ALTER TABLE sakila.film ALTER COLUMN rental_duration SET DEFAULT 5; // fast. This statement modifies the .frm file and leaves the table alone

Modifying Only the .frm file to convince MySQL not to rebuild the table
CREATE TABLE sakila.film_new LIKE sakila.film
ALTER TABLE sakila.film_new MODIFY COLUMN rating ENUM('G', 'PG', 'PG-13', 'R', 'NC-17', 'PG-14') DEFAULT 'G'
FLUSH TABLES WITH READ LOCK;
// swap the .frm files of table film and film_new
mv film.frm film_tmp.frm
mv film_new.frm film.frm
mv film_tmp.frm film_new.frm
UNLOCK TABLES
DROP TABLE sakila.film_new


Builing MyISAM Indexes Quickly
The usual trick for loading MyISAM tables efficiently is to disable keys, load the data, and reenable the keys:
ALTER TABLE test.load_data DISABLE KEYS
-- load the data
ALTER TABLE test.load_data ENABLE KEYS

This only works for nonunique indexes.
For unique indexes, the faster way:
1, create a table of the desied structure, without any indexes
2, load the data into the table to build the .MYD file
3, create another empty table with the desired structure, including indexes, this will create .frm and .MYI files
4, FLush the tables with a read lock
5, Rename the second tables's .frm and .MYI files, so MySQL uses them for the first table
6, Release the read lock
7, Use REPAIR TABLE to build the table's indexes

Notes on Storage Engines
MyISAM
Table locks
MyISAM tables have table-level locks, be careful this doesn't become a bottleneck
No automated data recovery
If the MySQL server crashes or power goes down, you should check and possibly repair your MyISAM tables before using them. If you have large tables, this could take hours
No transactions
MyISAM table don't support transactions
Only indexes are cached in memory
MyISAM caches only the index inside the MySQL process, in the key buffer. The operating system caches the table's data
Compact storage
Rows are stored jam-packed one after anotehr, so you get a small disk footprint and fast full table scans for on-disk data

Memory
Table locks
Memory tables have table locks but isn't usually a problem because queries on Memory tables are normally fast
No dynamic rows
Memory tables don't support dynamic(variable-length) rows, so they don't support BLOB and TEXT fields at all. Even a VARCHAR(5000) turns into a CHAR(5000) -- a huge memory waste if most values are small
Hash indexes are the default index type
No index statistics
You may get bad execution palns for some complex queries
Content is lost on restart
Memory tables don't persist any data to disk, so the data is lost when the server restarts, even though the table's definitions remain

InnoDB
Transactional
InnoDB supports transactions adn four transaction isolation levels
Foreign keys
As of MySQL 5.0, InnoDB is the only storage engine that supports foreign keys
Row-level locks
Locks are set at the row level, with no escalation and nonblocking selects--standard selects don't set any locks at all, which gives very good concurrency
Multiversioning
InnoDb uses multiversion concurrecncy control, so by default your selects may read stale data
Clustering by primary key
All InnoDB tables are clustered by the primary key, which you can use to your advantage in schema design
All indexes contain the primary key columns
Indexes refer to the rows by the primary key, so if you don't keep your primary key short, the indexes will grow very large
Optimized caching
InnoDB caches both data and memory in the buffer pool, it also automatically builds hash indexes to speed up row retrieval
Unpacked indexes
Indexes are not packed with prefix compression, so they can be much larger than for MyISAM tables
Slow data load
As of MySQL 5.0, InnoDb does not specially optimize data load operations. It builds indexes a row at a time, instead of building them by sorting. This may result in significantly slower data loads
Blocking AUTO_INCREMENT
In versions earlier than MySQL 5.1, InnoDB uses a table-level lock to generate each new AUTO_INCREMENT value
No cached COUNT(*) value
Unlick MyISAM or Memory tables, InnoDB tables don't store the number of rows in the table, which means COUNT(*) queries without a WHERE clause can't be optimized away and require full table or index scans
分享到:
评论

相关推荐

    HPM2010C4数据

    HPM2010C4数据

    HPM1005驱动程序

    3. pnp:即“Plug and Play”(即插即用),意味着该驱动支持Windows操作系统中的自动识别和配置设备功能。 4. win2kxp:表示该驱动程序兼容Windows 2000和Windows XP操作系统,说明这是一个较老版本的驱动,可能不...

    上海先楫HPM6750采集OV5640摄像头显示在屏幕【支持HPM6750和HPM6450】

    4. **显示接口**:HPM6750/HPM6450通常具备LCD或HDMI接口,用于连接显示屏。驱动程序需要适配这些接口,将处理后的图像数据转化为合适的格式,以便在屏幕上正确显示。 5. **实时性**:考虑到视频流的实时性要求,...

    HPM1210.INF

    HPM1210.INF

    hpm1210sd.rar

    4. 安装和配置:如果"HPM1210SD"是一个软件程序,这个RAR文件可能包含了安装脚本、配置文件和其他必要的组件,用户可以通过解压和运行其中的文件来安装或配置程序。 5. 设备驱动程序:在IT领域,"HPM1210SD"可能...

    PICMG_HPM_1R1_0.pdf

    这份文档,即HPM.1 R1.0,发布于2007年5月4日,详细定义了如何安全、高效地进行这一过程。 在服务器领域,HPM.1标准的实施对于确保系统的可靠性和可维护性至关重要。它提供了一个框架,允许系统管理员远程监控和...

    先楫半导体 HPM6700/6400 系列高性能微控制器用户手册

    HPM6700/6400 系列微控制器是基于 RISC-V 内核的 32 位高性能微控制器,适用于上海先楫半导体科技有限公司以下型号产品:HPM6750IVM1、HPM6750IAN1、HPM6730IVM1、HPM6730IAN1、HPM6450IVM1、HPM6450IAN1、HPM6430...

    HPM1210SU.ent

    HPM1210SU.ent HPM1210SU.ent HPM1210SU.ent

    TDC 3000 系统 HPM组态手册

    《TDC 3000系统HPM组态手册》是针对HONEYWELL公司旗下的TDC 3000控制系统的一份详细指南,主要涵盖了该系统的模块配置和设定,尤其适用于石化行业的分布式控制系统(DCS)应用。在这个高度集成且复杂的自动化系统中...

    HPM1210SM.exe

    HPM1210SM.exe

    HPM1210SU.VER

    HPM1210SU.VER

    HPM1210PP.dll

    HPM1210PP.dll

    HPM1210GC.DLL

    HPM1210GC.DLL

    hpm1005win7驱动

    描述中的“win7可以使用”确认了HP LaserJet M1005打印机在Windows 7系统上是支持的,并且提到了“hpm10005win7的驱动”,这可能是描述中的一个小错误,应该是指“hpm1005win7”的驱动,即对应的是HP LaserJet M1005...

    HPM.rar_ZIY_hpm

    4. **事件触发和报警机制**:HPM工具可能有阈值设置,当特定性能指标超过预设阈值时,会触发报警,这部分涉及到条件判断和事件处理。 5. **接口设计**:为了与其他系统组件(如Web服务或数据库)交互,代码可能包含...

    HPM1005驱动

    适用于XP32位操作系统的HPm1005打印机驱动

    HPM1210FPSD.DLL

    HPM1210FPSD.DLL

    最新 SAE J4002-2022 H-Point Machine (HPM-II) Specifications

    【最新】 SAE J4002-2022 H-Point Machine (HPM-II) Specifications and Procedure for H-Point Determination - Auditing Vehicle Seats.pdf

    OCP_2014_firware_update_HPM.1.pdf

    HPM.1 Firmware Update Open Compute Summit - Compute Summit HPM.1 Firmware Update Engineering Workshop HPM.1 File Format File is not sent as a single image Each action is sent by itself to IPM ...

    最新版 SAE J4002-2022 H-Point Machine (HPM-II) Specifications

    【最新版】 SAE J4002-2022 H-Point Machine (HPM-II) Specifications and Procedure for H-Point Determination - Auditing Vehicle Seats.pdf

Global site tag (gtag.js) - Google Analytics